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數據庫利用索引提高查詢效率
 
Mysql數據庫管理系統優化方案
首先,爲了使一個系統更快,最重要的部分就是基礎設計,不過有些東西是現有情況下無法逾越的,比如說系統常見的瓶頸. 我所能想到的: 1:磁盤尋道能力,以高速硬盤(7200轉/秒),理論上每秒尋道7200次.這是沒有辦法改變的,優化...查看完整版>>Mysql數據庫管理系統優化方案
 
運用ADO.NET對象優化數據查詢代碼
  毫無疑問,ADO.NET 向人們提供了一種功能強大、仿真數據庫的對象模型,它可以將數據記錄保存到內存中。尤其是ADO.net 的 DataSet 類,它不但在功能上相當于數據庫表的集中存儲器(central repository),而且支持表...查看完整版>>運用ADO.NET對象優化數據查詢代碼
 
運用 ADO.NET 對象優化數據查詢代碼
毫無疑問,ADO.NET 向人們提供了一種功能強大、仿真數據庫的對象模型,它可以將數據記錄保存到內存中。尤其是ADO.net 的 DataSet 類,它不但在功能上相當于數據庫表的集中存儲器(central repository),而且支持表間的...查看完整版>>運用 ADO.NET 對象優化數據查詢代碼
 
數據庫查詢優化
數據庫系統是管理信息系統的核心,基于數據庫的聯機事務處理(OLTP)以及聯機分析處理(OLAP)是銀行、企業、政府等部門最爲重要的計算機應用之一。從大多數系統的應用實例來看,查詢操作在各種數據庫操作中所占據的比...查看完整版>>數據庫查詢優化
 
 
回到王朝網路首頁