mysql45讲学习总结（二）—索引

​ 一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。

索引的常见模型

​ 索引实际上就像是字典的目录，当需要查询某个字时，通过目录可以快速定位到某一页，从而快速查找到所需要的数据，不必进行全局遍历，达到提高查询效率的目的，实现上一般都是通过设计某种的数据结构，简单介绍几个常用的数据结构。

1. 哈希表：一种以键值对存储数据的数据结构，类似于Java中的HashMap的实现（当然了jdk8之后是采用数组加红黑树的方式），数据结构为一个数组加链表（有的地方称拉链法），当添加某个值时计算出这个值的HASH值，然后插入到数组对应的链表尾部，这种数据结构对于添加和删除的效率是比较高的，只需要移动一个节点的引用。
2. 有序数组（规则数组）：通过某种规则将数据存入到数组中，在查询时同样可以根据这种规则直接通过下标获取到数据，如果是有序数组，对于范围查询的效率也是比较高的。这种数据结构瓶颈在于扩容以及空间的使用率上，比如现在是有序数组，数组长度为10，假设现在仅有两个值1和50，那么50的这个值存放在哪个位置？如果将数组长度扩大50，空间利用率就极低。
3. 二叉树：二叉树是课本中经典的数据结构了，同样在添加、删除节点时，需要进行平衡。

InnoDB的索引模型

​ InnoDB引擎中采用B+数索引模型，每一个索引都对应着一颗B+树。分为主键索引和非主键索引。

​ 主键索引又称为聚簇索引，主键索引的叶子节点是整行数据。

​ 非主键索引又称为二级索引，非主键索引的叶子节点的内容是主键的值。由下图可知，非主键索引的查询逻辑是通过非主键索引获取到要查询数据的主键，再通过主键索引获取到对应行数据，这个过程称为回表。

InnoDB索引模型

​ 在InnoDB存储引擎中，表都是根据主键顺序以索引的形式存放，这种存储方式的表称为索引组织表。InnoDB使用的索引模型为B+树形索引模型，所以数据都是存储在B+树中。

每一个索引在InnoDB里面都对应着一颗B+树。

假设有一个表，ID为主键，且还有字段k,name，同时k字段上有索引。

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中有5条记录：(ID,K)→ (100,1)、(200,2)、(300,3)、(500,5) 、 (600,6)

主键索引树和k字段索引树如下：(图中主键索引显示为[100, 200]在一页，[300, 500, 600]在一页)

由上图可知：

索引类型分为主键索引(聚簇索引)和非主键(二级索引)索引。

• 主键索引的叶子节点存储的是一整行的记录。
• 非主键索引的叶子节点存储的是主键ID。

某个查询语句使用主键索引和非主键索引的差别在于，非主键索引查询到树节点之后得到叶子节点上的主键ID之后，需要再通过主键索引树再查找一轮，得到主键ID对应的行数据，这个过程称为回表。

索引维护

​ 根据第一小节中的常见的索引模型可知，索引之所以查询速度高，实际上是依赖于索引模型，也就是说在插入数据时，就需要根据索引模型相应的规则进行数据的存储。所以虽然添加索引的查询效率高，但索引的数量并不是越多越好，过多的索引会增加插入数据带来的成本。

以上方索引树的图为例：

• 如果插入的新行ID值为700，则只需要在R5的记录后面插入一条新记录。
• 如果插入的新行ID值为400，则需要将500和600往后挪，空出位置。如果R5所在的数据页满了，则需要申请一个新的数据页，然后将部分数据挪过去，这个过程称为页分裂。
• 如果相邻两页数据由于删除了数据，导致利用率比较低，那么就会出现合并页，这个过程是页分裂的逆过程。

索引概念

还是这个表为例：

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
# 同时插入6条记录
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

执行查询语句：

select * from T where k between 3 and 5

执行流程：

1. 到非主键索引k上搜索k=3的树节点，得到主键ID=100。根据这个ID值，到主键索引树查询到对应行R3。
2. 到非主键索引k上取k=3的下一个值，得到k=5这个树节点，得到主键ID=500。根据这个ID值，到主键索引树查询到对应行R4。
3. 到非主键索引k上取k=3的下一个值，得到k=6这个树节点，不满足where条件，循环结束。

