MySQL索引优化指南

最新推荐文章于 2025-06-23 15:51:06 发布

原创最新推荐文章于 2025-06-23 15:51:06 发布 · 1k 阅读

本内容遵循CC 4.0 BY-SA版权协议

标签

#.netcore

4 篇文章

订阅专栏

MySQL索引优化是提升数据库查询性能的关键步骤。以下是一份系统的优化指南，涵盖设计原则、策略、常见误区及维护建议：

选择合适的索引类型：
- B-Tree索引：默认类型，支持范围查询、排序（如=, >, BETWEEN, ORDER BY）。
- 哈希索引：仅支持等值查询（=），适用于内存表。
- 全文索引：用于文本搜索（MATCH AGAINST）。
- 空间索引：优化地理数据查询（如GIS应用）。
高选择性列优先：
- 选择性高的列（如唯一ID）更适合单列索引。例如，email列比gender列更适合建索引。
- 组合索引应将高选择性列放在左侧（如(user_id, status)）。
覆盖索引：
- 确保索引包含查询所需的所有列，避免回表。例如，索引(a, b)覆盖查询SELECT a, b FROM table。
最左前缀原则：
- 组合索引(a, b, c)可优化a、a AND b、a AND b AND c的查询，但无法优化b或c单独使用的查询。

组合索引设计：
- 查询条件顺序：按查询频率和过滤能力排列。例如，高频查询WHERE a=? AND b=?适合索引(a, b)。
- 排序/分组优化：若需ORDER BY b, 可设计索引(a, b)，避免额外排序（Using filesort）。
避免冗余索引：
- 如已有(a, b)，再建(a)则冗余，但(b, a)可能有用。
- 使用工具（如pt-duplicate-key-checker）检测冗余索引。
前缀索引：
- 对长文本列（如VARCHAR(255)）使用前缀索引：ALTER TABLE ADD INDEX (column(20))。
- 平衡前缀长度与选择性，避免过长导致性能下降。
函数索引（MySQL 8.0+）：
- 支持对表达式建索引，如CREATE INDEX idx ON table ((YEAR(date_col)))。

索引列参与运算或函数：
- ❌ WHERE YEAR(date_col) = 2023 → ✅ WHERE date_col BETWEEN '2023-01-01' AND '2023-12-31'。
隐式类型转换：
- ❌ WHERE string_col = 123（若列为字符串类型）→ ✅ WHERE string_col = '123'。
LIKE通配符开头：
- ❌ WHERE name LIKE '%abc' → ✅ WHERE name LIKE 'abc%'（可用索引）。
OR条件导致全表扫描：
- ❌ WHERE a=1 OR b=2 → 可拆分为UNION查询或使用索引合并（index_merge）。

EXPLAIN解读：
- type列：const（主键）、ref（普通索引）、range（范围）、index（全索引扫描）、ALL（全表扫描）。
- Extra列：
  - Using index：覆盖索引。
  - Using where：需回表过滤数据。
  - Using filesort：需额外排序。

慢查询日志：

启用慢查询日志，分析执行时间长的SQL：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- 超过2秒的查询

索引碎片整理：
- 定期优化表：OPTIMIZE TABLE table_name; 或重建索引：ALTER TABLE table_name ENGINE=InnoDB;。
索引统计信息更新：
- 手动更新统计信息：ANALYZE TABLE table_name;。
监控索引使用率：
- 查询未使用的索引：
```
SELECT * FROM sys.schema_unused_indexes;
```

场景1：分页优化：

优化（延迟关联）：

SELECT * FROM table 
JOIN (SELECT id FROM table ORDER BY id LIMIT 100000, 10) AS tmp 
USING (id);

场景2：排序优化：
- 查询：SELECT * FROM orders WHERE user_id=1 ORDER BY create_time DESC;
- 索引：ALTER TABLE orders ADD INDEX (user_id, create_time);

索引优化需结合业务查询模式、数据分布及MySQL特性。通过合理设计索引、分析执行计划、避免失效场景，并定期维护，可显著提升数据库性能。