近期在看MySQL查询一个表时是怎样选择索引的。总结下索引结构与索引选择算法。
一 数据准备
1.1 建表语句
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
`e` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_idx_c_d` (`c`,`d`),
KEY `c` (`c`),
KEY `idx_c_d` (`c`,`d`),
KEY `idx_d_c` (`d`,`c`),
KEY `idx_e_c_d` (`e`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1.2 查询SQL
select * from t where c = 5 and d = 10;
1.3 explain结果
根据explain的结果,可知MySQL最终选择了uni_idx_c_d
这个索引。 那么接下来看下MySQL为什么会选择这个索引。
二 索引结构
CREATE命令在建表的同时,会创建如下所示的索引结构。共5个索引(KEY
),每个索引又由若干个KEY_PART_INFO
组成。KEY_PART_INFO
是对表字段的一种封装,包括字段偏移,字段名等信息。 以索引idx_c_d
为例 ,它包括用户定义的两个字段c
和d
,还包括主键id
。这有可能是因为此索引不是唯一索引,所以加主键用来区分。
索引结构示意图
三 索引选择数据结构
update_ref_and_keys
函数会根据索引结构与查询条件创建一个Key_use
数组。一个Key_use
用来代表table.column = value
这样一种判定条件,当然这里会填充索引相关信息。例如下面数组中第一个元素,代表着使用索引uni_idx_c_d
中的c
字段来处理c = 5
这个查询条件。 原始的数组是按字段来排列的。
根据索引结构与查询条件创建的Key_use数组
按表,索引,字段等条件排序后,会得到如下图所示的数组。
根据索引,字段排序后的Key_use数组
四 最左匹配原则
MySQL在处理联合索引时遵循最左匹配原则。实现其实比较简单。在处理最后两个Key_use
结构时,发现c
和d
并不是索引idx_e_c_d
的第一个字段,所以将这两个Key_use
直接舍弃掉。最终结构如下所示。此时余下备选的索引只有四个了。
最左匹配
五 索引选择
find_best_ref
函数根据最终的Key_use
数组进行索引选择。 它将索引分为以下四种类型。可以看到聚簇索引优化级是最高的,其次是唯一索引,再次是非唯一索引,最后是全文索引。
enum idx_type
{
CLUSTERED_PK,
UNIQUE,
NOT_UNIQUE,
FULLTEXT
};
函数实现比较复杂,但基本思想是选用成本最低的索引,成本的计算包括索引类型(聚簇,唯一,非唯一,全文),索引字段的匹配程度(全匹配/最区前缀匹配)等。
根据索引字段的匹配程序,索引c
被舍弃掉。根据索引优先级,索引idx_c_d
与idx_d_c
被舍弃掉。因此最终选择了索引uni_idx_c_d
。