2. 首页 > MySQL教程 > 正文

MySQL教程FG087-MySQL性能瓶颈定位

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：7

本教程将详细介绍MySQL性能瓶颈的定位方法，包括性能指标监控、慢查询分析、执行计划分析等内容。风哥教程参考MySQL官方文档Optimization部分。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 MySQL性能瓶颈的定义

MySQL性能瓶颈是指在MySQL数据库运行过程中，由于各种原因导致系统性能下降的关键点。这些瓶颈可能来自硬件、系统配置、SQL语句、索引设计等多个方面。学习交流加群风哥微信: itpux-com 01 更多视频教程www.fgedu.net.cn

1.2 性能瓶颈的常见类型

常见的MySQL性能瓶颈包括：

  • CPU瓶颈：CPU使用率过高，导致处理能力不足
  • 内存瓶颈：内存不足，导致频繁的磁盘I/O
  • 磁盘I/O瓶颈：磁盘读写速度跟不上数据处理需求
  • 网络瓶颈：网络带宽不足，导致数据传输缓慢
  • SQL语句瓶颈：SQL语句执行效率低下
  • 索引瓶颈：索引设计不合理或索引失效
  • 锁瓶颈：锁竞争导致并发性能下降

1.3 性能瓶颈定位的基本步骤

性能瓶颈定位的基本步骤包括：

  1. 监控系统资源使用情况
  2. 分析MySQL状态变量
  3. 分析慢查询日志
  4. 使用EXPLAIN分析SQL执行计划
  5. 使用Performance Schema和SYS Schema
  6. 使用查询分析器
  7. 综合分析定位瓶颈

Part02-生产环境规划与建议

2.1 监控系统的部署

在生产环境中，建议部署专业的监控系统，如Prometheus、Grafana等，实时监控MySQL的各项性能指标。 02 学习交流加群风哥微信: itpux-com

2.2 慢查询日志的配置

合理配置慢查询日志，设置适当的阈值，以便及时发现和分析慢查询。

2.3 性能模式的启用

启用MySQL的Performance Schema和SYS Schema，以便更详细地监控和分析MySQL的性能。

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

3.1 性能监控系统的搭建

搭建基于Prometheus和Grafana的MySQL性能监控系统，实时监控MySQL的各项性能指标。

3.2 慢查询分析系统的部署

部署慢查询分析系统，定期分析慢查询日志，找出性能瓶颈。 03 学习交流加群风哥QQ113257174

3.3 性能优化工具的配置

配置MySQL的性能优化工具，如pt-query-digest、MySQLTuner等，辅助性能瓶颈的定位和优化。

Part04-生产案例与实战讲解

4.1 性能指标监控

监控MySQL的关键性能指标是定位性能瓶颈的第一步。通过监控这些指标，可以快速发现系统中的性能问题。

# 查看MySQL全局状态
mysql> SHOW GLOBAL STATUS;

# 查看关键性能指标
mysql> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read%’;
+—————————————+——–+
| Variable_name | Value |
+—————————————+——–+
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 123456 |
| Innodb_buffer_pool_reads | 789 |
+—————————————+——–+

# 查看连接数
mysql> SHOW GLOBAL STATUS LIKE ‘Threads%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 5 |
| Threads_connected | 10 |
| Threads_created | 15 |
| Threads_running | 2 |
+——————-+——-+

# 查看查询缓存状态
mysql> SHOW GLOBAL STATUS LIKE ‘Qcache%’;
+————————-+——–+
| Variable_name | Value |
+————————-+——–+
| Qcache_free_blocks | 10 |
| Qcache_free_memory | 1048576|
| Qcache_hits | 5000 |
| Qcache_inserts | 2000 |
| Qcache_lowmem_prunes | 500 |
| Qcache_not_cached | 1000 |
| Qcache_queries_in_cache | 800 |
| Qcache_total_blocks | 200 |
+————————-+——–+

4.2 慢查询分析

慢查询日志是定位性能瓶颈的重要工具，通过分析慢查询日志，可以找出执行时间长的SQL语句并进行优化。

# 启用慢查询日志
mysql> SET GLOBAL slow_query_log = ON;
mysql> SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;
mysql> SET GLOBAL long_query_time = 1; — 设置慢查询阈值为1秒

