在 MySQL 建索引时需要注意哪些事项?

MySQL 建索引时的注意事项

在 MySQL 中合理使用索引可以显著提高查询性能，但不合理的索引设计可能会降低数据库的整体性能。以下是创建索引时需要注意的关键点：

📌 1. 选择合适的索引类型

MySQL 支持多种索引类型，不同的索引适用于不同的场景：

• 主键索引（PRIMARY KEY）：每个表只能有一个，默认是聚簇索引，数据存储按主键顺序排列。
• 唯一索引（UNIQUE）：保证列值唯一，但允许 NULL。
• 普通索引（INDEX）：用于提高查询效率，但不限制值的唯一性。
• 全文索引（FULLTEXT）：适用于 TEXT 或 VARCHAR 字段的全文搜索。
• 联合索引（Composite Index）：多列索引，可优化多条件查询。
• 前缀索引（Prefix Index）：针对 VARCHAR 或 TEXT 字段，索引前 n 个字符，减少索引体积。

✅ 示例：

CREATE UNIQUE INDEX idx_email ON users(email);  -- 唯一索引
CREATE INDEX idx_name ON users(name);          -- 普通索引

📌 2. 选择合适的索引字段

✅ 适合建索引的字段

• 经常用于 WHERE 查询的列

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

  给 email 字段建立索引可以加速查询。

• 用于 ORDER BY、GROUP BY 的列

  SELECT * FROM orders ORDER BY created_at DESC;
  

  created_at 建立索引可优化排序。

• 用于 JOIN 关联的字段

  SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
  

  customer_id 和 id 建立索引，可提高 JOIN 查询效率。

❌ 不适合建索引的字段

• 低选择性字段（重复值多）

  SELECT * FROM users WHERE gender = 'M';
  

  gender 只有 M 和 F，索引不会提高查询效率，反而增加维护成本。

• 经常更新的字段

  UPDATE users SET last_login = NOW() WHERE id = 1;
  

  last_login 经常变动，索引会导致频繁更新，降低写入性能。

• TEXT 或 BLOB 字段

  • MySQL 默认不支持对 TEXT 和 BLOB 直接建索引，必须使用前缀索引。

  CREATE INDEX idx_title ON articles(title(50));  -- 前 50 个字符建索引
  

📌 3. 索引不要太多

• 索引过多会影响 INSERT、UPDATE、DELETE 操作
  • 每次写入数据时，所有相关索引都需要更新，降低写入性能。
• 查询优化器可能会选择错误的索引
  • 索引过多，MySQL 可能错误地选择低效索引，导致查询变慢。

✅ 推荐策略

• 高选择性列创建索引（唯一值多的列）。
• 避免对小表创建索引，直接全表扫描可能更快。
• 定期分析索引的使用情况：

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

📌 4. 联合索引（组合索引）设计

最佳实践：左前缀匹配

• 联合索引的查询顺序非常重要，索引是按列顺序存储的。
• 创建联合索引：

  CREATE INDEX idx_name_age ON users(name, age);
  

  这个索引相当于：
  • name 有索引 ✅
  • (name, age) 有索引 ✅
  • 但 age 单独没有索引 ❌

正确使用联合索引

SELECT * FROM users WHERE name = 'Alice';      -- ✅ 可用索引
SELECT * FROM users WHERE name = 'Alice' AND age > 20;  -- ✅ 可用索引
SELECT * FROM users WHERE age > 20;           -- ❌ 无法使用索引

索引只会在匹配前缀字段时生效，单独查 age 不会使用 idx_name_age。

📌 5. 避免索引失效

❌ 避免在 WHERE 子句中对索引字段使用函数

SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- ❌ 索引失效

✅ 正确做法：

SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

❌ 避免隐式类型转换

SELECT * FROM users WHERE phone = 13812345678;  -- ❌ 索引失效

phone 是 VARCHAR，查询用的是 INT，MySQL 会转换数据类型，导致索引失效。

✅ 正确做法：

SELECT * FROM users WHERE phone = '13812345678';  -- ✅ 使用索引

❌ 避免使用 OR

SELECT * FROM users WHERE name = 'Alice' OR age = 25;  -- ❌ 索引可能失效

✅ 正确做法：

SELECT * FROM users WHERE name = 'Alice'
UNION
SELECT * FROM users WHERE age = 25;

📌 6. 使用覆盖索引（避免回表查询）

覆盖索引（Covering Index） 指的是查询的字段全部在索引中，无需回表，提高性能。

✅ 示例

CREATE INDEX idx_name_age ON users(name, age);

SELECT name, age FROM users WHERE name = 'Alice';  -- ✅ 覆盖索引，无需回表

如果查询的字段（name, age）都在索引中，就不会去查询原始表数据，提升查询速度。

📌 7. 定期优化和维护索引

• 查看索引使用情况

  SHOW INDEX FROM users;
  

• 分析 SQL 语句是否使用索引

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

• 清理冗余索引

  DROP INDEX idx_old ON users;
  

• 优化索引存储

  OPTIMIZE TABLE users;
  

✅ 总结

合理设计索引，可以让 MySQL 查询速度飞起