SQL优化 之 添加索引

索引：

什么时候应该给字段添加索引？

1. 频繁用于查询的列： 如果某个字段经常出现在查询的 WHERE 条件中，尤其是作为过滤条件，通常应该为该列添加索引。索引能显著加快检索速度。
例子： 假设你有一个 users 表，常常根据 email 字段进行查询：

SELECT * FROM users WHERE email = 'example@example.com';

如果 email 字段的数据量很大，并且该查询非常频繁，那么为 email 添加索引能提高查询速度。

CREATE INDEX idx_email ON users (email);

2. 经常用作联接条件的列： 如果两个或多个表经常通过某一列联接，应该考虑在该列上添加索引，特别是当这些列的数据量较大时。
例子： 假设你有一个 orders 表和一个 customers 表，并且经常通过 customer_id 来联接它们：

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

由于 customer_id 是联接条件，因此应该为 orders 表和 customers 表的 customer_id 列分别添加索引。

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

3. 频繁排序的列： 如果查询经常按某一列进行排序（ORDER BY），那么在该列上添加索引可以加速排序操作，尤其是当数据量非常大时。
例子： 假设你经常按 created_at 排序：

SELECT * FROM orders ORDER BY created_at DESC;

为 created_at 列创建索引，可以加速排序：

CREATE INDEX idx_orders_created_at ON orders (created_at);

4. 频繁用于聚合操作的列： 如果某列用于聚合函数（如 COUNT()、SUM()、AVG()、MIN()、MAX()），添加索引可以加速这些操作，尤其是在处理大量数据时。
例子： 假设你经常执行如下查询：

SELECT AVG(amount) FROM orders WHERE status = 'completed';

如果 status 字段上的数据量很大，且查询非常频繁，可以考虑在 status 字段上添加索引：

CREATE INDEX idx_orders_status ON orders (status);

5. 唯一性要求的字段： 对于需要保证唯一性的列（如主键列或具有唯一约束的列），数据库通常会自动为这些列创建索引。通常，PRIMARY KEY 和 UNIQUE 约束会自动创建索引。
例子：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

user_id 和 email 列会自动被索引。

6. 范围查询条件的列： 如果查询经常使用范围操作符（如 <、>、BETWEEN、IN 等），在这些列上创建索引可以显著加快检索速度。
例子： 假设你经常使用范围查询：

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

在 order_date 上创建索引可以加速此类范围查询：

CREATE INDEX idx_orders_order_date ON orders (order_date);

什么时候不应该给字段添加索引？

1. 数据量较小的表： 如果表的数据量比较小，数据库引擎的优化器通常会选择全表扫描（Full Table Scan），此时创建索引可能反而导致性能下降，因为索引会增加额外的存储空间和维护成本。

2. 频繁更新或插入的列： 每次对表进行插入、更新或删除操作时，相关的索引也需要被更新，因此频繁更新的列不适合添加索引。索引会带来额外的性能开销，尤其是在频繁修改数据的表上。

3. 不常用于查询的列： 如果某个列不常出现在查询条件中，或者该列的选择性很差（例如，一个包含很多相同值的列），则不应为该列添加索引。例如，性别列（male 和 female）可能没有太多查询需求，因此为其添加索引的效果可能有限。

4. 多列索引的使用注意： 如果经常组合多个列作为查询条件，可以考虑使用 组合索引。然而，多个列的索引不适用于所有查询模式。如果组合的列经常出现在查询的不同顺序中，单独的单列索引可能会更有效。

索引类型选择

• 单列索引：为单一列创建索引，用于加速该列的查询。

• 复合索引：为多个列组合创建索引，适用于多列作为查询条件时。例如：

CREATE INDEX idx_name_date ON orders (customer_id, order_date);

• 唯一索引：确保列中的每个值都是唯一的，例如为 email 添加唯一索引，避免重复的电子邮件。

• 全文索引：对于长文本字段（如文章内容、评论等）进行搜索时使用。

总结

1. 添加索引的常见场景：
• 频繁作为查询条件的列（WHERE 子句）。
• 用于联接的列。
• 经常排序的列。
• 需要聚合的列（COUNT、SUM 等）。
• 具有唯一性约束的列。
• 用于范围查询的列。

2. 避免创建索引的场景：
• 数据量小的表。
• 更新频繁的列。
• 不常用的列或选择性差的列。

是否可以在已经有数据的情况下添加索引？

你可以使用 CREATE INDEX 语句为已有数据的表添加索引：

CREATE INDEX idx_column_name ON table_name (column_name);

CREATE INDEX idx_users_email ON users (email);

添加索引时的注意事项

1. 锁表问题：
• 在 MySQL 的 InnoDB 引擎下，创建索引时会锁表，导致其他查询可能会被阻塞，影响业务。
• MySQL 5.6+ 支持在线添加索引，可以减少锁表时间：

ALTER TABLE users ADD INDEX idx_users_email (email);

• 如果数据量较大，可以考虑 临时关闭业务写操作 或 使用在线索引创建方式。

2. 避免影响性能（大数据表优化）：
• 使用 LOCK=NONE（适用于 MySQL 8.0）：

ALTER TABLE users ADD INDEX idx_users_email (email) LOCK=NONE;

• 使用 PT-ONLINE-SCHEMA-CHANGE（Percona 工具）：
• 适用于生产环境，可以无锁地添加索引。
• 示例：

pt-online-schema-change --alter "ADD INDEX idx_users_email (email)" D=mydb,t=users --execute

3. 影响写入性能：
• 添加索引后，每次 INSERT、UPDATE 和 DELETE 操作都会多维护一个索引，因此会稍微降低写入性能。
• 如果数据写入非常频繁，建议：
1. 批量插入时先删除索引，插入完成后再重新创建索引，提高插入速度。
2. 使用覆盖索引，减少查询对数据页的访问，提高查询速度。

何时在已有数据的表上添加索引？

判断是否需要索引的方法

1. 使用 EXPLAIN 分析查询性能：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

• 如果 type = ALL，说明是 全表扫描，需要索引。
• 如果 key = idx_users_email，说明查询已经走索引。

2. 查询变慢：
• 如果 SELECT 语句的响应时间超过 100ms（根据业务需求），可以考虑索引优化。

3. 写入操作较少但查询频繁：
• 适合添加索引，比如日志查询、订单查询等。

总结

• 你可以在表有 2 万条数据后再添加索引。

• 直接用 CREATE INDEX 可能会锁表，建议在业务低峰期操作。

• 使用 ALTER TABLE ADD INDEX 或 pt-online-schema-change 避免锁表问题。

• 先用 EXPLAIN 分析查询是否需要索引，避免无效索引增加存储负担。