【精】MySql性能（8）— order by与limit的爱恨
本文前提，比较复杂的查询语句，不是单单理论上的覆盖索引实现延迟关联可以解决的场景。
本文主要讨论：
- 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条记录即可。
image.png
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的记录。
image.png
2.2 当不存在Using filesort时
此时浅分页性能较好。
浅分页【性能好】：select * from table where user_id=10001 and type=1 limit 100;
image.png
- 深分页【性能查差】：
select * from table where user_id=10001 and type=1 limit 10000,100;
image.png
瓶颈：深分页要多读取数据到内存中，故性能差。
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）—覆盖索引与延迟关联（优化深分页查询）
