MySQL为什么"错误"选择代价更大的索引

头像
GreatSQL社区
    阅读 6 分钟

    欢迎来到 GreatSQL社区分享的MySQL技术文章，如有疑问或想学习的内容，可以在下方评论区留言，看到后会进行解答

    MySQL优化器索引选择迷思。

    高鹏（八怪）对本文亦有贡献。

    1. 问题描述

    群友提出问题，表里有两个列c1、c2，分别为INT、VARCHAR类型，且分别创建了unique key。

    SQL查询的条件是 WHERE c1 = ? AND c2 = ?，用EXPLAIN查看执行计划，发现优化器优先选择了VARCHAR类型的c2列索引。

    他表示很不理解，难道不应该选择看起来代价更小的INT类型的c1列吗？

    2. 问题复现

    创建测试表t1：

    [root@yejr.run]> CREATE TABLE `t1` (
  `c1` int NOT NULL AUTO_INCREMENT,
  `c2` int unsigned NOT NULL,
  `c3` varchar(20) NOT NULL,
  `c4` varchar(20) NOT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `k3` (`c3`),
  UNIQUE KEY `k2` (`c2`)
) ENGINE=InnoDB;

    利用 mysql_random_data_load 写入一万行数据：

    mysql_random_data_load -h127.0.0.1 -uX -pX yejr t1 10000

    查看执行计划：

    [root@yejr.run]> EXPLAIN SELECT * FROM t1 WHERE
 c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: const
possible_keys: k3,k2
          key: k3
      key_len: 82
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

    可以看到优化器的确选择了 k3 索引，而非"预期"的 k2 索引，这是为什么呢？

    3. 问题分析

    其实原因很简单粗暴：优化器认为这两个索引选择的代价都是一样的，只是优先选中排在前面的那个索引而已。

    再建一个相同的表 t2，只不过把 k2、k3 的索引创建顺序对调下：

    [root@yejr.run]> CREATE TABLE `t2` (
  `c1` int NOT NULL AUTO_INCREMENT,
  `c2` int unsigned NOT NULL,
  `c3` varchar(20) NOT NULL,
  `c4` varchar(20) NOT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `k2` (`c2`),
  UNIQUE KEY `k3` (`c3`)
) ENGINE=InnoDB;

    再查看执行计划：

    [root@yejr.run]> EXPLAIN SELECT * FROM t2 WHERE
 c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: const
possible_keys: k2,k3
          key: k2
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

    我们利用 EXPLAIN ANALYZE 来查看下两次执行计划的代价对比：

    -- 查看t1表执行计划代价
[root@yejr.run]> EXPLAIN ANALYZE SELECT * FROM t1 WHERE
  c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
*************************** 1. row ***************************
EXPLAIN: -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

-- 查看t2表执行计划代价
[root@yejr.run]> EXPLAIN ANALYZE SELECT * FROM t2 WHERE  c2 = 1755950419 AND c3 = 'MichaelaAnderson'\G
*************************** 1. row ***************************
EXPLAIN: -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

    可以看到，很明显代价都是一样的。

    再利用 OPTIMIZE_TRACE 查看执行计划，也能看到两个SQL的代价是一样的：

    ...
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
...

    所以，优化器认为选择哪个索引都是一样的，就看哪个索引排序更靠前。

    从执行SELECT时的debug trace结果也能佐证：

    -- 1、 T1表，k3索引在前面
  PRIMARY KEY (`c1`),
  UNIQUE KEY `k3` (`c3`),
  UNIQUE KEY `k2` (`c2`)
  
T@2: | | | | | | | | opt: (null): starting struct
T@2: | | | | | | | | opt: table: "`t1`"
T@2: | | | | | | | | opt: field: "c3"   (C3在前面，因此最后使用k3)
T@2: | | | | | | | | >convert_string
T@2: | | | | | | | | | >alloc_root
T@2: | | | | | | | | | | enter: root: 0x40a8068
T@2: | | | | | | | | | | exit: ptr: 0x4b41ab0
T@2: | | | | | | | | | <alloc_root 304
T@2: | | | | | | | | <convert_string 2610
T@2: | | | | | | | | opt: equals: "'Louise Garrett'" 
T@2: | | | | | | | | opt: null_rejecting: 0
T@2: | | | | | | | | opt: (null): ending struct
T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1
T@2: | | | | | | | | opt: (null): starting struct
T@2: | | | | | | | | opt: table: "`t1`"
T@2: | | | | | | | | opt: field: "c2"
T@2: | | | | | | | | opt: equals: "22896242"
T@2: | | | | | | | | opt: null_rejecting: 0
T@2: | | | | | | | | opt: null_rejecting: 0
T@2: | | | | | | | | opt: (null): ending struct
T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1
T@2: | | | | | | | | opt: (null): starting struct
T@2: | | | | | | | | opt: table: "`t1`"
T@2: | | | | | | | | opt: field: "c2"
T@2: | | | | | | | | opt: equals: "22896242"
T@2: | | | | | | | | opt: null_rejecting: 0
T@2: | | | | | | | | opt: (null): ending struct
T@2: | | | | | | | | opt: ref_optimizer_key_uses: ending struct
T@2: | | | | | | | | opt: (null): ending struct

