MySQL索引和查询优化

最新推荐文章于 2024-05-20 14:31:07 发布

小悟空GK

最新推荐文章于 2024-05-20 14:31:07 发布

阅读量1.8k

点赞数 44

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_48114126/article/details/135490840

版权

1 索引

1.1 索引概述

索引(Index)是帮助MySQL高效获取数据的数据结构

**索引的本质：**索引是数据结构。可以简单理解为“排好序的快速查找的数据结构”，满足特定的查找算法，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节，有些存储引擎支持更多的索引和更大的索引长度。

1.2 索引语法

1.2.1 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name( index_col_name,...... );

UNIQUE:指定索引中的值必须是唯一的
FULLTEXT:FULLTEXT 是一个用于创建全文索引的选项。全文索引是一种用于在文本数据中进行高效搜索的索引类型。

在 MySQL 数据库中，可以使用 FULLTEXT 选项来创建全文索引。全文索引适用于包含大量文本内容的列，例如文章的正文或产品的描述。

要在表的列上创建全文索引，可以使用以下语法：
```
CREATE FULLTEXT INDEX index_name ON table_name (column_name);
```
例如，如果我们有一个名为 articles 的表，其中包含一个名为 content 的列，我们可以使用以下 SQL 语句创建一个名为 idx_content_fulltext 的全文索引：
```
CREATE FULLTEXT INDEX idx_content_fulltext ON articles (content);
```
这将在 articles 表的 content 列上创建一个全文索引，使我们能够以更高效的方式搜索和匹配在 content 列中出现的关键词或短语。

要执行全文搜索查询，可以使用 MATCH AGAINST 关键字。例如：
```
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword');
```
这将返回在 content 列中包含指定关键词的所有匹配行。

1.2.2 查看索引

SHOW INDEX FROM table_name;

1.2.3 删除索引

DROP INDEX index_name ON table_name;

1.3 优缺点

优点

提高数据检索的效率，降低数据库的IO成本，这也是索引创建的最主要的原因。
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗

缺点

创建和维护索引耗费时间，随数据增加，所耗费时间也会增加
索引占磁盘空间，除了数据表占空间之外，每一个索引还要占一定的物理空间。存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
降低更新表的速度

1.4 索引分类

存储形式

聚簇索引（Clustered Index）：将数据存储与索引放到了一块，索引结构的叶子节点保存了自身所在行整行的数据（必须有，只有一个）ps:聚集索引
聚簇索引选取规则
- 如果主键存在，主键就是聚簇索引
- 如果主键不存在，将使用第一个唯一（UNIQUE）索引作为聚簇索引
- 如果没有主键，或者没适合的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有用户记录都存储在叶子节点上）。也就是索引即数据，数据即索引。

用于决定表的物理存储顺序。与其他类型的索引不同，一个表只能拥有一个聚簇索引。

聚簇索引将数据行存储在物理上连续的位置，并按照索引的键值进行排序。这意味着具有相似索引键值的数据行在物理上也是相邻的，这样可以提高基于范围或顺序访问的查询性能。

在聚簇索引中，索引和数据行紧密耦合在一起，因此聚簇索引的叶子节点包含了整个数据行的完整信息，而不仅仅是索引字段的值。这使得聚簇索引在满足查询条件时可以更快地返回数据，因为它避免了通过索引再去查找实际的数据行。

需要注意的是，由于聚簇索引决定了表的物理存储顺序，当对聚簇索引的键值进行修改、插入或删除操作时，可能会导致数据的物理重组和页分裂，影响性能。此外，由于每个表只能有一个聚簇索引，因此在选择使用聚簇索引时需要考虑索引字段的选择以及对表的查询模式和性能的影响。

非聚簇索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存在多个）ps:非聚集索引，非聚簇索引，辅助索引，二级索引

数据约束

主键索引
唯一索引
非唯一索引

索引列的位置

单列索引：一个索引只关联一个字段
组合索引：一个索引关联多个字段

innoDB可以创建的索引

1.5 索引的数据结构

1.5.1 二叉查找树

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

1.5.2 BTree

1.5.3 B+Tree

B树的缺点：

占用空间较大：由于B树的节点存储了键值和指针，因此每个节点会占用较大的空间。对于磁盘存储来说，节点的大小限制了每个节点可以容纳的索引键数量，导致树的高度较高，增加了IO操作次数，影响查询性能。
节点分裂代价高昂：当插入新键时，如果导致节点溢出，B树需要进行节点的拆分操作，这可能会导致频繁的节点分裂，浪费资源以及引发数据迁移，降低了插入操作的效率。

B+树相对于B树的改进主要体现在以下方面，因此其缺点与B树相比相对较少：

不支持直接查询：B+树的所有叶子节点都按顺序连接在一起，形成一个链表结构，这使得范围查询非常高效。但是，对于单个键的查找，必须通过从根节点开始的迭代过程来找到目标键的位置，相对于B树的直接查找要慢一些。
节点分裂代价仍然存在：虽然B+树相对于B树减少了节点的拆分，但仍然需要进行节点的拆分操作。对于高负载的写入工作负载，节点分裂可能会成为性能瓶颈。
占用空间相对较多：尽管B+树的内部节点不存储具体的数据，只存储键值和子节点指针，但仍然需要占用一定空间。

B树的数据存储在每个节点上，B+树中的数据存储在叶子节点上，并且通过链表的方式把叶子节点中的数据进行连接，子路数量=关键字数量

1.6 索引下推回表覆盖索引

1.6.1 索引下推

若是根据name 和 age搜索用户，若是没有索引下推，就是先从二级索引中根据name找到符合的主键id，分别是1和4，然后在聚簇索引中使用id=1和id=4查询到所匹配的数据行，进行了两次回表，最后在mysql的Server层再对数据使用age=18进行过滤。从mysql5.6有了索引下推，就可以在存储引擎中根据name和age进行过滤出id，再使用聚簇索引获取数据，这样只有一次回表。
总结：索引下推通过减少回表的次数，来提高数据库的查询效率

1.6.2 回表

回表：select * 时先走二级索引找到对应的主键值，再根据主键值走聚簇索引拿到所在行的整行数据

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

聚簇索引select *过程：（不需要回表）

非聚簇索引select * 过程:（需要回表）

1.6.3 覆盖索引

索引覆盖（Index Covering）是一种查询优化技术，通过构建合适的索引（创建合适的复合索引（Compound Indexes）将索引中的所有必要数据都包含在索引本身中）来覆盖查询中所需的数据，以减少数据库系统的IO开销和提高查询性能。然而，创建过多的索引也会增加维护成本和空间占用，因此在设计索引时需要权衡考虑。