【MySQL系列】-回表、覆盖索引真的懂吗
在面试时常会被问一些概念性的东西。这些内容其实在开发中比较少用，但是为了显示你的知识储备你必须学习。博主最近在考Mysql认证时，也常碰到这样的问题。整理MySQL概念输出这篇博文。
一、MYSQL索引结构
1.1 索引的概念
MYSQL官方对索引的定义为：索引（Index）是帮助MySQL提高获取数据的数据结构。索引的本质是数据结构。可以简单理解为"预先排好一组能快速查询的数据结构"。这些数据结构以某种方式指向数据，可以通过这些数据结构实现高级查询算法。
1.2 索引的特点
- 索引一个排序的数据结构可以加速数据库的检索速度。
- 索引降低了数据库Insert、Update、Delete等维护任务的难度
- MySQL索引只能创建在表上，不能创建在视图上。
- 查询处理器执行SQL语句，一个表上，一次只能使用一个索引
1.3 索引的优点
- 提高数据检索的效率，降低数据库的IO成本
- 创建唯一性的索引，保证数据库表中每一行数据的唯一性。
- 加速表和表之间的连接。
- 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
1.4 索引的缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
二、B-Tree与B+Tree
2.1 B-Tree
B-Tree及为B树。B树是一种自平衡的树，能够保持数据有序。这种数据结构能够让查询数据，顺序访问、插入数据及删除的动作，都在对数时间内完成。B数概况来说是一个一般化的二叉查找树，可以拥有多于2个子节点。与自平衡二叉查找树不同，B树为系统大块数据的读写操作做了优化。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。
2.2 B+Tree
B+Tree是B-Tree的一种优化。节点上只存储键值，不存储数据。这样的设计在有限的节点空间（页空间）内可以存放更多的键值、指针。所有数据都存放在叶子节点中，所有叶子节点之间有链指针（双向循环列表），便于范围查询，也便于排序。
2.3 B-Tree 与B+Tree树的区别
- B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。
- B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
- B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。
B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。
2.4 那么为什么InnoDB的主键最好要搞成有序的？
InnoDB中主键索引是聚集索引，所有数据都存在主键索引所在的聚集索引的B+Tree结构的叶子节点中。如果每次插入的主键是大小随机的话，每次数据进来找到的叶子节点的位置是随机的，这样的话，有些叶子节点所在页本来就排满了，结果又来了一条数据，就势必要引起页分裂，所以导致性能下降；但是如果主键是有序的话，每次进行都找到当前叶子前面的位置，一个一个叶子按顺序排满一个页再排一个页，就不会又页分裂的问题了。所以自增主键对于InnoDB这种使用B+Tree索引的存储引擎来说，性能更好。
三、回表查询
回表查询就是在数据查询过程中MySQL内部需要两次查询。既先定位查询数据所在表的主键值，在根据主键定位行记录。
要弄清楚回表查询，我们就要先从InnoDB的索引实现说起，InnoDB索引分为两大类：聚集索引（Clustered Index）和普通索引（Secondary Index）
3.1 InnoDB 聚集索引
聚集索引是索引结构和数据一起存放的索引。主键索引为聚集索引。
InnoDB聚集索引的叶子节点存储行记录，因此InnoDB 必须要有且只有一个聚集索引。
- 如果表定义了 PK （Primary Key，主键），那么 PK 就是聚集索引；
- 如果表没有定义 PK，则第一个 NOT NULL UNIQUE 的列就是聚集索引。
- 否则 InnoDB 会另外创建一个隐藏的 ROWID 作为聚集索引。
由于这种机制是直接定位行记录，因此使得基于 PK 的查询速度非常快。
3.2 InnoDB非聚集索引
非聚集索引是索引结构和数据分开存在的索引。辅助索引就是非聚集索引。
非聚集索引的叶子节点不一定存储的是数据的指针（辅助索引的叶子节点存储的是就是主键，然后根据主键在回表查询数据。）
3.3 InnoDB回表
回表查询，就是先通过非聚集索引查询到对应的主键，在通过主键索引查询到对应的值。两次经过B+Tree索引。
四、覆盖索引
如果执行一个查询语句不经过两次B+Tree查询直接得到要查询的值，这个时候就不需要回表，也就是说在这个查询中，索引"覆盖了"查询，这个称为覆盖索引。
由于覆盖索引减少B+Tree是搜索次数，提高查询性能，所以使用覆盖索引是一个常用的索引手段。使用覆盖索引最常见的方法是创建联合索引，将需要查询的字段都放在联合索引上。
用explain sql，如果Extra中有using index，则证明使用到了覆盖索引。
五、最左前缀原则
最左前缀就是利用索引来加速检索，最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符，就是说你要查询N个字段就包含在某个联合索引的最左N个字段内，简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。
最左前缀原则总结
- 假设有三个字段(col1, col2, col3)，MySQL可以支持(col1), (col1, col2), 和(col1, col2, col3)的联合索引。
- 比较有争议的(col1, col3) 是否支持联合索引，官方给的文档中是支持的，我们试验也是支持。
- where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序。
- where子句，若遇到范围查询（> < between, like）或未在总结1中创建的索引对时，就会停止匹配（遇到的范围查询还是参与索引）。
六、索引失效
建好索引后，但是一些不好的SQL会导致索引失效，有一下几种场景会导致失效。文章来源：https://www.toymoban.com/news/detail-660897.html
- 查询条件中有OR，即使有部分条件带索引也会失效；
- LIKE查询时已%开头；
- 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引；
- 索引列上参与计算会导致索引失效；
- 违背最左匹配原则；
- 如果Mysql估计全表扫描要比使用索引要快，会不适用索引
- B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走；
- 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走
七、索引下推
索引下推（index condition pushdown ）简称ICP，在Mysql5.6以后的版本上推出，用于优化回表查询；在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 ；在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，
然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 ；文章来源地址https://www.toymoban.com/news/detail-660897.html
- 查看索引下推的状态
show VARIABLES like '%optimizer_switch%';
-------------------------------------------------------
optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
- 关闭索引下推
#索引下推是mysql 5.6优化查询回表的功能，在5.6之前都不支持索引下推
set optimizer_switch='index_condition_pushdown=off';
- 开启索引下推
set optimizer_switch='index_condition_pushdown=on';
-
总结
- 索引下推功能是mysql 5.6推出优化回表的操作，只支持向上兼容，低版本是不支持的；
- 索引下推优化的只是回表次数，扫描行数还是一样的。
到了这里，关于【MySQL系列】-回表、覆盖索引真的懂吗的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
