本文档风哥主要介绍MySQL 8.4的优化技术，包括参数优化、索引优化、查询优化和存储引擎优化等方面。风哥教程参考MySQL官方文档Optimization等。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 MySQL优化概述
MySQL优化是指通过调整系统配置、数据库结构和查询语句等方式，提高MySQL数据库的性能和可靠性。学习交流加群风哥微信: itpux-com
1.2 优化层次
MySQL优化包括以下几个层次：
- 硬件层：服务器硬件配置优化
- 系统层：操作系统参数优化
- 数据库层：MySQL参数和配置优化
- 应用层：应用程序和查询语句优化
1.3 性能指标
MySQL性能指标包括：
- 响应时间：查询执行时间
- 吞吐量：单位时间内处理的查询数量
- 并发数：同时处理的连接数
- 资源利用率：CPU、内存、磁盘I/O等资源的使用情况
Part02-生产环境规划与建议
2.1 硬件规划
根据业务需求规划MySQL服务器的硬件配置，包括CPU、内存、存储等。
2.2 系统配置
优化操作系统参数，包括内核参数、文件系统设置等。
2.3 数据库设计
合理设计数据库结构，包括表结构、索引设计等。
Part03-生产环境项目实施方案
3.1 参数优化
MySQL参数优化的操作：
# vi /etc/my.cnf
[mysqld]
# 基础配置
datadir=/mysql/data
socket=/tmp/mysql.sock
log-error=/mysql/logs/error.log
pid-file=/mysql/data/mysqld.pid
# 内存配置
innodb_buffer_pool_size=16G
key_buffer_size=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=4M
# 连接配置
max_connections=1000
max_connect_errors=10000
wait_timeout=3600
interactive_timeout=3600
# 日志配置
slow_query_log=1
slow_query_log_file=/mysql/logs/slow.log
long_query_time=1
log_bin=/mysql/binlog/mysql-bin
expire_logs_days=7
# 存储引擎配置
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_log_buffer_size=32M
# 重启MySQL服务
# systemctl restart mysqld
# 验证参数
# mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;”
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 17179869184 |
+————————-+————+
3.2 索引优化
MySQL索引优化的操作：
# mysql -u root -p -e “CREATE INDEX idx_name ON fgedudb.fgedu_users (name);”
# 创建唯一索引
# mysql -u root -p -e “CREATE UNIQUE INDEX idx_email ON fgedudb.fgedu_users (email);”
# 创建复合索引
# mysql -u root -p -e “CREATE INDEX idx_name_age ON fgedudb.fgedu_users (name, age);”
# 查看索引
# mysql -u root -p -e “SHOW INDEX FROM fgedudb.fgedu_users;”
# 删除索引
# mysql -u root -p -e “DROP INDEX idx_name ON fgedudb.fgedu_users;”
# 分析表
# mysql -u root -p -e “ANALYZE TABLE fgedudb.fgedu_users;”
# 优化表
# mysql -u root -p -e “OPTIMIZE TABLE fgedudb.fgedu_users;”
3.3 查询优化
MySQL查询优化的操作：
# mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Slow_queries’;”
# 分析慢查询日志
# mysqldumpslow /mysql/logs/slow.log
# 使用EXPLAIN分析查询
# mysql -u root -p -e “EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE name = ‘张三’;”
+—-+————-+———–+————+——+—————+———+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———–+————+——+—————+———+———+——-+——+———-+——-+
| 1 | SIMPLE | fgedu_users | NULL | ref | idx_name | idx_name | 202 | const | 1 | 100.00 | NULL |
+—-+————-+———–+————+——+—————+———+———+——-+——+———-+——-+
# 优化查询语句
# 原查询
# SELECT * FROM fgedudb.fgedu_users WHERE age > 25;
# 优化后
# SELECT id, name, email FROM fgedudb.fgedu_users WHERE age > 25;
# 使用索引覆盖
# SELECT id, name FROM fgedudb.fgedu_users WHERE name = ‘张三’;
# 避免全表扫描
# SELECT * FROM fgedudb.fgedu_users WHERE name LIKE ‘张%’;
# 避免使用函数
# SELECT * FROM fgedudb.fgedu_users WHERE YEAR(created_at) = 2026;
# 优化后
# SELECT * FROM fgedudb.fgedu_users WHERE created_at BETWEEN ‘2026-01-01’ AND ‘2026-12-31’;
3.4 存储引擎优化
MySQL存储引擎优化的操作：
# mysql -u root -p -e “SHOW ENGINES;”
# 查看表的存储引擎
# mysql -u root -p -e “SHOW CREATE TABLE fgedudb.fgedu_users;”
# 修改表的存储引擎
# mysql -u root -p -e “ALTER TABLE fgedudb.fgedu_users ENGINE=InnoDB;”
# InnoDB优化参数
# vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=16G
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_log_buffer_size=32M
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_purge_threads=4
innodb_thread_concurrency=0
# 重启MySQL服务
# systemctl restart mysqld
Part04-生产案例与实战讲解
4.1 参数优化案例
MySQL参数优化的实际案例：
# 编辑配置文件
# vi /etc/my.cnf
[mysqld]
# 内存配置
innodb_buffer_pool_size=16G # 建议设置为物理内存的50-70%
key_buffer_size=256M # 用于MyISAM表的索引缓存
sort_buffer_size=2M # 排序缓存
tmp_table_size=64M # 临时表大小
max_heap_table_size=64M # 内存表大小
# 案例2：优化连接参数
# 编辑配置文件
# vi /etc/my.cnf
[mysqld]
# 连接配置
max_connections=1000 # 最大连接数
max_connect_errors=10000 # 最大连接错误数
wait_timeout=3600 # 非活动连接超时时间
interactive_timeout=3600 # 交互式连接超时时间
# 案例3：优化日志参数
# 编辑配置文件
# vi /etc/my.cnf
[mysqld]
# 日志配置
slow_query_log=1 # 启用慢查询日志
slow_query_log_file=/mysql/logs/slow.log # 慢查询日志文件
long_query_time=1 # 慢查询阈值（秒）
log_bin=/mysql/binlog/mysql-bin # 二进制日志
log_bin_index=/mysql/binlog/mysql-bin.index
max_binlog_size=1G # 二进制日志大小
expire_logs_days=7 # 二进制日志保留天数
# 重启MySQL服务
# systemctl restart mysqld
# 验证参数
# mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;”
# mysql -u root -p -e “SHOW VARIABLES LIKE ‘max_connections’;”
# mysql -u root -p -e “SHOW VARIABLES LIKE ‘slow_query_log’;”
4.2 查询优化案例
MySQL查询优化的实际案例：
# 原查询
# SELECT * FROM fgedudb.fgedu_users WHERE age > 25;
# 优化后
# SELECT id, name, email FROM fgedudb.fgedu_users WHERE age > 25;
# 案例2：优化JOIN查询
# 原查询
# SELECT * FROM fgedudb.fgedu_users u, fgedudb.fgedu_orders o WHERE u.id = o.user_id;
# 优化后
# SELECT u.id, u.name, o.order_id, o.amount FROM fgedudb.fgedu_users u INNER JOIN fgedudb.fgedu_orders o ON u.id = o.user_id;
# 案例3：优化GROUP BY查询
# 原查询
# SELECT age, COUNT(*) FROM fgedudb.fgedu_users GROUP BY age;
# 优化后
# SELECT age, COUNT(*) FROM fgedudb.fgedu_users GROUP BY age ORDER BY NULL;
# 案例4：优化子查询
# 原查询
# SELECT * FROM fgedudb.fgedu_users WHERE id IN (SELECT user_id FROM fgedudb.fgedu_orders WHERE amount > 100);
# 优化后
# SELECT u.* FROM fgedudb.fgedu_users u INNER JOIN (SELECT DISTINCT user_id FROM fgedudb.fgedu_orders WHERE amount > 100) o ON u.id = o.user_id;
# 案例5：使用索引覆盖
# 创建复合索引
# mysql -u root -p -e “CREATE INDEX idx_age_name ON fgedudb.fgedu_users (age, name);”
# 使用索引覆盖
# SELECT age, name FROM fgedudb.fgedu_users WHERE age > 25;
Part05-风哥经验总结与分享
5.1 常见优化问题
MySQL优化中常见的问题包括：
- 索引失效：查询条件不符合索引使用规则
- 全表扫描：查询没有使用索引
- 内存不足：参数设置不合理
- 磁盘I/O瓶颈：存储性能不足
- 连接数过多：应用程序连接管理不当
5.2 最佳实践建议
1. 合理设计数据库：遵循规范化原则，合理设计表结构和索引
2. 优化参数配置：根据服务器配置和业务需求调整MySQL参数
3. 使用合适的存储引擎：根据业务需求选择合适的存储引擎
4. 优化查询语句：避免全表扫描，使用索引，优化JOIN操作
5. 定期维护：定期分析和优化表，清理过期数据
6. 监控性能：使用监控工具监控MySQL性能，及时发现问题
7. 合理使用缓存：使用查询缓存和应用级缓存
8. 分区表：对大表使用分区，提高查询性能
9. 读写分离：使用主从复制实现读写分离
10. 垂直分库：将不同业务的数据分离到不同的数据库
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
