数据库优化-EW帮帮网

一、慢 SQL 排查全流程

1. 开启慢查询日志：精准定位问题 SQL

慢查询日志是定位性能问题的首要工具，通过记录执行超时或未使用索引的 SQL，为优化提供依据。

配置步骤：

① 临时启用（生效至服务重启）

sql

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置超时阈值（单位：秒，默认10秒，建议设为1秒）
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询（即使执行时间未超时）
SET GLOBAL log_queries_not_using_indexes = ON;

② 永久生效（修改配置文件）
编辑 MySQL 配置文件（my.cnf/my.ini）：

ini

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log  # 日志存储路径
long_query_time = 1
log_queries_not_using_indexes = 1

③ 查看日志位置

sql

SHOW VARIABLES LIKE 'slow_query_log_file';

分析工具：

mysqldumpslow（内置工具，命令行分析）：

bash

# 按执行时间排序，取最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  
# 按访问次数排序，取最频繁的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

pt-query-digest（Percona Toolkit，功能更强）：

bash

pt-query-digest /var/log/mysql/slow.log > slow_analysis.txt

2. 解析 EXPLAIN 执行计划：洞察查询逻辑

EXPLAIN通过可视化执行步骤，揭示 SQL 是否有效利用索引、扫描行数等关键信息。

核心字段解读：

字段	含义与优化重点
type	连接类型（效率从高到低）：`system` > `const` > `eq_ref` > `ref` > `range` > `index` > `ALL` ** 警惕`ALL`（全表扫描），必须通过索引优化。
key	实际使用的索引，若为`NULL`表示未用索引，需检查`WHERE`/`JOIN`条件是否命中索引。
rows	估算扫描行数，数值越小越好，全表扫描时可能等于表数据量。
Extra	- `Using filesort`：需额外排序（优化：为`ORDER BY`字段加索引） - `Using temporary`：使用临时表（优化：简化`GROUP BY`/`DISTINCT`逻辑） - `Using index`：覆盖索引（理想状态，无需回表）。

示例分析：

sql

EXPLAIN SELECT u.name, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 30 AND o.status = 'paid';

id	type	key	rows	Extra
1	range	idx_age	1000	Using where
1	ref	idx_user_id	5	Using where

结论：

users表通过idx_age索引扫描 1000 行（范围查询），性能可接受；
orders表通过user_id索引关联，但status字段未用索引，建议创建复合索引(user_id, status)。

二、索引失效的六大典型场景

1. 全值匹配时顺序错误（复合索引）

场景：复合索引(a, b, c)，查询WHERE b=1或WHERE a=1 AND c=1。
原因：索引需按顺序匹配，中间字段跳过则后续失效。
修复：查询条件包含索引最左前缀（如a=1 AND b=1）。

2. 范围查询后字段未使用索引

场景：WHERE a > 10 AND b=20，若a是范围查询（>, <, BETWEEN），b字段的索引失效。
原因：MySQL 仅对第一个范围字段使用索引，后续字段无法利用。
修复：对高频查询字段调整顺序，如复合索引(b, a)。

3. 数据类型隐式转换

场景：字段user_id为INT，查询WHERE user_id = '123a'（字符串转数字失败）或WHERE phone='13812345678'但字段定义为INT。
原因：类型不匹配导致索引失效，转为全表扫描。
修复：确保查询条件与字段类型一致（避免字符串加引号查询数字字段）。

4. 索引字段使用函数或表达式

场景：WHERE YEAR(create_time) = 2023 或 WHERE id + 1 = 5。
原因：对索引字段使用函数 / 计算，MySQL 无法命中索引。
修复：改写为WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。

5. OR 条件连接非索引字段

场景：WHERE id=1 OR name='Alice'，若name无索引，则整个条件退化为全表扫描。
原因：OR两侧字段必须都有索引才会生效，否则放弃索引。
修复：为name添加索引，或改用UNION拆分查询。

6. 模糊查询以通配符开头

场景：WHERE name LIKE '%abc' 或 WHERE name LIKE '%abc%'。
原因：左模糊（%开头）无法利用索引，仅右模糊（abc%）可命中。
修复：避免左模糊查询，或使用全文索引（Full-Text Index）。

三、索引高级特性：下推与覆盖

1. 索引下推（Index Condition Pushdown, ICP）

原理：MySQL 5.6 + 引入的优化，在索引遍历过程中，直接过滤掉不满足WHERE条件的记录，减少回表次数。
示例：表users有索引(name, age)，查询WHERE name LIKE 'A%' AND age=20。

无 ICP：通过name索引找到所有以 'A' 开头的记录，回表后再过滤age=20。
有 ICP：在索引层直接判断age=20，仅对符合条件的记录回表，减少 I/O。
开启方式：默认启用，可通过SHOW VARIABLES LIKE 'optimizer_switch'查看index_condition_pushdown状态。

2. 索引覆盖（Covering Index）

定义：查询所需的所有字段都包含在索引中，无需回表查询原数据，是索引优化的终极目标。
示例：表orders有索引(user_id, status, order_time)，查询SELECT status, order_time FROM orders WHERE user_id=1。

覆盖索引：直接通过索引获取数据，效率极高（Extra显示Using index）。
非覆盖索引：若索引缺少order_time，需回表查询完整行数据。
设计原则：为高频查询语句创建包含所有 SELECT 字段的复合索引，避免SELECT *。

四、最佳实践总结

慢 SQL 排查闭环：
开启慢日志 → 分析日志定位问题 SQL → 用EXPLAIN剖析执行计划 → 针对性优化索引或查询语句。
索引设计三原则：
- 最左前缀匹配：复合索引按查询条件顺序创建；
- 覆盖索引优先：减少回表，提升查询速度；
- 避免过度索引：索引过多会影响写入性能（插入 / 更新 / 删除）。
工具链推荐：
- 日志分析：pt-query-digest（全面统计）、mysqldumpslow（快速预览）；
- 可视化：MySQL Workbench（图形化执行计划）、Navicat（索引管理）。

通过系统化排查与索引优化，可显著提升 MySQL 查询性能，避免因慢 SQL 导致的系统瓶颈。

编辑

在排查慢 SQL 时，如何确定慢查询的阈值？

如何选择合适的索引来优化查询性能？

分享一些排查和优化 MySQL 慢 SQL 的实际案例

数据库优化