怎麼提高MySQL Limit查詢的效能

有個幾千萬條記錄的表 on MySQL 5.0.x，現在要讀出其中幾十萬萬條左右的記錄。常用方法，依次迴圈：

1

代碼如下	複製代碼
select * from mytable where index_col = xxx limit offset, limit;

經驗：如果沒有blob/text欄位，單行記錄比較小，可以把 limit 設大點，會加快速度。

問題：頭幾萬條讀取很快，但是速度呈線性下降，同時 mysql server cpu 99% ，速度不可接受。

調用

代碼如下	複製代碼
explain select * from mytable where index_col = xxx limit offset, limit;

顯示 type = ALL

在 MySQL optimization 的文檔寫到"All"的解釋

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

看樣子對於 all, mysql 就使用比較笨的方法，那就改用 range 方式？ 因為 id 是遞增的，也很好修改 sql 。

代碼如下	複製代碼
select * from mytable where id > offset and id < offset + limit and index_col = xxx

explain 顯示 type = range，結果速度非常理想，返回結果快了幾十倍。

Limit文法：

1

代碼如下	複製代碼
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT子句可以被用於強制 SELECT 語句返回指定的記錄數。LIMIT接受一個或兩個數字參數。參數必須是一個整數常量。

如果給定兩個參數，第一個參數指定第一個返回記錄行的位移量，第二個參數指定返回記錄行的最大數目。初始記錄行的位移量是 0(而不是 1)。

為了與 PostgreSQL 相容，MySQL 也支援句法：LIMIT # OFFSET #。

代碼如下	複製代碼
mysql> SELECT * FROM table LIMIT 5,10; //檢索記錄行6-15

  

 //為了檢索從某一個位移量到記錄集的結束所有的記錄行，可以指定第二個參數為-1 

代碼如下	複製代碼
mysql> SELECT * FROM table LIMIT 95,-1; //檢索記錄行96-last

    
 //如果只給定一個參數，它表示返回最大的記錄行數目，換句話說，LIMIT n 等價於 LIMIT 0,n 

代碼如下	複製代碼
mysql> SELECT * FROM table LIMIT 5;

//檢索前5個記錄行
MySQL的limit給分頁帶來了極大的方便，但資料量一大的時候，limit的效能就急劇下降。同樣是取10條資料，下面兩句就不是一個數量層級的。

代碼如下	複製代碼
select * from table limit 10000,10  select * from table limit 0,10

文中不是直接使用limit，而是首先擷取到offset的id然後直接使用limit size來擷取資料。根據他的資料，明顯要好於直接使用limit。

這裡我具體使用資料分兩種情況進行測試。

offset比較小的時候：

 

代碼如下

複製代碼

select * from table limit 10,10   //多次運行，時間保持在0.0004-0.0005之間  Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10    //多次運行，時間保持在0.0005-0.0006之間，主要是0.0006 結論：位移offset較小的時候，直接使用limit較優。這個顯然是子查詢的原因。 offset大的時候：  select * from table limit 10000,10    //多次運行，時間保持在0.0187左右       Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10  //多次運行，時間保持在0.0061左右，只有前者的1/3。可以預計offset越大，後者越優

下面我們來看個mysql千萬級資料分頁的方法，也是基於limit的

我們來做一個測試ipdatas表：
 

代碼如下

複製代碼

