解密 MySQL：索引—优化数据库性能的关键（一）

[th3ee9ine]

摘要：索引是关系型数据库中的关键性工具，用于加速数据检索和查询操作。通过了解索引的基本原理和工作机制，我们能够更好地优化和管理数据库，确保应用程序的高效运行。

---

一、索引基础

在我们深入讨论 MySQL 索引的不同类型和优化策略之前，让我们首先了解一下索引的基础知识。

1 、什么是索引？

索引是数据库中的一种数据结构，它类似于书籍的目录。它帮助数据库管理系统快速查找和定位数据，而不必扫描整个表。

索引通过创建索引键（ index key ）与实际数据行的对应关系，使得查询操作更加高效。

索引通常基于表的一个或多个列。

2 、索引的作用

索引的主要作用是加速数据检索操作，特别是在大型数据库表中。 它们可以大大减少查询响应时间，提高数据库的性能。

没有索引的情况下，数据库引擎需要逐行扫描整个表以找到匹配条件的数据，而索引允许它快速定位所需的行。

3 、索引与查询性能的关系

索引与数据库查询性能密切相关。一个优化良好的索引可以使查询速度大幅提升，而一个不合理的索引设计可能导致性能下降。

查询性能的提高主要体现在以下方面：

• 更快的数据检索：索引允许数据库直接跳到匹配的数据行，而不必逐行搜索。
• 减少磁盘 I/O：索引减少了磁盘 I/O 操作的需求，因为只需要访问索引数据而不是整个表。
• 更少的锁冲突：索引使得查询过程中需要的锁定时间更短，减少了并发访问时的锁冲突。
• 更少的 CPU 开销：索引可以减少数据库引擎的 CPU 开销，因为它们提供了更快速的数据定位方式。

4 、索引的数据结构

通常使用一种被称为 B 树或 B+树的数据结构来实现索引。这种树结构允许数据库引擎快速地定位索引键的值，并找到对应的数据行。

B 树和 B+树之所以被广泛用于索引，是因为它们在插入、删除和查找操作方面都表现出色，并且可以有效地处理大量数据。它们之间的主要区别包括以下几个方面：

• 数据存储方式：
  • B 树：B 树的所有节点，包括叶子节点和内部节点，都存储键值对数据。这意味着在 B 树中，数据同时存在于叶子节点和内部节点。
  • B+树：B+树的所有数据仅存储在叶子节点中，内部节点仅包含键（或索引）。这种分离的数据存储方式使 B+树更适合范围查询和顺序访问，因为数据在叶子节点形成有序链表。
• 范围查询性能：
  • B 树：B 树虽然能够支持范围查询，但由于数据分布在所有节点中，范围查询性能相对较低，需要在内部节点和叶子节点之间跳跃。
  • B+树： 由于 B+树的数据仅存储在叶子节点中，范围查询性能非常高，可以通过叶子节点的有序链表轻松执行范围查询。
• 叶子节点结构：
  • B 树：B 树的叶子节点包含数据以及相应的键，这使得每个叶子节点可以独立存储一部分数据。
  • B+树：B+树的叶子节点形成一个有序链表，数据按照键的顺序排列，支持高效的顺序访问。
• 内部节点结构：
  • B 树：B 树的内部节点包含键以及指向子节点的指针，每个内部节点通常对应一个范围。
  • B+树：B+树的内部节点仅包含键，用于导航到叶子节点，内部节点的数量通常比 B 树多。
• 树的高度：
  • B 树：B 树的树高度相对较低，因为数据分散在所有节点中，可以更快地定位数据，但范围查询性能较差。
  • B+树：B+树的树高度通常更高，但由于叶子节点形成有序链表，范围查询性能更好，适用于需要大量范围查询的情况。

5 、MyISAM 引擎与 InnoDB 引擎的索引结构的差别

MyISAM 引擎

MyISAM 引擎是使用 B+Tree 作为索引结构，叶子节点的 data 域存放的是数据记录的地址。