在步骤1和步骤2都有回表的动作，这是因为需要查询的字段在非主键索引k上没有，那么有没有办法避免回表？

覆盖索引

如果将执行语句修改为：

select ID from T where k between 3 and 5

由于这时需要查询的字段ID，就是非主键索引k的叶子节点上能获取到的数据，所以就不需要进行回表的操作，也就达到了减少一次回表查询的动作，从而提升查询效率。

联合索引

所以为了不进行回表，就需要在数节点上存储的数据做文章，就需要通过联合索引。这里的name_age就是联合索引。

假设一个市民信息表：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

如果这时需要用市民的名称查询他的年龄：

select age from tuser where name = “xxx”

如果使用name_age索引，就不需要进行一次回表就可以查出想要的某个名称对应的年龄，当然了这里会出现多条记录的情况。

最左前缀原则

如果将这条查询语句换个查询条件，能否使用这个联合索引呢？

select name from tuser where age = xx

由上图可以看出，联合索引是根据联合索引定义时字段的先后顺序进行排序，也就是这里先根据name排序，再根据age排序。

• 当需求为查询所有名字为”张三“的人，可以快速定位到ID4，然后逐个遍历节点与where条件进行比对。
• 当需求为查询所有名字第一个字为”张”，查询语句为where name like '张%'，也会命中这个索引，查询到第一个符合条件的节点为ID3，然后逐个遍历节点与where条件进行比对。
• 当需求为查询年龄为20的人，就无法使用这个索引。对这个索引来说，是先对name进行排序，在name一致的情况下，对age排序。

综上：查询条件中不一定要全部定义，只要满足最左前缀，就可以利用这个索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

在建立联合索引的时候，如何安排索引内的字段顺序。

这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推

如果查询语句的查询条件部分满足最左前缀原则呢？

以市民表的联合索引(name, age)为例

select * from tuser where name like ‘张 %’ and age=10 and ismale=1;

根据最左前缀的规则，这个语句在搜索树时，只能用到”张”，找到第一个满足条件的记录X，然后根据其他判断条件进行判断，就算只有这样，也是要比全表扫描效率高。

在MYSQL5.6之前，只能从记录X开始一个个回表，到主键索引上找出记录行，在对比字段值。

在MYSQL5.6之后，引入索引下推优化，可以在所有遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推执行过程如下图所示：不会获取联合索引中age的值，在匹配”张“之后，逐个往后遍历，并进行回表取出对应行数据进行查询条件的判断。

采用索引下推的执行过程如下图所示：在匹配”张“之后，会获取age的值与查询条件进行匹配，如果不匹配直接获取下一个节点。所以这里的年龄等于30和20的数据，获取到之后不会进行回表操作。

重建索引

重建非主键索引：

alter table T drop index k;

alter table T add index(k);

重建主键索引：

alter table T drop primary key;

alter table T add primary key(id);

重建非主键索引的做法是合理的，可以达到省空间的目的；但是重建主键索引的过程是不合理的，不论是删除主键还是创建主键，都会将整个表重建，所以第一个语句其实可有可无，单纯执行第二个语句就会对表进行重建。再者可以使用alter table T engine=InnoDB对表进行重建。

表数据删除一半，表文件大小不变？

​ InnoDB表包含两个部分，表结构定义和数据。在MYSQL8.0之前，表结构是存储在以.frm为后缀的文件里。而MYSQL8.0则已经允许将把表结构定义放在系统数据表，实际上表结构定义占用的空间很小，所以占用空间的主要部分就是表数据。

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以.ibd为后缀的文件中。

从MySQL 5.6.6版本开始，它的默认值就是ON。一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

在InnoDB中，数据都是以B+树模型且按页存储，前面也有说到，在进行查询数据时，MYSQL是将数据的某一页或者某几页加载到内存中。假设要删除A页的R4这条记录，这里只会将这个空间标记为复用，但是占用的空间并没有减少，如果之后又在300~600间插入一条数据，那么会复用这个空间，所以有时候删除记录并不会减少表占用空间。

