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 带来的扫描成本。

四维护与配置优化

五快速排查清单

执行计划是否命中索引（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 是否已优化或加索引。

最新问答