掌握MySQL索引优化，提升查询性能

当数据库响应变慢时，我通常会首先检查MySQL索引，但这也是最容易犯错的地方之一。一个索引可以将全表扫描转变为快速查找，但也可能拖慢写入速度、浪费内存，甚至如果查询优化器从未使用它，还会给你一种虚假的进步感。

实际的问题不是“这个列应该加索引吗？”，更好的问题是“我想让哪个查询变得更高效，以及如何证明索引确实有帮助？”。阅读时请牢记这个问题。好的索引始于真实的查询，而不是看起来重要的列列表。

什么是MySQL索引？

MySQL索引是一种数据结构，用于提高数据库表上数据检索操作的速度。可以把它想象成书中的索引：无需阅读整本书来查找特定主题，你可以在索引中查找该主题，它会告诉你确切的页码。类似地，数据库索引允许MySQL快速定位满足特定查询条件的行，而无需扫描整个表。

当你查询一个表时，MySQL可以使用索引来更快地找到相关行，这比逐行检查要高效得多。这对于包含大量行的表或涉及过滤（WHERE子句）、表连接（JOIN子句）或排序（ORDER BY子句）的查询尤其有益。

索引的工作原理

MySQL通常对普通的InnoDB索引使用B-tree索引。B-tree按键排序，因此它非常适合等值查找、范围查询、有序扫描以及许多连接操作。当你在一个或多个列上创建索引时，MySQL会构建一个结构，其中：

叶子节点包含实际的数据指针，或者在聚簇索引（如InnoDB的主键）的情况下，包含数据行本身。
内部节点包含帮助导航树以找到正确叶子节点的键。

当查询可以使用索引的左侧部分时，MySQL可以导航到树的狭窄部分，而不是读取整个表。这才是真正的优势。索引并不能使每个查询都变快，它只是让某些访问模式变得廉价。

MySQL索引的类型

MySQL支持多种类型的索引，每种都有其优势和用例。

1. 主键

PRIMARY KEY约束确保列中的每个值都是唯一的且不为NULL。它隐式地创建索引。
一个表只能有一个PRIMARY KEY。
InnoDB表在物理上按主键排序（聚簇索引）。

示例：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. 唯一索引

UNIQUE索引强制索引列中的所有值都是唯一的。它允许NULL值，但允许多个NULL（除非该列也是PRIMARY KEY或另一个阻止它的UNIQUE约束的一部分）。
用于确保数据完整性，当列必须唯一但不是主标识符时。

示例：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. 普通索引（INDEX或KEY）

标准索引，也称为非唯一索引。
用于加速数据检索。不强制唯一性。

示例：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. 全文索引

用于对CHAR、VARCHAR和TEXT列进行全文搜索。
允许在大型文本字段中进行关键字搜索。
现代MySQL版本中的InnoDB支持。较旧的MySQL安装可能有不同的限制，因此在设计之前请检查您的确切版本。

示例：

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. 空间索引

用于索引空间数据类型，如点、线和多边形。
行为和需求因MySQL版本和存储引擎而异。测试您计划运行的确切空间查询；不要假设空间索引会对每个GIS风格的谓词都有帮助。

6. 哈希索引（有限使用）

MySQL的MEMORY存储引擎支持HASH索引。它们专为等值查找而设计，不适用于范围扫描或排序。
对于大多数常见场景来说，这不是一种通用索引类型。

创建和管理索引

如何创建索引

您可以在创建表时创建索引，也可以通过修改现有表来创建。

1. 在创建表时：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. 修改现有表：

-- 添加单列索引
ALTER TABLE customers
ADD INDEX idx_email (email);

-- 添加唯一索引
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- 添加多列（复合）索引
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

如何删除索引

如果索引不再需要或对性能产生负面影响（例如，在写入期间），您可以删除它。

-- 删除普通索引
ALTER TABLE customers
DROP INDEX idx_email;

-- 删除唯一索引
ALTER TABLE users
DROP INDEX uidx_username;

多列（复合）索引

复合索引是在两个或多个列上创建的。复合索引中列的顺序至关重要。

在(col1, col2)上的复合索引可用于仅过滤col1的查询，或同时过滤col1和col2的查询。
通常不用于仅过滤col2的查询。

示例：

考虑一个在(customer_id, order_date)上的索引。此索引对以下查询最有效：

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

对于SELECT * FROM orders WHERE order_date = '2023-10-27';可能不太有用。

MySQL索引的最佳实践

1. 为`WHERE`、`JOIN`和`ORDER BY`子句中使用的列建立索引

这些是索引提供显著性能提升的最常见位置。

WHERE子句： 过滤条件是主要用例。
JOIN条件： 为JOIN语句的ON子句中使用的列建立索引可以显著加速表连接。
ORDER BY和GROUP BY子句： 索引可以帮助MySQL避免排序操作。

2. 明智地使用复合索引

