MySQL教程FG243-MySQL查询优化

Part01-基础概念与理论知识

1.1 MySQL查询优化概述

MySQL查询优化是确保数据库高效运行的重要组成部分，通过优化SQL语句、索引设计、执行计划等，提高查询的执行速度和效率。本教程将详细介绍MySQL查询优化的概念、执行计划和优化原则。风哥教程参考MySQL官方文档查询优化部分的相关内容。更多视频教程www.fgedu.net.cn

# MySQL查询优化概述
MySQL查询优化是确保数据库高效运行的重要组成部分，通过优化SQL语句、索引设计、执行计划等，提高查询的执行速度和效率。

# MySQL查询优化的重要性
1. 提高响应速度：减少查询执行时间，提高用户体验
2. 减少资源消耗：降低CPU、内存、磁盘I/O等资源的使用
3. 提高系统吞吐量：增加并发处理能力，支持更多用户
4. 降低硬件成本：通过优化充分利用硬件资源，减少硬件投入
5. 提高系统稳定性：减少系统负载，降低系统崩溃的风险

# MySQL查询优化的组成
1. 执行计划分析：分析SQL语句的执行计划，找出优化点
2. 索引优化：设计合理的索引，提高查询效率
3. SQL优化：优化SQL语句的编写，减少执行时间
4. 表结构优化：优化表结构设计，提高查询效率
5. 数据库参数优化：调整数据库参数，提高查询性能

# MySQL查询优化的挑战
1. 执行计划分析：理解执行计划的含义，找出优化点
2. 索引设计：设计合理的索引，避免过度索引
3. SQL优化：编写高效的SQL语句，避免性能问题
4. 表结构设计：设计合理的表结构，提高查询效率
5. 性能评估：评估优化措施的效果，确保优化成功

1.2 MySQL执行计划

MySQL执行计划是MySQL执行SQL语句的详细计划，包括如何访问表、使用哪些索引、如何连接表等。学习交流加群风哥微信: itpux-com

MySQL执行计划：1. id：查询的序列号；2. select_type：查询的类型，如SIMPLE、PRIMARY、SUBQUERY等；3. table：要访问的表；4. partitions：匹配的分区；5. type：访问类型，如ALL、index、range、ref、eq_ref、const、system、NULL等；6. possible_keys：可能使用的索引；7. key：实际使用的索引；8. key_len：使用的索引长度；9. ref：使用的索引列或常量；10. rows：估计要扫描的行数；11. filtered：过滤后的行数百分比；12. Extra：额外信息，如Using where、Using index、Using temporary、Using filesort等。

1.3 MySQL查询优化原则

MySQL查询优化原则是指导查询优化的基本准则，包括索引使用、SQL编写、表结构设计等方面。学习交流加群风哥QQ113257174

# MySQL查询优化原则
1. 索引使用原则：
– 选择合适的索引列：选择经常用于查询条件、排序和分组的列作为索引
– 避免过度索引：过多的索引会增加写操作的开销
– 考虑索引选择性：选择选择性高的列作为索引
– 使用复合索引：合理使用复合索引，遵循最左前缀原则
– 定期维护索引：定期重建索引，保持索引的效率

2. SQL编写原则：
– 只查询必要的列：避免使用SELECT *
– 使用WHERE子句过滤数据：减少返回的数据量
– 避免使用SELECT DISTINCT：除非必要，否则避免使用
– 避免使用ORDER BY RAND()：会导致全表扫描
– 避免使用子查询：尽量使用JOIN代替子查询
– 避免使用OR：尽量使用IN代替OR
– 避免使用LIKE ‘%…’：会导致全表扫描
– 避免使用函数：函数会使索引失效
– 合理使用LIMIT：限制返回的数据量

