MySQL 索引与查询优化实践

---

title: MySQL 索引与查询优化实践

keywords:

• MySQL
• 索引优化
• EXPLAIN
• 覆盖索引
• 联合索引
• 查询性能

description: 系统梳理 MySQL 索引与执行计划分析方法，给出可验证的实践示例以稳定提升查询性能。

tags:

• EXPLAIN
• MySQL
• 技术
• 数据库
• 查询性能
• 索引优化
• 联合索引
• 覆盖索引

categories:

• 文章资讯
• 技术教程

---

核心原则

• 优先 InnoDB，主键短且自增，避免随机主键造成页分裂。
• 充分利用左前缀原则设计联合索引。
• 使用 EXPLAIN 观察 type、key、rows、Extra。

示例表与索引

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_users_email (email),
  INDEX idx_users_status_created (status, created_at)
) ENGINE=InnoDB;

覆盖索引与查询

-- 覆盖索引：仅返回 email 列，避免回表
EXPLAIN SELECT email FROM users WHERE email = '[email protected]';
-- 观察 Extra 中常见 "Using index" 表示覆盖索引命中

-- 联合索引左前缀：先按 status，再按 created_at 范围
EXPLAIN SELECT * FROM users WHERE status = 1 AND created_at >= '2024-01-01';

EXPLAIN 关键字段（参数合法且稳定）

• type: 期望 ref、range、const 优于 ALL。
• key: 实际使用的索引名，如 idx_users_status_created。
• rows: 估计扫描行数，越小越好。
• Extra: Using index（覆盖索引）、Using where（过滤条件）、Using filesort（可能需要优化排序）。

常见优化动作

• 模糊查询：LIKE 'prefix%' 可利用索引；LIKE '%suffix' 无法命中索引。
• 排序优化：将排序列并入联合索引，减少 filesort。
• 分页优化：WHERE 加上游标条件（如 id > ?）替代 OFFSET 大量跳页。
• 统计与参数：合理设置 innodb_buffer_pool_size（内存允许范围内最大化），提高命中率。

验证步骤

• 使用 ANALYZE TABLE users; 更新统计信息。
• 对慢查询开启 slow_query_log 并分析 rows_examined 指标。