MySQL教程FG189-MySQL服务器性能管理

内容简介：MySQL服务器性能管理是数据库运维的重要组成部分，对于保障业务的高可用性和用户体验至关重要。本文风哥教程参考MySQL官方文档Performance
Tuning部分，详细介绍MySQL服务器性能管理的各种策略、工具和最佳实践，包括性能监控、查询优化、索引优化、配置优化和硬件优化等内容，帮助读者建立完善的MySQL性能管理体系。学习交流加群风哥微信:
itpux-com

Part01-基础概念与理论知识

1.1 MySQL性能概述

MySQL性能管理是指通过监控、分析和优化，确保MySQL数据库在满足业务需求的同时，能够高效地利用系统资源。性能管理的主要目标包括：

提高响应速度：减少查询执行时间，提高用户体验
增加吞吐量：提高系统处理并发请求的能力
优化资源利用：合理利用CPU、内存、磁盘和网络资源
降低成本：减少硬件和运维成本
保障稳定性：确保系统在高负载下的稳定性

1.2 性能指标

MySQL性能指标是衡量数据库性能的重要依据，主要包括：

查询性能指标：
- QPS（每秒查询数）：每秒处理的查询数量
- TPS（每秒事务数）：每秒处理的事务数量
- 响应时间：查询执行的平均时间
- 慢查询数：执行时间超过阈值的查询数量
资源使用指标：
- CPU使用率：CPU资源的使用情况
- 内存使用率：内存资源的使用情况
- 磁盘I/O：磁盘读写的频率和延迟
- 网络流量：网络传输的数据量
连接指标：
- 连接数：当前连接到数据库的客户端数量
- 连接命中率：连接池的命中率
- 连接等待时间：客户端等待连接的时间
缓存指标：
- 缓冲池命中率：InnoDB缓冲池的命中率
- 查询缓存命中率：查询缓存的命中率（MySQL 8.0已移除）
- 键缓存命中率：MyISAM键缓存的命中率
存储指标：
- 表空间大小：数据库占用的磁盘空间
- 索引大小：索引占用的磁盘空间
- 碎片率：数据和索引的碎片程度

1.3 性能瓶颈

MySQL性能瓶颈是指限制数据库性能的因素，主要包括：

CPU瓶颈：CPU资源不足，无法处理大量的查询请求
内存瓶颈：内存不足，导致频繁的磁盘I/O
磁盘I/O瓶颈：磁盘读写速度慢，无法满足数据库的I/O需求
网络瓶颈：网络带宽不足，导致数据传输延迟
锁竞争：并发事务之间的锁竞争，导致事务等待
查询效率低：SQL查询编写不当，执行效率低下
索引设计不合理：索引缺失或设计不当，导致全表扫描
配置不合理：MySQL配置参数设置不当，无法充分利用系统资源

性能瓶颈识别建议：通过监控性能指标，识别系统的性能瓶颈，然后针对性地进行优化。常用的性能瓶颈识别方法包括性能监控、慢查询分析、执行计划分析等。

Part02-生产环境规划与建议

2.1 性能策略规划

在生产环境中，MySQL性能策略的规划需要考虑以下因素：

业务需求：了解业务对性能的要求，如响应时间、并发量等
系统架构：根据系统架构，确定性能优化的重点
数据规模：根据数据规模，选择合适的存储引擎和索引策略
硬件资源：根据硬件资源，合理配置MySQL参数
应用特点：根据应用的读写比例、查询类型等特点，优化数据库设计
扩展性：考虑系统的扩展性，确保在业务增长时能够满足性能需求

2.2 性能监控体系

建立完善的性能监控体系是性能管理的基础，包括：

监控工具：选择合适的性能监控工具，如MySQL Enterprise Monitor、Zabbix、Prometheus等
监控指标：确定需要监控的性能指标，如QPS、TPS、响应时间、资源使用率等
监控频率：根据指标的重要性，确定监控的频率
告警机制：设置合理的告警阈值，及时发现性能问题
历史数据：存储历史监控数据，便于趋势分析和容量规划

2.3 性能优化方法

MySQL性能优化的主要方法包括：

