mysql 索引知识汇总

最新推荐文章于 2024-11-01 14:31:36 发布

黑猫๑

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量827

点赞数 14

文章标签： mysql 数据库 python

本文链接：https://blog.csdn.net/wmjf1/article/details/135915484

版权

一、mysql 数据结构

1.2 什么是B(-)树？

在这里插入图片描述

1.2.1 特点

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。
B-Tree在每一个节点(叶子结点和非叶子结点)存储了索引和数据。
每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。

1.2.2 缺点

进行搜索的时候需要把索引和数据都加载到内存中。但那些对我们没用的数据，会增多磁盘 I/O 操作次数，也占用内存资源。
范围查询需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

1.3 什么是B+树？

在这里插入图片描述

1.3.1 特点

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。
所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；
对于范围查询，B+树只需遍历叶子节点链表即可，而B树则需要重复中序遍历。
B+树的随机查找、插入和删除过程与B树相似，但在插入和删除时的平均时间复杂度较低。
B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

1.3.2 缺点

由于键会重复出现，因此会占用更多的空间。

1.4 为什么Mysql 使用了B+树?

在相同数据量的情况下，B+树比B树更矮，同阶位存放的索引更多。磁盘I/O次数更少。因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
B+树叶子结点使用了双向链表连接，更适合范围查询。

二、索引

2.1 什么是索引？

2.1.1 定义

官方定义：在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
简单点：用户数据的目录

2.1.2 不手动创建索引如何？

因为B+Tree必须依靠索引才能建立，所以每张表都会有且只有一个聚簇索引。
在创建表时：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

2.1.3 什么是回表查询和覆盖查询？

回表查询：当使用二级索引查询数据时，找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到想要的数据。例如：select * from product where product_no = '0002';
覆盖查询：在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。例如：select id from product where product_no = '0002';

2.2 索引的分类

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

2.2.1 按数据结构分类

在这里插入图片描述
InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

2.2.2 按物理存储分类

主键索引：叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引：叶子节点存放的是主键值，而不是实际数据。

2.2.3 按字段特性分类

主键索引：主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引：唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
普通索引：普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
前缀索引：前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

.2.2.4 按字段个数分类

单列索引:建立在单列上的索引称为单列索引，比如主键索引；
联合索引:建立在多列上的索引称为联合索引；

2.2.5 联合索引：

叶子结点是双向列表
在这里插入图片描述

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

联合索引范围查询：
联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

索引下推：
在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

2.3 何时创建索引？

2.3.1 索引的优缺点

优点：

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
可以加快数据的检索速度
可以加速表与表之间的连接
在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占用物理空间，数据量越大，占用空间越大
会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

2.3.2 什么时候适用索引？

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中排序的字段(因为索引是有顺序的)
查询中统计或者分组的字段；
与其他表关联的字段(join in)

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

不会被WHERE ，GROUP BY，ORDER BY 用到的字段。
表数据太少的时候，不需要创建索引
字段中数据重复率高的不要创建索引。
经常更新的字段不用创建索引。索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

2.4 索引优化

前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；
。。。。。。

2.6 explain 的type有哪些？

在这里插入图片描述

对于执行计划，参数有：

possible_keys 字段表示可能用到的索引；
key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
key_len 表示索引的长度；
rows 表示扫描的数据行数。
type 表示数据扫描类型，我们需要重点看这个。
type 字段就是描述了找到所需数据时使用的扫描方式是什么，
执行效率从低到高的顺序为：

All（全表扫描）；
index（全索引扫描）；
range（索引范围扫描）；从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式
ref（非唯一索引扫描）；
eq_ref（唯一索引扫描）；
const（结果只有一条的主键或唯一索引扫描）。