今天，我们的运维团队联系了我们，说有一个复杂的 SQL 查询让他们很困扰。这个查询如此复杂，甚至运行 EXPLAIN 都需要很长时间才能执行！我们的后端团队介入帮助解决这个 SQL 性能问题，在这个过程中，我们偶然发现了一个深深隐藏在我们系统中的生产问题。
有问题的 SQL 查询#
这是引发一切的查询：
select
a.share_code,
a.generated_time,
a.share_user_id,
b.user_count,
b.order_count,
a.share_order_id,
b.rewarded_amount
from t_risk_share_code a,
(select count(distinct r.user_id) user_count,
count(distinct r.order_id) order_count,
s.rewarded_amount,
r.share_code
from t_order s,t_order_rel r
where r.order_id = s.id and r.type = 1 and r.share_code = 'recently_shared_order_code'
group by r.share_code) b
where a.share_code = b.share_code and a.type = 1
第一个危险信号是，即使在这个查询上运行 EXPLAIN 也非常慢，这表明一些子查询实际上在规划阶段被执行。所以我们的第一步是分解子查询并逐段分析：
select count(distinct r.user_id) user_count,
count(distinct r.order_id) order_count,
max(s.rewarded_amount),
r.share_code
from t_order s,t_order_rel r
where r.order_id = s.id and r.type = 1 and r.share_code = 'recently_shared_order_code'
group by r.share_code
当我们在这个隔离的子查询上运行 EXPLAIN 时，它执行得很快，但结果令人困惑：
等等，什么？为什么 t_order 表在进行全表扫描？这个表有适当的索引，id 作为主键！
根本原因分析#
根据官方 MySQL 文档，MySQL 可能选择全表扫描的原因有几个：
表太小 - 使用索引不值得。但这不是我们的情况；两个表都包含数千万条记录。
没有适合 WHERE 或 ON 条件的索引 - 也不是我们的情况。两个表都有适合 WHERE 和 ON 条件的索引（尽管这里所有条件都在 WHERE 子句中，MySQL 会在执行期间将其优化为 JOIN ON + WHERE）。
索引分析显示大多数表值会被命中 - 当 MySQL 通过索引分析确定无论如何都需要将表的大多数页加载到内存中以进行最终数据检索时，直接扫描整个表实际上比首先将索引加载到内存以获取匹配行，然后无论如何加载大多数表页更快。从性能角度来看，这是有道理的。然而，在我们的 SQL 中，
t_order_rel表根据 WHERE 条件只会返回几十条记录，而t_order与t_order_rel具有一对多关系，所以这不应该命中太多记录。列的低基数（唯一性） - 基数是不同值的数量除以总行数，最大值为 1。对于 InnoDB，此值不是实时计算的，可能不准确，特别是当列值更新导致页内行位置更改时。然而，对于 DISTINCT 或主键列，这不需要计算，因为它总是 1。低基数类似于情况 #3 - 会命中太多行。由于我们的 SQL 使用主键，这不适用于这里。
虽然这些情况都不符合我们的情况，但以下是我们用来避免全表扫描的一些优化策略：
定期统计更新：为了使 SQL 执行计划分析器更准确（解决情况 #4），我们在低流量期间定期运行
ANALYZE TABLE以确保分析器统计数据的准确性。强制索引使用：考虑到数据库分片以及数据库 SQL 执行计划并不总是完美的，可能选择错误的索引，我们通常在 OLTP 查询中添加
FORCE INDEX以强制使用特定索引。这是使用基于中间件的分片解决方案（如 sharding-jdbc）或原生分布式数据库（如 TiDB）时的常见陷阱。MySQL 配置：我们设置
--max-seeks-for-key = 10000（默认值非常大）。这限制了 SQL 执行计划分析器认为在使用索引时可能扫描的行数。原理很简单，如源代码所示：
double find_cost_for_ref(const THD *thd, TABLE *table, unsigned keyno,
double num_rows, double worst_seeks) {
// 比较分析的扫描行数与 max_seeks_for_key，取较小值
// 这意味着 SQL 分析器的结论对于索引扫描不会超过 max_seeks_for_key
num_rows = std::min(num_rows, double(thd->variables.max_seeks_for_key));
if (table->covering_keys.is_set(keyno)) {
// 我们可以只使用索引树
const Cost_estimate index_read_cost =
table->file->index_scan_cost(keyno, 1, num_rows);
return index_read_cost.total_cost();
} else if (keyno == table->s->primary_key &&
table->file->primary_key_is_clustered()) {
const Cost_estimate table_read_cost =
table->file->read_cost(keyno, 1, num_rows);
return table_read_cost.total_cost();
} else
return min(table->cost_model()->page_read_cost(num_rows), worst_seeks);
}
此值不应设置得太低，因为它可能导致系统在多个索引可用时选择扫描最多行的索引。
使用 Optimizer Trace#
当 EXPLAIN 不足以进行分析时，我们不得不转向 optimizer_trace。我们不将 optimizer_trace 作为首选，因为它需要 SQL 完全执行后才能提供所有有用信息。
## 启用 optimizer_trace
set session optimizer_trace="enabled=on";
## 执行 SQL
select .....
## 查询跟踪结果
SELECT trace FROM information_schema.OPTIMIZER_TRACE;
通过跟踪结果，我们发现实际执行的 SQL 是：
SELECT
various_fields
FROM
`t_order_rel` `r`
JOIN `t_order` `s`
WHERE
(
( `r`.`order_id` = CONVERT ( `s`.`id` USING utf8mb4 ) )
AND ( `r`.`type` = 1 )
AND ( `r`.`share_code` = 'B2MTB6C' )
)
啊哈！两个表的字段具有不同的字符编码！这导致 JOIN ON 条件包装了字符编码转换：CONVERT ( s.id USING utf8mb4 )。我们知道，当字段在条件匹配中被函数包装时，无法使用索引。例如：date(create_time) < "2021-8-1" 不能使用索引，但 create_time < "2021-8-1" 可以。不同列类型之间的比较也不能使用索引，因为 MySQL 会自动用类型转换函数包装它们。这是由 MySQL 的语法糖引起的常见误用。
t_order_rel 表与其他表具有不同的默认编码。由于某些字段使用表情符号表达式，整个表默认使用 utf8mb4 编码创建。此外，此表仅用于日志记录目的，没有 OLTP 业务操作 - 只有运维团队成员使用的一些 OLAP 场景。这就是为什么这个问题被忽视这么长时间。
修复字段编码后，SQL 终于停止进行全表扫描。展望未来，我们将更加注意：
- 在数据库级别指定默认编码，不为表指定默认编码，只为需要特殊编码的单个字段指定编码
- 注意 JOIN 和 WHERE 比较两侧的类型一致性，以避免阻止索引使用
这是一个很好的提醒，有时最有影响力的问题就是那些隐藏在显而易见的地方的问题！