MySQL 索引篇详解
提到数据库索引，我想你并不陌生，在日常工作中会经常接触到。比如某一个 SQL 查询比较慢，分析完原因之后，你可能就会说“给某个字段加个索引吧”之类的解决方案。但到底什么是索引，索引又是如何工作的呢？今天就让我们一起来聊聊这个话题吧。
一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。
MySQL 有哪些索引类型？
我们可以按照四个角度来分类索引。
- 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类：聚簇索引（主键索引）、非聚簇索引（二级索引）。
- 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类：单列索引、联合索引。
接下来，按照这些角度来说说各类索引的特点。
按数据结构分类
从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。
在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：
B+树
- 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
- 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
- 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。
B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。
主键索引的 B+Tree 如图所示：
- 数据组织形式：Innod 8存储引擎的主键索引B+树的非叶子节点只存放索引键值和指向子节点的指针，不存储
实际的数据，这里对应到MVSOL中就是索引，叶子节点存储索引键值和行数据，所以InnodB存储引擎的主键
索引属于聚簇索引 - 叶子节点链表：所有叶子节点通过指针相连，形成一个双向链表，支持快速的顺序访问和范围查询.
- 平衡树结构：所有叶子节点在同一层上，树的高度平衡，保证任何数据记录的査找、插入和删除操作的路径长
度相同，稳定性好
其他索引的情况可以看这篇：
按物理存储分类
从物理存储的角度来看，索引分为聚簇索引（主键索引）、非聚簇索引（二级索引）。
聚簇索引和非聚簇索引有什么区别?
这两者之间的最主要的区别是 B+ 树的叶子节点存放的内容不同：
聚簇索引
- 聚簇索引的 B+ 树叶子节点存放的是主键值+完整的记录；
非聚簇索引
- 非聚簇索引的 B+ 树叶子节点存放的是索引值+主键值；
所以，在查询时使用了二级索引
如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。
如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程覆盖索引。
按字段特性分类
从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。
主键索引
主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
在创建表时，创建主键索引的方式如下：
CREATE TABLE table_name (
....
PRIMARY KEY (index_column_1) USING BTREE
);
唯一索引
唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
在创建表时，创建唯一索引的方式如下：
CREATE TABLE table_name (
....
UNIQUE KEY(index_column_1,index_column_2,...)
);
建表后，如果要创建唯一索引，可以使用这面这条命令：
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
普通索引
普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
在创建表时，创建普通索引的方式如下：
CREATE TABLE table_name (
....
INDEX(index_column_1,index_column_2,...)
);
建表后，如果要创建普通索引，可以使用这面这条命令：
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
前缀索引
前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。
在创建表时，创建前缀索引的方式如下：
CREATE TABLE table_name(
column_list,
INDEX(column_name(length))
);
建表后，如果要创建前缀索引，可以使用这面这条命令：
CREATE INDEX index_name
ON table_name(column_name(length));
按字段个数分类
从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。
- 建立在单列上的索引称为单列索引，比如主键索引；
- 建立在多列上的索引称为联合索引；
联合索引
通过将多个字段组合成一个索引，该索引就被称为联合索引。
比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：
CREATE INDEX index_product_no_name ON product(product_no, name);
联合索引(product_no, name) 的 B+Tree 示意图如下
可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。
也就是说，联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。
因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。
最左匹配原则
比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：
- where a=1；
- where a=1 and b=2 and c=3；
- where a=1 and b=2；
需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
所以如果我们创建的是这样一条查询语句
- where b = 1 and c = 3 and a = 1;
那么依然是能用上 （a，b，c）的联合索引的
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
- where b=2；
- where c=3；
- where b=2 and c=3；
上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。
联合索引范围查询
联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。
这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
范围查询有很多种，那到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢？
联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。
索引下推
现在我们知道，对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？
在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。
索引区分度
另外，建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。
区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下：
区分度计算公式：
比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。
因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。
参考文章：
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
