Debian MySQL索引使用技巧

Debian 上用好 MySQL 索引的实用技巧

一 索引类型与适用场景

• 主键索引 PRIMARY KEY：唯一标识行，InnoDB 中主键即聚簇索引，范围查询与点查都很快。
• 唯一索引 UNIQUE：保证列值唯一，适合业务唯一约束与高频等值查询。
• 普通索引：加速 WHERE、JOIN、ORDER BY、GROUP BY 中常用列。
• 复合索引：多列组合，遵循最左前缀原则，常用于多条件过滤、排序与分组。
• 全文索引 FULLTEXT：用于大文本的关键词检索，替代低效的 LIKE ‘%…%’。
• 空间索引 SPATIAL：面向 GIS 场景（如经纬度、几何对象）。
• 存储引擎差异：如 InnoDB 支持事务、行级锁与外键，索引与聚簇特性更契合 OLTP；MyISAM 全文索引能力较早支持，但在事务与并发上弱于 InnoDB。

二 索引设计与创建要点

• 只为高频出现在 WHERE、JOIN、ORDER BY、GROUP BY 的列建索引，并优先选择**区分度高（选择性高）**的列。
• 多条件经常一起出现时，使用复合索引并按“最左前缀”安排列顺序；将过滤性强、选择性高的列放在前面。
• 控制索引数量：索引会加速读，但会拖慢 INSERT/UPDATE/DELETE 并占用空间，定期清理无用索引。
• 对长字符串使用前缀索引节省空间，但要权衡选择性；必要时评估是否需要更长的索引或改用哈希/全文方案。
• 尽量让索引列不为 NULL，并选择合适的数据类型（如用 INT/BIGINT 存 IP，用 DATE/DATETIME 存时间），减少索引体积与比较开销。
• 设计示例：
  • 单列索引：CREATE INDEX idx_email ON users(email);
  • 复合索引：CREATE INDEX idx_user_date ON orders(user_id, order_date);
  • 前缀索引（示例对前 20 字符）：CREATE INDEX idx_name_prefix ON users(name(20));
  • 全文索引（MyISAM/InnoDB 均可用）：CREATE FULLTEXT INDEX idx_content ON articles(content);

三 让索引真正生效的 SQL 写法

• 用 EXPLAIN 检查执行计划，确认是否使用了索引、是否出现 type: ALL（全表扫描）、key: NULL 等风险信号。
• 避免在索引列上做函数或计算（如 WHERE YEAR(created_at) = 2024），否则通常无法使用索引；改写为范围条件（如 created_at >= '2024-01-01' AND created_at < '2025-01-01'）。
• 谨慎使用 OR，它常导致索引失效；可改写为 UNION 或拆分为多个查询。
• 模糊查询尽量改写为前缀匹配（如 LIKE 'abc%'），或使用 FULLTEXT 做关键词检索，避免 LIKE '%abc%'。
• 优先使用覆盖索引（索引包含查询所需全部列），减少回表；配合只查需要的列，避免 SELECT *。
• 分页深翻时，结合索引列的范围条件或记录上次位置，减少 OFFSET 带来的扫描成本。

四 维护与配置优化

• 维护统计与碎片：定期执行 ANALYZE TABLE 更新统计信息，使用 OPTIMIZE TABLE 整理碎片（大表操作请在低峰期进行）。
• 配置内存关键参数（示例为 Debian 上 my.cnf 的 [mysqld] 段）：
  • InnoDB 缓冲池：innodb_buffer_pool_size = 1G（按内存与负载调整）
  • MyISAM 键缓冲：key_buffer_size = 256M
  • 最大连接数：max_connections = 500
  • 排序/临时表内存：sort_buffer_size = 256M、tmp_table_size = 256M
  • 查询缓存：在 MySQL 5.7 及更早版本可按需开启；MySQL 8.0 起已移除，不建议依赖。
• 监控与诊断：开启慢查询日志定位问题 SQL，配合 EXPLAIN 持续验证优化成效。
• 硬件与架构：优先使用 SSD、充足 RAM 与多核 CPU；数据量大时结合分区/分表降低单表扫描压力。

五 快速排查清单

• 执行计划是否命中索引（EXPLAIN 的 key、type、rows）。
• WHERE/JOIN/ORDER/GROUP 是否使用了最左前缀匹配的复合索引。
• 是否存在对索引列的函数/计算、前置通配符 LIKE '%...'、OR 导致索引失效。
• 是否使用了覆盖索引，是否避免了 SELECT *。
• 索引数量是否过多，是否定期 ANALYZE/OPTIMIZE。
• 关键配置（innodb_buffer_pool_size、key_buffer_size、max_connections、sort_buffer_size、tmp_table_size）是否与负载匹配。
• 慢查询日志是否开启，TOP SQL 是否已优化或加索引。