-- 2、 T2表，k2索引在前面
  PRIMARY KEY (`c1`),
  UNIQUE KEY `k2` (`c2`),
  UNIQUE KEY `k3` (`c3`)
  
T@2: | | | | | | | | opt: (null): starting struct
T@2: | | | | | | | | opt: table: "`t2`"
T@2: | | | | | | | | opt: field: "c2" （C2在前面因此使用k2索引）
T@2: | | | | | | | | opt: equals: "22896242"
T@2: | | | | | | | | opt: null_rejecting: 0
T@2: | | | | | | | | opt: (null): ending struct
T@2: | | | | | | | | opt: Key_use: optimize= 0 used_tables=0x0 ref_table_rows= 18446744073709551615 keypart_map= 1
T@2: | | | | | | | | opt: (null): starting struct
T@2: | | | | | | | | opt: table: "`t2`"
T@2: | | | | | | | | opt: field: "c3"
T@2: | | | | | | | | >convert_string
T@2: | | | | | | | | | >alloc_root
T@2: | | | | | | | | | | enter: root: 0x40a8068
T@2: | | | | | | | | | | exit: ptr: 0x4b41ab0
T@2: | | | | | | | | | <alloc_root 304
T@2: | | | | | | | | <convert_string 2610
T@2: | | | | | | | | opt: equals: "'Louise Garrett'"
T@2: | | | | | | | | opt: null_rejecting: 0
T@2: | | | | | | | | opt: (null): ending struct
T@2: | | | | | | | | opt: ref_optimizer_key_uses: ending struct
T@2: | | | | | | | | opt: (null): ending struct

    4. 问题延伸

    到这里，我们不禁有疑问，这两个索引的代价真的是一样吗？

    就让我们用 mysqlslap 来做个简单对比测试吧：

    -- 测试1：对c2列随机point select
mysqlslap -hlocalhost -uroot -Smysql.sock --no-drop --create-schema X -i 3 --number-of-queries 1000000 -q "set @xid = cast(round(rand()*2147265929) as unsigned); select * from t1 where c2 = @xid" -c 8
...
    Average number of seconds to run all queries: 9.483 seconds
...


-- 测试2：对c3列随机point select
mysqlslap -hlocalhost -uroot -Smysql.sock --no-drop --create-schema X -i 3 --number-of-queries 1000000 -q "set @xid = concat('u',cast(round(rand()*2147265929) as unsigned)); select * from t1 where c3 = @xid" -c 8
...
    Average number of seconds to run all queries: 10.360 seconds
...

    可以看到，如果是走 c3 列索引，耗时会比走 c2 列索引多出来约 7% ~ 9%（在我的环境下测试的结果，不同环境、不同数据量可能也不同）。

    看来，MySQL优化器还是有必要进一步提高的哟 ：）

    测试使用版本：GreatSQL 8.0.25（MySQL 5.6.39结果亦是如此）。

    Enjoy GreatSQL :)

    本文由博客一文多发平台 OpenWrite 发布！
    mysql数据库sql
    GreatSQL社区
    8 声望6 粉丝

    GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

    « 上一篇
    技术分享 | 在MySQL对于批量更新操作的一种优化方式
    下一篇 »
    故障案例 | 主从复制环境中tokudb引擎报错排查过程

    引用和评论

    推荐阅读
    头像
    GreatSQL内存消耗异常排查攻略：从系统到应用层面的深入分析

    GreatSQL社区

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2.2k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.3k

    头像
    数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

    超神经HyperAI赞 3阅读 1.4k

    头像
    使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

    MongoDB数据平台赞 1阅读 12.9k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.4k

    头像
    MongoDB 8.0已全面可用

    MongoDB数据平台阅读 12.1k

    0 条评论
    得票最新