制作网站的图片素材,鞍山网站建设工作室,软件生成器手机版,ui网页设计学院MySQL 深度分页优化
理解总结#xff1a;
分页使用limit #xff0c;前提是要排序好的数据#xff0c;这时候#xff0c;就推荐使用带索引的字段排序#xff0c;因为索引是天然有序的#xff0c;不需要像是无序的字段一样#xff0c;全表扫描#xff0c;如果太大的话…MySQL 深度分页优化
理解总结
分页使用limit 前提是要排序好的数据这时候就推荐使用带索引的字段排序因为索引是天然有序的不需要像是无序的字段一样全表扫描如果太大的话还filesort 利用文件排序排序完成之后才能分页很慢。但是如果分页过深的话比如limit100万仍然无需要查询到100万数据中间有大量的io操作回表查询其它字段这时候考虑用上子查询先查到100万位置的往后10条数据直接用id主键查因为没有回表直接索引查所以很快然后再关联10条数据取得完整的数据。
举例
1. 没有查询条件没有排序
耗时0.613s
select id,m_id, name, identity_no, address, create_time, modify_time from t1 limit 1000000, 20;加上主键排序
耗时0.41
**select** id,m_id, name, identity_no, address, create_time, modify_time **from** t1 **order** **by** id limit 1000000, 20;加上主键排序使用了主键索引天然有序所以只读取前n条数据所以更快。
2. 带排序-排序字段没有索引
select id,m_id, name, identity_no, address, create_time, modify_time
from t1
order by create_time desc
limit 10000, 20;耗时2秒左右
select id,m_id, name, identity_no, address, create_time, modify_time
from t2
order by create_time desc
limit 10000, 20;与t1基本相同只是加了索引耗时0.9s左右
对比没有索引的表全表扫描排序用到filesort 。有索引的话可以利用索引排序limit 的话扫描的数据有少。
3. 排序字段有索引但是分页很深从100w开始取20条。
select id,m_id, name, identity_no, address, create_time, modify_time
from t2
order by create_time desc
limit 1000000, 20;很慢没有走索引因为MySQL优化器发现这条sql查询超过一定的比例就会自动转成全表扫描。
加force index(idx)强制走索引。有效果但是不明显。
结论即使有索引再深一点的分页也会有问题要避免
5. 解决方案
联表子查询
-- 改为
SELECT id, m_id, NAME, identity_no, address, create_time, modify_time
FROM t2
JOIN ( SELECT id FROM t2 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );变成0.7s原来15s。
-- 在t1执行
SELECT id, m_id, NAME, identity_no, address, create_time, modify_time
FROM t1
JOIN ( SELECT id FROM t1 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );这个也很快2.8s。原来18s
分析
直接通过索引树就能拿到查询字段的值索引快的原因是子查询查询的方式减少了回表查询操作进而减少了大量的回表IO因为高效。
参考https://juejin.cn/post/6985478936683610149
