mysql

场景

环境

Mysql
InnoDB

主键：id
唯一索引：user_id,order_no
查询索引：update_time

查询最新的一条

select * from t_table where user_id = '177' order by update_time desc limit 1 

Explain

关于索引

根据Explain，可以看到数据查询走了唯一索引

关于排序

• 根据Explain，可以看到Using filesort

关于filesort的出现

• 第一步：根据where条件查询出来数据

• 第二步：如果存在排序，判断排序规则是否和查询出来数据的顺序关系

  • 使用了索引查询，基于相同的索引内字段排序

    • 顺序相同，直接返回

  • 使用了索引查询，基于该索引外的字段，或其他索引进行排序

    • 顺序不同

• 第三步：顺序不同的情况下，执行filesort逻辑

  • 借助额外的存储空间暂存查询出来的数据（内存，文件）

  • 在此空间内基于排序字段进行排序

    • 如果排序字段有索引，基于索引排序
    • 如果无索引，全表扫描
  • 返回排序结果

关于filesort的性能影响

• 结果集的大小

  • 需要额外的空间和时间进行排序

    • sort_buffer_size 判断使用内存还是磁盘文件

      • 外部磁盘文件排序使用归并排序，会创建一系列临时文件，最后合并
      • 存在io消费

    • 排序会将需要返回字段放入额外空间

      • 全字段排序，空间占用高，但是只需要查询一次，排序后返回
      • 超过空间阈值，使用排序字段和rowid进行排序，然后合并后再通过主键索引，查询后，组合最终结果，相当于结果数据集，执行了2次查询

• 排序字段是否索引

  • 非索引字段，需要进行全表扫描

• 排序字段是否区分度高（分布均匀）

  • 需要更多的空间资源进行排序

优化方案

• 结果集较大，或者排序字段分布不均匀

  • 分区分段进行

• 使用覆盖索引，避免filesort的使用

  • 查询和排序使用相同索引

• 如果发现索引使用错误时

  • 强制覆盖索引

实际操作

• 新建覆盖索引

  查询索引2: user_id,order_no,update_time

• explain，未发生filesort
  

思路

• 判断返回的数据集大小和查询的频繁程度
• 排序字段增加索引
• 使用覆盖索引
• 结果集大小可控的情况下，程序侧使用代码排序（内存排序），避免数据库压力

本文由 Ivan Dong 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: Nov 2, 2023 at 10:43 am