MyISAM 索引文件和数据文件是分离的（非聚簇索引），MyISAM 的索引文件仅仅保存数据记录的地址。MyISAM 中首先搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

物理文件结构为：

• .frm 文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等。
• .myd （ mysql data ）文件：myisam 存储引擎专用，用于存储 myisam 表的数据。
• .myi （ mysql index ）文件：myisam 存储引擎专用，用于存储 myisam 表的索引相关信息。

InnoDB 引擎

InnoDB 引擎也是使用 B+Tree 作为索引结构，但是 InnoDB 的数据文件本身就是索引文件，叶子节点 data 域保存了完整的数据记录。

索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主键索引（聚簇索引）。InnoDB 的辅助索引的 data 域存储相应记录主键的值而不是地址。这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引（回表）。 所以我们在使用索引的时候，应注意以下两点：

• 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
• 在 InnoDB 中也不建议使用非单调的字段作为主键，因为 InnoDB 数据文件本身是一颗 B+Tree ，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，建议使用自增字段作为主键。

物理文件结构为：

• .frm 与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等。
• .ibd 文件和.ibdata 文件：这两种文件都是存放 innodb 数据的文件，之所以用两种文件来存放 innodb 的数据，是因为 innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
  • 独享表空间存储方式使用.ibd 文件，并且每个表一个 ibd 文件；
  • 共享表空间存储方式使用.ibdata 文件，所有表共同使用一个 ibdata 文件；

二、MySQL 索引类型

MySQL 索引可以按照多种不同的分类方式进行分组。以下是根据不同类别对 MySQL 索引进行的一些常见分类：

按数据结构分类：

• B 树索引（ B-tree Index ）：这是最常见的索引类型，用于支持等值查询和范围查询。它包括标准的 B 树和 B+树索引。
• 哈希索引（ Hash Index ）：用于快速等值查找，但不支持范围查询。
• 全文索引（ Full-Text Index ）：用于文本字段的全文搜索和匹配。
• 空间索引（ Spatial Index ）：用于地理空间数据的查询，数据结构是 R 树（这里不再过多介绍）。

按物理存储分类：

• 聚簇索引（ Clustered Index ）：用于定义数据表的物理存储顺序，每个数据表只能有一个聚簇索引，通常是主键索引，也可以使用唯一非空索引作为聚簇索引。通常在 InnoDB 等存储引擎中使用。
• 非聚簇索引（ Non-Clustered Index ）：与数据表的物理存储顺序无关的索引，数据行按照其在数据文件中的实际物理存储顺序排列，通常在 MyISAM 等存储引擎中使用。

按字段特性分类：

• 唯一索引（ Unique Index ）：确保索引列的值是唯一的，不允许重复值。
• 主键索引（ Primary Key Index ）：一种特殊的唯一索引，用于唯一标识数据表的每一行。
• 外键索引（ Foreign Key Index ）：用于维护表之间的关系，确保外键引用的值在关联表中存在。
• 复合索引（ Composite Index ）：在多个列上创建的索引，用于支持多列的等值查询和范围查询。

按字段个数分类：

• 单列索引（ Single-Column Index ）：在单个列上创建的索引。
• 多列索引（ Multi-Column Index ）：在多个列上创建的复合索引。

三、索引的创建与使用

正确创建和使用索引可以显著提高查询性能，但错误的使用可能导致性能下降或不必要的存储开销。在本节中，我们将讨论如何创建和使用索引。

注：下面的内容如果没有特别说明，都是默认使用 Innodb 存储引擎。

1 、创建索引

创建索引的语法如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

    index_option: {
    KEY_BLOCK_SIZE [=] value
    | index_type
    | WITH PARSER parser_name
    | COMMENT 'string'
    | {VISIBLE | INVISIBLE}
    | ENGINE_ATTRIBUTE [=] 'string'
    | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
    }

    index_type:
    USING {BTREE | HASH}

    algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

    lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

