MySQL索引

MySQL索引

​ 除了表数据，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 通过这些数据结构可以大大提高MySQL的查询效率。索引的本质就是数据结构 !

一、磁盘相关概念

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

1、磁盘IO

​ 每次从磁盘中查找数据称为磁盘IO， 而磁盘IO至少要经历磁盘寻道、磁盘旋转、数据读取等操作，非常影响性能，所以对于读取数据，最大的优化就是减少磁盘I/O。

2、磁盘常见概念

# 存储单位
	1TB=1024GB ；1GB=1024MB ；1MB=1024KB ；1KB=1024字节

# 磁道（Track）
	磁盘旋转时，磁头在磁盘表面划出的每一个圆形轨迹。

# 扇区（sector）
	磁盘上的每个磁道被等分为若干个弧段，这些弧段称之为扇区。（每个扇区的大小一般为512字节）
	扇区数量庞大，因此操作系统不会直接与扇区交互，而是与多个连续扇区组成的磁盘块交互。

# 磁盘块（Block）
	相邻的扇区组合在一起，形成一个磁盘块（每个磁盘块可以由 2、4、8、16、32 或 64 个扇区组成）
	磁盘块是文件系统读写数据的最小单位（操作系统规定一个磁盘块中只能放置一个文件）
	因此文件占用的空间只能是磁盘块的整数倍，这就会出现 [文件占用磁盘空间 > 文件实际大小] 的情况。

# 页（Page）
	内存的最小存储单位。页的大小通常为磁盘块大小的 2^n 倍。

# 小结
	扇区：
		硬盘读写的基本单位，磁盘中最小的物理存储单元。
	磁盘块：
		磁盘操作的基本单位，文件系统读写数据的最小单位。（多个扇区）
	页：
		内存操作的基本单位，内存存储的最小单位。（多个磁盘块）

3、磁盘存储的特点

系统从磁盘读取数据到内存时，是以磁盘块 block 为基本单位的，位于同一磁盘块的数据都会被读取出来。

如上图所示，如果要查询数字5：
	1. 系统找到磁盘块2之后，会将整个磁盘块2的数据都读取出来，而不是只读取一个5。
	2. 读取整个磁盘块2的数据（4,5,6）之后，再根据条件过滤保留5。

4、数据库IO的基本单位 - 页

InnoDB引擎 将数据划分为若干个 页page ，每个页的默认大小为16KB。

• 记录是按照行来存储的，一个页中可以存储多个行记录。
• InnoDB引擎将若干个地址链接磁盘块，以此来达到页的大小16KB。
• 查询数据时，一个页中的每条数据都有助于定位数据的位置，这将会减少磁盘IO次数，提高查询效率。

数据库管理存储空间的基本单位是页（Page），数据库I/O操作的最小单位是页。

• 不论读一行，还是读多行，都是将这些行所在的页进行加载。
• 也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

二、索引的常见模型

1、哈希表

用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

假设现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引如下：

优点：更新数据速度很快，只需要往后追加。

缺点：因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

应用：适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。

2、有序数组

要查ID_card_n2对应的名字，用二分法就可以快速得到，时间复杂度是O(log(N))。

要查身份证在[nx, ny]区间的User，可以先用二分法找到 ID_card_nx（如果不存在ID_card_nx，就找到大于ID_card_nx的第一个User），然后向右遍历，直到查到第一个大于ID_card_ny的身份证号，退出循环。

优点：在 等值查询 和 范围查询 场景中的性能都非常优秀。

缺点：更新数据的时候比较麻烦，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

应用：静态数据存储，比如2020年某个城市的所有人口信息，这类不会再修改的数据。

3、搜索树

二叉搜索树的特点：每个节点的左儿子小于父节点，父节点又小于右儿子。时间复杂度是O(log(N))
多叉搜索树的特点：每个节点有多个儿子，儿子之间的大小保证从左到右递增。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。对于一个100万行的表，单独访问一行可能需要 20×10 ms的时间。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。即使用多叉树，减少树高。

4、BTree

注意：这里的 BTree 是数据结构，和索引类型 BTree 是有区别的。