顺序很重要： 将匹配查询形状的列放在前面。等值过滤器通常放在范围过滤器之前。用于排序的列可以在过滤列之后提供帮助。
如果您的实际查询不按选择性最高的列进行过滤，不要盲目地将其放在第一位。一个在(status, created_at)上的索引对于WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50可能非常出色，即使status的基数较低，因为它匹配了访问模式。

3. 在添加索引前后使用`EXPLAIN`

永远不要凭感觉判断索引。运行EXPLAIN，在暂存或维护安全的环境中添加上索引，然后再次运行EXPLAIN。

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

查看type、key、rows和Extra列。如果key为NULL，则MySQL未选择索引。如果rows仍然接近表大小，则索引可能对该查询的选择性不够。如果Extra显示Using filesort，这并不自动意味着不好，但它告诉你MySQL无法从所选索引按请求顺序返回行。

对于MySQL 8.0.18及更高版本，EXPLAIN ANALYZE可能更有用，因为它会实际执行查询并报告实际时间和行数：

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

在生产系统上谨慎使用它，因为它会执行语句。

4. 围绕工作流构建索引，而不是表

想象一个管理屏幕，列出最近失败的付款：

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

仅在status上的索引可能仍然会让MySQL对大量失败行进行排序。一个在(status, created_at)上的索引通常是更好的匹配，因为MySQL可以找到失败的行并按时间顺序读取它们。如果查询只返回索引中的列，您可以考虑覆盖索引：

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

这可能会很快，但并非免费。索引更宽，占用更多存储空间，并且每次写入的成本更高。我只在频繁运行的热查询上使用覆盖索引，以证明额外的维护成本是合理的。

5. 注意那些看起来正确但实际上无用的索引

这些是常见的陷阱：

函数隐藏了索引值：WHERE DATE(created_at) = '2025-01-01'。
前导通配符阻止了正常的B-tree使用：WHERE email LIKE '%@example.com'。
类型不匹配强制转换：将整数列与带引号的字符串进行比较可能仍然有效，但在实际模式中可能会混淆计划。
索引以错误的列开头：(created_at, customer_id)与(customer_id, created_at)不同。

尽可能重写谓词：

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

这种形式允许MySQL在created_at上使用范围扫描。

6. 谨慎删除冗余和未使用的索引

过度索引是一个隐蔽的性能问题。每个额外的二级索引在INSERT、UPDATE和DELETE期间都需要维护。在写入密集的表上，五个未使用的索引可能比一个慢速SELECT更成问题。

在MySQL 5.7和8.0中，sys模式可以帮助您找到候选索引：

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

将该输出视为线索，而不是命令。一个索引可能看起来未使用，因为服务器最近重启，因为月度报告尚未运行，或者因为暂存环境的流量与生产环境不匹配。在删除索引之前，请检查部署历史、计划任务和外键要求。

7. 在大表上安全地添加索引

在小表上，ALTER TABLE ... ADD INDEX通常不会引起问题。但在大型生产表上，这可能是一个真正的操作。根据MySQL版本、存储引擎、表定义和确切的DDL，添加索引可能使用在线DDL，或者可能通过元数据锁、临时空间、重做日志生成和复制延迟造成压力。

在添加大型索引之前，请检查：

表和现有索引的大小。
副本能否跟上。
您的MySQL版本是否支持您期望的在线算法。
如果长时间事务阻塞DDL，应用程序能否容忍元数据锁。

对于敏感系统，请使用迁移工具，如pt-online-schema-change或gh-ost，或者在低流量时段安排DDL。

实用的索引审查流程

当我审查一个慢速MySQL查询时，我按以下顺序进行：

捕获带有实际绑定值的精确SQL。
运行EXPLAIN，并在安全的情况下运行EXPLAIN ANALYZE。
检查现有索引是否匹配WHERE、JOIN和ORDER BY模式。
在暂存环境中添加最小的有用复合索引。
比较检查的行数、查询时间和写入影响。
谨慎推出，并监控慢查询日志和复制延迟。

这个流程让索引保持诚实。你不是在收集索引，而是试图减少MySQL为应用程序实际运行的查询所做的工作量。

掌握MySQL索引优化，提升查询性能

什么是MySQL索引？

索引的工作原理

MySQL索引的类型

1. 主键

2. 唯一索引

3. 普通索引（INDEX或KEY）

4. 全文索引

5. 空间索引

6. 哈希索引（有限使用）

创建和管理索引

如何创建索引

如何删除索引

多列（复合）索引

MySQL索引的最佳实践

1. 为WHERE、JOIN和ORDER BY子句中使用的列建立索引

2. 明智地使用复合索引

3. 在添加索引前后使用EXPLAIN

4. 围绕工作流构建索引，而不是表

5. 注意那些看起来正确但实际上无用的索引

6. 谨慎删除冗余和未使用的索引

7. 在大表上安全地添加索引

实用的索引审查流程

1. 为`WHERE`、`JOIN`和`ORDER BY`子句中使用的列建立索引

3. 在添加索引前后使用`EXPLAIN`