MySQL數據庫索引查詢優化的分享

問題描述:

我們要訪問的表是一個非常大的表,四千萬條記錄,id是主鍵,program_id上建了索引。

執行一條SQL:

select * from program_access_log where program_id between 1 and 4000

這條SQL非常慢。

我們原以爲處理記錄太多的原因,所以加了id限制,一次只讀五十萬條記錄

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000

但是這條SQL仍然很慢,速度比上面一條幾乎沒有提升。

Mysql處理50萬條記錄的表,條件字段還建了索引,這條語句應該是瞬間完成的。

問題分析:

這張表大約容量30G,數據庫服務器內存16G,無法一次載入。就是這個造成了問題。

這條SQL有兩個條件,ID一到五十萬和Program_id一到四千,因爲program_id範圍小得多,mysql選擇它做爲主要索引。

先通過索引文件找出了所有program_id在1到4000範圍裏所有的id,這個過程非常快。

接下來要通過這些id找出表裏的記錄,由于這些id是離散的,所以mysql對這個表的訪問不是順序讀取。

而這個表又非常大,無法一次裝入內存,所以每訪問一條記錄mysql都要重新在磁盤上定位並把附近的記錄都載入內存,大量的IO操作導致了速度的下降。

問題解決方案:

1. 以program_id爲條件對表進行分區

2. 分表處理,每張表的大小不超過內存的大小

然而,服務器用的是mysql5.0,不支持分區,而且這個表是公共表,無法在不影響其它項目的條件下修改表的結構。

所以我們采取了第三種辦法:

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000

現在program_id的範圍遠大于id的範圍,id被當做主要索引進行查找,由于id是主鍵,所以查找的是連續50萬條記錄,速度和訪問一個50萬條記錄的表基本一樣

總結:

這是一個在千萬筆記錄表中由于使用了索引導致了數據查找變慢的問題,有一定的典型性和大家交流下!

· 把年齡相仿的獅虎熊放一起,誰更厲害?結果出人意料

很多人都想知道獅子、老虎和熊打起來誰最厲害,于是便有好事之人把這三種動物關在一起...

· 湖北宜昌三峽壩區水面驚現神秘動物

近日,湖北宜昌,一段視頻在當地熱傳:有網友在三峽壩區拍到神秘動物,體型碩大數米長...

· 什麽是語段?語段的類型以及和句群、段落的區別與聯系是什麽?

句群是最高級的語言單位。 段落(自然段)是章法單位...

 
MySQL查詢優化系列講座之數據類型與效率
     這一部分提供了如何選擇數據類型來幫助提高查詢運行速度的一些指導:  在可以使用短數據列的時候就不要用長的。如果你有一個固定長度的CHAR數據列,那麽就不要讓它的長度超出實際需要。如果你在數據列中存...查看完整版>>MySQL查詢優化系列講座之數據類型與效率
 
MySQL查詢優化系列講座之數據類型與效率
  這一部分提供了如何選擇數據類型來幫助提高查詢運行速度的一些指導:  在可以使用短數據列的時候就不要用長的。如果你有一個固定長度的CHAR數據列,那麽就不要讓它的長度超出實際需要。如果你在數據列中存儲的...查看完整版>>MySQL查詢優化系列講座之數據類型與效率
 
MySQL查詢優化系列講座之數據類型與效率
  這一部分提供了如何選擇數據類型來幫助提高查詢運行速度的一些指導:  在可以使用短數據列的時候就不要用長的。如果你有一個固定長度的CHAR數據列,那麽就不要讓它的長度超出實際需要。如果你在數據列中存儲的...查看完整版>>MySQL查詢優化系列講座之數據類型與效率
 
MySQL查詢優化系列講座之數據類型與效率
  這一部分提供了如何選擇數據類型來幫助提高查詢運行速度的一些指導:  在可以使用短數據列的時候就不要用長的。如果你有一個固定長度的CHAR數據列,那麽就不要讓它的長度超出實際需要。如果你在數據列中存儲的...查看完整版>>MySQL查詢優化系列講座之數據類型與效率
 
Informix數據庫利用索引提高查詢效率
如果查詢結果僅爲一行或很少幾行時(高選擇性high selectivity),利用索引進行查詢會大大提高效率。相比之下,如果沒有索引,查詢則只能順序掃描整個表。在OLTP環境下,事務處理在很大程度上依賴于索引。只有在表很小...查看完整版>>Informix數據庫利用索引提高查詢效率