提升 MySQL 性能的关键：索引规约指南

hello，大家好，我是千羽。

在数据库管理中，索引是提升查询性能的关键工具。对于 MySQL 小白来说，了解并掌握如何有效使用索引，是优化数据库性能的基础。本文将深入探讨 MySQL 索引的规约，通过具体的实例和建议，帮助你在实际应用中更好地利用索引，提高查询效率。

为什么要使用索引？

在数据库管理中，索引是一种特殊的数据结构，它能够加速数据检索的速度。可以把索引想象成一本书的目录，目录可以让你快速找到书中的某一章节，而不用逐页翻阅。类似地，索引能够帮助数据库快速定位到数据的位置，从而大幅度提高查询效率。

示例：

假设我们有一个用户表 users，包含一百万条记录。如果我们在 email 字段上建立索引，那么查询某个特定电子邮件的速度将从几秒钟减少到毫秒级别。

-- 查询电子邮件的速度差异
-- 无索引
SELECT * FROM users WHERE email = 'example@example.com';

-- 有索引
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'example@example.com';

在没有索引的情况下，数据库需要扫描整张表来找到匹配的记录，而有索引的情况下，数据库只需在索引中查找，大大提高了查询速度。

索引规约详解

1. 业务上具有唯一特性的字段必须建成唯一索引

【强制】 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建立唯一索引。

说明：唯一索引保证了字段的唯一性，即使在应用层进行了完善的校验，也难以完全避免数据重复的问题。

正例：对用户表中的 email 字段设置唯一索引，以确保每个电子邮件地址唯一。

CREATE UNIQUE INDEX idx_user_email ON users(email);

示例：

假设你的应用程序允许用户注册电子邮件地址，如果没有唯一索引，两个用户可能会用相同的电子邮件地址进行注册，这会导致数据不一致。通过建立唯一索引，数据库在插入时会自动检测并阻止重复的电子邮件地址。

2. 超过三个表禁止 join

【强制】 超过三个表的 join 操作应尽量避免。如果需要进行多表 join，确保被关联的字段数据类型一致，并且这些字段需要有索引。

说明：多表 join 的性能可能受到影响，尤其是当涉及到超过三个表时。

正例：对于涉及三个表的查询，确保所有关联字段都有索引，并优化查询语句。

SELECT a.*, b.*, c.*
FROM orders a
JOIN customers b ON a.customer_id = b.id
JOIN products c ON a.product_id = c.id;

示例：

如果你需要查询所有订单的客户信息和产品信息，可以将 orders 表、customers 表和 products 表进行连接。为了提高查询性能，应确保 customer_id 和 product_id 字段上都有索引。

3. 在 varchar 字段上建立索引时，必须指定索引长度

【强制】 在 varchar 字段上建立索引时，必须指定索引长度，而不是对整个字段建立索引。

说明：索引长度与区分度之间存在矛盾。通常，20 个字符的索引长度可以提供高达 90% 以上的区分度。

正例：在 username 字段上建立索引时，只对前 20 个字符进行索引。

CREATE INDEX idx_username ON users(username(20));

示例：

如果你的 username 字段非常长，但查询时只关注前 20 个字符，可以通过对 username 的前 20 个字符建立索引来优化性能。这不仅节省了存储空间，还提高了查询效率。

4. 页面搜索严禁左模糊或者全模糊

【强制】 页面搜索严禁使用左模糊（如 %abc）或者全模糊查询。

说明：左模糊和全模糊查询会导致索引无法有效利用，因为它们需要扫描整个索引。对于这种情况，使用搜索引擎或其他解决方案更为合适。

正例：

-- 右模糊
SELECT * FROM products WHERE name LIKE 'abc%';

示例：

如果你在电子商务网站上执行商品搜索，避免使用 %abc% 这种查询方式，而是使用 abc%，这样可以利用索引加快搜索速度。

5. 利用索引的有序性

【推荐】 如果查询中包含 ORDER BY 子句，请利用索引的有序性。

正例：在查询中使用 ORDER BY 的字段应是组合索引的一部分，并且放在索引组合的最后。

SELECT * FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC;

示例：

