MySQL 索引优化：慢查询分析与索引设计原则

# MySQL 索引优化：慢查询分析与索引设计原则 ## 一、开启慢查询日志 ```sql -- 查看当前配置 SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询日志 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 超过 1 秒记录 SET GLOBAL log_queries_not_using_indexes = ON; ``` ## 二、EXPLAIN 分析 ```sql EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' AND created_at > '2024-01-01' ORDER BY created_at DESC LIMIT 20; ``` 重点关注： - `type`: ALL(全表扫描) -> index -> range -> ref -> const - `key`: 实际使用的索引 - `rows`: 预估扫描行数 - `Extra`: Using filesort(需优化), Using index(覆盖索引,好) ## 三、复合索引与最左前缀原则 ```sql -- 创建复合索引 ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at); -- 能命中索引的查询 SELECT * FROM orders WHERE user_id = 100; -- 命中 SELECT * FROM orders WHERE user_id = 100 AND status = 'paid'; -- 命中 SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' AND created_at > '2024-01-01'; -- 命中 -- 无法命中索引 SELECT * FROM orders WHERE status = 'paid'; -- 跳过了 user_id SELECT * FROM orders WHERE created_at > '2024-01-01'; -- 跳过了前两列 ``` ## 四、覆盖索引 ```sql -- 如果查询只需要 id 和 created_at，可以用覆盖索引避免回表 ALTER TABLE orders ADD INDEX idx_cover (user_id, status, created_at, id); -- Extra 显示 Using index，无需回表 EXPLAIN SELECT id, created_at FROM orders WHERE user_id = 100 AND status = 'paid'; ``` ## 五、常见索引失效场景 ```sql -- 1. 对索引列使用函数 SELECT * FROM users WHERE YEAR(created_at) = 2024; -- 索引失效 SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; -- 索引有效 -- 2. 隐式类型转换 SELECT * FROM users WHERE phone = 13800138000; -- phone 是 varchar，索引失效 SELECT * FROM users WHERE phone = '13800138000'; -- 正确 -- 3. LIKE 左模糊 SELECT * FROM users WHERE name LIKE '%张'; -- 索引失效 SELECT * FROM users WHERE name LIKE '张%'; -- 索引有效 -- 4. OR 条件 SELECT * FROM users WHERE id = 1 OR name = '张三'; -- 若 name 无索引则全表扫描 ``` ## 六、索引设计原则 1. 区分度高的列放前面（如 user_id 优于 status） 2. 排序和分组列纳入索引避免 filesort 3. 单表索引数控制在 5 个以内 4. 长字符串考虑前缀索引：`ALTER TABLE t ADD INDEX idx_name (name(10))`