mysql总结笔记-002-索引进阶上

1 深入浅出索引

索引是一个快速查找的数据结构。

从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。

1.1 InnoDB 的索引模型

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

跳表、LSM 树等数据结构也被用于引擎设计中，学习一下。

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。

mysql5.6.23

db_user.frm —表结构 db_user.ibd --表数据

mysql8

cat.idb —表数据

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

1.2 索引维护

B+ 树为了维护索引有序性，随机插入值时可能造成数据页的分裂。页分裂会造成空间利用率低, 移动数据也会造成性能影响。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

建表语句里一定要有自增主键，自增主键好处：有序追加操作，不会涉及叶分裂。一般是整型，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

适用业务字段做主键场景：

1. 只有一个索引；

2. 该索引必须是唯一索引。

1.3 建立索引

1 覆盖索引

查询的列在索引中，可以不要回表，提升效率。

注意：在引擎层扫描行数会找到第一个不匹配的行。Server层扫描行数是引擎层返回的结果。

2 最左前缀原则

字符串满足这个条件，就可以走索引查询

3 索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

联合索引，可以先根据所有索引条件过滤，再去回表，减少回表次数。

1.4 思考

1，下面重建索引方式有什么不对吗？

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

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

2，“N叉树”的N值在MySQL中是可以被人工调整的么？

调整索引key 的大小，改变page大小。

innodb的页大小是16KB(由 innodb_page_size 变量控制）

N是由页大小和索引大小决定的。

3，索引使用实际案例？

实际上主键索引也是可以使用多个字段的。DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

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;

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。

但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“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索引不必要，因为select * from geek where c=N order by a limit 1; 会走索引，并且利用a排序。

4 老师，下面两条语句有什么区别，为什么都提倡使用2？

1.select * from T where k in(1,2,3,4,5)

2.select * from T where k between 1 and 5

第一个要树搜索5次

第二个搜索一次

in 里面的数据是取出来，一个一个等值查询的。

2 普通索引和唯一索引，应该怎么选择？

从这两种索引对查询语句和更新语句的性能影响来进行分析。

2.1 查询过程（影响不大）

假设，执行查询的语句是 select id from T where k=5。

• 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微，因为InnoDB 的数据是按数据页为单位来读写的，对于普通索引来说，多做一次判断操作。

2.2 更新过程

1，更新的目标页在内存中

• 唯一索引：判断到没有冲突，插入这个值，语句执行结束；
• 普通索引：插入或更新这个值，语句执行结束。

性能差别不大，只是一个判断，只会耗费微小的 CPU 时间。

2，更新的目标页不在内存中

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

change buffer 因为减少了随机磁盘访问，普通索引比唯一索引提升了性能。

2.3 Change Buffer 介绍

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

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

2.3.1 change buffer 使用场景

（1）change buffer 只限于用在普通索引的场景下，而不适用于唯一索引

（2）对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

（3）一个业务的更新模式是写入之后马上会做查询，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。change buffer 反而起到了副作用。

2.4 索引选择和实践

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

如果所有的更新后面，都马上伴随着查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

在实际使用中，你会发现，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

2.5 change buffer 和 redo log

现在，我们要在表上执行这个插入语句：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这里，我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。

图 2 带 change buffer 的更新过程

注意：redo log 记录了change buffer 和 buffer pool 中页的修改，确保了系统断电，数据恢复能力。

分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

这条更新语句做了如下的操作（按照图中的数字顺序）：

1. Page 1 在内存中，直接更新内存；

2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息

3. 将上述两个动作记入 redo log 中（图中 3 和 4）

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

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

那在这之后的读请求，要怎么处理呢？

比如，我们现在要执行 select * from t where k in (k1, k2)。这里，我画了这两个读请求的流程图。

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

图 3 带 change buffer 的读过程

从图中可以看到：

1. 读 Page 1 的时候，直接从内存返回。

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

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

主键索引用不上change buffer.

2.6 思考

1 change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？

不会丢失，虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

2 merge 的过程是否会把数据直接写回磁盘？

merge 的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；

2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；

3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

3 MySQL为什么有时候会选错索引？

select * from t force index(a) where a between 10000 and 20000;// 强制使用某索引。

3.1 优化器的逻辑

在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序, 是否回表等因素进行综合判断。

3.1.1 扫描行数是怎么判断的？

根据索引的基数，一个索引上不同的值的个数，我们称之为“基数”（cardinality），也叫索引的区分度。

如何查看索引基数？

show index;

索引统计值（cardinality 列）虽然不够精确，但大体上还是差不多的.

3.1.2 MySQL 是怎样得到索引的基数的呢？

InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。

设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。

3.1.3 如何重新统计索引信息？

analyze table t 命令，可以用来重新统计索引信息。

3.2 索引选择异常和处理

1 采用 force index 强行选择一个索引。 缺点：索引改名字，或者迁移数据库导致语法错误

2 考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

3 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

3.3 思考题

前面我们在构造第一个例子的过程中，通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。

答：delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。这样，索引 a 上的数据其实就有两份。

删的时候，由于有未提交事务开启的一致性视图read-view，所以导致了存在两个数据版本的数据，貌似优化器在"看"二级索引的时候，"看到"了多个历史版本的数据，错误以为有很多数据

而主键索引数量由于确认机制不同，数量没有变，综合考虑，优化器选择了主键索引。主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。