主键索引(PRIMARY KEY)

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)

唯一索引(UNIQUE)

ALTER TABLE `table_name` ADD UNIQUE (`column`)

普通索引(INDEX)

单列索引

ALTER TABLE `table_name` ADD INDEX index_name(`column`)

复合索引

ALTER TABLE `table_name` ADD INDEX index_name(`column1`, `column2`, `column3`)

前缀索引

# 语法
ALTER TABLE `table_name` ADD INDEX index_name(`column1`[length],  `column2`[length], ...)
# 例子
ALTER TABLE `user_info` ADD INDEX idx_id_name(`id`(10),  `name`(3))

全文索引(FULLTEXT)

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

空间索引(SPATIAL INDEX)

# 语法
CREATE SPATIAL INDEX `index_name` ON `table_name`(`column`);
# 例子
CREATE SPATIAL INDEX spatial_index ON city(location);

2 、修改、删除索引

MySQL 中没有真正意义上的修改索引，只有先删除后再创建新的索引才可以达到修改的目的。

具体原因如下：

• MySQL 的索引结构通常基于 B-tree 或 B+tree 等树结构，这些结构的设计要求在索引创建时就确定了索引的键值顺序和树的结构。一旦创建了索引，这些属性通常是不可变的。修改索引可能需要重新构建整个树结构。
• 重建索引可能需要大量的 I/O 和计算资源，这可能会影响数据库的正常运行。此外，索引的修改可能会导致数据完整性问题，因为在修改索引的同时，必须确保没有并发查询或事务正在使用该索引。
• 修改索引可能会影响已存在的查询计划。如果查询依赖于特定的索引，那么修改该索引可能导致查询性能下降。

# 语法：
DROP INDEX 索引名称 ON 表名;
# 例子：
DROP INDEX idx_id_name ON `user_info`;

# 修改：
DROP INDEX idx_id_name ON `user_info`;
ALTER TABLE `user_info` ADD INDEX idx_name ( `name` );

3 、查询索引

# 语法：SHOW INDEX FROM 表名;
# 例如：
SHOW INDEX FROM `user_info`;

4 、创建与使用索引的注意事项

• 选择适当的索引列：确保选择用于创建索引的列基于查询需求和查询模式。通常，索引应该放在经常用于等值查询、范围查询或排序操作的列上。
• 避免创建过多索引：不要为每个列都创建索引，因为过多的索引可能会导致性能下降和额外的存储开销，只创建必要的索引。
• 考虑复合索引：复合索引是包含多个列的索引。它可以在某些情况下提高性能，但要小心不要过度使用，因为复合索引可能会增加维护负担。
• 定期维护索引：定期重建或重新组织索引，以减少碎片并保持性能。索引碎片可能会导致查询性能下降。
• 避免使用通配符开头的模糊查询：模糊查询（例如 LIKE 操作符）的模式以通配符开头（例如%search ）可能无法有效利用索引。
• 谨慎修改或删除索引：修改或删除索引可能会影响数据库性能。在进行这些操作之前，进行测试并谨慎规划。删除索引时，确保不会影响正在运行的生产环境。

四、结尾

通过上面的内容，我们对 MySQL 索引的基础知识有了一个大致的了解，知道了如何创建与使用索引了，但是这些内容还不足以让我们用好索引，下篇文章，我们将继续深入了解索引的其他知识点，内容包括：最左匹配原则、覆盖索引、前缀索引、索引下推、回表、MRR(Multi-Range Read)、ICP(Index Condition Pushdown)等等。。。

参考资料

• 高性能 MySQL(第三版)
• MySQL 官方手册

原文链接：解密 MySQL：索引—优化数据库性能的关键（一）

微信公众号：啊杰在拱趴

求关注！！！！

[th3ee9ine]

新人公众号，求关注！！！
微信公众号：啊杰在拱趴

[pursuit]

支持。已关注~

[th3ee9ine]

@pursuit 感谢哈哈哈