万答#16，MySQL为什么"错误"选择代价更大的索引

ywz888

发布于 2022-8-25 11:01

浏览

0收藏

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;1.
2.
3.
4.
5.
6.
7.
8.
9.

利用 mysql_random_data_load 写入一万行数据：

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

查看执行计划：

[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: NULL1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

可以看到优化器的确选择了 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;1.
2.
3.
4.
5.
6.
7.
8.
9.

再查看执行计划：

[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: NULL1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

我们利用 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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

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

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

...
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
...1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

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

从执行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 struct1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.

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
...1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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

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

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

Enjoy GreatSQL :)

文章转载自公众号：GreatSQL社区

分类

其他

标签

GreatSQL

已于2022-8-25 11:01:19修改

51CTO

51CTO博客

51CTO学堂

万答#16，MySQL为什么"错误"选择代价更大的索引

订阅鸿蒙技术特刊，精选内容抢先看