MySQL 索引优化实战指南：原理、策略与案例分析

一、索引的基本概念

（一）索引的定义

索引是一种数据结构，用于快速定位和访问数据库表中的记录。它类似于书籍的目录，通过在数据表中建立一个额外的结构来存储数据的引用信息，从而加速数据的检索过程。在 MySQL 中，索引是存储在磁盘上的，它与数据表本身是分开存储的，但又与数据表紧密关联。

（二）索引的作用

提高查询速度
- 索引的主要作用是加快查询速度。当执行查询操作时，如果没有索引，数据库系统需要扫描整个表中的所有记录，这种操作称为全表扫描（Full Table Scan）。全表扫描的效率非常低，尤其是当表中数据量较大时，查询时间会显著增加。而通过索引，数据库系统可以快速定位到满足查询条件的记录，从而大大减少需要扫描的数据量，提高查询速度。
- 例如，假设有一个学生信息表，包含 100 万条记录，每条记录包括学号、姓名、年龄和成绩等字段。如果要查询学号为“20210001”的学生信息，没有索引时，数据库需要逐条扫描这 100 万条记录来查找符合条件的记录。但如果在学号字段上建立了索引，数据库就可以直接通过索引快速定位到该学生的记录，而无需扫描整个表，查询速度会显著提高。
优化排序和分组操作
- 索引还可以优化排序（ORDER BY）和分组（GROUP BY）操作。当查询语句中包含 ORDER BY 或 GROUP BY 子句时，如果没有索引，数据库需要对查询结果进行排序或分组，这通常需要额外的计算和存储资源。而如果在相关的字段上建立了索引，数据库可以利用索引的有序性来直接获取排序或分组的结果，从而避免了额外的排序或分组操作，提高了查询效率。
- 例如，对于上述学生信息表，如果经常需要按照成绩对学生进行排序，那么在成绩字段上建立索引后，查询语句“SELECT * FROM student ORDER BY score”就可以利用索引快速获取排序后的结果，而无需对整个查询结果进行排序。
实现数据的唯一性约束
- 索引还可以用于实现数据的唯一性约束。在 MySQL 中，可以通过创建唯一索引（UNIQUE INDEX）来确保某个字段或字段组合中的值是唯一的。唯一索引不仅可以加快查询速度，还可以防止数据重复，保证数据的完整性。
- 例如，在用户信息表中，用户名字段通常需要保证唯一性。通过在用户名字段上创建唯一索引，可以确保不会插入重复的用户名记录，同时也可以加快按照用户名查询用户信息的速度。

（三）索引的存储结构

MySQL 支持多种存储引擎，不同的存储引擎使用不同的索引存储结构。最常见的存储引擎是 InnoDB 和 MyISAM，它们分别使用 B+ 树和哈希表作为索引的存储结构。

B+ 树索引
- B+ 树是一种多路平衡查找树，它具有以下特点：
  - 每个节点包含多个关键字和指针，关键字是有序的，指针指向子节点或数据记录。
  - 所有关键字都存储在叶子节点中，叶子节点之间通过指针相互连接，形成一个有序链表，便于范围查询。
  - 非叶子节点只存储关键字和指向子节点的指针，不存储数据记录，这样可以减少非叶子节点的大小，提高索引的存储效率。
- InnoDB 存储引擎使用 B+ 树作为其主索引和辅助索引的存储结构。B+ 树索引适用于范围查询、排序查询和等值查询等多种查询场景。
- 例如，对于一个基于 B+ 树索引的表，如果要查询某个字段的值大于某个特定值的所有记录，数据库可以通过 B+ 树索引快速定位到第一个符合条件的记录，然后沿着叶子节点的链表顺序扫描，获取所有满足条件的记录，而无需扫描整个表。
哈希索引
- 哈希索引是基于哈希表实现的索引结构。哈希表是一种通过哈希函数将关键字映射到存储位置的数据结构，具有快速查找的特点。
- MyISAM 存储引擎支持哈希索引。哈希索引适用于等值查询，但不支持范围查询和排序查询。因为哈希索引是通过哈希函数将关键字映射到存储位置的，所以无法保证关键字的有序性，无法利用索引进行范围查询或排序操作。
- 例如，对于一个基于哈希索引的表，如果要查询某个字段的值等于某个特定值的记录，数据库可以通过哈希函数快速定位到该记录的存储位置，查询速度非常快。但如果要查询某个字段的值大于某个特定值的所有记录，哈希索引就无法发挥作用，需要扫描整个表。

