mysql 日志输出中的时间不对

一、   背景 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_requestWHERE  flag = 0  AND return_type in (-1,0,1)  AND state in (2,3,5)  and seller_audit = 2  and seller_id = xxxORDER BY  create_time DESCLIMIT 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) rr2where  rr1.id = rr2.id;

    解析执行计划:

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

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

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

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

三、   扯一扯索引 1：基本概念

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

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

我们比较关注的是第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执行过程

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

新sql执行过程

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

性能优化点

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

总结

    对于单表数据量比较大带来的查询慢问题,有很多优化方式,目前业内比较常用的有:

1数据接ES

    将业务数据同步到ES中,从而提供高性能的查询效果,需要解决存量数据导入和增量数据追评问题,并且是伪实时,对于实时性要求比较高的场景不可取,并且还要定期检查是否存在同步失败的问题,否则会导致数据不一致,也就失去了接入的价值和意义。

2分库分表

    将原来的大宽表拆分成多张表,把数据分散存储,可以解决索引树过大带来的查询问题,但是需要业务兼容和开发成本,并且带来比较棘手的分页查询问题。

3数据归档

    将非活跃数据(比如6个月以前)归档,转储到其他表或者存储工具中,只提供短期活跃数据的查询,大部分场景业务上无法接受。

4sql优化

    根据具体的查询场景和诉求,对sql进行改造,或者适当调整索引,从而短期提供单表高性能响应,并且此方案没有额外的机器成本,只需要对底层sql检索语句进行优化,当然这也需要更高的认知成本。

    对于以上几种查询性能优化方案,正所谓仁者见仁智者见智,不同的场景、不同的团队思考为题的角度和侧重点不尽相同,具体选择哪一种方式需要视场景而定,但是sql优化是短期内并且是首先需要考虑的优化方案。