InnoDB索引结构

InnoDB页结构

各个数据页可以组成一个双向链表
每个数据页的记录是一个单向链表
- 每个数据页会为里面的记录生成一个页目录(Page Directory)，通过主键查找某条记录可以在页目录使用中使用二分查找快速定位对应的槽，在遍历该槽对应分组即可找到指定的记录。
- 以非主键作为搜索条件，只能从最小记录开始一次遍历单链表中的每条记录。

索引 - 图1
索引 - 图2

一条没有优化的SQL语句select * from user where username = ‘Java3y’ ：

定位记录所在的页：通过遍历双向链表找到所在的页
从页中查找相应的数据，只能遍历页所在的单链表

使用索引

本质：将无序的数据变成了有序的。
索引 - 图3
找到id=8的简要步骤：
索引 - 图4

很明显：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过“目录”就可以很快地定位到对应的页上了！

维护索引

B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会破坏它的原有结构。
要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度

哈希索引

主流的还是使用B+树索引比较多，对于哈希索引，InnoDB是自适应哈希索引的
innodb不算是支持hash索引，因为索引一般用来存储数据的，但是这个哈希索引使用来快速找到内存中的页的。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。

索引 - 图5

局限：

无法利用索引完成排序
不支持最左匹配
碰撞
不支持范围查找

聚集主键非聚集主键

https://juejin.cn/post/6908219549351411726

一：InnoDB 建表时，可不可以不声明主键？ 可以不声明主键，但必须要有聚集索引：（1）有主键，主键是聚集索引；（2）没有主键，首个非空唯一列是聚集索引；（3）没有符合条件的列，row-id 是聚集索引；主键和聚集索引不是一个东西，不要混淆。 二：InnoDB 建表时，可不可以不声明主键非空？ 可以不声明主键非空，会自动加上非空限制。 三：InnoDB 建表时，可不可以选择多个字段做主键？ 可以使用联合主键，组合列唯一即可。 四：InnoDB 插入时，可不可以主动插入自增主键？ 可以指定自增列的值，但可能导致空洞。 五：InnoDB 建表时，可不可以使用联合自增主键？ 可以，但自增 ID 必须在联合主键的第一列。

作者：58沈剑_架构师之路链接：https://juejin.cn/post/6908219549351411726 来源：掘金

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

最左匹配
- 最左优先，在检索数据时从联合索引的最左边开始匹配
- 对于联合索引(col1,col2,col3)，查询语句SELECT * FROM test WHERE col2=2;是否能够触发索引？

大多数人都会说NO，实际上却是YES。
原因：

EXPLAIN SELECT * FROM test WHERE col2=2;
EXPLAIN SELECT * FROM test WHERE col1=1;

观察上述两个explain结果中的type字段。查询中分别是：

type: index
type: ref

index：这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个联合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。
ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

聚集索引就是以主键创建的索引
- 如果定义了主键，主键就是聚集索引；
- 如果没有定义主键，第一个非空 (not null) 且唯一 (unique) 列是聚集索引；
- 如果没有符合条件的列，会自动创建一个隐藏的 row-id 作为聚集索引；
非聚集索引就是以非主键创建的索引
- 也叫二级索引
- 可以使用多列来创建非聚集索引
  - 此时就涉及到了哪个列会走索引，哪个列不走索引的问题了(最左匹配原则)
  - 创建多个单列非聚集索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)
- 覆盖索引：（特殊的非聚集索引）
  - 非聚集索引，叶子节点存储的是主键+列值，最终还是要回表的，但是如果主键+索引已经包含了所要查找的列，就不需要进行回表。
  - 创建了索引(username,age)，在查询数据的时候：select username , age from user where username = 'Java3y' and age = 20。上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了。

区别：

聚集索引在叶子节点存储的是表中的数据
非聚集索引存储的是主键和索引列
使用非聚集索引的时候，拿到叶子上的主键再去查到想要查找的数据（回表：拿到主键在查找的过程）。

索引最左匹配

概念

索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否相等，遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
因此，列的排列顺序决定了可命中索引的列数。
如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。(很简单：索引命中只能是相等的情况，不能是范围匹配)

为什么会是最左匹配:

mysql创建复合索引的规则是首先会对复合索引的最左边，也就是索引中的第一个字段进行排序，在第一个字段排序的基础上，在对索引上第二个字段进行排序，其实就像是实现类似order by 字段1，字段2这样的排序规则，那么第一个字段是绝对有序的，而第二个字段就是无序的了，因此一般情况下直接只用第二个字段判断是用不到索引的，这就是为什么mysql要强调联合索引最左匹配原则的原因。
自动优化：=、in自动优化顺序

不需要考虑=、in等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

例子：

如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

索引失效

腾讯面试：一条SQL语句执行得很慢的原因有哪些？

一个 SQL 执行的很慢，我们要分两种情况讨论：
1、大多数情况下很正常，偶尔很慢，则有如下原因
(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)、执行的时候，遇到锁，如表锁、行锁。
2、这条 SQL 语句一直执行的很慢，则有如下原因。
(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)、数据库选错了索引。

索引