​ 当然了，如果将300-600的数据行都删除，那么这整个A数据页就被标记复用，对应数据页的复用，比行的复用稍微灵活，比如上述中将500对应行删除之后，只能在插入300-600之间的数据可以复用这个空间，但是如果整个数据页被标记复用，这个时候如果需要使用新页，这个标记删除的数据页就可以被复用。如果相邻两个数据页的利用率比较低，MYSQL也会将这两个页的数据合并到一个页，将另一个页标记为复用。

​ 所以如果使用delete删除整个表，结果就是这个表的所有数据页被标记为复用，但是占用的磁盘空间并不会减少；但是若使用truncate命令，相当于使用了drop和create命令的结合，单纯从这一方面看，truncate在删除整个表数据时会减少空间。(但是truncate不能加where条件，动作上是先删除表drop再create表，所以它是DDL命令)。

​ 这里将这些标记为复用的空间称为”空洞“。新增、删除、修改(比如将300修改为800，则操作上是将300对应行标记复用，在插入800对应行)数据都会存在生成新的空洞的情况，比如新增一条数据，导致某个页进行了页分裂，但是由于页空间没有填满，造成了较大的空洞，如果空洞比较多，产生的现象就是删除部分数据，并不会导致磁盘空间的减少。所以如果在删除数据之后能将这些空洞去掉，就可以减少占用磁盘空间。

重建表

​ 假设已经存在了一个较多空洞的表A，可以通过创建一个同样的表B，按照主键递增的顺序将数据从A表中读出，再写入到表B中，由于表B是新建表，所以表A主键索引上的空洞在表B是不存在的。这时用表B替换表A，从结果上来看，起到了收缩表A的目的。

​ 在MYSQL中已经存在这个命令，通过使用alter table A engine=InnoDB来重建表。实际上流程就是上述流程，只不过MYSQL将这些操作内置，你只需要通过这一个命令即可。

​ 当然了，这个过程中最消耗资源的过程，就是将表A数据拷贝到表B的过程。但是在这个过程中，如果出现新数据需要写入到表A中，就有可能造成数据丢失，所以在这个过程中表A不能有更新。

在MYSQL5.6版本开始引入Online DDL，对这个操作流程进行优化。也就是在拷贝的过程中，通过记录一个row log，拷贝完成之后，将row log的操作应用到表B。由于这个优化之后，在表重建过程中，允许对表A做写操作，所以称为Online DDL。

​ 这个DDL在alter启动的时候就获取了DML写锁，但是在真正拷贝数据时，就退化为读锁，这样是为了实现Online，MDL读锁不会阻塞写操作，至于为什么不直接释放锁，是因为要禁止其他线程同时做DDL。

上述的这些重建方法都会扫描原表数据和构建临时文件，对于大表来说，这个操作是很消耗IO和CPU资源，所以可以通过一些比较稳定的开源组件操作，比如GitHub开源的gh-ost。

据说100万行数据以下，可以使用online ddl超过百万可以使用gh-ost

关于重建表的三个命令：

• alter table t engine = InnoDB：在5.6版本之后，默认就是Online DDL的方式。
• analyze table t：这个命令实际上不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程加了MDL读锁。
• optimize table t：这个命令等于recreate+analyze

索引选择

使用唯一索引还是普通索引？这是分为查询过程和更新过程来分析两种索引之间的性能差别

查询过程

select id from T where k=5

这个查询语句在索引树上查找的过程：先从B+树根开始按层搜索叶子节点。

• 普通索引：查找到满足条件的第一个记录(5, 500)后，需要查找下一个记录，知道碰到第一个不满足的k=5条件的记录。
• 唯一索引：查找到满足条件的第一个记录(5, 500)后，由于唯一索引的特性，直接停止检索。

所以对于查询语句来说，两种索引的性能差别几乎没有差别，由于MYSQL是按页读写数据，所以当找到k=5的记录时，它所在的数据页都在内存里，那么对于普通索引来说仅比唯一索引多做了一个判断而已，所以几乎忽略不计。

更新过程

