MySQL 之索引和存储引擎

磁盘 IO 预读能力

操作系统和磁盘之间一次数据交换是以页为单位的，一页大小为4K，即每次IO操作系统会将4K数据加载进内存。

面试 B TREE 和 B+TREE

B TREE 和 B+TREE区别是什么？
B+Tree根节点和支节点没有数据区，关键字对应的数据只保存在叶子节点中。即只有叶子节点中的关键字数据区才会保存真正的数据内容或者是内容的地址。而在B树种，如果根节点命中，则会直接返回数据。

MySQL为什么最终要去选择B+Tree？
B+TREE 的根节点和支节点不保存数据区，所以 B+TREE 读写一次磁盘加载的关键字比 B TREE 更多。

Innodb 默认主键_rowid

Innodb表中在没有默认主键的情况下会生成一个6byte空间的自动增长主键。

什么是聚集索引

物理地址和列值的逻辑顺序相同，一般用主键，所以一个表中只能拥有一个聚集索引。

MyISAM存储引擎

如何查找数据的呢？
如果要查询id = 40的数据：先根据MyISAM索引文件去找id = 40的节点，通过这个节点的数据区拿到真正保存数据的磁盘地址，再通过这个地址从MYD数据文件中加载对应的记录。

如果有多个索引，表现形式如下：

所以在MYISAM存储引擎中，不存在主键索引和辅助索引，都属于索引，没有主次之分。

Innodb存储引擎

Innodb主键索引为聚集索引。
主键索引中叶子节点的数据区保存的就是真实的数据，在通过索引进行检索的时候，命中叶子节点，就可以直接从叶子节点中取出行数据。

辅助索引与主键索引关系：

主键索引的叶子节点保存的是真正的数据。
而辅助索引叶子节点的数据区保存的是主键索引关键字的值。

假如要查询name = C 的数据，其搜索过程如下：
先在辅助索引中通过C查询最后找到主键id = 9。
在主键索引中搜索id为9的数据，最终在主键索引的叶子节点中获取到真正的数据。
所以通过辅助索引进行检索，需要检索两次索引。

MySQL B+Tree 具体落地形式

创建了两张表，user_innodb和user_myisam，分别指定索引为innodb和myisam。对于每张表，MySQL会创建相应的文件保存数据和索引，具体如下：

-rw-rw----. 1 mysql mysql      8652 May  3 21:11 user_innodb.frm
-rw-rw----. 1 mysql mysql 109051904 May  7 21:26 user_innodb.ibd
-rw-rw----. 1 mysql mysql      8682 May 16 18:27 user_myisam.frm
-rw-rw----. 1 mysql mysql         0 May 16 18:27 user_myisam.MYD
-rw-rw----. 1 mysql mysql      1024 May 16 18:27 user_myisam.MYI

MYISAM存储引擎存储数据库数据，一共有三个文件：

• frm：表的定义文件。
• MYD：数据文件，所有的数据保存在这个文件中。
• MYI：索引文件。

Innodb存储引擎存储数据库数据，一共有两个文件(没有专门保存数据的文件)：

• frm文件： 表的定义文件。
• Ibd文件：数据和索引存储文件。数据以主键进行聚集存储，把真正的数据保存在叶子节点中。

InnoDB 和 MYISAM 区别

MySQL5.5版本之前默认采用的是MyISAM引擎，5.5之后默认采用的是InnoDB引擎。

对比项	MyISAM	InnoDB
主键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁住某一行，不对其他行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小性能有决定性的影响
表空间	小	大
适合场景	适合读多写少场景，因为读不涉及事务和锁，且占用表空间小，一般作为读库	适合写多读少场景，因为写需要涉及事务，一般作为写库

什么是索引

索引是一种排好序的数据结构，可以用来减少IO次数，加速查询。
索引包括：（primary key，unique key，index key），是存储引擎用于快速找到记录的一种数据结构。
primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果。

索引虽然会加快查询，但是会降低写入的效率，因为写的时候需要重新维护索引，并且占用磁盘空间，所以不能无限制的创建索引。

强调： 一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据。

检索原理：
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

为了加快Col2列的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引健值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉树查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

二叉树

1、一个节点只能有两个子节点，也就是一个节点不能超过2。
2、左子节点 小于 本节点；右子节点 大于等于 本节点，比我大的向右，比我小的向左。

对二叉树节点查找：
深度为 1 的节点的查找次数为 1；
深度为 2 的节点的查找次数为 2；
深度为 n 的节点的查找次数为 n；

二叉树存在的问题：
如果数据id是持续递增的话，会退化成一个链表结构

平衡二叉数

左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

平衡二叉数存在的问题：
树高度问题导致磁盘IO过多。（树高，即磁盘的IO次数最坏的情况下就等于树的高度）。
需要将瘦高的身形变成矮胖，通过降低树的高度达到减少IO的数据。

B树

每个节点占用一个磁盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。

模拟查找关键字29的过程：
1、根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第一次】
2、比较关键字 29 在区间（17，35）中间，找到磁盘块 1 的指针 P2。
3、根据 P2 指针找到磁盘块 3，读入内存。【磁盘I/O操作第二次】
4、比较关键字 29 在区间（26，30）中间，找到磁盘块 3 的指针 P2。
5、根据 P2 指针找到磁盘块 8，读入内存。【磁盘I/O操作第三次】
6、在磁盘块 8 中的关键字列表中找到关键字 20。

B+树

图中可以看出所有data信息都移动到叶子节点中，而且子节点和子节点之间会有个指针指向，这个也是B+树的核心点，这样可以大大提升范围查询效率，也方便遍历这个树。
1、非叶子节点不在存储数据，数据只存储在同一层的叶子节点上。
2、叶子之间，增加了链表，获取所有节点，不再需要中序遍历。

