索引总结

非聚簇索引就是将数据存储、索引分开结构，索引结构的叶子节点指向了数据的对应行，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据（这一步称为回表查询），这也就是为什么索引不在key buffer命中时，速度慢的原因。

5、最左匹配原则

5.1、联合索引的最左匹配原则

联合索引采用的是B+树的结构，所以会有最左前缀原则/最左匹配原则，即对于联合索引，只要查询条件与联合索引从左到右部分字段顺序相匹配，该次查询就可以利用联合索引进行加速；

例如现有联合索引（x,y,z）

WHERE x=1 AND y=2 AND z=3；是对该联合索引的完全匹配。

WHERE x=1 AND y=2是利用了该联合索引（符合最左匹配原则）

WHRER x=1;也可以以利用该联合索引

WHERE y=2 AND z=3；或者WHERE z=3等就无法利用联合索引

5.2、最左匹配原则

主要针对组合索引，满足如下2个条件即可满足左前缀原则：

①需要查询的列和组合索引的列顺序一致；

②查询不要跨列；

5.3、注意

最左匹配可以是字符串索引的最左N个字符，也可以是联合索引的最左M的字段，例如：

假如date和name是联合索引

select * from T where date > '1990-01-14' and name like '%Zhang%';

select * from T where date > '1990-01-14' and name like 'Zhang%';

SQL1中name like '%Zhang%'，前后都增加模糊匹配使得MySQL无法使用到索引；

SQL2去掉最左边的%后，该SQL语句就可以使用索引。

6、引用《MySQL实战45讲》案例

6.1、建表语句

create table T (

ID int primary key,

k int NOT NULL DEFAULT 0,

s varchar(16) NOT NULL DEFAULT '',

index k(k)

) engine=InnoDB DEFAULT CHARSET=utf8;

insert into T Values

(100, 1, 'aa'),

(200, 2, 'bb'),

(300, 3, 'cc'),

(500, 5, 'ee'),

(600,6,'ff'),

(700,7,'gg');

6.2、索引结构示意

左边是以主键ID建立起的聚集索引，其叶子节点存储了完整的表记录信息；右边是以普通字段K建立的普通索引，其叶子节点的值是主键ID。

其中R1代表Row1，即第1行记录；

6.3、select语句执行过程

select * from T where k between 3 and 5;

执行流程如下：

①在K索引树上找到k=3的记录，取得ID=300；

②再到ID索引树上查找ID=300对应的R3；

③在k索引树取下一个值k=5，取得ID=500；

④再回到ID索引树查到ID=500对应的R4；

⑤在k索引树取下一个值k=6，不满足条件，循环结束。

从K索引树再到主键ID索引树的过程，叫做回表，即回到主键索引树搜索的过程。

既然回表操作浪费时间，那么该如何避免回表呢？

7、回表与覆盖索引

CREATE TABLE `stud` (

`id` int(11) NOT NULL,

`birth` date NOT NULL,

`name` varchar(14) NOT NULL,

PRIMARY KEY (`id`),

KEY `i_name` (`name`),

KEY `i_birth` (`birth`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from stud where birth > '1990-01-14';

select id from stud where birth > '1990-01-14';

SQL1这里使用的是select *，所以，MySQL先根据birth查询普通索引，然后再根据普通索引上的主键id，来找到聚集索引中的记录，然后把结果反馈给客户端；

SQL2这里使用的是select id，当MySQL先根据birth查询普通索引，然后直接把普通索引上的主键id返回给客户端；

索引birth中包含了查询语句所需要的id字段的值，无需再次回到主键索引树查找，也就是“覆盖”了我们的查询需求，我们称之为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能。

8、索引下推

select * from stud where birth > '1990-01-14' and name like 'Zhang%';

索引下推功能是从MySQL5.6版本开始支持的。在此之前，name 索引是没有使用上的，需要每次去主键索引表取完整的记录值进行比较，而从5.6版本开始，由于索引name的存在，可以直接取索引的 name值进行过滤，这样不符合"name like 'Zhang%'"条件的记录就不再需要回表操作；

9、常见的索引失效场景

9.1、总结

①离散度太大导致只能顺序读：范围查询（>,<,<>）

②因使用函数导致B+树而失效：查询条件类型不一致

③因使用函数导致B+树而失效：查询条件使用了函数

④因无法快速定位索引位置而失效：模糊查询：%XXXX

⑤因无法快速定位索引位置而失效：不使用组合索引的首个字段当条件

9.2、为什么会导致索引失效

①顺序读比离散读性能要好

在不使用覆盖索引的情况下，优化器只有在数据量小的时候才会选择使用非聚集索引。受制于传统的机械磁盘特性，通过聚集索引顺序读数据行的性能会比通过非聚集索引离散读数据行要好。所以，优化器在即使有非聚集索引、但是访问数据量可能达到送记录数的 20%时会选择聚集索引。当然也可以用force index强制使用索引。

例如：

alter table T add index i_h(hire);

select * from T where hire > '1989-06-02';

select * from T where hire > '1999-06-02';

SQL1由于是从1989年开始筛选，且hire还是非聚集索引，符合1989年的数据量大于20%时，MySQL还是抛弃了i_h，转而采用全表扫描；

而SQL2的筛选条件是从1999年开始，数据量比较少，进而用了非聚集索引；

②无法使用 B+索引快速查找

由于B+树索引支持快速查询的根本要素是因为其索引键值是有序存储的，从左到右、由小到大，进而可以在每个层级的节点中快速查并进入下一层级，最终在叶子节点找到对应的值；

再回头看看函数，当你使用函数来作为where条件时，MySQL无法判断该函数结果到底是什么，也不知道该从哪里切入B+树，对索引字段做函数操作会破坏索引值的有序性，因此，优化器选择不使用索引。

同理查询条件类型不一致也是同样的情况，因为其使用了隐式类型转换。

另外，模糊匹配、不使用组合索引的首字段作为查询条件时，均无法快速定位索引位置从而导致无法使用索引；模糊匹配当查询条件是where name like 'zhang%'时，zhang是name列的最左前缀时，是可能用上索引的（最左匹配），至于是否用上最终还是依赖优化器对查询数据量的评估；

10、隐式类型转换

10.1、官网链接

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

如果时光可以倒流，我会选择在家陪伴家人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
索引总结

目录1、定义2、引擎对索引的支持3、B树与B+树3.1、简介3.2、磁盘与B+树4、索引分类4.1、按照功能逻辑来分4.2、按照物理实现方式来分5、最左匹配原则5.1、联合索引的最左匹配原则5.2、最左匹配原则5.3、注意6、引用《MySQL实战45讲》案例6.1、建表语句6.2、索引结构示意6.3、select语句执行过程7、回表与覆盖索引8、索引下推9、常见的索引失效场景9.1、总结9.2、为什么会导致索引失效10、
复制链接

扫一扫

专栏目录