1. 测试 limit start, count 分页语句
select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒
select * from product limit 400000, 20 3.229秒
select * from product limit 866613, 20 37.44秒
2. limit分页性能优化
SELECT * FROM product WHERE ID >= (select id from product limit 866613, 1) limit 20; 0.2秒
另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id; 查询时间也很短
3. 复合索引优化方法
数据表collect (id, title, info, vtype),vtype添加索引。
select id from collect where vtype=1 limit 90000,10; 8-9秒
添加复合索引(vtype,id),where第一位,主键第2位。
select id from collect where vtype=1 limit 90000,10; 0.04秒