MySQL学习笔记（三）InnoDB索引

索引概念

        索引在关系型数据库中，是一种单独的、物理的对数据库表中的一列或者 多列值进行排序的一种存储结构，它是某个表中一列或者若干列值的集合，还 有指向表中物理标识这些值的数据页的逻辑指针清单。

         索引的作用相当于图书的目录，可以根据目录重点页码快速找到所需要的 内容，数据库使用索引以找到特定值，然后顺着指针找到包含该值的行，这样 可以是对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

索引原理

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一 行开始，然后通读整个表以找到相关的行。表越大，花费就越多。如果表中有相关列的索引，MySQL可以快速确定数据文件中间要查找的位置，而不必查看所有数据。这比按顺序读取每一行要快得多。

所以索引的作用是加快我们的查询效率。

大部分Mysql索引都是用的b-树（注意：是B-Tree，不是B减树），InnoDB使用的BTree实际上就是B+树。

索引类型

在InnoDB里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的非 空的唯一索引）、全文索引。

普通（Normal）：也叫非唯一索引，是普通索引，没有任何限制

唯一（Unique）：唯一索引要求键值不能重复（可以为空），主键索引其实是一种特殊的唯一索引，不过他还多了一个限制条件，要求键值不能为空。主键索引用 primary key 创建。

全文（Fulltext）：针对比较大的数据，比如我们存放是文章，课文，邮 件，等等，有可能一个字段就需要几kb，如果要解决like查询在全文匹配的 时候效率低下的问题，可以创建全文索引。只有文本类型的字段才可以创建 全文索引，比如char、varchar、text。MyISAM和InnoDB都支持全文索引。

主键索引

主键索引是一种特殊的唯一索引,一个表只能有一个主键且不允许有空值;索引列只能出现一次且必须唯一。

page存储

Page 是 bufferpool 跟磁盘交互的最小单位， page 里面 存的是record ，也就是我们的行数据。 page 默认大小是 16KB 。

一行数据也是有大小的，并且最大不能超过page 大小的一定比例。

官网： https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

所以，一个 page 里面放的数据也是有限的。

 假如，我们现在一个page 页只能放 3 条数据。并且每个页都会有个页 ID ，如

图

为了在page页里面进行更快的查询，page页里面的数据都是有序进行排序，并且数据跟数据之间都是一个单链表。那么这个有序，可以根据不同的字段来，但是每个表最少有一个排序字段

这个字段：默认是主键，如果主键不存在，就是非空的唯一索引，如果非空唯一索引也没有，没行数据都会有个默认的row_id

多个Page页是怎么建立连接（数据在不同的页中）

MySQL把不同的页通过双向向链表建立链接，这样我们就可以通过上一页找到 下一页，通过下一页找到一页，由于我们不能快速定位的到记录的所在页，我们只能 从第一个页沿着双向链表一直往下找，在每个页中再按照在同一页的方式去查找指定 的记录，这个也是全表扫描嘛。

当Page页越来越多查询会出现什么问题、怎么解决怎么优化

当我们链表记录变多，我们出现了查询缓慢问题：

问题：

1.查询时间的复杂度0（N）

2.读写磁盘的IO次数过多

我们可以参考书籍的目录，我们看书时可以通过目录快速定位到我们需要看的章节，同时如果目录页过多，我们页可以创建目录的目录，这就是索引的索引

这棵树，因为是根据主键存储的，所以我们把它称之为主键索引树，因为主键 索引树里存储了我们的表里的所有数据，那么在MySQL中 索引即数据，数据即索引也是这个原因了。这就是MySQL的B+树。

索引树、页的分裂与合并

当Page页出现增加、修改、删除，都会遇到什么问题

有序增加，新增一条数据怎么办？ 那么是不是得开启一个新页！并且页的数据 必须满足一个条件：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，有序增加 我们直接在双向链表末端增加一个页即可。

无序增加，新增一条数据怎么办？

1. 开启一个新页，并且找到数据的位置。

2. 把旧数据移动到新页，把新的数据放到有序的位置上。

3. 叶子结点数据一直平移。

4. 触发叶子结点数据Page页的分裂与合并

5. 触发上层叶结点和根结点的再次分裂与合并。

6. 这叫什么，"牵一发而动全身",也叫做页分裂！！

