怎么提高MySQL Limit查询的性能

怎么提高MySQL Limit查询的性能?我们主要是在mysql limit上下功夫了,当然还有其它的像对数据表,数据库服务器配置等,但我们作为程序只只要在mysql查询语句的性能上进行优化即可了.

有个几千万条记录的表on MySQL 5.0.x,现在要读出其中几十万万条左右的记录,常用方法,依次循环,代码如下:

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语法,代码如下:

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

LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

为了与 PostgreSQL 兼容,MySQL 也支持句法：LIMIT # OFFSET #,代码如下:

1. mysql>SELECT*FROMtableLIMIT5,10;//检索记录行6-15
2. //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为-1
3. 代码如下复制代码
4. mysql>SELECT*FROMtableLIMIT95,-1;//检索记录行96-last
5. //phpfensi.com
6. //如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMITn等价于LIMIT0,n
7. 代码如下复制代码
8. mysql>SELECT*FROMtableLIMIT5;
9. //检索前5个记录行

MySQL的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降,同样是取10条数据,下面两句就不是一个数量级别的,代码如下:

select * from table limit 10000,10

select * from table limit 0,10

文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据,根据他的数据,明显要好于直接使用limit。

这里我具体使用数据分两种情况进行测试,offset比较小的时候,代码如下:

1. select*fromtablelimit10,10
2. //多次运行，时间保持在0.0004-0.0005之间
3. Select*FromtableWherevid＞=(SelectvidFromtableOrderByvidlimit10,1)limit10
4. //多次运行，时间保持在0.0005-0.0006之间，主要是0.0006
5. 结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。
6. offset大的时候：
7. select*fromtablelimit10000,10
8. //多次运行，时间保持在0.0187左右
10. Select*FromtableWherevid＞=(SelectvidFromtableOrderByvidlimit10000,1)limit10
11. //多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优

下面我们来看个mysql千万级数据分页的方法,也是基于limit的,我们来做一个测试ipdatas表,代码如下:

1. CREATETABLE`ipdatas`(
2. `id`INT(11)NOTNULLAUTO_INCREMENT,
3. `uid`INT(8)NOTNULLDEFAULT'0',
4. `ipaddress`VARCHAR(50)NOTNULL,
5. `source`VARCHAR(255)DEFAULTNULL,
6. `track`VARCHAR(255)DEFAULTNULL,
7. `entrance`VARCHAR(255)DEFAULTNULL,
8. `createdtime`DATETIMENOTNULLDEFAULT'0000-00-0000:00:00',
9. `createddate`DATENOTNULLDEFAULT'0000-00-00',
10. PRIMARYKEY(`id`),
11. KEY`uid`(`uid`)
12. )ENGINE=MYISAMAUTO_INCREMENT=67086110DEFAULTCHARSET=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条数据,代码如下:

1. SELECTCOUNT(id)FROMipdatas;
2. SELECTCOUNT(uid)FROMipdatas;
3. SELECTCOUNT(*)FROMipdatas;

首先这两个全表数据查询速度很快,mysql中包含数据字典应该保留了数据库中的最大条数.

查询索引条件,代码如下:

1. SELECTCOUNT(*)FROMipdatasWHEREuid=1;返回结果时间:2分31秒594
2. SELECTCOUNT(id)FROMipdatasWHEREuid=1;返回结果时间:1分29秒609
3. SELECTCOUNT(uid)FROMipdatasWHEREuid=1;返回结果时间:2分41秒813

第二次查询都比较快因为mysql中是有缓存区的所以增大缓存区的大小可以解决很多查询的优化,真可谓缓存无处不在啊在程序开发中也是层层都是缓存.

查询数据,代码如下:

1. --第一条开始查询
2. SELECT*FROMipdatasORDERBYidDESCLIMIT1,10;31毫秒
3. SELECT*FROMipdatasLIMIT1,10;15ms
5. --第10000条开始查询
6. SELECT*FROMipdatasORDERBYidASCLIMIT10000,10;266毫秒
7. SELECT*FROMipdatasLIMIT10000,10;16毫秒
8. --第500万条开始查询
9. SELECT*FROMipdatasLIMIT5000000,10;11.312秒
10. SELECT*FROMipdatasORDERBYidASCLIMIT5000000,10;221.985秒
11. --这两条返回结果完全一样,也就是mysql默认机制就是id正序然而时间却大相径庭
12. 第5000万条开始查询
13. SELECT*FROMipdatasLIMIT60000000,10;66.563秒(对比下面的测试)
14. SELECT*FROMipdatasORDERBYidASCLIMIT50000000,10;1060.000秒
15. SELECT*FROMipdatasORDERBYidDESCLIMIT17015307,10;434.937秒

第三条和第二条结果一样只是排序的方式不同但是用时却相差不少，看来这点还是不如很多的商业数据库,像oracle和sqlserver等都是中间不成两边还是没问题，看来mysql是开始行越向后越慢，这里看来可以不排序的就不要排序了性能差距巨大,相差了20多倍.

查询数据返回ID列表,代码如下:

1. 第一条开始查
2. selectidfromipdatasorderbyidasclimit1,10;31ms
3. SELECTidFROMipdatasLIMIT1,10;0ms
5. 第10000条开始
6. SELECTidFROMipdatasORDERBYidASCLIMIT10000,10;68ms
7. selectidfromipdataslimit10000,10;0ms
8. 第500万条开始查询
9. SELECTidFROMipdatasLIMIT5000000,10;1.750s
10. SELECTidFROMipdatasORDERBYidASCLIMIT5000000,10;14.328s
11. 第6000万条记录开始查询
12. SELECTidFROMipdatasLIMIT60000000,10;116.406s
13. SELECTidFROMipdatasORDERBYidASCLIMIT60000000,10;136.391s
14. selectidfromipdataslimit10000002,10;29.032s
15. selectidfromipdataslimit20000002,10;24.594s
16. selectidfromipdataslimit30000002,10;24.812s
17. selectidfromipdataslimit40000002,10;28.750s84.719s
18. selectidfromipdataslimit50000002,10;30.797s108.042s
19. selectidfromipdataslimit60000002,10;133.012s122.328s
20. select*fromipdataslimit10000002,10;27.328s
21. select*fromipdataslimit20000002,10;15.188s
22. select*fromipdataslimit30000002,10;45.218s
23. select*fromipdataslimit40000002,10;49.250s50.531s
24. select*fromipdataslimit50000002,10;73.297s56.781s
25. select*fromipdataslimit60000002,10;67.891s75.141s
26. selectidfromipdatasorderbyidasclimit10000002,10;29.438s
27. selectidfromipdatasorderbyidasclimit20000002,10;24.719s
28. selectidfromipdatasorderbyidasclimit30000002,10;25.969s
29. selectidfromipdatasorderbyidasclimit40000002,10;29.860d
30. selectidfromipdatasorderbyidasclimit50000002,10;32.844s
31. selectidfromipdatasorderbyidasclimit60000002,10;34.047s

至于SELECT * ipdatas order by id asc 就不测试了 大概都在十几分钟左右,可见通过SELECT id 不带排序的情况下差距不太大,加了排序差距巨大,下面看看这条语句,代码如下:

1. SELECT*FROMipdatasWHEREidIN(10000,100000,500000,1000000,5000000,10000000,2000000,30000000,40000000,50000000,60000000,67015297);--phpfensi.com

耗时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查询语句.