美文网首页
【精】MySql性能(8)— order by与limit的爱恨

【精】MySql性能(8)— order by与limit的爱恨

作者: 小胖学编程 | 来源:发表于2022-04-29 21:40 被阅读0次

本文前提,比较复杂的查询语句,不是单单理论上的覆盖索引实现延迟关联可以解决的场景。

本文主要讨论:

  1. order by 对分页的影响:
    1.1 order by 索引字段;
    1.2 order by 非索引字段(Using filesort);
  2. 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
  1. 借助索引有序性进行排序,优化【性能瓶颈3】,也防止浅分页下的【性能瓶颈1】会拉取所有记录;
  2. 使用嵌套子查询的方式优化【性能瓶颈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 业务上的优化

  1. 产品维度对分页游标进行约束,例如Google

    image
  2. 修改接口,每次返回scroll(即当前滚动id)。但不支持页码的跳转:select * from table where id>滚动id limit 200

相关阅读

本文前提:查询条件比较复杂,不能单纯的在子查询中使用覆盖索引(即不能实现延迟关联)。延迟关联详见:MySql性能(5)—覆盖索引与延迟关联(优化深分页查询)

相关文章

网友评论

      本文标题:【精】MySql性能(8)— order by与limit的爱恨

      本文链接:https://www.haomeiwen.com/subject/bdbsyrtx.html