change buffer：当需要更新一个数据页时，如果这个数据页在内存中，则直接更新这个数据页；如果这个数据页不在内存中，在不影响数据一致性的情况下，InnoDB会将这些更新动作缓存在 change buffer 中，这样就不需要从磁盘中读出这个数据页，当下次查询出这个数据页时，将数据页读入到内存中，然后先执行 change buffer 中与这个页相关的动作之后，再返回。(虽然这块缓存的名称叫 change buffer 实际上它是可以持久化数据，也就是说 change buffer 的数据也会被写入磁盘中。)

将 change buffer 的动作应用到操作页，得到最新的数据结果的过程称为 merge。除了访问数据页之外，后台会用定时线程会触发 merge、数据库正常关闭也会触发 merge。

实际上唯一索引并不会用到 change buffer。这是因为唯一索引在更新时，需进行唯一性约束。而这个判断就使得必须将数据页读入内存才能判断，所以如果都已经将数据读入到内存中，那么直接更新内存中的值即可。

如果要插入一个新记录（4, 400）：

第一种情况，这个记录要更新的数据行在内存中。

• 唯一索引：找到3到5之间的位置，判断到没有冲突，插入新记录。
• 普通索引：找到3到5之间的位置，插入新记录。

普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况，这个记录要更新的数据行不在内存中。

• 唯一索引：需要将数据页读到内存中，判断没有冲突，插入新记录
• 普通索引：将动作插入到 change buffer。

将数据从磁盘读入到内存中涉及随机IO的访问，是数据库里成本最高的操作之一。change buffer 减少了随机磁盘访问，所以对更新性能是很明显。

索引策略

​ 在MySQL中一张表是可以创建多个索引，但是具体的SQL语句使用哪个索引来进行查询，是由MySQL来确定，有没有可能MySQL选到的索引不是最优解。

# 创建表
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

# 创建10w条数据: 从(1,1,1)，(2,2,2)，(3,3,3)直到(100000,100000,100000)
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

现在来看一条SQL查询语句：

select * from t where a between 10000 and 20000;

这样查询语句会使用索引a来提高查询效率，用explain命令结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	range	a	a	5		10000	Using where

果然key=a表示使用了所以a，扫描了10000行数据。

再来：

sessionA	sessionB
Start transaction with consistent snapshot;
	delete from t; call idata();
	explain select * from t where a between 10000 and 20000;
commit;

这里表示sessionA开启一个事务后，sessionB把数据删了之后，再次调用写10w行数据的存储过程，再通过explain命令查询这条SQL语句的执行。

这时sessionB的查询语句就不会再选择索引a，但是如果这里使用select * from t force index(a) where a between 10000 and 20000;就会使用索引。

这个例子对应的就是我们平常不断的删除数据和新增数据的场景，在这种情况下MySQL可能会选错索引。

在sessionB中删除了所有数据，然后通过call idata()插入10w行数据，看上去这里重新插入了10w行数据，但是sessionA在sessionB删除之前就开启了事务且还没有提交，所以之前的10w行数据还不能删除，这就导致之前的每一行数据都有两个版本，旧版本是数据，新版本被标记delete。所以再重新插入10w行数据之后，索引a上就有两份10w行数据。

至于为什么会选错索引，实际上优化器在选择索引的时候，有很多判断维度：扫描行、是否使用临时表、是否排序等。这里主要是因为旧的10w行数据的存在，优化器认为需要扫描的行数比较多，索引没有命中索引。其实优化器在对扫描行的判断，是通过采样分析，也是一个预估的值。可以通过analyze table t来重新进行统计。

案例

联合主键

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

表geek已经存在联合主键(a, b)，是不是只需要创建(c)索引包含了(ca)和(cb)的场景，为什么需要再创建(ca)和(cb)?

假设存在以下两条语句：

select * from geek where c=N order by a limit 1;

select * from geek where c=N order by b limit 1;

那么这时所有ca和索引cb有存在的必要吗？

假设表中记录为：

a	b	c	d
1	2	3	d
1	3	2	d
1	4	3	d
2	1	3	d
2	2	2	d
2	3	4	d

主键a和b的联合主键相当于对数据进行order by a, b。也就是先按a排序，再按b排序，c无序，这里没有使用到d。

