MySQL 的索引-优快云博客

本文链接：https://blog.youkuaiyun.com/Coffeemaker88/article/details/146778948

MySQL 的索引

文章目录

MySQL 的索引

本文对 MySQL 中的索引进行学习与回顾，主要的参考资料来自 csview，原文的链接如下：https://www.csview.cn/mysql/indexing.html
在这里插入图片描述

MySQL 为什么使用 B+ 树来索引，它的优势是什么？

定义与特性
B+ 树是一种多叉树，叶子结点存放数据，非叶子结点之存放索引，每个节点里的数据按主键顺序存放。在叶子结点中，包含所有索引值信息，切每一个叶子结点都指向下一个叶子结点，形成一个链表。B+ 树存储千万级数据只需要 3～4 层高度就可以满足，千万级表的目标查询最多只需要 3～4 次磁盘 I/O。

B+ 树与 B 树相比

特性	B树	B+树
数据存储位置	所有结点都可能存放数据	只有叶子结点存储实际数据
叶子结点连接	叶子结点之间不相连	叶子结点之间通过指针形成有序链表
非叶子结点内容	存储键值和数据指针	只存储键值（索引）
查询性能	不稳定（因为非叶子结点也可能存放数据）	稳定（必须访问到叶子结点）
范围查询效率	需要中序遍历	通过叶子结点之间相连的链表直接顺序访问
空间利用率	相对 B+ 树而言较低	更高，因为非叶子结点不存放数据指针
相同数据量高度	通常比 B+ 树高	通常比 B 树低

优势

单点查询：B 树进行单个索引查询时，最快可以在 $O (1)$ 的时间复杂度那查找到。从平均代价来看，B 树比 B+ 树耗时更短，但 B 树的查询时间波动较大，因为每个结点既存储索引又存储数据。B+ 树的非叶子结点不存放实际数据，仅存放索引，数据量相同的情况下，B+ 树的非叶子结点存放的索引更多，因此查询到底层结点的磁盘 I/O 更少。
插入和删除效率：B+ 树有大量冗余结点，删除一个结点时，可以直接从叶子结点中删除，甚至可以不动非叶子结点，删除非常快。B+ 树的插入也是同理，虽然插入可能存在由于结点饱和带来的结点分裂，但是最多只涉及一条路径。B 树没有冗余结点，删除结点时非常复杂，可能涉及到树的变形。
范围查询：B+ 树的叶子结点之间通过链表相连，范围查询的效率显然比 B 树更高。针对存在大量范围查询的场景，适合使用 B+ 树，也就是说适合使用关系型数据库。而对于大量的单个索引查询的场景，可以考虑使用 B 树，比如 NoSQL 当中的 MongoDB。

索引有哪些种？

单值索引

一个索引只包含单个列，一个表可以有多个单值索引。

建表时，加上 key 指定；
单独创建，create index 索引名 on 表明(列名)；
单独创建，alter table 表明 add index 索引名(列名)；

唯一索引

索引列的值必须唯一，但允许有 null 且 null 可出现多次。

建表时，加上 unique(列名) 指定；
单独创建，create unique index idx 表明(列名) on 表名(列名)；
单独创建，alter table 表名 add unique 索引名(列名)；

主值索引

设定为主键后，数据库会自动建立索引，InnoDB 为聚簇索引，值必须唯一且不为 null。

建表时，加上primary key(列名) 指定。

复合索引

一个索引包含多个列。

建表时，加上 key(列名表名) 指定；
单独创建，create index 索引名 on.表名(列名列表)；
单独创建，alter table 表名 add index 索引名(列名表名)；

前缀索引

对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立索引。使用前缀索引的目的是减少索引占用的存储空间，提升查询效率。

单独创建，alter table 表名 add 索引名(column_name(索引长度))；

什么是覆盖索引？请举例说明