二、MySQL 索引的类型

（一）主键索引

主键索引是一种特殊的唯一索引，它用于唯一标识表中的每一行记录。每个表只能有一个主键索引，主键字段的值必须是唯一的，且不允许为空（NULL）。在 MySQL 中，可以通过定义字段为 PRIMARY KEY 来创建主键索引。

主键索引的存储结构通常是 B+ 树。对于 InnoDB 存储引擎，主键索引的叶子节点存储的是数据记录本身，而不是指向数据记录的指针。这种存储方式称为聚簇索引（Clustered Index）。聚簇索引的特点是数据记录按照主键的顺序存储在磁盘上，因此主键索引的查询效率非常高，尤其是在进行范围查询和排序查询时。

例如，对于一个用户信息表，可以将用户 ID 定义为主键，创建主键索引。这样，通过用户 ID 查询用户信息时，数据库可以直接通过主键索引快速定位到对应的记录，而无需扫描整个表。

（二）唯一索引

唯一索引用于保证某个字段或字段组合中的值是唯一的。与主键索引不同，唯一索引允许字段值为 NULL，且一个表可以有多个唯一索引。在 MySQL 中，可以通过定义字段为 UNIQUE 来创建唯一索引。

唯一索引的存储结构也是 B+ 树。对于 InnoDB 存储引擎，唯一索引是辅助索引（Secondary Index），其叶子节点存储的是主键值的引用，而不是数据记录本身。当通过唯一索引查询数据时，数据库首先通过唯一索引定位到主键值，然后再通过主键索引获取数据记录。

例如，在一个学生信息表中，可以将学号字段创建为唯一索引，以确保学号的唯一性。同时，还可以将身份证号字段创建为另一个唯一索引，以进一步保证数据的完整性。

（三）普通索引

普通索引是最基本的索引类型，它没有任何唯一性约束。普通索引可以加快查询速度，但不会限制字段值的重复性。在 MySQL 中，可以通过定义字段为 INDEX 或 KEY 来创建普通索引。

普通索引的存储结构同样是 B+ 树。对于 InnoDB 存储引擎，普通索引也是辅助索引，其叶子节点存储的是主键值的引用。普通索引适用于等值查询、范围查询和排序查询等多种查询场景。

例如，在一个商品信息表中，可以将商品名称字段创建为普通索引，以便快速查询某个特定名称的商品信息。同时，还可以将商品价格字段创建为另一个普通索引，以支持按照价格范围查询商品的功能。

（四）全文索引

全文索引是一种特殊的索引类型，用于支持全文搜索操作。全文索引可以对文本数据进行分词处理，并建立倒排索引，从而实现高效的文本搜索功能。在 MySQL 中，可以通过定义字段为 FULLTEXT 来创建全文索引。

全文索引适用于对文本字段（如文章内容、评论等）进行搜索的场景。它支持复杂的文本搜索操作，如模糊匹配、词干匹配、多词匹配等。全文索引的存储结构与普通索引不同，它使用倒排索引结构来存储文本数据的索引信息。

例如，在一个新闻网站的数据库中，可以对新闻内容字段创建全文索引，以便用户可以通过关键词快速搜索到相关的新闻文章。

（五）组合索引

组合索引是指在多个字段上创建的索引。组合索引可以同时对多个字段进行索引，从而提高多字段查询的效率。在 MySQL 中，可以通过在多个字段上定义 INDEX 或 KEY 来创建组合索引。

组合索引的存储结构也是 B+ 树。对于组合索引，数据库会按照字段的顺序对索引进行排序和存储。在查询时，如果查询条件中包含了组合索引的前缀字段，数据库可以利用组合索引进行查询优化。

例如，在一个订单信息表中，可以将订单日期和订单金额字段创建为组合索引。这样，在查询某个特定日期范围内的订单信息时，数据库可以通过组合索引快速定位到符合条件的记录，而无需扫描整个表。

三、创建索引

（一）创建索引的语法

在 MySQL 中，可以通过以下几种方式创建索引：

在创建表时创建索引

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    INDEX index_name (column1, column2, ...)
);

例如：

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    score INT,
    INDEX idx_name_age (name, age)
);

在这个例子中，创建了一个名为 student 的表，并在 name 和 age 字段上创建了一个名为 idx_name_age 的组合索引。

在已存在的表上创建索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

或者

CREATE INDEX index_name ON table_name (column1, column2, ...);

例如：

ALTER TABLE student ADD INDEX idx_score (score);

或者

