7.2.9 MySQL 如何優化 ORDER BY
在一些情況下，MySQL可以直接使用索引來滿足一個 ORDER BY 或 GROUP BY 子句而無需做額外的排序。
盡管 ORDER BY 不是和索引的順序准確匹配，索引還是可以被用到，只要不用的索引部分和所有的額外的 ORDER BY 字段在 WHERE 子句中都被包括了。下列的幾個查詢都會使用索引來解決 ORDER BY 或 GROUP BY 部分：
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
在另一些情況下，MySQL無法使用索引來滿足 ORDER BY，盡管它會使用索引來找到記錄來匹配 WHERE 子句。這些情況如下：
對不同的索引鍵做 ORDER BY ：
SELECT * FROM t1 ORDER BY key1, key2;
在非連續的索引鍵部分上做 ORDER BY：
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
同時使用了 ASC 和 DESC：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
用於搜索記錄的索引鍵和做 ORDER BY 的不是同一個：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
有很多表一起做連接，而且讀取的記錄中在 ORDER BY 中的字段都不全是來自第一個非常數的表中（也就是說，在 EXPLAIN 分析的結果中的第一個表的連接類型不是 const）。
使用了不同的 ORDER BY 和 GROUP BY 表達式。
表索引中的記錄不是按序存儲。例如，HASH 和 HEAP 表就是這樣。
通過執行 EXPLAIN SELECT ... ORDER BY，就知道MySQL是否在查詢中使用了索引。如果 Extra 字段的值是 Using filesort，則說明MySQL無法使用索引。詳情請看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。
當必須對結果進行排序時，MySQL 4.1 以前它使用了以下 filesort 算法：
根據索引鍵讀取記錄，或者掃描數據表。那些無法匹配 WHERE 分句的記錄都會被略過。
在緩沖中每條記錄都用一個‘對’存儲了2個值（索引鍵及記錄指針）。緩沖的大小依據系統變量 sort_buffer_size 的值而定。
當緩沖慢了時，就運行 qsort（快速排序）並將結果存儲在臨時文件中。將存儲的塊指針保存起來（如果所有的‘對’值都能保存在緩沖中，就無需創建臨時文件了）。
執行上面的操作，直到所有的記錄都讀取出來了。
做一次多重合並，將多達 MERGEBUFF（7）個區域的塊保存在另一個臨時文件中。重復這個操作，直到所有在第一個文件的塊都放到第二個文件了。
重復以上操作，直到剩余的塊數量小於 MERGEBUFF2 (15)。
在最後一次多重合並時，只有記錄的指針（排序索引鍵的最後部分）寫到結果文件中去。
通過讀取結果文件中的記錄指針來按序讀取記錄。想要優化這個操作，MySQL將記錄指針讀取放到一個大的塊裡，並且使用它來按序讀取記錄，將記錄放到緩沖中。緩沖的大小由系統變量 read_rnd_buffer_size 的值而定。這個步驟的代碼在源文件 `sql/records.cc' 中。
這個逼近算法的一個問題是，數據庫讀取了2次記錄：一次是估算 WHERE 分句時，第二次是排序時。盡管第一次都成功讀取記錄了（例如，做了一次全表掃描），第二次是隨機的讀取（索引鍵已經排好序了，但是記錄並沒有）。
在MySQL 4.1 及更新版本中，filesort 優化算法用於記錄中不只包括索引鍵值和記錄的位置，還包括查詢中要求的字段。這麼做避免了需要2次讀取記錄。改進的 filesort 算法做法大致如下：
跟以前一樣，讀取匹配 WHERE 分句的記錄。
相對於每個記錄，都記錄了一個對應的；‘元組’信息信息，包括索引鍵值、記錄位置、以及查詢中所需要的所有字段。
根據索引鍵對‘元組’信息進行排序。
按序讀取記錄，不過是從已經排序過的‘元組’列表中讀取記錄，而非從數據表中再讀取一次。
使用改進後的 filesort 算法相比原來的，‘元組’比‘對’需要占用更長的空間，它們很少正好適合放在排序緩沖中（緩沖的大小是由 sort_buffer_size 的值決定的）。因此，這就可能需要有更多的I/O操作，導致改進的算法更慢。為了避免使之變慢，這種優化方法只用於排序‘元組’中額外的字段的大小總和超過系統變量 max_length_for_sort_data 的情況（這個變量的值設置太高的一個表象就是高磁盤負載低CPU負載）。
想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非額外的排序過程。如果不能使用索引，可以試著遵循以下策略：
增加 sort_buffer_size 的值。
增加 read_rnd_buffer_size 的值。
修改 tmpdir，讓它指向一個有很多剩余空間的專用文件系統。如果使用MySQL 4.1或更新，這個選項允許有多個路徑用循環的格式。各個路徑之間在 Unix 上用冒號（':'）分隔開來，在 Windows，NetWare以及OS/2 上用分號（';'）。可以利用這個特性將負載平均分攤給幾個目錄。注意：這些路徑必須是分布在不同物理磁盤上的目錄，而非在同一個物理磁盤上的不同目錄。
默認情況下，MySQL也會對所有的 GROUP BY col1, col2, ... 查詢做排序，跟 ORDER BY col1, col2, ... 查詢一樣。如果顯式地包含一個有同樣字段列表的 ORDER BY 分句，MySQL優化它的時候並不會損失速度，因為排序總是會發生。如果一個查詢中包括 GROUP BY，但是想要避免對結果排序的開銷，可以通過使用 ORDER BY NULL 來取消排序。例如：
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
7.2.10 MySQL 如何優化 LIMIT
在一些情況下，MySQL在碰到一個使用 LIMIT row_count 但沒使用 HAVING
的查詢時會做不同的處理：
如果只是用 LIMIT 來取得很少的一些記錄， MySQL 有時會使用索引，但是更通常的情況是做一個全表掃描。
如果 LIMIT row_count 和 ORDER BY 一起使用，則MySQL在找到 row_count 條記錄後就會停止排序了，而非對整個表進行排序。
當 LIMIT row_count 和 DISTINCT 一起聯合起來時，MySQL在找到 row_count 條唯一記錄後就不再搜索了。
在某些情況下， GROUP BY 可以通過按照順序讀取索引鍵來實現（或者在索引鍵上做排序）並且計算累計信息直到索引鍵改變了。在這種情況下，LIMIT row_count 不會計算任何非必須的 GROUP BY 值。
一旦MySQL將請求的記錄全數發送給客戶端後，它就中止查詢除非使用了 SQL_CALC_FOUND_ROWS。
LIMIT 0 總是返回一個空的結果集。這對於檢查查詢或者取得結果字段的類型非常有用。
當服務器使用臨時表來處理查詢，則 LIMIT row_count 可以用來計算需要多少空間。
7.2.11 如何避免全表掃描
如果MySQL需要做一次全表掃描來處理查詢時，在 EXPLAIN 的結果中 type 字段的值是 ALL。在以下幾種條件下，MySQL就會做全表掃描：
數據表是在太小了，做一次全表掃描比做索引鍵的查找來得快多了。當表的記錄總數小於10且記錄長度比較短時通常這麼做。
沒有合適用於 ON 或 WHERE 分句的索引字段。
讓索引字段和常量值比較，MySQL已經計算（基於索引樹）到常量覆蓋了數據表的很大部分，因此做全表掃描應該會來得更快。詳情請看"7.2.4 How MySQL Optimizes WHERE Clauses"。
通過其他字段使用了一個基數很小（很多記錄匹配索引鍵值）的索引鍵。這種情況下，MySQL認為使用索引鍵需要大量查找，還不如全表掃描來得更快。
對於小表來說，全表掃描通常更合適。但是對大表來說，嘗試使用以下技術來避免讓優化程序錯誤地選擇全表掃描：
執行 ANALYZE TABLE tbl_name 更新要掃描的表的索引鍵分布。詳情請看"14.5.2.1 ANALYZE TABLE Syntax"。
使用 FORCE INDEX 告訴MySQL，做全表掃描的話會比利用給定的索引更浪費資源。詳情請看"14.1.7 SELECT Syntax"。
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
啟動 MySQLd 時使用參數 --max-seeks-for-key=1000 或者執行 SET max_seeks_for_key=1000 來告訴優化程序，所有的索引都不會導致超過1000次的索引搜索。請查看章節"5.2.3 Server System Variables"。
7.2.12 加速 INSERT
插入一條記錄花費的時間由以下幾個因素決定，後面的數字大致表示影響的比例：
連接：（3）
發送查詢給服務器：（2）
解析查詢：（2）
插入記錄：（1 x 記錄大小）
插入索引：（1 x 索引數量）
關閉：（1）
這裡並沒有考慮初始化時打開數據表的開銷，因為每次運行查詢只會做這麼一次。
如果是 B-tree 索引的話，隨著索引數量的增加，插入記錄的速度以 log N 的比例下降。
可以使用以下幾種方法來提高插入速度：
如果要在同一個客戶端在同一時間內插入很多記錄，可以使用 INSERT 語句附帶有多個 VALUES 值。這種做法比使用單一值的 INSERT 語句快多了（在一些情況下比較快）。如果是往一個非空的數據表裡增加記錄，可以調整變量 bulk_insert_buffer_size 的值使之更快。詳情請看"5.2.3 Server System Variables"。
如果要從不同的客戶端中插入大量記錄，使用 INSERT DELAYED 語句也可以提高速度。詳情請看"14.1.4 INSERT Syntax"。
對 MyISAM 而言，可以在 SELECT 語句正在運行時插入記錄，只要這時候沒有正在刪除記錄。
想要將一個文本文件加載到數據表中，可以使用 LOAD DATA INFILE。這通常是使用大量 INSERT 語句的20倍。詳情請看"14.1.5 LOAD DATA INFILE Syntax"。
通過一些額外的工作，就可能讓 LOAD DATA INFILE 在數據表有大量索引的情況下運行的更快。步驟如下：
用 CREATE TABLE 隨便創建一個表。
執行 FLUSH TABLES 語句或 MySQLadmin flush-tables 命令。
執行 myisamchk --keys-used=0 -rq /path/to/db/tbl_name 命令，刪掉數據表的所有索引。
執行 LOAD DATA INFILE，數據插入到表中，由於無需更新表索引，因此這將非常快。
如果將來只是讀取改表，運行 myisampack 讓數據表變得更小點。詳情查看"15.1.3.3 Compressed Table Characteristics"。
運行 myisamchk -r -q /path/to/db/tbl_name 重建索引。創建的索引樹在寫入磁盤前先保存在內存中，這省去了磁盤搜索，因此速度快多了。重建後的索引樹分布非常均衡。
執行 FLUSH TABLES 語句或 MySQLadmin flush-tables 命令。
注意，LOAD DATA INFILE 將數據插入一個空表時，也會做前接優化；主要的不同在於：運行 myisamchk 會分配更多的臨時內存用於創建索引，而執行 LOAD DATA INFILE 命令則是讓數據庫服務器分配內存用於重建索引。從 MySQL 4.0 起，可以運行 ALTER TABLE tbl_name DISABLE KEYS 來代替 myisamchk --keys-used=0 -rq /path/to/db/tbl_name，運行 ALTER TABLE tbl_name ENABLE KEYS 代替 myisamchk -r -q /path/to/db/tbl_name。這麼做就可以省去 FLUSH TABLES 步驟。
可以在鎖表後，一起執行幾個語句來加速 INSERT 操作：
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
這對性能提高的好處在於：直到所有的 INSERT 語句都完成之後，索引緩存一次性刷新到磁盤中。通常情況是，多有少次 INSERT 語句就會有多數次索引緩存刷新到磁盤中的開銷。如果能在一個語句中一次性插入多個值的話，顯示的鎖表操作也就沒必要了。對事務表而言，用 BEGIN/COMMIT 代替 LOCK TABLES 來提高速度。鎖表也回降低多次連接測試的總時間，盡管每個獨立連接為了等待鎖的最大等待時間也會增加。例如：
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果沒有鎖表，則連接2，3，4會在1，5之前就做完了。如果鎖表了，則連接2，3，4可能在1，5之後才能完成，但是總時間可能只需要40%。MySQL的 INSERT, UPDATE, DELETE 操作都非常快，不過在一個語句中如果有超過5個插入或者更新時最好加鎖以得到更好的性能。如果要一次性做很多個插入，最好是在每個循環（大約1000次）的前後加上 LOCK TABLES 和 UNLOCK TABLES，從而讓其他進程也能訪問數據表;這麼做性能依然不錯。INSERT 總是比 LOAD DATA INFILE 插入數據來得慢，因為二者的實現策略有著分明的不同。
想要讓 MyISAM 表更快，在 LOAD DATA
INFILE 和 INSERT 時都可以增加系統變量 key_buffer_size 的值，詳情請看"7.5.2 Tuning Server Parameters"。
7.2.13 加速 UPDATE
UPDATE 語句的優化和 SELECT 一樣，只不過它多了額外的寫入開銷。寫入的開銷取決於要更新的記錄數以及索引數。如果索引沒有發生變化，則就無需更新。
另一個提高更新速度的辦法是推遲更新並且把很多次更新放在後面一起做。如果鎖表了，那麼同時做很多次更新比分別做更新來得快多了。
注意，如果是在 MyISAM 表中使用了動態的記錄格式，那麼記錄被更新為更長之後就可能會被拆分。如果經常做這個，那麼偶爾做一次 OPTIMIZE TABLE 就顯得非常重要了。詳情請看"14.5.2.5 OPTIMIZE TABLE Syntax"。
7.2.14 加速 DELETE
刪除單個記錄的時間和它的索引個數幾乎成正比。想更快地刪除記錄，可以增加索引鍵的緩存。詳情請看"7.5.2 Tuning Server Parameters"。
如果想要刪除數據表的所有記錄，請使用 TRUNCATE TABLE tbl_name 而不是 DELETE FROM tbl_name。詳情請看"14.1.9 TRUNCATE Syntax"。
7.2.15 其他優化點子
本章節列出了一些改善查詢處理速度的其他點子：
使用永久連接到數據庫，避免連接的開銷。如果需要初始化很多連接，而又不能用永久連接，那麼可以修改變量 thread_cache_size 的值，詳情請看"7.5.2 Tuning Server Parameters"。
總是檢查查詢是否利用了表中已有的索引。在MySQL中，可以用 EXPLAIN 語句來分析。詳情請看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。
盡量不要在經常需要更新的 MyISAM 表上用太過復雜的 SELECT 語句，這是為了避免在讀和寫之間爭奪鎖。
在 MyISAM 表中，如果沒有正在刪除記錄，則可以在其他查詢正在讀取數據的同時插入記錄。如果這種情況十分重要，那麼就要盡量在表沒有刪除記錄時才使用表。另一個可能的辦法就是在刪除一大堆記錄之後執行 OPTIMIZE TABLE 語句。
如果總是需要按照 expr1, expr2, ... 的順序取得記錄，那麼請使用 ALTER TABLE ... ORDER BY expr1, expr2, ... 修改表。通過這種方法擴充修改表之後，就可能獲得更高的性能表現。
在一些情況下，讓一個字段類型是 ``hashed`` ，它基於其他字段信息。如果這個字段比較短而且基本上都是唯一值的話，那麼就可能會比在幾個字段上使用一個大索引來得更快，很簡單的就能使用這樣的額外字段，如下：
SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(col1,col2))
AND col1='constant' AND col2='constant';
如果 MyISAM 表經常大量修改，那麼要盡量避免修改所有的變長字段（VARCHAR, BLOB，TEXT）。盡管表中只有一個變長字段，它也會采用動態記錄格式的。詳情請看"15 MySQL Storage Engines and Table Types"。
通常情況下，當數據表記錄變 ``大`` 之後，將表拆分成幾個不同的表並沒有多大用處。訪問一條記錄是最大的性能點在於磁盤搜索時找到記錄的第一個字節上。只要找到記錄的位置後，現在的大部分磁盤對於大部分的應用程序來說都能很快的讀取到記錄。將 MyISAM 表拆分成多個唯一有關系的情況是，數據表中動態格式的字段（見上）就可以被修改成固定大小的記錄，或者需要頻繁的掃描表，但是卻不需要讀取出大部分的字段。詳情請看"15 MySQL Storage Engines and Table Types"。
如果需要頻繁的對一個表做基於很多字段信息的統計信息的話，那麼可能新建一個表來存儲這些實時更新的統計結果會更好。類似下面的更新就會非常快了：
UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;如果只需要表級鎖（多個讀/一個寫），那麼采用 MyISAM 存儲引擎就非常重要了，例如 MyISAM 和 ISAM 表。這在很多的數據庫中也會有不錯的性能表現，因為行級鎖管理程序在這種情況下也基本上沒什麼用。
如果需要從很大的日志表中搜集統計信息的話，可以用摘要表來代替掃描整個日志表。維護摘要表比保持 ``實時`` 的統計信息來得更快。當事情發生變化時（比如商業決策），重新建裡摘要表比修改運營中的應用程序快多了。
如果可能，最好是分類報告 ``實時`` 還是 ``統計`` 的，報告所需要的數據只需要來自摘要表，摘要表的信息則是周期的從實時數據中產生。
應該認識到一個優點就是字段有默認值。當要插入的值和默認值不一致時才需要明確指定。這就省去了MySQL需要來提高插入速度這步了。
在一些情況下，將數據組裝存儲在 BLOB 類型字段中更方便。那麼在應用程序中就需要增加額外的命令來組裝和拆開 BLOB 字段中的值，不過這麼做在一些時候就可以節省很多存儲開銷。這在數據無需遵從 記錄-和-字段 格式的表結構是很實用。
通常地，應該保存所有的冗余數據（在數據庫原理中叫做"第三范式"）。然而，為了能取得更高的效率復制一些信息或者創建摘要表也是劃算的。
存儲過程或者 UDFs（用戶定義函數） 的方式在執行一些任務時可能性能更高。盡管如此，當數據庫不支持這些特性時，還是有其他的替代方法可以達到目的，即使它們有點慢。
可以從查詢緩存或應答中取得結果，然後將很多次的插入及更新操作放在一起做。如果數據庫支持表鎖（如MySQL和Oracle），那麼這就可以確保索引緩存在所有的更新操作之後只需要刷新一次。
當不需要直到數據什麼時候寫入表中時，可以用 INSERT DELAYED。這就會提高速度，因為多條記錄同時在一起做一次磁盤寫入操作。
當想讓 SELECT 語句的優先級比插入操作還高時，用 INSERT LOW_PRIORITY。
用 SELECT HIGH_PRIORITY 來使檢索記錄跳過隊列，也就是說即使有其他客戶端正要寫入數據，也會先讓 SELECT 執行完。
在一條 INSERT 語句中采用多重記錄插入格式（很多數據庫都支持）。
用 LOAD DATA INFILE 來導入大量數據，這比 INSERT 快。
用 AUTO_INCREMENT 字段來生成唯一值。
定期執行 OPTIMIZE TABLE 防止使用動態記錄格式的 MyISAM 表產生碎片。詳情請看"15.1.3 MyISAM Table Storage Formats"。
采用 HEAP 表，它可能會提高速度。詳情請看"15.1.3 MyISAM Table Storage Formats"。
正常的WEB服務器配置中，圖片文件最好以文件方式存儲，只在數據庫中保存文件的索引信息。這麼做的原因是，通常情況下WEB服務器對於文件的緩存總是做的比數據庫來得好，因此使用文件存儲會讓系統更容易變得更快。
對於頻繁訪問的不是很重要的數據，可以保存在內存表中，例如對那些web客戶端不能保存cookIEs時用於保存最後一次顯示的標題等信息。
在不同表中值相同的字段應該將它們聲明為一樣的類型。在 MySQL 3.23 之前，不這麼做的話在表連接時就會比較慢。讓字段名盡可能簡單，例如，在一個叫做 customer 的表中，用 name 來代替 customer_name 作為字段名。為了讓字段名在其他數據庫系統中也能移植，應該保持在18個字符長度以內。
如果需要真正的高速，建議看看各種數據庫服務器支持的底層數據存儲接口之間的區別。例如，通過直接訪問MySQL的 MyISAM 存儲引擎，會比通過其他的SQL接口快2-5倍。這要求數據必須和應用程序在同一個服務器上，並且它通常只被一個進程訪問（因為外部文件鎖確實慢）。只用一個進程就可以消除在MySQL服務器上引入底層的 MyISAM 指令引發的問題了（這容易獲得更高性能，如果需要的話）。由於數據庫接口設計的比較細心，就很容易支持這種優化方式了。
如果使用數字型數據的話，在很多情況下想要訪問數據庫（使用在線連接）的信息會比采用文本文件來得快。由於數字型信息相比文本文件在數據庫中存儲的更加緊湊，因此訪問時只需要更少的磁盤搜索。而且在應用程序中也可以節省代碼，因為無需解析文本文件以找到對應的行和字段。
數據庫復制對一些操作會有性能上的益處。可以將客戶端從多個復制服務器上取得數據，這就能將負載分攤了。為了避免備份數據時會讓主服務器變慢，還可以將備份放在從服務器上。詳情請看"6 Replication in MySQL"。
定義 MyISAM 表時增加選項 DELAY_KEY_WRITE=1，這樣的話就會另索引更新更快，因為只有等到數據表關閉了才會刷新磁盤。不過缺點是可能會在數據表還打開時服務器被殺死，可以使用參數 --myisam-recover 來保證數據的安全，或者在數據庫重啟前運行 myisamchk 命令（盡管如此，在這種情況下，使用 DELAY_KEY_WRITE 的話也不會丟失任何東西，因為索引總是可以從數據中重新生成）。
