這篇文章主要介紹了分析一個MySQL的異常查詢的案例,主要是針對索引方面的操作問題,需要的朋友可以參考下

問題

用戶工單疑問：相同的語句，只是最后的limit行數不同。奇怪的是，limit 10 的性能比limit 100的語句還慢約10倍。

隱藏用戶表信息，語句及結果如下

1. SELECT f1 , SUM(`f2`) `CNT` FROM T WHERE f1 IS NOT NULL AND f3 = '2014-05-12' GROUP BY f1 ORDER BY `CNT` DESC LIMIT 10; 

執行時間3 min 3.65 sec

1. SELECT f1 , SUM(`f2`) `CNT` FROM T WHERE f1 IS NOT NULL AND f3 = '2014-05-12' GROUP BY f1 ORDER BY `CNT` DESC LIMIT 100; 

執行時間1.24Sec.

性能差距非常大!

分析

MySQL Tips：追查語句執行時最常用的方法，是通過explain來看語句的執行計劃。 ?

更有沖擊性的效果是通過縮小范圍后，在這個數據下，limit 67和limit 68的執行計劃相差很大。

兩個執行計劃：

1. LIMIT 67 
2. id: 1 
3. select_type: SIMPLE 
4. table: a 
5. type: range 
6. possible_keys: A,B,C 
7. key: B 
8. key_len: 387 
9. ref: NULL 
10. rows: 2555192 
11. Extra: Using where; Using temporary; Using filesort 
12. 1 row in set (0.00 sec) 
13.  
14. LIMIT 68 
15. id: 1 
16. select_type: SIMPLE 
17. table: a 
18. type: ref 
19. possible_keys: A,B,C 
20. key: A 
21. key_len: 3 
22. ref: const 
23. rows: 67586 
24. Extra: Using where; Using temporary; Using filesort 
25. 1 row in set (0.00 sec) 

可以看到，兩個語句的執行計劃不同：使用的索引不同。

MySQL Tips：explain的結果中，key表示最終使用的索引，rows表示使用這個索引需要掃描的行數，這是個估計值。

表中 索引A定義為 (f3, f4, f1, f2, f5); 索引B定義為(f1, f2, f3);

一個確認

雖然rows是估計值，但是指導索引使用的依據。既然limit 68能達到rows 67586,說明在第一個語句優化器可選結果中，也應該有此值，為什么不會選擇索引A?

先確認一下我們上面的這個結論。

MySQL Tips：MySQL語法中能夠用force index 來強行要求優化器使用某一個索引。

1. Explain SELECT f1 , SUM(f2) CNT FROM t force index(A) WHERE f1 IS NOT NULL AND f3 = ‘2014-05-12' GROUP BY P ORDER BY CNT DESC LIMIT 67/G 
2.  
3. id: 1 
4. select_type: SIMPLE 
5. table: a 
6. type: ref 
7. possible_keys:A 
8. key: A 
9. key_len: 3 
10. ref: const 
11. rows: 67586 
12. Extra: Using where; Using temporary; Using filesort 
13. 1 row in set (0.00 sec) 

順便說明，由于我們指定了force index，因此優化器不會考慮其他索引，possible_keys里只會顯示A。我們關注的是rows:67586。這說明在limit 67語句里，使用索引A也能夠減少行掃描。

MySQL Tips：MySQL優化器會對possiable_key中的每個可能索引都計算查詢代價，選擇最小代價的查詢計劃。

至此我們大概可以猜測，這個應該是MySQL實現上的bug：沒有選擇合適的索引，導致使用了明顯錯誤的執行計劃。

MySQL Tips：MySQL的優化器執行期間需要依賴于表的統計信息，而統計信息是估算值，因此有可能導致得到的執行計劃非最優。

但要說明的是，上述Tip是客觀情況造成(可接受)，但本例卻是例外，因此優化器實際上可以拿到能夠作出選擇正確結果的數據(rows值)，但是最終選擇錯誤。

原因分析

MySQL優化器是按照查詢代價的估算值，來確定要使用的索引。計算這個估算值的過程，基本是按照“估計需要掃描的行數”來確定的。

MySQL Tips：MySQL在目前集團主流使用的5.1和5.5版本中只能使用前綴索引。

因此，使用索引A只能用上字段f3，使用索引B只能用上字段f1。Rows即為使用了索引查到上下界，之后需要掃描的數據行數(估算值)。

上述的語句需要用到group和order by，因此執行計劃中都有Using temporary; Using filesort。

流程上按順序先計算使用索引A的查詢代價。

之后依次計算其他possitabe_key的查詢代價。由于過程中需要排序，在得到一個暫定結果后，需要判斷是否有代價更低的排序方式(test_if_cheaper_ordering)。

與之前的大同小異，也是依靠估計掃描行數來計算代價。

在這個邏輯的實現過程中，存在一個bug：在估計當前索引的區分度的時候，沒有考慮到前綴索引。

即：假設表中有50w行數據，索引B(f1,f2,f3)，則計算索引區分度時，需要根據能夠用上的前綴部分來確定。比如f1有1000個不同的值，則平均每個key值上的記錄數為500.如(f1,f2)有10000個同的值，則平均每個組合key上的記錄數為50，若(f1,f2,f3)有50w個不同的值，則平均每個組合key上的記錄數為1。

MySQL Tips：每個key上的記錄數越少，說明使用該索引查詢時效率最高。對應于show index from tbl 輸出結果中的Cardinality值越大。

在這個case下，索引B只能使用f1做前綴索引，但是在計算單key上的行平均值時用的是(f1,f2,f3)，這就導致估算用索引B估算的時候，得到的代價偏小。導致誤選。

回到問題本身

1、 為什么limit值大的時候反而選對了呢?

這是因為在計算B的查詢代價時，查詢需要返回的行數limit_rows也參與乘積，若limit值較大，則計算出來的B的代價就會更大，反而會由于代價。值超過A，而導致優化器最終選擇A。

2、 這個表有50w行數就，為什么limit相差為就差別這么大?

這與語句本身有關。這個語句中有group by，這就意味著每多limit一個值，實際上需要掃描更多的行N。 這里N為“表的總行數”/“表中不同的f2值”。

也就是說這個語句使得這個bug有放大作用。

解決方案

分析清楚后解決方法就比較簡單了，修改代碼邏輯，在執行test_if_cheaper_ordering過程中，改用字段f1的區分度來計算即可。