CREATE TABLE `ipdatas` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `uid` INT(8) NOT NULL DEFAULT '0',    `ipaddress` VARCHAR(50) NOT NULL,    `source` VARCHAR(255) DEFAULT NULL,    `track` VARCHAR(255) DEFAULT NULL,    `entrance` VARCHAR(255) DEFAULT NULL,    `createdtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',    `createddate` DATE NOT NULL DEFAULT '0000-00-00',    PRIMARY KEY (`id`),    KEY `uid` (`uid`)   ) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8;

這是我們做的廣告同盟推廣ip資料記錄表，由於我也不是mysql的DBA所以這裡咱們僅僅是測試
  因為原來裡面有大概7015291條資料

  這裡我們通過jdbc的batch插入6000萬條資料到此表當中“JDBC插入6000W條資料用時:9999297ms”；
  大概用了兩個多小時,這裡面我用的是batch大小大概在1w多每次提交,還有一點是每次提交的資料都很小，而且這裡用的myisam資料表，因為我需要知道mysql資料庫的大小以及索引資料的大小結果是
  ipdatas.MYD 3.99 GB (4,288,979,008 位元組)
  ipdatas.MYI 1.28 GB (1,377,600,512 位元組)
  這裡面我要說的是如果真的是大資料如果時間需要索引還是最好改成數字欄位,索引的大小和查詢速度都比時間欄位可觀。

  步入正題:
  1.全表搜尋
 返回結構是67015297條資料

代碼如下	複製代碼
SELECT COUNT(id) FROM ipdatas;    SELECT COUNT(uid) FROM ipdatas;    SELECT COUNT(*) FROM ipdatas;

   首先這兩個全表資料查詢速度很快,mysql中包含資料字典應該保留了資料庫中的最大條數
 查詢索引條件

代碼如下	複製代碼
SELECT COUNT(*) FROM ipdatas WHERE uid=1;   返回結果時間:2分31秒594    SELECT COUNT(id) FROM ipdatas WHERE uid=1;  返回結果時間:1分29秒609    SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回結果時間:2分41秒813

   第二次查詢都比較快因為mysql中是有緩衝區的所以增大緩衝區的大小可以解決很多查詢的最佳化，真可謂緩衝無處不在啊在程式開發中也是層層都是緩衝
 查詢資料

代碼如下

複製代碼

第一條開始查詢    SELECT * FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒    SELECT * FROM ipdatas LIMIT 1,10 ; 15ms      第10000條開始查詢    SELECT * FROM ipdatas ORDER BY id ASC LIMIT 10000,10 ; 266毫秒    SELECT * FROM ipdatas LIMIT 10000,10 ; 16毫秒    第500萬條開始查詢    SELECT * FROM ipdatas LIMIT 5000000,10 ;11.312秒    SELECT * FROM ipdatas ORDER BY id ASC LIMIT 5000000,10 ; 221.985秒    這兩條返回結果完全一樣,也就是mysql預設機制就是id正序然而時間卻大相徑庭    第5000萬條開始查詢    SELECT * FROM ipdatas LIMIT 60000000,10 ;66.563秒 (對比下面的測試)    SELECT * FROM ipdatas ORDER BY id ASC LIMIT 50000000,10; 1060.000秒    SELECT * FROM ipdatas ORDER BY id DESC LIMIT 17015307,10; 434.937秒

第三條和第二條結果一樣只是排序的方式不同但是用時卻相差不少，看來這點還是不如很多的商務資料庫,像oracle和sqlserver等都是中間不成兩邊還是沒問題，看來mysql是開始行越向後越慢，這裡看來可以不排序的就不要排序了效能差距巨大,相差了20多倍

 查詢資料返回ID列表
  

代碼如下

複製代碼

第一條開始查    select id from ipdatas order by id asc limit 1,10; 31ms    SELECT id FROM ipdatas LIMIT 1,10 ; 0ms      第10000條開始    SELECT id FROM ipdatas ORDER BY id ASC LIMIT 10000,10; 68ms    select id from ipdatas limit 10000,10;0ms    第500萬條開始查詢    SELECT id FROM ipdatas LIMIT 5000000,10; 1.750s    SELECT id FROM ipdatas ORDER BY id ASC LIMIT 5000000,10;14.328s    第6000萬條記錄開始查詢    SELECT id FROM ipdatas LIMIT 60000000,10; 116.406s    SELECT id FROM ipdatas ORDER BY id ASC LIMIT 60000000,10; 136.391s    select id from ipdatas limit 10000002,10; 29.032s    select id from ipdatas limit 20000002,10; 24.594s    select id from ipdatas limit 30000002,10; 24.812s    select id from ipdatas limit 40000002,10; 28.750s  84.719s    select id from ipdatas limit 50000002,10; 30.797s  108.042s    select id from ipdatas limit 60000002,10; 133.012s  122.328s    select * from ipdatas limit 10000002,10; 27.328s    select * from ipdatas limit 20000002,10; 15.188s    select * from ipdatas limit 30000002,10; 45.218s    select * from ipdatas limit 40000002,10; 49.250s   50.531s    select * from ipdatas limit 50000002,10; 73.297s   56.781s    select * from ipdatas limit 60000002,10; 67.891s   75.141s    select id from ipdatas order by id asc limit 10000002,10; 29.438s    select id from ipdatas order by id asc limit 20000002,10; 24.719s    select id from ipdatas order by id asc limit 30000002,10; 25.969s    select id from ipdatas order by id asc limit 40000002,10; 29.860d    select id from ipdatas order by id asc limit 50000002,10; 32.844s    select id from ipdatas order by id asc limit 60000002,10; 34.047s

至於SELECT * ipdatas order by id asc 就不測試了 大概都在十幾分鐘左右
   可見通過SELECT id 不帶排序的情況下差距不太大,加了排序差距巨大
   下面看看這條語句

代碼如下	複製代碼
SELECT * FROM ipdatas WHERE id IN (10000,100000,500000,1000000,5000000,10000000,2000000,30000000,40000000,50000000,60000000,67015297);    耗時0.094ms

   可見in在id上面的查詢可以忽略不計畢竟是6000多萬條記錄，所以為什麼很多lucene或solr搜尋都返回id進行資料庫重新獲得資料就是因為這個,當然lucene/solr+mysql是一個不錯的解決辦法這個非常適合前端搜尋技術,比如前端的分頁搜尋通過這個可以得到非常好的效能.還可以支援很好的分組搜尋結果集,然後通過id獲得資料記錄的真實資料來顯示效果真的不錯,別說是千萬層級就是上億也沒有問題,真是吐血推薦啊.

總結了，最關鍵的一句是

網上的改法可以參考一下，暫時解決問題

代碼如下	複製代碼
SELECT sql_no_cache *FROM table WHERE id>=(SELECTsql_no_cache id FROM table where conditon ORDER BY id DESC LIMIT 126380,1) limit 20;

很多問題大家可根據自身情況來分析最佳化mysql查詢語句。