MySQL 索引

目录

什么是索引？

索引的分类

按数据结构分类

按物理存储分类

按字段特性分类

主键索引

唯一索引

普通索引

前缀索引

按字段个数分类

联合索引

联合索引范围查询

索引下推

联合索引进行排序

什么时候需要/不需要创建索引？

什么时候适用索引？

什么时候不需要创建索引？

优化索引的方法

前缀索引优化

覆盖索引优化

主键索引最好是自增的

索引最好设置为 NOT NULL

防止索引失效

总结

---

什么是索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象地说就是索引是数据的目录。

所谓的存储引擎，就是如何存储数据、如何为数据建立索引和如何更新、查询数据等技术的实现方法。MySQL存储引擎有 MyISAM、InnoDB 、Memory ，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

下图是MySQL的结构图，索引和数据就是位于存储引擎中：

---

索引的分类

索引可以按照四个角度来分类索引。

• 按 [数据结构] 分类：B+tree 索引、Hash 索引、Full-text 索引。
• 按 [物理存储] 分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按 [字段特性] 分类：主键索引、唯一索引、普通索引、前缀索引。
• 按 [字段个数] 分类：单列索引、联合索引。

---

按数据结构分类

从数据结构的角度来看，MySQL常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

每一种存储引擎支持的索引类型不一定相同：

InnoDB是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL存储引擎采用最多的索引类型。

在创建表时，InnoDB存储引擎会根据不同场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键
• 如果没有主键，就选择第一个不包含 NULL值的唯一列作为聚簇索引的索引键
• 在上面两个都没有的情况下，InnoDB将会自动生成一个隐式自增 id 列作为聚簇索引的索引键

其他索引都属于辅助索引，也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

为了方便理解 B+Tree 索引的存储和查询的过程，我们通过一个简单的例子说明B + Tree索引在存储数据中的具体实现。

先创建一张商品表，id 为主键，如下：

CREATE TABLE `product`  (
  `id` int(11) NOT NULL,
  `product_no` varchar(20)  DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

商品表里，有这些行数据：

这些行数据，存储在 B+ Tree 索引时是什么样子的？

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子结点只存放索引，而且每个节点里的数据是按主键顺序存放的，每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

主键索引的 B+Tree 如图所示（图中的叶子节点是单项列表。但实际上是双向链表）

通过主键查询商品数据的过程

比如，我们执行了下面这条查询语句：

select * from product where id= 5;

这条语句使用了主键索引查询 id 号为 5 的商品。查询的过程是这样的，B+Tree会自顶向下逐层进行查找：

• 将 5 与根节点的索引数据（1,10,20）比较，5 在 1 和 10 之间，所以根据 B+Tree 的搜索逻辑，找到第二层的索引数据（1,4,7）
• 在第二层的索引数据（1,4,7）中进行查找，因为 5 在 4 和 7 之间，所以找到的第三层的索引数据（4,5,6）
• 在叶子节点的索引数据（4,5,6）中进行查找，然后找到了索引值为 5 的行数据

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当做一次磁盘I/O操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作

B+Tree 存储千万级别的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以 B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询的效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4 次。

通过二级索引查询商品数据的过程

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree 的叶子节点里
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

这里将其那面商品表中的product_no（商品编码）字段设置为二级索引，那么二级索引的 B+Tree 如下图(图中叶子节点为单向列表，实际上是双向列表)

其中非叶子节点的key值是 product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。

如果使用 product_no 二级索引查询商品，如下查询语句：

select * from product where product_no = '0002';

会先检索二级索引中的 B+Tree 的索引值（商品编码，product_no），找到对应叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫做回表，也就是说要查两个 B+Tree 才能查询到数据。

即，通过二级索引查询数据，会先查询二级索引构成的 B+Tree（结构与主键索引B+Tree类似，唯一区别是叶子节点为主键id），通过二级索引B+Tree 查询到 主键 id 后，会进行回表，查询主键索引的B+Tree，主键索引B+Tree叶子节点存储的为表数据值。 

不过，当查询的数据是能在二级索引的 B+Tree的叶子节点里查询到，这时就不用再查主键索引值，比如执行下面这条语句：

select id from product where product_no = '0002';

这种在二级索引的 B+Tree 能查询到结果的过程叫做 [覆盖索引] ，也就是需要查一个 B+Tree 就能找到数据。

为什么MySQL InnoDB 选择 B+Tree 作为索引的数据结构？

1、B+Tree VS B Tree

B+Tree 只在叶子节点存储数据，而 B树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外 ，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B树无法做到这一点。

2、B+Tree VS 二叉树

对于有N个叶子节点的B+Tree，其搜索复杂度为 O()，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中，d值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+Tree的高度依然维持在 3 - 4 层左右，也就是说一次数据查询操作只需要做 3 - 4 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点个数只能是 2 个，意味着其搜索复杂度为 O(logN) ，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

3、B+Tree VS Hash 

Hash在做等值查询的效率非常快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值查询，这也是 B+Tree 索引要比 Hash 索引有着更广泛使用场景的原因。

---

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据

所以在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据，这个过程叫做回表。

---

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许由空值。

在创建表的时候，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

普通索引允许被索引的数据列包含重复的值。比如说。因为有人可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此变得更有效率；二是MySQL会在有新纪录插入数据表的时候，自动检查新记录的这个字段的值是否已经在某个记录的字段里面出现过；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

在创建表时，创建唯一索引的方式如下：

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。

普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBYcolumn）中的数据列创建索引。

在创建表时，创建普通索引的方式如下：

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))
); 