CREATE INDEX idx_score ON student (score);

这两种方式都可以在已存在的 student 表上为 score 字段创建一个名为 idx_score 的普通索引。

（二）创建索引的注意事项

选择合适的字段创建索引
- 创建索引时，需要选择合适的字段。一般来说，应该为那些经常用于查询条件、排序字段或分组字段的字段创建索引。例如，如果某个字段经常出现在 WHERE 子句、ORDER BY 子句或 GROUP BY 子句中，那么可以考虑为该字段创建索引。
- 同时，需要考虑字段的基数（Cardinality），即字段中不同值的数量。如果字段的基数较低（即字段中大部分值是重复的），那么创建索引的效果可能不明显，甚至可能会降低性能。例如，对于一个性别字段，只有“男”和“女”两个值，为该字段创建索引的意义不大。
合理选择索引类型
- 根据实际需求选择合适的索引类型。如果需要保证字段的唯一性，可以创建唯一索引；如果需要支持全文搜索，可以创建全文索引；如果需要对多个字段进行联合查询，可以创建组合索引。
- 对于范围查询、排序查询和等值查询等场景，B+ 树索引通常是较好的选择。而对于等值查询，哈希索引也可以考虑，但需要注意哈希索引不支持范围查询和排序查询。
控制索引的数量和大小
- 索引虽然可以提高查询速度，但也会带来一些负面影响。索引会占用额外的存储空间，并且在插入、更新和删除数据时，需要同步更新索引，这会增加系统的开销。因此，需要合理控制索引的数量和大小，避免创建过多或过大的索引。
- 对于组合索引，应该尽量减少索引的字段数量，只包含那些真正需要的字段。同时，索引的字段顺序也很重要，应该将最常用的查询条件字段放在前面。
考虑存储引擎的特性
- 不同的存储引擎对索引的支持和性能表现有所不同。例如，InnoDB 存储引擎支持事务、行级锁和外键等特性，其主键索引是聚簇索引，辅助索引的叶子节点存储的是主键值的引用。而 MyISAM 存储引擎不支持事务，其索引是独立于数据存储的，支持哈希索引和全文索引。
- 在创建索引时，需要根据实际使用的存储引擎的特性来选择合适的索引类型和创建策略。例如，对于 InnoDB 存储引擎，主键索引的选择尤为重要，因为它会影响数据的存储顺序和查询性能。

四、索引的维护

（一）查看索引

在 MySQL 中，可以通过以下命令查看表的索引信息：

SHOW INDEX FROM table_name;

或者

SHOW INDEX FROM table_name FROM database_name;

例如：

SHOW INDEX FROM student;

这个命令会返回表 student 的索引信息，包括索引名称、索引类型、索引字段、索引是否唯一等详细信息。

（二）删除索引

如果某个索引不再需要，可以通过以下命令删除索引：

ALTER TABLE table_name DROP INDEX index_name;

或者

DROP INDEX index_name ON table_name;

例如：

ALTER TABLE student DROP INDEX idx_score;

或者

DROP INDEX idx_score ON student;

这两个命令都可以删除表 student 上名为 idx_score 的索引。

（三）更新索引

在插入、更新和删除数据时，MySQL 会自动更新索引。但是，如果表中的数据发生了大量变化，或者索引的碎片过多，可能会影响索引的性能。在这种情况下，可以通过以下命令优化索引：

OPTIMIZE TABLE table_name;

例如：

OPTIMIZE TABLE student;

这个命令会对表 student 进行优化，包括整理数据碎片、更新索引统计信息等操作，从而提高索引的性能。

（四）分析索引

可以通过以下命令分析索引的使用情况：

ANALYZE TABLE table_name;

例如：

ANALYZE TABLE student;

这个命令会对表 student 进行分析，更新索引的统计信息，帮助数据库优化器更好地选择索引进行查询优化。

五、索引的性能优化

（一）优化查询语句

使用索引覆盖扫描
- 索引覆盖扫描是指查询语句中所需的所有字段都可以通过索引直接获取，而无需回表查询数据记录。这种查询方式可以减少磁盘 I/O 操作，提高查询速度。
- 例如，对于一个包含主键索引和普通索引的表，如果查询语句中只包含索引字段，那么数据库可以直接通过索引获取结果，而无需访问数据记录。例如：
```
SELECT id, name FROM student WHERE name = 'John';
```
  如果在 name 字段上有索引，且查询语句中只需要 id 和 name 字段，那么可以通过索引覆盖扫描直接获取结果。
