mysql資料庫索引查詢一個最佳化大資料量的執行個體的分享

標籤：io   使用   檔案   資料   問題   log   資料庫   伺服器   sql   

我們要訪問的表是一個非常大的表，四千萬條記錄，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資料庫索引查詢一個最佳化大資料量的執行個體的分享