怎麼提高MySQL Limit查詢的性能

怎麼提高MySQL Limit查詢的性能?我們主要是在mysql limit上下功夫了，當然還有其它的像對數據表，數據庫服務器配置等，但我們作為程序只只要在mysql查詢語句的性能上進行優化即可了。

有個幾千萬條記錄的表 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查詢語句。