索引是什么
索引的本质:减少查询的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 '%做过%')