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 MySQL :)
