浅谈慢SQL优化

在下uptown
136 阅读3分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第5天，点击查看活动详情

MySQL系列文章

分析SQL

搜集sql

image.png

  • 按平均耗时倒序排序，取前10个进行优化

查看执行计划

  • 通过explain查看sql执行计划

关键字解释
table表名
type连接的数据类型，由好到差const、eq_ref、ref、range、index、all
const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次
eq_ref：对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
range：只检索给定范围的行,使用一个索引来选择行。
index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小
all：对于每个来自于先前的表的行组合，进行完整的表扫描。
rows估算扫描行数
extrausing index condition：搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
key适用的索引

image.png

  • 主要注意几个地方

    • key：执行器选择的索引

    • rows：估算的扫描数据行数

缺少索引

看完执行计划之后判断是否缺少索引就很明朗了，直接看explain语句返回结果的key，如果为空则说明没有走索引。

创建索引要本着以结合where条件区分度最高的原则创建，考虑以下几个方面

联合索引最左匹配原则

  • 最左前缀匹配：mysql会一直向右匹配直到遇到范围查询
  • 联合索引结合索引结构推断，索引会在范围查询断掉
  • 最左优先，在检索数据时从联合索引的最左边开始匹配
  • 遇到范围查询(>、<、between、like)比较就会停止
  • 举例：
    • 创建一个 index_magor_class(magor,class) 的联合索引，那么它的索引树就是下图的样子
    • image
    • 我们查询的where 条件如果只传入了班级，是走不到联合索引的，但是如果只传了学院编号，是可能会走到联合索引的。（为什么说可能，MYSQL的执行计划和查询的实际执行过程并不完全吻合，比如你数据库数据量很少，可能直接全量遍历速度更快，就不走索引了）

索引覆盖

  • 结合业务场景适当考虑索引覆盖

  • 普通的索引查询步骤为，现根据索引定位到主键，再根据主键去查找数据行(回表操作)

  • 指查询列在索引列中，不需要回表操作

索引失效

  • 时间类型data_format会导致时间字段索引失效

  • 可以用date(ctime) < str_to_date('2019-12-30', '%Y-%m-%d')

  • 对索引字段使用计算操作或函数失效，类似&运算等

  • 使用like %key%使索引失效，大数据量全文检索尽量使用es

  • or关键字前后没有同时使用索引

  • 联合索引最左匹配原则顺序不符合规则

  • 索引字段使用is null 或 is not null

  • join语句字段编码不一致导致引擎走错索引

  • case when不走索引

业务分析

深度分页

  • 尽量在产品层上规避掉此类需求，比如操作日志，除了出大问题谁会看自己几月前甚至几年前的操作。
  • 规避不了的话就要考虑深度分页方案
  • limit n, m; n代表偏移量通常在业务系统需要page_no * page_size如果偏移量很大如何优化
    • 参考es的深度分页策略，滚动分页。 利用id连续返回上次查询的最大记录(偏移量)，基于上一次查询偏移量作为where条件
    • 业务中限制页数
    • 先通过索引列拿到主键，通过主键回表查详情
select a.* from emp a, (select id from emp limit 1000000, 10) b where a.id = b.id

是否需要select *

  • 查询字段会占用网络带宽，也会拖慢sql执行，所以尽量避免select *情况，按需查询
avatar
文章
阅读
粉丝