MySQL（三）｜《千萬級大數據查詢優化》第一篇：創建高性能的索引 · php開發筆記

MySQL優化一般是需要索引優化、查詢優化、庫表結構優化三駕馬車齊頭并進。可以說，索引優化是對查詢性能優化最有效的手段，索引能夠輕易將查詢性能提高幾個數量級，“最優”的索引有時比一個“好的”索引性能要好幾個數量級。創建一個真正“最優”的索引經常需要重寫查詢，所以索引優化和查詢優化的關系很緊密。本文是《千萬級大數據查詢優化》系列第一篇：創建高性能的索引。我們先從一個面試題開始。 > 面試題： > 如果有四條sql語句，查詢條件分別是 > where A=1 and B=1 and C=1. > where A=1 and B=1. > where A=1 and C=1. > where B=1 and C=1. > 問該怎么設計索引？ > 四位求職者回答： > 第一個求職者回答到：需要創建3個組合索引(a, b, c)(a, c)(b, c)； > 第二個求職者回答：也是需要3個組合索引(a, b)(b, c)(a, c)； > 第三個求職者回答：只需要2個組合索引(a, b, c)(b, c)； > 第四個求職者回答：這個要看索引的區分度是怎么樣，如果ab區分度都很好，單獨idx\_a和idx\_b，普通情況idx\_abc和idx\_bc。看完四位求職者的回答，你的答案是什么呢？ ## 一、分析四位求職者的答案上面的面試題涉及到的知識是多列索引的創建和選擇合適的索引列順序，我們先創建一個表進行測試。 ~~~csharp # 創建數據表 create table tb_test_1( id smallint unsigned auto_increment primary key, AAA varchar(100) not null, BBB varchar(100) not null, CCC varchar(100) not null, DDD varchar(100) not null ); # 插入數據，執行幾十次 INSERT INTO tb_test_1 VALUES (null, CONCAT('aaa', ROUND(RAND()*1)), CONCAT('bbb', ROUND(RAND()*1)), CONCAT('ccc', ROUND(RAND()*1)), CONCAT('ddd', ROUND(RAND()*1))), (null, CONCAT('aaa', ROUND(RAND()*10)), CONCAT('bbb', ROUND(RAND()*10)), CONCAT('ccc', ROUND(RAND()*10)), CONCAT('ddd', ROUND(RAND()*10))), (null, CONCAT('aaa', ROUND(RAND()*100)), CONCAT('bbb', ROUND(RAND()*100)), CONCAT('ccc', ROUND(RAND()*100)), CONCAT('ddd', ROUND(RAND()*100))), (null, CONCAT('aaa', ROUND(RAND()*1000)), CONCAT('bbb', ROUND(RAND()*1000)), CONCAT('ccc', ROUND(RAND()*1000)), CONCAT('ddd', ROUND(RAND()*1000))); ~~~ 再按照四位面試者的回答一一進行測試。先按照第一個求職者回答到：需要創建3個組合索引(a, b, c)、(a, c)、(b, c)： ~~~cpp ALTER TABLE tb_test_1 ADD INDEX idx_abc (AAA, BBB, CCC); ALTER TABLE tb_test_1 ADD INDEX idx_ac (AAA, CCC); ALTER TABLE tb_test_1 ADD INDEX idx_bc (BBB, CCC); ~~~ 再把四個查詢分別執行下，通過執行計劃檢查命中索引的情況如何，在分析之前先把EXPLAIN字段的含義進行一個說明，如下所示： > 1）Table： > 顯示這一行的數據是關于哪張表的。 > 2）possible\_keys： > 顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。 > 3）key： > 實際使用的索引。如果為NULL，則沒有使用索引。MySQL很少會選擇優化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引。 > 4）key\_len： > 使用的索引的長度。在不損失精確性的情況下，長度越短越好。 > 5）ref： > 顯示索引的哪一列被使用了，如果可能的話，是一個常數。 > 6）rows： > MySQL認為必須檢索的用來返回請求數據的行數。 > 7）type： > 這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq\_reg、ref、range、index和ALL。分別執行如下四個查詢： ~~~bash EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' AND BBB='bbb1' AND CCC='ccc1'; EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' and BBB='bbb1'; EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' and CCC='ccc1'; EXPLAIN SELECT * FROM tb_test_1 WHERE BBB='bbb1' and CCC='ccc1'; ~~~ 執行計劃如下： ![](https://img.kancloud.cn/0a/e4/0ae447f67903d252796912523ce94385_2646x248.jpg) WHERE AAA='aaa1' AND BBB='bbb1' AND CCC='ccc1' ![](https://img.kancloud.cn/f2/72/f2726ae26c77d5164d95d61e0a378ba3_2666x244.jpg) WHERE AAA='aaa1' and BBB='bbb1' ![](https://img.kancloud.cn/94/67/9467e0277a03a3bc0974806a8aa215aa_2666x260.jpg) WHERE AAA='aaa1' and CCC='ccc1' ![](https://img.kancloud.cn/53/4d/534d8a98828ec39834d8a357e8c43981_2674x248.jpg) WHERE BBB='bbb1' and CCC='ccc1' 通過執行計劃得知，前面兩個查詢使用了idx\_abc組合索引，后面兩個查詢分別使用了idx\_ac和idx\_bc兩個組合索引。再按照第二個求職者回答：也是需要3個組合索引(a, b)、(b, c)、(a, c)。為了排除干擾，先把之前的索引全部刪除。 ~~~csharp # 刪除索引 DROP INDEX idx_abc ON tb_test_1; DROP INDEX idx_ac ON tb_test_1; DROP INDEX idx_bc ON tb_test_1; # 創建3個組合索引(a, b)、(b, c)、(a, c) ALTER TABLE tb_test_1 ADD INDEX idx_ab (AAA, BBB); ALTER TABLE tb_test_1 ADD INDEX idx_ac (AAA, CCC); ALTER TABLE tb_test_1 ADD INDEX idx_bc (BBB, CCC); ~~~ 再次執行 ~~~bash EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' AND BBB='bbb1' AND CCC='ccc1'; EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' and BBB='bbb1'; EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' and CCC='ccc1'; EXPLAIN SELECT * FROM tb_test_1 WHERE BBB='bbb1' and CCC='ccc1'; ~~~ ![](https://img.kancloud.cn/31/e8/31e8fd8fc0dabb9630c18e796b737687_2758x270.jpg) WHERE AAA='aaa1' AND BBB='bbb1' AND CCC='ccc1' ![](https://img.kancloud.cn/dc/2d/dc2da558668acb9ccf2b7f059bdbbb9f_2310x288.jpg) WHERE AAA='aaa1' and BBB='bbb1' ![](https://img.kancloud.cn/76/06/76061d99ad2ad188051d8127646fce72_2228x248.jpg) WHERE AAA='aaa1' and CCC='ccc1' ![](https://img.kancloud.cn/18/07/180767df7c4c220535eb54e1d2c43d3d_2232x262.jpg) WHERE BBB='bbb1' and CCC='ccc1' 再來看看第三個求職者回答（第四個的回答在普通情況下一致）：只需要2個組合索引(a, b, c)、(b, c)。 ~~~csharp # 刪除索引 DROP INDEX idx_ab ON tb_test_1; DROP INDEX idx_ac ON tb_test_1; DROP INDEX idx_bc ON tb_test_1; # 創建2個組合索引(a, b, c)、(b, c) ALTER TABLE tb_test_1 ADD INDEX idx_abc (AAA, BBB, CCC); ALTER TABLE tb_test_1 ADD INDEX idx_bc (BBB, CCC); ~~~ 執行計劃如下： ~~~bash EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' AND BBB='bbb1' AND CCC='ccc1'; EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' and BBB='bbb1'; EXPLAIN SELECT * FROM tb_test_1 WHERE AAA='aaa1' and CCC='ccc1'; EXPLAIN SELECT * FROM tb_test_1 WHERE BBB='bbb1' and CCC='ccc1'; ~~~ ![](https://img.kancloud.cn/bc/e5/bce59227d923c27135d7664e9bcc3e06_2232x270.jpg) WHERE AAA='aaa1' AND BBB='bbb1' AND CCC='ccc1' ![](https://img.kancloud.cn/c7/f1/c7f1416b7fe24023789af39307e258ff_2146x276.jpg) WHERE AAA='aaa1' and BBB='bbb1' ![](https://img.kancloud.cn/7e/1e/7e1e33cb3d2e2ebcaabbe51de0013008_2320x270.jpg) WHERE AAA='aaa1' and CCC='ccc1' ![](https://img.kancloud.cn/87/3b/873b04581cf364c30f556122e1974360_2158x288.jpg) WHERE BBB='bbb1' and CCC='ccc1' 最后，我們從key和rows的值來對比這三種情況的結果如何。第一種：創建3個組合索引(a, b, c)(a, c)(b, c) key:idx\_abc；rows:2 key:idx\_abc；rows:7 key:idx\_ac；rows:8 key:idx\_bc；rows:5 第二種：創建3個組合索引(a, b)(b, c)(a, c) key:idx\_bc,idx\_ab；rows:1 key:idx\_ab；rows:7 key:idx\_ac；rows:8 key:idx\_bc；rows:5 第三種：創建2個組合索引(a, b, c)(b, c) key:idx\_abc；rows:2 key:idx\_abc；rows:7 key:idx\_abc；rows:21 key:idx\_bc；rows:5 從索引數量和遍歷的行數兩個指標來評價，第一、二種的效果是一樣的，都需要3個組合索引，第三種的組合索引數量是2個，但是在`WHERE AAA='aaa1' and CCC='ccc1'`查詢時遍歷的行數為21，比前面兩種的8要大。 **綜合來說，在普通情況下，四位求職者的回答都是正確的。但是作為面試官來說，雖然前面三位都回答正確了，但是肯定都得不到錄用！** 第四位求職者說到“索引的區分度”是什么意思呢？我們以此為契機來分析如何創建一個高性能的索引。 ## 二、創建高性能的索引理解了后面的內容，第四位求職者的答案是否正確讀者自己去判斷。 ### 2.1、組合索引：將選擇性最高的列放到索引最前列在創建組合索引時，需要選擇合適的索引列順序。合適的索引列順序有一個經驗法則：**將選擇性最高的列放到索引最前列**（注意：這個法則也是在不需要考慮排序和分組的通常情況下有用）。比如我們要查詢`WHERE AAA='aaa1' and BBB='bbb1'`，組合索引是應該idx\_ab還是idx\_ba？參考經驗法則，先來看看這兩個值在這個表中的分布情況，確定哪個列的選擇性更高。如下查看AAA和BBB兩個列的選擇性值“ ~~~php # 查看選擇性值 SELECT COUNT(DISTINCT AAA)/COUNT(*) AS aaa_selectivity, COUNT(DISTINCT BBB)/COUNT(*) AS bbb_selectivity, COUNT(*) FROM tb_test_1; ~~~ 執行結果如下： ![](https://img.kancloud.cn/25/b9/25b94b9df5eda13161f109f54c3577ce_1702x294.jpg) 查看選擇性值從結果中的值來看，AAA的選擇性高于BBB，那么從這個方面來考慮組合索引應該為idx\_ab。 ### 2.2、索引長度和區分度的取舍首先介紹下索引長度和區分度的概念。索引長度很好理解，就是這個索引的長度。我們在上面提到的： > 4）key\_len： > 使用的索引的長度。在不損失精確性的情況下，長度越短越好。這里提到的精確性也就是稍微的區分度。通常情況下索引長度和區分度是相互矛盾的。我們舉例說明，向`tb_test_1`表中插入如下數據。 ~~~csharp INSERT INTO tb_test_1 VALUES (null, 'aaaaaaaaaaaaaaaaaaaaaaaa1111', 'b', 'c', 'd'), (null, 'aaaaaaaaaaaaaaaaaaaaaaaa1112', 'b', 'c', 'd'), (null, 'aaaaaaaaaaaaaaaaaaaaaaaa1122', 'b', 'c', 'd'), (null, 'aaaaaaaaaaaaaaaaaaaaaaaa1222', 'b', 'c', 'd'); ~~~ 在創建索引之前我們需要找出“索引長度和區分度”之間的平衡值，這個很有必要。因為當索引很長時，這會讓索引變得大且很慢。訣竅就是選擇足夠長的索引長度以保證較高的區分度，同時又不能太長（以便節約空間），也就是前綴索引應該足夠長，以使得前綴索引的選擇性接近于整個列。我們先找出整個列的選擇性： ~~~csharp SELECT COUNT(DISTINCT AAA)/COUNT(*) AS aaa_selectivity FROM tb_test_1; ~~~ 得出的選擇性值為0.5625，如下圖。 ![](https://img.kancloud.cn/c9/e9/c9e91436222953a9eace6ed61a2b50be_1000x212.jpg) AAA列的選擇性值進行找出最接近整個列的選擇性值的最小索引長度。 ~~~php SELECT COUNT(DISTINCT LEFT(AAA,4))/COUNT(*) AS aaa_selectivity_4, COUNT(DISTINCT LEFT(AAA,5))/COUNT(*) AS aaa_selectivity_5, COUNT(DISTINCT LEFT(AAA,6))/COUNT(*) AS aaa_selectivity_6, COUNT(DISTINCT LEFT(AAA,7))/COUNT(*) AS aaa_selectivity_7, COUNT(DISTINCT LEFT(AAA,8))/COUNT(*) AS aaa_selectivity_8, COUNT(DISTINCT LEFT(AAA,9))/COUNT(*) AS aaa_selectivity_9, COUNT(DISTINCT LEFT(AAA,26))/COUNT(*) AS aaa_selectivity_26, COUNT(DISTINCT LEFT(AAA,27))/COUNT(*) AS aaa_selectivity_27, COUNT(DISTINCT LEFT(AAA,28))/COUNT(*) AS aaa_selectivity_28, COUNT(DISTINCT LEFT(AAA,29))/COUNT(*) AS aaa_selectivity_29 FROM tb_test_1; ~~~ 執行結果如下： ![](https://img.kancloud.cn/f5/7a/f57a0a07f3f0b3b9eb6a4ada804b4c24_2116x906.jpg) 找出索引長度的平衡值從結果中我們得知，當索引長度為28時，區分度和整個列是一致的，當索引長度為6之后，區分度也已經很高了，為0.5391，比整個列的0.5625差不了多少。當然因為長度為28也不是很大，我們把索引長度定位28，在實際應用中，當索引再長的話就不得不的損失一些精確性。 `ALTER TABLE tb_test_1 ADD INDEX idx_a (AAA(28));` ### 2.3、網上關于索引的一些傳說最后貼上網上關于索引的一些傳說，讀者可以先判斷下是否正確。我也會在后面的文章一一驗證。 > 1、對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。 > 2、應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如： > select id from t where num is null > 可以在num上設置默認值0，確保表中num列沒有null值，然后這樣查詢： > select id from t where num=0 > 3、應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。 > 4、應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如： > select id from t where num=10 or num=20 > 可以這樣查詢： > select id from t where num=10 > union all > select id from t where num=20 > 5、in 和 not in 也要慎用，否則會導致全表掃描，如： > select id from t where num in(1,2,3) > 對于連續的數值，能用 between 就不要用 in 了： > select id from t where num between 1 and 3 > 6、下面的查詢也將導致全表掃描： > select id from t where name like '%abc%' > 若要提高效率，可以考慮全文檢索。 > 7、如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描： > select id from t where num=@num > 可以改為強制查詢使用索引： > select id from t with(index(索引名)) where num=@num > 8、應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如： > select id from t where num/2=100 > 應改為: > select id from t where num=100\*2 > 9、應盡量避免在where子句中對字段進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如： > select id from t where substring(name,1,3)='abc'--name以abc開頭的id > select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id > 應改為: > select id from t where name like 'abc%' > select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' > 10、不要在 where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。 > 11、在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。 > 12、不要寫一些沒有意義的查詢，如需要生成一個空表結構： > select col1,col2 into #t from t where 1=0 > 這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣： > create table #t(...) > 13、很多時候用 exists 代替 in 是一個好的選擇： > select num from a where num in(select num from b) > 用下面的語句替換： > select num from a where exists(select 1 from b where num=a.num) > 14、并不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。 > 15、索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。 > 16、并不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。 > 17、盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。 > 18、盡量使用表變量來代替臨時表。如果表變量包含大量數據，請注意索引非常有限（只有主鍵索引）。 > 19、避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。 > 20、在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然后insert。 > 21、盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那么就應該考慮改寫。 > 22、在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句后向客戶端發送 DONE\_IN\_PROC 消息。 > 23、盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。