vectorC++索引_MySQL索引与索引选择

近期在看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。