3. 表结构设计原则：
– 选择合适的数据类型：使用最小的数据类型，如INT代替BIGINT
– 避免使用NULL：NULL会增加存储和查询的开销
– 合理使用范式：在范式和性能之间取得平衡
– 分区表：对于大表，使用分区表提高查询效率
– 分表：对于超大表，考虑分表策略

4. 执行计划分析原则：
– 分析执行计划：使用EXPLAIN分析SQL语句的执行计划
– 关注访问类型：尽量使用range、ref、eq_ref等高效的访问类型
– 关注rows：减少扫描的行数
– 关注Extra：避免Using temporary、Using filesort等

5. 性能评估原则：
– 测试性能：使用真实的数据和场景测试查询性能
– 对比优化前后：对比优化前后的执行时间和资源消耗
– 监控性能：持续监控查询性能，及时发现问题

Part02-生产环境规划与建议

2.1 查询优化策略

MySQL查询优化策略是确保查询效率的重要措施，需要根据业务需求和数据特点制定合理的优化策略。风哥提示：生产环境中应制定完善的查询优化策略，确保查询效率。

查询优化策略：1. 执行计划分析：分析SQL语句的执行计划，找出优化点；2. 索引优化：设计合理的索引，提高查询效率；3. SQL优化：优化SQL语句的编写，减少执行时间；4. 表结构优化：优化表结构设计，提高查询效率；5. 数据库参数优化：调整数据库参数，提高查询性能。

2.2 索引设计建议

MySQL索引设计是查询优化的基础，合理的索引设计可以显著提高查询效率。更多学习教程公众号风哥教程itpux_com

# 索引设计建议
1. 选择合适的索引列：
– 经常用于WHERE条件的列
– 经常用于ORDER BY和GROUP BY的列
– 经常用于JOIN条件的列
– 选择性高的列（唯一值比例高的列）

2. 复合索引设计：
– 遵循最左前缀原则：复合索引的顺序应该与查询条件的顺序一致
– 将选择性高的列放在前面：提高索引的选择性
– 考虑查询覆盖：设计可以覆盖查询的复合索引，减少回表操作

3. 避免过度索引：
– 每个表的索引数量不宜过多（一般不超过5个）
– 避免在频繁更新的列上创建索引
– 避免在低选择性的列上创建索引（如性别列）

4. 索引维护：
– 定期重建索引：使用OPTIMIZE TABLE命令重建索引
– 监控索引使用情况：使用SHOW INDEX和sys.schema_index_statistics查看索引使用情况
– 删除未使用的索引：删除长期未使用的索引，减少维护开销

5. 特殊索引类型：
– 全文索引：用于全文搜索
– 空间索引：用于地理空间数据
– 哈希索引：用于精确匹配的场景

# 索引设计示例
## 示例1：单表查询
# 表结构
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
age INT,
created_at DATETIME
);

# 常见查询
SELECT * FROM users WHERE email = ‘user@example.com’;
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;

# 索引设计
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_age_created_at ON users(age, created_at);

## 示例2：多表连接
# 表结构
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
created_at DATETIME
);

# 常见查询
SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 18;

# 索引设计
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_age ON users(age);

2.3 SQL编写建议

MySQL SQL编写是查询优化的重要组成部分，合理的SQL编写可以显著提高查询效率。from MySQL:www.itpux.com

# SQL编写建议
1. 只查询必要的列：
– 避免使用SELECT *，只查询需要的列
– 示例：SELECT id, name FROM users WHERE email = ‘user@example.com’;

2. 使用WHERE子句过滤数据：
– 尽早过滤数据，减少返回的数据量
– 示例：SELECT * FROM users WHERE age > 18;

3. 避免使用SELECT DISTINCT：
– 除非必要，否则避免使用DISTINCT
– 示例：SELECT name FROM users WHERE age > 18;

4. 避免使用ORDER BY RAND()：
– ORDER BY RAND()会导致全表扫描
– 替代方案：使用LIMIT和随机数
– 示例：SELECT * FROM users WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 1;

