文章目录
参考文章
1.索引概述
-
索引是一种可以加快随机查找和范围查找效率的特殊数据结构,有效降低数据库的I/O成本;
-
MySQL的索引结构是B+树,特点是非叶子结点不存储数据。
-
索引本身存储在磁盘上的文件中:
– MyISAM:xxx.myi,只存储索引;
– InnoDB:xxx.myd,存储索引和数据;
-
索引并非全是优点:
– 优点:提升随机查找和范围查找效率;
– 缺点:占用磁盘空间,且降低表更新的效率,因为每次增删改,索引也要随之更新;
-
索引分为五种类型:主键索引、唯一索引、组合索引、普通索引和全文索引;
-
聚簇索引只存在于InnoDB,key的选择顺序:主键 > 唯一键 > row_id。
-
非聚簇索引在查询完整数据时必定会回表(二次查找)
– MyISAM:根据地址查找完整数据
– InnoDB:根据key在聚簇索引中查找其他字段值
2.索引数据结构
2.1.为什么不用其他数据结构
Hash索引
- 所有数据存在内存中,浪费内存空间;
- Hash是无序的,只能等值查询,无法实现范围查询
二叉树 / 红黑树
随着结点不断增加,树的深度也会不断增加,最终会导致I/O次数过多。
B树(BalanceTree)- 多路平衡查找树
特点:
- 所有键、值分布在整棵树当中;
- 数据可以分布在非叶子结点中,性能逼近二分查找。
缺点:
由于非叶子结点也有Key和Data,当Data过大将会导致结点存储的Key数量变得很小(每个结点只存储16k),这同样会导致树的深度加大,从而增加I/O次数。
2.2.MySQL采用的索引结构:B+树
特点与优点:
- 非叶子结点只存储key和指针,叶子结点中存储key和data - 避免data过大时导致深度过高,提升随机查找的效率;
- 叶子结点之间两两相连 - 提升范围查找的效率。
3.五种索引类型
- 主键索引:建表时自动对主键创建;
- 唯一索引:建表时若没有主键,就会对唯一键创建唯一索引;
- 组合索引:为多个列建立索引,涉及的知识点是(针对组合索引的)最左匹配原则;
- 普通索引:为普通字段建立的索引;
- 全文索引
最左匹配原则:假设创建了组合索引(name, age),有以下四条SQL语句:
√ SELECT * FROM student WHERE name = 'zs'; × SELECT * FROM student WHERE age = 14; √ SELECT * FROM student WHERE name = 'zs' AND age = 14; √ SELECT * FROM student WHERE age = 14 AND name = 'zs';
只有1、3、4会使用索引,而4会使用索引是因为SQL优化器优化了SQL的处理顺序,使之顺序调整成了3。
4.聚簇索引和非聚簇索引
4.1.聚簇索引(聚集索引)
使用InnoDB引擎时,只要表被创建,就必定会建立一个聚簇索引;
聚簇索引中,叶子结点的data存储的是非key的其他字段值;
而聚簇索引中key的选择顺序为:主键 > 唯一键 > MySQL生成的row_id,即
- 有主键时,对主键创建聚簇索引;
- 没有主键时,对唯一键创建聚簇索引;
- 既没有主键也没有唯一键时,在MySQL底层生成一个6位的row_id来创建聚簇索引。
据此便有以下结论:
- 主键索引一定是聚簇索引;
- 唯一索引不一定是聚簇索引(若有主键索引,那么这个唯一索引是后续建立的非聚簇索引);
- 聚簇索引不一定是主键索引(有可能是唯一索引)。
4.2.非聚簇索引(非聚集索引、辅助索引、二级索引)
使用MyISAM引擎时,只要表被创建,就必定会建立一个非聚簇索引;
使用InnoDB引擎时,在表创建完毕后手动再建立的索引都是非聚簇索引;
针对不同的引擎,非聚簇索引的叶子结点data存储的数据有所不同:
- InnoDB:存储聚簇索引中该结点的key;
- MyISAM:存储这条记录的地址。
使用非聚簇索引查找完整的数据时,总是要回表(二次查找)
回表:说白了就是二次查找。常见的两种回表场景:
- MyISAM:在非聚簇索引中根据key查出地址后,再根据地址获取完整数据;
- InnoDB:在非聚簇索引中查出聚簇索引的key后,再根据key在聚簇索引中获取其他字段值。
顺带一提,避免回表的过程,被称作索引覆盖
索引覆盖 / 覆盖索引
假设对name字段建立了索引,此时看以下两条SQL语句:
SELECT * FROM student WHERE name = 'zs'; SELECT id FROM student WHERE name = 'zs';
第一条SQL语句在name对应的非聚簇索引树中找到id值后,需要通过回表获取剩余字段值;
第二条SQL语句在找到id后不需要再回表了,即此次查询触发了索引覆盖。
5.两种引擎的索引特性
-
InnoDB在建表时,一定会创建一个聚簇索引(主键 > 唯一键 > row_id);
-
MyISAM在建表时,一定会创建一个非聚簇索引,叶子结点存储完整数据的地址。