B+树检索原理：

B+树相对于B树有几点不同：

• 非叶子节点只存储健值信息。
• 所有叶子节点之间都有一个链指针。
• 数据记录都存放子叶子节点中。

索引数据结构总结

二叉树存在的问题：极端情况下汇退化成一个链表，比如数据是有顺序的。
平衡二叉数：左右两个子树的高度不超过1，存在的问题：如果树高度过高的情况下，会导致IO过多，IO次数等于树的最高高度。
B树：每个节点占用一块磁盘空间，节点上有两个数据，并且有3个指针，分别指向小于，中间，大于的磁盘块指针。
B+树：所有数据到放到了叶子节点，且叶子节点之间用链接连起来，这样可以方便遍历这个树。

MySQL常用的索引

普通索引INDEX： 加速查找
唯一索引：

• 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
• 唯一索引UNIQUE：加速查找+约束（不能重复）

联合索引：

• PRIMARY KEY(id,name)：联合主键索引
• UNIQUE(id,name)：联合唯一索引
• INDEX(id,name)：联合普通索引

各种索引的应用场景

会员表、字段如下：

会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

会员编号，作为主键，使用 PRIMARY。
会员姓名，如果要建索引的话，那么就是普通的 INDEX。
会员身份证号码，如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）。

除此之外还有全文索引，即：FULLTEXT。
会员备注信息 ，如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，并不会使用MySQL自带的该索引，而是会选择Lucene搜索引擎。

索引的两大类型hash与btree

在创建索引的时候，可以为其指定索引类型：hash、btree。（B+树就是BTREE）
hash类型的索引： 只支持in跟=，不支持范围查询，时间复杂度：O(1)。innodb不支持Hash。
btree类型的索引： 支持范围查询，时间复杂度：O(log n)。innodb默认支持它。

不同的存储引擎支持的索引类型也不一样：

• InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；（注意工具创建索引选择Hash会自动跳到B-tree）
• MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
• Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
• NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
• Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

因为几乎就用InnoDB和MyISAM存储引擎，且这两个不支持Hash索引，所以一般只用Btree索引。

HASH

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

BTREE

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。

hash和btree的区别

• 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
• 如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
• B+树索引结构适用于绝大多数场景，比如：范围查询、排序、分组等查询特征，用B+树索引就可以了。

正确使用索引

范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、。

大于号、小于号：

如果写where id >1 and id <1000000会发现，随着范围的增大，速度会越来越慢，会成倍的体现出来。

不等于!=：

between …and…

​ like

使用like的时候，通配符写在最前面，也是需要全匹配一遍。

尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，唯一键的区分度是1。

联合索引

指对表上的多个列合起来做一个索引。

注意建立联合索引的一个原则： 最左匹配原则，所以建联合索引的时候，将区分度高的放在最左边，依次排下来，范围查询的条件尽可能的往后边放。

Mysql索引失效场景

例如： 一张USER表 有字段属性 name，age 其中name为索引。

索引失效的情况

• select * from USER where name=‘xzz’ or age=16;
  当语句中带有or的时候即使有索引也会失效。
• select * from USER where name like‘%xzz’;
  当语句索引 like 带%的时候索引失效（注意：如果上句为 like‘xzz’此时索引是生效的）
• select * from USER where name=123;
  如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

假如将a、b、c设置为联合索引，如果查询条件中不包含a的话，就会走index，即遍历索引树，如果查询条件中包括了a，就会走rang，即检索索引给定的范围行。

Where条件中IN可以使用索引， NOT IN 无法使用索引。
查询条件中使用函数，索引将会失效。

面试索引

什么是索引：
索引是排好序的数据结构，能够就少I/O次数。
MySQL使用B+树数据结构作为索引。
B树和B+树最大的区别是将数据存储到了叶子节点，并且叶子节点之间用指针相连。

MySQL有几种类型索引：

• 主键索引PRIMARY KEY：不为空、不能重复
• 唯一索引UNIQUE：可为空，不能重复
• 普通索引INDEX：加速查找
• 联合索引：PRIMARY KEY(id,name)：联合主键索引 UNIQUE(id,name)：联合唯一索引INDEX(id,name)：联合普通索引

索引优化原则：
最左前缀匹配原则：索引作为条件放在最左边。
使用like时注意：like ‘%L%’，不走索引、like ‘L%’走索引。
字段加函数不走索引。
数字作为字符串来查询，将数字加上引号。不加引号会使索引失效，MySQL会在数字列上加数据类型转换函数。
使用组合索引时，查询时一定要让组合索引中的第一个索引作为查询条件，否则就会走索引扫描。
尽量用表连接(join)替代子查询，表连接性能好于子查询，子查询会创建临时表。

InnoDB 存储引擎索引实现：
InnoDB 索引实现使用的是聚集索引。
表数据文件本身就是按主键进行排序组织的成一个B+树文件，叶子节点上包括了完整的数据记录。
表没有创建主键索引，MySQL会查找一个所有行都唯一的列作为索引来构建表数据文件，如果找不到这样的列就会新增一个隐藏的自增主键作为索引来构建表数据文件。
非主键索引结构的叶子节点存储的是主键值，为了节省存储空间，但是增加了回表的操作，回表就是通过非主键索引找到的主键值再到表数据文件中查询。

MyISAM存储引擎索引实现：
索引文件和数据文件是分离的（非聚集）。

联合索引最左前缀原则：

参考：
这篇 MySQL 索引和 B+Tree 讲得太通俗易懂
MySQL索引背后的数据结构及算法原理