# 查看慢查询配置
mysql> SHOW VARIABLES LIKE ‘%slow%’;
+—————————–+——————————-+
| Variable_name | Value |
+—————————–+——————————-+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+—————————–+——————————-+

# 使用mysqldumpslow分析慢查询日志
$ mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 10 Time=5.23s (52.30s) Lock=0.00s (0.00s) Rows=1000.0 (10000), user[user]@host
SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’

Count: 5 Time=3.12s (15.60s) Lock=0.00s (0.00s) Rows=500.0 (2500), user[user]@host
SELECT * FROM fgedu_orders WHERE status = ‘pending’

4.3 EXPLAIN执行计划分析

EXPLAIN语句可以分析SQL语句的执行计划，帮助我们理解MySQL如何执行SQL语句，从而找出性能瓶颈。 04 风哥提示：

# 使用EXPLAIN分析查询
mysql> EXPLAIN SELECT * FROM fgedu_users WHERE name = ‘test’;
+—-+————-+——-+————+——+—————+———+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+———+———+——-+——+———-+——-+
| 1 | SIMPLE | users | NULL | ref | name_idx | name_idx| 257 | const | 1 | 100.00 | NULL |
+—-+————-+——-+————+——+—————+———+———+——-+——+———-+——-+

# 分析没有索引的查询
mysql> EXPLAIN SELECT * FROM fgedu_users WHERE age > 30;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 33.33 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+————-+

4.4 系统资源监控

MySQL的性能与系统资源密切相关，监控系统资源使用情况可以帮助我们发现资源瓶颈。 05更多学习教程公众号风哥教程itpux_com

# 监控CPU使用情况
$ top

top – 10:00:00 up 1 day, 2:34, 2 users, load average: 0.50, 0.60, 0.70
Tasks: 150 total, 1 running, 149 sleeping, 0 stopped, 0 zombie
%Cpu(s): 10.0 us, 5.0 sy, 0.0 ni, 80.0 id, 5.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8192000 total, 4096000 free, 2096000 used, 2000000 buff/cache
KiB Swap: 4096000 total, 4096000 free, 0 used. 5000000 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12345 mysql 20 0 2097152 524288 16384 S 20.0 6.4 1:30.00 mysqld

# 监控内存使用情况
$ free -h
total used free shared buff/cache available
Mem: 8.0G 2.0G 4.0G 100M 2.0G 5.0G
Swap: 4.0G 0B 4.0G