5. 避免使用子查询：
– 尽量使用JOIN代替子查询
– 示例：
— 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
— 替代方案
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id;

6. 避免使用OR：
– 尽量使用IN代替OR
– 示例：
— 使用OR
SELECT * FROM users WHERE age = 18 OR age = 20;
— 替代方案
SELECT * FROM users WHERE age IN (18, 20);

7. 避免使用LIKE ‘%…’：
– LIKE ‘%…’会导致全表扫描
– 示例：
— 全表扫描
SELECT * FROM users WHERE name LIKE ‘%test%’;
— 可以使用索引
SELECT * FROM users WHERE name LIKE ‘test%’;

8. 避免使用函数：
– 函数会使索引失效
– 示例：
— 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
— 可以使用索引
SELECT * FROM users WHERE created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

9. 合理使用LIMIT：
– 限制返回的数据量，提高查询速度
– 示例：SELECT * FROM users LIMIT 10;

10. 使用批量操作：
– 减少网络开销和服务器负载
– 示例：
— 多次插入
INSERT INTO users (name) VALUES (‘user1’);
INSERT INTO users (name) VALUES (‘user2’);
— 批量插入
INSERT INTO users (name) VALUES (‘user1’), (‘user2’);

Part03-生产环境项目实施方案

3.1 执行计划分析

MySQL执行计划分析是查询优化的关键步骤，通过分析执行计划，找出查询的性能瓶颈。

# 执行计划分析
# 步骤1：使用EXPLAIN分析执行计划
# 基本语法
EXPLAIN SELECT * FROM users WHERE id = 1;

# 步骤2：分析执行计划的各个字段
# id：查询的序列号
# select_type：查询的类型
# table：要访问的表
# partitions：匹配的分区
# type：访问类型（ALL、index、range、ref、eq_ref、const、system、NULL）
# possible_keys：可能使用的索引
# key：实际使用的索引
# key_len：使用的索引长度
# ref：使用的索引列或常量
# rows：估计要扫描的行数
# filtered：过滤后的行数百分比
# Extra：额外信息

# 步骤3：分析常见的访问类型
# ALL：全表扫描，性能最差
# index：全索引扫描，比ALL好但仍需扫描所有索引行
# range：范围扫描，使用索引进行范围查询
# ref：使用非唯一索引或唯一索引的前缀进行查询
# eq_ref：使用唯一索引进行查询，最多返回一行
# const：使用主键或唯一索引进行查询，只返回一行
# system：表只有一行数据，是const的特例
# NULL：不需要访问表

# 步骤4：分析Extra字段
# Using where：使用WHERE子句过滤数据
# Using index：使用索引覆盖，不需要回表
# Using temporary：使用临时表，性能较差
# Using filesort：使用文件排序，性能较差
# Using join buffer：使用连接缓冲区
# Using index condition：使用索引条件推送

# 步骤5：使用EXPLAIN ANALYZE分析执行计划
# EXPLAIN ANALYZE会实际执行查询并返回执行统计信息
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

# 步骤6：验证执行计划分析结果
# 对比优化前后的执行计划
# 确认优化措施是否有效

# 执行计划分析最佳实践
1. 关注访问类型：尽量使用range、ref、eq_ref等高效的访问类型
2. 关注rows：减少扫描的行数
3. 关注Extra：避免Using temporary、Using filesort等
4. 对比分析：对比不同SQL语句的执行计划，选择最优方案
5. 实际验证：使用EXPLAIN ANALYZE实际执行查询，验证执行计划的准确性

3.2 索引优化

MySQL索引优化是查询优化的基础，合理的索引设计可以显著提高查询效率。

# 索引优化
# 步骤1：分析索引使用情况
# 查看表的索引
SHOW INDEX FROM users;

# 查看索引使用情况（需要开启performance_schema）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = ‘test’ AND object_name = ‘users’;

