MySQL索引

**K

已于 2024-07-31 19:57:41 修改

阅读量690

点赞数 16

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-07-31 17:21:23 首次发布

本文链接：https://blog.csdn.net/m0_59680769/article/details/140827181

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引的作用

索引是按照指定列和规则建立的一种数据排列组合的结构，当遇到数据表数据量比较大，检索消息成为系统瓶颈的时候，首先会想到为数据表创建索引。但索引对于一个业务而言，并非是必须的，尤其是对于数据量比较小的数据表或者修改频率比较大的数据表而言，索引的创建不仅不会加快查询速度，反而会降低查询速度，因为需要额外的索引维护。

索引的作用根据索引的类型划分主要包括：提高数据检索的效率、维护数据的唯一性。另外使用order by和group by的时候，恰当地使用索引，还可以减少排序和分组的时间，从而间接的加快数据检索的效率。

使用索引的操作

我们最熟悉使用索引的地方当属进行数据查询的时候，其实底层数据更新场景也进行了数据的查询，从而用到了索引。因为数据新增、修改、删除都会首先定位到满足条件的数据，这时候就会用索引检索数据。

另外用到order by或者group by的时候，如果使用的索引字段包含sql语句中的排序分组字段，就可以直接遍历索引结构，直接拼接结果即可，大大减少排序分组的开销。

使用索引的时机

当我们遇到系统反应时间长，找到慢sql后，首先会排查是否存在索引、存在的索引是否合理、是否适合当前业务场景。

在考虑使用索引的时候，需要结合具体的业务场景。因为索引的使用也是需要占用物理空间的，虽然提高检索效率采取的策略是用空间换取时间，但需要尽量追求二者的平衡。但是对于数据表的增删改操作都会触发索引结构的更新，所以索引并不是越多越好。

执行SQL语句的过程

从整体看，MySQL分为server层和存储引擎层。

连接器：想要执行一条sql语句，首先需要连接到指定数据库上，这时候涉及到的就是连接器。连接器首先会建立连接->验证权限->对建立的链接进行管理，如果账号错误，也会进行提示。
查询缓存：客户端和数据库建立连接后，就可以执行查询语句了，执行查询语句首先就会看查询缓存中是否存在之前的查询结果。之前的查询结果直接在内容中以key-value的格式存储，其中key是查询语句，value是查询结果，如果存在直接返回客户端value中存储的查询结果。如果在查询缓存中没有查询到历史执行结果，就会继续执行后面的步骤。

但在实际使用中，由于对一个数据表的任何修改，针对这个表的所有查询缓存都会失效导致失效频率频繁，所以查询缓存通常是是关闭状态。而Mysql从8.0版本开始已经将查询缓存功能彻底删除。

解析器：解析器主要负责对编写的查询语句做正确性判断，首先会根据输入的语句做识别，确认属于什么类型的语句，语句是否符合MySQL规范、字段、表明是否存在等作分析，如果有任何的问题，都会直接返回客户端错误信息，比如：大家最常见的unknown column错误。
优化器：通过解析器，MySQL已经知道你想要做什么了，通过优化器后，会对sql语句做优化，比如：是否使用索引、多个索引同时存在的情况下，具体使用哪个索引、多个数据表关联查询的时候，表的先后关系等。
执行器：到这里时，MySQL已经知道你要做什么(解析器)，以及怎么做(优化器),执行器开始执行具体语句。通过存储亲情提供的API接口，依次获取满足条件的数据行记录，全部数据获取完成后，返回给客户端。
存储引擎：负责数据的实际存储功能，存储引擎是插件式的嵌入到MySQL当中，从Mysql 5.5版本后，InnoDB作为默认的存储引擎。

索引数据结构B+树🌲

在数据库查询语句中，最常见的当属等值查询和范围查询，为什么有的语句使用索引查询效率就会比较高，有的明明就存在相关索引，但不能使用索引呢？接下来就从索引底层数据存储结构说起。

查询类型	描述	示例	适用场景	优缺点
等值查询	检索出恰好等于特定值的记录	`SELECT * FROM table WHERE column = value;`	需要确切匹配某个值的场景	优点：查询速度通常较快，索引利用效果好。缺点：只适用于查找特定值，当需要查找范围时不适合。
范围查询	检索出在某个范围内的记录，例如大于或小于某个值	`SELECT * FROM table WHERE column BETWEEN value1 AND value2;` \|\| `SELECT * FROM table WHERE column > value1;`	需要查找某个范围内数据的场景，如时间段内的数据查找	优点：适用于需要查找大范围内数据的场景。缺点：对于大数据量表，查询性能可能较差，尤其是范围较大时。