# 监控磁盘I/O
$ iostat -x 1
Linux 4.18.0-305.el8.x86_64 (fgedu.net.cn) 2026-04-01 _x86_64_ (4 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
10.00 0.00 5.00 5.00 0.00 80.00

device r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 10.0 20.0 0.10 0.20 20.00 0.10 3.33 2.00 4.00 1.00 3.00

4.5 MySQL状态变量分析

MySQL的状态变量记录了数据库的运行状态，通过分析这些变量，可以了解数据库的性能状况。

# 查看InnoDB缓冲池状态
mysql> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;
+—————————————+—————-+——-+
| Variable_name | Value |
+—————————————+—————-+
| Innodb_buffer_pool_dump_status | Dumping buffer |
| Innodb_buffer_pool_load_status | Loaded |
| Innodb_buffer_pool_pages_data | 10000 |
| Innodb_buffer_pool_pages_dirty | 100 |
| Innodb_buffer_pool_pages_free | 5000 |
| Innodb_buffer_pool_pages_misc | 1000 |
| Innodb_buffer_pool_pages_total | 16000 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 100000 |
| Innodb_buffer_pool_reads | 1000 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 50000 |
+—————————————+—————-+

# 计算缓冲池命中率
mysql> SELECT (1 – ( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests )) * 100 AS buffer_pool_hit_rate
FROM information_schema.global_status;
+———————+
| buffer_pool_hit_rate |
+———————+
| 99.00 |
+———————+

4.6 Performance Schema使用

Performance Schema是MySQL 5.5引入的性能监控工具，可以监控MySQL服务器的各种性能指标。

# 查看Performance Schema是否启用
mysql> SHOW VARIABLES LIKE ‘performance_schema’;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| performance_schema | ON |
+——————–+——-+

# 查看等待事件
mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
+————————-+————+——————-+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT |
+————————-+————+——————-+
| wait/io/file/innodb/innodb_data_file | 1000 | 1000000000000 |
| wait/io/file/innodb/innodb_log_file | 500 | 500000000000 |
| wait/io/file/sql/binlog | 200 | 200000000000 |
+————————-+————+——————-+

# 查看语句性能
mysql> SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
+————————-+————+——————-+
| DIGEST_TEXT | COUNT_STAR | SUM_TIMER_WAIT |
+————————-+————+——————-+
| SELECT * FROM fgedu_users WHERE name LIKE ? | 100 | 500000000000 |
| SELECT * FROM fgedu_orders WHERE status = ? | 50 | 250000000000 |
+————————-+————+——————-+

4.7 SYS Schema使用

SYS Schema是MySQL 5.7引入的系统视图集合，提供了更友好的性能监控接口。

# 查看慢查询
mysql> SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer_wait DESC
LIMIT 10;
+——————————————————————-+————+—————+————-+——————-+
| query | calls | exec_time | avg_exec_time | max_exec_time |
+——————————————————————-+————+—————+————-+——————-+
| SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’ | 10 | 50.00s | 5.00s | 8.00s |
| SELECT * FROM fgedu_orders WHERE status = ‘pending’ | 5 | 15.00s | 3.00s | 4.00s |
+——————————————————————-+————+—————+————-+——————-+

# 查看表的使用情况
mysql> SELECT * FROM sys.schema_table_statistics
ORDER BY rows_changed DESC
LIMIT 10;
+—————+————+——–+——–+—————-+—————-+——————+——————+
| table_schema | table_name | rows | rows_read | rows_changed | rows_changed_x | read_latency | write_latency |
+—————+————+——–+——–+—————-+—————-+——————+——————+
| fgedudb | fgedu_users | 1000 | 5000 | 2000 | 2000 | 1.00s | 2.00s |
| fgedudb | fgedu_orders | 500 | 3000 | 1000 | 1000 | 0.50s | 1.00s |
+—————+————+——–+——–+—————-+—————-+——————+——————+

4.8 查询性能分析

MySQL的查询分析器可以分析SQL语句的执行过程，帮助我们找出性能瓶颈。 06 from mysql视频:www.itpux.com

# 启用查询分析
mysql> SET profiling = 1;

# 执行查询
mysql> SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’;
+—-+——+—–+
| id | name | age |
+—-+——+—–+
| 1 | test | 25 |
+—-+——+—–+

# 查看分析结果
mysql> SHOW PROFILES;
+———-+————+————————————+
| Query_ID | Duration | Query |
+———-+————+————————————+
| 1 | 5.23000000 | SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’ |
+———-+————+————————————+

# 查看详细分析
mysql> SHOW PROFILE FOR QUERY 1;
+———————-+———-+———-+———-+———-+——————-+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary |
+———————-+———-+———-+———-+———-+——————-+
| starting | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 5.229940 | 5.229940 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
+———————-+———-+———-+———-+———-+——————-+

4.9 瓶颈定位方法

综合使用以上工具和方法，可以系统性地定位MySQL的性能瓶颈。

# 步骤1：监控系统资源使用情况
$ top
$ free -h
$ iostat -x 1

# 步骤2：查看MySQL状态变量
mysql> SHOW GLOBAL STATUS;

# 步骤3：分析慢查询日志
$ mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# 步骤4：使用EXPLAIN分析慢查询
mysql> EXPLAIN SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’;

# 步骤5：使用Performance Schema和SYS Schema
mysql> SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

# 步骤6：使用查询分析器
mysql> SET profiling = 1;
mysql> SELECT * FROM fgedu_users WHERE name LIKE ‘%test%’;
mysql> SHOW PROFILE FOR QUERY 1;

4.10 实际案例分析

通过一个实际案例，演示如何定位和解决MySQL性能瓶颈。

# 案例：用户反映系统响应缓慢

# 步骤1：监控系统资源
$ top

top – 10:00:00 up 1 day, 2:34, 2 users, load average: 2.50, 2.60, 2.70
Tasks: 150 total, 5 running, 145 sleeping, 0 stopped, 0 zombie
%Cpu(s): 80.0 us, 10.0 sy, 0.0 ni, 5.0 id, 5.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8192000 total, 512000 free, 6144000 used, 1536000 buff/cache
KiB Swap: 4096000 total, 3072000 free, 1024000 used. 512000 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12345 mysql 20 0 4194304 3145728 16384 S 80.0 38.4 5:30.00 mysqld

# 步骤2：查看MySQL状态
mysql> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;
+—————————————+—————-+——-+
| Variable_name | Value |
+—————————————+—————-+
| Innodb_buffer_pool_pages_data | 786432 |
| Innodb_buffer_pool_pages_dirty | 10000 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_total | 786432 |
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_reads | 50000 |
| Innodb_buffer_pool_wait_free | 1000 |
| Innodb_buffer_pool_write_requests | 500000 |
+—————————————+—————-+

# 步骤3：分析慢查询
$ mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 100 Time=10.23s (1023.00s) Lock=0.00s (0.00s) Rows=10000.0 (1000000), user[user]@host
SELECT * FROM fgedu_orders WHERE customer_id = ?

# 步骤4：使用EXPLAIN分析
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE customer_id = 123;
+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+
| 1 | SIMPLE | orders| NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 10.00 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+———+———-+————-+

# 步骤5：解决方案
# 1. 增加缓冲池大小
mysql> SET GLOBAL innodb_buffer_pool_size = 4G;

# 2. 创建索引
mysql> CREATE INDEX idx_customer_id ON fgedu_orders(customer_id);

# 3. 优化查询
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE customer_id = 123;
+—-+————-+——-+————+——+—————+—————+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+—————+———+——-+——+———-+——-+
| 1 | SIMPLE | orders| NULL | ref | idx_customer_id| idx_customer_id| 4 | const | 100 | 100.00 | NULL |
+—-+————-+——-+————+——+—————+—————+———+——-+——+———-+——-+

Part05-风哥经验总结与分享

5.1 性能瓶颈定位的关键要点

在定位MySQL性能瓶颈时，需要注意以下关键要点：

  • 从系统资源开始分析：CPU、内存、磁盘I/O是性能瓶颈的常见来源
  • 关注MySQL状态变量：特别是缓冲池、连接数、查询缓存等关键指标
  • 重视慢查询日志：慢查询是性能瓶颈的重要指示器
  • 善用EXPLAIN：分析SQL执行计划，找出索引使用问题
  • 利用Performance Schema和SYS Schema：获取更详细的性能数据
  • 综合分析：结合多个工具和指标，全面定位瓶颈

5.2 性能瓶颈的常见原因及解决方案

常见的性能瓶颈原因及解决方案包括：

CPU瓶颈：

  • 原因：复杂的SQL查询、大量的排序和聚合操作
  • 解决方案：优化SQL语句、增加索引、使用分区表
内存瓶颈：

  • 原因：缓冲池配置过小、内存泄漏
  • 解决方案：增加缓冲池大小、优化内存配置
磁盘I/O瓶颈：

  • 原因：大量的读写操作、磁盘速度慢
  • 解决方案：使用SSD、优化I/O配置、增加缓存
SQL语句瓶颈：

  • 原因：未使用索引、全表扫描、复杂的JOIN操作
  • 解决方案：优化SQL语句、创建合适的索引
索引瓶颈：

  • 原因：索引设计不合理、索引失效
  • 解决方案：重新设计索引、避免索引失效的操作

5.3 性能瓶颈定位的最佳实践

在实际工作中，定位MySQL性能瓶颈的最佳实践包括：

  • 建立完善的监控系统，实时监控MySQL的各项性能指标
  • 定期分析慢查询日志，及时发现和优化慢查询
  • 使用EXPLAIN分析SQL执行计划，确保SQL语句的高效执行
  • 合理配置MySQL参数，根据服务器资源和业务需求进行调优
  • 定期进行性能测试，了解系统的性能瓶颈和容量极限
  • 建立性能基准，以便在系统变更后进行对比分析
风哥提示：在实际应用中，性能瓶颈可能由多种因素引起，需要综合分析系统资源、MySQL状态、慢查询日志等信息，找出根本原因并采取相应的优化措施。学习交流加群风哥QQ113257174

通过本教程的学习，您应该能够掌握MySQL性能瓶颈的定位方法，及时发现和解决MySQL性能问题，提高数据库的性能和稳定性。更多学习教程公众号风哥教程itpux_com

from MySQL:www.itpux.com

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

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

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息