从一次sql优化谈mysql索引

叔牙 • 2023-01-02 • 云技术社区 • 283 阅读

一、背景

1：表现

最近seller平台查询退货的时候老是出现报错,出现频繁报警,去监控平台上看了一下:

两台机器都出现慢sql,然而这个时候还不能断定就是我们的sql有问题,出现慢sql有可能是数据库抖动导致读延迟比较高,也有可能sql确实存在优化空间。接着去看一下数据库的监控:

虽然会有少量的读延迟,但是整体上看来比较平稳,可以排除并不是数据库抖动导致,我们继续看一下应用机器的日志:

rpc服务默认超时时间为5S,服务器出现大量的服务超时,基本可以断定是sql的执行时间超过了5S,慢sql无疑。

2：数据现状

目前退货单表有千万级别数据,索引基本该有的都有,从日志埋点可以看到有些查询竟然用36S:

二、分析与优化

1：老sql分析

mybatis中sql片段:

<!-- 查询退货列表 -->
<select id="listByParamsForOms" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from return_request
    where 1=1
    <include refid="Return_Where_Cause"/>
    <if test="sort == 'asc'">
        order by create_time asc
    </if>
    <if test="sort == 'desc'">
        order by create_time desc
    </if>
    limit #{offset}, #{limit}
</select>

使用查询超时的参数映射成具体sql语句:

SELECT
    *
FROM
  return_request
WHERE
  flag = 0
  AND return_type in (-1,0,1)
  AND state in (2,3,5)
  and seller_audit = 2
  and seller_id = xxx
ORDER BY
  create_time DESC
LIMIT 0, 50;

使用explain解析sql执行计划:

使用了seller_id索引,扫描了106184行。

2：新sql改造

基于上述老的查询sql,我们做了以下改造:

<!-- 查询退货列表 -->
<select id="listByParamsForOms" resultMap="BaseResultMap">
    select
        *
    from return_request rr1,
        (select
            id
        from
            return_request
        where
        1=1
        <include refid="Return_Where_Cause"/>
        <if test="sort == 'asc'">
            order by id asc
        </if>
        <if test="sort == 'desc'">
            order by id desc
        </if>
        limit #{offset}, #{limit}) rr2
    where
    rr1.id = rr2.id;
</select>

映射成具体的查询sql:

select
  *
from
  return_request rr1,
  (
  SELECT
    id
  FROM
    return_request
  WHERE
    flag = 0
    AND return_type in (-1,0,1)
    AND state in (2,3,5)
    and seller_audit = 2
    and seller_id = xxx
    and return_order_type in (0)
  ORDER BY
    id DESC
  LIMIT 0, 50) rr2
where
  rr1.id = rr2.id;

解析执行计划:

由于有子查询和关联查询,有三条执行计划,主要看第二条和第三条,第三条也是使用seller_id索引扫描了106184行,

第二条是使用了主键索引扫描1行(直接定位到数据)。

3：优化验证

是骡子是马拉出来遛遛,同样基于日志埋点观察一下执行效果:

890毫秒,没有出现查询超时(根本不会触发5S超时的阈值),问题解决。

三、扯一扯索引

1：基本概念

首先看一下sql查询的执行过程:

客户端先发送一条查询给服务器；
服务器先检查查询缓存，如果命中了缓存，则立刻返回给存储在缓存中的结果，否则进入下一个阶段；
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回客户端。

我们比较关注的是第3,4步,先解析并生成执行计划,然后执行查询,所以一般对于sql优化也基本基于第三步进行。

接着我们先看几个概念:

聚簇索引

聚簇索引也叫聚集索引,对立的叫做非聚簇索引,区别在于聚簇索引叶子节点直接存储数据行,非聚簇索引叶子节点存储主键索引地址,innodb引擎主键默认使用聚簇索引,非主键索引使用非聚簇索引。

回行

回行也叫回表,先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

B+树

B+树在B树的基础上演化而来,是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。和B树最典型的区别是非叶子节点不存储数据,innodb引擎使用B+树维护索引结构。

覆盖索引

何为覆盖索引？说白了就是从索引结构上我们就能拿到想要查询的结果,也就是只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。比如查询只查询主键id。

延迟关联

延迟关联这个概念比较抽象,主要解决大分页的数据筛选问题,可以理解为在数据筛选阶段不去取具体数据,再筛选结束后再去取目标数据。比如一个非主键检索,查询偏移量limit 950,50按创建时间倒序的数据,数据库引擎会执行查询,从非聚簇索引树上回表到聚簇索引树上取出1000条数据,然后排序,最后筛选出第950~1000条数据。延迟关联就是在数据筛选阶段不回表,从非聚簇索引树上取1000条数据,筛选出需要的50条,然后通过主键取聚簇索引树上取数据。目的就是降低不必要的回表和筛选。

2：实例分析

介绍了一些数据库和索引的一些基本概念,那么我们就对开篇的案例优化过程做一下详细分析。

老sql执行过程

解析sql,生成执行计划,选择seller_id索引树执行查询
扫描了10684行记录找到记录
回表从主键索引树取出50条数据,选择50条返回(偏移量从0开始50条,如果偏移量从10000开始那么回行带来的性能问题会放大)

新sql执行过程

解析sql,生成执行计划,子查询选择seller_id索引树执行查询
扫描10684条记录,由于排序字段是主键,排序和筛选操作直接在seller_id索引树上完成,得到50条数据的主键id
关联查询使用主键索引(上一步的结果),直接获取到结果数据(不存在大批量回行)

性能优化点

排序字段,对于大多数单表场景,id主键和create_time都是自动生成,有相同的增长趋势,所以基于create_time排序完全可以使用id代替,性能会有巨大差异,id排序可以直接在索引树上完成,create_time排序要回行。
延迟关联,对于大分页场景,数据筛选操作在非聚簇索引树上完成,数据取值逻辑通过小批量回行或者主键关联(也是回行)完成。