总结：Page页出现增加、修改、删除遇到的问题：

我们可以说，当无序增加、更新主键ID、删除索引页的更新操作时候，会有大 量的树结点调整，触发子叶结点Page页和上层叶结点和根节点页的分页与合并，造 成大量磁盘碎片，损耗数据库的性能，也就是解释了我们为什么不要在频繁更新修改的列上建索引，或者是不要去更新主键。

二级索引

        何为二级索引，就是除了主键外，我们会根据查询的字段，也去建立相应

的字段索引。

联合二级索引

        我们在创建索引的时候，我们除了给单个列创建索引外，我们也是可以创建多字段的联合索引的，那么假如查询条件为多个字段的时候，也可能大大提升我们的查询性能。 所谓联合索引，无非就是多个字段排序，这个字段是有先后顺序关系的。 会根据字段的顺序，从最前面的开始排序，如果前面的字段相同然后再根 据第二个字段排序，以此类推

回表、覆盖索引

现在我们去根据索引字段去查询的话，已经大大提升我们查询的性能，因为查询会走到我们的索引树。

我们需要查询的数据如果全部在二级索引中能找到，那么 这个叫做场景叫做：覆盖索引

我们除了索引树上的字段外，我们还需要查询的字段，但是这个索引树上是没有这个字段。

只有在主键索引树才有，所以，我们需要通过主键索引的排序字段去主键索引拿到响应的数据，这个过程叫做：回表

为什么用B+树？

1.B+树的底层是多路平衡查找树，对于每一次的查询的都是从根节点触发，到子叶结点才存放数据，根节点和非叶子结点都是存放的索引指针，查找叶子结点互，可以根据键值数据查询。

2.扫库、扫表能力更强

3.排序能力更强

4.查询效率和查询性能稳定

5.存储能力更强、三层B+树就能存储千万级别的数据。

EXPLAIN

SELECT_TYPE 根据这个字段可以看到当前的查询类型

1.SIMPLE （简单SELECT, 不使用UNION 或者子查询）

2.PRIMARY (子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被 标记为PRIMARY)

3.UNION(UNION 中的第二个或者后面的SELECT语句)

4.DEPENDENT UNION(子查询中的 UNION 查询第二个或后面的SELECT 语句，取决于 外面的查询)

5.UNION RESULT(UNION的结果，UNION语句中的第二个SELECT 开始后面的所有 SELECT)

6.SUBQUERY/MATERIALIZED (子查询中使用 = 和IN的区别，= 是SUBQUERY、 IN 是METARIALIZED )

7.DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)

8.DERIVED(派生表的SELECT * FROM (SELECT....)子句的子查询)

9.UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第 一行 ，暂时没有找到体现的语句)

10.UNCACHEABLE UNION (一个子查询的 UNION 属于不可缓存子查询，没有找到提现)

索引相关面试题

为什么离散度低的列不走索引？

        离散度是什么概念，相同的数据越多离散度越低，相同的数据越少离散度就越高。 请问都是相同的数据怎么排序，没办法排序啊？ 在B+Tree 里面重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不了 多少的时候，就算建立了索引也不会走。走不走索引，是MySQL的优化器去决定 的。

索引是不是越多越好？

        空间上：用空间换时间，索引是需要占用磁盘空间的。

        时间上：命中索引，加快我们的查询效率，如果是更新删除，会导致页的分裂与合并，影响插入和更新语句的响应时间，延缓性能。 如果是频繁需要更新的列，不建议建立索引，因为频繁触发页的分裂与合并。

大字段不建议建立索引

        大字段不适合建立索引，因为大字段会占用内存空间，所以搜索会变慢。

尽量建立联合二级索引而不是单列二级索引

        因为联合索引本来就是多个字段的联合，比如 a b c三个字段的联合索引，相当于 a、ab 、abc 3个查询都能走到索引

我们主键一般建议趋势递增的原因在哪

        首先，我们知道我们每个表都会去有一个主键索引树，是根据主键进行排序的B+树，树的特性是会加快查询速度，但是在添加数据的时候，是要去维护这个树的，如果是递增的，我们只需要往树上添加节点，那么假如如果不是趋势递增的，那么我们会引发树的分裂与合并。然后索引树中的叶子节点的page里的数据也是排序好的，也会导致页的分裂与合并。