摘要：本文介紹了如何優化SQL查詢。你可以手工使用EXPLAIN語句檢查SQL查詢的效率。另外，還講述了一些優化SQL語句的原則，主要是檢索記錄和裝載數據時如何優化SQL語句的原則。
使用EXPLAIN語句檢查SQL語句
當你在一條SELECT語句前放上關鍵詞EXPLAIN，MySQL解釋它將如何處理SELECT，提供有關表如何聯結和以什麼次序聯結的信息。
借助於EXPLAIN，你可以知道你什麼時候必須為表加入索引以得到一個使用索引找到記錄的更快的SELECT。
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一個同義詞。
從EXPLAIN的輸出包括下面列：
·table
輸出的行所引用的表。
· type
聯結類型。各種類型的信息在下面給出。
不同的聯結類型列在下面，以最好到最差類型的次序：
system const eq_ref ref range index ALL possible_keys
· key
key列顯示MySQL實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。
· key_len
key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，長度是NULL。注意這告訴我們MySQL將實際使用一個多部鍵值的幾個部分。
· ref
ref列顯示哪個列或常數與key一起用於從表中選擇行。
· rows
rows列顯示MySQL相信它必須檢驗以執行查詢的行數。
·Extra
如果Extra列包括文字Only index，這意味著信息只用索引樹中的信息檢索出的。通常，這比掃描整個表要快。如果Extra列包括文字where used，它意味著一個WHERE子句將被用來限制哪些行與下一個表匹配或發向客戶。
通過相乘EXPLAIN輸出的rows行的所有值，你能得到一個關於一個聯結要多好的提示。這應該粗略地告訴你MySQL必須檢驗多少行以執行查詢。
例如，下面一個全連接：
mysql> EXPLAIN SELECT student.name From student,pet
-> WHERE student.name=pet.owner;
其結論為：
+---------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+------+---------+------+------+------------+
| student | ALL | NULL | NULL | NULL | NULL | 13 | |
| pet | ALL | NULL | NULL | NULL | NULL | 9 | where used |
+---------+------+---------------+------+---------+------+------+------------+
SELECT 查詢的速度
總的來說，當你想要使一個較慢的SELECT ... WHERE更快，檢查的第一件事情是你是否能增加一個索引。在不同表之間的所有引用通常應該用索引完成。你可以使用EXPLAIN來確定哪個索引用於一條SELECT語句。
一些一般的建議：
·為了幫助MySQL更好地優化查詢，在它已經裝載了相關數據後，在一個表上運行myisamchk --analyze。這為每一個更新一個值，指出有相同值地平均行數（當然，對唯一索引，這總是1。）
· 為了根據一個索引排序一個索引和數據，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序）。如果你有一個唯一索引，你想要根據該索引地次序讀取所有的記錄，這是使它更快的一個好方法。然而注意，這個排序沒有被最佳地編寫，並且對一個大表將花很長時間！
MySQL怎樣優化WHERE子句
where優化被放在SELECT中，因為他們最主要在那裡使用裡，但是同樣的優化被用於DELETE和UPDATE語句。
也要注意，本節是不完全的。MySQL確實作了許多優化而我們沒有時間全部記錄他們。
由MySQL實施的一些優化列在下面：
1、刪除不必要的括號：
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
2、常數調入：
(a-> b>5 AND b=c AND a=5
3、刪除常數條件(因常數調入所需)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
4、索引使用的常數表達式僅計算一次。
5、在一個單個表上的沒有一個WHERE的COUNT(*)直接從表中檢索信息。當僅使用一個表時，對任何NOT NULL表達式也這樣做。
6、無效常數表達式的早期檢測。MySQL快速檢測某些SELECT語句是不可能的並且不返回行。
7、如果你不使用GROUP BY或分組函數(COUNT()、MIN()……)，HAVING與WHERE合並。
8、為每個子聯結(sub join)，構造一個更簡單的WHERE以得到一個更快的WHERE計算並且也盡快跳過記錄。
9、所有常數的表在查詢中的任何其他表前被首先讀出。一個常數的表是：
·一個空表或一個有1行的表。
·與在一個UNIQUE索引、或一個PRIMARY KEY的WHERE子句一起使用的表，這裡所有的索引部分使用一個常數表達式並且索引部分被定義為NOT NULL。
所有下列的表用作常數表
mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
10、對聯結表的最好聯結組合是通過嘗試所有可能性來找到:(。如果所有在ORDER BY和GROUP BY的列來自同一個表，那麼當廉潔時，該表首先被選中。
11、如果有一個ORDER BY子句和一個不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是來自聯結隊列中的第一個表的其他表的列，創建一個臨時表。
12、如果你使用SQL_SMALL_RESULT，MySQL將使用一個在內存中的表。
13、因為DISTINCT被變換到在所有的列上的一個GROUP BY，DISTINCT與ORDER BY結合也將在許多情況下需要一張臨時表。
14、每個表的索引被查詢並且使用跨越少於30% 的行的索引。如果這樣的索引沒能找到，使用一個快速的表掃描。
15、在一些情況下，MySQL能從索引中讀出行，甚至不咨詢數據文件。如果索引使用的所有列是數字的，那麼只有索引樹被用來解答查詢。
16、在每個記錄被輸出前，那些不匹配HAVING子句的行被跳過。
下面是一些很快的查詢例子
mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;
下列查詢僅使用索引樹就可解決(假設索引列是數字的)：
mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;
下列查詢使用索引以排序順序檢索，不用一次另外的排序：
mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...
MySQL怎樣優化LEFT JOIN
在MySQL中，A LEFT JOIN B實現如下：
1、表B被設置為依賴於表A。
2、表A被設置為依賴於所有用在LEFT JOIN條件的表(除B外)。
3、所有LEFT JOIN條件被移到WHERE子句中。
4、進行所有標准的聯結優化，除了一個表總是在所有它依賴的表之後被讀取。如果有一個循環依賴，MySQL將發出一個錯誤。
5、進行所有標准的WHERE優化。
6、如果在A中有一行匹配WHERE子句，但是在B中沒有任何行匹配LEFT JOIN條件，那麼在B中生成所有列設置為NULL的一行。
7、如果你使用LEFT JOIN來找出在某些表中不存在的行並且在WHERE部分你有下列測試：column_name IS NULL，這裡column_name 被聲明為NOT NULL的列，那麼MySQL在它已經找到了匹配LEFT JOIN條件的一行後，將停止在更多的行後尋找(對一特定的鍵組合)。
MySQL怎樣優化LIMIT
在一些情況中，當你使用LIMIT #而不使用HAVING時，MySQL將以不同方式處理查詢。
1、如果你用LIMIT只選擇一些行，當MySQL一般比較喜歡做完整的表掃描時，它將在一些情況下使用索引。
2、如果你使用LIMIT #與ORDER BY，MySQL一旦找到了第一個 # 行，將結束排序而不是排序整個表。
3、當結合LIMIT #和DISTINCT時，MySQL一旦找到#個唯一的行，它將停止。
4、在一些情況下，一個GROUP BY能通過順序讀取鍵(或在鍵上做排序)來解決，並然後計算摘要直到鍵值改變。在這種情況下，LIMIT #將不計算任何不必要的GROUP。
5、只要MySQL已經發送了第一個#行到客戶，它將放棄查詢。
6、LIMIT 0將總是快速返回一個空集合。這對檢查查詢並且得到結果列的列類型是有用的。
7、臨時表的大小使用LIMIT #計算需要多少空間來解決查詢。
記錄轉載和修改的速度
很多時候關心的是優化 SELECT 查詢，因為它們是最常用的查詢，而且確定怎樣優化它們並不總是直截了當。相對來說，將數據裝入數據庫是直截了當的。然而，也存在可用來改善數據裝載操作效率的策略，其基本原理如下：
·成批裝載較單行裝載更快，因為在裝載每個記錄後，不需要刷新索引高速緩存；可在成批記錄裝入後才刷新。
·在表無索引時裝載比索引後裝載更快。如果有索引，不僅必須增加記錄到數據文件，而且還要修改每個索引以反映增加了的新記錄。
·較短的 SQL 語句比較長的 SQL 語句要快，因為它們涉及服務器方的分析較少，而且還因為將它們通過網絡從客戶機發送到服務器更快。
這些因素中有一些似乎微不足道（特別是最後一個因素），但如果要裝載大量的數據，即使是很小的因素也會產生很大的不同結果。
INSERT查詢的速度
插入一個記錄的時間由下列組成：
·連接：(3)
·發送查詢給服務器：(2)
·分析查詢：(2)
·插入記錄：（1 x 記錄大小）
·插入索引：（1 x 索引）
·關閉：(1)
這裡的數字有點與總體時間成正比。這不考慮打開表的初始開銷(它為每個並發運行的查詢做一次)。
表的大小以N log N (B 樹)的速度減慢索引的插入。
加快插入的一些方法：
·如果你同時從同一客戶插入很多行，使用多個值表的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。
·如果你從不同客戶插入很多行，你能通過使用INSERT DELAYED語句得到更高的速度。
·注意，用MyISAM，如果在表中沒有刪除的行，能在SELECT:s正在運行的同時插入行。
·當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍。
·當表有很多索引時，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列過程：
1、有選擇地用CREATE TABLE創建表。例如使用mysql或Perl-DBI。
2、執行FLUSH TABLES，或外殼命令mysqladmin flush-tables。
3、使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。這將從表中刪除所有索引的使用。
4、用LOAD DATA INFILE把數據插入到表中，這將不更新任何索引，因此很快。
5、如果你有myisampack並且想要壓縮表，在它上面運行myisampack。
6、用myisamchk -r -q /path/to/db/tbl_name再創建索引。這將在將它寫入磁盤前在內存中創建索引樹，並且它更快，因為避免大量磁盤尋道。結果索引樹也被完美地平衡。
7、執行FLUSH TABLES，或外殼命令mysqladmin flush-tables。
這個過程將被構造進在MySQL的某個未來版本的LOAD DATA INFILE。
·你可以鎖定你的表以加速插入
mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33); mysql> INSERT INTO a VALUES (8,26),(6,29); mysql> UNLOCK TABLES;
主要的速度差別是索引緩沖區僅被清洗到磁盤上一次，在所有INSERT語句完成後。一般有與有不同的INSERT語句那樣奪的索引緩沖區清洗。如果你能用一個單個語句插入所有的行，鎖定就不需要。鎖定也將降低多連接測試的整體時間，但是對某些線程最大等待時間將上升(因為他們等待鎖)。例如：
thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts
如果你不使用鎖定，2、3和4將在1和5前完成。如果你使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應該快大約40%。因為 INSERT, UPDATE和DELETE操作在MySQL中是很快的，通過為多於大約5次連續不斷地插入或更新一行的東西加鎖，你將獲得更好的整體性能。如果你做很多一行的插入，你可以做一個LOCK TABLES，偶爾隨後做一個UNLOCK TABLES(大約每1000行)以允許另外的線程存取表。這仍然將導致獲得好的性能。當然，LOAD DATA INFILE對裝載數據仍然是更快的。
為了對LOAD DATA INFILE和INSERT得到一些更快的速度，擴大關鍵字緩沖區。
UPDATE查詢的速度
更改查詢被優化為有一個寫開銷的一個SELECT查詢。寫速度依賴於被更新數據大小和被更新索引的數量。
使更改更快的另一個方法是推遲更改並且然後一行一行地做很多更改。如果你鎖定表，做一行一行地很多更改比一次做一個快。
注意，動態記錄格式的更改一個較長總長的記錄，可能切開記錄。因此如果你經常這樣做，時不時地OPTIMIZE TABLE是非常重要的。
DELETE查詢的速度
刪除一個記錄的時間精確地與索引數量成正比。為了更快速地刪除記錄，你可以增加索引緩存的大小。
從一個表刪除所有行比刪除行的一大部分也要得多。
索引對有效裝載數據的影響
如果表是索引的，則可利用批量插入（LOAD DATA 或多行的 INSERT 語句）來減少索引的開銷。這樣會最小化索引更新的影響，因為索引只需要在所有行處理過時才進行刷新，而不是在每行處理後就刷新。
·如果需要將大量數據裝入一個新表，應該創建該表且在未索引時裝載，裝載數據後才創建索引，這樣做較快。一次創建索引（而不是每行修改一次索引）較快。
·如果在裝載之前刪除或禁用索引，裝入數據後再重新創建或啟用索引可能使裝載更快。
·如果想對數據裝載使用刪除或禁用策略，一定要做一些實驗，看這樣做是否值得（如果將少量數據裝入一個大表中，重建和索引所花費的時間可能比裝載數據的時間還要長）。
可用 DROP INDEX 和 CREATE INDEX 來刪除和重建索引。
另一種可供選擇的方法是利用 myisamchk 或 isamchk 禁用和啟用索引。這需要在 MySQL 服務器主機上有一個帳戶，並對表文件有寫入權。為了禁用表索引，可進入相應的數據庫目錄，執行下列命令之一：
shell>myisamchk --keys-used=0 tbl_name shell>isamchk --keys-used=0 tbl_name
對具有 .MYI 擴展名的索引文件的 MyISAM 表使用 myisamchk，對具有 .ISM 擴展名的索引文件的 ISAM 表使用 isamchk。在向表中裝入數據後，按如下激活索引：
shell>myisamchk --recover --quick --keys-used=0 tbl_name shell>isamchk --recover --quick --keys-used=0 tbl_name
n 為表具有的索引數目。可用 --description 選項調用相應的實用程序得出這個值：
shell>myisamchk --discription tbl_name $isamchk --discription tbl_name
如果決定使用索引禁用和激活，應該使用第13章中介紹的表修復鎖定協議以阻止服務器同時更改鎖（雖然此時不對表進行修復，但要對它像表修復過程一樣進行修改，因此需要使用相同的鎖定協議）。
上述數據裝載原理也適用於與需要執行不同操作的客戶機有關的固定查詢。例如，一般希望避免在頻繁更新的表上長時間運行 SELECT 查詢。長時間運行 SELECT 查詢會產生大量爭用，並降低寫入程序的性能。一種可能的解決方法為，如果執行寫入的主要是 INSERT 操作，那麼先將記錄存入一個臨時表，然後定期地將這些記錄加入主表中。如果需要立即訪問新記錄，這不是一個可行的方法。但只要能在一個較短的時間內不訪問它們，就可以使用這個方法。使用臨時表有兩個方面的好處。首先，它減少了與主表上 SELECT 查詢語句的爭用，因此，執行更快。其次，從臨時表將記錄裝入主表的總時間較分別裝載記錄的總時間少；相應的索引高速緩存只需在每個批量裝載結束時進行刷新，而不是在每行裝載後刷新。
這個策略的一個應用是進入 Web 服務器的Web 頁訪問 MySQL 數據庫。在此情形下，可能沒有保證記錄立即進入主表的較高權限。
如果數據並不完全是那種在系統非正常關閉事件中插入的單個記錄，那麼減少索引刷新的另一策略是使用 MyISAM 表的 DELAYED_KEY_WRITE 表創建選項（如果將 MySQL 用於某些數據錄入工作時可能會出現這種情況）。此選項使索引高速緩存只偶爾刷新，而不是在每次插入後都要刷新。
如果希望在服務器范圍內利用延遲索引刷新，只要利用 --delayed-key-write 選項啟動 mysqld 即可。在此情形下，索引塊寫操作延遲到必須刷新塊以便為其他索引值騰出空間為止，或延遲到執行了一個 flush-tables 命令後，或延遲到該索引表關閉。