避免隐式类型转换
- 在查询语句中，如果字段类型与查询条件的类型不一致，可能会导致隐式类型转换。隐式类型转换可能会使索引失效，从而降低查询速度。
- 例如，假设有一个字段 age 是整数类型，但在查询语句中使用了字符串类型的值：
```
SELECT \* FROM student WHERE age = '20';
```
  这种情况下，数据库可能会对 age 字段进行隐式类型转换，从而导致索引失效。为了避免这种情况，应该确保查询条件的类型与字段类型一致：
```
SELECT \* FROM student WHERE age = 20;
```
合理使用 LIKE 查询
- LIKE 查询是一种模糊匹配查询，它使用百分号（%）和下划线（_）作为通配符。在使用 LIKE 查询时，需要注意以下几点：
  - 如果通配符出现在查询条件的开头，如 LIKE '%abc'，那么索引通常会失效，因为数据库无法利用索引的有序性来快速定位匹配的记录。在这种情况下，可以考虑使用全文索引或其他查询方式。
  - 如果通配符出现在查询条件的结尾，如 LIKE 'abc%'，那么索引仍然可以发挥作用，但查询效率会受到通配符的影响。为了提高查询效率，可以尽量减少通配符的使用，或者对字段进行分词处理，使用全文索引进行查询。
避免在索引字段上使用函数
- 在查询语句中，如果在索引字段上使用了函数，可能会导致索引失效。例如：
```
SELECT \* FROM student WHERE YEAR(birthdate) = 2000;
```
  这个查询语句中，birthdate 字段上有索引，但由于使用了 YEAR() 函数，索引无法直接发挥作用。为了避免这种情况，可以将查询语句改写为：
```
SELECT \* FROM student WHERE birthdate BETWEEN '2000-01-01' AND '2000-12-31';
```
  这样可以利用索引进行范围查询，提高查询速度。

（二）优化索引设计

合理选择组合索引的字段顺序
- 对于组合索引，字段的顺序非常重要。一般来说，应该将最常用的查询条件字段放在前面，这样可以提高索引的利用率。同时，需要考虑查询语句中字段的使用频率和过滤性，将过滤性高的字段放在前面。
- 例如，假设有一个查询语句经常使用 name 和 age 字段作为查询条件，且 name 字段的过滤性高于 age 字段，那么可以将 name 字段放在组合索引的前面：
```
CREATE INDEX idx_name_age ON student (name, age);
```
避免过度索引
- 虽然索引可以提高查询速度，但过多的索引会占用额外的存储空间，并且在插入、更新和删除数据时会增加系统的开销。因此，需要避免过度索引，只创建真正需要的索引。
- 在创建索引时，可以通过分析查询语句的执行计划来评估索引的有效性。如果某个索引很少被使用，或者对查询性能的提升不明显，可以考虑删除该索引。
使用前缀索引
- 对于字符串类型的字段，如果字段的值较长，可以考虑使用前缀索引来减少索引的大小。前缀索引是指在字段的前几个字符上创建索引，而不是在整个字段上创建索引。
- 例如，假设有一个字段 description 是一个较长的文本字段，可以为其创建一个前缀索引：
```
CREATE INDEX idx_description ON student (description(10));
```
  这个前缀索引只在 description 字段的前 10 个字符上创建索引，可以减少索引的大小，提高索引的创建和维护效率。但需要注意的是，前缀索引可能会降低索引的精确性，因此需要根据实际需求合理选择前缀长度。
考虑分区表的索引策略
- 对于大型表，可以考虑使用分区表来提高查询性能。分区表可以将数据按照一定的规则划分成多个分区，每个分区可以独立进行查询和维护操作。
- 在分区表中，索引的创建和使用也需要特别考虑。对于全局索引，索引会覆盖整个表的所有分区；而对于局部索引，每个分区都有自己的索引。根据查询需求和数据分布情况，可以选择合适的索引类型和分区策略，以提高查询性能。

（三）优化存储引擎参数