---

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引、比如主键索引；
• 建立在多列上的索引称为联合索引

联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。

比如，将商品表中的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 = 2
• where c = 3
• where b = 2 and c = 3

上面这些查询条件之所以会失效，是因为（a,b,c）联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序的，局部相对有序，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

eg：（图中叶子节点之间是单向链表，但实际上是双向链表，画错）

可以看到，a 是全局有序的（1, 2, 2, 3, 4, 5, 6, 7 ,8），而 b 是全局是无序的（12，7，8，2，3，8，10，5，2）。因此，直接执行where b = 2这种查询条件没有办法利用联合索引的，利用索引的前提是索引里的 key 是有序的。

只有在 a 相同的情况下 ，b 才是有序的，这个有序状态是局部的，因此，执行 where a=2 and b=7 是 a 和 b 字段都可以用到的联合索引，也就是联合索引生效。

联合索引范围查询

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree情况。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到 [范围查询] 就会停止匹配。也就是范围查询的字段可以用到联合查询，但是在范围查询字段的后面的字段无法用到联合索引。

那么，到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢？

Q1：select * from t_table where a > 1 and b = 2，联合索引（a,b）哪个字段用到了联合索引的B+Tree?

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

但是符合 a > 1 条件的二级索引记录的范围里， b字段的值是无序的。比如前面图的联合索引的 B+Tree 里，下面三条记录的 a 字段的值都符合 a > 1查询条件，而 b 字段的值是无序的：

• a 字段值为 5 的记录，该记录的 b 字段值为 8；
• a 字段值为 6 的记录，该记录的 b 字段值为 10；
• a 字段值为 7 的记录，该记录的 b 字段值为 5；

因此我们不能根据查询条件 b = 2 来进一步减少需要扫描的记录数量（b 字段无法利用联合索引进行索引查询）。

所以在执行 Q1这条查询的时候，对应的扫描区间是（2， + ），形成该扫描区间的边界条件是 a > 1，与 b = 2 无关。

因此，Q1这条查询语句只有 a 字段用到了联合索引进行查询，而 b 字段并没有使用到联合索引。

我们也可以在执行计划中的key_len知道这一点，在使用联合索引进行查询的时候，通过key_len我们可以知道优化器具体使用了多少字段的搜索条件来形成扫描区间的边界条件。

举个例子：a 和 b 都是 int 类型且不为 NULL 的字段，那么 Q1 这条查询语句执行计划如下，可以看到 key_len 为 4 字节（如果字段允许为 NULL，就在字段类型占用的字节数上加 1 ，也就是 5 字节），说明只有 a 字段用到了联合索引进行索引查询，而且可以看到，即使 b 字段没用到联合索引，key 为 idx_a_b，说明 Q1 查询语句使用了 idx_a_b 联合索引。

通过Q1查询语句可以知道，a 字段使用了 > 进行范围查询，联合索引的最左匹配原则在遇到 a 字段的范围查询 ( > ) 后就停止匹配了。因此 b 字段并没有使用到联合索引。

