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 un