怎么提高MySQL Limit查詢的性能?我們主要是在mysql limit上下功夫了,當(dāng)然還有其它的像對數(shù)據(jù)表,數(shù)據(jù)庫服務(wù)器配置等,但我們作為程序只只要在mysql查詢語句的性能上進行優(yōu)化即可了.

有個幾千萬條記錄的表on MySQL 5.0.x,現(xiàn)在要讀出其中幾十萬萬條左右的記錄,常用方法,依次循環(huán),代碼如下:

select * from mytable where index_col = xxx limit offset, limit;  

經(jīng)驗:如果沒有blob/text字段,單行記錄比較小,可以把 limit 設(shè)大點,會加快速度.

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

調(diào)用如下代碼:

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,結(jié)果速度非常理想,返回結(jié)果快了幾十倍.

Limit語法,代碼如下:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

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

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

為了與 PostgreSQL 兼容,MySQL 也支持句法：LIMIT # OFFSET #,代碼如下:

1.  mysql> SELECT * FROM table LIMIT 5,10; //檢索記錄行6-15 
2.  //為了檢索從某一個偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個參數(shù)為-1  
3.  代碼如下 復(fù)制代碼 
4.  mysql> SELECT * FROM table LIMIT 95,-1; //檢索記錄行96-last 
5.    //Vevb.com 
6.  //如果只給定一個參數(shù)，它表示返回最大的記錄行數(shù)目，換句話說，LIMIT n 等價于 LIMIT 0,n  
7.  代碼如下 復(fù)制代碼 
8.  mysql> SELECT * FROM table LIMIT 5; 
9. //檢索前5個記錄行  

MySQL的limit給分頁帶來了極大的方便,但數(shù)據(jù)量一大的時候,limit的性能就急劇下降,同樣是取10條數(shù)據(jù),下面兩句就不是一個數(shù)量級別的,代碼如下:

select * from table limit 10000,10

select * from table limit 0,10

文中不是直接使用limit，而是首先獲取到offset的id然后直接使用limit size來獲取數(shù)據(jù),根據(jù)他的數(shù)據(jù),明顯要好于直接使用limit。

這里我具體使用數(shù)據(jù)分兩種情況進行測試,offset比較小的時候,代碼如下:

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

下面我們來看個mysql千萬級數(shù)據(jù)分頁的方法,也是基于limit的,我們來做一個測試ipdatas表,代碼如下:

