一、背景
1：表现
最近seller平台查询退货的时候老是出现报错,出现频繁报警,去监控平台上看了一下:
两台机器都出现慢sql,然而这个时候还不能断定就是我们的sql有问题,出现慢sql有可能是数据库抖动导致读延迟比较高,也有可能sql确实存在优化空间。接着去看一下数据库的监控:
虽然会有少量的读延迟,但是整体上看来比较平稳,可以排除并不是数据库抖动导致,我们继续看一下应用机器的日志:
rpc服务默认超时时间为5S,服务器出现大量的服务超时,基本可以断定是sql的执行时间超过了5S,慢sql无疑。
2：数据现状
目前退货单表有千万级别数据,索引基本该有的都有,从日志埋点可以看到有些查询竟然用36S:
二、分析与优化
1：老sql分析
mybatis中sql片段:
代码语言：javascript复制<!-- 查询退货列表 -->
<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语句:
代码语言：javascript复制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,我们做了以下改造:
代码语言：javascript复制<!-- 查询退货列表 -->
<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:
代码语言：javascript复制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排序要回行。
- 延迟关联,对于大分页场景,数据筛选操作在非聚簇索引树上完成,数据取值逻辑通过小批量回行或者主键关联(也是回行)完成。
四、总结
对于单表数据量比较大带来的查询慢问题,有很多优化方式,目前业内比较常用的有:
- 数据接ES
将业务数据同步到ES中,从而提供高性能的查询效果,需要解决存量数据导入和增量数据追评问题,并且是伪实时,对于实时性要求比较高的场景不可取,并且还要定期检查是否存在同步失败的问题,否则会导致数据不一致,也就失去了接入的价值和意义。
- 分库分表
将原来的大宽表拆分成多张表,把数据分散存储,可以解决索引树过大带来的查询问题,但是需要业务兼容和开发成本,并且带来比较棘手的分页查询问题。
- 数据归档
将非活跃数据(比如6个月以前)归档,转储到其他表或者存储工具中,只提供短期活跃数据的查询,大部分场景业务上无法接受。
- sql优化
根据具体的查询场景和诉求,对sql进行改造,或者适当调整索引,从而短期提供单表高性能响应,并且此方案没有额外的机器成本,只需要对底层sql检索语句进行优化,当然这也需要更高的认知成本。
对于以上几种查询性能优化方案,正所谓仁者见仁智者见智,不同的场景、不同的团队思考为题的角度和侧重点不尽相同,具体选择哪一种方式需要视场景而定,但是sql优化是短期内并且是首先需要考虑的优化方案。
