MySQL索引优化与慢查询日志（超详细Markdown）

一、MySQL 慢查询日志

1.1 什么是慢查询日志

慢查询日志是MySQL内置的日志功能，用于记录执行时间超过指定阈值、执行效率低的SQL语句。常用于排查数据库性能瓶颈、定位慢SQL，是优化数据库最重要的手段之一。

特点：

• 默认关闭，生产环境建议开启

• 记录执行耗时、扫描行数、锁定时间、SQL语句

• 对服务器性能损耗极低

1.2 慢查询核心参数

参数	作用	推荐值
slow_query_log	是否开启慢日志	1（开启）
long_query_time	慢查询阈值（单位秒）	1
slow_query_log_file	日志存放路径	自定义路径
log_queries_not_using_indexes	记录未使用索引的SQL	1（开启）

1.3 临时开启慢查询（重启失效）

适合临时排查线上问题，无需重启MySQL：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
-- 设置超时时间为1秒
SET GLOBAL long_query_time = 1;
-- 记录没有使用索引的SQL
SET GLOBAL log_queries_not_using_indexes = 1;
-- 查看慢日志配置
SHOW VARIABLES LIKE '%slow%';

1.4 永久开启慢查询（修改配置文件）

修改 my.cnf（Linux）/ my.ini（Windows），在 [mysqld] 下添加：

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
log_queries_not_using_indexes = 1

修改后重启MySQL服务生效。

1.5 慢日志字段说明

# Query_time: 2.5  Lock_time: 0.1  Rows_examined: 10000  Rows_sent: 50
SELECT * FROM user;

• Query_time：SQL执行耗时

• Lock_time：锁等待时间

• Rows_examined：磁盘扫描行数（越大越危险）

• Rows_sent：最终返回给客户端行数

1.6 慢日志分析工具

1.6.1 mysqldumpslow（自带）

# 按耗时排序，取前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 按执行次数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

1.6.2 pt-query-digest（推荐）

分析结果更专业，适合生产环境：

pt-query-digest /var/lib/mysql/slow.log

二、MySQL 索引优化

2.1 索引概念

索引是数据库优化查询的数据结构，相当于书籍目录，能大幅降低查询扫描行数，提高查询效率。InnoDB默认使用B+树索引。

2.2 EXPLAIN 分析执行计划

优化SQL之前必须使用EXPLAIN查看执行计划：

EXPLAIN SELECT * FROM user WHERE name = 'test';

2.2.1 核心字段解释

字段	含义	优化标准
type	访问类型	最优：const > ref > range，禁止ALL（全表扫描）
key	实际使用索引	不为NULL表示用到索引
rows	扫描行数	越小越好
Extra	额外信息	避免Using filesort、Using temporary

2.3 索引分类

-- 普通索引
CREATE INDEX idx_name ON user(name);
-- 唯一索引
CREATE UNIQUE INDEX idx_phone ON user(phone);
-- 联合索引（最常用）
CREATE INDEX idx_age_sex ON user(age,sex);

2.4 联合索引最左匹配原则

联合索引(a,b,c)，遵循最左前缀匹配：

✅ 生效：a | a,b | a,b,c

❌ 失效：b | b,c | c

2.5 常见索引失效场景（高频面试）

1. 索引列使用函数、运算

-- 失效
SELECT * FROM user WHERE YEAR(create_time)=2025;
-- 优化
SELECT * FROM user WHERE create_time >= '2025-01-01';

1. 模糊查询%开头

-- 失效
SELECT * FROM user WHERE name LIKE '%张三';
-- 生效
SELECT * FROM user WHERE name LIKE '张三%';

1. 隐式类型转换

-- phone为varchar，传入数字索引失效
SELECT * FROM user WHERE phone = 13800000000;

1. 使用 != <> is not null

2. or 前后有非索引字段

3. order by 字段不合理导致文件排序

2.6 索引优化最佳实践

• where、order by、group by、join 字段优先建索引

• 区分度高的字段放在联合索引前面

• 单表索引不要超过5个

• 禁止select *，尽量使用覆盖索引

• limit大分页要优化（主键分页）

• 频繁更新、删除字段不建议建索引

三、生产环境慢SQL优化流程

1. 开启慢查询日志，捕获慢SQL

2. 使用explain分析执行计划

3. 判断是否缺索引、索引是否失效

4. 优化SQL语句、添加合适索引

5. 再次explain验证优化效果

6. 线上灰度发布，观察监控

四、数据库事务四大特性（ACID）

4.1 事务概述

事务是数据库执行逻辑的最小执行单元，保证一组SQL语句要么全部执行成功，要么全部失败回滚。MySQL InnoDB引擎支持事务，MyISAM不支持事务。事务拥有四大特性，简称ACID。

4.2 四大特性详解

特性	英文	含义解释	通俗理解
原子性	Atomicity	事务是不可分割的最小单元，事务内所有SQL要么全部执行成功，要么全部失败回滚。	要么全成，要么全废
一致性	Consistency	事务执行前后，数据库的数据完整性约束不变，数据状态合法、一致。	数据不会错乱、不会凭空增减
隔离性	Isolation	多个事务并发执行时，事务之间相互隔离、互不干扰。MySQL提供四种事务隔离级别。	各做各的，互不打扰
持久性	Durability	事务提交成功后，数据永久保存到磁盘，宕机、断电数据不会丢失。	提交即永久，不可回退

4.3 事务隔离级别（面试重点）

MySQL 默认隔离级别：可重复读（Repeatable Read）

1. 读未提交（Read Uncommitted）：能读取未提交事务，存在脏读。

2. 读已提交（Read Committed）：只能读取已提交数据，存在不可重复读。

3. 可重复读（Repeatable Read）：同一事务多次读取结果一致，存在幻读。

4. 串行化（Serializable）：最高级别，串行执行，无并发问题、性能最低。

4.4 并发事务问题

• 脏读：一个事务读取到另一个事务未提交的脏数据。

• 不可重复读：同一事务内，两次读取同一条数据不一致。

• 幻读：同一事务内，范围查询出现新增或删除的数据。

五、常用SQL命令汇总

-- 查看慢日志配置
SHOW VARIABLES LIKE '%slow%';
-- 查看表索引
SHOW INDEX FROM user;
-- 删除索引
DROP INDEX idx_name ON user;
-- 查看正在执行的SQL
SHOW PROCESSLIST;

六、总结

• 慢查询日志是数据库调优首要工具，生产必须开启，阈值设置1秒。

• EXPLAIN是索引优化核心，重点观察type、key、Extra字段。

• 索引遵循最左匹配、区分度优先、避免函数操作。

• 大部分慢SQL问题：合理建索引 + 改写SQL即可解决。

（注：文档部分内容可能由 AI 生成）