MySQL 索引与查询优化实践

核心原则优先 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` 指标。