# 步骤2：识别缺失的索引
# 使用pt-index-usage分析慢查询日志，识别缺失的索引
pt-index-usage /var/log/mysql/slow.log

# 步骤3：添加缺失的索引
# 添加单列索引
ALTER TABLE users ADD INDEX idx_email (email);

# 添加复合索引
ALTER TABLE users ADD INDEX idx_age_created_at (age, created_at);

# 步骤4：优化现有索引
# 重建索引
ALTER TABLE users DROP INDEX idx_old, ADD INDEX idx_new (column1, column2);

# 步骤5：删除未使用的索引
# 删除未使用的索引
ALTER TABLE users DROP INDEX idx_unused;

# 步骤6：验证索引优化效果
# 分析优化前后的执行计划
EXPLAIN SELECT * FROM users WHERE email = ‘user@example.com’;

# 测试查询响应时间
# 优化前
SELECT * FROM users WHERE email = ‘user@example.com’;
# 执行时间：0.5秒

# 优化后
SELECT * FROM users WHERE email = ‘user@example.com’;
# 执行时间：0.01秒

# 索引优化最佳实践
1. 选择合适的索引列：选择经常用于查询条件、排序和分组的列
2. 遵循最左前缀原则：复合索引的顺序应该与查询条件的顺序一致
3. 考虑索引覆盖：设计可以覆盖查询的复合索引
4. 避免过度索引：每个表的索引数量不宜过多
5. 定期维护索引：定期重建索引，删除未使用的索引

3.3 SQL优化

MySQL SQL优化是查询优化的重要组成部分，合理的SQL编写可以显著提高查询效率。

# SQL优化
# 步骤1：优化SELECT语句
# 只查询必要的列
— 优化前
SELECT * FROM users WHERE email = ‘user@example.com’;

— 优化后
SELECT id, name, email FROM users WHERE email = ‘user@example.com’;

# 避免使用SELECT DISTINCT
— 优化前
SELECT DISTINCT name FROM users WHERE age > 18;

— 优化后
SELECT name FROM users WHERE age > 18 GROUP BY name;

# 步骤2：优化WHERE子句
# 避免使用函数
— 优化前
SELECT * FROM users WHERE YEAR(created_at) = 2024;

— 优化后
SELECT * FROM users WHERE created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

# 避免使用OR
— 优化前
SELECT * FROM users WHERE age = 18 OR age = 20;

— 优化后
SELECT * FROM users WHERE age IN (18, 20);

# 避免使用LIKE ‘%…’
— 优化前
SELECT * FROM users WHERE name LIKE ‘%test%’;

— 优化后（如果业务允许）
SELECT * FROM users WHERE name LIKE ‘test%’;

# 步骤3：优化JOIN语句
# 使用INNER JOIN代替LEFT JOIN（如果可能）
— 优化前
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id;

— 优化后
SELECT u.*, o.* FROM users u INNER JOIN orders o ON u.id = o.user_id;

# 小表驱动大表
— 优化前（大表驱动小表）
SELECT u.*, o.* FROM orders o JOIN users u ON o.user_id = u.id;

— 优化后（小表驱动大表）
SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id;

# 步骤4：优化ORDER BY和GROUP BY
# 使用索引排序
— 优化前（文件排序）
SELECT * FROM users ORDER BY name;

— 优化后（索引排序）
ALTER TABLE users ADD INDEX idx_name (name);
SELECT * FROM users ORDER BY name;

# 避免使用ORDER BY RAND()
— 优化前
SELECT * FROM users ORDER BY RAND() LIMIT 10;

— 优化后
SELECT * FROM users WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 10;

# 步骤5：优化子查询
# 使用JOIN代替子查询
— 优化前
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

— 优化后
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100 GROUP BY u.id;

# 步骤6：验证SQL优化效果
# 分析优化前后的执行计划
EXPLAIN SELECT id, name, email FROM users WHERE email = ‘user@example.com’;

