最近遇到一个问题,mysql分页时,随着页码的增大,查询效率越来越慢
select * from tb_mq_record limit 10, 20; 0.024秒
select * from tb_mq_record limit 100, 20; 0.037秒
select * from tb_mq_record limit 1000, 20; 0.055秒
select * from tb_mq_record limit 10000, 20; 0.120秒
select * from tb_mq_record limit 230000, 20; 50.12秒
通过分析得知,select * 查询所有的列,表中大部分字段是没有索引的,一般主键ID有索引
经过修改测试效率惊人
select id from tb_mq_record limit 88000, 20; 0.54秒
然后可以把分页ID取出并且拼接,再次查询,效率提升很多。
最终的SQL如下:
select * from tb_mq_record where id in (select id from tb_mq_record limit 230000, 20);
===========================
在很多分页的程序中都这样写:
SELECT COUNT(*)
from
`table` WHERE ......; 查出符合条件的记录总数
SELECT * FROM `table` WHERE ...... limit M,N; 查询当页要显示的数据
这样的语句可以改成:
SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE ...... limit M, N;
SELECT FOUND_ROWS();
这样只要执行一次较耗时的复杂查询可以同时得到与不带limit同样的记录条数
第二个 SELECT返回一个数字,指示了在没有LIMIT子句的情况下,第一个SELECT返回了多少行 (若上述的 SELECT语句不包括 SQL_CALC_FOUND_ROWS 选项,则使用LIMIT 和不使用时,FOUND_ROWS() 可能会返回不同的结果)。