第15课: 性能优化

慢查询分析、EXPLAIN、查询优化、性能监控

性能优化概述

数据库性能优化是一个系统工程，涉及查询优化、索引设计、配置调优、硬件升级等多个方面。

MySQL性能优化流程图

慢查询日志

-- 示例1: 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 示例2: 分析慢查询日志
-- 使用 mysqldumpslow 工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  -- 按时间排序，显示前10条
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log  -- 按次数排序

EXPLAIN 执行计划分析

EXPLAIN 显示 MySQL 如何执行查询，是性能优化的核心工具。

-- 示例3: 基本 EXPLAIN 使用
EXPLAIN SELECT * FROM users WHERE age > 18;

EXPLAIN SELECT u.username, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 100;

EXPLAIN 输出字段解析

字段	说明
id	查询序号，id相同从上到下执行，id不同值越大越先执行
select_type	查询类型：SIMPLE、PRIMARY、SUBQUERY、UNION等
table	访问的表名
type	访问类型：ALL、index、range、ref、eq_ref、const、system
possible_keys	可能使用的索引
key	实际使用的索引
rows	预计扫描的行数
Extra	额外信息：Using index、Using where、Using filesort等

type 字段性能排序

从最优到最差：system > const > eq_ref > ref > range > index > ALL

-- 示例4: 不同 type 的查询
-- ALL（全表扫描，最差）
EXPLAIN SELECT * FROM users WHERE username LIKE '%test%';

-- index（索引扫描）
EXPLAIN SELECT id FROM users;

-- range（范围扫描）
EXPLAIN SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- ref（非唯一索引扫描）
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- eq_ref（唯一索引扫描）
EXPLAIN SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- const（常量查询，最优）
EXPLAIN SELECT * FROM users WHERE id = 1;

查询优化技巧

-- 示例5: 避免 SELECT *
-- 不好的写法
SELECT * FROM users WHERE age > 18;

-- 好的写法
SELECT id, username, email FROM users WHERE age > 18;

-- 示例6: 使用 LIMIT 限制结果
-- 不好的写法
SELECT * FROM orders ORDER BY order_date DESC;

-- 好的写法
SELECT * FROM orders ORDER BY order_date DESC LIMIT 100;

-- 示例7: 避免在 WHERE 中使用函数
-- 不好的写法（索引失效）
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 好的写法
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 示例8: 使用 JOIN 代替子查询
-- 不好的写法
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);

-- 好的写法
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 1000;

索引优化

-- 示例9: 覆盖索引（避免回表）
-- 创建覆盖索引
CREATE INDEX idx_cover ON users(username, email, age);

-- 查询只使用索引字段
SELECT username, email, age FROM users WHERE username = 'test';
-- Extra: Using index（性能最优）

-- 示例10: 前缀索引
-- 对长字符串使用前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 分析前缀长度的选择性
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15
FROM users;

索引设计原则

索引设计的核心原则：

选择性原则：索引字段的选择性越高（唯一值越多），索引效果越好
最左前缀原则：复合索引遵循最左前缀匹配规则
覆盖查询原则：尽量设计能够覆盖常用查询的索引
避免过度索引：每个索引都会增加写入开销，只创建必要的索引
定期维护原则：定期检查和优化索引使用情况

-- 示例11: 复合索引设计
-- 针对查询: SELECT * FROM orders WHERE user_id = 1 AND status = 'completed' ORDER BY order_date DESC
CREATE INDEX idx_user_status_date ON orders(user_id, status, order_date DESC);

-- 示例12: 索引失效场景
-- 1. 函数操作
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 索引失效

-- 2. 类型转换
SELECT * FROM users WHERE id = '123';  -- 索引可能失效

-- 3. 不等于操作
SELECT * FROM users WHERE age != 18;  -- 索引可能失效

-- 4. LIKE 前导通配符
SELECT * FROM users WHERE username LIKE '%test';  -- 索引失效

-- 5. OR 条件（部分字段无索引）
SELECT * FROM users WHERE age = 18 OR name = 'test';  -- 如果name无索引，索引失效

高级查询优化技巧

-- 示例13: 批量操作优化
-- 不好的写法（多次单行插入）
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');
INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');

-- 好的写法（批量插入）
INSERT INTO users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

-- 示例14: 分页查询优化（避免偏移量过大）
-- 不好的写法（大偏移量）
SELECT * FROM users ORDER BY id LIMIT 100000, 10;  -- 扫描100010行

-- 好的写法（使用游标分页）
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;  -- 只扫描10行

-- 示例15: 条件优化
-- 不好的写法
SELECT * FROM users WHERE age > 18 AND age < 30;

-- 好的写法（使用BETWEEN）
SELECT * FROM users WHERE age BETWEEN 19 AND 29;

数据库架构优化

架构优化策略：

读写分离：主库处理写操作，从库处理读操作
分库分表：按业务或数据范围拆分数据库和表
缓存策略：使用Redis等缓存减轻数据库压力
垂直拆分：将不同业务的表拆分到不同数据库
水平拆分：将大表按规则拆分到多个子表

