程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL數據庫查詢優化

MySQL數據庫查詢優化

編輯: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萬,具體看客戶網絡數據的分布情況,不好估計。

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

解決統計查詢過程中,讓我體會到詳細分析業務流程細節,作出相應的優化,有時是可以解決問題的。

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

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

原文鏈接:http://www.cppblog.com/cool-liangbing/archive/2009/06/14/87665.html

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved