MySQL筆記：select默認使用不當索引導致的巨大性能損失問題 數據庫使用菜鳥一枚，只會最基本的select。最近碰到一個mysql對某select語句使用索引不當而導致的性能問題，頗有意思，故記之 索引，是對數據庫操作性能最息息相關的一個因素，我也不必多說。但是，你是否想過，就算建立了合適的索引，數據庫也有可能沒有足夠的“智能”去選擇針對某條select最合適的索引呢？這種事還真被我碰上了，於是第一次用上了force index這種神奇的東西~ 先說一下背景情況： www.2cto.com 系統環境： os: windows 7 home edition 64 bit db: MySQL 5.5.28 x64 涉及的數據庫表，就一張，叫flow，用MyISAM引擎，有下面幾列： start: int end: int time: timestamp amount: int 含義：表中每行指從地點start到地點end在time時刻，共有amount的數據流動。 注意，在我們的應用場景下，start=end是可能的，即同一個地點發送和接收。 數據量：1000多個可選擇的地點（都可作為start或end），時間跨度約15天，共20,000,000條以上數據 www.2cto.com 在這個表上有下面這幾個索引： idx_start_time: 以start, time為key idx_end_time: 以end, time為key idx_time: 以time為key 要解決的問題有如下3個： 一個時間段T內，以某個地點A為起點發出的數據總和 一個時間段T內，以某個地點A為終點收到的數據總和 一個時間段T內，以某個地點A為起點或終點產生的數據總和（如果起點和終點都是A，數據流動只計算一次） 怎麼樣，都是很簡單的問題吧，於是三下五除二，就倒騰出了三個select語句。 設地點A為1，時間范圍T是2012-01-01一整天 問題1： select sum(amount) from flow where start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 問題2: select sum(amount) from flow where end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 問題3： select sum(amount) from flow where (start=1 or end=1) and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 再來測試一下： 跑第一個，耗時0.01s。(不錯。) 再跑第二個，耗時0.01s。(很不錯。這活兒太容易了~) 再來第三個，耗時3s。(等等，這是砸回事兒？太不科學啦！怎麼一合並一下多出了300倍的耗時？？) 沒辦法，果然沒那麼簡單輕松，又得苦逼地接著找辦法啦。否則回頭給1000多個地點統計半年裡每一天的數據，不得算上1000 * 180 * 3 = 540,000s = 150h。150個小時啊，就做這麼一個簡單到爆的匯總，不扯淡嗎！ 好在，有前兩個問題的幫忙，並利用在小學裡打下的扎實的"集合論"基礎，想到了一個回旋的方法： 問題3答案 = 問題1答案+問題2答案-(A同時為起點和終點的在時間段T內的數據流動) 而(A同時為起點和終點的在時間段T內的數據流動)，這還不簡單，直接把問題3裡面的or改成and就行了： select sum(amount) from flow where (start=1 and end=1) and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 再一跑這個，也不過耗時0.01s。把這三個查詢合一塊兒，也不到0.05s，比起那個坑爹的3s可是好多了。這下整個統計總能在幾個小時裡跑完，還成~ 不過，如果到這兒就結束這問題，也就不會有這文章了。我沒法就這麼接受這種無比別扭的臨時解決方案，況且在代碼的注釋上寫個"I don't know why, but this method is faster"也有點太sb了~所以，本著從小養成的打破沙鍋問到底的優良習慣，我就開始琢磨更'優雅'的解決方案。 終於，在腦子的一角想起了在那個我瘋狂看書的年代，曾在一本sql的教程上看到過一個叫做explain的命令，可以用來分析select語句。好吧，操起這家伙開干吧。由於我貧乏的數據庫知識，我也只能想到這是索引在搗蛋，於是我也就關注了explaint結果裡的索引那一部分（說實話，其他的我也看不太懂= =）。 問題1~3的sql語句在explain命令分析下，得到的優先采用的索引如下： 問題1：idx_start_time 問題2：idx_end_time 問題3：idx_time www.2cto.com 這一看，果然索引不對勁。第1和第2個用的索引非常完美，但第3個就不對了。MySQL默認首先用了time作索引，也就是說它首先用time過濾一遍所有數據。在現在的問題下，先用time過濾導致效率底下的可能原因有（基本上是自己的想象，因為對數據庫的底層實現機理實在是不了解）： time的比較操作采用的是between范圍比較，而start和end都是直接的等於比較 一張表中大概包含15天的數據，所以在按天查詢的情況下，time第一遍過濾後，還會剩下大約1/15的數據需要進行後續過濾。相反，如果第一遍使用start或end進行過濾，因為一共有1000個左右的不同地點，所以只剩下約1/1000的數據還需要後續的條件過濾。 那麼，我怎麼樣才能讓MySQL修正這個索引判斷錯誤呢。一搜，發現有個叫force index的東西，開始嘗試： select sum(amount) from flow force index (idx_start_time, idx_end_time) where (start=1 or end=1) and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59' 結果1.7s。快是快了一點，但也沒多大改進啊，還是坑爹。 於是，接著想，這個式子到底怎麼跑才能快呢？我得到的初步結論是： 用start過濾一次原始數據，得到一個過濾結果r1 用end再過濾一次原始數據，得到一個過濾結果r2 合並r1和r2為r 在r上，對time進行過濾 呃，是不是現在對問題3寫的SQL語句讓MySQL沒辦法找到這種解法呢？那麼就改寫法吧，搞不好就能讓MySQL開竅了。於是，把or展開： select sum(amount) from flow where (start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') or (end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') 先不加force index，依然是坑爹的3s。 www.2cto.com 接著，加上force index select sum(amount) from flow force index (idx_start_time, idx_end_time) where (start=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') or (end=1 and time between '2012-01-01 00:00:00' and '2012-01-01 23:59:59') 見證奇跡的時刻到了，0.01s 這坑爹的MySQL在這個問題上終於被調教好了！ 後記： 正如一開始提到的，我並沒有很強的數據庫知識和使用經驗，所以上面提到的解法和觀點很有可能是不精確甚至是錯誤的。雖然我最終看似得到了一些結論，但是產生這個問題的根本原因依然沒有理解的十分透徹。進一步的分析可能需要對MySQL或其他類似關系型數據庫的底層實現機制有一定的了解，對我而言這目前是一個徹底的空白。 我只能說， 對於MySQL，在有些情況下更改SQL語句的字面寫法和強制指定索引真的是有可能起到奇效的。這並不只是理論上的可能性，而是實際工作學習中可能遇到的實實在在的問題。
