-- 查看执行计划EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';-- 详细格式EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';-- MySQL 8.0+ 实际执行分析EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN 字段解读
字段
说明
id
查询序号，id 相同执行顺序从上到下
select_type
查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table
访问的表
type
访问类型（性能关键指标）
possible_keys
可能使用的索引
key
实际使用的索引
key_len
索引使用的字节数
ref
与索引比较的列
rows
预估扫描行数
filtered
过滤百分比
Extra
额外信息
type 访问类型（从好到差）
类型
说明
性能
system
表只有一行
最好
const
主键或唯一索引等值查询
极好
eq_ref
JOIN 使用主键或唯一索引
很好
ref
非唯一索引等值查询
好
range
索引范围扫描
较好
index
全索引扫描
一般
ALL
全表扫描
最差
Extra 常见值
值
说明
Using index
覆盖索引，无需回表
Using where
需要在服务器层过滤
Using temporary
使用临时表
Using filesort
需要额外排序
Using index condition
索引条件下推
慢查询日志
-- 查看慢查询设置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 slow_query_log_file = '/var/log/mysql/slow.log';
索引优化
索引设计原则
-- 1. 选择性高的列优先建索引-- 选择性 = 不同值数量 / 总行数SELECT COUNT(DISTINCT email) / COUNT(*) AS email_selectivity, COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivityFROM users;-- email 选择性高，适合建索引-- gender 选择性低，不适合单独建索引-- 2. 频繁查询条件的列CREATE INDEX idx_status ON orders(status);-- 3. JOIN 关联的列CREATE INDEX idx_user_id ON orders(user_id);-- 4. ORDER BY、GROUP BY 的列CREATE INDEX idx_created_at ON orders(created_at);
复合索引设计
-- 遵循最左前缀原则CREATE INDEX idx_dept_salary_age ON employees(department_id, salary, age);-- ✅ 可以使用索引SELECT * FROM employees WHERE department_id = 1;SELECT * FROM employees WHERE department_id = 1 AND salary > 10000;SELECT * FROM employees WHERE department_id = 1 AND salary > 10000 AND age > 25;-- ❌ 无法使用索引（跳过了 department_id）SELECT * FROM employees WHERE salary > 10000;SELECT * FROM employees WHERE age > 25;
覆盖索引
-- 创建覆盖索引CREATE INDEX idx_name_email ON users(name, email);-- 查询只需要索引中的列，无需回表SELECT name, email FROM users WHERE name = '张三';-- EXPLAIN 显示 Using index
索引失效场景
-- 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 是 VARCHARSELECT * 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 name = '张三' OR age = 25; -- 可能失效SELECT * FROM users WHERE name = '张三'UNIONSELECT * FROM users WHERE age = 25; -- 改用 UNION-- 5. 使用 != 或 NOT INSELECT * FROM users WHERE status != 'deleted'; -- 可能失效-- 6. 范围条件后的列无法使用索引-- 索引 (a, b, c)SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 5;-- 只能使用 a 和 b，c 无法使用
查询优化
SELECT 优化
-- 1. 只查询需要的列SELECT id, name, email FROM users; -- ✅SELECT * FROM users; -- ❌-- 2. 避免 SELECT DISTINCT（如果不必要）SELECT DISTINCT department_id FROM employees;-- 考虑是否可以用 GROUP BY 或者其他方式-- 3. 使用 LIMIT 限制结果SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
JOIN 优化
-- 1. 小表驱动大表SELECT * FROM small_table sJOIN large_table l ON s.id = l.small_id;-- 2. 确保 JOIN 列有索引CREATE INDEX idx_user_id ON orders(user_id);-- 3. 避免过多 JOIN（一般不超过 3-4 个）-- 4. 使用 STRAIGHT_JOIN 强制表顺序（谨慎使用）SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
WHERE 优化
-- 1. 避免在 WHERE 中对索引列计算SELECT * FROM orders WHERE YEAR(order_date) = 2024; -- ❌SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'; -- ✅-- 2. 使用 IN 替代多个 ORSELECT * FROM users WHERE status IN ('active', 'pending'); -- ✅SELECT * FROM users WHERE status = 'active' OR status = 'pending'; -- ❌-- 3. 使用 EXISTS 替代 IN（大子查询时）SELECT * FROM users uWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); -- ✅SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); -- 大数据量时较慢
ORDER BY 优化
-- 1. 利用索引排序CREATE INDEX idx_created ON orders(created_at);SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;-- 2. 避免 filesort-- 查看是否有 Using filesortEXPLAIN SELECT * FROM users ORDER BY name;-- 3. 复合索引覆盖排序CREATE INDEX idx_status_created ON orders(status, created_at);SELECT * FROM orders WHERE status = 'completed'ORDER BY created_at DESC; -- 可以使用索引排序
GROUP BY 优化
-- 1. 利用索引分组CREATE INDEX idx_department ON employees(department_id);SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;-- 2. 避免在 GROUP BY 后排序SELECT department_id, COUNT(*) FROM employees GROUP BY department_idORDER BY NULL; -- 如果不需要排序
分页优化
-- 传统分页（偏移量大时性能差）SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000; -- ❌ 需要扫描 100010 行-- 优化1：使用游标分页SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10; -- ✅-- 优化2：延迟关联SELECT o.* FROM orders oJOIN (SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 100000) tON o.id = t.id;-- 优化3：记住上一页最后一条SELECT * FROM orders WHERE id > {last_id} -- 上一页最后一条的 IDORDER BY id LIMIT 10;