[MySQL]MySQL面试专题(1)

1. MySQL索引数据结构

MySQL 数据库使用的索引数据结构是什么？

B+ 树。B+ 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在 B+ 树中，所有记录节点都是按键值的大 小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。B+ 索引在数据库中有一个特点是高扇出性，因此在数据库中，B+ 树的高度一般都在 2～4 层，这也就是说查找某一键值的行记录时最多只需要 2 到 4 次 IO。因为当前一般的机械磁盘每秒至少可以做 100 次 IO，2～4 次的 IO 意味着查询时间只需 0.02～0.04 秒。数据库中的B+ 树索引可以分为聚簇索引(clustered inex)和辅助索引(secondary index)。

假设每条 sql 信息为 1KB，主键 ID 为 bigint 型，一颗高度为 4 的 B+ 树能存储多少数据？

在 Innodb 存储引擎里面，最小的存储单元是页(page)，一个页的大小是 16KB。

假设一行数据的大小是 1KB，那么一个页可以存放 16 行这样的数据。那如果想查找某个页里面的一个数据的话，得首先找到他所在的页。Innodb 存储引擎我们都知道使用 B+ 树的结构来组织数据。如果是在主键上建立的索引就是聚簇索引，即只有在叶子节点才存储行数据，而非叶子节点里面的内容其实是键值和指向数据页的指针。

如果是 2 层的 B+ 树，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：根节点指针数 * 单个叶子节点记录行数。因为单个页的大小为 16KB，而一行数据的大小为 1KB，也就是说一页可以存放 16 行数据。然后因为非叶子节点的结构是：“页指针 + 键值”，我们假设主键 ID 为 bigint 类型，长度为 8 字节(byte)，而指针大小在 InnoDB 源码中设置为 6 字节(byte)，这样一共 14 字节(byte)，因为一个页可以存放 16K 个 byte，所以一个页可以存放的指针个数为 16384/14=1170 个。因此一个两层的 B+ 树可以存放的数据行的个数为：1170 * 16 = 18720(行)。

那么对于高度为 3 的 B+ 树呢？也就是说第一层的页，即根页可以存放 1170 个指针，然后第二层的每个页也可以 存放 1170 个指针。这样一共可以存放 1170 * 1170 个指针，所以一共可以存放 1170 * 1170 * 16 = 21902400(2 千万左右)行记录。也就是说一个三层的 B+ 树就可以存放千万级别的数据了。而每经过一个节点都需要 IO 一次， 把这个页数据从磁盘读取到缓存，也就是说读取一个数据只需要三次 IO。 继续来说，高度为 4 的 B+ 树呢？ 1170 * 1170 * 1170 * 16 约等于 200 亿。

为什么选用 B+ 树而不选用二叉树或者 B 树？

B 树(balance tree)和 B+ 树应用在数据库索引，可以认为是 m 叉的多路平衡查找树，但是从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？因为我们要考虑磁盘 IO 的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页(对应索引树的节点)。所以我们要减少 IO 次数，对于树来说，IO 次数就是树的高度，而 “矮胖” 就是 B 树的特征之一，它的每个节点最多包含 m 个孩子，m 称为 B 树的阶。

B+ 树，是 B 树的一种变体，查询性能更好。 B+ 树相比于 B 树的查询优势：

• B+ 树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”。B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)，指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
• B+ 树查询必须查找到叶子节点，B 树只要匹配到即可直接返回。因此 B+ 树查找更稳定(并不慢)，必须查找到叶子节点；而 B 树，如果数据在根节点最快，在叶子节点最慢，查询效率不稳定。
• 对于范围查找来说，B+ 树只需遍历叶子节点链表即可，并且不需要排序操作，因为叶子节点已经对索引进行了排序操作。B 树却需要重复地中序遍历，找到所有的范围内的节点。

为什么用 B+ 树做索引而不使用哈希表做索引？

• 模糊查找不支持：哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而 B+ 树则可以通过最左前缀原则快速找到对应的数据。
• 范围查找不支持：如果我们要进行范围查找，例如查找 ID 为 100 ~ 400 的人，哈希表同样不支持，只能遍历全表。
• 哈希冲突问题：索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