# 测试查询响应时间
# 优化前
SELECT * FROM users WHERE email = ‘user@example.com’;
# 执行时间：0.5秒

# 优化后
SELECT id, name, email FROM users WHERE email = ‘user@example.com’;
# 执行时间：0.01秒

# SQL优化最佳实践
1. 只查询必要的列：避免使用SELECT *
2. 使用WHERE子句过滤数据：减少返回的数据量
3. 避免使用函数：函数会使索引失效
4. 避免使用OR：尽量使用IN代替OR
5. 避免使用LIKE ‘%…’：会导致全表扫描
6. 使用JOIN代替子查询：提高查询效率
7. 小表驱动大表：减少连接次数
8. 使用索引排序：避免文件排序
9. 合理使用LIMIT：限制返回的数据量
10. 使用批量操作：减少网络开销

3.4 查询性能验证

MySQL查询性能验证是确保优化效果的重要步骤，通过测试和监控，验证查询优化是否达到预期效果。

# 查询性能验证
# 步骤1：测试查询响应时间
# 使用MySQL客户端测试
mysql> SELECT BENCHMARK(1000000, SELECT * FROM users WHERE id = 1);

# 使用pt-query-digest测试
pt-query-digest –query=”SELECT * FROM users WHERE id = 1″ –host=localhost –user=root –password=password –database=test

# 步骤2：测试并发性能
# 使用sysbench测试
# 准备测试数据
sysbench –db-driver=mysql –mysql-host=localhost –mysql-port=3306 –mysql-user=root –mysql-password=password –mysql-db=test –table-size=1000000 –tables=10 prepare

# 运行OLTP测试
sysbench –db-driver=mysql –mysql-host=localhost –mysql-port=3306 –mysql-user=root –mysql-password=password –mysql-db=test –table-size=1000000 –tables=10 –threads=16 –time=60 –report-interval=10 oltp_read_write run

# 清理测试数据
sysbench –db-driver=mysql –mysql-host=localhost –mysql-port=3306 –mysql-user=root –mysql-password=password –mysql-db=test cleanup

# 步骤3：监控查询性能
# 使用Performance Schema监控
SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;

# 使用慢查询日志监控
SHOW VARIABLES LIKE ‘%slow%’;

# 步骤4：对比优化前后的性能
# 记录优化前的性能指标
# 记录优化后的性能指标
# 分析性能提升情况

# 步骤5：验证业务场景
# 在实际业务场景中验证查询性能
# 确保业务系统正常运行

# 步骤6：文档记录
# 记录查询优化过程和结果
# 总结优化经验和教训

# 查询性能验证最佳实践
1. 测试多种场景：测试不同数据量、不同并发下的查询性能
2. 对比分析：对比优化前后的性能指标，评估优化效果
3. 实际验证：在实际业务场景中验证查询性能
4. 持续监控：持续监控查询性能，及时发现问题
5. 文档记录：记录查询优化过程和结果，便于后续参考

Part04-生产案例与实战讲解

4.1 执行计划分析案例

执行计划分析是MySQL查询优化的关键步骤，以下是具体的执行计划分析案例。

# 执行计划分析案例
# 环境说明
# MySQL 8.0.29
# 数据库：test
# 表：users（100万行数据）
# 表结构：
# CREATE TABLE users (
# id INT PRIMARY KEY,
# name VARCHAR(50),
# email VARCHAR(100),
# age INT,
# created_at DATETIME
# );

# 问题描述
# 需要分析SQL语句的执行计划，找出性能瓶颈

# 解决方案
## 步骤1：分析简单查询的执行计划
# 分析主键查询
EXPLAIN SELECT * FROM users WHERE id = 1;

# 分析非索引列查询
EXPLAIN SELECT * FROM users WHERE age = 18;

## 步骤2：分析复杂查询的执行计划
# 分析JOIN查询
EXPLAIN SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 18;

# 预期输出：
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+————-+
| 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000000 | 90.00 | Using where |
| 1 | SIMPLE | o | NULL | ref | idx_user_id | idx_user_id | 4 | test.u.id | 1 | 100.00 | NULL |
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+————-+

## 步骤3：分析索引使用情况
# 添加age列索引
ALTER TABLE users ADD INDEX idx_age (age);

# 重新分析查询
EXPLAIN SELECT * FROM users WHERE age = 18;

## 步骤4：分析排序操作
# 分析ORDER BY操作
EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;

# 添加复合索引
ALTER TABLE users ADD INDEX idx_age_created_at (age, created_at);

# 重新分析查询
EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;

# 处理效果
# 成功分析了SQL语句的执行计划
# 发现了性能瓶颈：非索引列查询导致全表扫描，排序操作导致文件排序
# 通过添加索引，优化了执行计划
# 提高了查询性能

4.2 索引优化案例

索引优化是MySQL查询优化的基础，以下是具体的索引优化案例。

# 索引优化案例
# 环境说明
# MySQL 8.0.29
# 数据库：test
# 表：users（100万行数据）
# 表结构：
# CREATE TABLE users (
# id INT PRIMARY KEY,
# name VARCHAR(50),
# email VARCHAR(100),
# age INT,
# created_at DATETIME
# );

# 问题描述
# 发现查询响应时间长，需要优化索引

# 解决方案
## 步骤1：分析查询需求
# 常见查询：
# 1. SELECT * FROM users WHERE email = ‘user@example.com’;
# 2. SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;
# 3. SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 18;

## 步骤2：分析现有索引
# 查看现有索引
SHOW INDEX FROM users;

## 步骤3：添加缺失的索引
# 添加email列索引
ALTER TABLE users ADD INDEX idx_email (email);

# 添加age和created_at的复合索引
ALTER TABLE users ADD INDEX idx_age_created_at (age, created_at);

## 步骤4：优化orders表的索引
# 查看orders表结构
SHOW CREATE TABLE orders;

# 添加user_id列索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

## 步骤5：验证索引优化效果
# 分析查询1的执行计划
EXPLAIN SELECT * FROM users WHERE email = ‘user@example.com’;

# 分析查询2的执行计划
EXPLAIN SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;

# 分析查询3的执行计划
EXPLAIN SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 18;

# 预期输出：
+—-+————-+——-+————+——+—————+——————+———+——————+———+———-+—————-+-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——————+———+——————+———+———-+—————-+-
| 1 | SIMPLE | u | NULL | range | PRIMARY,idx_age_created_at | idx_age_created_at | 5 | NULL | 900000 | 100.00 | Using index condition |
| 1 | SIMPLE | o | NULL | ref | idx_user_id | idx_user_id | 4 | test.u.id | 1 | 100.00 | NULL |
+—-+————-+——-+————+——+—————+——————+———+——————+———+———-+—————-+-

## 步骤6：测试查询响应时间
# 测试查询1
SELECT * FROM users WHERE email = ‘user@example.com’;
# 优化前：0.5秒
# 优化后：0.01秒

# 测试查询2
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;
# 优化前：2.0秒
# 优化后：0.05秒

# 测试查询3
SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 18 LIMIT 10;
# 优化前：3.0秒
# 优化后：0.1秒

# 处理效果
# 成功优化了索引设计
# 添加了email列索引和age、created_at的复合索引
# 优化了orders表的索引
# 查询响应时间显著减少
# 提高了系统性能

4.3 SQL优化案例

SQL优化是MySQL查询优化的重要组成部分，以下是具体的SQL优化案例。

# SQL优化案例
# 环境说明
# MySQL 8.0.29
# 数据库：test
# 表：users（100万行数据）
# 表结构：
# CREATE TABLE users (
# id INT PRIMARY KEY,
# name VARCHAR(50),
# email VARCHAR(100),
# age INT,
# created_at DATETIME
# );