Q2：select * from t_table where a>=1 and b=2，联合索引（a,b）哪一个字段用到了联合索引的 B+Tree?

Q1 和 Q2 的查询语句很像，唯一的区别就是 a 字段的查询条件 [ 大于等于 ]。

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 >=1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 >=1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a>= 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

虽然在符合 a>= 1条件的二级索引记录的范围里，b字段的值是 [无序] 的，但是对于符合 a = 1 的二级索引记录的范围里， b 字段的值是 [ 有序 ] 的（因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序）。

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围（b 字段可以利用联合索引进行索引查询）。也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。

所以，Q2这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

我们也可以在执行计划中的key_len知道这一点，在使用联合索引进行查询的时候，通过key_len我们可以知道优化器具体使用了多少字段的搜索条件来形成扫描区间的边界条件。

通过Q2查询语句我们可以知道，虽然 a 字段使用了 >= 进行范围查询，但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询( >= ) 后就停止匹配了，b 字段还是可以用到联合索引的。

Q3：SELECT * from t_table where a between  2 and 8 and b = 2，联合索引（a,b）哪一个字段用到了联合索引的 B+Tree?

Q3查询条件中 a between 2 and 8 的意思是查询 a 字段的值在 2 和 8 之间的记录。不同的数据库对 between ... and 处理方式是有差异的。在MySQL中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and <= 。而有的数据库则不包含 value1 和 value2 边界值（类似于 > and <）

这里我们只考虑MySQL。由于MySQL的 between 包含 vaue1 和 value2 边界值，所以类似于 Q2 查询语句，因此 Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

Q4：select * from t_user where name like 'j%' and age = 22,联合索引（name,age）哪一个字段用到了联合索引的 B+Tree。

由于联合索引（二级索引）是先按照 name字段的值排序的，所以前缀为 'j' 的 name 字段的二级索引记录都是相邻的，于是在进行索引扫描的时候，可以定位到符合前缀为 'j' 的 name 字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的 name 前缀不为 'j' 为止。

 所以 a 字段可以在联合索引的 B+Tree 中进行索引查询，形成的扫描区间是（'j' , 'k'）。

PS: j 是闭区间，如下图：

虽然在符合前缀为 'j' 的 name 字段的二级索引记录的范围里， age 字段的值是 [ 无序 ] 的，但是对于符合 name = j 的二级索引记录的范围里，age 字段的值是 [有序] 的（因为对于联合索引，是先按照 name 字段的值排序的，然后在 name 字段的值相同的情况下，再按照 age 字段的值进行排序）

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 name 字段值为 'j' 时，可以通过 age = 22 条件减少需要扫描的二级索引记录范围（age 字段可以利用联合索引进行索引查询）。也就是说，从符合 name = 'j' and age = 22 条件的第一条记录时开始扫描，而不需要从第一个 name 为 j 的记录开始扫描。如下图：

所以，Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

该例子中：

• name 字段的类型是 varchar(30) 且不为 NULL，数据表使用了 utf8mb4 字符集，一个字符集为 utf8mb4 的字符是 4 个字节，因此 name 字段的实际数据最多占用的存储空间长度是 120 字节（30 * 4），然后因为 name 是变长类型的字段，需要再加 2 字节（用于存储该字段实际数据的长度值），也就是 name 的 key_len 为 122；
• age 字段的类型是 int 且不为 NULL，key_len 为 4；

Q4 查询语句的执行计划如下，可以看到 key_len 为 126 字节，name 的 key_len 为 122，age 的 key_len 为 4，说明优化器使用了 2 个字段的查询条件来形成扫描区间的边界条件，也就是 name 和 age 字段都用到了联合索引进行索引查询。

通过 Q4查询语句：虽然 name 字段使用了 like 前缀匹配进行范围查询，但是联合索引的最左匹配原则并没有在遇到 name 字段的范围查询后就停止了，age 字段还是可以用到联合索引的。

综上，联合索引的最左匹配原则，在遇到范围查询 （如 >,<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、between、like 前缀匹配的范围查询，并不会停止匹配，前面四个例子已说明。

索引下推

对于联合索引（a,b），在执行 select * from table where a >1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

• 在 MySQL 5.6 之前，只能从ID（主键值）开始一个个回表，到 [主键索引] 上找出数据行，再对比 b 字段值。
• 而 MySQL 5.6 引入的索引下推优化，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。（先根据 a >1 条件联合索引数据，不过接下来不是直接进行回表操作，而是根据 b = 2 这个条件对索引出的数据进行筛选，将符合条件的索引对应的id进行回表扫描，最后将找到的行数返回给server层）

当查询语句的执行计划里，出现了 Extra 为 Using index condition ，那么说明使用了索引下推的优化。

索引区分度

另外，建立联合索引的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排放在前面，这样区分度大的字段越有可能被更多的SQL使用到。

区分度就是某个字段 column 不同值的个数 [除以] 表的总行数，计算公式如下：

比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引的靠前的位置，而UUID这类字段就比较适合做索引或排在联合索引列的靠前的位置。

因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为MySQL还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（一般是30%）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引进行排序

比如下面这条SQL，如何通过索引来提高查询效率呢？

select * from order where status = 1 order by create_time asc

有的同学认为，单独给 status 建立一个索引就可以了。

但更好的方式是给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现 Using filesort。

所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

---

什么时候需要/不需要创建索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大
• 会降低表的增删改查的效率，因为每次增删改索引，B+树为了维护索引有序性，都需要进行动态维护。

所以，索引不是万能钥匙，它也是根据场景来使用的。

什么时候适用索引？

• 字段有唯一性限制的，比如商品编码
• 经常用于 where 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引
• 经常用于 group by 和 order by 的字段，这样查询的时候就不需要再去做一次排序了，因为我们已经知道了建立索引后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

• where 条件，group by ，order by 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引时会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据，这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描
• 表数据太少的时候，不需要创建索引
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree 的有序性，那么就需要频繁地重建索引，这个过程是会影响数据库性能的。 

---

优化索引的方法

常见的优化索引的方法：

• 前缀索引优化
• 覆盖索引优化
• 主键索引最好是自增的
• 防止索引失效

前缀索引优化

使用前缀索引时为了减少索引字段的大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度，在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减少索引项的大小。

不过，前缀索引有一定的局限性，例如：

• order by 无法使用前缀索引
• 无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引是指SQL中 query 的所有字段，在索引 B+Tree的叶子节点上都能找到那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即 [商品 ID、名称、价格] 作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以、使用覆盖索引的好处就是，不需要查询出整行记录的所有信息，也就减少了大量的 I/O操作。

主键索引最好是自增的

InnoDB 创建主键索引默认为聚簇索引，数据被存放在 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一个新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新纪录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其他数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

eg：假设某个数据页中的数据是1、3、5、9，且数据页满了，现在准备插入一个数据 7 ，则需要把数据页分割为两个数据页：

出现页分裂时，需要将一个页的记录移动到另一个页，性能会受到影响，同时页空间的利用率下降，造成存储空间的浪费。

而如果记录是顺序插入的，例如插入数据 11 ，则只需要开辟新的数据页，也就不会发生页分裂：

因此，在使用InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

索引最好设置为 NOT NULL

为了更好地利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

• 原因一：索引列存在 NULL 就会导致优化器在所索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时， count 会省略值为 NULL 的行。
• 原因二：NULL值是一个没有意义的值，但是它会占用物理空间，所以会带来存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL值列表，如下图：

防止索引失效

用上了索引并不意味着查询的时候会使用到索引，所以我们得知道哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率很低。

发生索引失效的情况：

• 当我们使用左或者右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 where 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

上面说的都是常见的索引失效的场景，实际过程中，可能会出现其他的索引失效场景，这时我们就需要查看执行计划，通过执行计划显示的数据判断查询语句是否使用了索引。

如下图，就是一个没有使用索引，并且是一个全表扫描的查询语句。

对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用到的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度
• rows 表示扫描的数据行数
• type 表示数据扫描类型，我们需要重点看这个

type 字段就是描述找到所需数据时使用的扫描方式是什么。常见扫描类型的执行效率从低到高的顺序为：

• ALL （全表扫描）
• index （全索引扫描）
• range（索引范围扫描）
• ref（非唯一索引扫描）
• eq_ref（唯一索引扫描）
• const（结果只有一条的主键或者唯一索引扫描）

这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销还是很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用了 <、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

---

总结