MySQL深入(三):联合索引和覆盖索引

1. 索引分类

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

2. 设计原则

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。
避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

3. 联合索引

3.1 内部B+树结构

a.创建表:

create table t (
a INT,
b INT,
KEY idx_a_b (a,b) // 建立联合索引
) ENGINE = INNODB

b.B+树图

联合索引是指对表上的多个列进行索引,何时需要使用联合索引呢？在讨论这个之前，先来看一下联合索引内部的结构。从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。我们来讨论由两个整型列组成的联合索引，假定两个键值的名称分别为a、b，如下图所示。

从上图得到的信息:

通过叶子节点可以逻辑上顺序地读出所有数据，这里是(1，1)、(1，2)、(2，1)、(2，4)、(3，1)、(3，2)。数据按(a，b)的顺序进行了存放，对a列查询可以使用到索引。
可以看到叶子节点上的b值为1、2、1、4、1、2，显然不是顺序的，因此对于b列的查询不能使用（a，b）的索引。

3.2 实例应用分析

联合索引的另一个好处是可以对第二个键值进行排序

在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，取出最近三次的购买记录，这时使用联合索引可以减少一次排序操作，因为索引本身在叶子节点已经排序了。

a.创建表SQL:

# 创建表
CREATE TABLE `user_buy_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uid` int unsigned NOT NULL,
  `buy_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`) # 添加一个单值索引
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
# 添加记录
INSERT INTO `test`.`user_buy_log` (`uid`, `buy_date`) VALUES (2, '2019-09-27');
INSERT INTO `test`.`user_buy_log` (`uid`, `buy_date`) VALUES (2, '2019-04-18');
INSERT INTO `test`.`user_buy_log` (`uid`, `buy_date`) VALUES (4, '2019-09-23');
INSERT INTO `test`.`user_buy_log` (`uid`, `buy_date`) VALUES (4, '2019-09-13');
INSERT INTO `test`.`user_buy_log` (`uid`, `buy_date`) VALUES (1, '2019-06-22');
INSERT INTO `test`.`user_buy_log` (`uid`, `buy_date`) VALUES (1, '2019-07-19');

b.取出uid=1最近一次的购买记录:

# 分析SQL
explain SELECT * from user_buy_log where uid = 1 order by buy_date LIMIT 1;

c.修改索引:

# 添加联合索引
ALTER TABLE user_buy_log ADD KEY uid_date(`uid`,`buy_date`)

d.再次分析SQL:

4. 覆盖索引

InnoDB存储引擎支持覆盖索引（covering index）或称索引覆盖（indexcoverage），**即从辅助索引中就可以得到查询的字段，就不需要再查询聚集索引中的记录(回表查询)**。

4.1 查找指定字段,避免回表查询

a. 表结构:

CREATE TABLE `user_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uid` int unsigned NOT NULL DEFAULT '0',
  `birth_date` date DEFAULT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `age` int unsigned NOT NULL DEFAULT '0',
  `home` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `uid_date` (`uid`,`birth_date`) USING BTREE
) ENGINE=InnoDB;