今天收到运营同学的一个 SQL,有点复杂,尤其是这个 SQL explain 都很长时间执行不出来,于是我们后台团队帮忙解决这个 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 = '我刚刚分享的订单编码'
group by r.share_code) b
where a.share_code = b.share_code and a.type = 1
首先,我们发现,直接 EXPLAIN 这个 SQL 也很慢,也就是可能某些子查询被实际执行了导致。所以,第一步我们先将其中的子查询拆解出来,逐步分析,即:
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 = '我刚刚分享的订单编码'
group by r.share_code
EXPLAIN 这个 SQL,执行很快,我们发现结果是:
奇了怪了,怎么 t_order 这张表的扫描就成为全扫描了?这张表的索引是正常的呀,主键就是 id。
根据官方文档,可以知道有如下几个原因
虽然以上都不是我们这里要讨论的情况,但是这里还是提一些我们为了避免出现全扫描的优化:
ANALYZE TABLE
,来确保分析器的统计数据的准确性。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)) {
// We can use only index tree
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);
}
这个不能设置太小,否则会出现可以走多个索引但是走到实际扫描行数最多的索引。
现在没办法了,EXPLAIN 已经不够我们分析出问题了,只能进一步求助 optimizer_trace 了。不直接用 optimizer_trace 的原因是,optimizer_trace 必须完整的执行 SQL 之后,才能获取到所有有用的信息。
## 打开 optimizer_trace
set session optimizer_trace="enabled=on";
## 执行 SQL
select .....
## 查询 trace 结果
SELECT trace FROM information_schema.OPTIMIZER_TRACE;
通过 trace 结果我们发现,实际执行的 SQL 是:
SELECT
各种字段
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.
idUSING utf8mb4 ) )
.我们知道,字段外套一层函数这种条件匹配,是走不到索引的,例如:date(create_time) < "2021-8-1"
是不能走索引的,但是 create_time < "2021-8-1"
是可以的。不同类型之间列的比较,也走不到索引,因为 MySQL 会自动套上类型转换函数。这也是 MySQL 的语法糖经常带来的误用。
这个 t_order_rel
的默认编码和其他表不一样,由于某些字段使用了 emoji 表情,所以建表的时候整个表默认编码使用了 utf8mb4。而且这个表仅仅是记录使用,没有 OLTP 的业务,只有一些运营同学使用的 OLAP 场景。所以一直没有发现这个问题。
修改字段编码后,SQL 终于不是全扫描了。同时以后要注意: