索引

索引分类

  1. 按数据结构
    B+tree索引，Hash索引，Full-text索引
  2. 按物理存储
    聚簇索引（主键索引），二级索引（辅助索引）
  3. 按字段特性
    主键索引，唯一索引，普通索引，前缀索引
  4. 按字段个数
    单列索引，联合索引

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

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

其他索引都属于辅助索引，也被称为二级索引。创建的主键索引和二级索引默认使用的都是B+Tree数据结构。

B+Tree

假如有以下的一张商品表

1
2
3
4
5
6
7
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+树是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据时按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双链表。

B+树相对比与B树来说，最大的优势在于查询效率高，因为即使在数据量很大的情况，查询一个数据的磁盘I/O次数比较低。

通过主键查询商品数据的过程
以下语句

1
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 的行数据。

通过二级索引查询商品数据的过程
主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
    我这里将前面的商品表中的 product_no （商品编码）字段设置为二级索引，那么二级索引的 B+Tree 如下图

以下语句

1
select * from product where product_no = '0002';

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

如果查询的数据能在二级索引的B+树的叶子节点中查询到，就不用回表了，比如我们查的就是id值。这种也叫覆盖索引

按字段特分类

  1. 主键索引
    一张表最多只能有一个主键索引，索引列的值不允许有空值。
  2. 唯一索引
    唯一索引建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，允许有空值。
  3. 前缀索引
    建立在字符类型（char、varchar、binary、varbinary）字段的前几个字符上的索引。目的是减少索引占用的储存空间，提升查询效率。
  4. 普通索引
    建立在普通字段上的索引。

联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。例如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)。联合索引的B+树示意图如下：

使用联合索引时，存在 最左匹配原则，如果不遵循，联合索引就会失效。

比如，如果创建了一个 (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 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。例如下面（a,b）联合索引的示意图

联合索引的范围查询
联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、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，那么说明使用了索引下推的优化。

不需要索引 & 索引失效

不需要索引

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

索引失效

对索引使用左或者左右模糊匹配

  • 做模糊匹配： like %xx
  • 左右模糊匹配： like %xx%

对索引使用函数

索引优化