Effective MySQL之SQL語句最優化--索引 1 兩個索引取並集組合 [sql] ALTER TABLE album ADD INDEX name_release (name,first_released); EXPLAIN SELECT a.name, ar.name, a.first_released FROM album a INNER JOIN artist ar USING (artist_id) WHERE a.name = 'Greatest Hits' ORDER BY a.first_released; mysql> EXPLAIN SELECT a.name, ar.name, -> a.first_released -> FROM album a -> INNER JOIN artist ar USING (artist_id) -> WHERE a.name = 'Greatest Hits' -> ORDER BY a.first_released; +----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | a | ref | name_release,name_2,name_part2 | name_release | 257 | const | 659 | Using where | | 1 | SIMPLE | ar | eq_ref | PRIMARY | PRIMARY | 4 | union.a.artist_id | 1 | | +----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+ 2 rows in set (0.00 sec) ALTER TABLE album ADD INDEX name_release (name,first_released); MySQL 可以在WHERE、ORDER BY 以及GROUP BY 列中使用索引；然而，一般來說MySQL 在一個表上只選擇一個索引。 從MySQL 5.0 開始，在個別例外情況中優化器可能會使用一個以上的索引，但是在早期的版本中這樣做會導致查詢運行更加緩慢。 2 兩個索引取並集 第一種: 最常見的索引合並的操作是兩個索引取並集，當用戶對兩個有很 高基數的索引執行OR 操作時會出現這種這種索引合並操作。請 看下面的示例： [sql] SET @@session.optimizer_switch='index_merge_intersection=on'; EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen' OR founded = 1942\G mysql> EXPLAIN SELECT artist_id, name -> FROM artist -> WHERE name = 'Queen' -> OR founded = 1942; +----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+ | 1 | SIMPLE | artist | index_merge | name,founded | name,founded | 257,2 | NULL | 499 | Using union(name,founded); Using where | +----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+ 1 row in set (0.01 sec) Extra: Using union(name,founded); 采用了union的聯合索引模式,取合集. 注意 在MySQL 5.1 中首次引入了optimizer_switch 系統變量，可以 通過啟用或禁用這個變量來控制這些附加選項。 2 第二種類型的索引合並是對兩個有少量唯一值的索引取交集，如下所示： [sql] SET @@session.optimizer_switch='index_merge_intersection=on'; EXPLAIN SELECT artist_id, name FROM artist WHERE type = 'Band' AND founded = 1942; mysql> SET @@session.optimizer_switch='index_merge_intersection=on'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> EXPLAIN SELECT artist_id, name -> FROM artist -> WHERE type = 'Band' -> AND founded = 1942; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | artist | ref | founded | founded | 2 | const | 498 | Using where | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) Extra: Using intersect(founded,type); Using where 這裡由於是AND,所以只需要取2個索引中最高效的那個索引來進行遍歷取值. 3 第三種類型的索引合並操作和對兩個索引取並集比較類似，但它需要先經過排序： [sql] EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen' OR (founded BETWEEN 1942 AND 1950); mysql> EXPLAIN SELECT artist_id, name -> FROM artist -> WHERE name = 'Queen' -> OR (founded BETWEEN 1942 AND 1950); +----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+ | 1 | SIMPLE | artist | index_merge | name,founded | name,founded | 257,2 | NULL | 5900 | Using sort_union(name,founded); Using where | +----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+ 1 row in set (0.00 sec) 4 數個索引合並的情況 在創建這些示例的過程中，還發現一種以前在任何客戶端的查詢中未曾出現過的新情況。以下是三個索引合並的示例： [sql] mysql> EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen' OR (type = 'Band' AND founded = '1942'); ..... mysql> EXPLAIN SELECT artist_id, name -> FROM artist -> WHERE name = 'Queen' -> OR (type = 'Band' AND founded = '1942'); +----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+ | 1 | SIMPLE | artist | index_merge | name,founded | name,founded | 257,2 | NULL | 499 | Using union(name,founded); Using where | +----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+ 1 row in set (0.00 sec) 技巧 應該經常評估多列索引是否比讓優化器合並索列效率更高。多個單列索引和多個多列索引到底哪個更有優勢？這個問題 只有結合特定應用程序的查詢類型和查詢容量才能給出答案。在各種不同的查詢條件下，將一些高基數列上的那些單列索引進行 索引合並能夠帶來很高的靈活性。數據庫寫操作的性能參考因素也同樣會影響到獲取數據的最優的數據訪問路徑。 5 創建更好的MySQL 索引 主要用的比較多的2個特殊的索引 通過使用索引，查詢的執行時間可以從秒的數量級減少到毫秒數量級，這樣的性能改進能夠為你的應用程序的性能帶來飛躍。 合理的調整你的索引對優化來說是非常重要的，尤其是對於高吞吐量的應用程序。即使對執行時間的改進僅僅是數毫秒，但對於 一個每秒執行1000 次的查詢來說這也是非常有意義的性能提升。例如，把一個原本需要20 毫秒執行的每秒運行1 000 次的查詢的 執行之間縮短4 毫秒，這對於優化SQL 語句來說是至關重要的。我們將使用第4 章介紹的方法創建多列索引，並在這一基礎 上創建更好的覆蓋索引。 ● 創建覆蓋索引 ALTER TABLE artist DROP INDEX founded, ADD INDEX founded_name (founded,name); 在InnoDB 中，主碼的值會被附加在非主碼索引的每個對應記錄後面，因此沒有必要在非主碼索引中指定主碼。 這一重要特性意味著InnoDB 引擎中所有非主碼索引都隱含主碼列了。並且對於那些從MyISAM 存儲引擎轉換過來的表，通常會 在它們InnoDB 表索引中將主碼添加為最後一個元素。 當QEP 在Extra 列中顯示Using index 時，這並不意味著在訪 問底層表數據時使用到了索引，這表示只有這個索引才是滿足查詢所有要求的。這種索引可以為大型查詢或者頻繁執行的查詢帶 來顯著的性能提升，它被稱為覆蓋索引。覆蓋索引得名於它滿足了查詢中給定表用到的所有的列。想 要創建一個覆蓋索引，這個索引必須包含指定表上包括WHERE語句、ORDER BY 語句、GROUP BY 語句(如果有的話)以及 SELECT 語句中的所有列。 [Comment]:隨著數據容量的增加，尤其是超過內存和磁盤最大容量的時候，為一個大型列創建索引可能 會對系統整體性能有影響。覆蓋索引對於那些使用了很多較小長度的主碼和外鍵約束的大型規范化模式來說是理想的優化方式。 ● 創建局部列的索引 [sql] ALTER TABLE artist DROP INDEX name, ADD INDEX name_part(name(20)); 這裡主要考慮的是如何減小索引占用的空間。一個更小的索引意味著更少的磁盤I/O 開銷，而這又意味著能更快地訪問到需 要訪問的行，尤其是當磁盤上的索引和數據列遠大於可用的系統內存時。這樣獲得的性能改進將會超過一個非唯一的並且擁有低 基數的索引帶來的影響。局部索引是否適用取決於數據是如何訪問的。之前介紹覆蓋索引時，你可以看到記錄一個短小版本的name 列不會對執行過 的SQL 語句有任何好處。最大的益處只有當你在被索引的列上添加限制條件時才能體現出來。 [sql] EXPLAIN SELECT artist_id,name,founded FROM artist WHERE name LIKE 'Queen%'; mysql> EXPLAIN SELECT artist_id,name,founded -> FROM artist -> WHERE name LIKE 'Queen%'; +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | artist | range | name | name | 257 | NULL | 93 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) 在這個示例中，Extra後面沒有出現Using Index，所以在索引中記錄全名並沒有帶來額外的益處。 而所提供的局部列索引滿足了WHERE 條件。如何選擇合適的長度取決於數據的分布以及訪問路徑。目前沒有准確的方法計算索 引的恰當長度。因此對給定范圍的列長度內的唯一值數目的比較 是必不可少的。 count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93條記錄，而SELECT count(*) FROM artist;有577983條記錄，按照普遍的情況，可以走索引，難道是name(20)的20定義的太長了？ [sql] ALTER TABLE artist DROP INDEX name_part, ADD INDEX name_part2(name(10)); mysql> ALTER TABLE artist -> DROP INDEX name_part, -> ADD INDEX name_part2(name(10)); Query OK, 0 rows affected (3.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT artist_id,name,founded -> FROM artist -> WHERE name LIKE 'Queen%'; +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | artist | range | name_part2 | name_part2 | 12 | NULL | 93 | Using where | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) 看結果，再用name(5) 試試看。 mysql> ALTER TABLE artist -> DROP INDEX name_part2, -> ADD INDEX name_part3(name(5)); Query OK, 0 rows affected (3.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT artist_id,name,founded -> FROM artist -> WHERE name LIKE 'Queen%'; +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | artist | range | name_part3 | name_part3 | 7 | NULL | 93 | Using where | +----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+ 1 row in set (0.00 sec) 看來局部索引對like的效果不是很明顯的，可能跟數據分布范圍有關，也許這93條數據全部打散在各個數據庫塊中， 所以導致解析器認為不能簡單地通過數次index就能遍歷出數據，故而Extra欄裡面就沒有出現Using Index的提示。 總結：在索引中正確的定義列(包括定義列的順序和位置)能夠改變索引的實際使用效果。好的索引能夠為一個執行緩慢的查詢帶來 巨大的性能提升。索引也可能使原來執行很快的查詢的執行時間減少若干毫秒。在高並發系統中，將1 000 000 條查詢減少幾毫秒 將會顯著改善性能，並且獲得更大的容量和擴展性。為SQL 查詢創建最優索引可以認為是一項藝術。