调整缓存大小
- 对于 InnoDB 存储引擎，可以通过调整 innodb_buffer_pool_size 参数来设置缓存大小。缓存越大，可以存储更多的数据和索引信息，从而减少磁盘 I/O 操作，提高查询速度。
- 一般来说，可以将 innodb_buffer_pool_size 设置为服务器物理内存的 50% - 75%，具体值需要根据服务器的配置和实际负载情况进行调整。
优化日志参数
- InnoDB 存储引擎使用日志来保证事务的持久性和一致性。通过调整日志相关的参数，如 innodb_log_file_size 和 innodb_log_buffer_size，可以提高日志的写入效率，从而间接提高索引的更新效率。
- 较大的日志文件可以减少日志切换的频率，提高日志的写入速度；较大的日志缓冲区可以减少日志的磁盘 I/O 操作，提高日志的写入效率。但需要注意的是，日志参数的调整需要谨慎进行，因为较大的日志文件和缓冲区会占用更多的磁盘空间和内存资源。
调整锁粒度
- InnoDB 存储引擎支持行级锁，通过调整锁粒度可以提高并发性能。在高并发的场景下，可以通过设置 innodb_lock_wait_timeout 参数来控制锁等待的超时时间，避免长时间的锁等待导致的性能问题。
- 同时，可以通过调整 innodb_row_lock_time 和 innodb_row_lock_waits 等参数来监控锁的使用情况，优化锁的管理策略，从而提高系统的并发性能。

六、索引的常见误区

（一）索引越多越好

这是一个常见的误区。虽然索引可以提高查询速度，但过多的索引会带来以下问题：

增加存储空间的占用
- 每个索引都需要占用额外的存储空间，过多的索引会导致存储空间的浪费。尤其是在大型表中，索引的大小可能会非常可观。
降低数据更新的效率
- 在插入、更新和删除数据时，数据库需要同步更新索引。索引越多，更新操作的开销越大，从而降低数据更新的效率。在高并发的写操作场景下，过多的索引可能会导致性能瓶颈。
增加查询优化的复杂性
- 过多的索引会使查询优化器的选择更加复杂。查询优化器需要在多个索引中选择最优的索引进行查询优化，而过多的索引可能会导致查询优化器选择错误的索引，从而降低查询性能。

因此，在创建索引时，需要根据实际需求合理选择索引的数量和类型，避免过度索引。

（二）索引可以解决所有性能问题

索引虽然可以提高查询速度，但它并不能解决所有性能问题。以下是一些索引无法解决的性能问题：

数据量过大导致的性能问题
- 如果表中的数据量过大，即使有索引，查询速度也可能较慢。在这种情况下，需要考虑使用分区表、分表或分库等技术来分散数据量，提高查询性能。
复杂的查询语句导致的性能问题
- 对于一些复杂的查询语句，如多表连接查询、子查询、嵌套查询等，索引的作用可能会受到限制。在这种情况下，需要优化查询语句的结构，减少查询的复杂性，或者使用视图、存储过程等技术来提高查询性能。
系统资源不足导致的性能问题
- 如果服务器的 CPU、内存或磁盘 I/O 资源不足，即使有索引，查询速度也可能较慢。在这种情况下，需要增加服务器的硬件资源，或者优化系统的配置参数，提高系统的整体性能。

因此，索引只是提高数据库性能的一种手段，还需要结合其他优化技术，如查询优化、系统优化、硬件优化等，综合解决性能问题。

（三）唯一索引和主键索引可以互换

虽然唯一索引和主键索引都可以保证字段的唯一性，但它们之间存在以下区别：

主键索引的唯一性和非空性
- 主键索引的值必须是唯一的，且不允许为空（NULL）。主键字段是表中记录的唯一标识，每个表只能有一个主键索引。
唯一索引的空值允许性
- 唯一索引的值也必须是唯一的，但允许为空（NULL）。一个表可以有多个唯一索引，且唯一索引可以用于多个字段的组合。
存储结构和查询效率
- 对于 InnoDB 存储引擎，主键索引是聚簇索引，数据记录按照主键的顺序存储在磁盘上。因此，主键索引的查询效率非常高，尤其是在进行范围查询和排序查询时。而唯一索引是辅助索引，其叶子节点存储的是主键值的引用，查询效率相对较低。

因此，主键索引和唯一索引不能互换。在设计数据库表时，需要根据实际需求合理选择主键索引和唯一索引。

（四）全文索引可以替代普通索引

全文索引和普通索引适用于不同的查询场景，不能相互替代。

全文索引的特点
- 全文索引主要用于支持文本搜索操作，如模糊匹配、词干匹配、多词匹配等。它对文本数据进行分词处理，并建立倒排索引，从而实现高效的文本搜索功能。全文索引适用于对文本字段（如文章内容、评论等）进行搜索的场景。
普通索引的特点
- 普通索引适用于等值查询、范围查询和排序查询等多种查询场景。它通过 B+ 树结构存储索引信息，可以快速定位到满足查询条件的记录。普通索引适用于对数值字段、日期字段或字符串字段进行精确查询或范围查询的场景。