对于订单表的查询，若需要按 created_at 字段排序，确保建立的索引中包括 status 和 created_at，并且 created_at 在索引的最后部分，这样可以有效利用索引排序。

6. 利用覆盖索引来进行查询操作

【推荐】 利用覆盖索引来避免回表查询。

说明：覆盖索引允许数据库只从索引中读取数据，而不需要访问数据表，从而提高查询性能。使用 EXPLAIN 工具时，extra 列会显示 using index。

正例：

-- 使用覆盖索引
CREATE INDEX idx_user_email ON users(email);
SELECT email FROM users WHERE email = 'example@example.com';

示例：

如果查询只涉及索引字段，而不涉及表中的其他字段，可以利用覆盖索引加速查询。例如，上述查询只读取了 email 字段，而没有回表访问 users 表。

7. 优化超多分页场景

【推荐】 对于超多分页场景，使用延迟关联或子查询优化查询性能。

说明：MySQL 处理分页时，会取 OFFSET + N 行，然后返回放弃前 OFFSET 行。对于大页码的分页，这种方式效率低下。可以通过子查询优化分页。

正例：

SELECT a.*
FROM orders a
JOIN (
    SELECT id FROM orders WHERE status = 'shipped' LIMIT 100000, 20
) b ON a.id = b.id;

示例：

当你需要查询订单的第 100,000 到第 100,020 条记录时，直接查询可能非常慢。通过先查询 ID 列表，再使用这些 ID 查询订单，可以显著提高性能。

8. SQL 性能优化目标

【推荐】 SQL 性能优化的目标应至少达到 range 级别，ref 级别最佳，const 级别最佳。

说明：

1. const：最多只有一个匹配行，优化阶段即可读取数据。
2. ref：使用普通索引。
3. range：对索引进行范围检索。

反例：EXPLAIN 结果

中的 type 为 index，表示索引全扫描，性能较差。

示例：

对于查询 WHERE status = 'shipped' AND created_at > '2024-01-01'，尽量优化到 range 级别。如果查询仅涉及 status 字段，可以优化到 const 级别。

9. 组合索引的顺序

【推荐】 建立组合索引时，将区分度最高的字段放在索引的最左边。

正例：对于查询 WHERE a = ? AND b = ?，a 列的区分度更高，应将 a 放在索引的最前面。

说明：存在非等号和等号混合判断条件时，将等号条件的列放在索引的最前面。

示例：

CREATE INDEX idx_a_b ON table(a, b);

对于查询 WHERE a = ? AND b = ?，将 a 列放在索引的最前面，以便于快速定位数据。

10. 防止隐式转换导致索引失效

【推荐】 防止因字段类型不同造成的隐式转换，导致索引失效。

说明：确保字段的数据类型一致，避免因隐式转换导致索引失效。

示例：

-- 如果字段类型不同，会导致隐式转换
SELECT * FROM orders WHERE order_id = '1001';  -- order_id 是整数型

确保查询中的字段类型与索引类型一致，以避免因类型不匹配导致的性能问题。

11. 创建索引时的常见误解

【参考】

1. 宁滥勿缺：认为每个查询都需要建立索引。
2. 宁缺勿滥：认为索引会消耗空间，严重拖慢更新和新增速度。
3. 抵制唯一索引：认为业务的唯一性应通过“先查后插”方式在应用层解决。

示例：

• 宁滥勿缺：不必要对每个字段建立索引，合理选择索引可以避免性能问题。
• 宁缺勿滥：虽然索引会消耗空间，但它带来的性能提升通常是值得的。
• 抵制唯一索引：业务逻辑中的唯一性应通过唯一索引来确保，而不仅仅是应用层的检查。

总结

索引是 MySQL 数据库性能优化的关键工具。通过遵循上述索引规约，你可以显著提高查询效率，避免数据冗余和潜在的性能问题。掌握并应用这些规约，不仅能帮助你更好地管理数据库，还能为你的应用提供更高效的数据访问能力。

• 参考链接：https://github.com/alibaba/p3c/blob/master/Java%E5%BC%80%E5%8F%91%E6%89%8B%E5%86%8C(%E9%BB%84%E5%B1%B1%E7%89%88).pdf