一条线上慢查询 sql 的原因分析及优化建议

1. 背景

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 语句：

SELECT id, tenant_code, order_no, promote_business_id, shop_id, user_id, is_return AS canReturn, is_part_return AS canPartReturn, order_type, order_source, order_seq, stock_shop_id, performance_type, pickup_shop_id, order_status, order_remark, expect_start_time, expect_end_time, sku_count, goods_count, goods_weight, goods_amount, pre_sale_amount, pre_sale_balance, goods_retail_amount, package_amount, delivery_amount, discount_amount, basic_delivery_amount, time_markup_amount, distance_markup_amount, weight_markup_amount, delivery_disc_amount, package_disc_amount, goods_disc_amount, whole_disc_amount, pay_amount, commission, order_validity, sales_validity, is_delete, refund_status, version, create_time, creator, update_time, modifier FROM s_order WHERE order_status = 1 AND create_time <= DATE_SUB(NOW(), INTERVAL 5 MINUTE) AND tenant_code = '10000006'

执行计划如下：

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。