1. CREATE TABLE `ipdatas` ( 
2.    `id` INT(11) NOT NULL AUTO_INCREMENT, 
3.    `uid` INT(8) NOT NULL DEFAULT '0', 
4.    `ipaddress` VARCHAR(50) NOT NULL, 
5.    `source` VARCHAR(255) DEFAULT NULL, 
6.    `track` VARCHAR(255) DEFAULT NULL, 
7.    `entrance` VARCHAR(255) DEFAULT NULL, 
8.    `createdtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', 
9.    `createddate` DATE NOT NULL DEFAULT '0000-00-00', 
10.    PRIMARY KEY (`id`), 
11.    KEY `uid` (`uid`) 
12.   ) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8; 

這是我們做的廣告聯(lián)盟的推廣ip數(shù)據(jù)記錄表,由于我也不是mysql的DBA所以這里咱們僅僅是測試,因為原來里面有大概7015291條數(shù)據(jù),這里我們通過jdbc的batch插入6000萬條數(shù)據(jù)到此表當(dāng)中“JDBC插入6000W條數(shù)據(jù)用時:9999297ms”；

大概用了兩個多小時,這里面我用的是batch大小大概在1w多每次提交,還有一點是每次提交的數(shù)據(jù)都很小，而且這里用的myisam數(shù)據(jù)表，因為我需要知道m(xù)ysql數(shù)據(jù)庫的大小以及索引數(shù)據(jù)的大小結(jié)果是:

ipdatas.MYD 3.99 GB (4,288,979,008 字節(jié))

ipdatas.MYI 1.28 GB (1,377,600,512 字節(jié))

這里面我要說的是如果真的是大數(shù)據(jù)如果時間需要索引還是最好改成數(shù)字字段,索引的大小和查詢速度都比時間字段可觀.

步入正題:

1.全表搜索

返回結(jié)構(gòu)是67015297條數(shù)據(jù),代碼如下:

1. SELECT COUNT(id) FROM ipdatas; 
2. SELECT COUNT(uid) FROM ipdatas; 
3. SELECT COUNT(*) FROM ipdatas; 

首先這兩個全表數(shù)據(jù)查詢速度很快,mysql中包含數(shù)據(jù)字典應(yīng)該保留了數(shù)據(jù)庫中的最大條數(shù).

查詢索引條件,代碼如下:

1. SELECT COUNT(*) FROM ipdatas WHERE uid=1;   返回結(jié)果時間:2分31秒594 
2. SELECT COUNT(id) FROM ipdatas WHERE uid=1;  返回結(jié)果時間:1分29秒609 
3. SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回結(jié)果時間:2分41秒813 

第二次查詢都比較快因為mysql中是有緩存區(qū)的所以增大緩存區(qū)的大小可以解決很多查詢的優(yōu)化,真可謂緩存無處不在啊在程序開發(fā)中也是層層都是緩存.

查詢數(shù)據(jù),代碼如下:

1. --第一條開始查詢 
2.    SELECT * FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒 
3.    SELECT * FROM ipdatas LIMIT 1,10 ; 15ms 
4.    
5.   -- 第10000條開始查詢 
6.    SELECT * FROM ipdatas ORDER BY id ASC LIMIT 10000,10 ; 266毫秒 
7.    SELECT * FROM ipdatas LIMIT 10000,10 ; 16毫秒 
8.    --第500萬條開始查詢 
9.    SELECT * FROM ipdatas LIMIT 5000000,10 ;11.312秒 
10.    SELECT * FROM ipdatas ORDER BY id ASC LIMIT 5000000,10 ; 221.985秒 
11.    --這兩條返回結(jié)果完全一樣,也就是mysql默認(rèn)機制就是id正序然而時間卻大相徑庭 
12.    第5000萬條開始查詢 
13.    SELECT * FROM ipdatas LIMIT 60000000,10 ;66.563秒 (對比下面的測試) 
14.    SELECT * FROM ipdatas ORDER BY id ASC LIMIT 50000000,10; 1060.000秒 
15.    SELECT * FROM ipdatas ORDER BY id DESC LIMIT 17015307,10; 434.937秒 

第三條和第二條結(jié)果一樣只是排序的方式不同但是用時卻相差不少，看來這點還是不如很多的商業(yè)數(shù)據(jù)庫,像oracle和sqlserver等都是中間不成兩邊還是沒問題，看來mysql是開始行越向后越慢，這里看來可以不排序的就不要排序了性能差距巨大,相差了20多倍.

查詢數(shù)據(jù)返回ID列表,代碼如下:

1. 第一條開始查 
2.    select id from ipdatas order by id asc limit 1,10; 31ms 
3.    SELECT id FROM ipdatas LIMIT 1,10 ; 0ms 
4.    
5.    第10000條開始 
6.    SELECT id FROM ipdatas ORDER BY id ASC LIMIT 10000,10; 68ms 
7.    select id from ipdatas limit 10000,10;0ms 
8.    第500萬條開始查詢 
9.    SELECT id FROM ipdatas LIMIT 5000000,10; 1.750s 
10.    SELECT id FROM ipdatas ORDER BY id ASC LIMIT 5000000,10;14.328s 
11.    第6000萬條記錄開始查詢 
12.    SELECT id FROM ipdatas LIMIT 60000000,10; 116.406s 
13.    SELECT id FROM ipdatas ORDER BY id ASC LIMIT 60000000,10; 136.391s 
14.    select id from ipdatas limit 10000002,10; 29.032s 
15.    select id from ipdatas limit 20000002,10; 24.594s 
16.    select id from ipdatas limit 30000002,10; 24.812s  
17.    select id from ipdatas limit 40000002,10; 28.750s  84.719s 
18.    select id from ipdatas limit 50000002,10; 30.797s  108.042s 
19.    select id from ipdatas limit 60000002,10; 133.012s  122.328s 
20.    select * from ipdatas limit 10000002,10; 27.328s 
21.    select * from ipdatas limit 20000002,10; 15.188s 
22.    select * from ipdatas limit 30000002,10; 45.218s 
23.    select * from ipdatas limit 40000002,10; 49.250s   50.531s 
24.    select * from ipdatas limit 50000002,10; 73.297s   56.781s 
25.    select * from ipdatas limit 60000002,10; 67.891s   75.141s 
26.    select id from ipdatas order by id asc limit 10000002,10; 29.438s 
27.    select id from ipdatas order by id asc limit 20000002,10; 24.719s 
28.    select id from ipdatas order by id asc limit 30000002,10; 25.969s 
29.    select id from ipdatas order by id asc limit 40000002,10; 29.860d 
30.    select id from ipdatas order by id asc limit 50000002,10; 32.844s 
31.    select id from ipdatas order by id asc limit 60000002,10; 34.047s 

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

1. SELECT * FROM ipdatas WHERE id IN (10000,100000,500000,1000000,5000000,10000000,2000000,30000000,40000000,50000000,60000000,67015297);  --Vevb.com 

耗時0.094ms

可見in在id上面的查詢可以忽略不計畢竟是6000多萬條記錄，所以為什么很多l(xiāng)ucene或solr搜索都返回id進行數(shù)據(jù)庫重新獲得數(shù)據(jù)就是因為這個,當(dāng)然lucene/solr+mysql是一個不錯的解決辦法這個非常適合前端搜索技術(shù),比如前端的分頁搜索通過這個可以得到非常好的性能.還可以支持很好的分組搜索結(jié)果集,然后通過id獲得數(shù)據(jù)記錄的真實數(shù)據(jù)來顯示效果真的不錯,別說是千萬級別就是上億也沒有問題,真是吐血推薦啊.

總結(jié)了,最關(guān)鍵的一句是,網(wǎng)上的改法可以參考一下，暫時解決問題,代碼如下:

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;

很多問題大家可根據(jù)自身情況來分析優(yōu)化mysql查詢語句.