名词	含义
节点	指树中的一个元素（图中的一个框）
节点的度	节点拥有的子树的个数，二叉树的度不大于2
叶子节点	度为0的节点，也称之为终端结点
高度	叶子结点的高度为1，叶子结点的父节点高度为2，以此类推，根节点的高度最高

BTree又叫自平衡多叉查找树

1. 每个节点的组成：
		1）索引：索引值(如id)
        2）数据：索引值对应的data
        3）指针：该层没有指定索引，通过指针到下一层寻找

2. 每个节点的大小：
		索引大小（如5B） + 数据大小（如95B）

3. 每个节点的大小设置为一个页的大小
		这样每个节点只需要一次IO就可以完全载入。

4. 度比较大，每个节点可以存储多个索引值和数据
		度越大，树的高度越低，磁盘IO的次数就越少
		度 = 页的大小/(索引大小+数据大小)

5. 叶子节点具有相同的深度，叶节点的指针为空
		查询效率比较稳定

BTree和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，层级结构小，因此搜索速度快。

5、B+Tree

InnoDB 和 MyISAM 存储引擎默认的索引类型是BTree，其底层的数据结构就是 B+Tree （BTree的变种）

• B+Tree 是 BTree索引 的数据结构，索引类型是没有B+Tree类型的。
• B+Tree 通常用于数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。

# B+Tree和BTree的区别
1. 非叶子节点不存储data，只存储键值信息（索引+指针），可以增大度（子树数目），减少树的高度
2. 数据data都存放在叶子节点中，叶子节点不存储指针。
3. 叶子节点之间通过链表指针/双向指针相连。(方便范围查询)

由于 B+Tree只有叶子节点保存data，因此查询任何key都要走到叶子节点。

三、索引的分类

1、索引的常见类型

索引是在存储引擎中实现的，不同的存储引擎支持的索引也不一样，常见的有以下3类：

• BTREE索引：InnoDB 和 MyISAM 存储引擎中默认的索引类型，底层数据结构为 B+Tree。
• HASH索引：Memory 存储引擎中默认的索引类型 ，底层数据结构为 Hash。
• R-tree索引：空间索引，MyISAM 存储引擎中一种特殊索引，主要用于地理空间数据类型，使用较少。

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	支持	支持	不支持

MySQL数据库 默认使用InnoDB引擎，因此我们平常所说的索引，都是指 B+Tree 结构的索引。

2、聚簇索引 & 非聚簇索引

在 B+Tree 索引模型中，每一个索引都对应着一棵B+树。

• 聚簇索引：索引和数据存储在一个文件中，索引文件本身又是数据文件。
• 非聚簇索引：索引文件和数据文件是分离的，数据文件 需要通过 索引文件 查询。

1）MyISAM（非聚簇索引）

MyISAM 索引文件和数据文件是分离的（非聚簇索引）

• 索引文件：数据结构是 B+Tree，叶子节点存储的是数据的磁盘地址。
• 数据文件：磁盘地址对应的数据data。

非主键索引 和 主键索引 的结构类似。

2）InnoDB（聚簇索引）

InnoDB 索引文件本身就包含数据文件，叶子节点包含了完整的数据记录（聚簇索引）

• 主键索引：叶子节点存的是整行数据。（主键索引也被称为 聚簇索引 clustered index）
• 非主键索引：叶子节点存的是主键的值。（方便加行锁，但是查询可能会产生二次查找）
  • 唯一索引字段生成的 B+Tree 只能拿到 唯一索引字段 和 主键字段
  • 如果要拿到其他字段的值，就需要根据 主键字段 再去查询 主键索引生成的 B+Tree

在InnoDB中，表都是根据主键顺序，以索引的形式存放的，这种存储方式的表称为索引组织表。

• 默认情况下，使用主键索引字段来生成 B+Tree
• 如果没有id字段，就使用唯一索引字段来生成 B+Tree
• 唯一索引也没有，就生成一个隐藏字段row_id来生成 B+Tree

3、覆盖索引

覆盖索引：在一颗索引树上就能获取SQL所需的所有列数据，不需要回表查询。

【SQL示例】

# 主键索引id、非主键索引name
create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
) engine = innodb;

第一个SQL语句：

