一条线上慢查询 sql 的原因分析及优化建议
source link: https://blog.51cto.com/defonds/5856652
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
一条线上慢查询 sql 的原因分析及优化建议
精选 原创s_order 表的相关字段定义 DDL:
-
user_id
varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '下单用户ID', -
order_status
tinyint(4) NOT NULL COMMENT '订单状态', -
create_time
datetime NOT NULL COMMENT '创建时间(下单时间)', -
tenant_code
varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商户号', -
order_source
tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单来源 1.小程序 2.支付宝 3.抖音',
该表所有索引定义 DDL:
- PRIMARY KEY (
id
) USING BTREE, - UNIQUE KEY
udx_tenant_orderno
(tenant_code
,order_no
), - KEY
idx_tenant_user_status_source
(tenant_code
,user_id
,order_status
,order_source
), - KEY
idx_tenant_createtime
(tenant_code
,create_time
) USING BTREE, - KEY
idx_tenant_shop_status
(tenant_code
,shop_id
,order_status
)
该表目前数据量为 330 万行。
2. 线上慢查询情况
慢查询统计结果里有 s_order 的一条超过 3 秒钟的 sql 语句:
执行计划如下:
3. 慢查询原因分析
看原 sql 执行计划可以看出,最适合的索引应该是 idx_tenant_createtime,但为何解释器走了索引 idx_tenant_user_status_source 呢?走这个 idx_tenant_user_status_source 索引扫描的行数超过 110 万行,仅仅比全表扫描强那么一丢丢。 好,我们强制它走 idx_tenant_createtime,查看执行计划:
92 万行,达到全表的 28%,这就是该索引失效的罪魁祸首:当有范围检索条件时,解释器发现通过索引扫描的记录数已经超过全表的 10% ~ 30% 时,它会放弃该索引。 也就是说最优解 idx_tenant_createtime 首先被放弃。 现在剩下了 3 个索引:udx_tenant_orderno、idx_tenant_user_status_source、idx_tenant_shop_status,那么为何解释器最终会选择 idx_tenant_user_status_source 呢?我们来看, 强制走 udx_tenant_orderno 的执行计划:
强制走 idx_tenant_shop_status 的执行计划:
对比篇首的 idx_tenant_user_status_source 的扫描行数。。。蜀中无大将,廖化做先锋,强如诸葛亮的解释器也很无奈啊。。。
4. 优化建议
笔者给开发的优化方案是:
- 【继续走 idx_tenant_user_status_source】如果能提前拿到 user_id,检索条件里可以加入 AND user_id in ('3333', '4444')
- 【改走 idx_tenant_createtime】没走 idx_tenant_createtime 是因为索引失效,建议缩小 create_time 的范围让它生效
结果验证一下,方案一的执行计划:
方案二的执行计划:
可见方案一似乎是最优解。但开发反映 user_id 是可以拿到,但不方便——会使相关的业务逻辑变得更加复杂,最终开发选择第二个方案优化掉了该性能 sql。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK