MySQL 索引、事务与状态查询

本文合并自：07-状态查询与索引优化.md、04-事务处理与视图.md、2-5-MySQL索引事务与备份恢复.md

适用环境：CentOS 7 / RHEL 7 + MySQL 5.7 / 8.0、MariaDB 10.x（10.1 ~ 10.5） 内容范围：运行状态查询与监控、慢查询定位、索引原理与管理、执行计划与查询优化、事务与锁、视图与存储过程。 范围边界：备份与恢复（mysqldump、物理备份、binlog 时间点恢复、备份策略）见 11-05-备份与恢复.md；主从复制与读写分离见 11-06-主从复制与读写分离.md；约束（PRIMARY KEY / FOREIGN KEY / CHECK）与外连接语法见表设计与多表查询相关文档。

章节	主题	关键内容
一	状态查询与监控	SHOW STATUS、SHOW VARIABLES、SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS、慢查询日志、profiling
二	索引原理与管理	B+Tree、聚簇索引与回表、索引分类、索引 DDL、最左前缀
三	执行计划与查询优化	EXPLAIN 逐字段、索引失效场景、优化实战、统计信息
四	事务与锁	ACID、隔离级别与复现实验、MVCC、行锁/间隙锁、死锁
五	视图、存储过程与练习	视图、存储过程与函数、优化清单、排错清单、练习题

---

一、状态查询与监控

1.1 SHOW STATUS 与 SHOW GLOBAL STATUS

SHOW STATUS 查看 MySQL 所有运行状态变量，是判断实例健康度的第一手数据。

-- 查看所有运行状态
show status;

-- 当前终端（会话）启动到执行命令时各 SQL 命令的执行次数，等价于 show session status
show status like 'Com%';

-- 数据库历史操作总次数（自最近一次启动起，全局累计）
show global status like 'Com%';

-- 当前会话 select 执行次数
show status like 'Com_select%';

-- 全局 select 执行次数
show global status like 'Com_select%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+

区别务必记牢：不带 GLOBAL 是当前会话从连接建立至今的计数，带 GLOBAL 是整个实例从最近一次启动至今的累计计数。做压测或对比时要用 GLOBAL，两次采样求差值再除以时间间隔，才是「每秒多少次」。

1.1.1 连接与线程类

状态变量	含义	判读要点
Connections	自最近一次启动以来的累计连接尝试次数（含失败）	只增不减，用于算连接速率
Threads_connected	当前已建立的连接数	接近 max_connections 就要报警
Threads_running	当前正在执行 SQL（非 Sleep）的线程数	最能反映真实压力，正常个位数；持续 > CPU 核数说明堆积
Threads_created	累计创建过的线程数	增长快说明线程缓存不够，调大 thread_cache_size
Threads_cached	线程缓存中的空闲线程数	与上一项配合看命中率
Max_used_connections	启动以来同时连接数的峰值	用来给 max_connections 定容量：峰值 / max_connections 建议 < 85%
Aborted_connects	连接建立失败的次数（认证失败、权限不足、握手超时）	突增说明有密码错误、扫描器或网络抖动
Aborted_clients	已建立但被异常中断的连接数（客户端未正常关闭、超过 wait_timeout）	大量出现多为应用连接池未正常释放
Uptime	MySQL 最近一次启动至今的运行时间（秒）	突然变小 = 实例被重启过

show status like 'Connections';
show status like 'Uptime';
show global status like 'Threads_%';
show global status like 'Max_used_connections';
show global status like 'Aborted_%';

容量估算：

连接使用率 = Threads_connected / max_connections
峰值使用率 = Max_used_connections / max_connections   -- 建议 < 85%
平均每秒新建连接 = Connections 的两次采样差值 / 间隔秒数

1.1.2 SQL 执行量类（Com_*）

状态变量	含义
Com_select	SELECT 执行次数
Com_insert	INSERT 执行次数
Com_update	UPDATE 执行次数
Com_delete	DELETE 执行次数
Com_commit / Com_rollback	提交 / 回滚次数，Com_rollback 占比高说明业务频繁回滚或死锁多
Questions	客户端发来的语句总数（不含存储过程内部语句）
Queries	语句总数（包含存储过程内部执行的语句）

-- 读写比例判断：读多写少可上读写分离，写多要考虑分库分表
show global status where Variable_name in
  ('Com_select','Com_insert','Com_update','Com_delete');

QPS ≈ (Questions 差值) / 间隔秒数
TPS ≈ (Com_commit + Com_rollback 的差值) / 间隔秒数
读写比 = Com_select : (Com_insert + Com_update + Com_delete)

1.1.3 InnoDB 缓冲池类与命中率计算

状态变量	含义
Innodb_buffer_pool_read_requests	从缓冲池逻辑读的请求次数（无论命中与否）
Innodb_buffer_pool_reads	缓冲池未命中，必须从磁盘读的次数
Innodb_buffer_pool_pages_total	缓冲池总页数
Innodb_buffer_pool_pages_free	空闲页数，长期为 0 说明缓冲池偏小
Innodb_buffer_pool_pages_dirty	脏页数，过高说明刷盘跟不上写入
Innodb_row_lock_waits	行锁等待次数
Innodb_row_lock_time_avg	行锁平均等待毫秒数
Innodb_row_lock_current_waits	当前正在等待行锁的数量，非 0 就说明现在有锁等待

命中率计算公式：

缓冲池命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100%

show global status like 'Innodb_buffer_pool_read%';

-- 直接算出命中率
SELECT
  (1 - (
    SELECT VARIABLE_VALUE FROM information_schema.global_status
     WHERE VARIABLE_NAME='Innodb_buffer_pool_reads')
   /(
    SELECT VARIABLE_VALUE FROM information_schema.global_status
     WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')
  ) * 100 AS hit_ratio_pct;

判读标准：OLTP 系统命中率应 ≥ 99%。低于 95% 说明 innodb_buffer_pool_size 太小（经验值：独立数据库服务器给物理内存的 50%~70%），或存在大量全表扫描把热数据挤出了缓冲池。

1.1.4 慢查询与锁等待类

状态变量	含义
Slow_queries	慢查询总次数（超过 long_query_time 的语句数，默认阈值 10 秒）
Table_locks_immediate	立即获得表锁的次数
Table_locks_waited	等待表锁的次数，比值高说明表锁争用严重（MyISAM 表尤甚，应改 InnoDB）
Created_tmp_tables	创建内存临时表的次数
Created_tmp_disk_tables	创建磁盘临时表的次数，占比高需调大 tmp_table_size/max_heap_table_size 或优化 SQL
Sort_merge_passes	排序合并次数，高说明 sort_buffer_size 不足或缺排序索引
Select_full_join	没有使用索引的 JOIN 次数，非 0 就必须查（被驱动表关联列缺索引）
Select_scan	全表扫描的 SELECT 次数

show status like 'Slow_queries';
show global status like 'Table_locks%';
show global status like 'Created_tmp%';
show global status like 'Select_full_join';
show global status like 'Innodb_row_lock%';

表锁争用率     = Table_locks_waited / (Table_locks_immediate + Table_locks_waited)   -- 应 < 1%
磁盘临时表占比 = Created_tmp_disk_tables / Created_tmp_tables                        -- 应 < 10%

1.1.5 Handlerread* 与索引使用判断

Handler_read_* 是判断索引是否被有效使用的最直接指标，它统计存储引擎层的读行方式。

状态变量	含义	判读
Handler_read_key	通过索引读到一行的次数	越高越好，说明索引在起作用
Handler_read_first	读取索引第一条记录的次数	多为 MIN() 或全索引扫描的起点
Handler_read_next	按索引顺序读下一行的次数	范围扫描/索引扫描时增长，正常
Handler_read_prev	按索引顺序读上一行的次数	ORDER BY ... DESC 时出现
Handler_read_rnd	根据固定位置读一行的次数	高 = 大量排序后回表取行，考虑覆盖索引
Handler_read_rnd_next	读取数据文件中下一行的次数	高就是全表扫描多，最需要警惕的指标

show global status like 'Handler_read%';

索引健康度粗判：
  Handler_read_key 高、Handler_read_rnd_next 低      → 索引使用良好
  Handler_read_rnd_next 远大于 Handler_read_key      → 大量全表扫描，去查慢日志补索引
  Handler_read_rnd 很高                              → 排序/回表过多，考虑覆盖索引

1.2 SHOW VARIABLES 与参数查看修改

SHOW STATUS 看的是运行结果（只读计数器），SHOW VARIABLES 看的是配置参数（可调）。

-- 查看全部参数（几百个，一般都带 like 过滤）
SHOW VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW SESSION VARIABLES LIKE 'sort_buffer_size';

-- 三种等价的单值查询写法
SHOW VARIABLES LIKE 'long_query_time';
SELECT @@long_query_time;            -- 会话值
SELECT @@global.long_query_time;     -- 全局值

-- 从系统表查询，可用 WHERE / ORDER BY
SELECT * FROM information_schema.global_variables WHERE VARIABLE_NAME LIKE 'innodb_buffer%';

参数的三个作用域：

作用域	修改方式	生效范围	重启后
会话级（SESSION）	SET 参数=值 或 SET SESSION 参数=值	只影响当前连接	失效
全局级（GLOBAL）	SET GLOBAL 参数=值	影响之后新建立的连接，已有连接不变	失效
持久化	写入 /etc/my.cnf 的 [mysqld] 段后重启；或 MySQL 8.0 的 SET PERSIST	全局且永久	保留

SET sort_buffer_size = 4*1024*1024;          -- 仅当前会话
SET GLOBAL max_connections = 1000;            -- 全局，新连接生效，重启丢失
SET PERSIST max_connections = 1000;           -- MySQL 8.0+：写入 mysqld-auto.cnf，重启保留
SET PERSIST_ONLY innodb_buffer_pool_size = 8589934592;  -- 8.0+：只写文件不立即生效（只读参数用）

# /etc/my.cnf —— 唯一可靠的持久化手段（MySQL 5.7 / MariaDB 必须用这种）
[mysqld]
max_connections=1000
innodb_buffer_pool_size=8G
long_query_time=1

关键区别与常见事故：

1. SET GLOBAL 不会改变当前已打开会话的值，也不写配置文件，重启即回退。线上临时救火用 SET GLOBAL，同时必须同步改 my.cnf，否则下次重启问题重现。
2. 部分参数是只读参数（如 innodb_buffer_pool_instances、datadir、log_bin），只能改配置文件后重启。
3. long_query_time 这类参数的修改只对新建立的连接生效，当前会话查到的仍是旧值，需重连验证。
4. 单位陷阱：innodb_buffer_pool_size 以字节为单位，写 8G 只能在配置文件里，SET GLOBAL 时要写完整字节数。

1.3 SHOW PROCESSLIST 与 information_schema.processlist

慢日志是事后分析，PROCESSLIST 是现场抓人——数据库突然卡住时第一条要执行的命令。

SHOW PROCESSLIST;             -- Info 列的 SQL 被截断到 100 字符
SHOW FULL PROCESSLIST;        -- 显示完整 SQL，排查时用这个

-- 用 information_schema 做条件过滤，比 SHOW 灵活
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

-- 统计每个来源 IP 的连接数，定位是哪台应用机把连接打满的
SELECT SUBSTRING_INDEX(host,':',1) AS ip, COUNT(*) AS conn_cnt
FROM information_schema.processlist
GROUP BY ip ORDER BY conn_cnt DESC;

-- 杀掉问题会话（id 取自上面的 Id 列）
KILL 12345;          -- 终止连接（连接一起断开）
KILL QUERY 12345;    -- 只终止当前语句，保留连接

关键字段：

字段	含义
Id	连接 ID，KILL 时使用，与慢日志中的 Id: 对应
User / Host	连接的用户与来源地址（ip:port）
db	当前使用的库
Command	Query（正在执行）、Sleep（空闲等待）、Binlog Dump（主库发 binlog 给从库）、Connect（从库连接主库）
Time	当前状态已持续的秒数，排查时先按此列倒序
State	线程状态，最有诊断价值，见下表
Info	正在执行的 SQL（SHOW FULL PROCESSLIST 才完整）

State 列常见取值：

State	含义与处置
Sending data	正在读取并发送行数据。慢查询最常见的状态，通常意味着扫描行数巨大 → 去 EXPLAIN 补索引
Sorting result / Sorting for group	正在排序，对应 Using filesort → 给 ORDER BY 列建索引
Copying to tmp table / Copying to tmp table on disk	正在建临时表，后者已落磁盘，最慢 → 优化 GROUP BY/DISTINCT，调大 tmp_table_size
Locked / Waiting for table level lock	等表锁（MyISAM 常见）
Waiting for table metadata lock	等元数据锁（MDL）：有 DDL 被长事务/长查询阻塞，后续请求全部排队 → 查 innodb_trx 找长事务并处理
statistics	优化器在收集统计信息选执行计划，长时间停留说明表统计信息或索引过多
Creating sort index	用内部临时表做排序
Sending to client / Writing to net	网络回传中，结果集过大或客户端消费慢
Opening tables / closing tables	打开/关闭表，卡住多为 table_open_cache 不足或 MDL
Free lock、cleaning up	结束阶段，正常
User sleep	执行了 SLEEP() 函数

排查套路：

数据库卡住
  → SHOW FULL PROCESSLIST 按 Time 倒序
      → 大量 Sleep 且连接数打满 → 应用连接池未释放 / 调大 max_connections
      → 某条 Query 的 Time 极长 → 复制其 SQL 去 EXPLAIN，补索引；必要时 KILL QUERY
      → 大量 Waiting for table metadata lock → 有未提交的长事务或 DDL 被阻塞（见第四章）
      → 大量 Sending data 且同一条 SQL → 缺索引导致的雪崩

KILL 的注意点：KILL QUERY 只中断语句，事务仍然保持并继续持有锁，应用需要显式 ROLLBACK；KILL 连接会触发事务回滚，回滚大事务本身很慢（可能比执行还慢），期间锁不释放，切勿反复 KILL。

1.4 SHOW ENGINE INNODB STATUS 关键段解读

SHOW ENGINE INNODB STATUS\G

输出很长，按段落速查：

段落	关键内容	用途
BACKGROUND THREAD	主线程循环次数、刷盘活动	判断后台是否忙
SEMAPHORES	信号量等待、Mutex spin waits	大量等待说明内部争用严重（并发过高）
LATEST DETECTED DEADLOCK	最近一次死锁的两个事务、持有锁、等待锁、被回滚方	死锁分析唯一入口（详见 4.7）
LATEST FOREIGN KEY ERROR	最近一次外键错误的详细原因	排查 errno 150 / 1452
TRANSACTIONS	事务列表、ACTIVE n sec、LOCK WAIT、undo log entries	找长事务、找正在等锁的事务
FILE I/O	各 IO 线程状态、pending reads/writes、fsyncs/s	判断磁盘是否是瓶颈
INSERT BUFFER AND ADAPTIVE HASH INDEX	change buffer 与自适应哈希的使用量	二级索引写入优化效果
LOG	Log sequence number（LSN）、Last checkpoint at、pending log flushes	LSN 与 checkpoint 差值过大 = 刷盘跟不上
BUFFER POOL AND MEMORY	总页数、free、dirty、Buffer pool hit rate	直接给出命中率，如 1000 / 1000 表示 100%
ROW OPERATIONS	每秒 insert/update/delete/read 行数、n row lock(s)	实时吞吐

重点关注的三处：

---TRANSACTION 3512, ACTIVE 1852 sec            <- ACTIVE 秒数很大 = 长事务，必须处理
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)   <- 正在等锁
mysql tables in use 1, locked 1

Buffer pool hit rate 999 / 1000, young-making rate ...      <- 命中率 99.9%
Modified db pages  12345                                    <- 脏页数

Log sequence number 108 3456789
Last checkpoint at  108 1234567              <- 与 LSN 差距过大说明 redo 刷盘压力大

1.5 慢查询日志

重点优化对象是慢查询，默认阈值为 10 秒（long_query_time）。

show variables like 'long_query_time';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

1.5.1 临时开启（重启失效，用于线上临时排查）

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;                -- 单位秒，支持小数如 0.5 / 0.2
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未用索引的语句，日志量可能暴涨，慎开

注意：long_query_time 的修改只对新建立的连接生效，当前会话看到的仍是旧值，需重连验证。

1.5.2 永久开启：配置 /etc/my.cnf

最简配置：

slow_query_log_file=/var/lib/mysql/slowquery.log
slow_query_log=on
long_query_time=1
log-queries-not-using-indexes

生产推荐配置：

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1
log_queries_not_using_indexes=0
min_examined_row_limit=100          # 扫描行数少于 100 的不记录，过滤噪音
log_slow_admin_statements=1         # 记录 ALTER 等管理语句
# MariaDB 专有：记录慢查询的执行计划，定位更快
# log_slow_verbosity=query_plan,explain

参数说明：

参数	说明
slow_query_log	是否开启慢查询日志
slow_query_log_file	慢查询日志路径
long_query_time	慢查询阈值（秒），超过该时间即为慢查询。支持小数，如 0.2
log-queries-not-using-indexes	同时记录未使用索引的查询（等价 log_queries_not_using_indexes）
min_examined_row_limit	扫描行数低于该值的语句不记录，过滤小表噪音
log_slow_admin_statements	是否记录 ALTER、ANALYZE 等管理语句
log_output	FILE（默认）/ TABLE（写入 mysql.slow_log 表）/ FILE,TABLE

mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql
systemctl restart mysqld     # MariaDB 为 systemctl restart mariadb

CentOS 7 上若日志目录不在 /var/lib/mysql，需检查 SELinux 上下文，否则启动失败： semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?" && restorecon -Rv /var/log/mysql

查看慢查询配置：

show variables like 'slow%';

+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_launch_time    | 2                        |
| slow_query_log      | OFF                      |
| slow_query_log_file | /dta/mysql/slowquery.log |
+---------------------+--------------------------+

show variables like 'long%';

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

1.5.3 慢查询验证与日志内容

制造一条慢查询（emp 表 180 万行）：

select * from test.emp;
-- 1800000 rows in set (2.11 sec)

查看慢查询日志：

cat /var/lib/mysql/slowquery.log

SET timestamp=1537887627;
select * from emp;

使用了等值条件（如索引列）的查询很快，不会记录到慢查询日志：

select * from emp where empno='123456';

生产环境完整的单条慢日志格式：

# Time: 2026-08-22T10:15:32.123456Z
# User@Host: app[app] @ 10.0.0.61 []  Id: 12345
# Query_time: 3.521847  Lock_time: 0.000135  Rows_sent: 10  Rows_examined: 2500000
SET timestamp=1755857732;
SELECT empno, ename, sal FROM emp WHERE deptno=20 ORDER BY sal DESC LIMIT 10;

字段	含义	判读
Query_time	执行耗时	排序的主要依据
Lock_time	等锁耗时	占比高说明是锁竞争问题，不是索引问题（见第四章）
Rows_sent	返回给客户端的行数
Rows_examined	扫描的行数	Rows_examined / Rows_sent 比值越大，索引效率越差；上例 25 万:1 明显缺索引
Id	连接 ID，可与 PROCESSLIST 的 Id 对应

1.5.4 mysqldumpslow 分析慢查询日志

mysqldumpslow 是 MySQL 自带工具，会把同类 SQL 按模板归并（数值抽象成 N、字符串抽象成 S）后统计。

mysqldumpslow /var/lib/mysql/slowquery.log

Reading mysql slow query log from slowquery.log
Count: 1  Time=2.11s (2s)  Lock=0.00s (0s)  Rows=1800000.0 (1800000), root[root]@localhost
  select * from emp

常用参数：

参数	说明
-s	排序方式：c 记录次数、t 时间、l 锁时间、r 返回记录数；加 a 前缀表示按平均值（at、al、ar），加 a 也常用于倒序表达
-s t / at	按总耗时 / 平均耗时排序
-s c	按执行次数排序
-s l / al	按总锁时间 / 平均锁时间排序
-s r / ar	按返回行数 / 平均返回行数排序
-t	top N，只显示前多少条
-g	正则匹配过滤，大小写不敏感
-a	不把数值/字符串抽象成 N/S（默认会抽象以便归并）