命中name索引，SQL所需的数据 id 和 name 都在 name索引树 上，无需回表，符合覆盖索引，效率高。

第二个SQL语句：

命中name索引，但不是所有字段都在name索引树上，sex字段必须回表查询才能获取到，不符合索引覆盖。

第三个SQL语句：

# 创建联合索引(name, sex)
alter table user add index idx_name_sex(name, sex);

命中name索引，而且所有字段都在 (name, sex) 联合索引树上，符合覆盖索引，效率高。

4、组合索引 - 最左匹配原则

组合索引：多个字段组合成一个索引（where条件中经常存在多个条件查询时，可以创建联合索引）

# 组合索引的创建
alter table stu add index idx_three(name, age, sex);

# 为什么不单独为这三个字段创建索引？
  假设有100w的数据，一个索引可以筛选出10%的数据
    1. 分别创建索引，MySQL只会选择辨识度高的一列作为索引，可以筛选出10w的数据；
    2. 建立组合索引，筛选的数据就是 100w*10%*10%*10% = 1000条

# 最左匹配原则
	1. 最左边的列必须存在，否则索引失效（索引字段的顺序可以是任意的，MySQL优化器会自动调整字段顺序）
	2. 存在范围查询 -> 使用范围查询的字段索引生效，该字段右侧的字段索引失效
	3. 存在模糊查询 -> 使用模糊查询的字段及其右侧的字段索引都失效

# 建立组合索引(a,b,c)，判断以下语句中索引的使用情况

select * from table where a=4 
-- a
select * from table where a=4 and b=6
-- a b
select * from table where a=4 and c=5 and b=6 
-- a b c
select * from table where a=4 and c=6
-- a
select * from table where b=4 or b=5
-- 不生效 （组合索引最左边的字段不存在 -> 索引失效）
select * from table where a=4 and c=6 and b>5
-- a b   （存在范围查询 -> 使用范围查询的字段索引生效，该字段右侧的字段索引失效）
select * from table where a=4 and b like '%test' and c=6
-- a     （存在模糊查询 -> 使用模糊查询的字段及其右侧的字段索引都失效）

诀窍：如果某个查询不限制性别，那么可以通过在查询条件中新增 AND SEX IN('m','f') 来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。

这个“诀窍”在由于最左列无法满足最左匹配原则时非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了。

四、索引的优化

1、索引的优缺点

【优点】

1. 可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性。
2. 可以提高数据检索的效率，降低数据库的IO成本。（随机IO变为顺序IO）
3. 索引是有序的，通过索引列对数据进行分组和排序，可以减少分组和排序所消耗的时间，降低CPU的消耗。
4. 在表关联的连接条件建立索引，可以加速表与表之间的相连。

【缺点】

1. 索引文件会占用物理空间。除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间。
2. 创建索引 和 维护索引 会耗费时间，随着数据量的增加而增加。
3. 表的数据发生修改时，索引也要动态的维护。增加了维护成本，降低了表更新的速度。

2、回表查询

回表查询：先在 普通索引树 定位主键索引值，然后回到 主键索引树 定位行记录。（要多扫描一棵索引树）

# 主键索引ID，普通索引k
select * from T where k between 3 and 5;

# 执行流程如下：
1. 在k索引树上找到k=3的记录，取得ID=300
2. 到ID索引树查到ID=300对应的R3

3. 在k索引树取下一个值k=5，取得ID=500
4. 再回到ID索引树查到ID=500对应的R4

5. 在k索引树取下一个值
6. k=6，不满足条件，循环结束。

查询过程：读取了k索引树的3条记录（步骤1、3、5），回表查询了两次（步骤2、4）。

主键索引只要扫描主键索引树，而 基于非主键索引的查询需要多扫描一棵索引树，因此应该尽量使用 主键查询 或 覆盖索引。

3、索引下推

最左前缀可以用于在索引中定位记录，那些不符合最左前缀的部分，会怎么样呢？

# 组合索引 (name, age)
select * from stu where name like '张%' and age=10 and ismale=1;

根据组合索引，只能用 “张” 找到第一个满足条件的记录ID3，然后判断其他条件是否满足

• MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
• MySQL 5.6开始， 可以在索引遍历过程中，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推：不看age的值，按顺序把name第一个字是“张”的记录一条条取出来回表。（需要回表4次）

有索引下推：在 (name, age) 索引内部就判断了age是否等于10，直接跳过不等于10的记录。（只要回表2次）

4、索引的设计与使用原则

适合建立索引

1. 有唯一性约束的字段（主键索引、唯一索引）
2. 频繁作为 where 查询条件的字段
3. 经常 group by 和 order by 的列
4. 需要 distinct 去重的字段
5. 用于表关联的字段

不适合建立索引

1. where条件中用不到的字段
2. 索引选择性比较低的字段
		例如：国籍、性别等，数据的`差异率不高`，这种建立索引就没有太多意义。
3. 无序的字段
		例如：身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
4. 数据量比较少的表
5. 经常变更表结构的表

索引的使用原则

- 表的主键、外键必须有索引；
- 数据量大的表应该有索引；
- 经常与其他表进行连接的表，应该在连接字段上建立索引；
- 经常出现在where子句中的字段，特别是大表的字段，应该建立索引；

- 索引应该建在选择性高的字段上，像sex性别这种不适合建立索引；
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
		在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度
- 频繁进行数据更新的表，不要建立太多的索引；

- 不要定义冗余或重复的索引
		过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的
- 删除无用的索引，避免对执行计划造成负面影响；
- 索引的数目不是越多越好，建议单张表索引数量不超过6个
		- 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
		- 索引对于插入、删除、更新操作也会增加处理上的开销。因为表中的数据更改的同时，索引也会进行调整和更新
		- 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的`索引来进行评估`，索引过多会增加优化时间
		
- 尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。
- 对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”（in）则没有这个限制。

五、索引失效的情况

1、不符合最左匹配原则

# 最左匹配原则
	1. 最左边的列必须存在，否则索引失效（索引字段的顺序可以是任意的，MySQL优化器会自动调整字段顺序）
	2. 存在范围查询 -> 使用范围查询的字段索引生效，该字段右侧的字段索引失效
	3. 存在模糊查询 -> 使用模糊查询的字段及其右侧的字段索引都失效

# 建立组合索引(a,b,c)，判断以下语句中索引的使用情况

select * from table where a=4 
-- a
select * from table where a=4 and b=6
-- a b
select * from table where a=4 and c=5 and b=6 
-- a b c
select * from table where a=4 and c=6
-- a

select * from table where b=4 or b=5
-- 不生效 （组合索引最左边的字段不存在 -> 索引失效）

select * from table where a=4 and c=6 and b>5
-- a b   （存在范围查询 -> 使用范围查询的字段索引生效，该字段右侧的字段索引失效）
select * from table where a=4 and b in('t1','t2') and c=6
-- a b c （对于select来说，in不属于范围查询，最左匹配依然生效）

select * from table where a=4 and b like '%test' and c=6
-- a     （存在模糊查询 -> 使用模糊查询的字段及其右侧的字段索引都失效）

# 建立组合索引(a,b,c)，判断 order by 语句中索引的使用情况
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC 

如果WHERE使用索引的最左前缀定义为常量，则 order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c 

不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC 		/* 排序不一致 */
- WHERE g = const ORDER BY b,c 		/* 丢失a索引 */
- WHERE a = const ORDER BY c 		/* 丢失b索引 */
- WHERE a = const ORDER BY a,d 		/* d不是索引的一部分 */
- WHERE a in (...) ORDER BY b,c 	/* 对于排序来说，多个相等条件也是范围查询 */

2、发生类型转换（自动/手动）

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';

# 发生类型转换 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = age;

3、进行计算 / 使用函数

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 20;
# 进行计算 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age+1 = 21;

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 使用函数 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name, 3) = 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE SUBSTRING(name, 1, 3) = 'abc';

4、like以通配符%开头

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# like以通配符%开头 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';

5、不等于（!= 或 <>）和 not in

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc';
# 使用不等于 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age in (1,2,3);
# 使用not in - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age not in (1,2,3);

6、is not null

# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name IS NULL;
# 使用not null - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name IS NOT NULL;

7、or前后存在非索引列

