04.MySQL索引优化与慢查询日志和事务四大特性
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：
-- 开启慢查询日志SETGLOBALslow_query_log=1;-- 设置超时时间为1秒SETGLOBALlong_query_time=1;-- 记录没有使用索引的SQLSETGLOBALlog_queries_not_using_indexes=1;-- 查看慢日志配置SHOWVARIABLESLIKE'%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: 50SELECT*FROMuser;Query_time：SQL执行耗时
Lock_time：锁等待时间
Rows_examined：磁盘扫描行数（越大越危险）
Rows_sent：最终返回给客户端行数
1.6 慢日志分析工具
1.6.1 mysqldumpslow（自带）
# 按耗时排序，取前10条mysqldumpslow-st-t10/var/lib/mysql/slow.log# 按执行次数排序mysqldumpslow-sc-t10/var/lib/mysql/slow.log1.6.2 pt-query-digest（推荐）
分析结果更专业，适合生产环境：
pt-query-digest /var/lib/mysql/slow.log二、MySQL 索引优化
2.1 索引概念
索引是数据库优化查询的数据结构，相当于书籍目录，能大幅降低查询扫描行数，提高查询效率。InnoDB默认使用B+树索引。
2.2 EXPLAIN 分析执行计划
优化SQL之前必须使用EXPLAIN查看执行计划：
EXPLAINSELECT*FROMuserWHEREname='test';2.2.1 核心字段解释
| 字段 | 含义 | 优化标准 |
|---|---|---|
| type | 访问类型 | 最优：const > ref > range，禁止ALL（全表扫描） |
| key | 实际使用索引 | 不为NULL表示用到索引 |
| rows | 扫描行数 | 越小越好 |
| Extra | 额外信息 | 避免Using filesort、Using temporary |
2.3 索引分类
-- 普通索引CREATEINDEXidx_nameONuser(name);-- 唯一索引CREATEUNIQUEINDEXidx_phoneONuser(phone);-- 联合索引（最常用）CREATEINDEXidx_age_sexONuser(age,sex);2.4 联合索引最左匹配原则
联合索引(a,b,c)，遵循最左前缀匹配：
✅ 生效：a | a,b | a,b,c
❌ 失效：b | b,c | c
2.5 常见索引失效场景（高频面试）
- 索引列使用函数、运算
-- 失效SELECT*FROMuserWHEREYEAR(create_time)=2025;-- 优化SELECT*FROMuserWHEREcreate_time>='2025-01-01';- 模糊查询%开头
-- 失效SELECT*FROMuserWHEREnameLIKE'%张三';-- 生效SELECT*FROMuserWHEREnameLIKE'张三%';- 隐式类型转换
-- phone为varchar，传入数字索引失效SELECT*FROMuserWHEREphone=13800000000;使用 != <> is not null
or 前后有非索引字段
order by 字段不合理导致文件排序
2.6 索引优化最佳实践
where、order by、group by、join 字段优先建索引
区分度高的字段放在联合索引前面
单表索引不要超过5个
禁止select *，尽量使用覆盖索引
limit大分页要优化（主键分页）
频繁更新、删除字段不建议建索引
三、生产环境慢SQL优化流程
开启慢查询日志，捕获慢SQL
使用explain分析执行计划
判断是否缺索引、索引是否失效
优化SQL语句、添加合适索引
再次explain验证优化效果
线上灰度发布，观察监控
四、数据库事务四大特性（ACID）
4.1 事务概述
事务是数据库执行逻辑的最小执行单元，保证一组SQL语句要么全部执行成功，要么全部失败回滚。MySQL InnoDB引擎支持事务，MyISAM不支持事务。事务拥有四大特性，简称ACID。
4.2 四大特性详解
| 特性 | 英文 | 含义解释 | 通俗理解 |
|---|---|---|---|
| 原子性 | Atomicity | 事务是不可分割的最小单元，事务内所有SQL要么全部执行成功，要么全部失败回滚。 | 要么全成，要么全废 |
| 一致性 | Consistency | 事务执行前后，数据库的数据完整性约束不变，数据状态合法、一致。 | 数据不会错乱、不会凭空增减 |
| 隔离性 | Isolation | 多个事务并发执行时，事务之间相互隔离、互不干扰。MySQL提供四种事务隔离级别。 | 各做各的，互不打扰 |
| 持久性 | Durability | 事务提交成功后，数据永久保存到磁盘，宕机、断电数据不会丢失。 | 提交即永久，不可回退 |
4.3 事务隔离级别（面试重点）
MySQL 默认隔离级别：可重复读（Repeatable Read）
读未提交（Read Uncommitted）：能读取未提交事务，存在脏读。
读已提交（Read Committed）：只能读取已提交数据，存在不可重复读。
可重复读（Repeatable Read）：同一事务多次读取结果一致，存在幻读。
串行化（Serializable）：最高级别，串行执行，无并发问题、性能最低。
4.4 并发事务问题
脏读：一个事务读取到另一个事务未提交的脏数据。
不可重复读：同一事务内，两次读取同一条数据不一致。
幻读：同一事务内，范围查询出现新增或删除的数据。
五、常用SQL命令汇总
-- 查看慢日志配置SHOWVARIABLESLIKE'%slow%';-- 查看表索引SHOWINDEXFROMuser;-- 删除索引DROPINDEXidx_nameONuser;-- 查看正在执行的SQLSHOWPROCESSLIST;六、总结
慢查询日志是数据库调优首要工具，生产必须开启，阈值设置1秒。
EXPLAIN是索引优化核心，重点观察type、key、Extra字段。
索引遵循最左匹配、区分度优先、避免函数操作。
大部分慢SQL问题：合理建索引 + 改写SQL即可解决。
（注：文档部分内容可能由 AI 生成）