因此，全文索引和普通索引各有优缺点，需要根据实际查询需求选择合适的索引类型。在某些场景下，可以同时使用全文索引和普通索引，以满足不同的查询需求。

七、案例分析

（一）案例背景

假设有一个电商网站的数据库，其中有一个订单表 orders，表结构如下：

字段名	数据类型	描述
order_id	INT	订单 ID（主键）
user_id	INT	用户 ID
order_date	DATE	订单日期
order_amount	DECIMAL(10,2)	订单金额
status	VARCHAR(20)	订单状态（如“已支付”、“未支付”等）

该表中存储了大量订单数据，随着业务的发展，订单数据量不断增加，查询性能逐渐下降。为了提高查询性能，需要对表进行索引优化。

（二）查询需求分析

根据业务需求，常见的查询场景包括：

按用户 ID 查询订单
- 查询某个用户的所有订单信息。
- 查询语句示例：
```
SELECT \* FROM orders WHERE user_id = 123;
```
按订单日期查询订单
- 查询某个日期范围内的订单信息。
- 查询语句示例：
```
SELECT \* FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
```
按订单金额查询订单
- 查询订单金额大于某个值的订单信息。
- 查询语句示例：
```
SELECT \* FROM orders WHERE order_amount > 1000;
```
按订单状态查询订单
- 查询某个订单状态的订单信息。
- 查询语句示例：
```
SELECT \* FROM orders WHERE status = '已支付';
```

（三）索引优化策略

根据上述查询需求，可以考虑以下索引优化策略：

为 user_id 字段创建索引
- 由于经常需要按用户 ID 查询订单，可以为 user_id 字段创建普通索引：
```
CREATE INDEX idx_user_id ON orders (user_id);
```
- 这样可以加快按用户 ID 查询订单的速度。
为 order_date 字段创建索引
- 由于经常需要按订单日期查询订单，可以为 order_date 字段创建普通索引：
```
CREATE INDEX idx_order_date ON orders (order_date);
```
- 这样可以加快按订单日期查询订单的速度，尤其是在进行范围查询时。
为 order_amount 字段创建索引
- 由于经常需要按订单金额查询订单，可以为 order_amount 字段创建普通索引：
```
CREATE INDEX idx_order_amount ON orders (order_amount);
```
- 这样可以加快按订单金额查询订单的速度，尤其是在进行范围查询时。
为 status 字段创建索引
- 由于经常需要按订单状态查询订单，可以为 status 字段创建普通索引：
```
CREATE INDEX idx_status ON orders (status);
```
- 这样可以加快按订单状态查询订单的速度。

（四）优化后的性能对比

在创建索引后，对上述查询语句进行性能测试，结果如下：

查询语句	优化前时间（毫秒）	优化后时间（毫秒）	性能提升比例
`SELECT \* FROM orders WHERE user_id = 123;`	1200	10	99.2%
`SELECT \* FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';`	2500	50	97.9%
`SELECT \* FROM orders WHERE order_amount > 1000;`	1800	30	98.3%
`SELECT \* FROM orders WHERE status = '已支付';`	800	8	98.8%

从测试结果可以看出，创建索引后，查询性能得到了显著提升，查询时间大幅减少。

（五）进一步优化

虽然索引优化已经取得了显著的效果，但还可以进一步优化。例如：

创建组合索引
- 如果某些查询语句同时使用多个字段作为查询条件，可以考虑创建组合索引。例如，如果经常需要按用户 ID 和订单日期查询订单，可以创建组合索引：
```
CREATE INDEX idx_user_id_order_date ON orders (user_id, order_date);
```
- 这样可以进一步提高查询效率，减少索引的数量。
调整索引顺序
- 对于组合索引，字段的顺序非常重要。可以根据查询语句中字段的使用频率和过滤性，调整组合索引的字段顺序。例如，如果 order_date 字段的过滤性高于 user_id 字段，可以将 order_date 字段放在前面：
```
CREATE INDEX idx_order_date_user_id ON orders (order_date, user_id);
```
使用全文索引
- 如果需要对订单的详细信息（如订单描述字段）进行文本搜索，可以考虑使用全文索引。例如：
```
CREATE FULLTEXT INDEX idx_order_description ON orders (description);
```
- 这样可以支持复杂的文本搜索操作，提高文本查询的效率。

posted @ 2025-04-08 16:57 软件职业规划阅读(22) 评论(0) 收藏举报

刷新页面返回顶部