Mysql原理，命令，及技巧總結 什麼是MySql數據庫 MySql數據庫是開放源代碼的關系型數據庫。目前，它可以提供的功能有：支持sql語言、子查詢、存儲過程、觸發器、視圖、索引、事務、鎖、外鍵約束和影像復制等。同Oracle 和SQL Server等大型數據庫系統一樣，MySql也是客戶/服務器系統並且是單進程多線程架構的數據庫。並且MySQL是一個真正的多用戶、多線程SQL數據庫服務器。MySQL的客戶機/服務器結構由一個服務器守護程序mysql和很多不同的客戶程序和庫組成。由於其源碼的開放性及穩定性，且與網站流行編徎語言PHP的完美結合，現在很多站點都利用其當作後端數據庫，使其獲得了廣泛應用。MySql區別於其它數據庫系統的一個重要特點是支持插入式存儲引擎。 那麼什麼是存儲引擎呢？ 存儲引擎說白了就是如何存儲數據、如何為存儲的數據建立索引和如何更新、查詢數據等技術的實現方法。因為在關系數據庫中數據的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型（即存儲和操作此表的類型）。在Oracle 和SQL Server等數據庫中只有一種存儲引擎，所有數據存儲管理機制都是一樣的。而MySql數據庫提供了多種存儲引擎。用戶可以根據不同的需求為數據表選擇不同的存儲引擎，用戶也可以根據自己的需要編寫自己的存儲引擎。 MySql中有哪些存儲引擎？ 1. MyISAM：這種引擎是MySql最早提供的， 這種引擎又可以分為靜態MyISAM、動態MyISAM 和壓縮MyISAM三種： 靜態MyISAM：如果數據表中的各數據列的長度都是預先固定好的，服務器將自動選擇這種表類型。因為數據表中每一條記錄所占用的空間都是一樣的，所以這種表存取和更新的效率非常高。當數據受損時，恢復工作也比較容易做。 動態MyISAM：如果數據表中出現varchar、xxxtext或xxxBLOB字段時，服務器將自動選擇這種表類型。相對於靜態MyISAM，這種表存儲空間比較小，但由於每條記錄的長度不一，所以多次修改數據後，數據表中的數據就可能離散的存儲在內存中，進而導致執行效率下降。同時，內存中也可能會出現很多碎片。因此，這種類型的表要經常用optimize table命令或優化工具來進行碎片整理。 壓縮MyISAM：以上說到的兩種類型的表都可以用myisamchk工具壓縮。這種類型的表進一步減小了占用的存儲，但是這種表壓縮之後不能再被修改。另外，因為是壓縮數據，所以這種表在讀取的時候要先時行解壓縮。 但是，不管是何種MyISAM表，目前它都不支持事務，行級鎖和外鍵約束的功能。 2 MyISAM Merge引擎：這種類型是MyISAM類型的一種變種。合並表是將幾個相同的MyISAM表合並為一個虛表。常應用於日志和數據倉庫。 3 InnoDB：InnoDB表類型可以看作是對MyISAM的進一步更新產品，它提供了事務、行級鎖機制和外鍵約束的功能。 4 memory(heap)：這種類型的數據表只存在於內存中。它使用散列索引，所以數據的存取速度非常快。因為是存在於內存中，所以這種類型常應用於臨時表中。 5 archive：這種類型只支持select 和 insert語句，而且不支持索引。常應用於日志記錄和聚合分析方面。 當然MySql支持的表類型不止上面幾種。 下面介紹如何查看和設置數據表類型。 MySql中關於存儲引擎的操作： 1查看數據庫可以支持的存儲引擎：show engines，默認數據表類型是MyISAM。當然，我們可以通過修改數據庫配置文件中的選項，設定默認表類型。 2.通過顯示表的創建語句可以查看該表的Engine, Showcreate table tablename; 3 設置或修改表的存儲引擎，在創建表的語句上加上engine=engineName即可，如： [sql] create table user( id intnot null auto_increment, usernamechar(20) not null, sexchar(2), primarykey(id) ) engine=merge 而修改存儲引擎，使用alter命令，很容易想到，因為Engine是在創建表的時候定好的，顧使用alter來修改，可以用命令Altertable tableName engine =engineName 假如，若需要將表user的存儲問引擎修改為archive類型，則可使用命令alter table userengine=archive。 聚集索引 聚集索引確定表中數據的物理順序。聚集索引類似於電話簿，後者按姓氏排列數據。由於聚集索引規定數據在表中的物理存儲順序，因此一個表只能包含一個聚集索引。但該索引可以包含多個列（組合索引），就像電話簿按姓氏和名字進行組織一樣。 聚集索引對於那些經常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行後，便可以確保包含後續索引值的行在物理相鄰。例如，如果應用程序執行 的一個查詢經常檢索某一日期范圍內的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然後檢索表中所有相鄰的行，直到到達結束日期。這樣有助於提高此類查詢的性能。同樣，如果對從表中檢索的數據進行排序時經常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進行排序，從而節 省成本。 當索引值唯一時，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇員 ID 列 emp_id 查找特定雇員的最快速的方法，是在 emp_id 列上創建聚集索引或 PRIMARY KEY 約束 My SQL常用目錄及系統命令 1、數據庫默認目錄 /var/lib/mysql/,為啥放在這個地方？一般var下面是最大的目錄，數據文件也存儲在這個地方，如果要想修改數據文件目錄，要配置文件進行修改，有幾個地方要進行修改 （1）在配置文件中mysql.sock文件產生的位置，mysql.sock文件的產生位置，如socket = /home/data/mysql/mysql.sock （2）修改mysql的啟動腳本中的datadir：init.d/myql：datadir=/home/data/mysql需要注意的有要將默認路徑下的data文件copy到新目錄而不是新建，還有要將Mysql的一份配置文件copy到/etc/下面，對sock位置的修改要在改文件下修改，最後在修改前停止mysql, 修改完進行生效重啟Mysql. 2、配置文件 /usr/share/mysql（mysql.server命令及配置文件），如/usr/share/mysql/my-medium.cnf 3、相關命令 /usr/bin(mysqladminmysqldump等命令) 命令在/usr/bin下 4、啟動腳本 /etc/rc.d/init.d/mysql（啟動腳本文件mysql的目錄） 5、自動啟動：只要記住關於自動啟動的都在/sbin/chkconfig就可以了 1）察看mysql是否在自動啟動列表中 [root@test1local]# /sbin/chkconfig –list 2）把MySQL添加到系統啟動服務組裡面去 [root@test1local]# /sbin/chkconfig –add mysql 3）把MySQL從啟動服務組裡面刪除。[root@test1 local]# /sbin/chkconfig –del mysql 6、停掉與啟動Mysql命令：mysqladmin -u root -pshutdown 和 /etc/rc.d/init.d/mysql start Sql命令中的幾個關鍵字 (1) Group by使用， 比如delete from tablename where id not in (select max(id) from tablenamegroup by col1,col2,...) 《= 刪除重復數據的例子， Group By的含義是進行分組值相同的為一組，注意使用Group By 之後 Select 出來的列必須只能來自於group By 或使用聚合函數Sum, AVG, max, min等中的列：返回集字段中，這些字段要麼就要包含在Group By語句的後面，作為分組的依據；要麼就要被包含在聚合函數中，GroupBy與聚合函數一塊使用，表示對分組後的數據進行求和，平均，最大值等。 (2) Group By 和 Having, Where ,Order by語句的執行順序：以盡可能的減少操縱的數據為原則，顯然順序為：Where, Group By, Having, Order by，首先where將最原始記錄中不滿足條件的記錄篩選，減少分組數據，然後Group By與Having顯然是Having是基於Groupby的，顧Having在Groupby之後，而Order By在Groupby和where之前都沒用用，只有在Groupby之後才起作用，顧最後。 (3) Having 的使用：Having對Group By進行限定條件，可以使用聚合函數和Groupby中的列。 (4) Left outer join 與 left inner join的區別：外連接可以包含主表的全部行，而內連接只含有匹配那行 (5) Distinct 用法，去掉重復行，類似於Linux命令中的uniq,但uniq命令只能去掉相鄰的重復行，所以在用之前要進行sort (6) SQL Sever中用法： sysobjects和syscolumns表放了數據庫中的所有表名和一個表的列名，而在My SQL中使用：show tables MySql命令 (1) Mysql的所有用戶都在user表中，可以通過該表進行，增加用戶，修改用戶密碼，刪除用戶，和賦予用戶權限等，注意修改後要使用FLUSH PRIVILEGES進行確認，如下： 刪除匿名用戶：delete from User where User=""; 匿名用戶的User是空 增加一個用戶 INSERT INTO mysql.user (Host,User,Password) VALUES ('%','system', PASSWORD('manager')); 修改密碼update User set Password=PASSWORD(newpassword) where User=root; (1) 用Grant 命令授權，輸入的代碼如下: grant select,insert,update on mydb.*to NewUserName@HostNameidentified by"password"; 如果對HostName不限制，則使用% (2) 對用戶的每一項權限進行設置： 如： mysql>INSERT INTO user VALUES('localhost','system',PASSWORD('manager'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); 對於3.22.34版本的MySQL,這裡共14個"Y",其相應的權限如下(按字段順序排列): 權限 表列名稱 相應解釋 使用范圍 select Select_priv 只有在真正從一個表中檢索時才需要select權限 表 insert Insert_priv 允許您把新行插入到一個存在的表中 表 update Update_priv 允許你用新值更新現存表中行的列 表 delete Delete_priv 允許你刪除滿足條件的行 表 create Create_priv 允許你創建新的數據庫和表 數據庫、表或索引 drop Drop_priv 拋棄(刪除)現存的數據庫和表 數據庫或表 reload Reload_priv 允許您告訴服務器再讀入授權表 服務器管理 shutdown Shutdown_priv 可能被濫用(通過終止服務器拒絕為其他用戶服務) 服務器管理 process Process_priv 允許您察看當前執行的查詢的普通文本,包括設定或改變口令查詢 服務器管理 file File_priv 權限可以被濫用在服務器上讀取任何可讀的文件到數據庫表 服務器上文件存取 grant Grant_priv 允許你把你自己擁有的那些權限授給其他的用戶 數據庫或表 references References_priv 允許你打開和關閉記錄文件 數據庫或表 index Index_priv 允許你創建或拋棄(刪除)索引 表 alter Alter_priv 允許您改變表格,可以用於通過重新命名表來推翻權限系統 表 對上面的14中權限，可以分為兩種全局管理權限和數據表權限 全局管理權限： FILE: 在MySQL服務器上讀寫文件。 PROCESS: 顯示或殺死屬於其它用戶的服務線程。 RELOAD: 重載訪問控制表，刷新日志等。 SHUTDOWN: 關閉MySQL服務。 數據庫/數據表/數據列權限： ALTER: 修改已存在的數據表(例如增加/刪除列)和索引。 CREATE: 建立新的數據庫或數據表。 DELETE: 刪除表的記錄。 DROP: 刪除數據表或數據庫。 INDEX: 建立或刪除索引。 INSERT: 增加表的記錄。 SELECT: 顯示/搜索表的記錄。 UPDATE: 修改表中已存在的記錄。 特別的權限： ALL: 允許做任何事(和root一樣)。 USAGE: 只允許登錄--其它什麼也不允許做。 (2) 查看所有用show命令，查看當前用select函數
查看所有的數據庫Show databases 創建和刪除數據庫create/drop database name 使用或連接數據庫use databasename(), 查看當前使用的數據庫：select database() 查詢當前時間：selectnow() 查詢當前用戶：select user() 查詢數據庫版本：select version() 查看所有表：show tables show processlist;列出每一筆聯機的信息。 show variables;列出mysql的系統設定。 show tables from db_name;列出db_name中所有數據表; show [full] columns from table_name;列出table_name中完整信息，如欄名、類型,包括字符集編碼。 show index from table_name; 列出table_name中所有的索引。 show table status;；列出當前數據庫中數據表的信息。 show table status from db_name;；列出當前db_name中數據表的信息。 alter table table_name engine innodb|myisam|memory ;更改表類型 explain table_name / describe table_name ; 列出table_name完整信息，如欄名、類型。 show create table table_name 顯示當前表的建表語句 alter table table_name add primary key (picid) ; 向表中增加一個主鍵 alter table table_name add column userid int after picid 修改表結構增加一個新的字段 alter table table_name character set gb2312 改變表的編碼 select user(); 顯示當前用戶。 select password(’root’); 顯示當前用戶密碼 select now(); 顯示當前日期 flush privileges 在不重啟的情況下刷新用戶權限 mysqld –default-character-set=gb2312 ;設置默認字符集為gb2312
(3) 表操作 備注:操作之前使用“use<數據庫名>”應連接某個數據庫。 匹配字符：可以用通配符_代表任何一個字符，％代表任何字符串; 聯合字符或者多個列(將列id與":"和列name和"="連接) select concat(id,':',name,'=') fromstudents; limit(選出10到20條)<第一個記錄集的編號是0>，SQLServer上為Top select * from students order by id limit 9,10; 建表：create table <表名> (<字段名 1> <類型 1> [,..<字段名 n><類型 n>]); 插入數據：insert into <表名> [( <字段名 1>[,..<字段名 n >])] values ( 值 1 )[, ( 值 n )] 獲取表結構：describe tablename, desc tablename, show columns from tablename 刪除表 drop table tablename 刪除表中數據 命令:delete from 表名 where 表達式 修改表中數據 命令:update 表名 set 字段=新值,... where 條件 在表中增加字段 命令:alter table 表名 add 字段 類型 其他; 更改表名 命令:rename table 原表名 to 新表名 更新字段內容 命令:update 表名 set 字段名 = 新內容, 刪除student_course數據庫中的students數據表：rm-f student_course/students.* 直接使 用linux命令來刪除，也可以使用drop命令 創建臨時表：create temporary table zengchao(name varchar(10)); 創建表是先判斷表是否存在: create table ifnot exists students(……); 從已經有的表中復制表的結構: create table table2 select * from table1 where 1<>1; 復制表，復制表的時候也會將表結構也復制過去了:create table table2 select * from table1; 對表重新命名：altertable table1 rename as table2; 創建索引，創建索引有兩種方式，alter原來的表結構然後add，或者
create index on altertable table1 add index ind_id (id); createindex ind_id on table1 (id); createunique index ind_id on table1 (id);//建立唯一性索引
刪除索引drop index idx_id on table1; alter table table1 dropindex ind_id; 可以使用ALTER TABLE語句來更新與屬性或表有關的約束。關於修改索引也是用類似的命令 刪除約束：ALTER TABLEDROP CONSTRAINT約束名 增加約束ALTER TABLEADD CONSTRAINT約束名約束定義 (4) 數據庫導入導出 從數據庫導出數據庫文件, 使用“mysqldump”命令 1)導出數據庫: mysqldump -u [用戶名] –p [數據庫名] -A>[備份文件的保存路徑]，如 mysqldump -h localhost -u root -p mydb >e:\MySQL\mydb.sql 2)導出數據和數據結構：mysqldump -u [用戶名] -p [數據名 表名>[備份文件的保存路徑] mysqldump -h localhost -u root -p mydb mytable>e:\MySQL\mytable.sql ：數據表 mysqldump -h localhost -u root -p mydb --add-drop-table >e:\MySQL\mydb_stru.sql：數據庫結構 3)只導出數據不導出數據結構：mysqldump -u [用戶名] -p -t [數據庫名]>[備份文件的保存路徑] 4)導出數據庫中的Events: mysqldump -u [用戶名] -p -E [數據庫名]>[備份文件的保存路徑] 5)導出數據庫中的存儲過程和函數:mysqldump -u [用戶名] -p -R [數據庫名]>[備份文件的保存路徑] 從外部文件導入數據庫中 1)使用“source”命令：source [備份文件的保存路徑] 這個Source有點類似shell的source命令 2)使用“<”符號：mysql -u root –p < [備份文件的保存路徑] 3)用文本方式將數據裝入數據庫表中（例如D:/mysql.txt） mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE; MySQL命令應用： (1) mysql之刪除重復數據，刪除id重復的數據
delete person as a from person as a,
(
select *,min(id) from person group by id having count(1)> 1
) as b
where a.id = b.id
(2) 查找重復的，並且除掉最小的那個
delete tb_person as a from tb_person as a, ( select *,min(id) from tb_person group by name having count(1) > 1 ) as b where a.name = b.name and a.id > b.id;
MySql explain優化SQL語句 在mysql version 4.1中，explain輸出的結果格式改變了，使得它更適合例如 union語句、子查詢以及派生表的結構。更令人注意的是，它新增了2個字段： id和 select_type。當你使用早於mysql4.1的版本就看不到這些字段了。 explain結果的每行記錄顯示了每個表的相關信息，每行記錄都包含以下幾個字段： (1) id ： 本次 select 的標識符。在查詢中每個 select都有一個順序的數值。 (2) select_type：select 的類型，可能會有以下幾種： A. simple: 簡單的 select （沒有使用 union或子查詢） B. primary: 最外層的 select。 C. union: 第二層，在select 之後使用了 union。 D. dependent union: union 語句中的第二個select，依賴於外部子查詢 E. subquery: 子查詢中的第一個 select F. dependent subquery: 子查詢中的第一個 subquery依賴於外部的子查詢 G. derived: 派生表 select（from子句中的子查詢） (3) table：記錄查詢引用的表。 (4) type：表連接類型。以下列出了各種不同類型的表連接，依次是從最好的到最差的： A. system: 表只有一行記錄（等於系統表）。這是 const表連接類型的一個特例。 B. const: 表中最多只有一行匹配的記錄，它在查詢一開始的時候就會被讀取出來。由於只有一行記錄，在余下的優化程序裡該行記錄的字段值可以被當作是一個恆定值。const表查詢起來非常快，因為只要讀取一次！const用於在和 primary key或unique索引中有固定值比較的情形。下面的幾個查詢中，tbl_name 就是 c表了：
select * from tbl_name where primary_key=1; select * from tbl_name whereprimary_key_part1=1 and primary_key_part2=2;
C. eq_ref: 從該表中會有一行記錄被讀取出來以和從前一個表中讀取出來的記錄做聯合。與const類型不同的是，這是最好的連接類型。它用在索引所有部分都用於做連接並且這個索引是一個primary key或 unique類型。eq_ref可以用於在進行"="做比較時檢索字段。比較的值可以是固定值或者是表達式，表達式中可以使用表裡的字段，它們在讀表之前已經准備好 了。以下的兩個例子中，mysql使用了eq_ref 連接來處理
ref_table：（1）select * from ref_table,other_table where ref_table.key_column=other_table.column;（2）select * from ref_table,other_tablewhere ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
D. ref: 該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。ref用於連接程序使用鍵的最左前綴或者是該鍵不是 primary key或 unique索引（換句話說，就是連接程序無法根據鍵值只取得一條記錄）的情況。當根據鍵值只查詢到少數幾條匹配的記錄時，這就是一個不錯的連接類型。ref還可以用於檢索字段使用 =操作符來比較的時候。以下的幾個例子中，mysql將使用 ref 來處理
ref_table：(1) select * from ref_table wherekey_column=expr; (2) select * from ref_table,other_table where ref_table.key_column=other_table.column; (3) select * fromref_table,other_tablewhereref_table.key_column_part1=other_table.columnandref_table.key_column_part2=1;
E. ref_or_null: 這種連接類型類似 ref，不同的是mysql會在檢索的時候額外的搜索包含null值的記錄。這種連接類型的優化是從mysql4.1.1開始的，它經常用於子查詢。在以下的例子中，mysql使用ref_or_null 類型來處理 ref_table：select * from ref_table wherekey_column=expr or key_column is null; F. unique_subquery: 只是用來完全替換子查詢的索引查找函數效率更高了，這種類型用例如一下形式的 in 子查詢來替換ref：value in(select primary_key from single_table where some_expr) G. Index_subquery: 這種連接類型類似 unique_subquery。它用子查詢來代替in，不過它用於在子查詢中沒有唯一索引的情況下，例如以下形式：value in (select key_column from single_table where some_expr) H. range: 只有在給定范圍的記錄才會被取出來，利用索引來取得一條記錄。key字段表示使用了哪個索引。 key_len字段包括了使用的鍵的最長部分。這種類型時 ref 字段值是 null。range用於將某個字段和一個定植用以下任何操作符比較時 =, <>, >,>=,<, <=, is null, <=>, between, 或 in：select * from tbl_name where key_column = 10; select * fromtbl_namewhere key_column between 10 and 20; select * from tbl_namewhere key_column in(10,20,30); select * from tbl_name wherekey_part1= 10 and key_part2 in(10,20,30); I. index: 連接類型跟 all 一樣，不同的是它只掃描索引樹。它通常會比 all快點，因為索引文件通常比數據文件小。mysql在查詢的字段知識單獨的索引的一部分的情況下使用這種連接類型。 J. all: 將對該表做全部掃描以和從前一個表中取得的記錄作聯合。這時候如果第一個表沒有被標識為const的話就不大好了，在其他情況下通常是非常糟糕的。正常地，可以通過增加索引使得能從表中更快的取得記錄以避免all。 (5) possible_keys：possible_keys字段是指 mysql在搜索表記錄時可能使用哪個索引。注意，這個字段完全獨立於explain 顯示的表順序。這就意味著 possible_keys裡面所包含的索引可能在實際的使用中沒用到。如果這個字段的值是null，就表示沒有索引被用到。這種情況下，就可以檢查 where子句中哪些字段那些字段適合增加索引以提高查詢的性能。就這樣，創建一下索引，然後再用explain 檢查一下。想看表都有什麼索引，可以通過 show index from tbl_name來看。 (6) key：key字段顯示了mysql實際上要用的索引。當沒有任何索引被用到的時候，這個字段的值就是null。想要讓mysql強行使用或者忽略在 possible_keys字段中的索引列表，可以在查詢語句中使用關鍵字force index,use index,或 ignore index。如果是 myisam 和 bdb 類型表，可以使用 analyzetable 來幫助分析使用使用哪個索引更好。如果是 myisam類型表，運行命令 myisamchk --analyze也是一樣的效果。 (7) key_len：key_len 字段顯示了mysql使用索引的長度。當 key 字段的值為 null時，索引的長度就是 null。注意，key_len的值可以告訴你在聯合索引中mysql會真正使用了哪些索引。 (8) ref：ref 字段顯示了哪些字段或者常量被用來和 key配合從表中查詢記錄出來。 (9) rows：rows 字段顯示了mysql認為在查詢中應該檢索的記錄數。 (10) extra：本字段顯示了查詢中mysql的附加信息。以下是這個字段的幾個不同值的解釋： A. distinct:mysql當找到當前記錄的匹配聯合結果的第一條記錄之後，就不再搜索其他記錄了。 B. not exists:mysql在查詢時做一個 left join優化時，當它在當前表中找到了和前一條記錄符合 left join條件後，就不再搜索更多的記錄了。下面是一個這種類型的查詢例子：select * from t1left join t2 on t1.id=t2.id where t2.id is null; 假使 t2.id 定義為 not null。這種情況下，mysql將會掃描表 t1並且用 t1.id 的值在 t2 中查找記錄。當在 t2中找到一條匹配的記錄時，這就意味著 t2.id 肯定不會都是null，就不會再在 t2 中查找相同 id值的其他記錄了。也可以這麼說，對於 t1 中的每個記錄，mysql只需要在t2 中做一次查找，而不管在 t2 中實際有多少匹配的記錄。 C. range checked for each record(index map: #) mysql沒找到合適的可用的索引。取代的辦法是，對於前一個表的每一個行連接，它會做一個檢驗以決定該使用哪個索引（如果有的話），並且使用這個索引來從表裡取得記錄。這個過程不會很快，但總比沒有任何索引時做表連接來得快。 D. using filesort: mysql需要額外的做一遍從而以排好的順序取得記錄。排序程序根據連接的類型遍歷所有的記錄，並且將所有符合 where條件的記錄的要排序的鍵和指向記錄的指針存儲起來。這些鍵已經排完序了，對應的記錄也會按照排好的順序取出來。 E. using index字段的信息直接從索引樹中的信息取得，而不再去掃描實際的記錄。這種策略用於查詢時的字段是一個獨立索引的一部分。 F. using temporary: mysql需要創建臨時表存儲結果以完成查詢。這種情況通常發生在查詢時包含了groupby和 order by 子句，它以不同的方式列出了各個字段。 G. using where，where子句將用來限制哪些記錄匹配了下一個表或者發送給客戶端。除非你特別地想要取得或者檢查表種的所有記錄，否則的話當查詢的extra 字段值不是 usingwhere 並且表連接類型是 all 或 index時可能表示有問題。 H. 如果你想要讓查詢盡可能的快，那麼就應該注意 extra 字段的值為using filesort 和 using temporary 的情況。 使用explain優化SQL實例 通過 explain 的結果中 rows字段的值的乘積大概地知道本次連接表現如何。它可以粗略地告訴我們mysql在查詢過程中會查詢多少條記錄。如果是使用系統變量 max_join_size 來取得查詢結果，這個乘積還可以用來確定會執行哪些多表select 語句。下面的例子展示了如何通過 explain提供的信息來較大程度地優化多表聯合查詢的性能。假設有下面的 select 語句，正打算用 explain 來檢測：
[sql] explain select tt.ticketnumber, tt.timein, tt.projectreference,tt.estimatedshipdate, tt.actualshipdate, tt.clientid,tt.servicecodes, tt.repetitiveid, tt.currentprocess,tt.currentdppers tt.recordvolume, tt.dpprinted, et.country,et_1.country, do.custname [sql] from tt, et, et as et_1, do [sql] where tt.submittime is null and tt.actualpc = et.employid and tt.assignedpc = et_1.employid and tt.clientid = do.custnmbr;
從from子句可以看出select子句要進行多表查詢，要從三個表tt, et 和do三個表中進行聯合查詢，看起來是四個表 重點看where比較子句：tt.submittime is null and tt.actualpc = et.employid and tt.assignedpc =et_1.employid and tt.clientid = do.custnmbr; 假設要比較的字段定義為表tt中的actualpc，actualpc 和clientid 均為 char(10)類型，et的employid 為char(15) 類型，do 的custnmbr為char(15) 類型， 這三個表的索引為tt有上那個索引：actualpc（值分布不均勻），assignedpc和clientid， et的為主鍵索引：employid (primary key)，do表的也為主鍵索引custnmbr (primary key) 第一步，在任何優化措施未采取之前，經過 explain分析的結果顯示如下：
[sql] table type possible_keys key key_len ref rows extra et all primary null null null 74 do all primary null null null 2135 et_1 all primary null null null 74 tt all assignedpc null null null 3872 clientid, actualpc range checked for each record (key map: 35)
分析：由於字段 type 的對於每個表值都是all，這個結果意味著mysql對所有的表做一個迪卡爾積；這就是說，每條記錄的組合。這將需要花很長的時間，因為需要掃描每個表總 記錄數乘積的總和。在這情況下，它的積是74 * 2135 * 74 *3872 = 45,268,558,720條記錄。如果數據表更大的話，你可以想象一下需要多長的時間。 在這裡有個問題是當字段定義一樣的時候，mysql就可以在這些字段上更快的是用索引（對isam類型的表來說，除非字段定義完全一樣，否則不會使用索 引）。在這個前提下，varchar和 char是一樣的除非它們定義的長度不一致。由於 tt.actualpc 定義為char(10)，et.employid 定義為 char(15)，二者長度不一致。 為了解決這個問題，需要用 alter table 來加大 actualpc的長度從10到15個字符：altertable tt modify actualpc varchar(15); 現在 tt.actualpc 和 et.employid 都是 varchar(15)，在執行一次explain:
[sql] table type possible_keys key key_len ref rows extra tt all assignedpc, null null null 3872 using clientid, where actualpc do all primary null null null 2135 range checked for each record (keymap: 1) et_1 all primary null null null 74 range checked for eachrecord (key map: 1) et eq_ref primary primary 15 tt.actualpc 1
這還不夠，它還可以做的更好：現在 rows值乘積已經少了74倍。這次查詢需要用2秒鐘。 第二個改變是消除在比較 tt.assignedpc = et_1.employid 和 tt.clientid= do.custnmbr 中字段的長度不一致問題：
altertable tt modify assignedpc varchar(15), ->modify clientid varchar(15); [sql] table type possible_keys key key_len ref rows extra et all primary null null null 74 tt ref assignedpc, actualpc 15 et.employid 52 using clientid, where actualpc et_1 eq_ref primary primary 15 tt.assignedpc 1 do eq_ref primary primary 15 tt.clientid 1
這看起來已經是能做的最好的結果了。遺留下來的問題是，mysql默認地認為字段tt.actualpc的值是均勻分布的，然而表tt並非如此。幸好，我們可以很方便的讓mysql分析索引的分布：mysql>analyze table tt; 到此為止，表連接已經優化的很完美了，explain 的結果如下：
[sql] table type possible_keys key key_len ref rows extra tt all assignedpc null null null 3872 using clientid, where actualpc et eq_ref primary primary 15 tt.actualpc 1 et_1 eq_ref primary primary 15 tt.assignedpc 1 do eq_ref primary primary 15 tt.clientid 1
請注意，explain 結果中的 rows字段的值也是mysql的連接優化程序大致猜測的，請檢查這個值跟真實值是否基本一致。如果不是，可以通過在select 語句中使用 straight_join 來取得更好的性能，同時可以試著在from分句中用不同的次序列出各個表。 調整Mysql數據庫性能： 改變索引緩沖區長度(key_buffer)：一般，該變量控制緩沖區的長度在處理索引表（讀/寫操作）時使用。MySQL使用手冊指出該變量可以不斷增加以確保索引表的最佳性能，並推薦使用與系統內存25％的大小作為該變量的值。這是MySQL十分重要的配置變量之一，如果你對優化和提高系統性能有興趣，可以從改變key_buffer_size變量的值開始。 改變表長(read_buffer_size)：當一個查詢不斷地掃描某一個表，MySQL會為它分配一段內存緩沖區。read_buffer_size變量控制這一緩沖區的大小。如果你認為連續掃描進行得太慢，可以通過增加該變量值以及內存緩沖區大小提高其性能。 設定打開表的數目的最大值(table_cache)：該變量控制MySQL在任何時候打開表的最大數目，由此能控制服務器響應輸入請求的能力。它跟max_connections變量密切相關，增加 table_cache值可使MySQL打開更多的表，就如增加max_connections值可增加連接數一樣。當收到大量不同數據庫及表的請求時，可以考慮改變這一值的大小。 對緩長查詢設定一個時間限制(long_query_time)：MySQL帶有“慢查詢日志”，它會自動地記錄所有的在一個特定的時間范圍內尚未結束的查詢。這個日志對於跟蹤那些低效率或者行為不端的查詢以及尋找優化對象都非常有用。long_query_time變量控制這一最大時間限定，以秒為單位。 Mysql 優化表命令 AnalyzeTable MySQL 的Optimizer（優化元件）在優化SQL語句時，首先需要收集一些相關信息，其中就包括表的cardinality（可以翻譯為“散列程度”），它表示某個索引對應的列包含多少個不同的值——如果cardinality大大少於數據的實際散列程度，那麼索引就基本失效了。 我們可以使用SHOW INDEX語句來查看索引的散列程度：SHOWINDEX FROM PLAYERS; TABLE KEY_NAME COLUMN_NAMECARDINALITY ------- -------- ----------- ----------- PLAYERS PRIMARYPLAYERNO 14 因為此時PLAYER表中不同的PLAYERNO數量遠遠多於14，索引基本失效。 下面我們通過Analyze Table語句來修復索引： ANALYZE TABLE PLAYERS; SHOW INDEX FROM PLAYERS; 結果是： TABLE KEY_NAME COLUMN_NAMECARDINALITY ------- -------- ----------- ----------- PLAYERS PRIMARYPLAYERNO 1000 此時索引已經修復，查詢效率大大提高。 需要注意的是，如果開啟了binlog，那麼Analyze Table的結果也會寫入binlog，我們可以在analyze和table之間添加關鍵字local取消寫入。 Checksum Table數據在傳輸時，可能會發生變化，也有可能因為其它原因損壞，為了保證數據的一致，我們可以計算checksum（校驗值）。使用MyISAM引擎的表會把checksum存儲起來，稱為live checksum，當數據發生變化時，checksum會相應變化。在執行Checksum Table時，可以在最後指定選項qiuck或是extended；quick表示返回存儲的checksum值，而extended會重新計算checksum，如果沒有指定選項，則默認使用extended。 Optimize Table經常更新數據的磁盤需要整理碎片，數據庫也是這樣，Optimize Table語句對MyISAM和InnoDB類型的表都有效。如果表經常更新，就應當定期運行OptimizeTable語句，保證效率。與Analyze Table一樣，Optimize Table也可以使用local來取消寫入binlog。對於經常修改的表，容易產生碎片，使在查詢數據庫時必須讀取更多的磁盤塊，降低查詢性能。具有可變長的表都存在磁盤碎片問題，這個問題對blob數據類型更為突出，因為其尺寸變化非常大。可以通過使用optimize table來整理碎片，保證數據庫性能不下降，優化那些受碎片影響的數據表。 optimize table可以用於MyISAM和BDB類型的數據表。實際上任何碎片整理方法都是用mysqldump來轉存數據表，然後使用轉存後的文件並重新建數據表； Check Table數據庫經常可能遇到錯誤，譬如數據寫入磁盤時發生錯誤，或是索引沒有同步更新，或是數據庫未關閉MySQL就停止了。遇到這些情況，數據就可能發生錯誤：Incorrectkey file for table: ' '. Try to repair it. 此時，我們可以使用Check Table語句來檢查表及其對應的索引。譬如我們運行CHECKTABLE PLAYERS; 結果是 TABLE OP MSG_TYPE MSG_TEXT -------------- ----- -------- -------- TENNIS.PLAYERS check status OK MySQL會保存表最近一次檢查的時間，每次運行check table都會存儲這些信息： 執行
SELECT TABLE_NAME, CHECK_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PLAYERS' AND TABLE_SCHEMA ='TENNIS'; /*TENNIS是數據庫名*/
結果是 TABLE_NAME CHECK_TIME ---------- ------------------- PLAYERS 2006-08-2116:44:25 Check Table還可以指定其它選項： UPGRADE：用來測試在更早版本的MySQL中建立的表是否與當前版本兼容。 QUICK：速度最快的選項，在檢查各列的數據時，不會檢查鏈接（link）的正確與否，如果沒有遇到什麼問題，可以使用這個選項。 FAST：只檢查表是否正常關閉，如果在系統掉電之後沒有遇到嚴重問題，可以使用這個選項。 CHANGED：只檢查上次檢查時間之後更新的數據。 MEDIUM：默認的選項，會檢查索引文件和數據文件之間的鏈接正確性。 EXTENDED：最慢的選項，會進行全面的檢查。 Repair Table 用於修復表，只對MyISAM和ARCHIVE類型的表有效。 這條語句同樣可以指定選項： QUICK：最快的選項，只修復索引樹。 EXTENDED：最慢的選項，需要逐行重建索引。 USE_FRM：只有當MYI文件丟失時才使用這個選項，全面重建整個索引。 與Analyze Table一樣，Repair Table也可以使用local來取消寫入binlog。
