目录
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、隐式类型转换
10.1、官网链接
1、定义
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
2、引擎对索引的支持
索引是由MySQL的存储引擎来实现的,而不是MySQL来实现的,因此,不同存储引擎所支持的索引各不相同;
Innodb存储引擎支持B+树索引、全文索引和hash索引。其中 Innodb存储引擎支持的hash索引是自适应的,Innodb存储引擎会根据表的使用情况自动为表生成hash索引,不能人为干预。
3、B树与B+树
3.1、简介
B树又叫多路平衡搜索树,一颗m叉的B Tree特性如下:
①树中每个节点最多包含m个孩子节点;
②除根节点与叶子节点外,每个节点至少有[ceil(m/2)]个孩子节点;
③若根节点不是叶子节点,则至少有两个孩子;
④所有的叶子结点都在同一层;
⑤每个非叶子结点由n个key与n+1个指针组成,其中[ceil(m/2) -1]<=n<=m-1;
注明:ceil:返回大于或者等于指定表达式的最小整数
B+树是B树的变种,B+树与B树的区别为:
①n叉B+树最多含有n个key,而B树最多只能含有n-1个key;
②B+树的叶子结点保存所有的key信息,依照key的大小顺序排列;
③所有的非叶子节点都可以看作key的索引部分;
B+树的 3 个优点:
①层级更低,IO 次数更少;
②每次都需要查询到叶子节点,查询性能稳定;
③叶子节点形成有序链表,范围查询方便;
3.2、磁盘与B+树
从磁盘上读取数据,耗时由下面几部分组成:寻道时间 + 寻点时间 + 传输时间;鉴于索引本身也很大,不会存储在内存中,往往是以索引文件的形式存储在磁盘上的,现在的问题在于如何在IO最少次数的前提下,获取到索引文件的内容?
而B+树层次低,所以,结合磁盘的情况,决定采用B+树;
4、索引分类
4.1、按照功能逻辑来分
单值索引(单列索引)
单值索引即一个索引只包含单个列,一个表中可以有多个单列索引;
create index 索引名字 on 表名(列名);
drop index 索引名字;
主键索引
alter table 表名 add primary key 表名(列名);
alter table 表名 primary key;
唯一索引
索引列的值必须唯一,但允许有空值;
create unique index 索引名 on 表名(列名);
drop index 索引名 on 表名;
复合索引(联合索引)
复合索引即一个索引中包含多个列,在数据库操作期间,相对于相同的多个列建立单值索引,复合索引所需要的开销更小;
create unique index 索引名 on 表名(列名1,..列名N);
drop index 索引名 on 表名;
4.2、按照物理实现方式来分
聚簇索引(聚集索引)
聚簇索引就是将数据存储与索引放到了一块,找到索引也就找到了数据。InnoDB的聚簇索引实际上是在同一个B Tree结构中同时存储了索引和整行数据,通过该索引查询可以直接获取查询数据行。
聚簇索引不是一种单独的索引类型,而是一种数据的存储方式,聚簇索引的顺序,就是数据在硬盘上的物理顺序。在 MySQL 通常聚簇索引是主键的同义词,每张表只包含一个聚簇索引(其他数据库不一定)。
辅助索引(二级索引)
非聚簇索引就是将数据存储、索引分开结构,索引结构的叶子节点指向了数据的对应行,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、建表语句
|
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、回表与覆盖索引
|
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、隐式类型转换