# 索引生效（age、name都有索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';
# or右侧classid无索引 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
# or左侧classid无索引 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 100 OR name = 'Abel';
# or右侧classid无索引 - 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel' OR classid = 100;

8、索引选择性太差

索引选择性太差 - 通过索引查询的重复的值太多（例如性别）

• 通过索引找到的数据超过了表总数的一定比例时，会导致mysql选择全表扫描（大概是25%）

Tips：无法避免索引失效时，尽量使用覆盖索引

9、数据库和表的字符集不统一

不同的 字符集 进行比较前，需要进行 转换 ，会造成索引失效。

• 统一使用 utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。

六、唯一索引 vs 普通索引

1、查询过程比较

# 执行查询的语句
select id from T where k=5

这个查询语句，先是通过B+Tree从树根开始，按层搜索到叶子节点，然后在数据页内部通过二分法来定位记录。

• 普通索引：查找到第一个满足条件的记录后，还需要查找下一个记录，直到碰到第一个不满足条件的记录。
• 唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

1. InnoDB的数据是以数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。
2. 因为引擎是按页读写的，所以查询一条记录时，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
3. 如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。
4. 但是，对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。

2、change buffer

当需要更新一个数据页时：

• 如果数据页在内存中，就直接更新
• 如果数据页不在内存中，将更新操作缓存在change buffer中（在不影响数据一致性的前提下）

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

change buffer在内存中有拷贝，也会被写入到磁盘上。（这个过程称为merge）

1. 访问这个数据页时，会触发merge
2. 系统有后台线程，会定期merge
3. 数据库正常关闭shutdown的过程中，也会执行merge操作。
4. 在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

merge的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版数据页）；
2. 从change buffer里找出这个数据页的change buffer 记录（可能有多个），依次应用，得到新版数据页；
3. 写redo log。这个redo log包含了 数据的变更 和 change buffer的变更。

change buffer的优点：

• 将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。
• 数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

change buffer的大小：

• change buffer用的是buffer pool里的内存，因此不能无限增大。
• change buffer的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。
  这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

3、更新过程比较

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

如果要在这张表中插入一个新记录 (4, 400) 的话，InnoDB的处理流程是怎样的？

第一种情况是，这个记录要更新的目标页在内存中。

• 唯一索引：找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 普通索引：找到3和5之间的位置，插入这个值，语句执行结束。

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

第二种情况是，这个记录要更新的目标页不在内存中。

• 唯一索引：需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 普通索引：只需将更新记录在change buffer，语句执行就结束了。

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

普通索引的所有场景，使用change buffer都可以起到加速作用吗？

change buffer的主要目的是将记录的变更缓存下来，merge才是真正进行数据更新的时候。所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

假设一个数据写入之后马上会做查询，那么即使将更新先记录在change buffer，但由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

4、索引的选择

普通索引和唯一索引在查询性能上是没差别的，主要考虑的是对更新性能的影响。

• 如果要通过数据库索引来保证业务的正确性，那么没得选，必须创建唯一索引。
• 在一些 “归档库” 的场景，也可以考虑使用唯一索引。

其他尽量选择普通索引。普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个表数据写入速度。

5、change buffer 和 redo log

WAL 提升性能的核心机制，也的确是尽量减少随机读写，这里放到一个流程中说明：

# k1所在的数据页在内存中(InnoDB buffer pool)，k2所在的数据页不在内存中
insert into t(id,k) values(id1,k1),(id2,k2);

更新语句涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

带 change buffer 的更新过程：

1. Page1 在内存中，直接更新内存
2. Page2 不在内存中，就在内存的change buffer区域，记录下 “ 我要往Page 2插入一行 ” 这个信息
3. 将上述两个动作记入redo log中（两次操作合在一次顺序写入）

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

带 change buffer 的查询过程

如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。

• 读 Page1 的时候：

  直接从内存返回（WAL之后如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可以返回？其实是不用的）

• 读 Page2 的时候：

  把 Page2 从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读 Page2 的时候，这个数据页才会被读入内存。

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话：

• redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写）
• change buffer 主要节省的是随机读磁盘的IO消耗。

七、字符串添加索引

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB 、TEXT 或者很长的VARCHAR 类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。