MySQL索引腾讯面试必问

索引是什么

索引的本质：减少查询的I/O次数

索引类似于一本书的目录，帮助我们快速找到我们需要的页码。MySQL的索引，本质是通过一定机制，尽量减少磁盘I/O次数以加快定位数据在磁盘中的位置来提高查询性能。

实际工程中，大量数据都是存放在磁盘里，其中机械硬盘(Hard Disk Drive、HDD)和固态硬盘(Solid State Drive、SSD)是两种最常见的硬盘。CPU 想要访问磁盘中的数据一定要先通过 I/O 操作将磁盘中的数据读入到内存中，再访问存储在内存中的数据。普通磁盘(非 SSD)加载数据需要经过队列、寻道、旋转以及传输的这些过程，大概要花费 10ms 左右的时间，加载数据的过程中，大量的时间被消耗在寻道上。对于顺序I/O来说，情况还算可观，速度可以达到 40MB/s；然而对于随机I/O来说，其与顺序I/O性能相比差距可达几个数量级。SSD较HDD而言，随机I/O的性能提升了几十倍甚至几百倍，但是不管如何，随机I/O相较于顺序I/O来说性能都会差一些。对于数据库来说，最为理想的状态就是能一次磁盘I/O顺序拿出全部数据，但是大部分情况下，我们的数据特点是多且小，就算是直接指定数据唯一ID，也无法保证一次I/O就能获取到数据。

虽然磁盘的寻道时间只需要 10ms，但是在 CPU看来已经是非常长的时间了，当我们将上述的时间等比例放大后，就能直观地感受到它们的性能差异。如果 CPU 访问 L1 缓存需要 1 秒，那么访问主存需要 3 分钟、从 SSD 中随机读取数据需要 3.4 天、磁盘寻道需要 2 个月，网络传输可能需要 1 年多的时间。
数据引用出处：https://www.51cto.com/article/623465.html

B+树

1）B+树的非叶子节点不保存具体的数据，而只保存关键字的索引，而所有的数据最终都会保存到叶子节点。因为所有数据必须要到叶子节点才能获取到，所有的查询都要查找到叶子节点，查询性能是稳定的。
2）叶子节点实际上又是通过指针相连的双向联表，在进行范围查找，排序查找，分组查找时具有先天性的优势。
B+树结构详解：https://zhuanlan.zhihu.com/p/27700617

聚簇索引与非聚簇索引

MySQL底层使用B+树来存储索引，数据均存在叶子节点上。聚簇索引的叶子节点存储的是数据，而非聚簇索引的叶子节点存储的是主键信息及索引信息。非聚簇索引查询过程中，需要在非聚簇索引的B+树中检索到数据的主键，再到聚簇索引中获取其详细数据（覆盖索引除外）。
在InnoDB中，只存在一个聚集索引：

• 若表存在主键，则主键索引就是聚集索引；

• 若表不存在主键，则会把第一个非空的唯一索引作为聚集索引；

• 否则，会隐式定义一个rowid作为聚集索引。

回表查询

select * from table_name where primary_key = 1000;

在主键索引树上，通过主键就可以一次性查出我们所需要的数据，速度很快。因为主键就和行记录存储在一起，定位到了主键就定位到了所要找的包含所有字段的记录。

select * from table_name where name = index;

但是对于非聚集索引，需要先根据name字段所在的索引树找到对应主键，然后通过主键索引树查询到对应记录，这个过程叫做回表查询。

索引覆盖

建立(sample_id, frame_arg, sample_vid)联合唯一索引

ALTER TABLE `tbl_sample_frame` ADD UNIQUE `sample_task` USING btree (	
sample_id, frame_arg, sample_vid);

SELECT * FROM `*` WHERE `sample_id` = '1000670';  回表查询

SELECT * FROM `tbl_sample_frame` WHERE `sample_id` = '1000670';   索引覆盖

可以看到，回表查询多进行了一次I/O，无疑会降低查询的效率（1ms -> 3ms），那么有没有办法让它不回表呢？这就是索引覆盖。所谓索引覆盖指：在使用这个索引查询时，使它的索引树的叶子节点上的数据可以覆盖你查询的所有字段，就可以避免回表了。

EXPLAIN 是什么

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SQL 语句进行分析, 并输出 SQL 执行的详细信息, 以供开发人员针对性优化。

EXPLAIN SELECT * FROM `tbl_sample_frame` WHERE `sample_id` = '1000670'

执行结果：

EXPLAIN 结果中的type字段

• system：系统表，少量数据，往往不需要进行磁盘IO
• const：常量连接
• eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
• ref：非主键非唯一索引等值扫描
• range：范围扫描，索引上的范围查询，它会在索引上扫码特定范围内的值
• index：索引树扫描，需要扫描索引上的全部数据，它仅比全表扫描快一点
• ALL：全表扫描(full table scan)

扫描速度：

system > const > eq_ref > ref > range > index > ALL

最左匹配原则

最初理解：在联合索引中，从最左边的字段开始匹配，若条件中字段在联合索引中符合从左到右的顺序则走索引，否则不走。可以简单理解为(a, b, c)的联合索引相当于创建了a索引、(a, b)索引和(a, b, c)索引。

SELECT sample_id, frame_arg FROM `tbl_sample_frame` WHERE `sample_vid` = '1000670'

上面这句sql明显不符合最左匹配原则，不会走到索引；但我们用EXPLAIN语句分析后，会发现一个很有趣的现象，使用了索引。

对此，继续执行不同sql语句，查看索引效果：

查询 sample_id 走ref索引
EXPLAIN SELECT * FROM `tbl_sample_frame` WHERE `sample_id` = '1000670'

查询 sample_vid and sample_id 走ref索引
EXPLAIN SELECT * FROM `tbl_sample_frame` WHERE `sample_vid` = 'w14655rm23x' AND `sample_id` = '1000670'

查询 sample_vid 不走索引
EXPLAIN SELECT * FROM `tbl_sample_frame` WHERE `sample_vid` = 'w14655rm23x'

总结：若符合最左覆盖原则，则走ref这种索引（需注意：查询的时候如果两个条件都用上了，但是顺序不同，如 sample_vid = ‘w14655rm23x’ AND sample_id = ‘1000670’，那么查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。）；若不符合最左匹配原则，但是符合覆盖索引（index），就可以扫描整个索引树，从而找到覆盖索引对应的列，避免回表；若不符合最左匹配原则，也不符合覆盖索引（如本例的select *），则需要扫描整个索引树，并且回表查询行记录，此时，查询优化器认为这样两次查找索引树，还不如全表扫描来得快（因为联合索引此时不符合最左匹配原则，要不普通索引查询慢得多），此时则走全表扫描。

最后的一点疑问，以下两个语句都是全表扫描，为什么执行时间相差两倍？希望哪位大佬可以告诉我，万分感谢！！！

SELECT * FROM `tbl_sample` WHERE `content` LIKE '%做过%'

SELECT * FROM `tbl_sample` WHERE id = (SELECT id FROM `tbl_sample` WHERE `content` LIKE '%做过%')