这个大表走索引字段查询的 SQL 怎么就成全扫描了，我TM人傻了

旧城等待， 2022-09-03 12:10 3阅读 0赞

![image][]

今天收到运营同学的一个 SQL，有点复杂，尤其是这个 SQL explain 都很长时间执行不出来，于是我们后台团队帮忙解决这个 SQL 问题，却正好发现了一个隐藏很深的线上问题。

![image][image 1]

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，执行很快，我们发现结果是：  
![image][image 2]

奇了怪了，怎么 t\_order 这张表的扫描就成为全扫描了？这张表的索引是正常的呀，主键就是 id。

![image][image 3]

根据[官方文档][Link 1]，可以知道有如下几个原因

1.  表太小了，走索引不值当的。但我们这里这两张表都非常大，都是千万级别的数据。
2.  对于 WHERE 或者 ON 的条件，没有合适的索引，这也不是我们这里的情况，两张表都针对 WHERE 和 ON 条件有合适的索引（这里查询条件虽然都放到了 WHERE 里面，但是后面的分析我们会知道这个 SQL 会被改成 JOIN ON + WHERE 去执行）。
3.  使用索引列与常数值作比较， MYSQL 通过索引分析出这个覆盖了表中大部分的值，其实就是分析出**命中的行最后回表拉取数据的时候，表的文件中大部分页都要被加载到内存中进行读取，这样的话与其说先将索引加载到内存中获取命中列，不如直接扫描整个表，反正最后也是差不多将表的文件中大部分页都加载到内存中**。这种情况很显然，不走索引反而会更快。我们这个 SQL 中，t\_order\_rel 表实际上根据 where 条件只会返回几十条数据，t\_order 与 t\_order\_rel 是 1 对多的关系，这里不会命中太多数据的。
4.  这一列值的离散度（Cardinality）太低，离散度就是是不同值的个数除以行数，最大为 1。但是这个值对于 innoDB 引擎来说，并不是实时计算的，可能不准确（**尤其是在这一列的值发生更新导致行在页中的位置发生变化的时候**）.**但是对于 distinct 或者主键列是不用计算的，就是 1**。如果离散度太低，那么其实和第三种情况差不多，会命中过多的行数。这里我们要优化的 SQL 使用的是主键，所以不属于这种情况。

虽然以上都不是我们这里要讨论的情况，但是这里还是提一些我们为了避免出现全扫描的优化：

1.  为了让 SQL 执行计划分析器更准确，针对第四种情况，我们对于某些表可能需要在业务闲时定期执行 `ANALYZE TABLE`，来确保分析器的统计数据的准确性。
2.  由于考虑分库分表，以及有时候数据库 SQL 执行计划总是不完美还是会出现索引走错的情况，我们一般尽量在 OLTP 查询业务上加 force index 强制走一些索引。这在使用基于中间件的分库分表（例如 sharding-jdbc）或者原生分布式数据库（例如 TiDB）过程中，我们经常遇到的坑。
3.  对于 MySQL，我们设置 --max-seeks-for-key = 10000（默认这个值非常大），这样其实就是限制了每次 SQL 执行计划分析器分析出来的走索引可能扫描的行数。其原理非常简单，参考源码：

[sql\_planner.cc][sql_planner.cc]

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);
    }

这个**不能设置太小，否则会出现可以走多个索引但是走到实际扫描行数最多的索引**。

![image][image 4]

现在没办法了，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`.`id`USING utf8mb4 ) )`.我们知道，字段外套一层函数这种条件匹配，是走不到索引的，例如：`date(create_time) < "2021-8-1"` 是不能走索引的，但是 `create_time < "2021-8-1"` 是可以的。**不同类型之间列的比较，也走不到索引，因为 MySQL 会自动套上类型转换函数**。这也是 **MySQL 的语法糖经常带来的误用**。

这个 `t_order_rel` 的默认编码和其他表不一样，由于某些字段使用了 emoji 表情，所以建表的时候整个表默认编码使用了 utf8mb4。而且这个表仅仅是记录使用，没有 OLTP 的业务，只有一些运营同学使用的 OLAP 场景。所以一直没有发现这个问题。

修改字段编码后，SQL 终于不是全扫描了。同时以后要注意：

1.  **数据库指定默认的编码，表不再指定默认编码，同时对于需要使用特殊编码的字段，针对字段指定编码**
2.  **join，where 的时候，注意 compare 两边的类型是否一致，是否会导致不走索引**

> **微信搜索“我的编程喵”关注公众号，每日一刷，轻松提升技术，斩获各种offer**：

![image][image 5]

[image]: /images/20220829/3f9fb40b01cc46ac8dc80d379f47dab6.png
[image 1]: /images/20220829/339ef39a03224cf3a55434b29574f76a.png
[image 2]: /images/20220829/ef130da2e4684ab7b598a0654b29b957.png
[image 3]: /images/20220829/c46bc1b7ae634f369ffdb8375656313f.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html
[sql_planner.cc]: https://github.com/mysql/mysql-server/blob/8.0/sql/sql_planner.cc
[image 4]: /images/20220829/efe9316b808c4cf9bcffa93c27ca7980.png
[image 5]: /images/20220829/3e91ab732dc943579dcf4e5efbf63da3.png