【13】MySQL如何选择合适的索引？

选择合适的索引是数据库优化中非常重要的一步，正确的索引可以大幅提高查询性能，减少数据库响应时间；而不当的索引选择则可能增加存储开销，影响数据库性能，甚至使得某些操作变得更加缓慢。以下是选择合适索引的一些指导原则和策略：

1. 基于查询需求选择索引

索引的目的是为了加速查询操作，因此，首先要分析应用中最常执行的查询类型，并根据查询需求来选择合适的索引。

1.1. 基于 WHERE 子句的选择

大多数情况下，索引主要用于加速 WHERE 子句中的条件查询。如果查询条件中涉及某些列并且这些列的数据量较大，通常可以为这些列创建索引。

单列索引： 当查询仅涉及某个列时，可以为该列创建单列索引。例如：

SELECT * FROM users WHERE age = 30;
--为 age 列创建索引可以加速此查询。

复合索引（联合索引）： 当查询涉及多个列时（例如 WHERE 子句中有多个条件），可以考虑创建复合索引。复合索引不仅加速查询，还能在多列条件同时出现时优化查询速度。例如：

SELECT * FROM orders WHERE customer_id = 1001 AND order_date > '2023-01-01';

在 customer_id 和 order_date 列上创建复合索引：

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

复合索引的列顺序非常重要，应该根据查询中使用列的顺序来创建索引。例如，如果查询中首先使用 customer_id，其次使用 order_date，那么复合索引的顺序应该是 (customer_id, order_date)。

1.2. 基于排序和分组的选择

如果查询中涉及排序（ORDER BY）或分组（GROUP BY）操作，适当地为这些列创建索引可以提升性能。例如：

SELECT COUNT(*) FROM orders WHERE customer_id = 1001 GROUP BY order_date;

如果我们在 customer_id 和 order_date 列上有复合索引，那么在执行查询时，MySQL 可以通过该索引加速分组操作。

1.3. 基于连接查询的选择

如果查询中涉及多表连接操作（JOIN），可以考虑为连接条件中的列创建索引。通常，连接操作涉及表中用于关联的列，这些列是优化连接性能的关键。

例如：

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

在 orders.customer_id 和 customers.customer_id 上分别创建索引可以加速这类连接查询：

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_customers_customer_id ON customers (customer_id);

1.4. 基于范围查询的选择

对于范围查询（如使用 >、<、BETWEEN 等操作符），通常也会受益于索引。比如：

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

如果 order_date 列有索引，MySQL 就可以使用该索引快速查找符合条件的记录。

2. 考虑表的大小和查询频率

2.1. 数据量较大的表：

对于包含大量数据的表（例如，百万级或千万级记录的表），索引对于提高查询性能至关重要。尤其是在频繁执行查询的列上创建索引，能够显著减少查询的响应时间。例如，用户信息表、订单表等通常具有较大数据量。

2.2. 数据量较小的表：

对于数据量较小的表，MySQL 的查询优化器通常可以通过全表扫描来完成查询，创建索引的性能提升可能微乎其微，反而会带来额外的存储开销。在这种情况下，应该避免过多创建索引，或者仅在经常被查询的列上创建索引。

2.3. 查询频率较高的列：

如果某列频繁出现在查询条件中，通常为该列创建索引是明智的选择。例如，如果查询经常基于 user_id 查找记录，应该为 user_id 列创建索引。

3. 索引的选择与查询优化

3.1. 避免在低基数列上创建索引

低基数列（Low Cardinality）： 指的是该列的数据值数量较少（例如性别列，只有“男”和“女”两个值）。在低基数列上创建索引的效果往往不明显，甚至可能适得其反。因为在查询时，MySQL 会扫描大量不相关的记录，浪费索引的存储和维护成本。
例如，创建索引在 gender 列上就没有太多意义，因为其可能只有两个值：

CREATE INDEX idx_gender ON users (gender);

这种索引的创建可能不会提高查询性能，反而会影响性能。

3.2. 避免在经常更新的列上创建索引

对于经常进行插入、更新和删除操作的列，避免在这些列上创建过多的索引，因为每次数据的变动都需要更新索引，可能会造成额外的性能开销。例如，某些标记字段或状态字段如果经常变化，最好不要为这些列创建索引。

3.3. 索引覆盖（Covering Index）

如果查询只需要某些列，并且这些列正好都包含在索引中，那么索引就称为 覆盖索引。覆盖索引可以让查询直接从索引中返回结果，而无需访问表的数据行，从而提高查询性能。

例如，查询仅涉及 order_id 和 customer_id 列：

SELECT order_id, customer_id FROM orders WHERE customer_id = 1001;

如果为 (customer_id, order_id) 创建复合索引，那么 MySQL 可以直接从该索引中返回查询结果，无需访问 orders 表的数据行。

4. 索引的维护和优化

4.1. 定期评估索引的效果

随着数据量的增加和查询模式的变化，数据库的查询需求也会发生变化。因此，定期评估现有索引的有效性是非常重要的。使用 MySQL 提供的 EXPLAIN 或 SHOW INDEX 命令来分析查询是否真正使用了索引。

4.2. 删除无效的索引

如果某个索引长期未被使用，或者由于查询模式的变化不再起作用，应该考虑删除该索引，减少存储开销和维护成本。可以使用以下命令删除索引：

DROP INDEX idx_name ON table_name;

5. 总结：如何选择合适的索引

选择合适的索引主要依据以下几个因素：

• 查询条件：为经常出现在 WHERE、JOIN、ORDER BY 和 GROUP BY 等子句中的列创建索引。
• 列的基数：对于高基数（unique 值多）的列创建索引，而低基数列则不建议创建索引。
• 数据量：大表中的查询需要索引来提高效率，而小表中的索引影响可能不大。
• 查询模式：根据查询的复杂性和使用频率，选择单列索引或复合索引。
• 索引维护：定期检查索引的效果，删除不再需要的索引，避免索引过多带来负担。

总之，选择合适的索引需要根据具体的查询需求、数据规模和数据库的写入频率来平衡，既要提高查询性能，又要避免过度优化带来的存储开销和写操作的负担。