查询优化：优化SQL查询，提高查询执行效率
索引优化：合理设计和使用索引，减少数据扫描
配置优化：调整MySQL配置参数，优化系统性能
硬件优化：升级硬件设备，提高系统性能
架构优化：优化数据库架构，如读写分离、分库分表等
应用优化：优化应用程序，减少数据库压力

风哥提示：性能策略的规划需要与业务需求相匹配，确保在保障性能的同时，不影响业务的正常运行。同时，性能策略需要定期更新和优化，以适应业务需求和系统环境的变化。

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

3.1 性能监控工具

性能监控工具用于收集和分析MySQL的性能指标，帮助识别性能瓶颈。

3.1.1 使用SHOW STATUS和SHOW VARIABLES

# 计算QPS（每秒查询数）
mysql> SELECT
VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME =
‘Uptime’) AS QPS
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = ‘Queries’;
+———+——————-+
| QPS | VARIABLE_SOURCE |
+———+——————-+
| 0.9259 | global |
+———+——————-+

# 计算缓冲池命中率
mysql> SELECT
ROUND((1 – (VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE
VARIABLE_NAME = ‘Innodb_buffer_pool_read_requests’))) * 100, 2) AS BUFFER_POOL_HIT_RATE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’;
+———————+——————-+
| BUFFER_POOL_HIT_RATE | VARIABLE_SOURCE |
+———————+——————-+
| 99.90 | global |
+———————+——————-+

3.1.2 使用Performance Schema

# 启用Performance Schema
mysql> SET GLOBAL performance_schema = ON;
Query OK, 0 rows affected (0.00 sec)

# 查询热点表
mysql> SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM
performance_schema.table_io_waits_summary_by_table
WHERE
OBJECT_SCHEMA = ‘fgedudb’
ORDER BY
(COUNT_FETCH + COUNT_INSERT + COUNT_UPDATE + COUNT_DELETE) DESC
LIMIT 10;

# 输出示例
+—————+——————+————-+————–+————–+————–+
| OBJECT_SCHEMA | OBJECT_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+—————+——————+————-+————–+————–+————–+
| fgedudb | fgedu_orders | 20000 | 5000 | 3000 | 500 |
| fgedudb | fgedu_users | 15000 | 2000 | 1000 | 100 |
| fgedudb | fgedu_products | 10000 | 3000 | 2000 | 300 |
| fgedudb | fgedu_order_items | 10000 | 5000 | 1000 | 200 |
+—————+——————+————-+————–+————–+————–+

# 查询热点索引
mysql> SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
OBJECT_SCHEMA = ‘fgedudb’
AND INDEX_NAME IS NOT NULL
ORDER BY
(COUNT_FETCH + COUNT_INSERT + COUNT_UPDATE + COUNT_DELETE) DESC
LIMIT 10;

# 输出示例
+—————+——————+————————+————-+————–+————–+————–+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+—————+——————+————————+————-+————–+————–+————–+
| fgedudb | fgedu_orders | PRIMARY | 15000 | 5000 | 3000 | 500 |
| fgedudb | fgedu_orders | fk_order_user_id | 10000 | 0 | 0 | 0 |
| fgedudb | fgedu_users | PRIMARY | 12000 | 2000 | 1000 | 100 |
| fgedudb | fgedu_users | idx_email | 5000 | 0 | 0 | 0 |
| fgedudb | fgedu_products | PRIMARY | 8000 | 3000 | 2000 | 300 |
+—————+——————+————————+————-+————–+————–+————–+

# 查询慢查询
mysql> SELECT
THREAD_ID,
EVENT_ID,
TIMER_START,
TIMER_WAIT,
SQL_TEXT
FROM
performance_schema.events_statements_history_long
WHERE
TIMER_WAIT > 1000000000000 # 超过1秒的查询
ORDER BY
TIMER_WAIT DESC
LIMIT 10;

# 输出示例
+———–+———-+—————-+—————-+———————————————+
| THREAD_ID | EVENT_ID | TIMER_START | TIMER_WAIT | SQL_TEXT |
+———–+———-+—————-+—————-+———————————————+
| 123 | 456 | 100000000000000 | 5000000000000 | SELECT * FROM fgedu_orders ORDER BY RAND() LIMIT 10 |
| 124 | 789 | 100000000000001 | 3000000000000 | SELECT * FROM fgedu_orders WHERE user_id = 1 |
+———–+———-+—————-+—————-+———————————————+

