MySQL分组分页优化_mysql数据库千万级别数据的查询优化和分页测试

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

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获得数据记录的真实数据来显示效果真的不错,别说是千万级别就是上亿也没有问题,真是吐血推荐啊. 上面的内容还没有进行有条件的查询仅仅是一些关于orderby和limit的测试,请关注我的下一篇文件对于条件查询的1亿数据检索测试