本文前提,比较复杂的查询语句,不是单单理论上的覆盖索引实现延迟关联可以解决的场景。
本文主要讨论:
- order by 对分页的影响:
1.1 order by 索引字段;
1.2 order by 非索引字段(Using filesort); - order by 非索引字段下:浅分页和深分页的区别
字段 | 关系 |
---|---|
id | 自增主键 |
user_id | 二级索引 |
type | 普通字段 |
create_time | 普通字段 |
1. 排序对浅分页的影响
1.1 存在排序条件(借助索引有序性)
无影响,性能好
创建索引:user_id、create_time
select * from table where user_id=10001 and type=1 order by create_time limit 100;
- 当user_id相同时,create_time是有序的,借助create_time的有序性,只需要读取100条记录即可。
1.2 存在排序条件(不走索引)
极大影响,性能极差。此时explain中出现Using filesort
select * from table where user_id=10001 and type=1 order by create_time desc limit 100;
image.png
- 分页出现性能瓶颈的点有两个(此时深分页的性能等同浅分页):
- 将所有数据读取到内存时,若内存空间不足,会使用临时磁盘;
- 将内存中所有字段排序时,若内存空间不足,会使用临时磁盘;
2. 浅分页一定性能好吗?
2.1 当存在Using filesort时
此时浅分页性能也不好
浅分页【性能差】:select * from table where user_id=10001 and type=1 order by create_time desc limit 100;
注意:user_id=10001 and type=1条件后得到的记录依旧非常多。
深分页【性能差】:select * from table where user_id=10001 and type=1 order by create_time desc limit 10000,100;
当分页中存在Using filesort
时,那么一定会将所有记录都读取到内存中,进行统一排序,然后选择出limit的记录。
2.2 当不存在Using filesort时
此时浅分页性能较好。
浅分页【性能好】:select * from table where user_id=10001 and type=1 limit 100;
- 深分页【性能查差】:
select * from table where user_id=10001 and type=1 limit 10000,100;
瓶颈:深分页要多读取数据到内存中,故性能差。
3 如何优化复杂的深分页
3.1 方式一:优化sql
select * from table where user_id=10001 and type=1 order by create_time desc limit 10000,100;
image.png
- 借助索引有序性进行排序,优化【性能瓶颈3】,也防止浅分页下的【性能瓶颈1】会拉取所有记录;
- 使用嵌套子查询的方式优化【性能瓶颈2】,即在子查询中只查询id;
优化后的语句:
select * FROM table INNER JOIN (
SELECT id FROM table b WHERE user_id=1001 and type=1 order by id DESC LIMIT 10000,100) h
on a.id=h.id
image.png
- 注意id是自增主键,所以
order by create_time
等效于order by id
,而记录就是默认以id的顺序来存储的。就可以借助索引有序性来完成排序; - 子查询中查到的是id,这样可以防止深分页查询到记录太多,导致的临时文件存储记录的场景;
注意:依旧存在【性能瓶颈1】,在深分页场景下性能依旧偏忧。
3.2 方式二:新建create_time索引
select * from table where user_id=10001 and type=1 order by create_time desc limit 100;
此时存在两个索引:user_id和create_time,此时mysql将使用create_time来完成查询。
image.png在浅分页中性能不错。
注意:依旧存在【性能瓶颈1】,在深分页场景下性能依旧偏忧。
3.3 业务上的优化
-
产品维度对分页游标进行约束,例如Google
image -
修改接口,每次返回scroll(即当前滚动id)。但不支持页码的跳转:select * from table where id>滚动id limit 200
相关阅读
本文前提:查询条件比较复杂,不能单纯的在子查询中使用覆盖索引(即不能实现延迟关联)。延迟关联详见:MySql性能(5)—覆盖索引与延迟关联(优化深分页查询)
网友评论