共计 4909 个字符，预计需要花费 13 分钟才能阅读完成。
欢送来到 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 struct4. 问题延长
到这里，咱们不禁有疑难，这两个索引的代价真的是一样吗？
就让咱们用 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 公布！
正文完