3.1.3 使用sys Schema

# 查询慢查询
mysql> SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
+——————————————————————+———–+—————+————-+————–+———–+—————+—————+
| query | db | full_scan | exec_count | err_count | warn_count | total_latency | avg_latency |
+——————————————————————+———–+—————+————-+————–+———–+—————+—————+
| SELECT * FROM fgedu_orders ORDER BY RAND() LIMIT ? | fgedudb | Yes | 100 | 0 | 0 | 500.00 s | 5.00 s |
| SELECT * FROM fgedu_orders WHERE user_id = ? | fgedudb | Yes | 500 | 0 | 0 | 150.00 s | 300.00 ms |
| SELECT * FROM fgedu_products WHERE category_id = ? AND price > ? | fgedudb | Yes | 200 | 0 | 0 | 80.00 s |
400.00 ms |
+——————————————————————+———–+—————+————-+————–+———–+—————+—————+

# 查询索引使用情况
mysql> SELECT * FROM sys.schema_index_statistics WHERE object_schema = ‘fgedudb’ ORDER BY rows_selected DESC
LIMIT 10;
+—————+——————+————————+—————+—————+—————+
| object_schema | object_name | index_name | rows_selected | rows_inserted | rows_updated |
+—————+——————+————————+—————+—————+—————+
| fgedudb | fgedu_orders | PRIMARY | 15000 | 5000 | 3000 |
| fgedudb | fgedu_orders | fk_order_user_id | 10000 | 0 | 0 |
| fgedudb | fgedu_users | PRIMARY | 12000 | 2000 | 1000 |
| fgedudb | fgedu_users | idx_email | 5000 | 0 | 0 |
+—————+——————+————————+—————+—————+—————+

3.1.4 使用第三方监控工具

# 使用pt-query-digest分析慢查询日志
$ pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_query_analysis.txt

# 查看分析结果
$ head -n 100 /tmp/slow_query_analysis.txt

# 输出示例
# 260ms user time, 10ms system time, 24.00M rss, 214.74M vsz
# Current date: Sat Apr 8 22:05:00 2026
# Hostname: mysql-server
# Files: /var/lib/mysql/slow.log
# Overall: 100 total, 5 unique, 0.01 QPS, 0.05x concurrency ________
# Time range: 2026-04-08T21:00:00 to 2026-04-08T22:00:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 500.00s 1.00s 10.00s 5.00s 8.00s 2.00s 5.00s
# Lock time 0.00s 0.00s 0.00s 0.00s 0.00s 0.00s 0.00s
# Rows sent 1.00k 10.0 10.0 10.0 10.0 0.0 10.0
# Rows examine 100.00M 1000.0k 5000.0k 1000.0k 2000.0k 500.0k 1000.0k
# Query size 15.00B 10.0 10.0 10.0 10.0 0.0 10.0

# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ==========
# 1 0x1234567890ABCDEF 500.00s 100.0% 100 5.00s 0.00 SELECT fgedu_orders ORDER BY RAND()
# 2 0x234567890ABCDEF1 150.00s 30.0% 500 300.00ms 0.00 SELECT fgedu_orders WHERE user_id = ?
# 3 0x34567890ABCDEF2 80.00s 16.0% 200 400.00ms 0.00 SELECT fgedu_products WHERE category_id = ? AND price >
?

# 使用MySQL Workbench进行性能监控
# 1. 打开MySQL Workbench
# 2. 连接到MySQL服务器
# 3. 点击”Performance”
# 4. 查看性能仪表盘、查询统计、索引使用等信息

# 使用Prometheus + Grafana进行性能监控
# 1. 安装Prometheus和mysqld_exporter
# 2. 配置Prometheus收集MySQL指标
# 3. 安装Grafana并导入MySQL监控仪表盘（ID: 7362）
# 4. 查看性能指标和趋势图表

3.2 查询优化

查询优化是提高MySQL性能的重要手段，包括优化SQL语句和执行计划。