# 问题描述
# 发现SQL语句执行效率低，需要优化SQL

# 解决方案
## 步骤1：优化SELECT语句
# 优化前：使用SELECT *
SELECT * FROM users WHERE email = ‘user@example.com’;

# 优化后：只查询必要的列
SELECT id, name, email FROM users WHERE email = ‘user@example.com’;

## 步骤2：优化WHERE子句
# 优化前：使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;

# 优化后：使用范围查询
SELECT * FROM users WHERE created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

# 优化前：使用OR
SELECT * FROM users WHERE age = 18 OR age = 20;

# 优化后：使用IN
SELECT * FROM users WHERE age IN (18, 20);

## 步骤3：优化JOIN语句
# 优化前：使用子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

# 优化后：使用JOIN
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100 GROUP BY u.id;

## 步骤4：优化ORDER BY语句
# 优化前：使用ORDER BY RAND()
SELECT * FROM users ORDER BY RAND() LIMIT 10;

# 优化后：使用随机数
SELECT * FROM users WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 10;

## 步骤5：优化分组语句
# 优化前：使用GROUP BY without索引
SELECT age, COUNT(*) FROM users GROUP BY age;

# 优化后：添加索引
ALTER TABLE users ADD INDEX idx_age (age);
SELECT age, COUNT(*) FROM users GROUP BY age;

## 步骤6：验证SQL优化效果
# 分析优化前后的执行计划
EXPLAIN SELECT id, name, email FROM users WHERE email = ‘user@example.com’;

# 测试查询响应时间
# 优化前：0.5秒
# 优化后：0.01秒

# 处理效果
# 成功优化了SQL语句
# 只查询必要的列，使用范围查询，使用IN代替OR，使用JOIN代替子查询
# 优化了ORDER BY和GROUP BY语句
# 查询响应时间显著减少
# 提高了系统性能

4.4 复杂查询优化案例

复杂查询优化是MySQL查询优化的挑战，以下是具体的复杂查询优化案例。

# 复杂查询优化案例
# 环境说明
# MySQL 8.0.29
# 数据库：test
# 表：users（100万行数据）
# 表：orders（500万行数据）
# 表：products（10万行数据）

# 问题描述
# 需要优化一个复杂的多表连接查询

# 原始查询
SELECT
u.name,
o.order_id,
o.amount,
p.product_name
FROM
users u
JOIN
orders o ON u.id = o.user_id
JOIN
products p ON o.product_id = p.id
WHERE
u.age > 18
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
AND p.category = ‘electronics’
ORDER BY
o.amount DESC
LIMIT 10;

# 解决方案
## 步骤1：分析执行计划
EXPLAIN SELECT
u.name,
o.order_id,
o.amount,
p.product_name
FROM
users u
JOIN
orders o ON u.id = o.user_id
JOIN
products p ON o.product_id = p.id
WHERE
u.age > 18
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
AND p.category = ‘electronics’
ORDER BY
o.amount DESC
LIMIT 10;

# 预期输出：
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+———————————+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+———————————+
| 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000000 | 90.00 | Using where |
| 1 | SIMPLE | o | NULL | ref | idx_user_id | idx_user_id | 4 | test.u.id | 5 | 30.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | NULL | ref | PRIMARY | PRIMARY | 4 | test.o.product_id | 1 | 10.00 | Using where |
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+———————————+

## 步骤2：优化索引
# 添加users表的age索引
ALTER TABLE users ADD INDEX idx_age (age);

# 添加orders表的复合索引
ALTER TABLE orders ADD INDEX idx_user_id_created_at_amount (user_id, created_at, amount);

# 添加products表的category索引
ALTER TABLE products ADD INDEX idx_category (category);

## 步骤3：优化SQL语句
# 优化前
SELECT
u.name,
o.order_id,
o.amount,
p.product_name
FROM
users u
JOIN
orders o ON u.id = o.user_id
JOIN
products p ON o.product_id = p.id
WHERE
u.age > 18
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
AND p.category = ‘electronics’
ORDER BY
o.amount DESC
LIMIT 10;

