mysql如何通过日志优化查询性能_mysql性能提升方案

mysql需手动开启慢查询日志并合理配置：启用slow_query_log、设置slow_query_log_file和long_query_time（如0.5秒），可动态set global生效；用mysqldumpslow分析日志，重点关注explain的type/key/rows三列及order by+limit索引使用陷阱。

如何开启并定位慢查询日志

MySQL 默认不记录慢查询，必须手动开启才能拿到真实执行耗时过长的 SQL。关键不是“开了就行”，而是要确保日志能准确捕获你关心的查询——比如排除管理类语句、控制阈值贴近业务响应要求。

• 在 my.cnf 或 mysqld.cnf 中添加：

  slow_query_log = ON<br>slow_query_log_file = /var/log/mysql/mysql-slow.log<br>long_query_time = 0.5

  （long_query_time 单位是秒，设为 0.5 可捕获半秒以上查询，比默认 10 更实用）

• 避免漏记：加上 log_queries_not_using_indexes = ON，但注意这会产生大量日志，仅调试期启用
• 运行中动态开启（无需重启）：

  SET GLOBAL slow_query_log = 'ON';<br>SET GLOBAL long_query_time = 0.5;

  但该设置在服务重启后失效

用 mysqldumpslow 快速分析慢日志

直接打开 mysql-slow.log 看原始内容效率极低，重复 SQL 多、无统计、难排序。MySQL 自带的 mysqldumpslow 是最轻量有效的聚合工具。

• 按执行次数降序看 Top 10：

  mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

• 按总耗时排序（更反映性能瓶颈）：

  mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

• 只看含某个表或字段的慢查询：

  mysqldumpslow -g "user_order" /var/log/mysql/mysql-slow.log

  （-g 支持正则，注意转义特殊字符）

• 注意：mysqldumpslow 会自动归一化 SQL（如把 WHERE id = 123 变成 WHERE id = N），所以看到的是“模板级”统计，不是原始语句

EXPLAIN 输出里真正要盯住的三列

拿到慢 SQL 后，光看 EXPLAIN 不等于看懂瓶颈。很多开发者扫一眼 type=ALL 就加索引，结果无效甚至更慢。重点看三列： type、key、rows，它们共同暴露索引是否被正确使用。

• type 值从优到劣： const ≈ eq_ref > ref > range > index > ALL；出现 ALL 说明全表扫描，但需结合 rows 判断影响面——如果 rows=100 的 ALL 比 rows=100000 的 range 更值得先优化
• key 显示实际使用的索引名；若为 NULL，说明没走索引（可能因类型隐式转换、函数包裹字段、OR 条件未覆盖等）
• rows 是 MySQL 预估扫描行数，不是返回行数；若远大于实际结果集（比如 select count(*) 返回 10 行，但 rows=85000），说明统计信息过期，需执行 ANALYZE table table_name

ORDER BY + LIMIT 为什么有时不走索引

这是线上高频误判点：明明有索引，EXPLAIN 却显示 type=ALL 或 Using filesort，尤其出现在分页场景（如 ORDER BY created_at DESC LIMIT 20, 10）。根本原因不是“索引建错了”，而是 MySQL 无法复用同一个索引同时满足排序和范围过滤。

• 典型陷阱：对 WHERE status = ? ORDER BY created_at DESC 建了 (status) 单列索引，但 created_at 未包含在索引中 → 必须回表排序 → 触发 Using filesort
• 正确做法：建立联合索引 (status, created_at)，且字段顺序必须匹配查询中 WHERE 和 ORDER BY 的依赖关系；若改成 ORDER BY created_at ASC，而索引是 (status, created_at DESC)，部分旧版本 MySQL 仍可能不走索引
• 注意 LIMIT 偏移量过大（如 LIMIT 10000, 20）时，即使走了索引，也要先扫描前 10020 行再丢弃，此时应改用游标分页（WHERE created_at ）

慢查询日志只是起点，真正卡点常藏在索引覆盖是否完整、统计信息是否及时、以及查询写法是否触发了优化器的“放弃治疗”逻辑——这些地方没有银弹，只有结合 EXPLAIN、SHOW INDEX 和真实 rows 预估反复验证。