示例：

# 记录次数最多的 10 条 SQL（高频小慢查询往往比单次巨慢更值得优化）
mysqldumpslow -s c -t 10 /var/lib/mysql/slowquery.log

# 返回记录集最多的 10 个查询
mysqldumpslow -s r -t 10 /var/lib/mysql/slowquery.log

# 按累计耗时倒序取前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 按时间排序前 10 条中含 left join 的查询
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slowquery.log

# 按平均耗时；只看含 emp 表的语句
mysqldumpslow -s at -t 10 -g "emp" /var/log/mysql/mysql-slow.log

更强的替代品是 percona-toolkit 的 pt-query-digest，输出包含耗时分布（95 分位）、样本 SQL、Rows examined 统计，生产首选：

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

# 只分析最近 12 小时
pt-query-digest --since=12h /var/log/mysql/mysql-slow.log

# 分析结果写入数据库表，便于做趋势对比
pt-query-digest --review h=127.0.0.1,D=slow_db,t=global_query_review \
  /var/log/mysql/mysql-slow.log

开启慢查询日志会在一定程度上占用 CPU 与磁盘 IO，影响性能，但可阶段性开启以定位性能瓶颈；生产建议长期开启并把 long_query_time 设为 1 秒，成本可接受。

1.6 profiling 分析查询资源消耗

慢查询日志可定位哪些 SQL 效率低下，EXPLAIN 可查看 SQL 的执行计划和索引使用，profiling 则能提供更准确的SQL 执行各阶段资源消耗信息。

1.6.1 开启 profiling

profiling 默认关闭：

select @@profiling;

+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

开启（会话级）：

set profiling=1;

1.6.2 查看 profiles

执行测试查询后查看简要信息：

select * from dept;
show profiles\G

*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00024325
   Query: select * from dept

1.6.3 查看某条查询的详细执行过程

show profile for query 4;

-- 还可以查看 CPU、IO、内存等细分维度
show profile cpu, block io for query 4;
show profile all for query 4\G

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000057 |
| checking permissions | 0.000058 |
| Opening tables       | 0.000064 |
| init                 | 0.000020 |
| System lock          | 0.000009 |
| optimizing           | 0.000003 |
| statistics           | 0.000011 |
| preparing            | 0.000009 |
| executing            | 0.000002 |
| Sending data         | 2.433287 |
| end                  | 0.000014 |
| query end            | 0.000010 |
| closing tables       | 0.000010 |
| freeing items        | 0.004363 |
| logging slow query   | 0.003992 |
| cleaning up          | 0.000022 |
+----------------------+----------+

各阶段含义：

阶段	含义
starting	启动
checking permissions	检查权限
Opening tables	打开表
init	初始化
System lock	系统锁定
optimizing	优化
statistics	统计（收集统计信息、选执行计划）
preparing	准备
executing	执行
Sending data	发送数据（实际包含读取与处理行数据，通常是耗时大头）
end	结束
query end	查询结束
closing tables	关闭表
freeing items	释放项目内存
logging slow query	写入慢查询日志
cleaning up	清理

profiling 在 MySQL 5.7 起标记为 deprecated，官方推荐改用 performance_schema： SELECT * FROM performance_schema.events_stages_history_long; 但 SHOW PROFILE 至今仍可用，教学与临时排查依然方便。

1.7 通用查询日志与错误日志

日志	参数	记录内容	生产建议
错误日志	log_error=/var/log/mysql/error.log	启动/停止信息、崩溃堆栈、复制错误、InnoDB 恢复过程	必须开启，出问题第一个看
慢查询日志	slow_query_log 等，见 1.5	超过阈值的语句	开启，阈值 1 秒
通用查询日志	general_log、general_log_file	所有到达服务器的语句（含 SELECT）	默认关闭，仅短时排查用，量极大
binlog	log_bin	所有变更（用于复制与时间点恢复）	开启，详见 11-05-备份与恢复.md

-- 通用查询日志：临时开启抓取全部 SQL，抓完立刻关闭
SHOW VARIABLES LIKE 'general_log%';
SET GLOBAL general_log = ON;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- 也可写入表，便于用 SQL 检索
SET GLOBAL log_output = 'TABLE';
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 20;
SET GLOBAL general_log = OFF;      -- 用完必须关，否则磁盘几小时就被写满

-- 错误日志位置与告警级别
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_error_verbosity';   -- 8.0：1 只错误 / 2 错误+警告 / 3 全部

# 错误日志常用排查手法
tail -f /var/log/mysql/error.log
grep -iE 'error|warn|deadlock|crash' /var/log/mysql/error.log | tail -50

---

二、索引原理与管理

2.1 为什么需要索引

假设 emp 表有 1000 万行，执行：

SELECT * FROM emp WHERE ename = 'wangwu';

有无索引	执行方式	需要读取
无索引	全表扫描（Full Table Scan）：从第一页读到最后一页逐行比较	全部数据页，可能几十万次磁盘 IO
有索引	索引查找：在有序的索引结构中定位	3~4 次 IO

索引的本质是用额外的存储空间和写入代价，换取查询时的定位速度——和书的目录、字典的偏旁检字表完全一样。

索引主要用于：

• 快速找出匹配 WHERE 子句的行；
• 快速定位要 UPDATE / DELETE 的行（同时决定了锁的范围，见 4.6）；
• 执行联接（JOIN）时从其他表检索行；
• 完成排序（ORDER BY）与分组（GROUP BY），避免额外排序；
• 求最值（MIN/MAX）时直接取索引端点。

有时 MySQL 即使有可用索引也不使用：当优化器估计使用索引需要访问表中大部分行时，表扫描可能更快。但如果查询使用 LIMIT 只搜索部分行，MySQL 会倾向使用索引，因为能更快找到几行并返回。

2.2 索引的代价：不要盲目加索引

索引不是免费的：

代价	说明
写入放大	每个 INSERT/UPDATE/DELETE 都要同步维护所有相关索引的 B+Tree（分裂、合并、页移动）。10 个索引的表，写入可能比无索引慢数倍
空间开销	每个二级索引都是一棵完整 B+Tree，存索引列 + 主键值。索引总量超过数据量在宽表上很常见
内存挤占	索引页与数据页共享 innodb_buffer_pool_size，无用索引会挤掉热数据页，降低缓存命中率
优化器负担	候选索引越多，优化器选择成本越高，也更容易选错索引
DDL 风险	大表加索引耗时长且影响业务，见 2.6.2

实践准则：

准则	说明
单表索引数量控制在 5~6 个以内	超过说明表职责过多或查询未收敛
用联合索引替代多个单列索引	(a,b,c) 一个索引即可覆盖 a、a,b、a,b,c 三种场景
只为真实的高频查询建索引	依据慢日志和 pt-query-digest 的统计，不靠猜
区分度低的列不建	如 sex、is_deleted、status（只有 2~3 个值）
定期清理无用索引	MySQL 5.7+ 可查 sys.schema_unused_indexes；MariaDB 用 pt-index-usage 分析慢日志
大表加索引避开高峰	见 2.6.2

-- MySQL 5.7：找出从未被使用过的索引
SELECT * FROM sys.schema_unused_indexes;
-- 找出冗余/重复索引
SELECT * FROM sys.schema_redundant_indexes;

-- 检查冗余索引（需 percona-toolkit）
pt-duplicate-key-checker --host=127.0.0.1 --user=root --ask-pass

2.3 索引的数据结构

2.3.1 为什么是 B+Tree，而不是二叉树或哈希

数据库索引存在磁盘上，而磁盘 IO 比内存访问慢几个数量级。InnoDB 以页（page，默认 16KB）为最小 IO 单位，读一个字节和读 16KB 的代价几乎相同。所以索引结构的设计目标只有一个：尽量减少 IO 次数，也就是尽量降低树高。

结构	问题
二叉搜索树	1000 万数据树高约 24 层 → 最多 24 次 IO；且顺序插入会退化成链表
平衡二叉树 / 红黑树	树高仍是 log₂(n)，约 24 层，每个节点只存 1 个键，一次 IO 读 16KB 却只用到十几字节，浪费严重
哈希索引	单值等值查询 O(1) 很快，但不支持范围查询、不支持排序、不支持最左前缀；InnoDB 只在自适应哈希索引（AHI）内部使用，用户无法建；MEMORY 引擎默认使用 HASH 索引
B-Tree	非叶子节点也存数据行，导致每个节点能放的键变少，树变高；叶子节点之间无链表，范围查询要反复回溯
B+Tree	非叶子节点只存键 + 指针（能放更多键 → 扇出大 → 树矮）；所有数据都在叶子节点且用双向链表串联（范围查询与 ORDER BY 只需顺序遍历叶子）

树高的直观计算（InnoDB，页 16KB）：

假设主键 BIGINT 8 字节 + 页指针 6 字节 = 14 字节/条目
一个非叶子页可放：16KB / 14B ≈ 1170 个指针
一个叶子页假设一行 1KB，可放 16 行

树高 2（1 层非叶子 + 1 层叶子）： 1170 × 16        ≈ 1.8 万行
树高 3（2 层非叶子 + 1 层叶子）： 1170 × 1170 × 16 ≈ 2190 万行
树高 4：                          ≈ 256 亿行

结论：2000 万行级别的表，主键查找只需 3 次 IO，而且根节点常驻内存（Buffer Pool），实际磁盘 IO 往往只有 1~2 次。这就是 B+Tree 被所有关系型数据库选中的原因。

B+Tree 示意（键为主键 id）

              [ 10 | 30 ]                  <- 根（非叶子，只有键和指针）
             /     |      \
      [1|5|9]   [10|20]   [30|40|50]       <- 非叶子层
        /          |            \
 [1,5,9]<->[10,20]<->[30,40,50]            <- 叶子层：存完整数据行，双向链表相连

2.3.2 其他索引结构

结构	关键字	适用	限制
B+Tree	USING BTREE（默认）	等值、范围、排序、前缀	无
Hash	USING HASH	MEMORY 引擎的等值查询	不支持范围、排序、最左前缀
全文索引	FULLTEXT	文本关键词检索	需分词器，中文需 ngram
空间索引	SPATIAL	地理坐标（GEOMETRY、POINT）	列必须 NOT NULL，仅 MyISAM/InnoDB 5.7+

全文索引（MySQL 5.7 起 InnoDB 支持，中文需配 ngram 分词）：

ALTER TABLE article ADD FULLTEXT KEY ft_content (content) WITH PARSER ngram;

SELECT * FROM article
WHERE MATCH(content) AGAINST('数据库 索引' IN NATURAL LANGUAGE MODE);

-- 布尔模式：+ 必含，- 排除
SELECT * FROM article
WHERE MATCH(content) AGAINST('+索引 -分区' IN BOOLEAN MODE);

生产中的全文检索通常交给 Elasticsearch，MySQL 全文索引只适合小规模场景。

空间索引：

CREATE TABLE geo (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pt POINT NOT NULL,
  SPATIAL KEY sp_pt (pt)
) ENGINE=InnoDB;

2.4 聚簇索引与二级索引（InnoDB 特有）

类型	别名	叶子节点存什么
聚簇索引	主键索引 / clustered index	完整的数据行
二级索引	辅助索引 / secondary index	索引列的值 + 主键值

InnoDB 的关键特性：表数据本身就按主键组织在聚簇索引里（所以 InnoDB 表又叫索引组织表）。若建表时没定义主键：

1. 找第一个非空唯一索引当聚簇索引；
2. 都没有则隐式生成一个 6 字节的 ROW_ID 作为聚簇索引（用户不可见、不可用）。

2.4.1 回表（Back to Table）——二级索引查询的核心开销

-- 假设 idx_ename 是 ename 上的二级索引
SELECT * FROM emp WHERE ename='wangwu';

执行过程：

步骤 1：在 idx_ename 的 B+Tree 中找到 'wangwu' → 得到 empno=4     （第一次树搜索）
步骤 2：拿 empno=4 回到聚簇索引中找到完整行                        （第二次树搜索 = 回表）

-- 只查索引里已有的列，则不需要回表（覆盖索引）
SELECT empno FROM emp WHERE ename='wangwu';   -- idx_ename 叶子里就有 empno，直接返回

这解释了两条重要经验：一是主键要短（每个二级索引叶子都要存一份主键值）；二是*`SELECT ` 有害**，它几乎总会导致回表。

2.4.2 主键选择建议：自增主键 vs UUID

主键方案	优点	缺点
BIGINT UNSIGNED AUTO_INCREMENT	单调递增，新行总是追加到最后一个页，几乎无页分裂；只占 8 字节，二级索引小	分库分表下需要发号器；ID 可被外部枚举
UUID / 随机字符串	全局唯一，分布式友好，不可枚举	随机插入导致频繁页分裂与页碎片；CHAR(36) 长度是 BIGINT 的 4.5 倍，撑大所有二级索引；写入性能可能差数倍
业务字段（如身份证号）	无需额外列	业务规则会变，长度大，改主键代价极高

结论与折中：

• OLTP 单库单表：优先自增整型主键，INT UNSIGNED 上限约 42.9 亿，BIGINT UNSIGNED 几乎用不完。
• 必须用 UUID 时：改存 BINARY(16)（配合 MySQL 8.0 的 UUID_TO_BIN(uuid, 1) 做时间高位交换，恢复单调性），或改用雪花算法（Snowflake）这类趋势递增 ID。
• AUTO_INCREMENT 的空洞是正常现象，不要把自增 ID 当「记录条数」或「连续序号」用：

操作	自增计数器
DELETE FROM t 删光数据	不重置，继续往后发号
事务 ROLLBACK	不回退，序号出现空洞
INSERT 唯一键冲突失败	也可能已消耗序号
TRUNCATE TABLE t	重置为 1（本质是重建表）
MySQL 5.7 InnoDB 重启	计数器丢失，重启后取 MAX(id)+1
MySQL 8.0 InnoDB	计数器持久化到 redo log，重启后不回退

SHOW CREATE TABLE t_seq\G          -- 查看当前 AUTO_INCREMENT 计数器
SELECT LAST_INSERT_ID();           -- 本连接最后一次插入产生的自增值
ALTER TABLE t_seq AUTO_INCREMENT=5000;   -- 只能调大，调小到已有最大值以下无效

2.5 索引分类

2.5.1 基础分类表

索引类型	说明
普通索引（INDEX / KEY）	最基本的索引类型，没有唯一性等限制
唯一性索引（UNIQUE INDEX）	与普通索引基本相同，但索引列值保持唯一（允许多个 NULL）
主键（PRIMARY KEY）	一种唯一索引，必须指定为 PRIMARY KEY，唯一 + 非空 + 聚簇，一表一个
全文索引（FULLTEXT）	可在 VARCHAR 或 TEXT 类型列上创建

按功能完整分类：

类型	创建方式	说明
主键索引	PRIMARY KEY (id)	唯一 + 非空 + 聚簇，一表一个
唯一索引	UNIQUE KEY uk_x (col)	值唯一，允许多个 NULL
普通索引	KEY idx_x (col)	无唯一性要求，最常用
联合（复合）索引	KEY idx_x (a,b,c)	多列组合，遵循最左前缀原则
前缀索引	KEY idx_x (col(10))	只索引字符串前 N 个字符，省空间
全文索引	FULLTEXT KEY ft_x (content)	文本关键词检索，MATCH ... AGAINST
空间索引	SPATIAL KEY sp_x (geom)	地理数据，列必须 NOT NULL
覆盖索引	不是一种独立类型	指查询所需列全部包含在索引中的使用状态

2.5.2 单列索引与多列（复合）索引

索引可以是单列索引，也可以是多列索引。对相关列使用索引是提高 SELECT 性能的最佳途径之一。

• 一个多列索引最多可包含 15 个列（MySQL 8.0 为 16 列）。
• 对某些列类型可以索引列的左前缀，列的顺序非常重要。
• 多列索引可视为通过连接索引列值而创建的排序数组，其限制能力远高于单列索引。

2.5.3 前缀索引

VARCHAR(255) 的 URL 或邮箱列，整列做索引又大又慢，可只取前缀：

-- 先评估选择性（区分度），越接近 1 越好
SELECT COUNT(DISTINCT email)/COUNT(*)      AS full_sel,
       COUNT(DISTINCT LEFT(email,6))/COUNT(*) AS sel6,
       COUNT(DISTINCT LEFT(email,10))/COUNT(*) AS sel10
FROM emp;

-- 选一个接近 full_sel 的最短长度
ALTER TABLE emp ADD KEY idx_email_pre (email(10));

局限：前缀索引不能用于 ORDER BY、GROUP BY，也无法做覆盖索引（因为只有部分值）。

2.5.4 覆盖索引

覆盖索引是最有价值的优化手段之一：查询所需的列全部能从索引中取到，无需回表，EXPLAIN 的 Extra 会出现 Using index。

-- 慢：需要回表
SELECT * FROM emp WHERE deptno=20 ORDER BY sal;

-- 建立联合索引后成为覆盖索引，Extra 出现 Using index
ALTER TABLE emp ADD KEY idx_deptno_sal_ename (deptno, sal, ename);
SELECT deptno, sal, ename FROM emp WHERE deptno=20 ORDER BY sal;

注意：InnoDB 二级索引叶子天然含主键值，所以只查「索引列 + 主键」也是覆盖索引。

2.6 索引管理（DDL）

2.6.1 创建、查看与删除

创建语法一：CREATE INDEX

-- 创建普通索引
create index index_name on table_name (list_name);

-- 创建唯一索引
create unique index index_name on table_name (list_name);

CREATE INDEX idx_ename ON emp (ename);
CREATE UNIQUE INDEX uk_email ON emp (email);
CREATE INDEX idx_dept_sal ON emp (deptno, sal);
CREATE INDEX idx_ename_pre ON emp (ename(6));
-- 指定排序方向（MySQL 8.0 起真正生效，5.7 只解析不生效）
CREATE INDEX idx_sal_desc ON emp (sal DESC);

创建语法二：ALTER TABLE（功能更全，可加主键、外键，可一条语句加多个索引）

-- 创建普通索引
alter table table_name add index index_name (list_name);

-- 创建唯一索引
alter table table_name add unique (list_name);

-- 创建主键索引
alter table table_name add primary key (list_name);

ALTER TABLE emp ADD INDEX idx_hiredate (hiredate);
ALTER TABLE emp ADD UNIQUE INDEX uk_email (email);
ALTER TABLE emp ADD PRIMARY KEY (empno);
ALTER TABLE emp ADD FULLTEXT INDEX ft_ename (ename);
-- 一条 ALTER 加多个索引，只重建/扫描表一次，比多条 CREATE INDEX 快
ALTER TABLE emp ADD INDEX idx_a (sex), ADD INDEX idx_b (sal);

参数说明：

参数	说明
table_name	要添加索引的表名称
list_name	需要建索引的列，多列以逗号分隔，如 (id,name,sex)
index_name	索引名称，非必选，不指定则以第一列名称作为索引名

注意：CREATE INDEX 命令无法创建主键索引，主键索引必须使用 ALTER TABLE ... ADD PRIMARY KEY 创建。

建表时直接定义索引（推荐，一次成型无需重建）：