3.2.1 使用EXPLAIN分析执行计划

# 分析带索引提示的查询执行计划
mysql> EXPLAIN SELECT /*+ INDEX(fgedu_orders idx_order_user_id) */ * FROM fgedu_orders WHERE user_id = 1 AND
status = ‘paid’;
+—-+————-+————+————+——+—————+————————+———+——-+——-+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
Extra |
+—-+————-+————+————+——+—————+————————+———+——-+——-+———-+——-+
| 1 | SIMPLE | fgedu_orders| NULL | ref | fk_order_user_id,idx_order_status | fk_order_user_id | 4 | const |
200 | 10.00 | Using where |
+—-+————-+————+————+——+—————+————————+———+——-+——-+———-+——-+

# 分析带JOIN的查询执行计划
mysql> EXPLAIN SELECT o.*, u.name FROM fgedu_orders o JOIN fgedu_users u ON o.user_id = u.id WHERE o.status
= ‘paid’ ORDER BY o.created_at DESC LIMIT 10;
+—-+————-+——-+————+——–+——————————–+————————+———+—————+——–+———-+————————————+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
Extra |
+—-+————-+——-+————+——–+——————————–+————————+———+—————+——–+———-+————————————+
| 1 | SIMPLE | o | NULL | ref | fk_order_user_id,idx_order_status,idx_order_created_at | idx_order_status |
4 | const | 1000 | 100.00 | Using index condition; Using filesort |
| 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | fgedudb.o.user_id | 1 | 100.00 | NULL |
+—-+————-+——-+————+——–+——————————–+————————+———+—————+——–+———-+————————————+

3.2.2 优化SQL查询

# 1. 避免SELECT *
# 优化前
SELECT * FROM fgedu_orders WHERE user_id = 1;

# 优化后
SELECT id, order_no, total_amount, status, created_at FROM fgedu_orders WHERE user_id = 1;

# 2. 使用LIMIT限制结果集
# 优化前
SELECT * FROM fgedu_orders WHERE status = ‘paid’;

# 优化后
SELECT * FROM fgedu_orders WHERE status = ‘paid’ LIMIT 100;

# 3. 避免在WHERE子句中使用函数
# 优化前
SELECT * FROM fgedu_orders WHERE DATE(created_at) = ‘2026-04-08’;

# 优化后
SELECT * FROM fgedu_orders WHERE created_at >= ‘2026-04-08 00:00:00’ AND created_at < '2026-04-09 00:00:00' ; # 4. 避免在WHERE子句中使用 !=或 NOT IN # 优化前 SELECT * FROM fgedu_orders WHERE status !='cancelled' ; # 优化后 SELECT * FROM fgedu_orders WHERE status IN ('paid', 'shipped' , 'delivered' ); # 5. 避免使用ORDER BY RAND() # 优化前 SELECT * FROM fgedu_orders ORDER BY RAND() LIMIT 10; # 优化后 SELECT * FROM fgedu_orders WHERE id>=
(SELECT FLOOR(MAX(id) * RAND()) FROM fgedu_orders) LIMIT 10;

# 6. 使用JOIN代替子查询
# 优化前
SELECT * FROM fgedu_orders WHERE user_id IN (SELECT id FROM fgedu_users WHERE status = ‘active’);

# 优化后
SELECT o.* FROM fgedu_orders o JOIN fgedu_users u ON o.user_id = u.id WHERE u.status = ‘active’;

# 7. 使用UNION ALL代替UNION
# 优化前
SELECT * FROM fgedu_orders WHERE status = ‘paid’ UNION SELECT * FROM fgedu_orders WHERE status =
‘shipped’;

# 优化后
SELECT * FROM fgedu_orders WHERE status = ‘paid’ UNION ALL SELECT * FROM fgedu_orders WHERE status =
‘shipped’;

# 8. 使用Prepared Statements
# 优化前
SELECT * FROM fgedu_orders WHERE user_id = 1 AND status = ‘paid’;
SELECT * FROM fgedu_orders WHERE user_id = 2 AND status = ‘paid’;
SELECT * FROM fgedu_orders WHERE user_id = 3 AND status = ‘paid’;