覆盖索引指的是查询的列都包含在了索引当中，故无需回表查询数据文件。覆盖索引不是一种索引类型，而是一种索引的使用方式，是索引的一种高效使用场景【应该仔细区分覆盖索引和复合索引，前者是索引的使用方式，是一种查询优化技术，描述的是查询与索引之间的关系，而后者是一种具体的索引结构，在物理上按照指定列顺序存储数据】。

覆盖索引的具体使用案例如下：

-- 创建索引
create index idx_name_age on users(name, age);

-- ✅覆盖索引查询
select name, age from users where name = 'yggp';

-- ❌非覆盖索引查询
select name, age, address from users where name = 'yggp';
-- 非覆盖索引的场景下, 需要回表查询 address 数据

什么是最左匹配原则？

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引匹配。在使用复合索引时，MySQL 会从索引最左列开始，向右连续匹配，直到遇到范围查询（>、<、BETWEEN、LIKE等）为止。

索引区分度？

当查询优化器发现某个值出现在表中数据行占比过高时，会忽略索引而直接全表扫描。

联合索引如何排序？

可以利用索引的有序性，给索引列和排序列建立一个联合索引。

使用索引会有哪些缺陷？

索引大大提高了表的查询速度，但是会降低表的更新速度。在对表进行 INSERT、UPDATE 和 DELETE 时，MySQL 不仅要保存数据，还要维护索引。

什么时候需要/不需要创建索引？

需要使用索引的情况

表的 primary 字段：自动成为唯一索引；
表的字段唯一约束：可以通过建立唯一索引来确保；
直接条件查询的字段：常用于 WHERE 查询的字段可以建立索引提高查询效率；
查询与其他表关联的字段：可以为外键字段建立索引；
查询中排序的字段：排序的字段如果通过索引去访问将大大提高排序速度；
查询中统计或分组统计的字段：经常用于 GROUP BY 和 ORDER BY 的字段可以创建联合索引。

不需要使用索引的情况

表记录很少：不需要索引；
经常插入、修改、删除的字段：如果对这类字段建立索引，由于需要维护 B+ 树的有序性，因此需要频繁地重建索引，导致数据库的性能下降；
数据重复且分布平均的表字段：例如“Gender”字段，只有“male”和“female”两种值，且值的分布比例大约在 50%，对这个字段建立索引一般不会提高数据库的查询速度；
经常和 primary 字段一起查询但 primary 字段索引值较多的字段不需要建立索引。

索引的优化（使用索引的注意事项）？

like 语句的前导模糊查询不能使用索引：

select * from doc where title like '%XX';	--❌不能使用索引
select * from doc where title like 'XX%'; 	--✅非前导模糊查询, 可以使用索引

union、in、or 都能名字索引，建议使用 in：因为 in 的综合效率最高。

负向条件查询不能使用索引：负向条件包括!=/<>/not in/not exists/not like等。

联合索引的最左匹配原则。

在联合索引当中，范围列之后的索引全部失效。

不要在索引上做任何操作（计算、函数、强制类型转换），否则会导致索引实效而转向全表扫描。

更新十分频繁、数据区分度不高的列不宜建立索引。

利用覆盖索引来进行查询操作，避免回表，减少 select * 的使用。

索引不包含有 NULL 值的列，IS NULL 和 IS NOT NULL 无法使用索引。

如果有 order by、group by 的场景，可以使用索引的有序性。

如果明确知道只有一条返回结果，使用 limit 1 可以提高效率。

超过三个表最好不要 join。

单表索引建议控制在 5 个以内。

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建立唯一索引。

WHERE 语句索引使用的注意事项？

where 子句用到的所有字段，必须建立索引。

索引何时失效？

查询条件中带 or，除非所有的查询条件都建立了索引；
like 查询以 % 开头时，索引失效；
如果列类型是字符串，那么在查询条件中需要将数据用引号引用起来，否则索引失效；
如果索引列上有计算，或进行了强制类型转换，那么索引失效，退化为全表扫描；
违背最左匹配原则时，索引失效；
如果 MySQL 估计全表扫描将会比使用索引快，那么索引失效。