CREATE TABLE emp (
    empno     INT           NOT NULL AUTO_INCREMENT,
    ename     VARCHAR(20)   NOT NULL,
    sex       CHAR(1)       NOT NULL DEFAULT 'm',
    email     VARCHAR(50)   DEFAULT NULL,
    hiredate  DATE          NOT NULL,
    sal       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    deptno    TINYINT       DEFAULT NULL,
    PRIMARY KEY (empno),
    UNIQUE KEY uk_email (email),
    KEY idx_deptno (deptno),
    KEY idx_deptno_sal (deptno, sal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命名约定（团队统一后极大降低维护成本）：

对象	前缀	示例
主键	无（固定名 PRIMARY）	—
唯一索引	uk_	uk_email
普通索引	idx_	idx_deptno
联合索引	idx_ + 列序	idx_deptno_sal

删除索引：删除 INDEX 或 UNIQUE INDEX 时必须指定索引名称。

drop index index_name on table_name;
alter table table_name drop index index_name;

-- 删除主键索引
alter table table_name drop primary key;

实例：

-- 删除普通索引或唯一索引
drop index empno on emp;
-- Query OK, 1800000 rows affected (1.77 sec)

alter table emp drop index empno;

-- 删除主键索引（有 AUTO_INCREMENT 列时会报错，需先去掉自增属性）
alter table emp drop primary key;
-- Query OK, 1800000 rows affected (1.55 sec)

重命名索引（MySQL 5.7+ / MariaDB 10.5+）：

ALTER TABLE emp RENAME INDEX idx_ename TO idx_emp_ename;

查看索引：

SHOW INDEX FROM emp;
SHOW INDEX FROM emp\G
SHOW KEYS FROM emp;          -- 同义词
SHOW CREATE TABLE emp\G      -- 最直观，能看到完整定义

-- 从系统表查看（可做条件过滤）
SELECT table_name, index_name, seq_in_index, column_name, cardinality
FROM information_schema.statistics
WHERE table_schema='shop' AND table_name='emp'
ORDER BY index_name, seq_in_index;

SHOW INDEX 输出各列含义：

字段	含义
Table	表名
Non_unique	0 = 唯一索引，1 = 非唯一索引（注意是反的）
Key_name	索引名，主键固定为 PRIMARY；同名多行表示这是一个联合索引
Seq_in_index	该列在联合索引中的位置，从 1 开始，用于确认列顺序
Column_name	列名
Collation	A = 升序，D = 降序，NULL = 未排序（如 HASH）
Cardinality	基数：该列不同值的估算数量，越大区分度越好，优化器据此选索引
Sub_part	前缀索引长度，非前缀索引为 NULL
Packed	键是否被压缩，一般为 NULL
Null	该列是否允许 NULL（YES / 空）
Index_type	BTREE / FULLTEXT / HASH / SPATIAL
Comment / Index_comment	备注，disabled 表示索引不可见（8.0 的 invisible index）

Cardinality 是采样估算值，可能严重失真导致优化器选错索引，可手动刷新统计信息：

ANALYZE TABLE emp;           -- 重新采样统计信息，代价小，可在业务低峰执行
OPTIMIZE TABLE emp;          -- 重建表 + 整理碎片，InnoDB 上等价于 ALTER TABLE ... FORCE，会锁表

2.6.2 在线 DDL 与 ALGORITHM=INPLACE

大表加索引的锁影响：MySQL 5.6+ / MariaDB 10.0+ 支持 Online DDL，加二级索引默认 ALGORITHM=INPLACE, LOCK=NONE，期间可读写，但开始和结束时仍需短暂的 MDL 排他锁——若此时有长事务或长查询，会造成整表阻塞（Waiting for table metadata lock），后续所有请求排队，是典型的线上事故成因。

参数	取值	含义
ALGORITHM	INPLACE	原地修改，不拷贝整表（多数加/删索引可用），速度快
	COPY	拷贝整表重建，全程禁止写入，最慢，是 5.5 之前的唯一方式
	INSTANT	8.0.12+：只改元数据（如加列到末尾），秒级完成
LOCK	NONE	允许读写（并发 DML）
	SHARED	允许读，禁止写
	EXCLUSIVE	读写全禁

-- 显式声明，若无法在线执行则直接报错，而不是悄悄锁表
ALTER TABLE emp ADD KEY idx_hiredate (hiredate), ALGORITHM=INPLACE, LOCK=NONE;

# 亿级大表推荐用 pt-online-schema-change：影子表 + 触发器 + 分批拷贝，全程不锁表
pt-online-schema-change \
  --alter "ADD INDEX idx_hiredate (hiredate)" \
  D=shop,t=emp \
  --host=127.0.0.1 --user=root --ask-pass \
  --max-load Threads_running=50 \
  --print --execute
# 演练阶段用 --dry-run 替代 --execute

加索引前的检查清单：① 有无长事务（information_schema.innodb_trx）；② 是否业务低峰；③ 磁盘剩余空间是否大于表大小；④ 是否已在从库或测试库验证过耗时。

2.6.3 创建索引实验：不同索引类型对 type 的影响

实验表为 180 万行的 emp（建表与造数脚本见附录）。

① 普通索引（INDEX）

未建索引前：

select * from emp where empno=1000001;
-- 1 row in set (0.35 sec)

创建索引：

create index empno on emp(empno);

再次查询：

select * from emp where empno=1000001;
-- 1 row in set (0.00 sec)

查看执行计划（desc 是 explain 的同义词）：

desc select * from emp where empno=1000001;

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | empno         | empno | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

当前查询 type 为 ref（非唯一索引等值匹配）。

② 唯一索引（UNIQUE INDEX）

create unique index empno on emp(empno);

desc select * from emp where empno=1000001;

+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | empno         | empno | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+-------+

当前查询 type 提升为 const（唯一索引等值匹配，最多一行，优化器当常量处理）。

③ 主键索引（PRIMARY KEY）

CREATE INDEX 无法创建主键索引，只能使用 ALTER：

alter table emp add primary key (empno);

查询验证：

select * from emp where empno=1000000;
-- 1 row in set (0.00 sec)

desc select * from emp where empno=1000000;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

结论：同样的 SQL，索引唯一性越强，type 越优（ALL → ref → const），耗时从 0.35 秒降到几乎为 0。

2.7 复合索引的最左前缀原则与列顺序设计

联合索引 (a, b, c) 的 B+Tree 是先按 a 排序，a 相同再按 b，b 相同再按 c——类似电话簿按「省、市、区」排序。因此：

最左前缀原则（Leftmost Prefixing）：只有从索引最左列开始、且不跳过中间列，索引才能被利用；一旦遇到范围查询（>、<、BETWEEN、LIKE 'x%'），该列可用索引，但它右边的列无法再用索引做过滤。

假设有多列索引 key(firstname, lastname, age)，以下搜索条件组合会使用该索引：

• firstname, lastname, age
• firstname, lastname
• firstname

即相当于同时建立了 key(firstname, lastname) 和 key(firstname)。

原理示意：

a='id'
b='name'
c='sex'
多列索引：dl=a,b,c;  dl=a,b;  dl=a

2.7.1 匹配规则全表（以 KEY idx_abc (a, b, c) 为例）

WHERE 条件	能否用索引	实际生效的索引列	说明
a=1	能	a	标准最左匹配
a=1 AND b=2	能	a, b
a=1 AND b=2 AND c=3	能	a, b, c	全列命中，最优
b=2	不能	—	缺最左列 a，只能全表扫描
c=3	不能	—	同上
b=2 AND c=3	不能	—	同上
a=1 AND c=3	部分	a	跳过了 b，c 无法用于查找（5.6+ 可用索引条件下推 ICP 在索引里过滤 c）
b=2 AND a=1	能	a, b	条件书写顺序无关，优化器会自动重排
a>1 AND b=2	部分	a	a 是范围，b 不能再用于查找
a=1 AND b>2 AND c=3	部分	a, b	b 是范围，c 不能再用于查找
a=1 AND b IN (2,3) AND c=4	能	a, b, c	IN 等值列表可继续向右传递
a=1 ORDER BY b, c	能	a + 排序	索引已有序，避免 Using filesort
a=1 ORDER BY c	部分	a	跳过 b，排序无法用索引 → Using filesort
a=1 ORDER BY b DESC, c ASC	部分	a	5.7 混合方向无法用索引；8.0 有降序索引可解
a IS NULL	能	a	IS NULL 可以走索引（!=/<> 通常不行）

派生规则：

• 建 (a,b,c) 后，无需再单独建 (a) 和 (a,b)——它们是冗余索引。
• 但 (b) 或 (b,c) 不冗余，若有单查 b 的场景需另建。
• 列顺序设计原则：
  1. 等值查询列放左边，范围查询列放右边；
  2. 区分度（Cardinality）高的列放左边；
  3. 如果所有查询都带 a，则 a 必须最左；
  4. ORDER BY 的列紧跟在等值列之后；
  5. 最后追加需要返回的列，构成覆盖索引。

2.7.2 完整实验：用 key_len 验证最左前缀

准备实验表（三列都是 INT NOT NULL，各占 4 字节，便于用 key_len 反推用了几列）：

CREATE TABLE t_abc (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  a  INT NOT NULL,
  b  INT NOT NULL,
  c  INT NOT NULL,
  d  VARCHAR(20) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY idx_abc (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 造 10 万行测试数据（依赖附录中的 rand_num 函数，也可用下面的递归/循环写法）
DELIMITER $$
CREATE PROCEDURE fill_abc(IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  WHILE i < max_num DO
    SET i = i + 1;
    INSERT INTO t_abc (a,b,c,d) VALUES
      (FLOOR(1+RAND()*100), FLOOR(1+RAND()*100), FLOOR(1+RAND()*100), 'x');
  END WHILE;
  COMMIT;
  SET autocommit = 1;
END $$
DELIMITER ;

CALL fill_abc(100000);
ANALYZE TABLE t_abc;

逐条执行并观察 key、key_len、type、Extra：

-- 实验 1：只用最左列 a → 命中，key_len=4（只用到 a）
EXPLAIN SELECT * FROM t_abc WHERE a=10;

-- 实验 2：a、b → 命中，key_len=8（用到 a、b）
EXPLAIN SELECT * FROM t_abc WHERE a=10 AND b=20;

-- 实验 3：a、b、c 全列 → 命中，key_len=12（索引被充分利用）
EXPLAIN SELECT * FROM t_abc WHERE a=10 AND b=20 AND c=30;

-- 实验 4：条件顺序打乱 → 与实验 3 完全相同，key_len=12（优化器自动重排）
EXPLAIN SELECT * FROM t_abc WHERE c=30 AND b=20 AND a=10;

-- 实验 5：缺最左列 → key=NULL、type=ALL（全表扫描）
EXPLAIN SELECT * FROM t_abc WHERE b=20;
EXPLAIN SELECT * FROM t_abc WHERE c=30;
EXPLAIN SELECT * FROM t_abc WHERE b=20 AND c=30;

-- 实验 6：跳过中间列 → key_len=4（只有 a 生效，c 只能在索引里下推过滤）
EXPLAIN SELECT * FROM t_abc WHERE a=10 AND c=30;
-- Extra 中可见 Using index condition（ICP，5.6+）

-- 实验 7：范围条件之后的列失效 → key_len=8（a 范围 + b，c 不再生效）
EXPLAIN SELECT * FROM t_abc WHERE a>10 AND b=20 AND c=30;

-- 实验 8：范围在中间 → key_len=8（a 等值 + b 范围，c 失效）
EXPLAIN SELECT * FROM t_abc WHERE a=10 AND b>20 AND c=30;

-- 实验 9：IN 是等值列表，可继续向右传递 → key_len=12
EXPLAIN SELECT * FROM t_abc WHERE a=10 AND b IN (20,21) AND c=30;

-- 实验 10：排序用上索引 → 无 Using filesort
EXPLAIN SELECT * FROM t_abc WHERE a=10 ORDER BY b, c;

-- 实验 11：跳过 b 排序 → 出现 Using filesort
EXPLAIN SELECT * FROM t_abc WHERE a=10 ORDER BY c;

-- 实验 12：覆盖索引 → Extra 出现 Using index，且不回表
EXPLAIN SELECT a, b, c FROM t_abc WHERE a=10 AND b=20;

预期结果汇总：

实验	WHERE / ORDER BY	key	key_len	type	Extra 关键字
1	a=10	idx_abc	4	ref	Using where（回表时）
2	a=10 AND b=20	idx_abc	8	ref
3	a=10 AND b=20 AND c=30	idx_abc	12	ref
4	c=30 AND b=20 AND a=10	idx_abc	12	ref	与实验 3 相同
5	b=20 / c=30 / b=20 AND c=30	NULL	NULL	ALL	Using where
6	a=10 AND c=30	idx_abc	4	ref	Using index condition
7	a>10 AND b=20 AND c=30	idx_abc	8	range	Using index condition
8	a=10 AND b>20 AND c=30	idx_abc	8	range	Using index condition
9	a=10 AND b IN(20,21) AND c=30	idx_abc	12	range
10	a=10 ORDER BY b,c	idx_abc	4	ref	无 filesort
11	a=10 ORDER BY c	idx_abc	4	ref	Using filesort
12	SELECT a,b,c ... a=10 AND b=20	idx_abc	8	ref	Using index

实验要点：key_len 是唯一能证明「联合索引到底用了前几列」的证据，possible_keys/key 只能说明索引被选中，不能说明用了几列。做优化时每次只改一个条件，对比 key_len 变化。

---

三、执行计划与查询优化

3.1 EXPLAIN 基础

EXPLAIN 可以模拟优化器执行 SQL，从而知道 MySQL 如何处理 SQL，帮助分析查询语句或表结构的性能瓶颈。通过 EXPLAIN 可以得到：

1. 表的读取顺序；
2. 数据读取操作的类型；
3. 哪些索引可以使用；
4. 哪些索引被实际使用；
5. 表之间的引用；
6. 每张表有多少行被优化器查询（估算）。

EXPLAIN 不真正执行 SQL（加在 SELECT 前完全安全；MySQL 5.6+ 也支持 EXPLAIN UPDATE/DELETE/INSERT，同样不执行）。

explain select * from dept;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | dept  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

各种变体：

EXPLAIN SELECT * FROM emp WHERE deptno=20;
DESC SELECT * FROM emp WHERE deptno=20;   -- DESC 是 EXPLAIN 的同义词
EXPLAIN EXTENDED SELECT ...;              -- 5.6/MariaDB：配合 SHOW WARNINGS 看重写后的 SQL
EXPLAIN FORMAT=JSON SELECT ...;           -- 5.6+：包含成本（cost）等更详细信息
EXPLAIN ANALYZE SELECT ...;               -- MySQL 8.0.18+：真正执行并给出实际耗时与行数
ANALYZE SELECT ...;                        -- MariaDB 10.1+：真正执行并给出实际行数，对照估算值

3.2 EXPLAIN 输出各列详解

字段	含义	关注点
id	查询序号。数字越大越先执行；数字相同则从上到下执行	用来判断子查询/派生表的执行顺序
select_type	查询类型，见 3.2.1	出现 DEPENDENT SUBQUERY 要警惕
table	该行数据来自哪张表；<derived2> 表示派生表，<union1,2> 表示 UNION 结果	确认连接顺序（第一行是驱动表）
partitions	命中的分区（5.7+）	非分区表为 NULL
type	访问类型，最重要的字段之一，见 3.2.2	出现 ALL 或 index 需重点优化
possible_keys	可应用于该表的索引，即优化器认为可能用到的索引	为 NULL 说明没有可用索引
key	实际使用的索引，为 NULL 则未使用索引	为 NULL 而 possible_keys 非空 → 优化器主动弃用，通常因区分度低或统计信息失真
key_len	使用到的索引字节长度，在不损失精确性的前提下越短越好	判断联合索引用了几列的唯一手段，算法见 3.2.3
ref	索引的哪一列/哪个值被使用：const（常量）、func、某表的列名	看 JOIN 的关联方式
rows	MySQL 认为必须检索以返回请求数据的估算行数	越小越好；与实际差距过大说明统计信息需 ANALYZE TABLE
filtered	经 WHERE 过滤后剩余行的百分比（5.7+）	rows × filtered% ≈ 传给下一步的行数
Extra	附加信息，见 3.2.4	出现 Using filesort / Using temporary 需优化

3.2.1 select_type

值	说明
SIMPLE	简单查询，不含子查询或 UNION
PRIMARY	含子查询时的最外层查询
SUBQUERY	SELECT/WHERE 中的子查询（只执行一次）
DEPENDENT SUBQUERY	依赖外层的相关子查询（外层每行执行一次，性能杀手）
DERIVED	FROM 后的派生表（临时表）
MATERIALIZED	子查询被物化成临时表（5.6+ 的优化）
UNION	UNION 中第二个及后续的 SELECT
UNION RESULT	UNION 的去重合并结果
UNCACHEABLE SUBQUERY	结果无法缓存的子查询（含 RAND()、用户变量等）

3.2.2 type 访问类型（性能从优到劣）

简版排序（教学常用）：

system > const > eq_ref > ref > range > index > ALL

完整排序：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > range > index > ALL

值	含义	评价
system	表只有一行（系统表），const 的特例	最优
const	通过主键或唯一索引等值匹配（如 id=1），最多返回一行，优化器直接把查询变量转为常量处理	最优
eq_ref	访问索引返回某单行数据，通常出现在联接中，被驱动表用主键或唯一索引等值关联，每行只匹配一行	优
ref	访问索引返回某个值的匹配数据（可多行），使用非唯一索引等值匹配，通常用 = 时发生	良，最常见的达标状态
fulltext	使用全文索引	视场景
ref_or_null	ref + 额外查找 NULL（WHERE col=? OR col IS NULL）	良
index_merge	索引合并，多个索引结果做交集/并集（常见于 OR）	可接受，但常提示索引设计可优化
range	使用索引返回范围内的行（>、<、BETWEEN、IN、LIKE 'x%'）	良
index	以索引顺序进行全表扫描（扫描整棵索引树）。优点是不用额外排序、索引比数据小；缺点是仍要扫全部记录	差，需关注
ALL	全表扫描，应尽量避免	最差，大表必须优化（小表可接受）

达标线：OLTP 查询至少达到 range，理想是 ref 或 const。

3.2.3 key_len 计算规则

key_len 是判断「联合索引到底用了前几列」的唯一手段。计算方式（utf8 每字符 3 字节，utf8mb4 每字符 4 字节）：

列定义	key_len
TINYINT NOT NULL	1
SMALLINT NOT NULL	2
MEDIUMINT NOT NULL	3
INT NOT NULL	4
INT NULL	4 + 1 = 5（可空列多 1 字节 NULL 标记）
BIGINT NOT NULL	8
DATE NOT NULL	3
DATETIME NOT NULL	5（5.6+）
TIMESTAMP NOT NULL	4
CHAR(10) NOT NULL utf8	10 × 3 = 30
VARCHAR(10) NOT NULL utf8	10 × 3 + 2 = 32（变长列多 2 字节长度前缀）
VARCHAR(10) NULL utf8	10 × 3 + 2 + 1 = 33
VARCHAR(10) NOT NULL utf8mb4	10 × 4 + 2 = 42

计算口诀：字段类型基础长度 + 可空加 1 + 变长加 2 + 字符集倍数。

示例：KEY idx_abc (a,b,c)，其中 a/b/c 都是 INT NOT NULL（各 4 字节）。

key_len	结论
4	只用到 a
8	用到 a、b
12	用到 a、b、c，索引被充分利用

3.2.4 Extra 常见值

Extra 提供 MySQL 如何解析查询的额外信息，是判断是否还有优化空间的关键。

值	含义	是否需要优化
Using index	覆盖索引，只用到索引即可返回所需数据，可避免访问表（不回表）	最理想，追求出现
Using where	在 Server 层用 WHERE 过滤了行。并非所有 WHERE 都会显示，如以 = 方式访问索引时可能不显示	正常；若同时 type=ALL 说明没走索引
Using index condition	索引条件下推（ICP，5.6+），在存储引擎层就用索引过滤，减少回表次数	好现象
Using where; Using index	用了覆盖索引，并在索引上做了过滤	好
Using filesort	需要额外排序（如 ORDER BY 未用到索引），内存或磁盘排序	需优化：给 ORDER BY 列建合适索引
Using temporary	需要创建临时表（常见于 GROUP BY、DISTINCT、UNION、ORDER BY 与 GROUP BY 列不同）	需优化：调整索引使 GROUP BY 列有序
Using join buffer (Block Nested Loop)	JOIN 未用索引，退化到块嵌套循环，用连接缓冲逐块比较	需优化：给被驱动表的关联列建索引
Using index for group-by	GROUP BY 直接用索引完成，无需临时表与排序	好
Using MRR	多范围读优化（MariaDB 常见），把随机回表变顺序 IO	好
Range checked for each record (index map:N)	没有好的索引可用，对每行重新评估是否能用索引	需优化
Impossible WHERE	WHERE 恒为假，无需执行（如 WHERE 1=0）	正常
Select tables optimized away	优化器直接算出结果（如 SELECT MAX(id) 走索引端点）	最优
No tables used	无表查询（如 SELECT NOW()）	正常
Using union(...) / Using intersect(...) / Using sort_union(...)	索引合并的具体方式	可接受
Distinct	找到第一个匹配行后即停止为当前行组合搜索	正常

Using filesort 中的 "file" 是历史名称，并不一定写磁盘：数据量小于 sort_buffer_size 时在内存完成。但它依然意味着一次额外排序，大结果集下代价很高。

3.2.5 EXPLAIN FORMAT=JSON 与 EXPLAIN ANALYZE

FORMAT=JSON 输出优化器的成本估算，可用于对比两种写法哪个更便宜：

EXPLAIN FORMAT=JSON
SELECT empno, ename FROM emp WHERE deptno=20 ORDER BY sal DESC LIMIT 10\G

{
  "query_block": {
    "select_id": 1,
    "cost_info": { "query_cost": "3021.60" },      <- 总成本，越小越好
    "ordering_operation": {
      "using_filesort": true,                       <- 布尔字段，比 Extra 更明确
      "table": {
        "table_name": "emp",
        "access_type": "ref",
        "possible_keys": ["idx_deptno"],
        "key": "idx_deptno",
        "used_key_parts": ["deptno"],               <- 直接告诉你用了哪几列，无需算 key_len
        "key_length": "2",
        "rows_examined_per_scan": 250000,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "2521.60",
          "eval_cost": "500.00",
          "prefix_cost": "3021.60",
          "data_read_per_join": "12M"
        },
        "used_columns": ["empno","ename","sal","deptno"]
      }
    }
  }
}

JSON 格式的独有价值：used_key_parts（直接列出用到的索引列）、cost_info（成本明细）、attached_condition（下推到引擎的条件）、used_columns（是否真的只需要少数列）。

EXPLAIN ANALYZE（MySQL 8.0.18+）会真正执行语句，输出实际耗时与实际行数，用于验证估算是否准确：

EXPLAIN ANALYZE
SELECT empno, ename FROM emp WHERE deptno=20 ORDER BY sal DESC LIMIT 10\G

-> Limit: 10 row(s)  (actual time=812.3..812.3 rows=10 loops=1)
    -> Sort: emp.sal DESC, limit input to 10 rows per chunk
        -> Index lookup on emp using idx_deptno (deptno=20)
           (cost=25000 rows=250000) (actual time=0.1..701.2 rows=249881 loops=1)

读法：cost/rows 是估算，actual time/rows/loops 是实测。若估算 rows 与 actual rows 差一个数量级以上，说明统计信息失真，需 ANALYZE TABLE。MariaDB 10.1+ 的等价命令是 ANALYZE SELECT ...。

3.3 EXPLAIN 实战案例

3.3.1 案例一：从 ALL 优化到 ref

-- 优化前：deptno 上没有索引
EXPLAIN SELECT empno, ename, sal FROM emp WHERE deptno = 20;

+----+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 1000000 |    10.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+

解读：type=ALL + possible_keys=NULL + rows=100 万 → 无索引可用，全表扫描，100 万行里筛出约 10%。

ALTER TABLE emp ADD KEY idx_deptno (deptno);
EXPLAIN SELECT empno, ename, sal FROM emp WHERE deptno = 20;

+----+-------------+-------+------+---------------+------------+---------+-------+--------+----------+-------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows   | filtered | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | emp   | ref  | idx_deptno    | idx_deptno | 2       | const | 250000 |   100.00 | NULL  |
+----+-------------+-------+------+---------------+------------+---------+-------+--------+----------+-------+

解读：type=ref、key=idx_deptno、ref=const（与常量比较）、filtered=100%。rows 仍有 25 万是因为 20 号部门确实有这么多人——索引解决的是「定位」，不解决「结果集本身太大」，这种情况要靠分页或业务条件收敛。

3.3.2 案例二：消除 Using filesort，做成覆盖索引

EXPLAIN SELECT empno, ename, sal FROM emp WHERE deptno=20 ORDER BY sal DESC LIMIT 10;

| id | type | key        | key_len | rows   | Extra                       |
|  1 | ref  | idx_deptno | 2       | 250000 | Using where; Using filesort |

解读：索引只帮了 deptno 的定位，25 万行还要全部取出再排序，Using filesort 是瓶颈。

-- 联合索引：等值列 deptno 在左，排序列 sal 在右，再带上要查的 ename 形成覆盖
ALTER TABLE emp ADD KEY idx_deptno_sal_ename (deptno, sal, ename);

EXPLAIN SELECT empno, ename, sal FROM emp WHERE deptno=20 ORDER BY sal DESC LIMIT 10;

| id | type | key                  | key_len | rows | Extra                    |
|  1 | ref  | idx_deptno_sal_ename | 2       |   10 | Using where; Using index |

解读：Using filesort 消失（索引中 sal 已有序，倒序遍历即可），Using index 出现（empno 作为主键天然存在于二级索引叶子中，ename 也在索引里，完全不回表），rows 从 25 万降到 10。这是索引优化的教科书式结果。

3.3.3 案例三：JOIN 的驱动表与索引

EXPLAIN SELECT e.ename, d.dname
FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 15000;

+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | SIMPLE      | e     | range  | idx_sal       | idx_sal | 5       | NULL           | 1200 | Using where |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 1       | shop.e.deptno  |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+

解读要点：

• 两行 id 都是 1 → 同一层查询，按显示顺序执行：e 是驱动表，d 是被驱动表。
• 驱动表 e 用 idx_sal 做 range 扫描，筛出约 1200 行。
• 被驱动表 d 的 type=eq_ref、ref=shop.e.deptno → 拿驱动表每行的 deptno 去 dept 主键上精确定位，每次只命中 1 行，这是 JOIN 的最优形态。
• 总成本约 1200 × 1 次索引查找。JOIN 优化的第一原则：小结果集做驱动表，被驱动表的关联列必须有索引；若 Extra 出现 Using join buffer (Block Nested Loop)，说明被驱动表关联列没索引，必须补。

3.4 索引失效的常见场景

以下写法会让已有索引「白建」，是慢查询的主要来源。

#	场景	反例	正确写法
1	索引列上做运算	WHERE sal+1000 > 10000	WHERE sal > 9000
2	索引列上用函数	WHERE YEAR(hiredate)=2020	WHERE hiredate >= '2020-01-01' AND hiredate < '2021-01-01'
3	隐式类型转换（字符串列传数字）	WHERE phone = 13800138000（phone 是 varchar）	WHERE phone = '13800138000'
4	前导通配的 LIKE	WHERE ename LIKE '%wu' 或 '%wu%'	WHERE ename LIKE 'wang%'；必须后缀匹配则考虑反转列 + 索引，或上全文/ES
5	OR 连接了无索引列	WHERE ename='a' OR sex='m'（sex 无索引）	两列都建索引（可走 index merge），或改写为 UNION ALL
6	否定条件	WHERE deptno != 20、NOT IN、<>	改用 IN 正向枚举；或接受全扫（区分度低时反而更快）
7	不符合最左前缀	(a,b,c) 上 WHERE b=1	调整索引列顺序，或另建 (b)
8	区分度太低	在 sex（只有 m/f）上建索引	不建；优化器算出要扫过半数据会主动弃用索引
9	两边字符集/排序规则不一致的 JOIN	utf8 表 JOIN utf8mb4 表	统一字符集
10	ORDER BY 与索引方向/顺序不符	(a,b) 上 ORDER BY a ASC, b DESC	5.7 无解，改查询或升级 8.0 用降序索引
11	范围条件之后的列	(a,b,c) 上 a>1 AND b=2 AND c=3	把范围列放到索引最右，或调整为 (b,c,a)
12	深分页	LIMIT 1000000, 20 需先丢弃 100 万行	用「上次最大 id」游标：WHERE id > 上次最大id ORDER BY id LIMIT 20
13	*`SELECT `**	阻断了覆盖索引的可能	只列出真正需要的列

3.4.1 逐条对比 SQL 与验证方法

统一验证方法：对每组「反例 / 正例」分别 EXPLAIN，比较 type、key、key_len、rows、Extra 五项；再用 SHOW STATUS LIKE 'Handler_read%' 前后差值确认是否真的走了索引。

-- 通用验证模板
FLUSH STATUS;                        -- 清零会话状态计数器
SELECT ...;                          -- 执行待测 SQL
SHOW STATUS LIKE 'Handler_read%';    -- Handler_read_rnd_next 暴增 = 全表扫描

① 列上做运算

EXPLAIN SELECT * FROM emp WHERE sal + 1000 > 10000;   -- type=ALL，key=NULL
EXPLAIN SELECT * FROM emp WHERE sal > 9000;           -- type=range，key=idx_sal

② 列上用函数（最常见的日期查询错误）

-- 反例：函数把列包住，B+Tree 无法用有序性定位
EXPLAIN SELECT * FROM emp WHERE YEAR(hiredate) = 2020;         -- type=ALL
EXPLAIN SELECT * FROM emp WHERE DATE(create_time) = '2026-08-22';  -- type=ALL
EXPLAIN SELECT * FROM emp WHERE LEFT(ename,3) = 'wan';         -- type=ALL

-- 正例：改成范围（左闭右开，避免遗漏 23:59:59.999）
EXPLAIN SELECT * FROM emp
 WHERE hiredate >= '2020-01-01' AND hiredate < '2021-01-01';   -- type=range
EXPLAIN SELECT * FROM emp
 WHERE create_time >= '2026-08-22 00:00:00' AND create_time < '2026-08-23 00:00:00';
EXPLAIN SELECT * FROM emp WHERE ename LIKE 'wan%';             -- type=range

MySQL 8.0.13+ 支持函数索引，可作为无法改 SQL 时的补救： ALTER TABLE emp ADD KEY idx_year ((YEAR(hiredate)));

③ 隐式类型转换（最隐蔽，值得单独演示）

CREATE TABLE t_user (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(20) NOT NULL,
  KEY idx_phone (phone)
) ENGINE=InnoDB;

-- 错误：数字与字符串比较，MySQL 把 phone 列整体 CAST 成数字
--       → 等于在列上加了函数 → 索引失效 → 全表扫描
EXPLAIN SELECT * FROM t_user WHERE phone = 13800138000;    -- type: ALL

-- 正确
EXPLAIN SELECT * FROM t_user WHERE phone = '13800138000';  -- type: ref, key: idx_phone

反向情况是安全的：如果列是 INT 而传入 '123'，MySQL 转换的是常量而不是列，索引依然可用。规则记为：转换发生在列上则失效，发生在常量上则无妨。 验证技巧：SHOW WARNINGS; 在这类查询后常给出 Cannot use range access on index ... due to type or collation conversion 的提示。

④ 前导模糊查询

EXPLAIN SELECT * FROM emp WHERE ename LIKE '%wu';    -- type=ALL（前导 % 无法定位起点）
EXPLAIN SELECT * FROM emp WHERE ename LIKE '%wu%';   -- type=ALL
EXPLAIN SELECT * FROM emp WHERE ename LIKE 'wan%';   -- type=range，可用索引

-- 补救 1：只查索引列时，5.7 可能选 type=index（全索引扫描）比全表扫描略好
EXPLAIN SELECT ename FROM emp WHERE ename LIKE '%wu';   -- type=index, Using where; Using index

-- 补救 2：存一列反转值并建索引，把后缀匹配变成前缀匹配
ALTER TABLE emp ADD COLUMN ename_rev VARCHAR(20), ADD KEY idx_ename_rev (ename_rev);
UPDATE emp SET ename_rev = REVERSE(ename);
SELECT * FROM emp WHERE ename_rev LIKE REVERSE('wu') || '%';   -- 等价于 ename LIKE '%wu'

⑤ OR 连接非索引列

-- 反例：sex 无索引，整条退化为全表扫描（一个 OR 分支没索引就全废）
EXPLAIN SELECT * FROM emp WHERE ename='wangwu' OR sex='m';     -- type=ALL

-- 正例 1：两列都有索引 → 可能走 index_merge（Extra: Using union(...)）
ALTER TABLE emp ADD KEY idx_sex (sex);
EXPLAIN SELECT * FROM emp WHERE ename='wangwu' OR deptno=20;   -- type=index_merge

-- 正例 2：改写为 UNION ALL，两个分支各自走索引（注意可能有重复行，必要时用 UNION）
EXPLAIN SELECT * FROM emp WHERE ename='wangwu'
UNION ALL
SELECT * FROM emp WHERE deptno=20 AND ename<>'wangwu';

⑥ 不满足最左前缀 / ⑦ 范围条件之后的列

-- idx_abc(a,b,c)
EXPLAIN SELECT * FROM t_abc WHERE b=2 AND c=3;            -- key=NULL, type=ALL
EXPLAIN SELECT * FROM t_abc WHERE a=1 AND b=2 AND c=3;    -- key_len=12（全部命中）
EXPLAIN SELECT * FROM t_abc WHERE a>1 AND b=2 AND c=3;    -- key_len=8（b、c 只能下推过滤）

验证方法：只看 key_len 的变化即可确定失效边界（见 2.7.2 实验表）。

⑧ IS NULL 与 IS NOT NULL

-- IS NULL 可以走索引（InnoDB 的索引里也存 NULL 值，且排在最前）
EXPLAIN SELECT * FROM emp WHERE deptno IS NULL;       -- type=ref，key=idx_deptno

-- IS NOT NULL 通常要扫描绝大部分索引 → 优化器多半弃用索引
EXPLAIN SELECT * FROM emp WHERE deptno IS NOT NULL;   -- type=ALL（当非 NULL 行占多数时）

-- 若非 NULL 行只占极少数，优化器又会用索引 —— 结论：取决于数据分布，不能一概而论

⑨ != 与 NOT IN

EXPLAIN SELECT * FROM emp WHERE deptno != 20;                 -- 多为 type=ALL
EXPLAIN SELECT * FROM emp WHERE deptno NOT IN (20,30);        -- 多为 type=ALL
EXPLAIN SELECT * FROM emp WHERE deptno IN (10,40);            -- type=range，正向枚举有效

-- NOT IN 的 NULL 陷阱：子查询结果含 NULL 时整个结果集为空
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
-- Empty set
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL);
-- 正确返回
-- 更稳的写法：NOT EXISTS 或 LEFT JOIN ... IS NULL
SELECT d.deptno FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno WHERE e.empno IS NULL;

⑩ 数据分布导致优化器弃用索引

-- 表中 99% 的行 status=1，查 status=1 时走索引反而更慢（回表次数太多）
EXPLAIN SELECT * FROM orders WHERE status=1;   -- key=NULL 但 possible_keys=idx_status

-- 验证优化器的判断是否正确：强制使用索引后对比耗时与 rows
EXPLAIN SELECT * FROM orders FORCE INDEX (idx_status) WHERE status=1;
SELECT ... FORCE INDEX (idx_status) ...;   -- 实测耗时
SELECT ... IGNORE INDEX (idx_status) ...;  -- 对照耗时

-- 若确认是统计信息失真导致选错，先刷新统计信息
ANALYZE TABLE orders;

判断原则：key=NULL 而 possible_keys 非空时，先怀疑数据分布（正常行为），再怀疑统计信息失真。FORCE INDEX 是临时手段，长期应通过改索引或改 SQL 解决——数据分布会变，硬编码 hint 会成为下一个坑。

3.5 查询优化实战

3.5.1 分页深翻优化

-- 反例：LIMIT 1000000,20 需要先读出 100 万零 20 行再丢弃前 100 万行
SELECT * FROM emp ORDER BY empno LIMIT 1000000, 20;   -- 耗时随偏移线性增长

-- 方案 1（最优）：游标分页，记住上一页最后一个主键值
SELECT * FROM emp WHERE empno > 1000000 ORDER BY empno LIMIT 20;

-- 方案 2：延迟关联，先在索引里翻页拿主键，再回表取完整行
SELECT e.* FROM emp e
INNER JOIN (
  SELECT empno FROM emp ORDER BY empno LIMIT 1000000, 20
) t ON e.empno = t.empno;

-- 方案 3：业务上限制最大页数（如只允许翻到第 100 页），配合搜索条件收敛

方案	适用	局限
游标分页	无限下拉、顺序翻页	不能跳页
延迟关联	必须支持跳页的后台管理页面	仍随偏移变慢，但常数小很多（走覆盖索引翻页）
限制页数	搜索结果页	需产品配合

3.5.2 COUNT 优化

SELECT COUNT(*)  FROM emp;    -- 推荐：优化器会选最小的可用索引扫描
SELECT COUNT(1)  FROM emp;    -- 与 COUNT(*) 性能相同（无本质差异）
SELECT COUNT(id) FROM emp;    -- 需判断 id 是否为 NULL，略慢
SELECT COUNT(deptno) FROM emp;-- **语义不同**：不统计 deptno 为 NULL 的行

引擎 / 写法	行为
MyISAM COUNT(*) 无 WHERE	直接读表元数据中的行数，O(1)
InnoDB COUNT(*)	必须逐行扫描（MVCC 下每个事务可见行数不同，无法缓存），大表很慢
InnoDB + 二级索引	优化器会选择最小的二级索引做覆盖扫描，比扫聚簇索引快

大表精确计数的替代方案：

-- ① 估算值（够用则最快，误差可达 ±50%）
SHOW TABLE STATUS LIKE 'emp'\G          -- 看 Rows 字段
EXPLAIN SELECT * FROM emp;              -- 看 rows 字段
SELECT table_rows FROM information_schema.tables
 WHERE table_schema='shop' AND table_name='emp';

-- ② 维护计数表（写入时同步 +1/-1，读取 O(1)）
-- ③ 用 Redis 计数器，定期与 DB 校准
-- ④ 分段并行统计后求和

-- 多条件计数合并成一次扫描（避免 N 次全表扫描）
SELECT COUNT(*) AS total,
       SUM(CASE WHEN sex='m'   THEN 1 ELSE 0 END) AS male_cnt,
       SUM(CASE WHEN sal>10000 THEN 1 ELSE 0 END) AS high_sal_cnt
FROM emp;

3.5.3 排序优化与 filesort

-- 反例：无索引可用，25 万行取出后排序
SELECT * FROM emp WHERE deptno=20 ORDER BY sal DESC;    -- Using filesort

-- 正例：等值列 + 排序列建联合索引
ALTER TABLE emp ADD KEY idx_deptno_sal (deptno, sal);
SELECT * FROM emp WHERE deptno=20 ORDER BY sal DESC;    -- filesort 消失

filesort 的两种算法：

算法	过程	触发条件
双路排序（回表排序）	只取排序列 + 行指针排序，排完再回表取数据	单行长度超过 max_length_for_sort_data
单路排序（一次性排序）	把所需列全部取入 sort_buffer 一次排完	默认，行较短时；sort_buffer_size 不足会分块并归并（Sort_merge_passes 增长）

SHOW VARIABLES LIKE 'sort_buffer_size';               -- 默认 256K，可调 1~4M（每连接独占，勿过大）
SHOW VARIABLES LIKE 'max_length_for_sort_data';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';          -- 持续增长说明 sort_buffer 偏小

排序优化规则：

• ORDER BY 的列顺序、方向必须与索引一致（5.7 不支持混合方向，8.0 用降序索引解决）；
• WHERE 中的等值列可以「吃掉」索引前缀，例如 (a,b) 索引下 WHERE a=1 ORDER BY b 有效；
• 只 ORDER BY 不带 WHERE 时，可直接用索引顺序扫描；
• 尽量配合 LIMIT，让优化器用「优先队列」只维护 N 行；
• 避免 ORDER BY RAND()（必然 filesort + 临时表），改为先随机取主键再查。

3.5.4 子查询改连接

-- 反例：相关子查询，外层每行执行一次（select_type=DEPENDENT SUBQUERY）
SELECT * FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno=d.deptno AND e.sal>10000);

-- 反例：IN 子查询在 5.5 及以前会被改写成 DEPENDENT SUBQUERY，性能极差
SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM emp WHERE sal>10000);

-- 正例：改写为 JOIN，优化器可自由选择驱动表和算法
SELECT DISTINCT d.* FROM dept d
INNER JOIN emp e ON e.deptno=d.deptno AND e.sal>10000;

MySQL 5.6+ 对 IN 子查询做了半连接（semi-join）与物化（MATERIALIZED）优化，差距缩小，但 JOIN 写法的执行计划更稳定、更可预测，是团队规范首选。NOT IN 必须改 NOT EXISTS 或 LEFT JOIN ... IS NULL（NULL 陷阱见 3.4.1 ⑨）。

3.5.5 批量操作与其他写法要点

-- ① 批量插入：一条多值 INSERT 远快于 N 条单行 INSERT（减少解析、网络往返与事务提交）
INSERT INTO emp (ename,sal,deptno) VALUES ('a',1,10),('b',2,10),('c',3,20);
-- 建议每批 500~1000 行，过大会超 max_allowed_packet 并造成大事务

-- ② 关闭自动提交后批量提交
SET autocommit=0;
-- ... 多条 INSERT ...
COMMIT;
SET autocommit=1;

-- ③ 超大量导入用 LOAD DATA（比 INSERT 快数倍）
LOAD DATA LOCAL INFILE '/tmp/emp.csv' INTO TABLE emp
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

-- ④ 导入前临时关闭校验与索引维护（导入完必须恢复）
SET unique_checks=0;
SET foreign_key_checks=0;
-- ... 导入 ...
SET unique_checks=1;
SET foreign_key_checks=1;

-- ⑤ 批量更新/删除必须分批，避免大事务与长时间锁
DELETE FROM log_table WHERE create_time < '2025-01-01' LIMIT 2000;   -- 循环直到 ROW_COUNT()=0

-- ⑥ 避免 SELECT *：明确列名才能用覆盖索引，也减少网络与内存开销
SELECT empno, ename FROM emp WHERE deptno=20;      -- 而不是 SELECT *

-- ⑦ 用 LIMIT 1 提前终止（确定只需一行时）
SELECT empno FROM emp WHERE ename='wangwu' LIMIT 1;

-- ⑧ 相同 SQL 保持一致的写法与参数化，便于慢日志归并与执行计划缓存

大表 DDL 的注意事项：

事项	说明
先查长事务	information_schema.innodb_trx，有长事务时 DDL 会卡在 MDL 上并阻塞全表
显式声明算法	ALGORITHM=INPLACE, LOCK=NONE，不满足则报错而非悄悄锁表
磁盘空间	INPLACE 重建表需要与表等大的临时空间，先确认剩余空间
选低峰执行	并设置 lock_wait_timeout，避免无限等待 MDL
亿级表用工具	pt-online-schema-change 或 gh-ost
从库先验证	先在从库/测试库跑一遍，记录耗时
一次合并多项变更	多个 ADD INDEX 写在一条 ALTER 里，只扫表一次

3.6 表统计信息与碎片整理

-- 查看表状态：行数估算、平均行长、数据大小、索引大小、碎片（Data_free）
SHOW TABLE STATUS LIKE 'emp'\G

-- 精确查询数据与索引占用空间
SELECT table_name,
       ROUND(data_length/1024/1024, 2)  AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       ROUND(data_free/1024/1024, 2)    AS free_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema='shop' ORDER BY data_length DESC;

-- 刷新统计信息（Cardinality 采样），代价小，执行计划突然变差时首先执行
ANALYZE TABLE emp;

-- 重建表并整理碎片：大量 DELETE / 变长列频繁更新后 Data_free 很大时执行
OPTIMIZE TABLE emp;
-- InnoDB 上 OPTIMIZE 等价于：
ALTER TABLE emp ENGINE=InnoDB;      -- 或 ALTER TABLE emp FORCE;

命令	作用	锁与代价
ANALYZE TABLE	只重新采样统计信息	轻量，短暂读锁，可在低峰随时执行
OPTIMIZE TABLE	重建表 + 整理碎片 + 重建索引 + 更新统计	重建整表，InnoDB 上以 Online DDL 方式执行但仍需 MDL，大表耗时长，务必低峰
CHECK TABLE	检查表与索引是否损坏	只读，加读锁
REPAIR TABLE	修复表（仅 MyISAM，InnoDB 不支持）	长时间锁表

InnoDB 统计信息相关参数：

SHOW VARIABLES LIKE 'innodb_stats_persistent';         -- ON：统计信息持久化到磁盘（5.6+ 默认）
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';  -- 默认 20，采样页数，可调大提高准确度
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';        -- ON：表变更超 10% 自动重算

---

四、事务与锁

4.1 什么是事务

SQL 事务具有以下要点：

1. 只有 InnoDB 引擎才能使用事务（MyISAM 不支持）；
2. 事务处理可用来维护数据的完整性；
3. 事务用于管理 insert、update、delete 语句（DML）。

问：MySQL 默认是否开启事务自动提交？

答：MySQL 默认已开启自动提交（autocommit=1），否则每次插入数据都需手动提交。

4.2 ACID 四特性及其实现机制

事务必须满足四个条件：

特性	含义
原子性（Atomicity）	事务中所有操作要么全部成功，要么全部失败；失败后整个事务回滚至开始前状态，不对数据做任何修改
一致性（Consistency）	事务开始至结束，数据必须保持一致、未被破坏；不符合规则时事务自动回退。例如两账户转账，总额初始 2000，无论如何转账，最终相加仍为 2000
隔离性（Isolation）	多个事务并发运行时互不干扰，否则可能出现数据交叉导致损坏
持久性（Durability）	事务执行成功后，数据被写入磁盘永久保存

InnoDB 的具体实现机制：

特性	全称	含义	InnoDB 的实现机制
A	Atomicity 原子性	事务内的操作要么全成功，要么全失败回滚	undo log（回滚日志）：记录修改前的旧值，回滚时反向执行
C	Consistency 一致性	事务前后数据都满足完整性约束	由 A、I、D 共同保证 + 各类约束
I	Isolation 隔离性	并发事务互不干扰	锁 + MVCC（多版本并发控制）
D	Durability 持久性	一旦提交，数据永不丢失，即使宕机	redo log（重做日志，即 ib_logfile*）+ WAL 机制

三个核心日志的对比（初学者最易混淆）：

日志	层次	记录内容	用途
undo log	InnoDB 引擎层	修改前的数据（逻辑日志）	事务回滚 + MVCC 读旧版本
redo log	InnoDB 引擎层	数据页的物理修改	崩溃恢复（crash recovery），保证已提交事务不丢
binlog	Server 层	逻辑 SQL 或行变更	主从复制 + 时间点恢复（见 11-05-备份与恢复.md、11-06-主从复制与读写分离.md）

WAL（Write-Ahead Logging）：提交事务时并不立刻把数据页刷盘（随机 IO 很慢），而是先顺序写 redo log 并 fsync，数据页由后台线程异步刷。宕机后重放 redo log 即可恢复。这是 InnoDB 又快又安全的根本原因。

-- 关键持久性参数（生产双 1 配置，最安全但最慢）
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';  -- 1：每次提交都 fsync redo log
SHOW VARIABLES LIKE 'sync_binlog';                      -- 1：每次提交都 fsync binlog

innodb_flush_log_at_trx_commit	行为	风险
1（默认，生产必须）	每次提交都写盘并 fsync	无数据丢失
2	每次提交写入 OS 缓存，每秒 fsync	MySQL 崩溃不丢，操作系统崩溃丢 1 秒
0	每秒写入并 fsync	MySQL 崩溃即丢 1 秒，仅测试/导数据可用

4.3 事务控制语句

语句	作用
set autocommit=0	关闭事务自动提交
set autocommit=1	开启事务自动提交
begin / start transaction	开始事务
rollback	回滚事务
commit	提交事务
savepoint 标识符	创建事务保存点
release savepoint 标识符	删除保存点
rollback to 标识符	回滚到指定保存点
set transaction	修改事务隔离级别

4.3.1 两种执行事务的方式

方式 1：使用 begin 手动执行

begin;       -- 开始事务
rollback;    -- 回滚事务
commit;      -- 确认事务

方式 2：关闭自动提交

set autocommit=0;   -- 关闭自动提交
set autocommit=1;   -- 开启自动提交

4.3.2 完整语法

-- 开启事务（两种写法等价）
BEGIN;
START TRANSACTION;
START TRANSACTION READ ONLY;         -- 只读事务，优化器可做额外优化
START TRANSACTION WITH CONSISTENT SNAPSHOT;  -- 立即建立一致性快照（备份用）

-- 提交 / 回滚
COMMIT;
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;   -- 回滚到保存点，事务仍在进行中
ROLLBACK TO sp1;             -- 简写
RELEASE SAVEPOINT sp1;       -- 释放保存点

事务执行示例：

begin;
commit;
rollback;

保存点基础示例：

begin;
savepoint a;
rollback to a;
release savepoint a;

4.3.3 完整示例：银行转账（事务的经典场景）

CREATE TABLE account (
    id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_name (name)
) ENGINE=InnoDB;

INSERT INTO account (name,balance) VALUES ('A',1000),('B',1000);

BEGIN;
UPDATE account SET balance = balance - 200 WHERE name='A';
UPDATE account SET balance = balance + 200 WHERE name='B';
-- 若第二条失败，第一条必须撤销，否则钱凭空消失
COMMIT;   -- 或 ROLLBACK;

保存点进阶示例：

BEGIN;
INSERT INTO account (name,balance) VALUES ('C',500);
SAVEPOINT after_c;

INSERT INTO account (name,balance) VALUES ('D',600);
-- 发现 D 的数据有误，只撤销 D，保留 C
ROLLBACK TO after_c;

COMMIT;   -- 最终只有 C 被插入

4.3.4 autocommit 自动提交

MySQL 默认 autocommit=1：每条单独的 DML 都是一个独立事务，执行完立即提交。这就是为什么平时写 UPDATE 不需要 COMMIT——也是为什么误删数据无法 ROLLBACK。

SHOW VARIABLES LIKE 'autocommit';
SELECT @@autocommit;

SET autocommit = 0;         -- 当前会话关闭自动提交
SET GLOBAL autocommit = 0;  -- 全局关闭（仅对新连接生效；不建议改全局）

关闭后，所有语句都在一个隐式事务里，必须手动 COMMIT，否则：

• 数据对其他会话不可见；
• 持有的锁不释放，容易造成大面积锁等待；
• 长事务导致 undo log 无法清理，ibdata1 膨胀。

-- 生产安全操作套路：先关自动提交，验证后再提交
SET autocommit=0;
DELETE FROM emp WHERE deptno=99;
SELECT ROW_COUNT();          -- 确认影响行数是否符合预期
-- 符合预期 → COMMIT; 不符合 → ROLLBACK;
COMMIT;
SET autocommit=1;

隐式提交——以下语句会强制提交当前事务，即使处于 BEGIN 中：

类别	语句
DDL	CREATE、ALTER、DROP、TRUNCATE、RENAME
DCL	GRANT、REVOKE、SET PASSWORD、CREATE USER
事务控制	再次 BEGIN、START TRANSACTION
锁与复制	LOCK TABLES、UNLOCK TABLES、START SLAVE、FLUSH
其他	LOAD DATA、ANALYZE TABLE、OPTIMIZE TABLE

因此 DDL 不能回滚（MySQL 8.0 有原子 DDL 但仍不支持用户回滚）。DROP TABLE 打错表名只能靠备份 + binlog 恢复（见 11-05-备份与恢复.md）。

查看当前活跃事务与长事务：

-- 找出运行超过 60 秒的事务（加索引/发布前必查）
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

4.4 隔离级别与三类并发问题

SQL 标准定义了四种隔离级别：

隔离级别	英文	说明
读未提交	read uncommitted	可读到其他事务未提交的数据
读已提交	read committed	只能读到已提交的数据
可重复读	repeatable read	MySQL 默认级别，同一事务内多次读取结果一致
串行化	serializable	事务完全隔离，串行执行

4.4.1 各级别详细说明

1. read uncommitted（读未提交）

事务 A 未提交的数据，事务 B 依然能读取到，这种现象称为脏读，读到的数据叫"脏数据"。该级别隔离性最低，一般不使用。

2. read committed（读已提交）

任何事务只能读取到已经提交的数据。此级别存在不可重复读（Nonrepeatable Read）：若其他事务重新提交了数据，当前事务第二次查询会发现结果发生变化。

3. repeatable read（可重复读）

MySQL 默认事务隔离级别。事务 A 和事务 B 并发查询同一数据时看到相同结果；即使其他事务已重新提交数据，本事务再查询仍看到之前的状态。按 SQL 标准该级别仍可能出现幻读，但新版本 MySQL 使用 InnoDB 引擎（MVCC + 间隙锁）已很好地解决了幻读问题。

4. serializable（串行化）

事务之间完全隔离，不同事务不能在同一时间对相同数据进行操作。隔离效果最好，但可能导致大量超时和锁竞争。

4.4.2 隔离级别与并发问题对应关系表

级别	脏读	不可重复读	幻读	并发性能	典型使用者
READ UNCOMMITTED 读未提交	会	会	会	最高	无（禁用）
READ COMMITTED 读已提交	不会	会	会	高	Oracle / PostgreSQL 默认；互联网 MySQL 常改为此级别
REPEATABLE READ 可重复读	不会	不会	理论会，InnoDB 中基本不会	中	MySQL / MariaDB 默认
SERIALIZABLE 串行化	不会	不会	不会	最低	极少，强一致小并发场景

三类问题的区别：

问题	侧重点	解决手段
脏读	读到未提交数据	提交读（只读已提交版本）
不可重复读	同一行的值变了（UPDATE）	MVCC 一致性视图
幻读	结果集的行数变了（INSERT/DELETE）	间隙锁 / 串行化

4.4.3 查看与设置隔离级别

-- 查看（MySQL 5.7 / MariaDB）
SELECT @@tx_isolation;                  -- 会话级
SELECT @@global.tx_isolation;            -- 全局级
SELECT @@global.tx_isolation, @@tx_isolation;   -- 一次看两个
SHOW VARIABLES LIKE 'tx_isolation';

-- MySQL 8.0 改名为 transaction_isolation（tx_isolation 在 8.0 被移除）
SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;

-- 全局级别（仅对新连接生效，重启后失效）
set global transaction isolation level read committed;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 当前会话级别（只影响当前会话之后开启的事务）
set session transaction isolation level read committed;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 只影响下一个事务
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

# /etc/my.cnf 永久生效
[mysqld]
transaction-isolation=READ-COMMITTED

4.4.4 实验一：脏读（Dirty Read）复现

准备：把隔离级别调到读未提交，account 表中 A 的余额为 1000。

set global transaction isolation level read uncommitted;
select @@global.tx_isolation, @@tx_isolation;
-- 注意：改 global 后需要【重新连接】才对新会话生效

事务 A：

begin;

事务 A 尚未提交时，事务 B：

select * from 表名;   -- 可读到事务 A 未提交的数据（脏读）

完整双会话步骤：

时刻	会话 A（转账）	会话 B（查询）
T1	BEGIN;	BEGIN;
T2	UPDATE account SET balance=800 WHERE name='A';（未提交）
T3		SELECT balance FROM account WHERE name='A'; → 800
T4	ROLLBACK;（回滚，实际仍是 1000）
T5		B 已基于 800 做了业务决策 → 数据错误

结论：只有 READ UNCOMMITTED 隔离级别会出现脏读，因此该级别生产从不使用。把级别改为 READ COMMITTED 后重做本实验，T3 读到的将是 1000。

4.4.5 实验二：不可重复读（Non-repeatable Read）复现

准备：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;（两个会话都要设，或改 global 后重连）。

时刻	会话 A（报表统计）	会话 B（业务修改）
T1	BEGIN;
T2	SELECT balance FROM account WHERE name='A'; → 1000
T3		UPDATE account SET balance=800 WHERE name='A'; COMMIT;
T4	SELECT balance FROM account WHERE name='A'; → 800
T5	同一事务两次读到不同值 → 报表前后不一致

侧重已存在行的 UPDATE。READ COMMITTED 及以下会出现，REPEATABLE READ 通过 MVCC 解决。

对照实验：把 A 会话改成 REPEATABLE READ 再重做，T4 仍然读到 1000（读的是 T2 建立的快照）——这就是「可重复读」的含义。

4.4.6 实验三：幻读（Phantom Read）复现

准备：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

时刻	会话 A	会话 B
T1	BEGIN;
T2	SELECT COUNT(*) FROM account WHERE balance>500; → 2 行
T3		INSERT INTO account (name,balance) VALUES ('E',900); COMMIT;
T4	SELECT COUNT(*) FROM account WHERE balance>500; → 3 行，多出一条「幻影行」

侧重新增/删除行导致的行数变化。

InnoDB 在 REPEATABLE READ 下的验证：

时刻	会话 A（RR）	会话 B
T1	BEGIN; SELECT COUNT(*) FROM account WHERE balance>500; → 2
T2		INSERT INTO account (name,balance) VALUES ('E',900); COMMIT;
T3	SELECT COUNT(*) FROM account WHERE balance>500; → 仍然 2（快照读，无幻读）
T4	SELECT COUNT(*) FROM account WHERE balance>500 FOR UPDATE; → 3（当前读，能看到最新数据）
T5	若 T1 时就执行了 FOR UPDATE，会话 B 的 INSERT 会被间隙锁阻塞，从根本上杜绝幻读

这组对比同时演示了快照读与当前读的差别，也是「RR 下加了锁定读才真正防幻读」的直接证据。

4.4.7 实验四：串行化（SERIALIZABLE）

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

时刻	会话 A	会话 B
T1	BEGIN; SELECT * FROM account WHERE name='A';（普通 SELECT 也隐式加 S 锁）
T2		UPDATE account SET balance=800 WHERE name='A'; → 阻塞，直到 A 提交或 innodb_lock_wait_timeout 超时
T3	COMMIT;	阻塞解除，UPDATE 成功

结论：SERIALIZABLE 把所有普通 SELECT 都变成加锁读，三类问题全部消失，但并发能力极低，只用于极少数强一致场景。

4.5 MVCC 原理

按 SQL 标准，REPEATABLE READ 无法防止幻读，只有 SERIALIZABLE 才行。但 InnoDB 的 RR 通过两套机制基本消除了幻读：

读类型	语句	机制
快照读（一致性非锁定读）	普通 SELECT	MVCC：事务第一条 SELECT 时建立 Read View，之后一直读这个快照，别人提交的新增/修改都看不到 → 无幻读
当前读（锁定读）	SELECT ... FOR UPDATE / LOCK IN SHARE MODE / UPDATE / DELETE / INSERT	Next-Key Lock（记录锁 + 间隙锁）：锁住命中范围及其间隙，阻止别人在范围内插入 → 无幻读

4.5.1 版本链

每行数据除用户列外，还有三个隐藏列：

隐藏列	作用
DB_TRX_ID	最后一次修改该行的事务 ID（6 字节）
DB_ROLL_PTR	回滚指针，指向 undo log 中该行的上一个版本（7 字节）
DB_ROW_ID	无主键时隐式生成的行号（6 字节）

每次 UPDATE 都把旧值写入 undo log，并用 DB_ROLL_PTR 把新旧版本串成版本链：

最新版本 (trx_id=105) --DB_ROLL_PTR--> 旧版本 (trx_id=102) --> 更旧版本 (trx_id=98) --> ...
   balance=600                            balance=800              balance=1000

4.5.2 ReadView（一致性视图）

ReadView 是「快照」的实现，包含四个要素：

要素	含义
m_ids	创建 ReadView 时所有活跃（未提交）事务的 ID 列表
min_trx_id	m_ids 中的最小值
max_trx_id	下一个将要分配的事务 ID
creator_trx_id	创建该 ReadView 的事务自身 ID

可见性判断规则（沿版本链从新到旧逐个判断行的 DB_TRX_ID）：

若 trx_id == creator_trx_id          → 可见（自己改的）
若 trx_id <  min_trx_id              → 可见（创建视图前已提交）
若 trx_id >= max_trx_id              → 不可见（创建视图之后才开启的事务）
若 min_trx_id <= trx_id < max_trx_id：
     在 m_ids 中  → 不可见（当时还未提交）→ 沿 DB_ROLL_PTR 找更旧版本
     不在 m_ids 中 → 可见（当时已提交）

RR 与 RC 的实现差别只在ReadView 的创建时机这一处：

级别	Read View 创建时机
REPEATABLE READ	事务内第一次快照读时创建一次，整个事务复用 → 可重复读
READ COMMITTED	每条 SELECT 都重新创建 → 每次都能看到最新已提交数据 → 不可重复读

因此读不加锁，读写互不阻塞，这是 InnoDB 高并发的关键。

4.5.3 快照读与当前读

类型	语句	读到什么	是否加锁
快照读	SELECT ...	ReadView 对应的历史版本	不加锁
当前读	SELECT ... FOR UPDATE	最新已提交版本	加 X 锁
当前读	SELECT ... LOCK IN SHARE MODE	最新已提交版本	加 S 锁
当前读	UPDATE / DELETE / INSERT	最新已提交版本	加 X 锁

-- 经典坑：RR 下先快照读判断，再更新，可能基于过期数据决策
BEGIN;
SELECT stock FROM goods WHERE id=1;             -- 快照读：可能是旧值
UPDATE goods SET stock=stock-1 WHERE id=1;      -- 当前读：基于最新值，且 stock-1 是原子的
COMMIT;

-- 正确做法：判断也用当前读
BEGIN;
SELECT stock FROM goods WHERE id=1 FOR UPDATE;  -- 当前读 + X 锁
-- 业务判断 stock > 0
UPDATE goods SET stock=stock-1 WHERE id=1;
COMMIT;

生产选型：MySQL 默认 RR 主要是历史原因（早期 STATEMENT 格式的 binlog 在 RC 下会导致主从数据不一致）。如今用 ROW 格式 binlog 后，很多互联网公司改用 RC，因为 RC 的间隙锁范围小、死锁概率低、并发更高。若要改，必须确保 binlog_format=ROW。

4.6 锁机制

4.6.1 按粒度分

锁	粒度	引擎	特点
全局锁	整个实例	全部	FLUSH TABLES WITH READ LOCK，全库只读
表锁	整张表	MyISAM、InnoDB（DDL/显式）	开销小、加锁快、并发极差
元数据锁 MDL	表结构	全部	自动加，读写 DML 加 MDL 读锁，DDL 加 MDL 写锁
行锁	单行记录	InnoDB	开销大、并发好；必须通过索引命中，否则退化为锁全表所有行
页锁	数据页	BDB（已废弃）	折中

全局锁（物理备份、切主时使用）：

FLUSH TABLES WITH READ LOCK;      -- 全库只读，简称 FTWRL：阻塞所有写入与 DDL
-- ... 做快照 / 记录 binlog 位点 ...
UNLOCK TABLES;                     -- 会话断开也会自动释放

FTWRL 期间整库不可写，业务等于停摆，只能在维护窗口用。逻辑备份优先用 mysqldump --single-transaction（依赖 MVCC，不锁表），详见 11-05-备份与恢复.md。

显式表锁（一般只在特殊维护场景使用）：

LOCK TABLES emp READ;             -- 读锁：自己和别人都可读，谁都不能写
LOCK TABLES emp WRITE;            -- 写锁：只有自己能读写
LOCK TABLES emp READ, dept WRITE; -- 一次锁多张表（未列出的表在本会话不可访问）
UNLOCK TABLES;

元数据锁（MDL）：

• 任何 DML（含 SELECT）自动持有该表的 MDL 读锁，读锁之间兼容；
• 任何 DDL（ALTER、DROP、TRUNCATE）需要 MDL 写锁，与读锁互斥；
• 事务未提交则 MDL 一直持有到事务结束 → 一个未提交的长事务会把后面的 DDL 卡住，DDL 又把它后面的所有 DML 卡住，形成雪崩，PROCESSLIST 中表现为大量 Waiting for table metadata lock。

-- 处置流程
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;   -- 找最早的长事务
KILL <trx_mysql_thread_id>;                                          -- 杀掉它
-- 8.0 可查 MDL 持有者（需开启 instrument）
SELECT * FROM performance_schema.metadata_locks;

-- DDL 加超时保护，避免无限等待
SET lock_wait_timeout = 5;      -- 只影响 MDL 等待，单位秒
ALTER TABLE emp ADD KEY idx_x (sal);

4.6.2 行锁必须命中索引（关键陷阱）

InnoDB 的行锁是加在索引上的。若 UPDATE 的 WHERE 条件用不到索引，InnoDB 只能扫描全部行并逐行加锁，效果等同锁表：

-- ename 无索引 → 全表扫描 → 锁住表中所有行，其他会话的任何 UPDATE 都被阻塞
UPDATE emp SET sal=sal+100 WHERE ename='wangwu';

-- 加索引后 → 只锁命中的那一行
ALTER TABLE emp ADD KEY idx_ename (ename);

验证方法：

-- 会话 A
BEGIN;
UPDATE emp SET sal=sal+100 WHERE ename='wangwu';   -- ename 无索引

-- 会话 B（更新完全无关的另一行）
UPDATE emp SET sal=sal+1 WHERE empno=999999;       -- 被阻塞 → 证明锁范围是全表
-- 加上 idx_ename 后重做，会话 B 立即成功

推论：索引优化同时也是锁优化。缺索引的 UPDATE/DELETE 在高并发下必然引发锁风暴。

4.6.3 按性质分：共享锁与排他锁

锁	简称	加锁语句	兼容性
共享锁（读锁）	S	SELECT ... LOCK IN SHARE MODE（8.0: FOR SHARE）	S 与 S 兼容，S 与 X 互斥
排他锁（写锁）	X	SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT	X 与任何锁互斥

兼容矩阵：

请求 \ 已持有	S	X
S	兼容	冲突
X	冲突	冲突

-- 典型用法：先锁住再改，防止并发超卖
BEGIN;
SELECT stock FROM goods WHERE id=1 FOR UPDATE;   -- 加 X 锁，其他事务在此阻塞
-- 判断 stock > 0
UPDATE goods SET stock = stock - 1 WHERE id=1;
COMMIT;                                           -- 提交后才释放锁

-- 不加锁的写法有并发 bug：两个会话可能同时读到 stock=1，各减 1 → 变成 -1

-- NOWAIT / SKIP LOCKED（MySQL 8.0+ / MariaDB 10.6+），避免长时间阻塞
SELECT * FROM goods WHERE id=1 FOR UPDATE NOWAIT;       -- 拿不到锁立即报错
SELECT * FROM task WHERE status=0 LIMIT 10 FOR UPDATE SKIP LOCKED;  -- 跳过已锁行，适合任务队列

注意：LOCK IN SHARE MODE 容易引发死锁——两个事务各持 S 锁再同时想升级为 X 锁，必然互等。除非确有共享读需求，优先直接用 FOR UPDATE。

4.6.4 意向锁（Intention Lock）

意向锁是表级锁，由 InnoDB 自动加，用户无需干预。作用是让表锁能快速判断「表里是否已有行锁」，而不必逐行检查。

锁	含义
IS（意向共享锁）	事务准备给表中某些行加 S 锁
IX（意向排他锁）	事务准备给表中某些行加 X 锁

规则：加行级 S 锁前自动先加表级 IS，加行级 X 锁前自动先加表级 IX。IS/IX 之间互相兼容（都只是「意向」），但都与表级 S/X 锁互斥。这样 LOCK TABLES emp WRITE 只需检查表上有无 IS/IX，即可 O(1) 判断是否要等待。

4.6.5 记录锁、间隙锁与临键锁

InnoDB 在 RR 级别下，锁定读会用三种行锁：

锁	英文	锁定范围
记录锁	Record Lock	命中的索引记录本身
间隙锁	Gap Lock	索引记录之间的开区间，不含记录本身，用于阻止插入
临键锁	Next-Key Lock	记录锁 + 前面的间隙锁 = 左开右闭区间，RR 下的默认行为

示例：account 表 id 为主键，现有 id = 1, 5, 10。

-- 会话 A
BEGIN;
SELECT * FROM account WHERE id BETWEEN 3 AND 8 FOR UPDATE;

InnoDB 锁定的临键区间约为 (1,5] 和 (5,10]，于是：

会话 B 的操作	结果
INSERT INTO account VALUES (6,...)	阻塞（6 落在间隙 (5,10] 中）
INSERT INTO account VALUES (2,...)	阻塞（2 落在间隙 (1,5] 中）
UPDATE ... WHERE id=5	阻塞（记录锁）
INSERT INTO account VALUES (11,...)	成功（超出锁定范围）
SELECT * FROM account WHERE id=5（普通读）	成功（快照读不加锁）

间隙锁的重要特性与影响：

• 间隙锁只在 RR 级别生效；改为 RC 后间隙锁基本消失（外键检查与唯一键冲突检测除外），这是 RC 死锁更少的原因。
• 间隙锁之间互相兼容（两个事务可同时持有同一间隙的 GAP 锁），但都会阻止对方插入 → 这是死锁的高频来源。
• 等值查询命中唯一索引时，临键锁退化为记录锁（无需防插入）；未命中时退化为纯间隙锁。
• 关闭间隙锁：SET GLOBAL innodb_locks_unsafe_for_binlog=1（5.7 已废弃，不建议），正确做法是改用 RC。

4.6.6 锁等待的排查

-- MySQL 5.7 / MariaDB
SELECT * FROM information_schema.innodb_locks;        -- 当前锁
SELECT * FROM information_schema.innodb_lock_waits;   -- 锁等待关系
SELECT * FROM information_schema.innodb_trx;          -- 活跃事务

-- 一条 SQL 看清「谁阻塞了谁」（5.7）
SELECT r.trx_id AS 等待事务, r.trx_mysql_thread_id AS 等待线程, r.trx_query AS 等待SQL,
       b.trx_id AS 阻塞事务, b.trx_mysql_thread_id AS 阻塞线程, b.trx_query AS 阻塞SQL
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- MySQL 8.0 改为（innodb_locks / innodb_lock_waits 已移除）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 8.0 的现成视图，最直观
SELECT * FROM sys.innodb_lock_waits\G

配套状态变量：

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_current_waits 非 0 = 此刻正有事务在等锁
-- Innodb_row_lock_time_avg 偏大    = 锁竞争严重

4.7 死锁

死锁是两个事务各持有对方需要的锁，互相等待。InnoDB 有死锁检测（innodb_deadlock_detect=ON），会主动回滚代价较小的那个事务，另一个继续执行。

4.7.1 可复现的死锁示例

-- 准备
CREATE TABLE dl (id INT PRIMARY KEY, v INT) ENGINE=InnoDB;
INSERT INTO dl VALUES (1,0),(2,0);

两个会话交叉加锁，顺序相反：

时刻	会话 A	会话 B
T1	BEGIN;	BEGIN;
T2	UPDATE dl SET v=1 WHERE id=1;（持有 id=1 的 X 锁）
T3		UPDATE dl SET v=1 WHERE id=2;（持有 id=2 的 X 锁）
T4	UPDATE dl SET v=2 WHERE id=2; → 等待 B 释放 id=2
T5		UPDATE dl SET v=2 WHERE id=1; → 等待 A 释放 id=1
T6	循环等待形成，InnoDB 检测到死锁	立即报错：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
T7	A 继续执行，可 COMMIT	B 的事务已被整体回滚

4.7.2 查看死锁日志

SHOW ENGINE INNODB STATUS\G

在输出中找 LATEST DETECTED DEADLOCK 段落：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-08-22 10:30:15 0x7f8c1c0f5700
*** (1) TRANSACTION:
TRANSACTION 3512, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 21, query id 512 localhost root updating
UPDATE dl SET v=2 WHERE id=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 45 page no 3 n bits 72 index PRIMARY of table `shop`.`dl`
trx id 3512 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 3513, ACTIVE 5 sec starting index read
*** (2) HOLDS THE LOCK(S):  ... lock_mode X ...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ... index PRIMARY of table `shop`.`dl` ...
*** WE ROLL BACK TRANSACTION (2)

读日志的四个关键点：① 两个事务各自执行的 SQL；② HOLDS THE LOCK(S) 已持有什么；③ WAITING FOR THIS LOCK 在等什么；④ WE ROLL BACK TRANSACTION (n) 谁被牺牲。据此就能还原加锁顺序并改代码。

锁模式关键字的读法：

关键字	含义
lock_mode X locks rec but not gap	纯记录锁
lock_mode X locks gap before rec	纯间隙锁
lock_mode X（无后缀）	临键锁（记录 + 间隙）
lock mode S	共享锁
lock_mode X insert intention	插入意向锁（等待间隙释放）

# 把每次死锁都写进错误日志，便于事后追查（生产建议开启）
[mysqld]
innodb_print_all_deadlocks=1

4.7.3 锁等待超时

死锁会被立即检测，但普通锁等待只会超时：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';   -- 默认 50 秒
SET GLOBAL innodb_lock_wait_timeout = 10;          -- 建议调小到 5~10 秒，快速失败优于长时间挂住
SHOW VARIABLES LIKE 'innodb_deadlock_detect';      -- ON：开启死锁检测（超高并发下可考虑关闭）

超时报错：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

错误码	含义	事务状态
1213	死锁，被自动回滚	整个事务已回滚
1205	锁等待超时	只有当前语句失败，事务仍在，需应用显式 ROLLBACK

这个区别常被忽略：遇到 1205 若应用不回滚，事务会一直挂着持有锁，把问题扩散。

4.7.4 避免死锁的实践

实践	说明
统一加锁顺序	最有效的一条。所有代码路径按同一顺序访问表和行（例如始终按主键升序更新），死锁基本消失
缩短事务	事务内只放必要的 DB 操作，把 RPC 调用、发短信、文件上传等移到事务外
避免大事务	一次 UPDATE 百万行会锁大量记录。改为分批：WHERE id BETWEEN ? AND ? LIMIT 1000 循环处理并及时提交
让 SQL 走索引	无索引的 UPDATE 锁全部行，死锁概率飙升（见 4.6.2）
降低隔离级别	RR → RC 可消除大部分间隙锁死锁（需 binlog_format=ROW）
一次性锁定所需资源	能用一条 SQL 完成的不要拆成多条
应用层重试	死锁是并发系统的正常现象，捕获 1213 后重试整个事务（注意幂等），而非视为致命错误
热点行拆分	秒杀等场景把一行库存拆成 N 行分片，或用 Redis 前置扣减

-- 大事务改为分批的示例
SET autocommit=1;
-- 循环执行，直到影响行数为 0
DELETE FROM log_table WHERE create_time < '2025-01-01' LIMIT 2000;
SELECT ROW_COUNT();

---

五、视图、存储过程与练习

5.1 视图

5.1.1 什么是视图

视图是基于一个或多个表（或其他视图）查询结果的虚拟表，本身不存储数据，本质上是一条保存在数据库中的 select 语句。

5.1.2 视图的作用

优点	说明
简单	使用视图的用户无需关心底层表结构和关联条件，所见即所需
安全	视图使用者只能看到被授权的数据；普通权限控制只能到表级别，视图可精确到列级别
数据独立	视图结构确定后，源表增加列对视图无影响；源表改列名可通过修改视图解决，不影响访问者

总结：使用视图是为了提高数据安全性和查询效率。

缺点与代价：

缺点	说明
性能不可控	复杂视图（尤其嵌套视图）可能生成低效执行计划，且难以加 hint 优化
更新受限	多数复杂视图不可更新（见 5.1.6）
维护成本	视图依赖源表结构，源表变更需同步维护；视图套视图后排查困难
掩盖复杂度	开发者以为在查一张「表」，实际是多表 JOIN，容易写出慢查询

5.1.3 创建视图的语法

create view 视图名称 (视图字段) as select语句;

完整语法：

CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  VIEW 视图名 [(列名列表)]
  AS select 语句
  [WITH [CASCADED | LOCAL] CHECK OPTION];

删除视图：

drop view 视图名称;
DROP VIEW IF EXISTS v_emp_dept;

查看与修改视图：

SHOW CREATE VIEW v_emp_dept\G          -- 查看定义
SHOW FULL TABLES WHERE table_type='VIEW';   -- 列出当前库所有视图
SELECT table_name, view_definition, is_updatable
FROM information_schema.views WHERE table_schema='shop';

ALTER VIEW v_emp_dept AS SELECT ...;   -- 修改视图定义
CREATE OR REPLACE VIEW v_emp_dept AS SELECT ...;   -- 不存在则建，存在则替换

5.1.4 基于单表的视图

创建物理表：

create table a (
  id int,
  name varchar(20),
  sex char(1),
  phone int,
  address varchar(20),
  pos varchar(20)
);

+------+----------+------+------------+---------+--------+
| id   | name     | sex  | phone      | address | pos    |
+------+----------+------+------------+---------+--------+
|    1 | zhangsan | m    | 1888888888 | gz      | cook   |
|    1 | cuihua   | f    | 1888888888 | sz      | waiter |
|    1 | lisi     | f    | 1886666666 | sz      | boss   |
+------+----------+------+------------+---------+--------+

基于表 a 创建视图 b，只暴露 name、sex、phone 三列：

create view b(name, sex, phone)
as
select name, sex, phone from a;

查询视图：

select * from b;

+----------+------+------------+
| name     | sex  | phone      |
+----------+------+------------+
| zhangsan | m    | 1888888888 |
| cuihua   | f    | 1888888888 |
| lisi     | f    | 1886666666 |
+----------+------+------------+

这正是视图「安全」价值的体现：把 address、pos 等敏感列挡在视图之外，只给使用者授权视图 b 的 SELECT 权限，即实现了列级权限控制。

5.1.5 基于多表的视图

准备两张物理表：

create table emp (
  empno int(1), ename char(20), sex char(1),
  birthday date, hiredate date,
  sal decimal(10,2), deptno tinyint(1), managerno tinyint
);

create table emp1 (
  deptno tinyint(1), dname char(20), loc char(5)
);

插入数据：

insert into emp values
(1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),
(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'),
(3,'lisi','f','1973-01-28','1998-11-21','13000','3','1'),
(4,'wangwu','f','1975-06-03','1999-12-12','12000','4','1'),
(5,'maliu','m','1982-08-18','2001-07-03','8000','2','2'),
(6,'tianqi','f','1983-02-15','2002-11-01','7000','2','2'),
(7,'mark','m','1984-08-12','2003-10-02','6500','3','3'),
(8,'john','m','1985-09-14','2005-04-03','6000','3','3'),
(9,'mm','f','1990-06-08','2008-09-13','4000','4','4');

insert into emp1 values
('1','cook','bj'),('2','message','sz'),
('3','cook','gz'),('4','secretary','lz');

两表数据如下：

-- emp
+-------+----------+------+------------+------------+----------+--------+-----------+
| empno | ename    | sex  | birthday   | hiredate   | sal      | deptno | managerno |
+-------+----------+------+------------+------------+----------+--------+-----------+
|     1 | boss     | m    | 1964-08-08 | 1995-01-01 | 20000.00 |      1 |         1 |
|     2 | zhangsan | m    | 1967-04-05 | 1995-04-11 | 15000.00 |      2 |         1 |
|     3 | lisi     | f    | 1973-01-28 | 1998-11-21 | 13000.00 |      3 |         1 |
|     4 | wangwu   | f    | 1975-06-03 | 1999-12-12 | 12000.00 |      4 |         1 |
|     5 | maliu    | m    | 1982-08-18 | 2001-07-03 |  8000.00 |      2 |         2 |
|     6 | tianqi   | f    | 1983-02-15 | 2002-11-01 |  7000.00 |      2 |         2 |
|     7 | mark     | m    | 1984-08-12 | 2003-10-02 |  6500.00 |      3 |         3 |
|     8 | john     | m    | 1985-09-14 | 2005-04-03 |  6000.00 |      3 |         3 |
|     9 | mm       | f    | 1990-06-08 | 2008-09-13 |  4000.00 |      4 |         4 |
+-------+----------+------+------------+------------+----------+--------+-----------+

-- emp1
+--------+-----------+------+
| deptno | dname     | loc  |
+--------+-----------+------+
|      1 | cook      | bj   |
|      2 | message   | sz   |
|      3 | cook      | gz   |
|      4 | secretary | lz   |
+--------+-----------+------+

需求 1：得到员工姓名、工资、部门号及部门位置

期望结果：

+----------+----------+--------+------+
| ename    | sal      | deptno | loc  |
+----------+----------+--------+------+
| boss     | 20000.00 |      1 | bj   |
| zhangsan | 15000.00 |      2 | sz   |
| lisi     | 13000.00 |      3 | gz   |
| wangwu   | 12000.00 |      4 | lz   |
| maliu    |  8000.00 |      2 | sz   |
| tianqi   |  7000.00 |      2 | sz   |
| mark     |  6500.00 |      3 | gz   |
| john     |  6000.00 |      3 | gz   |
| mm       |  4000.00 |      4 | lz   |
+----------+----------+--------+------+

核心查询：

select a.ename, a.sal, b.deptno, b.loc
from emp a
inner join emp1 b on a.deptno = b.deptno;

基于该查询创建视图：

create view v_emp_dept(ename, sal, deptno, loc)
as
select a.ename, a.sal, b.deptno, b.loc
from emp a
inner join emp1 b on a.deptno = b.deptno;

需求 2：只查询女员工信息

在视图基础上或直接在查询中加条件：

select a.ename, a.sal, b.deptno, b.loc
from emp a
inner join emp1 b on a.deptno = b.deptno
where a.sex = 'f';

-- 也可以直接查已有视图（视图上可继续加 WHERE，但视图里没有 sex 列，需先把 sex 加入视图）
select * from v_emp_dept where deptno=2;

删除视图：

drop view v_emp_dept;

5.1.6 视图算法：MERGE 与 TEMPTABLE

算法	行为	性能	可更新
MERGE	把视图定义合并进外层查询后再执行（等价于手写 JOIN）	好，能用上底层索引	可能可更新
TEMPTABLE	先把视图结果物化成临时表，再在临时表上执行外层条件	差，外层 WHERE 无法下推，索引失效	不可更新
UNDEFINED（默认）	由 MySQL 自行选择，能 MERGE 就 MERGE	—	—

CREATE ALGORITHM=MERGE VIEW v_high_sal AS
  SELECT empno, ename, sal FROM emp WHERE sal > 10000;

CREATE ALGORITHM=TEMPTABLE VIEW v_dept_avg AS
  SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno;

以下情况只能用 TEMPTABLE（写了 MERGE 也会被降级并给出警告）：

• 视图中含聚合函数（SUM、AVG、COUNT、MIN、MAX）；
• 含 DISTINCT、GROUP BY、HAVING；
• 含 UNION / UNION ALL；
• 含子查询在选择列表中；
• 含 LIMIT；
• 只引用了字面值（FROM 后无真实表）。

-- 验证视图用了什么算法：EXPLAIN 中出现 <derived2> 即为 TEMPTABLE
EXPLAIN SELECT * FROM v_dept_avg WHERE deptno=20;
SHOW WARNINGS;

性能要点：TEMPTABLE 视图上的 WHERE 条件无法下推到底层表，等于先算出全量结果再过滤。大表上的聚合视图是典型的慢查询来源，生产中应改为定时任务写入汇总表。

5.1.7 WITH CHECK OPTION

WITH CHECK OPTION 要求通过视图执行的 INSERT / UPDATE 必须仍满足视图的 WHERE 条件，否则报错，防止「插进去就看不见」的数据。

CREATE VIEW v_dept20 AS
  SELECT empno, ename, sal, deptno FROM emp WHERE deptno=20
  WITH CHECK OPTION;

INSERT INTO v_dept20 (ename, sal, deptno) VALUES ('newman', 8000, 20);  -- 成功
INSERT INTO v_dept20 (ename, sal, deptno) VALUES ('other', 8000, 30);
-- ERROR 1369 (HY000): CHECK OPTION failed 'shop.v_dept20'

UPDATE v_dept20 SET deptno=30 WHERE ename='newman';
-- ERROR 1369：改完就不满足视图条件，被拒绝

CASCADED 与 LOCAL（视图套视图时的检查范围）：

选项	含义
CASCADED（默认）	同时检查本视图和所有底层视图的条件
LOCAL	只检查本视图的条件，底层视图条件不检查

CREATE VIEW v1 AS SELECT * FROM emp WHERE sal > 5000;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE deptno=20 WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE deptno=20 WITH CASCADED CHECK OPTION;

INSERT INTO v2 (ename,sal,deptno) VALUES ('x', 100, 20);   -- 成功（不检查 v1 的 sal>5000）
INSERT INTO v3 (ename,sal,deptno) VALUES ('y', 100, 20);   -- 失败（级联检查 sal>5000）

5.1.8 可更新视图的限制

视图可更新（能对其执行 INSERT/UPDATE/DELETE）的前提是视图行与基表行存在一一对应关系。以下任一情况会使视图不可更新：

不可更新的原因	说明
含聚合函数	SUM、AVG、COUNT、MIN、MAX
含 GROUP BY / HAVING / DISTINCT	多行被压缩成一行，无法反推
含 UNION / UNION ALL	无法确定目标表
含子查询在选择列表中
ALGORITHM=TEMPTABLE	物化后与基表脱钩
FROM 后是不可更新的视图	逐层传递
只有字面值，无基表
多表连接视图的 INSERT	多表视图可以 UPDATE 单表的列，但不能 INSERT（除非只涉及一张表）
目标列是计算列	如 sal*12 AS year_sal，该列不可写
INSERT 时基表有 NOT NULL 且无默认值的列未包含在视图中	插入必然失败

-- 检查视图是否可更新
SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema='shop';

实践建议：视图只用于读。生产中把视图当成「保存好的查询」和「列级权限工具」，写操作一律直接对基表执行，避免可更新视图的种种隐式规则。

5.2 存储过程与函数

5.2.1 DELIMITER 与存储函数

存储过程/函数体内有分号，需要先把语句结束符改掉，否则 MySQL 会在第一个分号处就提交。

创建随机字符串函数：

delimiter $$
drop function if exists rand_string $$

create function rand_string(n INT)
returns varchar(255)
begin
  declare chars_str varchar(100) default
    'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  declare return_str varchar(255) default '';
  declare i int default 0;
  while i < n do
    set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
    set i = i + 1;
  end while;
  return return_str;
end $$

delimiter ;

产生 6 位随机字符串：

select rand_string(6);

创建随机数字函数：

delimiter $$

create function rand_num()
returns int(5)
begin
  declare i int default 0;
  set i = floor(10+rand()*500);
  return i;
end $$

delimiter ;

select rand_num();

若报 ERROR 1418（函数未声明为确定性且启用了二进制日志），需加特性声明或临时放开：

SET GLOBAL log_bin_trust_function_creators = 1;   -- 临时；持久化写 my.cnf
-- 或在函数定义中声明：DETERMINISTIC / NO SQL / READS SQL DATA

5.2.2 存储过程与参数 IN / OUT / INOUT

参数模式	含义
IN（默认）	输入参数，过程内修改不影响调用者
OUT	输出参数，过程内赋值后返回给调用者（调用时需传变量）
INOUT	既输入又输出

DELIMITER $$
CREATE PROCEDURE p_dept_stat(
  IN    p_deptno TINYINT,
  OUT   p_cnt    INT,
  INOUT p_bonus  DECIMAL(10,2)
)
BEGIN
  -- 变量声明必须写在 BEGIN 之后、其他语句之前
  DECLARE v_avg DECIMAL(10,2) DEFAULT 0;

  SELECT COUNT(*), IFNULL(AVG(sal),0) INTO p_cnt, v_avg
  FROM emp WHERE deptno = p_deptno;

  -- 流程控制：IF / ELSEIF / ELSE
  IF v_avg > 15000 THEN
    SET p_bonus = p_bonus * 2;
  ELSEIF v_avg > 8000 THEN
    SET p_bonus = p_bonus * 1.5;
  ELSE
    SET p_bonus = p_bonus;
  END IF;

  -- CASE 分支
  CASE
    WHEN p_cnt = 0 THEN SELECT '空部门' AS msg;
    WHEN p_cnt < 5 THEN SELECT '小部门' AS msg;
    ELSE                SELECT '大部门' AS msg;
  END CASE;
END $$
DELIMITER ;

-- 调用
SET @bonus = 1000;
CALL p_dept_stat(20, @cnt, @bonus);
SELECT @cnt, @bonus;

三种循环写法：

DELIMITER $$
CREATE PROCEDURE p_loops()
BEGIN
  DECLARE i INT DEFAULT 0;

  -- ① WHILE
  WHILE i < 3 DO
    SET i = i + 1;
  END WHILE;

  -- ② REPEAT ... UNTIL（先执行后判断）
  SET i = 0;
  REPEAT
    SET i = i + 1;
  UNTIL i >= 3
  END REPEAT;

  -- ③ LOOP + LEAVE / ITERATE
  SET i = 0;
  my_loop: LOOP
    SET i = i + 1;
    IF i < 2 THEN ITERATE my_loop; END IF;   -- 相当于 continue
    IF i >= 3 THEN LEAVE  my_loop; END IF;   -- 相当于 break
  END LOOP my_loop;

  SELECT i;
END $$
DELIMITER ;

5.2.3 实用示例：批量造数存储过程

创建 emp 表批量插入存储过程（配合 5.2.1 的两个随机函数）：

delimiter $$

create procedure insert_emp(in start int(10), in max_num int(10))
begin
  declare i int default 0;
  set autocommit = 0;
  repeat
    set i = i + 1;
    insert into emp values ((start+i), rand_string(6), 'SALESMAN', 0001, curdate(), 2000, 400, rand_num());
  until i = max_num
  end repeat;
  commit;
end $$

delimiter ;

调用存储过程，从 100001 开始插入 1800000 条记录：

call insert_emp(100001, 1800000);

创建 dept 表批量插入存储过程：

delimiter $$
drop procedure if exists insert_dept $$

create procedure insert_dept(in start int(10), in max_num int(10))
begin
  declare i int default 0;
  set autocommit = 0;
  repeat
    set i = i + 1;
    insert into dept values ((start+i), rand_string(10), rand_string(8));
  until i = max_num
  end repeat;
  commit;
end $$

delimiter ;

call insert_dept(100, 10);

注意：这两个过程内 set autocommit=0 + 最后一次性 commit，等于一个 180 万行的大事务，造数时可接受（快），但生产业务代码绝不能这样写（undo 膨胀、锁范围巨大，见 4.7.4）。

5.2.4 游标（CURSOR）

游标用于在存储过程中逐行处理查询结果。

DELIMITER $$
CREATE PROCEDURE p_cursor_demo()
BEGIN
  DECLARE v_empno INT;
  DECLARE v_sal   DECIMAL(10,2);
  DECLARE done    INT DEFAULT 0;

  -- ① 声明游标（必须在变量声明之后、处理程序之前）
  DECLARE cur_emp CURSOR FOR SELECT empno, sal FROM emp WHERE deptno=20;
  -- ② 声明结束标志处理程序
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  -- ③ 打开游标
  OPEN cur_emp;

  read_loop: LOOP
    -- ④ 取一行到变量
    FETCH cur_emp INTO v_empno, v_sal;
    IF done = 1 THEN LEAVE read_loop; END IF;

    -- 逐行处理逻辑
    IF v_sal < 8000 THEN
      UPDATE emp SET sal = v_sal * 1.1 WHERE empno = v_empno;
    END IF;
  END LOOP;

  -- ⑤ 关闭游标
  CLOSE cur_emp;
END $$
DELIMITER ;

CALL p_cursor_demo();

声明顺序硬性要求：DECLARE 变量 → DECLARE 游标 → DECLARE HANDLER，顺序错会报语法错误。

游标是逐行处理，性能远低于一条集合式 SQL。上例完全可以用 UPDATE emp SET sal=sal*1.1 WHERE deptno=20 AND sal<8000; 一条语句替代，能用集合操作就不要用游标。

5.2.5 查看、调用与删除

-- 调用
CALL insert_dept(100, 10);

-- 查看
SHOW PROCEDURE STATUS WHERE db='shop';
SHOW FUNCTION STATUS WHERE db='shop';
SHOW CREATE PROCEDURE insert_dept\G
SHOW CREATE FUNCTION rand_string\G
SELECT routine_name, routine_type, created
FROM information_schema.routines WHERE routine_schema='shop';

-- 删除
DROP PROCEDURE IF EXISTS insert_dept;
DROP FUNCTION  IF EXISTS rand_string;

-- 修改（只能改特性，不能改过程体；改逻辑必须 DROP 后重建）
ALTER PROCEDURE insert_dept COMMENT '批量造部门数据';

存储过程与函数的区别：

对比项	存储过程 PROCEDURE	函数 FUNCTION
调用方式	CALL p();	在 SQL 表达式中直接用：SELECT f(x);
返回值	通过 OUT 参数，可返回多个/结果集	必须 RETURNS 一个标量值
参数模式	IN / OUT / INOUT	只有 IN
能否含事务控制	可以（COMMIT/ROLLBACK）	一般不允许
典型用途	批量任务、复杂业务流程	数据加工、字段计算

生产权衡：存储过程把业务逻辑放进数据库，难以版本管理、难以调试、迁移成本高、占用数据库 CPU。互联网架构通常禁用或极少使用存储过程，只在数据初始化、造数、运维脚本等场景使用。

5.3 触发器

触发器是绑定在表上、由 DML 事件自动触发的存储程序。

CREATE TRIGGER 触发器名
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON 表名 FOR EACH ROW
  触发器体;

关键字	说明
BEFORE / AFTER	在事件前/后触发
INSERT / UPDATE / DELETE	触发事件（不支持 TRUNCATE，也不响应 SELECT）
FOR EACH ROW	行级触发，MySQL 只支持行级，影响 N 行就触发 N 次
NEW.列名	新值，可用于 INSERT、UPDATE（BEFORE 中可赋值修改）
OLD.列名	旧值，可用于 UPDATE、DELETE（只读）

示例：操作审计与数据校正

CREATE TABLE emp_log (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  empno INT, action VARCHAR(10),
  old_sal DECIMAL(10,2), new_sal DECIMAL(10,2),
  op_user VARCHAR(50), op_time DATETIME
) ENGINE=InnoDB;

DELIMITER $$

-- ① AFTER UPDATE：记录工资变更历史
CREATE TRIGGER trg_emp_upd AFTER UPDATE ON emp FOR EACH ROW
BEGIN
  IF OLD.sal <> NEW.sal THEN
    INSERT INTO emp_log (empno, action, old_sal, new_sal, op_user, op_time)
    VALUES (NEW.empno, 'UPDATE', OLD.sal, NEW.sal, USER(), NOW());
  END IF;
END $$

-- ② BEFORE INSERT：数据校正/兜底默认值（BEFORE 中可以改 NEW）
CREATE TRIGGER trg_emp_ins BEFORE INSERT ON emp FOR EACH ROW
BEGIN
  IF NEW.sal < 0 THEN SET NEW.sal = 0; END IF;
  IF NEW.hiredate IS NULL THEN SET NEW.hiredate = CURDATE(); END IF;
END $$

-- ③ AFTER DELETE：归档被删数据
CREATE TRIGGER trg_emp_del AFTER DELETE ON emp FOR EACH ROW
BEGIN
  INSERT INTO emp_log (empno, action, old_sal, new_sal, op_user, op_time)
  VALUES (OLD.empno, 'DELETE', OLD.sal, NULL, USER(), NOW());
END $$

DELIMITER ;

查看与删除：

SHOW TRIGGERS\G
SHOW TRIGGERS FROM shop LIKE 'emp'\G
SELECT trigger_name, event_manipulation, action_timing, event_object_table
FROM information_schema.triggers WHERE trigger_schema='shop';

DROP TRIGGER IF EXISTS trg_emp_upd;

触发器的注意事项：

事项	说明
隐式开销	触发器与触发它的语句在同一个事务内，会拉长事务、扩大锁范围
排错困难	数据「莫名被改」时极难定位，务必在文档和代码评审中登记所有触发器
不支持 TRUNCATE	TRUNCATE TABLE 不触发 DELETE 触发器
报错即回滚	触发器内出错会导致原始语句一起失败回滚
不能调用返回结果集的过程	触发器体内不允许 SELECT 出结果集给客户端
与 pt-online-schema-change 冲突	该工具本身依赖触发器，表上已有同类触发器时会失败

生产建议：审计需求优先用 binlog + 解析工具（如 canal），而非触发器。

5.4 性能优化总结清单

影响数据库性能的因素（从上到下影响范围递减）：

1. 数据库或表设计是否合理：设计时一般遵循三大范式（有时适当逆范式效果更好）。
   • 第一范式：数据拥有原子性，字段不可再分（事务包含的操作要么全部成功要么全部失败回滚，关系型数据库均符合）。
   • 第二范式：在满足第一范式前提下，保证数据唯一性（某列内容不重复，设置主键，非主键列完全依赖主键）。
   • 第三范式：在满足第二范式前提下，消除传递依赖，表之间数据可通过关联查询（多表查询）获得。
2. SQL 语句优化：重点是 select 查询的优化。
3. 数据库配置：配置文件的合理配置。
4. 服务器硬件配置及操作系统类型。

SQL 语句分类：

分类	全称	命令	用途
DQL	数据查询语言	select	查询数据
DDL	数据定义语言	create、drop、alter、truncate、rename	对表结构操作
DML	数据操纵语言	insert、update、delete	对表内容操作
DCL	数据控制语言	grant、revoke	授权

5.4.1 第一层：表设计

原则	说明
必有主键	每张 InnoDB 表都要有主键，最好是自增 BIGINT UNSIGNED 或短整型
主键短且单调递增	主键是聚簇索引，过长会撑大所有二级索引；随机主键（如 UUID）造成页分裂
字段尽量 NOT NULL	NULL 使索引统计、比较、聚合都变复杂，用 0/'' 加 DEFAULT 代替
选最小够用的类型	状态用 TINYINT 而非 INT；定长短串用 CHAR，变长用 VARCHAR
金额禁用 FLOAT/DOUBLE	必须用 DECIMAL(10,2)，浮点有精度误差
字符集统一	全库统一 utf8mb4（需存 emoji）或 utf8，避免连接时字符集不一致导致索引失效
少用大字段	TEXT/BLOB 拆到独立表，避免影响主表行密度
适度反范式	高频 JOIN 的少量字段可冗余，用异步任务保证一致
冷热分离 / 归档	历史数据按时间归档或分区，控制单表在千万行级别

5.4.2 第二层：索引

原则	说明
只为高频查询建索引	依据慢日志与 pt-query-digest 统计，不靠猜
联合索引优于多个单列索引	遵循「等值列在左、范围列在右、区分度高的在左」
追求覆盖索引	目标是 Extra: Using index
索引数量 ≤ 5~6 个	写入放大与优化器成本都随索引数增长
区分度低的列不建	sex、status、is_deleted
长字符串用前缀索引	先算选择性再定长度
定期清理无用/冗余索引	sys.schema_unused_indexes、pt-duplicate-key-checker
大表加索引用在线 DDL	ALGORITHM=INPLACE, LOCK=NONE 或 pt-online-schema-change

5.4.3 第三层：SQL 写法

原则	说明
禁止 SELECT *	阻断覆盖索引，浪费网络与内存
不在索引列上做函数/运算	见 3.4
避免隐式类型转换	参数类型与列类型严格一致
LIKE 不用前导 %	后缀匹配上全文索引或 ES
深分页用游标或延迟关联	见 3.5.1
子查询改 JOIN	尤其避免 DEPENDENT SUBQUERY
NOT IN 改 NOT EXISTS / LEFT JOIN IS NULL	规避 NULL 陷阱
JOIN 用小表驱动大表	被驱动表关联列必须有索引，JOIN 表数量建议 ≤ 3
ORDER BY 配合索引与 LIMIT	消除 Using filesort
批量操作分批提交	每批 500~2000 行
事务尽量短小	只包含必要的 DB 操作
用 EXPLAIN 卡点上线	新 SQL 必须达到 range 及以上

5.4.4 第四层：参数配置

参数	建议	说明
innodb_buffer_pool_size	物理内存的 50%~70%	最重要的参数，目标命中率 ≥ 99%
innodb_buffer_pool_instances	8（缓冲池 > 8G 时）	降低内部争用
innodb_flush_log_at_trx_commit	1	生产必须，配合 sync_binlog=1（双 1）
innodb_log_file_size	1~2G	太小导致频繁 checkpoint
innodb_file_per_table	ON	独立表空间，便于回收空间
innodb_io_capacity	SSD 上 2000~8000	影响后台刷脏速度
max_connections	峰值连接的 1.3 倍	配合连接池，不宜盲目调到几万
wait_timeout / interactive_timeout	600~3600 秒	回收空闲连接
innodb_lock_wait_timeout	5~10 秒	快速失败优于长期挂住
sort_buffer_size / join_buffer_size	1~4M	每连接独占，调大需乘以连接数评估内存
tmp_table_size / max_heap_table_size	64~256M，两者设为相同值	降低磁盘临时表比例
slow_query_log / long_query_time	ON / 1	长期开启
transaction_isolation	REPEATABLE-READ 或 READ-COMMITTED	改 RC 需 binlog_format=ROW
character_set_server	utf8mb4	全链路统一

5.4.5 第五层：架构

手段	适用场景
缓存前置（Redis / 本地缓存）	读多写少、热点数据，减少 90% 以上的库压力
读写分离	读远多于写（详见 11-06-主从复制与读写分离.md），注意主从延迟带来的读旧数据问题
分库分表	单表超千万行、单库容量或连接达到瓶颈；优先垂直拆分（按业务）再水平拆分（按分片键）
表分区	按时间归档、便于整分区删除
队列削峰	秒杀、批量写入
冷热分离与归档	历史数据迁到归档库/数仓
引入 ES / ClickHouse	全文检索、复杂分析查询交给专用引擎

5.5 排错清单

5.5.1 慢查询定位流程

① 用户反馈慢 / 监控告警
   ↓
② SHOW FULL PROCESSLIST 按 Time 倒序 —— 现在有没有正在跑的慢 SQL？
   ↓
③ 慢查询日志 + mysqldumpslow / pt-query-digest —— 找出 TOP N 慢 SQL
   ↓
④ 对 TOP SQL 执行 EXPLAIN：
      type=ALL / index          → 缺索引或索引失效（见 3.4）
      Extra=Using filesort      → 排序无索引
      Extra=Using temporary     → GROUP BY/DISTINCT 无索引
      Extra=Using join buffer   → 被驱动表关联列无索引
      rows 与实际差距大          → ANALYZE TABLE 刷新统计信息
   ↓
⑤ 加索引 / 改写 SQL → 在测试库验证 → EXPLAIN 复核 → 上线
   ↓
⑥ 复核 SHOW GLOBAL STATUS：Handler_read_rnd_next、Slow_queries 是否下降

5.5.2 锁等待与死锁处理

现象：更新变慢、大量 Lock wait timeout（1205）或 Deadlock（1213）
   ↓
① SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;   -- 找最早的事务
② 5.7：查 innodb_lock_waits 关联 innodb_trx 看「谁阻塞了谁」
   8.0：SELECT * FROM sys.innodb_lock_waits;
③ 紧急止血：KILL 阻塞源的 trx_mysql_thread_id
④ 根因：SHOW ENGINE INNODB STATUS\G 看 LATEST DETECTED DEADLOCK，还原加锁顺序
⑤ 长期：统一加锁顺序、缩短事务、给 UPDATE/DELETE 的 WHERE 列加索引、考虑改 RC
⑥ 兜底：innodb_lock_wait_timeout 调到 5~10 秒；应用捕获 1213 后重试整个事务

5.5.3 连接数打满

报错：ERROR 1040 (HY000): Too many connections
   ↓
① 用管理员预留连接进入（root 有 1 个额外连接额度），或用 SUPER 权限账号
② SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';
③ 按来源 IP 统计连接，定位是哪台应用机泄漏（见 1.3 的 SQL）
④ 大量 Sleep → 应用连接池未释放/未设 maxIdle；调小 wait_timeout 回收
   大量 Query 且 Time 长 → 是慢查询堆积导致连接耗尽，本质是索引问题
⑤ 临时：SET GLOBAL max_connections=...（立即生效，勿忘同步 my.cnf）
⑥ 长期：应用侧上连接池并限制上限；数据库前置 ProxySQL/MaxScale 做连接复用

5.5.4 临时表与磁盘排序过多

指标：Created_tmp_disk_tables / Created_tmp_tables 偏高，Sort_merge_passes 持续增长
   ↓
① 找出对应 SQL：慢日志中带 Using temporary / Using filesort 的语句
② 优化 SQL：给 GROUP BY / ORDER BY 列建索引，避免 SELECT * 带上大字段
③ 检查是否有 TEXT/BLOB 列参与 —— 含大字段的临时表必然落磁盘
④ 调参兜底：tmp_table_size 与 max_heap_table_size 设为相同值并调大；sort_buffer_size 适度调大
⑤ 确认 tmpdir 所在磁盘性能与剩余空间

5.5.5 大事务的危害与处置

危害	说明
锁范围大、时间长	阻塞大量并发事务，引发连接堆积与雪崩
undo log 膨胀	长事务导致 purge 无法清理旧版本，ibdata1 只增不减（5.7 上无法回收）
MVCC 版本链变长	快照读需回溯更多版本，查询变慢
主从延迟	大事务在从库串行重放，延迟骤增
回滚极慢	回滚耗时可能超过执行耗时，且期间不释放锁；KILL 也要等回滚完成
binlog 单事务过大	传输与应用都成为瓶颈，可能超 max_allowed_packet

-- 监控长事务（建议做成告警，阈值 60 秒）
SELECT trx_id, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_sec,
       trx_rows_locked, trx_rows_modified, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY run_sec DESC;

-- 查看未提交事务持有的 undo 量
SELECT * FROM information_schema.innodb_metrics
WHERE name LIKE 'trx_rseg_history_len';   -- history list length 持续增长 = 有长事务

处置：定位 trx_mysql_thread_id → 通知业务方 → 必要时 KILL；代码层面拆分为小批次事务，并把非 DB 操作移出事务。

5.6 事务与视图小结

主题	关键点
事务前提	仅 InnoDB 支持；管理 DML 语句
ACID	原子性（undo log）、一致性、隔离性（锁 + MVCC）、持久性（redo log + WAL）
控制语句	begin、commit、rollback、savepoint、rollback to、release savepoint
自动提交	MySQL 默认开启；set autocommit=0 关闭
隔离级别	read uncommitted → read committed → repeatable read → serializable
默认级别	repeatable read（可重复读）
并发问题	脏读（未提交）、不可重复读（值变）、幻读（行数变）
锁	全局锁 / 表锁 / MDL / 行锁（记录锁、间隙锁、临键锁）、S 与 X、意向锁
死锁	1213 整个事务回滚，1205 仅语句失败；SHOW ENGINE INNODB STATUS 看日志
视图本质	保存的 select 语句，虚拟表
视图优势	简单、安全（列级控制）、数据独立
视图操作	create view ... as select ...、drop view、alter view
视图算法	MERGE（快，可下推）、TEMPTABLE（慢，不可更新）、UNDEFINED（自动）

5.7 关键命令速查

目的	命令
看运行状态	SHOW GLOBAL STATUS LIKE '...'
看参数	SHOW VARIABLES LIKE '...'、SELECT @@x
改参数	SET [GLOBAL] x=v、SET PERSIST x=v(8.0)、my.cnf
看当前连接	SHOW FULL PROCESSLIST、information_schema.processlist
杀会话	KILL id / KILL QUERY id
看引擎内部	SHOW ENGINE INNODB STATUS\G
看执行计划	EXPLAIN / DESC / EXPLAIN FORMAT=JSON / EXPLAIN ANALYZE
看资源消耗	SET profiling=1、SHOW PROFILES、SHOW PROFILE FOR QUERY n
建索引	CREATE INDEX ... ON t(c)、ALTER TABLE t ADD INDEX
看索引	SHOW INDEX FROM t、SHOW CREATE TABLE t\G
删索引	DROP INDEX i ON t、ALTER TABLE t DROP INDEX i / DROP PRIMARY KEY
刷统计	ANALYZE TABLE t
整碎片	OPTIMIZE TABLE t
事务	BEGIN / COMMIT / ROLLBACK / SAVEPOINT
隔离级别	SELECT @@tx_isolation、SET SESSION TRANSACTION ISOLATION LEVEL ...
加锁读	SELECT ... FOR UPDATE / LOCK IN SHARE MODE
查锁等待	information_schema.innodb_trx / innodb_locks / innodb_lock_waits、sys.innodb_lock_waits、performance_schema.data_locks
慢日志分析	mysqldumpslow -s t -t 10 日志、pt-query-digest 日志

---

附录 A：大数据量测试表生成脚本

以下脚本用于创建 dept、emp、salgrade 表并生成海量测试数据，供慢查询和索引优化实验使用。

A.1 创建表结构

CREATE TABLE dept(
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  dname VARCHAR(20) NOT NULL DEFAULT "",
  loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE emp(
  empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  ename VARCHAR(20) NOT NULL DEFAULT "",
  job VARCHAR(9) NOT NULL DEFAULT "",
  mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  hiredate DATE NOT NULL,
  sal DECIMAL(7,2) NOT NULL,
  comm DECIMAL(7,2) NOT NULL,
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE salgrade(
  grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  losal DECIMAL(17,2) NOT NULL,
  hisal DECIMAL(17,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

说明：原始脚本使用 MyISAM 是为了造数更快（无事务开销）。做事务与锁实验时必须改为 ENGINE=InnoDB，否则 BEGIN/COMMIT、行锁、MVCC 实验全部无效。

A.2 造数函数与存储过程

随机字符串函数 rand_string、随机数字函数 rand_num、批量插入过程 insert_emp 与 insert_dept 的完整代码见 5.2.1 与 5.2.3。执行顺序：

-- ① 建表（A.1）
-- ② 建函数（5.2.1）
-- ③ 建过程（5.2.3）
-- ④ 造数
call insert_dept(100, 10);
call insert_emp(100001, 1800000);
-- ⑤ 刷新统计信息后再做 EXPLAIN 实验
analyze table emp;
analyze table dept;

---

附录 B：练习

B.1 状态查询与慢查询

1. 写出查看「实例启动以来累计的 SELECT 执行次数」与「当前会话的 SELECT 执行次数」的命令，并说明两者区别。

show global status like 'Com_select';   -- 实例累计
show status like 'Com_select';          -- 当前会话（等价 show session status）

区别：不带 GLOBAL 统计当前连接自建立以来的次数，连接断开即归零；带 GLOBAL 统计整个实例自最近一次启动以来的累计值。

2. 缓冲池命中率如何计算？低于 95% 说明什么？

show global status like 'Innodb_buffer_pool_read_requests';
show global status like 'Innodb_buffer_pool_reads';

命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100%，OLTP 应 ≥ 99%。低于 95% 说明 innodb_buffer_pool_size 过小，或存在大量全表扫描把热数据挤出缓冲池。

3. 开启慢查询日志，阈值设为 0.5 秒，并要求记录未使用索引的语句。写出临时方式与永久方式。

-- 临时（重启失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;
-- 注意：long_query_time 只对新连接生效，需重连验证

# 永久：/etc/my.cnf，改完 systemctl restart mysqld
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=0.5
log_queries_not_using_indexes=1

4. 用一条命令找出慢日志中执行次数最多的前 5 条 SQL。

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

5. 数据库突然响应变慢，写出你的前三步操作。

① SHOW FULL PROCESSLIST 按 Time 倒序，看有无长时间运行的 Query 及 State； ② 若见 Waiting for table metadata lock 或大量等待，查 information_schema.innodb_trx 找长事务； ③ 复制最慢的 SQL 去 EXPLAIN，确认是否缺索引；必要时 KILL QUERY 紧急止血。

6. Handler_read_rnd_next 远大于 Handler_read_key 说明什么？

说明存储引擎大量按数据文件顺序读下一行，即全表扫描过多，索引没有被有效使用；应结合慢日志找出对应 SQL 并补索引。

B.2 索引

7. key(a,b,c) 索引下，判断以下条件能否用到索引，用到几列：

条件	答案
WHERE b=1 AND c=2	用不到（缺最左列 a）
WHERE a=1 AND c=2	用到 1 列（a），c 只能做索引条件下推
WHERE c=2 AND b=1 AND a=3	用到 3 列（书写顺序无关，优化器重排）
WHERE a>1 AND b=2	用到 1 列（a 是范围，b 不能再用于查找）
WHERE a=1 ORDER BY c	用到 1 列（a），排序产生 Using filesort

8. 如何证明一条 SQL 到底用了联合索引的前几列？

看 EXPLAIN 的 key_len：把各列长度按「类型基础长度 + 可空 1 字节 + 变长 2 字节 + 字符集倍数」累加，反推列数。或用 EXPLAIN FORMAT=JSON 直接看 used_key_parts。

9. 三列均为 INT NOT NULL 的 idx_abc(a,b,c)，key_len=8 说明什么？若 b 改为 INT NULL 且用到 a、b 两列，key_len 是多少？

key_len=8 说明用到 a、b 两列（4+4）。若 b 可空，则为 4 + (4+1) = 9。

10. 写出为 emp(deptno, sal, ename) 建联合索引的两种语法，并说明为何这个顺序能同时消除 filesort 和回表。

CREATE INDEX idx_deptno_sal_ename ON emp (deptno, sal, ename);
ALTER TABLE emp ADD INDEX idx_deptno_sal_ename (deptno, sal, ename);

deptno 是等值条件放最左，sal 紧随其后使 ORDER BY sal 可直接利用索引有序性（消除 Using filesort），ename 与叶子节点自带的主键 empno 一起覆盖了查询所需全部列（消除回表，出现 Using index）。

11. 已知 phone 是 VARCHAR(20) 且有索引，为什么 WHERE phone = 13800138000 会全表扫描？如何验证？

数字与字符串比较时，MySQL 把列整体 CAST 成数字，等于在索引列上套了函数，索引失效。验证：EXPLAIN 显示 type=ALL；改为 phone='13800138000' 后 type=ref；SHOW WARNINGS 也会提示类型转换导致无法使用范围访问。

12. 单表最多建几个索引？为什么不能盲目加？

建议 5~6 个。代价：写入放大（每次 DML 维护所有索引 B+Tree）、空间开销、挤占 buffer pool、增加优化器选错索引的概率、大表 DDL 风险。

B.3 事务与锁

13. 说出四种隔离级别，以及各自会出现哪些并发问题。MySQL 默认是哪一种？

READ UNCOMMITTED（脏读、不可重复读、幻读）→ READ COMMITTED（不可重复读、幻读）→ REPEATABLE READ（理论有幻读，InnoDB 基本消除）→ SERIALIZABLE（均无）。MySQL/MariaDB 默认 REPEATABLE READ。

14. 设计一个双会话实验证明「不可重复读」，并说明改成 RR 后结果如何变化。

两会话都设 READ COMMITTED；A BEGIN 后查 balance（1000）→ B 更新为 800 并 COMMIT → A 再查得到 800，即不可重复读。改为 REPEATABLE READ 后 A 第二次仍读到 1000（复用第一次快照读建立的 ReadView）。

15. RR 与 RC 在实现上只差一处，是哪一处？

ReadView 的创建时机：RR 在事务内第一次快照读时创建一次并全程复用；RC 每条 SELECT 都重新创建。

16. 为什么 UPDATE emp SET sal=sal+100 WHERE ename='x'（ename 无索引）会阻塞整张表的更新？如何验证与修复？

InnoDB 行锁加在索引上，无索引只能全表扫描并逐行加锁，效果等同表锁。验证：会话 A 执行该语句不提交，会话 B 更新一条完全无关的行也被阻塞。修复：ALTER TABLE emp ADD KEY idx_ename (ename);。

17. 错误 1213 与 1205 有何本质区别？应用应分别如何处理？

1213 是死锁，整个事务已被 InnoDB 回滚，应用应捕获后重试整个事务（保证幂等）；1205 是锁等待超时，只有当前语句失败、事务仍然存在并持有锁，应用必须显式 ROLLBACK，否则问题扩散。

*18. `SELECT FROM account WHERE id BETWEEN 3 AND 8 FOR UPDATE`（id 主键，现有 1、5、10）会锁住哪些区间？会话 B 插入 id=6、id=11 分别是什么结果？**

锁定临键区间约 (1,5] 和 (5,10]。插入 id=6 被阻塞（落在间隙内）；插入 id=11 成功（超出锁定范围）。

19. 长事务有哪些危害？如何监控？

危害：锁范围大且时间长、undo 膨胀无法 purge、MVCC 版本链变长、主从延迟、回滚极慢、binlog 事务过大。监控：查 information_schema.innodb_trx 中 TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60 的事务，并关注 history list length。

20. 为什么 DDL 不能回滚？误 DROP TABLE 后如何补救？

DDL 会隐式提交当前事务，MySQL 不提供 DDL 的用户级回滚。补救只能靠全量备份 + binlog 做时间点恢复（见 11-05-备份与恢复.md）。

B.4 视图与综合

21. 视图的三大优点是什么？为什么说视图能实现列级权限控制？

简单（屏蔽底层结构与关联）、安全（只暴露授权列）、数据独立（源表加列不影响视图）。因为普通 GRANT 只能到表级，而视图可以只 SELECT 部分列，再把视图的查询权限授予用户，从而实现列级控制。

22. 基于表 a(id,name,sex,phone,address,pos) 创建只暴露姓名、性别、电话的视图并查询。

create view b(name, sex, phone) as select name, sex, phone from a;
select * from b;

23. ALGORITHM=MERGE 与 TEMPTABLE 的区别？哪些写法会强制 TEMPTABLE？

MERGE 把视图定义合并进外层查询，条件可下推、能用底层索引；TEMPTABLE 先物化成临时表再过滤，条件无法下推且不可更新。含聚合函数、GROUP BY、HAVING、DISTINCT、UNION、LIMIT、选择列表中的子查询等会强制 TEMPTABLE。

24. 哪些视图不可更新？生产上对视图的写操作建议是什么？

含聚合函数、GROUP BY/HAVING/DISTINCT/UNION、选择列表子查询、TEMPTABLE 算法、多表视图的 INSERT、计算列等均不可更新。生产建议：视图只用于读，写操作直接对基表执行。

25. WITH CHECK OPTION 的作用？CASCADED 与 LOCAL 有何区别？

要求通过视图的 INSERT/UPDATE 结果仍满足视图的 WHERE 条件，否则报 1369，避免「写进去却查不到」。CASCADED（默认）级联检查本视图与所有底层视图的条件，LOCAL 只检查本视图条件。

*26. 综合题：某分页接口 `SELECT FROM orders WHERE user_id=? ORDER BY create_time DESC LIMIT 1000000, 20` 很慢，给出至少三条优化措施。**

① 建联合索引 (user_id, create_time)，让等值 + 排序都走索引，消除 Using filesort； ② 去掉 SELECT *，只查需要的列，配合索引形成覆盖索引； ③ 改为游标分页 WHERE user_id=? AND create_time < 上一页最后时间 ORDER BY create_time DESC LIMIT 20，或用延迟关联先在索引上翻页取主键再回表； ④ 业务上限制最大可翻页数，或用搜索条件收敛结果集。

27. 综合题：某表每天凌晨批量删除 500 万行历史数据，导致主从延迟和锁等待。如何改造？

改为分批小事务删除：DELETE FROM t WHERE create_time < ? LIMIT 2000; 循环执行直至 ROW_COUNT()=0，每批之间加短暂 sleep；确保 create_time 上有索引避免锁全表；更优方案是按时间做表分区，直接 ALTER TABLE ... DROP PARTITION（秒级、几乎无锁），或用「保留数据导入新表 + RENAME TABLE」的方式替换。