什么是最左前缀原则？

在 MySQL 中，最左前缀原则是指在使用索引进行查询时，MySQL 会尽可能地利用索引中的最左前缀来匹配查询条件，以提高查询的效率。

具体来说，如果在创建索引时，使用的是多个列组成的复合索引，那么 MySQL 只能使用这个复合索引的最左前缀来匹配查询条件。也就是说，如果查询语句中的条件不是以复合索引的最左前缀开始的，那么MySQL 就无法使用这个索引。

举个例子，如果我们有一个名为 student 的表，其中有 id、name、age 三个字段，我们想查询 name 和 age 的值分别为 'John' 和 20 的记录。如果我们在 name 和 age 两个字段上都创建了单独的索引，那么 MySQL 会选择其中一个索引来执行查询。但如果我们在 name 和 age 两个字段上创建了一个复合索引，那么 MySQL 只会使用这个复合索引的最左前缀——也就是 name 字段来匹配查询条件，而无法使用这个索引来加速查询。

因此，在 MySQL 中，为了提高查询效率，需要根据查询的条件和频率，合理地创建索引，尽可能利用最左前缀原则。

什么是聚簇索引？

InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚簇索引(clustered index)就是按照每张表的主键构造一棵 B+ 树，同时叶子节点中存放的即为整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵 B+ 树进行排序，因此每张表只能拥有一个聚簇索引。在多数情况下，查询优化器倾向于采用聚簇索引。因为聚簇索引能够在 B+ 树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据 如用户需要查询一张注册用户的表，查询最后注册的 10 位用户，由于 B+ 树索引是双向链表的，用户可以快速找到最后一个数据页，并取出 10 条记录 SELECT * FROM Profile ORDER BY id LIMIT 10; 虽然使用 ORDER BY 对主键 id 记录进行排序，但是在实际过程中并没有进行所谓的 filesort 操作，而这就是因为聚簇索引的特点。另一个是范围查询(range query)，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

如：SELECT * FROM Profile where id > 1 and id < 100;

什么是辅助索引？

对于辅助索引(Secondary Index，也称非聚簇索引)，叶子节点并不包含行记录的全部数据。叶子节点 除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。辅助索引的存在并不影响数据在聚簇索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为 3 的辅助索引树中查找数据，那需要对这棵辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页。

说说 FIC(Fast Index Creation)原理，与普通 Index 操作有何不同？

MySQL 5.5 版本之前(不包括 5.5)存在的一个普遍被人诟病的问题是：MySQL 数据库对于索引的添加或者删除的这类 DDL 操作，MySQL 数据库的操作过程为： - 首先创建一张新的临时表，表结构为通过命令 ALTER TABLE 新定义的结构。 - 然后把原表中数据导入到临时表。 - 接着删除原表。 - 最后把临时表重命名为原来的表名。

可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。MySQL 数据库的索引维护始终让使用者感觉非常痛苦。InnoDB 存储引擎从 InnoDB 1.0.x 版本开始支持一种称为 Fast Index Creation(快速索引创建)的索引创建方式简称 FIC。对于辅助索引的创建，InnoDB 存储引擎会对创建索引的表加上一个 S 锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB 储引擎只需更新内部视图，并将辅助索引的空间标记为可用(不影响附注索引的使用，因为可读，后边的同时删除四个字非常传神)，同时删除 MySQL 数据库内部视图上对该表的索引定义即可。 由于 FIC 在索引的创建的过程中对表加上了 S 锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC 方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

有没有比 FIC 更好的方式？

虽然 FIC 可以让 InnoDB 存储引擎避免创建临时表，从而提高索引创建的效率。但正如前面面试题中所说的，索引创建时会阻塞表上的 DML 操作(除读操作)。OSC(一个 FaceBook 的 PHP 脚本)虽然解决了上述的部分问题，但是还是有很大的局限性。MySQL 5.6 版本开始支持 Online DDL(在线数据定义)操作，其允许辅助索引创建的同时，还允许其他诸如 INSERT、UPDATE、DELETE 这类 DML 操作，这极大地提高了 MySQL 数据库在生产环境中的可用性。