# 优化后
PREPARE stmt FROM ‘SELECT * FROM fgedu_orders WHERE user_id = ? AND status = ?’;
SET @user_id = 1, @status = ‘paid’;
EXECUTE stmt USING @user_id, @status;
SET @user_id = 2, @status = ‘paid’;
EXECUTE stmt USING @user_id, @status;
SET @user_id = 3, @status = ‘paid’;
EXECUTE stmt USING @user_id, @status;
DEALLOCATE PREPARE stmt;

3.3 索引优化

索引优化是提高MySQL查询性能的重要手段，包括创建合适的索引和优化现有索引。

3.3.1 创建合适的索引

# 1. 创建主键索引
ALTER TABLE fgedu_orders ADD PRIMARY KEY (id);

# 2. 创建唯一索引
ALTER TABLE fgedu_users ADD UNIQUE INDEX idx_email (email);

# 3. 创建普通索引
ALTER TABLE fgedu_orders ADD INDEX idx_order_user_id (user_id);

# 4. 创建复合索引
ALTER TABLE fgedu_orders ADD INDEX idx_order_user_status (user_id, status);

# 5. 创建前缀索引
ALTER TABLE fgedu_products ADD INDEX idx_product_name (name(100));

# 6. 创建全文索引
ALTER TABLE fgedu_articles ADD FULLTEXT INDEX idx_article_content (content);

# 7. 创建空间索引
ALTER TABLE fgedu_locations ADD SPATIAL INDEX idx_location_point (point);

3.3.2 优化现有索引

# 1. 删除未使用的索引
ALTER TABLE fgedu_orders DROP INDEX idx_status;

# 2. 合并重复的索引
# 例如：如果有idx_user_id和idx_user_id_status，可以保留idx_user_id_status
ALTER TABLE fgedu_orders DROP INDEX idx_user_id;

# 3. 优化索引顺序
# 复合索引中，选择性高的列应该放在前面
ALTER TABLE fgedu_orders DROP INDEX idx_user_status;
ALTER TABLE fgedu_orders ADD INDEX idx_status_user (status, user_id);

# 4. 重建索引
# 修复索引碎片
ALTER TABLE fgedu_orders REBUILD INDEX PRIMARY;

# 或者重建表
ALTER TABLE fgedu_orders ENGINE=InnoDB;

# 6. 查看索引使用情况
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COUNT_STAR AS access_count
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
TABLE_SCHEMA = ‘fgedudb’
AND INDEX_NAME IS NOT NULL
ORDER BY
access_count DESC;

3.4 配置优化

配置优化是提高MySQL性能的重要手段，包括调整MySQL的配置参数。

3.4.1 内存配置优化

# 1. InnoDB缓冲池大小
# 建议设置为服务器内存的50-80%
# vi /etc/my.cnf
innodb_buffer_pool_size = 4G

# 2. InnoDB缓冲池实例数
# 建议设置为CPU核心数或CPU核心数的一半
innodb_buffer_pool_instances = 4

# 3. InnoDB缓冲池块大小
# 建议使用默认值128M
innodb_buffer_pool_chunk_size = 128M

# 4. 键缓存大小（MyISAM）
# 建议设置为服务器内存的10-20%
key_buffer_size = 512M

# 5. 查询缓存大小（MySQL 5.7及以下）
# MySQL 8.0已移除查询缓存
query_cache_size = 0
query_cache_type = 0

# 6. 临时表大小
# 建议设置为服务器内存的10-20%
tmp_table_size = 256M
max_heap_table_size = 256M

# 7. 连接内存
# 每个连接使用的内存
read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size = 256K
join_buffer_size = 256K

# 8. 二进制日志缓存大小
binlog_cache_size = 32K
binlog_stmt_cache_size = 32K

3.4.2 InnoDB配置优化

# 1. InnoDB日志文件大小
# 建议设置为缓冲池大小的25%
innodb_log_file_size = 1G

# 2. InnoDB日志缓冲大小
innodb_log_buffer_size = 64M

# 3. InnoDB刷新策略
innodb_flush_log_at_trx_commit = 1 # 最安全，性能较低
# innodb_flush_log_at_trx_commit = 2 # 性能较好，安全性较低
# innodb_flush_log_at_trx_commit = 0 # 性能最高，安全性最低