-- 示例16: 水平分表策略（按时间）
CREATE TABLE orders_2023 (
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 示例17: 垂直分表策略（按访问频率）
-- 核心表（高频访问）
CREATE TABLE users_core (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 扩展表（低频访问）
CREATE TABLE users_extra (
    user_id INT PRIMARY KEY,
    phone VARCHAR(20),
    address VARCHAR(200),
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users_core(user_id)
);

性能监控工具

-- 示例18: 使用 Performance Schema
-- 启用 Performance Schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statement%';

-- 查看慢查询
SELECT * FROM performance_schema.events_statements_history_long
WHERE timer_wait > 10000000000  -- 10秒
ORDER BY timer_wait DESC
LIMIT 10;

-- 示例19: 使用 sys  schema
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看全表扫描的查询
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY rows_examined DESC
LIMIT 10;

-- 查看使用临时表的查询
SELECT * FROM sys.statements_with_temp_tables
ORDER BY temp_tables DESC
LIMIT 10;

生产环境性能调优案例

案例1：电商系统订单查询优化

问题：订单列表页面加载缓慢，响应时间超过3秒

分析：使用EXPLAIN分析发现订单表全表扫描，且关联查询未使用索引

解决方案：

为订单表的user_id、status、order_date字段创建复合索引
优化查询语句，避免SELECT *，只查询必要字段
使用LIMIT限制分页大小，避免一次性加载过多数据
实现结果缓存，减少重复查询

效果：页面加载时间从3秒优化到0.5秒以内

案例2：用户登录系统性能优化

问题：高峰期登录响应缓慢，并发用户数超过500时系统不稳定

分析：数据库连接池配置不合理，登录验证查询未优化

解决方案：

调整连接池配置，增加最大连接数到1000
为用户表的username和email字段创建唯一索引
实现登录缓存，减少数据库查询
优化认证逻辑，减少数据库交互次数

效果：系统稳定支持1000并发用户，登录响应时间保持在0.1秒以内

配置优化最佳实践

-- 示例20: 针对不同硬件配置的优化
-- 1. 内存配置（8GB内存服务器）
[mysqld]
innodb_buffer_pool_size = 6G  -- 75% of RAM
innodb_log_buffer_size = 32M
key_buffer_size = 128M

-- 2. 并发配置
max_connections = 1000
max_connect_errors = 10000
thread_cache_size = 100

-- 3. 存储配置
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

-- 4. 查询优化
query_cache_size = 0  -- MySQL 8.0已移除
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 2M

-- 5. 日志配置
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

性能优化最佳实践：

合理使用索引：为常用查询字段建立索引，但避免过度索引
优化查询语句：避免 SELECT *，使用 LIMIT，避免函数操作
分析执行计划：使用 EXPLAIN 分析慢查询
定期维护：OPTIMIZE TABLE 整理碎片，ANALYZE TABLE 更新统计信息
监控性能：关注慢查询日志，及时发现问题
硬件升级：增加内存、使用 SSD、提升网络带宽
读写分离：使用主从复制，读操作分散到从库

性能监控

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- 查看查询缓存
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

配置优化

-- 关键配置参数（my.cnf）
[mysqld]
# 缓冲池大小（建议设置为物理内存的 70-80%）
innodb_buffer_pool_size = 4G

# 日志文件大小
innodb_log_file_size = 512M

# 最大连接数
max_connections = 500

# 查询缓存（MySQL 8.0 已移除）
query_cache_size = 64M
query_cache_type = 1

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序缓冲区
sort_buffer_size = 2M
read_buffer_size = 2M

表维护

-- 优化表（整理碎片）
OPTIMIZE TABLE users;
OPTIMIZE TABLE orders;

-- 分析表（更新统计信息）
ANALYZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

实战场景：性能诊断流程

-- 1. 识别慢查询
SELECT * FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

-- 2. 分析执行计划
EXPLAIN SELECT u.username, COUNT(o.order_id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 3. 检查索引使用
SHOW INDEX FROM users;
SHOW INDEX FROM orders;

-- 4. 优化查询
-- 添加必要的索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 5. 验证优化效果
EXPLAIN SELECT u.username, COUNT(o.order_id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 6. 监控性能指标
SELECT
    table_name,
    table_rows,
    avg_row_length,
    data_length,
    index_length
FROM information_schema.tables
WHERE table_schema = 'mydb';

分区表优化（高级）

-- 按日期分区
CREATE TABLE orders_partitioned (
    order_id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10,2),
    order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询自动使用分区裁剪
EXPLAIN SELECT * FROM orders_partitioned
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

练习题：

开启慢查询日志，设置阈值为1秒
使用 EXPLAIN 分析一个复杂查询的执行计划
找出一个全表扫描的查询，并通过添加索引优化
比较 SELECT * 和指定字段查询的性能差异
优化一个使用了函数的 WHERE 条件
使用 OPTIMIZE TABLE 优化一个表
查看当前数据库的连接数和慢查询数量
设计一个完整的性能优化方案（包括索引、查询、配置）

总结

性能优化是一个持续的过程，需要：

定期监控数据库性能指标
分析慢查询日志，优化问题查询
合理设计索引，避免过度索引
优化查询语句，遵循最佳实践
根据业务增长调整配置参数
考虑架构优化：读写分离、分库分表

上一课下一课