# 优化后：调整JOIN顺序，使用小表驱动大表
SELECT
u.name,
o.order_id,
o.amount,
p.product_name
FROM
products p
JOIN
orders o ON p.id = o.product_id
JOIN
users u ON o.user_id = u.id
WHERE
p.category = ‘electronics’
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
AND u.age > 18
ORDER BY
o.amount DESC
LIMIT 10;

## 步骤4：验证优化效果
# 分析优化后的执行计划
EXPLAIN SELECT
u.name,
o.order_id,
o.amount,
p.product_name
FROM
products p
JOIN
orders o ON p.id = o.product_id
JOIN
users u ON o.user_id = u.id
WHERE
p.category = ‘electronics’
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
AND u.age > 18
ORDER BY
o.amount DESC
LIMIT 10;

# 预期输出：
+—-+————-+——-+————+——+———————————-+———————————-+———+——————+———+———-+—————-+-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+———————————-+———————————-+———+——————+———+———-+—————-+-
| 1 | SIMPLE | p | NULL | ref | PRIMARY,idx_category | idx_category | 102 | const | 10000 | 100.00 | Using index condition |
| 1 | SIMPLE | o | NULL | ref | idx_user_id,idx_user_id_created_at_amount | idx_user_id_created_at_amount | 4 | test.p.id | 50 | 30.00 | Using index condition |
| 1 | SIMPLE | u | NULL | ref | PRIMARY,idx_age | PRIMARY | 4 | test.o.user_id | 1 | 90.00 | Using where |
+—-+————-+——-+————+——+———————————-+———————————-+———+——————+———+———-+—————-+-

## 步骤5：测试查询响应时间
# 优化前：5.0秒
# 优化后：0.1秒

# 处理效果
# 成功优化了复杂查询
# 添加了必要的索引，调整了JOIN顺序
# 查询响应时间从5.0秒减少到0.1秒
# 提高了系统性能

Part05-风哥经验总结与分享

通过多年的MySQL数据库管理经验，我总结了以下关于MySQL查询优化的关键点：

风哥提示：MySQL查询优化是一个持续的过程，需要根据业务需求和数据特点不断调整和优化。

1. 执行计划分析：使用EXPLAIN分析SQL语句的执行计划，找出性能瓶颈。

2. 索引优化：设计合理的索引，包括单列索引和复合索引，遵循最左前缀原则。

3. SQL优化：优化SQL语句的编写，只查询必要的列，使用WHERE子句过滤数据，避免使用函数和OR。

4. JOIN优化：使用合适的JOIN类型，调整JOIN顺序，小表驱动大表。

5. 排序和分组优化：使用索引排序，避免使用ORDER BY RAND()，合理使用GROUP BY。

6. 子查询优化：尽量使用JOIN代替子查询，提高查询效率。

7. 性能验证：定期测试查询性能，对比优化前后的效果。

8. 持续监控：持续监控查询性能，及时发现和解决性能问题。

生产环境最佳实践：1. 建立完善的索引体系：根据查询需求设计合理的索引；2. 优化SQL语句：编写高效的SQL语句，避免性能问题；3. 分析执行计划：定期分析SQL语句的执行计划，找出优化点；4. 监控查询性能：持续监控查询性能，及时发现问题；5. 定期优化：定期进行查询优化，适应业务需求的变化；6. 培训开发人员：培训开发人员SQL优化技能，提高代码质量；7. 使用工具：使用专业的SQL优化工具，如pt-query-digest、MySQL Enterprise Monitor等；8. 文档化：记录查询优化过程和结果，便于后续参考；9. 持续学习：关注MySQL新版本的性能改进，及时应用新特性；10. 建立性能基准：建立查询性能基准，便于对比优化效果。

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html