剖析一個MySQL的異常查詢的案例。本站提示廣大學習愛好者：（剖析一個MySQL的異常查詢的案例）文章只能為提供參考，不一定能成為您想要的結果。以下是剖析一個MySQL的異常查詢的案例正文
成績
用戶工單疑問：雷同的語句，只是最初的limit行數分歧。奇異的是，limit 10 的機能比limit 100的語句還慢約10倍。
隱蔽用戶表信息，語句及成果以下
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
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的履行籌劃相差很年夜。
兩個履行籌劃：
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)
可以看到，兩個語句的履行籌劃分歧：應用的索引分歧。
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 來強行請求優化器應用某一個索引。
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，是以優化器不會斟酌其他索引，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的辨別度來盤算便可。
