MySQL 最佳化（四）

文章目錄

• 7.2.11 如何避免全表掃描
• 7.2.15 其他最佳化點子

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 t1Where 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 演算法：

1. 根據索引鍵讀取記錄，或者掃描資料表。那些無法匹配 Where 分句的記錄都會被略過。

    

    

2. 在緩衝中每條記錄都用一個‘對’儲存了2個值（索引鍵及記錄指標）。緩衝的大小依據系統變數 sort_buffer_size 的值而定。

    

    

3. 當緩衝慢了時，就運行 qsort（快速排序）並將結果儲存在臨時檔案中。將儲存的塊指標儲存起來（如果所有的‘對’值都能儲存在緩衝中，就無需建立臨時檔案了）。

    

    

4. 執行上面的操作，直到所有的記錄都讀取出來了。

    

    

5. 做一次多重合并，將多達 MERGEBUFF（7）個地區的塊儲存在另一個臨時檔案中。重複這個操作，直到所有在第一個檔案的塊都放到第二個檔案了。

    

    

6. 重複以上操作，直到剩餘的塊數量小於 MERGEBUFF2 (15)。

    

    

7. 在最後一次多重合并時，只有記錄的指標（排序索引鍵的最後部分）寫到結果檔案中去。

    

    

8. 通過讀取結果檔案中的記錄指標來按序讀取記錄。想要最佳化這個操作，MySQL將記錄指標讀取放到一個大的塊裡，並且使用它來按序讀取記錄，將記錄放到緩衝中。緩衝的大小由系統變數 read_rnd_buffer_size 的值而定。這個步驟的代碼在源檔案 `sql/records.cc' 中。

 

這個逼近演算法的一個問題是，資料庫讀取了2次記錄：一次是估算 Where 分句時，第二次是排序時。儘管第一次都成功讀取記錄了（例如，做了一次全表掃描），第二次是隨機的讀取（索引鍵已經排好序了，但是記錄並沒有）。

在MySQL 4.1 及更新版本中，filesort 最佳化演算法用於記錄中不只包括索引索引值和記錄的位置，還包括查詢中要求的欄位。這麼做避免了需要2次讀取記錄。改進的 filesort 演算法做法大致如下：

 

 

1. 跟以前一樣，讀取匹配 Where 分句的記錄。

    

    

2. 相對於每個記錄，都記錄了一個對應的；‘元組’資訊資訊，包括索引索引值、記錄位置、以及查詢中所需要的所有欄位。

    

    

3. 根據索引鍵對‘元組’資訊進行排序。

    

    

4. 按序讀取記錄，不過是從已經排序過的‘元組’列表中讀取記錄，而非從資料表中再讀取一次。

 

使用改進後的 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 fooSelect 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 在資料表有大量索引的情況下啟動並執行更快。步驟如下：
  1. 用 Create TABLE 隨便建立一個表。

      

      

  2. 執行 FLUSH TABLES 語句或 mysqladmin flush-tables 命令。

      

      

  3. 執行 myisamchk --keys-used=0 -rq /path/to/db/tbl_name 命令，刪掉資料表的所有索引。

      

      

  4. 執行 LOAD DATA INFILE，資料插入到表中，由於無需更新表索引，因此這將非常快。

      

      

  5. 如果將來只是讀取改表，運行 myisampack 讓資料表變得更小點。詳情查看"15.1.3.3 Compressed Table Characteristics"。

      

      

  6. 運行 myisamchk -r -q /path/to/db/tbl_name 重建索引。建立的索引樹在寫入磁碟前先儲存在記憶體中，這省去了磁碟搜尋，因此速度快多了。重建後的索引樹分布非常均衡。
  7. 執行 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 的話也不會丟失任何東西，因為索引總是可以從資料中重建）。