「浅谈MySQL索引」

来自 gpt 的摘要： 本文详细介绍了 MySQL 中索引的概念、分类、使用场景及优缺点。索引是一种用于加速数据查询的数据结构，MySQL 中常用的索引类型包括 B+树、哈希索引、聚簇索引、非聚簇索引等。索引可以分为主键索引、普通索引、唯一索引等，主要用于提高查询速度，尤其适用于频繁查询、排序和连接操作的字段。虽然索引能够显著提升查询性能，但在数据修改时会增加开销，并占用物理存储空间。此外，索引在某些情况下会失效，比如使用不当的查询条件或不遵循最左前缀原则。因此，合理使用索引可以优化数据库的性能，但过度依赖索引可能带来负面影响。

讲一下 MySQL 的索引？

• 索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

• 索引往往是存储在磁盘上的文件中的，占用空间大。

• 索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

索引的分类？

按照数据结构维度划分 ：

• BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。

• 哈希索引：类似键值对的形式，一次即可定位。

• RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分 ：

• 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。

• 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分 ：

• 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。

• 普通索引：仅加速查询。

• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。

• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。

• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

MySQL 8.x 中实现的索引新特性：

• 隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。

• 降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。

• 函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

索引的使用场景是什么？

• 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0，1，true，false 这样语义较为清晰的短值或短字符作为替代。

• 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。

• 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。

• 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

• 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

索引的优缺点？

优点 ：

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

• 索引需要使用物理文件存储，也会耗费一定空间。

总结：

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

MySQL 索引失效的情况？

• WHERE 子句中使用 !=，<>， or，表达式操作，函数操作， = 左边进行运算。

• 联合索引不遵循最左前缀原则。

• MySQL 系统评估索引比全表扫描更慢。

• 字段为字符串且查询时不带引号。

• LIKE 查询，% 在前。

• 字段为 NULL。

• IN 的取值范围较大。