mysql資料庫查詢最佳化：分析商務程序細節

　　上兩周一直想辦法提高查詢速度，取得一點效果，解決了部分問題，記下來以便將來自己查看。

　　由於公司沒有專門的DBA，我自己對mysql資料庫也不是很熟悉，而且這個JAVA開發的網路審計系統的管理系統，是經過了N多人幾年時間的修修改改，今天到我們手裡，要改成能支援大流量情況的版本，所以對我們這個只有幾個人的JAVA組來說，確實是個難題。

　　這個大流量的情況在以前的文章裡也提到過，就是要支援每秒鐘處理1G左右的網路資料包，HTTP協議的資料包最多，因此HTTP協議分析模組的流水日誌表記錄最大，據估算可能到達一天4000萬條記錄，採用一天一張表，那也是很大的，我看了.MYD檔案大小，已經是8G多了。

　　而我們管理系統查詢日誌記錄時，對好幾個欄位都要進行條件查詢，而且有幾個欄位長度達到256，在8G這麼大的表裡查詢一個字串，如果找不到，那必定從頭要查到尾，速度慢得根本受不了。客戶還要好幾個欄位一起設定條件來查詢，這樣基本上是二三十分鐘都出不來，系統可用性極差。

　　我採用的方法是以測試為主，同時看JAVA代碼，通過Log4j和Perf4j日誌，看每個sql語句使用的時間，尋找效能瓶頸，然後有的放矢地進行最佳化。

　　對查詢最有效果的最佳化，自然是建立索引了，ID自然是自增、主鍵，這個前人已經做了;從where語句分析，時間欄位作為查詢條件很多，時間是8位元組，而且不重複，設定索引比較適合。我把時間設定為索引，有一點效果，但不大，估算一下：8 * 4000 0000 = 320 000 000 位元組，4000萬記錄的表僅僅時間一個欄位的索引將是320M，這還僅僅是我們上百張表的一張表而已(客戶要求我們至少儲存3個月記錄)。

　　建立索引能起到一定作用，但還是解決不了我們的問題。物理表建立不能再縮短時間了，因為一天一張表，3個月就91~92張表，30個協議模組就得2700多，這僅僅是協議流水日誌表，還有其它表呢。

　　也不能把客戶要求做成條件的欄位都設定成索引，那索引表將和原表差不多大，索引就失去意義了。在資料庫本身上最佳化，想去想來實在一下子想不到好辦法，感覺資料量大了，就算在Oracle上也沒有什麼神奇辦法吧。

　　我最後採用分段查詢的方法，就是4000萬條資料，我不管你設定什麼條件來查詢，我都是平均劃為成N段來查詢，比如400萬為一段，在頁面上提供一個下拉單：0~400萬，400~800萬，…，3600~4000萬，雖然查詢比較麻煩一點，但每段查詢的速度大大提高，控制在30秒左右，犧牲一些可用性，總比30分鐘還查不出來好吧。

　　流水日誌可以採用分段查詢解決，但客戶要求的各種統計呢，這不能說分段統計，別人要統計2天的，你分開是不行的。

　　以前已經採用了一次預統計，預先定時在後台對流水日誌表進行統計一次，儲存到預統計表，等使用者來查詢時，從預統計表進行各種查詢—-這個做法好，不得不誇下前任開發人員。

　　但現在形勢不同了，因為預統計表是採用一個月一張的，就現在流水日誌表的規模，那預統計表可能一張表超過4000萬，具體看客戶網路資料的分布情況，不好估計。

　　最後我和同事們對統計模式詳細分析，一個同事提出再在預統計表基礎上進行二次預統計，我們估算了一下，基本上等使用者來查詢時，所面對的表已經很小了，最多幾千條記錄，很快了。

　　解決統計查詢過程中，讓我體會到詳細分析商務程序細節，作出相應的最佳化，有時是可以解決問題的。

　　總體上來說，對資料庫查詢的最佳化，我們採取了一些常規的最佳化之後，如果還沒有取得想要的效果，我們有時候不必硬碰硬去最佳化查詢本身，改變一下使用模式，找找業務處理流程是否還有可修改的，說不定就輕鬆解決了存在的難題。

　　還有就是主管要把整個開發組積極性調動起來，大家一起測試、分析、想辦法、驗證，最後一致確定一個可行的方案，然後大家分頭去不打折扣的實現。

　　來源：liangbing 的部落格