# 4. InnoDB双写缓冲区
innodb_doublewrite = ON

# 5. InnoDB自适应哈希索引
innodb_adaptive_hash_index = ON

# 6. InnoDB文件格式
innodb_file_format = Barracuda

# 7. InnoDB文件格式检查
innodb_file_format_check = ON

# 8. InnoDB表空间
innodb_file_per_table = ON

# 9. InnoDB IO线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 10. InnoDB刷新线程数
innodb_purge_threads = 4

# 11. InnoDB自适应刷新
innodb_adaptive_flushing = ON

# 12. InnoDB统计信息
innodb_stats_on_metadata = OFF

3.4.3 连接配置优化

# 1. 最大连接数
max_connections = 1000

# 2. 连接超时时间
wait_timeout = 7200
interactive_timeout = 7200

# 3. 连接队列大小
back_log = 200

# 4. 最大错误连接数
max_connect_errors = 10000

# 5. 跳过DNS解析
skip_name_resolve = ON

3.4.4 查询优化器配置

# 1. 优化器开关
optimizer_switch =
‘index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on’

# 2. 优化器成本模型
optimizer_cost_model = ‘default’

# 3. 统计信息采样率
innodb_stats_persistent_sample_pages = 20

# 4. 索引统计信息更新频率
innodb_stats_auto_recalc = ON

3.5 硬件优化

硬件优化是提高MySQL性能的基础，包括CPU、内存、磁盘和网络的优化。

# 1. CPU优化
# 选择多核CPU，MySQL可以利用多核并行处理查询
# 查看CPU信息
$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 142
Model name: Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz
Stepping: 10
CPU MHz: 3200.000
CPU max MHz: 4600.0000
CPU min MHz: 800.0000
BogoMIPS: 6384.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 12288K
NUMA node0 CPU(s): 0-7

# 2. 内存优化
# 增加服务器内存，提高InnoDB缓冲池大小
# 查看内存信息
$ free -h
total used free shared buff/cache available
Mem: 16G 8G 2G 100M 6G 7G
Swap: 4G 0B 4G

# 3. 磁盘优化
# 使用SSD磁盘，提高I/O性能
# 查看磁盘信息
$ lsblk -o NAME,SIZE,TYPE,FSTYPE,MOUNTPOINT
NAME SIZE TYPE FSTYPE MOUNTPOINT
sda 100G disk
├─sda1 500M part xfs /boot
└─sda2 99.5G part xfs /
sdb 500G disk
└─sdb1 500G part xfs /mysql

