乐趣区

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

jiezi

明天收到经营同学的一个 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。

依据官网文档，能够晓得有如下几个起因

  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

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.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：

退出移动版