MySQL索引开销

索引

概念

索引是一种数据结构，用于快速查找数据库中的记录。它类似于书籍的目录，可以帮助数据库系统快速定位到存储在表格中的特定数据行，而不必逐行扫描整个表格。

MySQL索引通常基于B树或B+树等数据结构，这些树形结构按照索引列的值进行排序，并且支持快速的搜索、插入、更新和删除操作。通过在查询中使用索引，数据库可以大大减少数据访问的成本，提高查询效率，从而加速数据检索过程。

索引通常是在表格上的一个或多个列上创建的。例如，对于一个包含用户信息的表格，你可能会在用户ID列上创建一个索引，以便快速查找特定用户的信息。除了单列索引外，MySQL还支持复合索引，即在多个列上创建的索引，以支持多列的查询条件。

分类

应用层维度

普通索引：基于单个列的索引，用于加速根据单个列进行的查询操作。

复合索引： 复合索引是基于多个列的索引，用于加速根据多个列组合条件进行的查询操作。它可以在一个索引中包含多个列，提供更高效的查询。复合索引的列顺序很重要，因为只有在查询中使用了索引的左边前缀列，MySQL才会利用该索引。

唯一索引： 唯一索引要求索引列的值是唯一的，不允许重复值存在。

主键索引： 主键索引要求索引列的值是唯一的且不为空。如果表格有主键，则MySQL会自动为主键创建索引。

全文索引： 全文索引用于全文搜索，可以加速对文本内容的搜索和匹配。它通常用于包含大量文本数据的列，如文章内容或评论内容。与普通索引不同，全文索引可以支持自然语言查询，并且可以识别停用词和分词等语言处理技术。

存储结构维度

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

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

一直有知道，索引虽然能够大幅度提高性能，但是索引不能滥用，因为会对系统造成过大的负担，至于是为什么呢？

索引开销

首先需要了解整个索引生效（生成B+树并被定位数据的过程），详细可以看这篇文章，

MySQL的执行顺序如下

MySQL指令执行过程

主要有以下几点

额外的存储空间消耗

每个索引都需要额外的存储空间来存储索引数据结构。如果有太多的索引，会占用大量的磁盘空间，特别是对于大型表格来说。

写操作的开销增加

每次对表格进行插入、更新或删除操作时，MySQL都需要更新索引。如果有太多的索引，写操作的开销会显著增加，因为每次写操作都要更新多个索引。

查询优化器的复杂度增加

当有多个索引可供选择时，MySQL的查询优化器需要决定使用哪个索引来执行查询。如果有太多的索引，查询优化器可能需要更长的时间来评估所有可能的索引，导致查询执行时间延长。

内存消耗增加

MySQL会将索引缓存在内存中以提高查询性能。如果有太多的索引，需要更多的内存来存储这些索引，这可能会导致内存不足的问题，进而影响系统性能。

索引维护的成本增加

随着索引数量的增加，索引的维护成本也会增加。这包括索引的重建、优化和碎片整理等操作，这些操作可能会在数据库的维护期间占用大量的系统资源。