# 查看磁盘I/O性能
$ iostat -x 1 3
Linux 3.10.0-1160.el7.x86_64 (mysql-server) 04/08/2026 _x86_64_ (8 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
5.00 0.00 2.00 1.00 0.00 92.00

Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 0.00 10.00 20.00 400.00 800.00 80.00 0.10 3.33 2.00 4.00 0.50 1.50

# 4. 网络优化
# 增加网络带宽，使用千兆或万兆网卡
# 查看网络接口信息
$ ifconfig eth0
eth0: flags=4163 mtu 1500
inet 192.168.1.10 netmask 255.255.255.0 broadcast 192.168.1.255
inet6 fe80::a00:27ff:fe9a:4b62 prefixlen 64 scopeid 0x20 ether 08:00:27:9a:4b:62 txqueuelen 1000 (Ethernet)
RX packets 100000 bytes 100000000 (95.3 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 50000 bytes 50000000 (47.6 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

# 查看网络连接数
$ netstat -ant | grep :3306 | wc -l
20

Part04-生产案例与实战讲解

4.1 慢查询优化实战

慢查询优化的实战案例。

案例：电商网站订单查询慢查询优化
问题描述：用户报告订单查询页面加载缓慢，需要优化查询性能
慢查询：SELECT * FROM fgedu_orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10

# 1. 分析慢查询
# 查看慢查询日志
$ grep -A 10 “# Query_time: 1.” /var/lib/mysql/slow.log | head -n 20
# Time: 2026-04-08T22:00:00.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 [192.168.1.100]
# Query_time: 1.580000 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 100000
use fgedudb;
SET timestamp=1750000000;
SELECT * FROM fgedu_orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;

# 3. 优化方案
# 方案1：创建复合索引，包含WHERE和ORDER BY的列
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_user_created (user_id, created_at DESC);
Query OK, 0 rows affected (1.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 执行优化后的查询
mysql> SELECT * FROM fgedu_orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
+—–+———+————+————–+——–+———————+
| id | user_id | order_no | total_amount | status | created_at |
+—–+———+————+————–+——–+———————+
| 100 | 1 | ORDER100000 | 299.00 | paid | 2026-04-08 22:00:00 |
| 99 | 1 | ORDER099999 | 199.00 | paid | 2026-04-08 21:00:00 |
| 98 | 1 | ORDER099998 | 399.00 | paid | 2026-04-08 20:00:00 |
| 97 | 1 | ORDER099997 | 99.00 | paid | 2026-04-08 19:00:00 |
| 96 | 1 | ORDER099996 | 499.00 | paid | 2026-04-08 18:00:00 |
| 95 | 1 | ORDER099995 | 149.00 | paid | 2026-04-08 17:00:00 |
| 94 | 1 | ORDER099994 | 249.00 | paid | 2026-04-08 16:00:00 |
| 93 | 1 | ORDER099993 | 349.00 | paid | 2026-04-08 15:00:00 |
| 92 | 1 | ORDER099992 | 49.00 | paid | 2026-04-08 14:00:00 |
| 91 | 1 | ORDER099991 | 449.00 | paid | 2026-04-08 13:00:00 |
+—–+———+————+————–+——–+———————+
10 rows in set (0.01 sec) # 执行时间从1.58秒减少到0.01秒

# 5. 监控优化效果
# 查看慢查询数
mysql> SHOW GLOBAL STATUS LIKE ‘Slow_queries’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Slow_queries | 20 |
+—————+——-+

4.2 索引优化实战

索引优化的实战案例。

案例：电商网站商品搜索索引优化
问题描述：用户报告商品搜索功能响应缓慢，需要优化搜索性能
搜索查询：SELECT * FROM fgedu_products WHERE category_id = 1 AND price > 100 AND price < 500 ORDER BY sales DESC LIMIT 20

# 4. 验证优化结果
# 分析优化后的执行计划
mysql> EXPLAIN SELECT * FROM fgedu_products WHERE category_id = 1 AND price > 100 AND price < 500 ORDER BY sales DESC LIMIT 20; +----+-------------+------------------+------------+-------+----------------------------------------+-----------------------------+---------+------+------+----------+----------------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+-------+----------------------------------------+-----------------------------+---------+------+------+----------+----------------+-------+ | 1 | SIMPLE | fgedu_products | NULL | range | idx_product_category,idx_product_price,idx_category_price_sales | idx_category_price_sales | 12 | NULL | 200 | 100.00 | NULL | +----+-------------+------------------+------------+-------+----------------------------------------+-----------------------------+---------+------+------+----------+----------------+-------+ # 执行优化后的查询 mysql> SELECT * FROM fgedu_products WHERE category_id = 1 AND price > 100 AND
price < 500 ORDER BY sales DESC LIMIT 20; +-----+-------------+----------------+--------+-------+----------------+---------------------+ | id | category_id | name | price | sales | description | created_at | +-----+-------------+----------------+--------+-------+----------------+---------------------+ | 100 | 1 | 商品100 | 399.00 | 10000 | 商品描述100 | 2026-04-08 22:00:00 | | 99 | 1 | 商品99 | 299.00 | 9000 | 商品描述99 | 2026-04-08 21:00:00 | | 98 | 1 | 商品98 | 499.00 | 8000 | 商品描述98 | 2026-04-08 20:00:00 | | 97 | 1 | 商品97 | 199.00 | 7000 | 商品描述97 | 2026-04-08 19:00:00 | | 96 | 1 | 商品96 | 349.00 | 6000 | 商品描述96 | 2026-04-08 18:00:00 | | 95 | 1 | 商品95 | 249.00 | 5000 | 商品描述95 | 2026-

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