为了同时支持等值查询和范围查询，同时减少每次查询的访问次数，MySQL数据库采用了B+树作为索引的存储结构。B+树分为叶子节点和非叶子节点，对于非叶子节点，并不存储数据本身，而是存储索引关系，而后在叶子节点才真正存储数据，而且叶子节点使用双向链表相连，链表上的数据按照从小到大的顺序排列，从而支持基于区间的数据查询。

使用B+树还有另一个原因，减少磁盘的访问次数，从而提高数据查询的效率。为了节约内存，索引通常是存储在硬盘上的，B+树每个节点代表一次磁盘IO，树的高度就是实际磁盘IO的次数，所以B+树采用了增加叉数，降低高度的方式，从而减少了磁盘IO的次数。

B+树的特性

所有叶子节点都在同一层：这是B+树的一个重要特性，确保了所有数据项的检索都具有相同的I/O延迟，提高了搜索效率。每个叶子节点都包含指向相邻叶子节点的指针，形成一个链表，由于叶子节点之间的链接，B+树非常适合进行范围查询和排序扫描。可以沿着叶子节点的链表顺序访问数据，而无需进行多次随机访问。
非叶子节点存储键值：非叶子节点仅存储键值和指向子节点的指针，不包含数据记录。这些键值用于指导搜索路径，帮助快速定位到正确的叶子节点。并且，由于非叶子节点只存放键值，当数据量比较大时，相对于B树，B+树的层高更少，查找效率也就更高。
叶子节点存储数据记录：与B树不同，B+树的叶子节点存储实际的数据记录或指向数据记录的指针。这意味着每次搜索都会到达叶子节点，才能找到所需数据。
自平衡：B+树在插入、删除和更新操作后会自动重新平衡，确保树的高度保持相对稳定，从而保持良好的搜索性能。每个节点最多可以有M个子节点，最少可以有ceil(M/2)个子节点（除了根节点），这里的M是树的阶数。

执行性能分析

对sql语句进行性能分析最常用的方式是通过explain查看分析结果，语法格式为：explain <sql语句>。

输出可能会包含以下信息：

id: 查询的序列号，表示查询中所有选择子查询的顺序。
select_type: 查询的类型，例如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
table: 查询涉及的表。
partitions: 该查询访问的分区（如果有）。
type: 连接类型，表示查询的访问方法。值包括 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。
possible_keys: 查询可能使用的索引。
key: 查询实际使用的索引。
key_len: 使用的索引键长度。
ref: 索引的比较参考对象。
rows: 预估的需要读取的行数。
filtered: 表示返回结果集的行在满足条件后的百分比。
Extra: 额外的信息，例如是否需要使用临时表或文件排序。

索引失效的场景

索引虽然具有提高查询效率的能力，但是过分使用索引，会给系统性能带来副作用，不适合创建索引的场景：

数据表数据量较少，使用全表扫描效率可以接受的场景
更新操作频繁的数据表
区分度不高的字段，比如有效和无效
对于很少使用到的字段
现有索引前缀已经包括要创建索引涉及字段
字段类型为text、image等占用存储空间太大的类型
经常需要使用聚合函数计算的字段
经常需要使用模糊查询的字段
经常需要通过负向组合的查询语句，如：not、not in、not like、!=等

最左匹配原则

复合索引是指在多个列上创建的索引。例如，你可以在 table_name 表的 column1 和 column2 上创建一个索引：

CREATE INDEX idx_name ON table_name (column1, column2);

最左前缀匹配原则指的是，MySQL在使用复合索引时，会优先匹配最左边列的前缀部分，即索引的最左前几列。

完整匹配最左前列：必须从索引的最左列开始匹配，即 column1。
部分匹配：可以匹配最左几列的完整值，而不必匹配全部列。例如，可以只匹配 column1 或者同时匹配 column1 和 column2。
不中断匹配：一旦在匹配过程中跳过某列，后面的列就不能再被匹配上索引。例如，如果复合索引是 (column1, column2, column3)，那么查询条件中缺少 column2 的话，就不能有效利用 column3。