MySQL高级特性
第9章 查询优化
9.1 EXPLAIN执行计划详解
EXPLAIN基础用法
-- EXPLAIN：查看SQL执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;
-- EXPLAIN FORMAT：指定输出格式
EXPLAIN FORMAT = JSON SELECT * FROM users WHERE id = 1;
EXPLAIN FORMAT = TRADITIONAL SELECT * FROM users WHERE id = 1;
EXPLAIN FORMAT = TREE SELECT * FROM users WHERE id = 1; -- MySQL 8.0+
-- EXPLAIN ANALYZE：实际执行并分析（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
-- 查看表结构
EXPLAIN PARTITIONS SELECT * FROM users WHERE id = 1;
EXPLAIN输出字段详解
-- 创建示例表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
age INT,
email VARCHAR(100),
city VARCHAR(50),
created_at TIMESTAMP,
INDEX idx_age (age),
INDEX idx_city (city),
INDEX idx_created (created_at)
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
order_date DATE,
amount DECIMAL(10, 2),
INDEX idx_user_id (user_id),
INDEX idx_order_date (order_date)
) ENGINE=InnoDB;
-- 插入测试数据
INSERT INTO users (username, age, email, city, created_at)
SELECT CONCAT('user', n), n % 50 + 18, CONCAT('user', n, '@test.com'),
CASE WHEN n % 3 = 0 THEN 'Beijing' WHEN n % 3 = 1 THEN 'Shanghai' ELSE 'Guangzhou' END,
DATE_ADD('2020-01-01', INTERVAL n DAY)
FROM (
SELECT @n := @n + 1 AS n FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t1,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t2,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t3,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t4,
(SELECT @n := 0) init
) numbers LIMIT 10000;
INSERT INTO orders (user_id, order_date, amount)
SELECT (n % 1000) + 1, DATE_ADD('2023-01-01', INTERVAL n DAY), ROUND(RAND() * 1000, 2)
FROM (
SELECT @n := @n + 1 AS n FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t1,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t2,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t3,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2) t4,
(SELECT @n := 0) init
) numbers LIMIT 50000;
-- 基础EXPLAIN示例
EXPLAIN SELECT * FROM users WHERE id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
EXPLAIN字段详解
1. id：查询序列号
-- id相同，执行顺序从上到下
EXPLAIN
SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;
-- id不同，id越大优先级越高（先执行）
EXPLAIN
SELECT u.*,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u
WHERE u.id = 1;
-- id为NULL：结果集并集
EXPLAIN
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE age = 30;
2. select_type：查询类型
| 类型 | 说明 | 示例 |
|---|---|---|
| SIMPLE | 简单查询（无子查询、无UNION） | SELECT * FROM users |
| PRIMARY | 最外层查询 | SELECT * FROM (SELECT * FROM users) t |
| SUBQUERY | 子查询（不在FROM子句） | WHERE id IN (SELECT id FROM orders) |
| DERIVED | 派生表（FROM子句中的子查询） | FROM (SELECT * FROM users) t |
| UNION | UNION中的第二个或后续查询 | SELECT 1 UNION SELECT 2 |
| UNION RESULT | UNION的结果 | UNION的结果集 |
-- SIMPLE：简单查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- PRIMARY + SUBQUERY
EXPLAIN
SELECT u.*, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u
WHERE u.id = 1;
-- DERIVED：派生表
EXPLAIN
SELECT * FROM (SELECT * FROM users WHERE age > 20) AS t
WHERE t.city = 'Beijing';
-- UNION
EXPLAIN
SELECT * FROM users WHERE age < 25
UNION
SELECT * FROM users WHERE age > 30;
3. type：访问类型（重要）
| 类型 | 说明 | 性能 |
|---|---|---|
| system | 表只有一行记录 | 最优 |
| const | 主键或唯一索引等值查询 | 优秀 |
| eq_ref | 连接时使用主键或唯一索引 | 很好 |
| ref | 非唯一索引等值查询 | 好 |
| fulltext | 全文索引 | 一般 |
| ref_or_null | ref + IS NULL | 一般 |
| index_merge | 索引合并优化 | 较好 |
| unique_subquery | 子查询使用唯一索引 | 很好 |
| index_subquery | 子查询使用非唯一索引 | 较好 |
| range | 索引范围扫描 | 中等 |
| index | 全索引扫描 | 较差 |
| ALL | 全表扫描 | 最差 |
-- const：最优，主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- eq_ref：连接时使用主键
EXPLAIN
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';
-- range：范围查询
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- index：全索引扫描
EXPLAIN SELECT age FROM users;
-- ALL：全表扫描（最差）
EXPLAIN SELECT * FROM users WHERE email LIKE '%test%';
4. possible_keys：可能使用的索引
-- 可能使用的索引（列表）
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = 'Beijing';
-- possible_keys: idx_age, idx_city
-- 实际使用的索引
-- key: idx_age（优化器选择的最优索引）
-- 没有索引
EXPLAIN SELECT * FROM users WHERE email LIKE '%test%';
-- possible_keys: NULL
-- key: NULL
5. key_len：索引使用长度
-- key_len：使用的索引字节数
-- 计算规则：
-- - INT: 4字节
-- - BIGINT: 8字节
-- - VARCHAR(M): M * (字符集长度) + 2（NULL标记）+ 变长长度
-- - utf8mb4: 4字节/字符
-- - utf8: 3字节/字符
-- - DATE: 3字节
-- - DATETIME: 8字节
-- - TIMESTAMP: 4字节
-- 示例1：INT主键
EXPLAIN SELECT * FROM users WHERE id = 1;
-- key_len: 4（INT 4字节）
-- 示例2：VARCHAR索引
CREATE TABLE test (
id INT PRIMARY KEY,
name VARCHAR(50),
KEY idx_name (name)
) ENGINE=InnoDB;
EXPLAIN SELECT * FROM test WHERE name = 'test';
-- key_len: 202（50 * 4 + 2，utf8mb4 + 可变长度 + NULL标记）
-- 示例3：联合索引
CREATE TABLE test2 (
id INT PRIMARY KEY,
age INT,
city VARCHAR(50),
KEY idx_age_city (age, city)
) ENGINE=InnoDB;
EXPLAIN SELECT * FROM test2 WHERE age = 25 AND city = 'Beijing';
-- key_len: 4 + 202 = 206
EXPLAIN SELECT * FROM test2 WHERE age = 25;
-- key_len: 4（只使用了age部分）
6. ref：索引查找使用的列
-- ref：显示索引的哪一列被使用
EXPLAIN SELECT * FROM users WHERE age = 25;
-- ref: NULL（常量）
EXPLAIN SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- ref: blogdb.u.id（使用u表的id列）
7. rows：预估扫描行数
-- rows：预估需要扫描的行数
EXPLAIN SELECT * FROM users WHERE age = 25;
-- rows: 200（预估扫描200行）
-- rows越小越好
EXPLAIN SELECT * FROM users WHERE id = 1;
-- rows: 1（最优）
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';
-- rows: 3333（约1/3数据，索引效果一般）
8. filtered：过滤后的百分比
-- filtered：存储引擎返回后，服务器层过滤的百分比
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = 'Beijing';
-- rows: 200
-- filtered: 33.33
-- 说明：200行中，只有33.33%满足city条件
-- 100%：所有行都满足条件
EXPLAIN SELECT * FROM users WHERE id = 1;
-- filtered: 100.00
9. Extra：额外信息（重要）
| Extra信息 | 说明 | 优化建议 |
|---|---|---|
| Using index | 使用覆盖索引 | 优秀，无需回表 |
| Using index condition | 索引下推 | 很好，减少回表 |
| Using where | WHERE过滤 | 一般，检查是否可用索引 |
| Using filesort | 文件排序 | 差，需优化 |
| Using temporary | 使用临时表 | 差，需优化 |
| Using join buffer | 连接缓冲 | 较差，检查连接条件 |
| Impossible WHERE | 不可能条件 | 优化查询 |
| Select tables optimized away | 优化器直接返回 | 优秀 |
| Distinct | 优化DISTINCT | 很好 |
-- Using index：覆盖索引（最优）
EXPLAIN SELECT id, age FROM users WHERE age = 25;
-- Using index condition：索引下推
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = 'Beijing';
-- Using where：需要WHERE过滤
EXPLAIN SELECT * FROM users WHERE age > 25;
-- Using filesort：需要文件排序（差）
EXPLAIN SELECT * FROM users WHERE age = 25 ORDER BY email;
-- 优化：创建索引
CREATE INDEX idx_age_email ON users(age, email);
EXPLAIN SELECT * FROM users WHERE age = 25 ORDER BY email;
-- Extra: Using index condition
-- Using temporary：使用临时表（差）
EXPLAIN SELECT age, COUNT(*) FROM users GROUP BY age ORDER BY COUNT(*);
-- 优化：创建索引
CREATE INDEX idx_age ON users(age);
-- 或者去掉不必要的排序
-- Using join buffer：连接缓冲
EXPLAIN SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.username = o.user_id;
-- 优化：在连接字段上创建索引
9.2 查询优化器工作原理
优化器目标
-- MySQL查询优化器：基于成本的优化器（CBO）
-- 目标：找到执行成本最低的执行计划
-- 查看优化器配置
SHOW VARIABLES LIKE 'optimizer%';
-- optimizer_switch：优化器开关选项
SHOW VARIABLES LIKE 'optimizer_switch';
-- 常用优化器选项
-- index_merge: 索引合并
-- index_merge_union: 索引合并-UNION
-- index_merge_sort_union: 索引合并-SORT UNION
-- index_merge_intersection: 索引合并-INTERSECTION
-- engine_condition_pushdown: 引擎条件下推
-- index_condition_pushdown: 索引条件下推（ICP）
-- using_index_condition: 使用索引条件下推
-- 设置优化器选项
SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on';
优化器工作流程
┌─────────────────────────────────────────────┐
│ 1. SQL解析与验证 │
│ - 语法解析 │
│ - 语义验证 │
│ - 权限检查 │
├─────────────────────────────────────────────┤
│ 2. 逻辑查询计划生成 │
│ - 转换为逻辑执行计划 │
│ - 查询重写 │
│ * 视图合并 │
│ * 子查询优化 │
│ * 外连接转内连接 │
├─────────────────────────────────────────────┤
│ 3. 物理查询计划生成 │
│ - 访问路径选择 │
│ * 全表扫描 vs 索引扫描 │
│ * 索引选择 │
│ - 连接顺序选择 │
│ * 多表连接顺序 │
│ - 连接算法选择 │
│ * Nested Loop Join │
│ * Hash Join (MySQL 8.0.18+) │
├─────────────────────────────────────────────┤
│ 4. 成本估算 │
│ - IO成本 │
│ - CPU成本 │
│ - 选择最优执行计划 │
└─────────────────────────────────────────────┘
查询重写
-- 1. 常量折叠
SELECT 1 + 2 + 3;
-- 优化为：SELECT 6
-- 2. 条件简化
SELECT * FROM users WHERE age = 25 AND age = 25;
-- 优化为：SELECT * FROM users WHERE age = 25
SELECT * FROM users WHERE age = 25 OR age = 26 OR age = 27;
-- 优化为：SELECT * FROM users WHERE age IN (25, 26, 27);
-- 3. 子查询优化
-- 不优化（相关子查询）
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE amount > 100);
-- 优化为JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;
-- 4. 外连接优化
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NOT NULL;
-- 优化为：INNER JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id > 100;
-- 优化为：INNER JOIN（WHERE条件过滤NULL）
索引提示
-- USE INDEX：建议使用指定索引
SELECT * FROM users USE INDEX(idx_age) WHERE age = 25;
-- FORCE INDEX：强制使用指定索引
SELECT * FROM users FORCE INDEX(idx_age) WHERE age = 25;
-- IGNORE INDEX：忽略指定索引
SELECT * FROM users IGNORE INDEX(idx_city) WHERE age = 25 AND city = 'Beijing';
-- 使用场景：优化器选择错误时手动干预
-- 注意：索引提示可能导致更差的执行计划，谨慎使用
9.3 慢查询日志分析与优化
启用慢查询日志
-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- 超过2秒的查询记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
-- 指定慢查询日志路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- 配置文件（my.cnf）
[mysqld]
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_queries_not_using_indexes = ON
慢查询日志格式
# 慢查询日志内容示例
# Time: 2024-01-15T10:30:00.123456Z
# User@Host: root[root] @ localhost [] Id: 10
# Query_time: 5.234567 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 1000000
SET timestamp=1705306200;
SELECT * FROM users WHERE email LIKE '%test%';
# 字段说明：
# - Time：查询执行时间
# - User@Host：执行用户和主机
# - Query_time：查询执行时长（秒）
# - Lock_time：锁等待时长（秒）
# - Rows_sent：返回行数
# - Rows_examined：扫描行数
慢查询分析工具
-- mysqldumpslow：MySQL自带的慢查询分析工具
-- 安装：通常随MySQL安装
# 常用命令
mysqldumpslow /var/log/mysql/mysql-slow.log # 汇总统计
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 按时间排序，前10条
mysqldumpslow -s l -t 10 /var/log/mysql/mysql-slow.log # 按锁时间排序
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log # 按返回行数排序
mysqldumpslow -g 'SELECT.*FROM users' /var/log/mysql/mysql-slow.log # 过滤查询
# 选项说明：
# -s：排序方式（t=时间, l=锁时间, r=返回行数, c=次数）
# -t：显示前N条
# -g：正则表达式过滤
# -v：详细模式
# 输出示例：
# Count: 5 Time=10.00s (50s) Lock=0.00s (0s) Rows=1000.0 (5000), root[root]@localhost
# SELECT * FROM users WHERE email LIKE '%S%';
# pt-query-digest：Percona Toolkit提供的高级分析工具
# 安装：yum install percona-toolkit
# 基础使用
pt-query-digest /var/log/mysql/mysql-slow.log
# 输出到文件
pt-query-digest /var/log/mysql/mysql-slow.log > report.txt
# 实时分析
pt-query-digest --processlist h=localhost,u=root,p=password
# 过滤查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/log/mysql/mysql-slow.log
# 分析最近1小时的查询
pt-query-digest --since '1h ago' /var/log/mysql/mysql-slow.log
# 输出格式
pt-query-digest --report-format=report /var/log/mysql/mysql-slow.log # 文本格式
pt-query-digest --report-format=json /var/log/mysql/mysql-slow.log # JSON格式
pt-query-digest --report-format=slow-query-log /var/log/mysql/mysql-slow.log # 慢查询日志格式
慢查询优化案例
-- 案例1：全表扫描
-- 慢查询
SELECT * FROM users WHERE email LIKE '%test%';
-- Query_time: 5.234567
-- Rows_examined: 1000000
-- 分析：
-- 前缀模糊查询无法使用索引
-- 优化方案：
-- 1. 使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_email (email);
SELECT * FROM users WHERE MATCH(email) AGAINST('test' IN BOOLEAN MODE);
-- 2. 使用外部搜索引擎（Elasticsearch）
-- 案例2：索引失效
-- 慢查询
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- Query_time: 3.456789
-- Rows_examined: 1000000
-- 分析：
-- 函数破坏索引
-- 优化方案：
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 案例3：连接查询优化
-- 慢查询
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.username = o.user_id
WHERE u.city = 'Beijing';
-- Query_time: 8.901234
-- 分析：
-- 1. username类型不匹配（字符串 vs 数值）
-- 2. 连接字段无索引
-- 优化方案：
-- 1. 确保连接字段类型一致
ALTER TABLE orders MODIFY COLUMN user_id INT;
-- 2. 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_id ON orders(user_id);
-- 案例4：子查询优化
-- 慢查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- Query_time: 6.789012
-- 优化方案：改写为JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;
-- 案例5：分页查询优化
-- 慢查询（深分页）
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;
-- Query_time: 10.123456
-- Rows_examined: 1000010
-- 优化方案1：使用子查询
SELECT * FROM users
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 1000000, 1)
LIMIT 10;
-- 优化方案2：记录上次最大ID（适用于连续浏览）
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;
9.4 连接查询优化
连接类型选择
-- INNER JOIN vs LEFT JOIN
-- 只在必要时使用LEFT JOIN
-- 不推荐（使用LEFT JOIN但WHERE过滤NULL）
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NOT NULL;
-- 推荐（改用INNER JOIN）
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 优化器会自动转换
EXPLAIN SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NOT NULL;
-- select_type会显示为SIMPLE（优化后）
小表驱动大表
-- 原则：小表驱动大表（外层循环表要小）
-- 示例
-- users: 1000行
-- orders: 100000行
-- 不推荐（大表驱动小表）
SELECT u.*, o.*
FROM orders o
INNER JOIN users u ON o.user_id = u.id;
-- 推荐（小表驱动大表）
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 使用STRAIGHT_JOIN强制连接顺序
SELECT u.*, o.*
FROM users u
STRAIGHT_JOIN orders o ON u.id = o.user_id;
连接字段优化
-- 1. 连接字段类型要一致
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50)
) ENGINE=InnoDB;
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id VARCHAR(50), -- 错误：类型不一致
amount DECIMAL(10, 2)
) ENGINE=InnoDB;
-- 不推荐（类型不一致）
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id; -- INT vs VARCHAR
-- 推荐（类型一致）
ALTER TABLE orders MODIFY COLUMN user_id INT;
-- 2. 连接字段要有索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 3. 连接字段字符集和排序规则要一致
SHOW CREATE TABLE users;
SHOW CREATE TABLE orders;
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
批量连接优化
-- 避免：N+1查询问题
-- 不推荐（循环查询）
-- 在应用代码中：
-- for user in users:
-- orders = SELECT * FROM orders WHERE user_id = user.id
-- 推荐：一次连接查询
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 如果结果集太大，分批查询
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id BETWEEN 1 AND 1000;
9.5 子查询优化与物化
子查询优化策略
-- 1. 独立子查询优化
-- 不优化（相关子查询）
SELECT u.*,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
-- 优化方案1：改写为JOIN
SELECT u.*, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- 优化方案2：使用派生表
SELECT u.*, o.order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id;
-- 2. IN子查询优化
-- MySQL 5.6+ 会物化子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- MySQL 5.5及之前需要改写为JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;
-- 3. EXISTS子查询优化
-- EXISTS通常比IN快（有索引时）
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 100
);
-- 4. NOT IN vs NOT EXISTS
-- NOT IN性能差（无法使用索引）
SELECT * FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders WHERE amount > 100);
-- 改用NOT EXISTS（性能更好）
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 100
);
物化视图与派生表
-- MySQL没有真正的物化视图，但可以使用派生表或临时表模拟
-- 使用派生表
CREATE TABLE daily_sales_summary (
report_date DATE PRIMARY KEY,
total_sales DECIMAL(15, 2),
total_orders INT,
avg_order_value DECIMAL(10, 2)
) ENGINE=InnoDB;
-- 定期刷新（每天）
INSERT INTO daily_sales_summary
SELECT
order_date,
SUM(amount) AS total_sales,
COUNT(*) AS total_orders,
AVG(amount) AS avg_order_value
FROM orders
WHERE order_date = CURDATE() - INTERVAL 1 DAY
GROUP BY order_date
ON DUPLICATE KEY UPDATE
total_sales = VALUES(total_sales),
total_orders = VALUES(total_orders),
avg_order_value = VALUES(avg_order_value);
-- 查询汇总表（快）
SELECT * FROM daily_sales_summary
WHERE report_date BETWEEN '2024-01-01' AND '2024-01-31';
-- 使用临时表（会话级别）
CREATE TEMPORARY TABLE temp_user_stats AS
SELECT
u.id,
u.username,
COUNT(o.order_id) AS order_count,
SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
SELECT * FROM temp_user_stats WHERE order_count > 10;
DROP TEMPORARY TABLE temp_user_stats;
子查询缓存
-- MySQL 8.0之前有查询缓存（已废弃）
-- MySQL 8.0+移除了查询缓存
-- 应用层缓存子查询结果
-- 示例：Redis缓存用户订单数
-- Python伪代码
def get_user_order_count(user_id):
cache_key = f'user:order_count:{user_id}'
# 尝试从缓存获取
count = redis.get(cache_key)
if count is not None:
return int(count)
# 缓存未命中，查询数据库
count = db.execute(
'SELECT COUNT(*) FROM orders WHERE user_id = %s',
(user_id,)
)[0][0]
# 写入缓存（过期时间1小时）
redis.setex(cache_key, 3600, count)
return count
第10章 存储过程与函数
10.1 存储过程创建与调用
存储过程基础
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;
-- 调用存储过程
CALL GetUserById(1);
-- 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'blogdb';
SHOW CREATE PROCEDURE GetUserById;
-- 删除存储过程
DROP PROCEDURE IF EXISTS GetUserById;
参数类型
-- IN参数（输入参数，默认）
DELIMITER //
CREATE PROCEDURE GetUserByEmail(IN email VARCHAR(100))
BEGIN
SELECT * FROM users WHERE users.email = email;
END //
DELIMITER ;
CALL GetUserByEmail('test@example.com');
-- OUT参数（输出参数）
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT total_count INT)
BEGIN
SELECT COUNT(*) INTO total_count FROM users;
END //
DELIMITER ;
-- 调用
CALL GetUserCount(@count);
SELECT @count;
-- INOUT参数（输入输出参数）
DELIMITER //
CREATE PROCEDURE IncrementValue(INOUT value INT)
BEGIN
SET value = value + 1;
END //
DELIMITER ;
SET @num = 10;
CALL IncrementValue(@num);
SELECT @num; -- 11
-- 综合示例
DELIMITER //
CREATE PROCEDURE CreateUser(
IN p_username VARCHAR(50),
IN p_email VARCHAR(100),
IN p_age INT,
OUT p_user_id INT,
OUT p_result VARCHAR(100)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_result = 'Error creating user';
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO users (username, email, age)
VALUES (p_username, p_email, p_age);
SET p_user_id = LAST_INSERT_ID();
SET p_result = 'User created successfully';
COMMIT;
END //
DELIMITER ;
-- 调用
CALL CreateUser('newuser', 'new@example.com', 25, @user_id, @result);
SELECT @user_id, @result;
变量与条件语句
-- 局部变量
DELIMITER //
CREATE PROCEDURE TestVariables()
BEGIN
-- 声明变量
DECLARE v_count INT DEFAULT 0;
DECLARE v_username VARCHAR(50);
DECLARE v_total DECIMAL(10, 2);
-- 赋值
SET v_count = 100;
SET v_username = 'test';
-- 从查询赋值
SELECT COUNT(*) INTO v_count FROM users;
SELECT username INTO v_username FROM users WHERE id = 1;
-- 使用变量
SELECT v_count, v_username;
END //
DELIMITER ;
-- IF语句
DELIMITER //
CREATE PROCEDURE CheckUserAge(IN user_id INT)
BEGIN
DECLARE v_age INT;
SELECT age INTO v_age FROM users WHERE id = user_id;
IF v_age < 18 THEN
SELECT 'Minor' AS age_group;
ELSEIF v_age >= 18 AND v_age < 60 THEN
SELECT 'Adult' AS age_group;
ELSE
SELECT 'Senior' AS age_group;
END IF;
END //
DELIMITER ;
CALL CheckUserAge(1);
-- CASE语句
DELIMITER //
CREATE PROCEDURE CategorizeUser(IN user_id INT)
BEGIN
DECLARE v_city VARCHAR(50);
SELECT city INTO v_city FROM users WHERE id = user_id;
CASE v_city
WHEN 'Beijing' THEN SELECT 'North' AS region;
WHEN 'Shanghai' THEN SELECT 'East' AS region;
WHEN 'Guangzhou' THEN SELECT 'South' AS region;
ELSE SELECT 'Other' AS region;
END CASE;
END //
DELIMITER ;
-- CASE表达式
DELIMITER //
CREATE PROCEDURE GetDiscount(IN user_level INT)
BEGIN
DECLARE v_discount DECIMAL(3, 2);
SET v_discount = CASE user_level
WHEN 1 THEN 0.10
WHEN 2 THEN 0.20
WHEN 3 THEN 0.30
ELSE 0.00
END;
SELECT v_discount AS discount;
END //
DELIMITER ;
循环语句
-- WHILE循环
DELIMITER //
CREATE PROCEDURE GenerateNumbers(IN max_num INT)
BEGIN
DECLARE v_counter INT DEFAULT 1;
WHILE v_counter <= max_num DO
INSERT INTO numbers (num) VALUES (v_counter);
SET v_counter = v_counter + 1;
END WHILE;
SELECT CONCAT('Generated ', max_num, ' numbers') AS result;
END //
DELIMITER ;
-- REPEAT UNTIL循环
DELIMITER //
CREATE PROCEDURE TestRepeat()
BEGIN
DECLARE v_counter INT DEFAULT 1;
DECLARE v_sum INT DEFAULT 0;
REPEAT
SET v_sum = v_sum + v_counter;
SET v_counter = v_counter + 1;
UNTIL v_counter > 10 END REPEAT;
SELECT v_sum AS total;
END //
DELIMITER ;
-- LOOP循环
DELIMITER //
CREATE PROCEDURE TestLoop()
BEGIN
DECLARE v_counter INT DEFAULT 1;
test_loop: LOOP
IF v_counter > 10 THEN
LEAVE test_loop; -- 退出循环
END IF;
INSERT INTO numbers (num) VALUES (v_counter);
SET v_counter = v_counter + 1;
END LOOP;
SELECT 'Loop completed' AS result;
END //
DELIMITER ;
-- ITERATE（类似CONTINUE）
DELIMITER //
CREATE PROCEDURE TestIterate()
BEGIN
DECLARE v_counter INT DEFAULT 0;
test_loop: LOOP
SET v_counter = v_counter + 1;
IF v_counter <= 10 THEN
ITERATE test_loop; -- 继续下一次循环
END IF;
IF v_counter > 20 THEN
LEAVE test_loop;
END IF;
INSERT INTO numbers (num) VALUES (v_counter);
END LOOP;
END //
DELIMITER ;
游标使用
-- 游标：逐行处理查询结果
DELIMITER //
CREATE PROCEDURE ProcessUsers()
BEGIN
-- 声明变量
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_username VARCHAR(50);
DECLARE v_age INT;
-- 声明游标
DECLARE user_cursor CURSOR FOR
SELECT id, username, age FROM users WHERE age > 20;
-- 声明句柄（处理 NOT FOUND）
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 创建临时表
CREATE TEMPORARY TABLE IF NOT EXISTS processed_users (
user_id INT,
username VARCHAR(50),
status VARCHAR(50)
);
-- 打开游标
OPEN user_cursor;
-- 循环读取
read_loop: LOOP
FETCH user_cursor INTO v_id, v_username, v_age;
IF done THEN
LEAVE read_loop;
END IF;
-- 处理数据
INSERT INTO processed_users VALUES (v_id, v_username, 'Processed');
END LOOP;
-- 关闭游标
CLOSE user_cursor;
-- 返回结果
SELECT * FROM processed_users;
DROP TEMPORARY TABLE processed_users;
END //
DELIMITER ;
CALL ProcessUsers();
错误处理
-- 错误处理句柄
DELIMITER //
CREATE PROCEDURE TestErrorHandler()
BEGIN
-- 声明错误句柄
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'Error occurred' AS message;
ROLLBACK;
END;
START TRANSACTION;
-- 可能出错的语句
INSERT INTO users (username, email) VALUES ('user1', 'email1');
-- INSERT INTO users (username, email) VALUES (NULL, NULL); -- 错误
COMMIT;
SELECT 'Success' AS message;
END //
DELIMITER ;
-- 捕获特定错误
DELIMITER //
CREATE PROCEDURE TestSpecificError()
BEGIN
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE EXIT HANDLER FOR duplicate_key
BEGIN
SELECT 'Duplicate key error' AS message;
END;
INSERT INTO users (id, username) VALUES (1, 'test');
INSERT INTO users (id, username) VALUES (1, 'test2'); -- 重复主键
END //
DELIMITER ;
-- 获取错误信息
DELIMITER //
CREATE PROCEDURE TestErrorInfo()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT
ERROR_CODE() AS error_code,
ERROR_MESSAGE() AS error_message;
ROLLBACK;
END;
START TRANSACTION;
-- 错误的SQL
INSERT INTO nonexistent_table VALUES (1);
COMMIT;
END //
DELIMITER ;
10.2 存储函数
创建存储函数
-- 创建存储函数
DELIMITER //
CREATE FUNCTION GetUserAge(user_id INT) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_age INT;
SELECT age INTO v_age FROM users WHERE id = user_id;
RETURN v_age;
END //
DELIMITER ;
-- 调用存储函数
SELECT GetUserAge(1);
SELECT username, GetUserAge(id) FROM users;
-- 查看存储函数
SHOW FUNCTION STATUS WHERE Db = 'blogdb';
SHOW CREATE FUNCTION GetUserAge;
-- 删除存储函数
DROP FUNCTION IF EXISTS GetUserAge;
函数特性
-- DETERMINISTIC：确定性函数（相同输入始终返回相同输出）
DELIMITER //
CREATE FUNCTION CalculateTax(amount DECIMAL(10, 2)) RETURNS DECIMAL(10, 2)
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN amount * 0.13; -- 13%税率
END //
DELIMITER ;
SELECT CalculateTax(1000);
-- NO SQL：不包含SQL语句
DELIMITER //
CREATE FUNCTION FormatPrice(price DECIMAL(10, 2)) RETURNS VARCHAR(20)
NO SQL
DETERMINISTIC
BEGIN
RETURN CONCAT('¥', price);
END //
DELIMITER ;
-- CONTAINS SQL：包含SQL（默认）
-- READS SQL DATA：只读取数据
-- MODIFIES SQL DATA：修改数据
常用函数示例
-- 计算年龄
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE) RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_age INT;
SET v_age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
RETURN v_age;
END //
DELIMITER ;
SELECT CalculateAge('1990-01-01');
-- 生成随机字符串
DELIMITER //
CREATE FUNCTION GenerateRandomString(length INT) RETURNS VARCHAR(100)
DETERMINISTIC
NO SQL
BEGIN
DECLARE chars VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE result VARCHAR(100) DEFAULT '';
DECLARE i INT DEFAULT 1;
WHILE i <= length DO
SET result = CONCAT(result, SUBSTRING(chars, FLOOR(1 + RAND() * 62), 1));
SET i = i + 1;
END WHILE;
RETURN result;
END //
DELIMITER ;
SELECT GenerateRandomString(10);
-- 格式化电话号码
DELIMITER //
CREATE FUNCTION FormatPhone(phone VARCHAR(20)) RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
IF phone IS NULL THEN
RETURN NULL;
END IF;
IF LENGTH(phone) = 11 THEN
RETURN CONCAT(LEFT(phone, 3), '-', SUBSTRING(phone, 4, 4), '-', RIGHT(phone, 4));
ELSE
RETURN phone;
END IF;
END //
DELIMITER ;
SELECT FormatPhone('13800138000');
-- 结果：138-0013-8000
存储函数 vs 存储过程
| 特性 | 存储过程 | 存储函数 |
|---|---|---|
| 返回值 | OUT参数（可多个） | RETURN单个值 |
| 调用方式 | CALL procedure() | SELECT function() |
| SQL语句 | DDL、DML | 仅SELECT |
| 事务 | 可以包含事务 | 不能包含事务 |
| 使用场景 | 复杂业务逻辑 | 计算和转换 |
-- 存储过程：复杂业务逻辑
DELIMITER //
CREATE PROCEDURE TransferFunds(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10, 2),
OUT result INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET result = -1; -- 失败
ROLLBACK;
END;
START TRANSACTION;
UPDATE accounts SET balance = balance - amount WHERE id = from_account;
UPDATE accounts SET balance = balance + amount WHERE id = to_account;
COMMIT;
SET result = 1; -- 成功
END //
DELIMITER ;
-- 存储函数：简单计算
DELIMITER //
CREATE FUNCTION CalculateDiscount(total_amount DECIMAL(10, 2)) RETURNS DECIMAL(10, 2)
DETERMINISTIC
READS SQL DATA
BEGIN
IF total_amount > 1000 THEN
RETURN total_amount * 0.9; -- 9折
ELSE
RETURN total_amount;
END IF;
END //
DELIMITER ;
10.3 触发器
触发器基础
-- 创建触发器
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
-- 在插入前执行
SET NEW.created_at = NOW();
END //
DELIMITER ;
-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users';
SHOW CREATE TRIGGER before_user_insert;
-- 删除触发器
DROP TRIGGER IF EXISTS before_user_insert;
触发时机与类型
| 时机 | 类型 | 说明 |
|---|---|---|
| BEFORE | INSERT | 插入前触发 |
| AFTER | INSERT | 插入后触发 |
| BEFORE | UPDATE | 更新前触发 |
| AFTER | UPDATE | 更新后触发 |
| BEFORE | DELETE | 删除前触发 |
| AFTER | DELETE | 删除后触发 |
触发器示例
-- 1. INSERT触发器
DELIMITER //
CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
-- 设置默认值
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
-- 用户名转小写
SET NEW.username = LOWER(NEW.username);
-- 自动生成昵称
IF NEW.nickname IS NULL OR NEW.nickname = '' THEN
SET NEW.nickname = CONCAT('user_', NEW.username);
END IF;
END //
DELIMITER ;
INSERT INTO users (username, email) VALUES ('TestUser', 'test@example.com');
-- 自动设置created_at，转换为testuser，生成user_testuser
-- 2. UPDATE触发器
DELIMITER //
CREATE TRIGGER before_update_user
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
-- 更新修改时间
SET NEW.updated_at = NOW();
-- 记录变更（日志表）
INSERT INTO user_logs (user_id, action, old_email, new_email, changed_at)
VALUES (OLD.id, 'email_update', OLD.email, NEW.email, NOW());
END //
DELIMITER ;
-- 3. DELETE触发器
DELIMITER //
CREATE TRIGGER before_delete_user
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
-- 软删除：将用户移到归档表
INSERT INTO users_archive (id, username, email, age, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, OLD.age, NOW());
END //
DELIMITER ;
-- 4. AFTER触发器（数据已写入）
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- 更新用户总订单数
UPDATE users
SET total_orders = total_orders + 1,
total_amount = total_amount + NEW.amount
WHERE id = NEW.user_id;
END //
DELIMITER ;
触发器实战应用
-- 1. 数据审计日志
CREATE TABLE audit_log (
log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(50),
record_id INT,
action VARCHAR(10),
old_data JSON,
new_data JSON,
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER //
CREATE TRIGGER after_update_users_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
VALUES (
'users',
NEW.id,
'UPDATE',
JSON_OBJECT(
'username', OLD.username,
'email', OLD.email,
'age', OLD.age
),
JSON_OBJECT(
'username', NEW.username,
'email', NEW.email,
'age', NEW.age
)
);
END //
DELIMITER ;
-- 2. 数据同步
-- 主表：users
-- 同步到：user_search（用于全文搜索）
DELIMITER //
CREATE TRIGGER after_user_insert_sync
AFTER INSERT ON users
FOR EACH ROW
BEGIN
INSERT INTO user_search (id, username, email, profile)
VALUES (NEW.id, NEW.username, NEW.email, NEW.profile);
END //
CREATE TRIGGER after_user_update_sync
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
UPDATE user_search
SET username = NEW.username,
email = NEW.email,
profile = NEW.profile
WHERE id = NEW.id;
END //
CREATE TRIGGER after_user_delete_sync
AFTER DELETE ON users
FOR EACH ROW
BEGIN
DELETE FROM user_search WHERE id = OLD.id;
END //
DELIMITER ;
-- 3. 数据完整性检查
DELIMITER //
CREATE TRIGGER before_update_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
-- 余额不能为负
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Balance cannot be negative';
END IF;
-- 记录大额变动
IF ABS(NEW.balance - OLD.balance) > 10000 THEN
INSERT INTO alerts (account_id, message, amount)
VALUES (NEW.id, 'Large balance change', NEW.balance - OLD.balance);
END IF;
END //
DELIMITER ;
触发器注意事项
-- 1. 触发器性能影响
-- 触发器会自动执行，可能影响性能
-- 建议：只用于关键业务逻辑
-- 2. 递归触发器
-- MySQL不支持递归触发器，避免在触发器中修改触发表
-- 3. 触发器与外键
-- 触发器在外键检查之前执行（BEFORE）
-- 或之后执行（AFTER）
-- 4. 查看触发器执行顺序
SHOW TRIGGERS LIKE 'users';
-- 按创建时间排序
-- 5. 临时禁用触发器
-- MySQL不支持直接禁用触发器
-- 可以：DROP TRIGGER ... 然后 CREATE TRIGGER ...
10.4 事件调度器
启用事件调度器
-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';
-- 启用事件调度器
SET GLOBAL event_scheduler = ON;
-- 或在配置文件中启用
[mysqld]
event_scheduler = ON
创建事件
-- 创建事件：每天执行一次
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 查看事件
SHOW EVENTS;
SHOW EVENTS LIKE 'daily%';
SHOW CREATE EVENT daily_cleanup;
-- 修改事件
ALTER EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00';
-- 删除事件
DROP EVENT IF EXISTS daily_cleanup;
-- 禁用/启用事件
ALTER EVENT daily_cleanup DISABLE;
ALTER EVENT daily_cleanup ENABLE;
事件调度示例
-- 1. 定期备份数据
DELIMITER //
CREATE EVENT backup_daily_data
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
BEGIN
-- 备份到历史表
INSERT INTO orders_history
SELECT * FROM orders
WHERE order_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
-- 清理已备份的数据
-- DELETE FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY);
END //
DELIMITER ;
-- 2. 定期统计
DELIMITER //
CREATE EVENT update_daily_stats
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:05:00'
DO
BEGIN
INSERT INTO daily_stats (stat_date, new_users, new_orders)
VALUES (
DATE_SUB(CURDATE(), INTERVAL 1 DAY),
(SELECT COUNT(*) FROM users WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)),
(SELECT COUNT(*) FROM orders WHERE order_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY))
)
ON DUPLICATE KEY UPDATE
new_users = VALUES(new_users),
new_orders = VALUES(new_orders);
END //
DELIMITER ;
-- 3. 定期清理临时表
DELIMITER //
CREATE EVENT cleanup_temp_tables
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
TRUNCATE TABLE temp_sessions;
TRUNCATE TABLE temp_cache;
END //
DELIMITER ;
-- 4. 周期性任务
DELIMITER //
CREATE EVENT weekly_report
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-01 00:00:00'
DO
BEGIN
-- 生成周报
INSERT INTO weekly_reports
SELECT
YEARWEEK(order_date) AS report_week,
COUNT(*) AS total_orders,
SUM(amount) AS total_amount
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
GROUP BY YEARWEEK(order_date);
END //
DELIMITER ;
-- 5. 一次性事件
DELIMITER //
CREATE EVENT one_time_cleanup
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
BEGIN
-- 年底清理
DELETE FROM logs WHERE YEAR(created_at) < YEAR(CURDATE());
END //
DELIMITER ;
事件管理
-- 查看所有事件
SELECT
EVENT_NAME,
EVENT_DEFINITION,
INTERVAL_VALUE,
INTERVAL_FIELD,
STATUS,
ON_COMPLETION
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();
-- 事件状态
-- ENABLED：启用
-- DISABLED：禁用
-- SLAVESIDE_DISABLED：从库禁用
-- 完成后保留或删除事件
CREATE EVENT test_event
ON SCHEDULE EVERY 1 DAY
ON COMPLETION PRESERVE -- 保留事件（默认DROP，删除事件）
DO
INSERT INTO logs (message) VALUES ('Test event');
-- 查看事件执行历史
-- 需要启用日志
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = ON;
SELECT * FROM mysql.general_log
WHERE argument LIKE '%test_event%';
第11章 分区与分表
11.1 分区类型
分区概述
-- 分区：将大表拆分成多个物理存储块
-- 优势：
-- 1. 提高查询性能（只扫描相关分区）
-- 2. 便于数据维护（删除/归档整个分区）
-- 3. 提高IO并行度
-- 分区限制：
-- 1. 主键和唯一键必须包含分区键
-- 2. 外键不支持
-- 3. 不支持全文索引（MySQL 8.0前）
-- 4. 每个表最多8192个分区
-- 查看是否支持分区
SHOW PLUGINS WHERE Name = 'partition';
RANGE分区
-- RANGE分区：按值范围分区
CREATE TABLE orders (
order_id INT AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10, 2),
PRIMARY KEY (order_id, order_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2022 VALUES LESS THAN (2022),
PARTITION p2023 VALUES LESS THAN (2023),
PARTITION p2024 VALUES LESS THAN (2024),
PARTITION p2025 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 插入数据
INSERT INTO orders (order_date, customer_id, amount)
VALUES ('2024-01-01', 1, 1000);
-- 查看分区
SELECT
PARTITION_NAME,
PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders' AND TABLE_SCHEMA = DATABASE();
-- 查询特定分区（分区裁剪）
SELECT * FROM orders PARTITION (p2024)
WHERE order_date = '2024-01-01';
-- 查看分区使用情况
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- partitions: p2024（只扫描p2024分区）
-- 删除分区（快速删除数据）
ALTER TABLE orders DROP PARTITION p2022;
-- 等价于：DELETE FROM orders WHERE order_date < '2022-01-01'
-- 但速度快得多（直接删除分区文件）
LIST分区
-- LIST分区：按值列表分区
CREATE TABLE users (
user_id INT AUTO_INCREMENT,
username VARCHAR(50),
city VARCHAR(50),
PRIMARY KEY (user_id, city)
) ENGINE=InnoDB
PARTITION BY LIST COLUMNS(city) (
PARTITION p_north VALUES IN ('Beijing', 'Tianjin', 'Shijiazhuang'),
PARTITION p_east VALUES IN ('Shanghai', 'Nanjing', 'Hangzhou'),
PARTITION p_south VALUES IN ('Guangzhou', 'Shenzhen', 'Foshan'),
PARTITION p_other VALUES IN ('Other')
);
-- 插入数据
INSERT INTO users (username, city) VALUES ('user1', 'Beijing');
-- 查看各分区数据量
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'users';
-- 添加新分区
ALTER TABLE users ADD PARTITION (
PARTITION p_central VALUES IN ('Wuhan', 'Changsha')
);
HASH分区
-- HASH分区：按哈希值均匀分布
CREATE TABLE orders_hash (
order_id INT AUTO_INCREMENT,
customer_id INT,
order_date DATE,
amount DECIMAL(10, 2),
PRIMARY KEY (order_id, customer_id)
) ENGINE=InnoDB
PARTITION BY HASH(customer_id)
PARTITIONS 4;
-- 数据均匀分布到4个分区
INSERT INTO orders_hash (customer_id, order_date, amount)
VALUES (1, '2024-01-01', 1000);
-- 查看分区分布
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders_hash';
-- LINEAR HASH：更快的分区定位，但数据可能不均匀
CREATE TABLE orders_linear (
order_id INT AUTO_INCREMENT,
customer_id INT,
PRIMARY KEY (order_id, customer_id)
) ENGINE=InnoDB
PARTITION BY LINEAR HASH(customer_id)
PARTITIONS 4;
KEY分区
-- KEY分区：类似HASH，但使用MySQL哈希函数
CREATE TABLE orders_key (
order_id INT AUTO_INCREMENT,
customer_id INT,
order_date DATE,
PRIMARY KEY (order_id)
) ENGINE=InnoDB
PARTITION BY KEY()
PARTITIONS 4;
-- 指定分区键
CREATE TABLE orders_key2 (
order_id INT AUTO_INCREMENT,
customer_id INT,
order_date DATE,
PRIMARY KEY (order_id, customer_id)
) ENGINE=InnoDB
PARTITION BY KEY(customer_id)
PARTITIONS 4;
复合分区
-- RANGE + HASH复合分区（子分区）
CREATE TABLE orders_subpartition (
order_id INT AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10, 2),
PRIMARY KEY (order_id, order_date, customer_id)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH(customer_id)
SUBPARTITIONS 4 (
PARTITION p2023 VALUES LESS THAN (2023),
PARTITION p2024 VALUES LESS THAN (2024),
PARTITION p2025 VALUES LESS THAN (2025)
);
-- 查看子分区
SELECT
PARTITION_NAME,
SUBPARTITION_NAME,
TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders_subpartition'
ORDER BY PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION;
11.2 分区管理
分区维护
-- 1. 添加分区
ALTER TABLE orders ADD PARTITION (
PARTITION p2026 VALUES LESS THAN (2026)
);
-- 2. 删除分区
ALTER TABLE orders DROP PARTITION p2022;
-- 3. 重命名分区
ALTER TABLE orders REORGANIZE PARTITION pmax INTO (
PARTITION p2026 VALUES LESS THAN (2026),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 4. 合并分区
ALTER TABLE orders REORGANIZE PARTITION p2023, p2024 INTO (
PARTITION p2024 VALUES LESS THAN (2024)
);
-- 5. 拆分分区
ALTER TABLE orders REORGANIZE PARTITION p2024 INTO (
PARTITION p2024_h1 VALUES LESS THAN (202407),
PARTITION p2024_h2 VALUES LESS THAN (2025)
);
-- 6. 重建分区
ALTER TABLE orders REBUILD PARTITION p2024;
-- 7. 优化分区
ALTER TABLE orders OPTIMIZE PARTITION p2024;
-- 8. 分析分区
ALTER TABLE orders ANALYZE PARTITION p2024;
-- 9. 检查分区
ALTER TABLE orders CHECK PARTITION p2024;
-- 10. 修复分区
ALTER TABLE orders REPAIR PARTITION p2024;
分区交换
-- 分区交换：快速加载数据
-- 1. 创建临时表（结构与目标分区相同）
CREATE TABLE orders_temp_2024 (
order_id INT AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10, 2),
PRIMARY KEY (order_id, order_date)
) ENGINE=InnoDB;
-- 2. 加载大量数据到临时表
INSERT INTO orders_temp_2024 (order_date, customer_id, amount)
SELECT order_date, customer_id, amount
FROM staging_orders
WHERE YEAR(order_date) = 2024;
-- 3. 交换分区
ALTER TABLE orders EXCHANGE PARTITION p2024 WITH TABLE orders_temp_2024;
-- 4. 删除临时表
DROP TABLE orders_temp_2024;
分区裁剪
-- 分区裁剪：查询优化器只扫描相关分区
-- 示例1：日期范围查询
EXPLAIN PARTITIONS
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 只扫描p2024分区
-- 示例2：等值查询
EXPLAIN PARTITIONS
SELECT * FROM orders
WHERE order_date = '2024-06-15';
-- 只扫描p2024分区
-- 示例3：未使用分区键（全表扫描）
EXPLAIN PARTITIONS
SELECT * FROM orders
WHERE customer_id = 100;
-- 扫描所有分区（不推荐）
-- 优化：在WHERE子句中包含分区键
SELECT * FROM orders
WHERE customer_id = 100 AND order_date BETWEEN '2024-01-01' AND '2024-12-31';
11.3 水平分表与垂直分表
分表策略对比
| 策略 | 说明 | 适用场景 | 示例 |
|---|---|---|---|
| 垂直分表 | 按列拆分 | 表字段多，访问模式不同 | users_base + users_ext |
| 水平分表 | 按行拆分 | 数据量大，查询性能 | users_2024, users_2025 |
垂直分表
-- 垂直分表：将大表按列拆分成多个小表
-- 原表
CREATE TABLE users_all (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
nickname VARCHAR(50),
avatar VARCHAR(255),
bio TEXT,
phone VARCHAR(20),
address VARCHAR(255),
city VARCHAR(50),
country VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
last_login_at TIMESTAMP,
status VARCHAR(20) DEFAULT 'active'
) ENGINE=InnoDB;
-- 拆分为基础表和扩展表
-- 基础表：经常访问的核心字段
CREATE TABLE users_base (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 扩展表：不常用的字段
CREATE TABLE users_ext (
user_id INT PRIMARY KEY,
nickname VARCHAR(50),
avatar VARCHAR(255),
bio TEXT,
phone VARCHAR(20),
address VARCHAR(255),
city VARCHAR(50),
country VARCHAR(50),
last_login_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users_base(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- 查询：JOIN获取完整信息
SELECT
b.user_id,
b.username,
b.email,
e.nickname,
e.avatar,
e.city
FROM users_base b
LEFT JOIN users_ext e ON b.user_id = e.user_id
WHERE b.user_id = 1;
-- 垂直分表优势：
-- 1. 减少表宽度，提高缓存效率
-- 2. 热点数据和冷数据分离
-- 3. 部分字段可以加更多索引
水平分表
-- 水平分表：按行拆分成多个表
-- 方式1：按时间分表
CREATE TABLE orders_2024 (
order_id INT AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10, 2),
PRIMARY KEY (order_id, order_date),
INDEX idx_customer (customer_id),
INDEX idx_date (order_date)
) ENGINE=InnoDB;
CREATE TABLE orders_2025 (
order_id INT AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10, 2),
PRIMARY KEY (order_id, order_date),
INDEX idx_customer (customer_id),
INDEX idx_date (order_date)
) ENGINE=InnoDB;
-- 查询2024年订单
SELECT * FROM orders_2024
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 方式2：按ID范围分表
CREATE TABLE users_1m (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB;
CREATE TABLE users_2m (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB;
-- 查询
SELECT * FROM users_1m WHERE user_id BETWEEN 1 AND 1000000;
SELECT * FROM users_2m WHERE user_id BETWEEN 1000001 AND 2000000;
-- 方式3：按哈希分表
CREATE TABLE users_hash0 (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB;
CREATE TABLE users_hash1 (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB;
-- 应用层路由：根据user_id % 2路由到不同表
分表路由实现
-- 使用MERGE存储引擎（MySQL 5.7+ 不支持）
-- 推荐：在应用层实现路由
-- 创建合并视图（模拟路由）
CREATE OR REPLACE VIEW orders_all AS
SELECT * FROM orders_2024
UNION ALL
SELECT * FROM orders_2025;
-- 查询视图
SELECT * FROM orders_all
WHERE order_date = '2024-06-15';
-- 注意：视图无法利用分区裁剪优化
-- 推荐：应用层直接查询具体表
# Python分表路由示例
class ShardingDB:
def __init__(self):
self.conn = mysql.connector.connect(...)
def get_table_name(self, order_date):
"""根据日期确定表名"""
year = order_date.year
return f"orders_{year}"
def insert_order(self, order_date, customer_id, amount):
"""插入订单"""
table_name = self.get_table_name(order_date)
query = f"""
INSERT INTO {table_name} (order_date, customer_id, amount)
VALUES (%s, %s, %s)
"""
cursor = self.conn.cursor()
cursor.execute(query, (order_date, customer_id, amount))
self.conn.commit()
def query_orders(self, start_date, end_date):
"""查询订单"""
tables = set()
current = start_date
while current <= end_date:
tables.add(self.get_table_name(current))
current = current.replace(year=current.year + 1)
query = " UNION ALL ".join([
f"SELECT * FROM {table} WHERE order_date BETWEEN %s AND %s"
for table in tables
])
cursor = self.conn.cursor()
cursor.execute(query, (start_date, end_date) * len(tables))
return cursor.fetchall()
# 使用
db = ShardingDB()
db.insert_order(date(2024, 6, 15), 1001, 1000)
orders = db.query_orders(date(2024, 1, 1), date(2024, 12, 31))
11.4 分库分表中间件简介
主流分库分表中间件
| 中间件 | 架构 | 语言 | 特点 | 适用场景 |
|---|---|---|---|---|
| ShardingSphere | JDBC/Proxy | Java | 功能丰富，社区活跃 | 复杂分片场景 |
| Mycat | Proxy | Java | 成熟稳定 | 中小规模 |
| Vitess | Proxy | Go | Kubernetes原生 | 云原生环境 |
| ProxySQL | Proxy | C++ | 高性能 | 读写分离、查询缓存 |
| MySQL Router | Proxy | C++ | 官方支持 | InnoDB Cluster |
ShardingSphere简介
# ShardingSphere-JDBC配置示例
# 数据源配置
dataSources:
ds0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/demo_ds_0
username: root
password: password
ds1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/demo_ds_1
username: root
password: password
# 分片规则
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: db_inline
shardingAlgorithms:
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
db_inline:
type: INLINE
props:
algorithm-expression: ds${user_id % 2}
Mycat简介
<!-- Mycat schema.xml配置示例 -->
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
<!-- 全局表：每个节点都有完整数据 -->
<table name="t_country" primaryKey="ID" type="global"/>
<!-- 分片表：按user_id分片 -->
<table name="t_user" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="mod-long"/>
<!-- ER表：与t_user关联 -->
<table name="t_user_detail" primaryKey="ID" dataNode="dn1,dn2,dn3"
joinKey="user_id" parentKey="id"/>
</schema>
<!-- 数据节点 -->
<dataNode name="dn1" dataHost="localhost1" database="db1"/>
<dataNode name="dn2" dataHost="localhost1" database="db2"/>
<dataNode name="dn3" dataHost="localhost1" database="db3"/>
<!-- 数据主机 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="localhost:3306" user="root" password="password"/>
</dataHost>
</mycat:schema>
分库分表最佳实践
-- 1. 分片键选择原则
-- - 查询频率高的字段
-- - 数据分布均匀
-- - 避免跨分片查询
-- 2. 分片策略
-- - 范围分片：适合时间序列数据
-- - 哈希分片：适合均匀分布
-- - 地理位置：适合位置数据
-- 3. 跨分片问题及解决方案
-- 问题1：JOIN跨分片
-- 解决：1. 应用层JOIN 2. 冗余数据 3. 全局表
-- 问题2：分页查询
-- 解决：1. 限制分页深度 2. 二次查询 3. 禁止跳页
-- 问题3：分布式事务
-- 解决：1. 最终一致性 2. TCC 3. Saga
-- 问题4：全局唯一ID
-- 解决：1. 雪花算法 2. UUID 3. 数据库自增
-- 4. 监控与运维
-- - 分片健康检查
-- - 数据均衡监控
-- - 慢查询分析
-- - 容量规划
小结
本章深入讲解了MySQL高级特性的三个重要方面：
第9章 - 查询优化重点：
- 掌握EXPLAIN执行计划的各个字段含义
- 理解查询优化器的工作原理和成本估算
- 熟练使用慢查询日志分析工具（mysqldumpslow、pt-query-digest）
- 掌握连接查询、子查询的优化策略
- 了解索引下推、分区裁剪等优化技术
第10章 - 存储过程与函数重点：
- 熟练编写存储过程（参数、变量、条件、循环、游标）
- 掌握存储函数的创建和使用
- 理解触发器的工作原理和应用场景
- 了解事件调度器的配置和管理
- 能够使用存储过程实现复杂的业务逻辑
第11章 - 分区与分表重点：
- 理解分区的类型（RANGE、LIST、HASH、KEY）和应用场景
- 掌握分区管理和维护操作
- 了解水平分表和垂直分表的区别与实现
- 了解分库分表中间件的特点和使用场景
- 掌握分表路由的实现方法
核心要点：
- EXPLAIN是SQL优化的必备工具，必须熟练掌握
- 慢查询日志是性能优化的关键依据
- 存储过程适合复杂业务逻辑，但要谨慎使用
- 分区表是处理大数据量的有效手段
- 分库分表是解决单机瓶颈的最终方案
下章预告： 下一章将学习MySQL主从复制、高可用架构、备份恢复等内容，构建可靠的MySQL生产环境。