索引ca的组织结果：逻辑上最后一列是ab的值，但是由于联合索引中已经存在了a，索引最后一列中只有b。与索引(c)一致。

c	a	b(主键的b部分)
2	1	3
2	2	2
3	1	2
3	1	4
3	2	1
4	2	3

索引cb的组织结果：同理，最后一列也只有b。

c	a	a(主键的a部分)
2	2	2
2	3	1
3	1	2
3	2	1
3	4	1
4	3	2

综上：ca索引和c的数据组织结果一致，但cb不一致，若上述两条查询语句为高频语句，则cb可以保留。

字符串添加索引

​ 字符串字段存在一个问题，如果是热点字段使用比较频繁，在不加字段的情况下，就会出现一直全表扫描，那么如果为字符串字段添加索引？

如果你仅仅只想到直接为该字符串字段添加一个索引，那么只能说你只看到了问题的表面，因为为字符串字段添加索引需要考虑到这个字符串字段的长度问题，如果这个字符串长度比较长，那么这个索引需要占用的空间就会比较大。

前缀索引

前缀索引是只取字段的部分长度作为索引：

alter table SUser add index index2(email(6));

设置SUser表的email字段的前6位作为索引。
假设表中有数据：

id	email
1	xiaocainiaoya@foxmail
2	cainiao@foxmail
3	niao@foxmail

那么如果根据email前六位来做为前缀索引就只需要匹配一次，回表一次后，将字段值与查询值再次匹配若匹配成功，则获取数据。
所以这种方式需要这个字段存储的值具备一定的规则，然后根据规则设置索引的长度，长度越长，区分度就越高，查询效率就越高，伴随着占用空间就越大。

注：前缀索引会破坏覆盖索引，如果仅查询索引上的字段，但是由于需要回表进行一次匹配（系统不知道这个字段值到底有没有被截断）。所以覆盖索引相关的优化可能就失效了。

倒叙存储

一些字段的规则比如身份证，身份证的前面6位表示地域位置，在查询时需要遍历的列就比较多，可以将身份证倒叙存储，也就是通过reverse(idCard)进行存储，再通过index(idCard(6))设置索引，减少遍历的行数。

哈希字段

再设置一个哈希字段，比如创建身份证字段之后，再创建一个身份证的哈希字段，插入的时候计算身份证的哈希值填入，那么就可以为这个哈希字段添加索引，从而减少索引字段的长度，但是由于不同值经过哈希算法后可能会得到同一个值，所以存在一定的误差，在查询时还是要将身份证的原值加上。

select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=’input_id_card_string’

小结

1. 直接创建完整索引，这样可能比较占用空间；

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

条件字段函数操作

如果在查询条件上添加聚合函数会导致索引失效，这是因为对索引字段进行聚合函数会破坏索引的有序性，导致优化器放弃走索引树的搜索功能，但是并不是说放弃了这个索引，因为如果遍历这个索引比遍历主键索引来的快，还是会使用这个索引，但是结果是使用了这个索引扫描的行也是全表的行数。

但是尽管有些聚合操作不破坏索引有序性，但是MySQL也不予支持，比如where age + 1 = 1001，不会改变索引的有序性，但是这时候也是扫描全表，要修改为where age = 1001 - 1

隐式类型转换

如果表中有一个varchar字段，但是查询语句是where age = 10，那么就涉及到类型的转换，MySQL采用的是字符串转换成数字的转换。也就是说where age = 10，需要将全表的数据都进行字符串转数字的转换，所以导致了索引失效。
如果换过来，表中有一个int字段，但是查询语句是where age = '10',这个时候实际上是将这个'10'转为数字，在去表中匹配，这时就会命中索引。

隐式字符编码转换

假设有两个表有关联操作，但是关联字段的编码不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，在做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，是按“数据长度增加的方向”进行转换。

也就是说，实际上这个语句等同于下面这个写法:

select * from trade_detail where traideid USING utf8mb4 = $L2.tradeid.value;
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

在这种情况下实际上就是需要判断谁是驱动表，谁是被驱动表，

select * from trade_detail where traideid = CONVERT($L2.tradeid.value USING utf8mb4);