MySQL索引

初衷不改~

已于 2024-08-01 22:24:33 修改

阅读量612

点赞数 24

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

于 2024-07-13 11:01:17 首次发布

本文链接：https://blog.csdn.net/qq_41892775/article/details/140395100

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引

什么是索引

在 MySQL 中，索引（Index）是一种特殊的数据结构，用于帮助数据库系统高效地检索数据。索引可以加快对数据库表中数据的访问速度。

在数据库中，数据通常存储在表中，表由行和列组成。当我们需要从表中检索特定的数据行时，如果没有索引，数据库系统只能顺序扫描整个表，直到找到满足条件的行为止。这种顺序扫描称为全表扫描，尤其是在大型表中，会消耗大量的时间和系统资源。

索引的作用就是为了避免这种全表扫描，它通过创建一种额外的数据结构来加速数据的检索。这种数据结构类似于一个有序数组或者树结构，其中存储了数据表中某一列（或者多列）的值与其对应行的映射关系。这样，当我们根据索引列的值进行查询时，数据库系统可以直接定位到索引中对应的位置，从而快速找到满足条件的数据行，而不需要逐行扫描整个表。

由于索引是一种额外的数据结构，创建索引会占用额外的存储空间，就就好像一本书的目录本身也要占用书的几页空间。此外，创建索引通常会带来一些额外的存储开销和更新成本，因为每次插入、更新或删除数据时，MySQL 需要更新索引的结构，就好像我们写文档时一样，当标题改变了，目录自然也要更新。因此，需要权衡索引的使用场景，以确保在查询效率和数据更新效率之间取得适当的平衡。

索引的优点	索引的缺点
提高数据检索的效率，降低数据库的IO成本	索引是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

索引数据结构

以下是Mysql中的四种索引数据结构

索引数据结构	描述
B+Tree 索引	B+Tree 是一种平衡树数据结构，索引最常见的索引类型，大部分引擎都支持 B+Tree 树索引
Hash 索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	全文索引使用特定的数据结构，如倒排索引（Inverted Index），用于支持全文搜索功能

以下是索引数据结构在不同引擎中的支持情况，以下列举了大部分情况

索引数据结构	InnoDB	MyISAM	MEMORY
B+tree	支持	支持	支持
Hash	不支持	不支持	支持
R-tree	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

具体的支持情况可以参考以下地址：

MySQL 8.0 版本的存储引擎文档
 MySQL 5.7 版本的存储引擎文档

索引分类

Mysql中，索引通常可以分为以下几类：

索引分类	特点描述
主键索引	唯一标识表中每一行数据的索引，通常是表的主键。
唯一索引	确保索引列的所有值都是唯一的，允许空值（NULL），但空值不算重复。
普通索引	最基本的索引类型，没有唯一性限制。适用于普通的等值查询、范围查询和排序操作。
全文索引	用于全文搜索的特殊索引类型，适合于对文本内容进行搜索。

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

索引语法

创建索引

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

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

索引失效的情况

当在WHERE条件中使用函数操作索引列时，索引可能会失效。例如：
```
SELECT * FROM table WHERE YEAR(date_column) = 2024;
```
如果查询条件中的列顺序与索引的列顺序不匹配，索引可能会失效。例如，如果索引是(column1, column2)，而查询条件是WHERE column2 = ‘value’ AND column1 = ‘value’，则索引无法有效使用。
当使用LIKE语句并且通配符（%）在查询字符串的开头时，索引可能会失效。例如：
```
SELECT * FROM table WHERE column LIKE '%value';
```
如果查询条件中的数据类型与索引列的数据类型不匹配，索引可能会失效。例如，索引列是整数类型，但查询条件使用了字符串比较。
当表中数据量较少时，MySQL可能会选择全表扫描而不是使用索引。这是因为全表扫描可能比索引查找更快，尤其是在数据分布不均匀的情况下。