《Mysql實例分析一個MySQL的異常查詢的案例》要點：
本文介紹了Mysql實例分析一個MySQL的異常查詢的案例，希望對您有用。如果有疑問，可以聯(lián)系我們。

問題MYSQL教程

用戶工單疑問：相同的語句,只是最后的limit行數(shù)不同.奇怪的是,limit 10 的性能比limit 100的語句還慢約10倍.MYSQL教程

隱藏用戶表信息,語句及結(jié)果如下
MYSQL教程

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;

執(zhí)行時間3 min 3.65 secMYSQL教程

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;

執(zhí)行時間1.24Sec.MYSQL教程

性能差距非常大!MYSQL教程

分析
MySQL Tips：追查語句執(zhí)行時最常用的方法,是通過explain來看語句的執(zhí)行計劃. ?MYSQL教程

更有沖擊性的效果是通過縮小范圍后,在這個數(shù)據(jù)下,limit 67和limit 68的執(zhí)行計劃相差很大.MYSQL教程

兩個執(zhí)行計劃：
MYSQL教程

LIMIT 67
id: 1
select_type: SIMPLE
table: a
type: range
possible_keys: A,B,C
key: B
key_len: 387
ref: NULL
rows: 2555192
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)
LIMIT 68
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: A,B,C
key: A
key_len: 3
ref: const
rows: 67586
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

可以看到,兩個語句的執(zhí)行計劃不同：使用的索引不同.MYSQL教程

MySQL Tips：explain的結(jié)果中,key表示最終使用的索引,rows表示使用這個索引需要掃描的行數(shù),這是個估計值.MYSQL教程

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

一個確認MYSQL教程

雖然rows是估計值,但是指導索引使用的依據(jù).既然limit 68能達到rows 67586,說明在第一個語句優(yōu)化器可選結(jié)果中,也應該有此值,為什么不會選擇索引A?
先確認一下我們上面的這個結(jié)論.MYSQL教程

MySQL Tips：MySQL語法中能夠用force index 來強行要求優(yōu)化器使用某一個索引.MYSQL教程

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
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys:A
key: A
key_len: 3
ref: const
rows: 67586
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

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

MySQL Tips：MySQL優(yōu)化器會對possiable_key中的每個可能索引都計算查詢代價,選擇最小代價的查詢計劃.MYSQL教程

至此我們大概可以猜測,這個應該是MySQL實現(xiàn)上的bug：沒有選擇合適的索引,導致使用了明顯錯誤的執(zhí)行計劃.MYSQL教程

MySQL Tips：MySQL的優(yōu)化器執(zhí)行期間需要依賴于表的統(tǒng)計信息,而統(tǒng)計信息是估算值,因此有可能導致得到的執(zhí)行計劃非最優(yōu).MYSQL教程

但要說明的是,上述Tip是客觀情況造成(可接受),但本例卻是例外,因此優(yōu)化器實際上可以拿到能夠作出選擇正確結(jié)果的數(shù)據(jù)(rows值),但是最終選擇錯誤.MYSQL教程

原因分析MYSQL教程

MySQL優(yōu)化器是按照查詢代價的估算值,來確定要使用的索引.計算這個估算值的過程,基本是按照“估計需要掃描的行數(shù)”來確定的.MYSQL教程

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

因此,使用索引A只能用上字段f3,使用索引B只能用上字段f1.Rows即為使用了索引查到上下界,之后需要掃描的數(shù)據(jù)行數(shù)(估算值).MYSQL教程

上述的語句需要用到group和order by,因此執(zhí)行計劃中都有Using temporary; Using filesort.
流程上按順序先計算使用索引A的查詢代價.MYSQL教程

之后依次計算其他possitabe_key的查詢代價.由于過程中需要排序,在得到一個暫定結(jié)果后,需要判斷是否有代價更低的排序方式(test_if_cheaper_ordering).
與之前的大同小異,也是依靠估計掃描行數(shù)來計算代價.MYSQL教程

在這個邏輯的實現(xiàn)過程中,存在一個bug：在估計當前索引的區(qū)分度的時候,沒有考慮到前綴索引.MYSQL教程

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

MySQL Tips：每個key上的記錄數(shù)越少,說明使用該索引查詢時效率最高.對應于show index from tbl 輸出結(jié)果中的Cardinality值越大.MYSQL教程

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

回到問題本身MYSQL教程

1、 為什么limit值大的時候反而選對了呢?
這是因為在計算B的查詢代價時,查詢需要返回的行數(shù)limit_rows也參與乘積,若limit值較大,則計算出來的B的代價就會更大,反而會由于代價.值超過A,而導致優(yōu)化器最終選擇A.MYSQL教程

2、 這個表有50w行數(shù)就,為什么limit相差為就差別這么大?
這與語句本身有關.這個語句中有group by,這就意味著每多l(xiāng)imit一個值,實際上需要掃描更多的行N. 這里N為“表的總行數(shù)”/“表中不同的f2值”.
也就是說這個語句使得這個bug有放大作用.MYSQL教程

解決方案MYSQL教程

分析清楚后解決方法就比較簡單了,修改代碼邏輯,在執(zhí)行test_if_cheaper_ordering過程中,改用字段f1的區(qū)分度來計算即可.
MYSQL教程

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/1754.html