1. 优化层次

MySQL性能优化可以从以下层次进行：

+——————-+
| 应用层优化 | SQL语句、连接池、缓存
+——————-+
| 数据库层优化 | 表结构、索引、查询
+——————-+
| 实例层优化 | 参数配置、内存分配
+——————-+
| 操作系统层优化 | 内核参数、文件系统
+——————-+
| 硬件层优化 | CPU、内存、磁盘、网络
+——————-+

2. 性能指标

关键性能指标：
– 响应时间：查询执行时间
– 吞吐量：每秒查询数(QPS)、每秒事务数(TPS)
– 并发性：同时处理的连接数
– 资源利用率：CPU、内存、磁盘I/O使用率

查看性能指标：
mysql> SHOW STATUS LIKE ‘Questions’;
mysql> SHOW STATUS LIKE ‘Com_%’;
mysql> SHOW STATUS LIKE ‘Innodb_%’;

输出示例：
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Questions | 12345 |
| Com_select | 10000 |
| Com_insert | 1000 |
| Com_update | 500 |
| Com_delete | 100 |
+————————-+——-+

3. 性能瓶颈识别

常见性能瓶颈：
– CPU瓶颈：复杂查询、缺少索引
– 内存瓶颈：缓冲池不足、排序操作
– I/O瓶颈：磁盘读写慢、表扫描
– 锁瓶颈：锁等待、死锁
– 网络瓶颈：带宽不足、延迟高

识别瓶颈方法：
# 查看CPU使用
top -bn1 | head -5

输出示例：
top – 12:00:00 up 30 days, 2 users, load average: 2.50, 2.00, 1.50

# 查看I/O等待
iostat -x 1 5

输出示例：
avg-cpu: %user %nice %sys %iowait %idle
50.00 0.00 10.00 20.00 20.00

# 查看MySQL状态
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 137363456
Buffer pool size 8192
Free buffers 100
Database pages 8000
…

4. 优化目标设定

设定优化目标：
– 响应时间：95%查询<100ms - 吞吐量：QPS>10000
– 可用性：99.99%
– 资源使用：CPU<80%，内存<85% 建立基线： mysql> SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
‘Questions’,
‘Uptime’,
‘Threads_connected’,
‘Innodb_buffer_pool_read_requests’,
‘Innodb_buffer_pool_reads’
);

输出示例：
+———————————+—————-+
| VARIABLE_NAME | VARIABLE_VALUE |
+———————————+—————-+
| Questions | 12345678 |
| Uptime | 86400 |
| Threads_connected | 50 |
| Innodb_buffer_pool_read_requests| 12345678 |
| Innodb_buffer_pool_reads | 12345 |
+———————————+—————-+

1. 优化原则

原则1：先诊断后优化
– 收集性能数据
– 分析瓶颈原因
– 制定优化方案
– 验证优化效果

原则2：优化收益最大项
– 优先优化影响最大的问题
– 80%的性能问题来自20%的代码
– 关注高频查询

原则3：渐进式优化
– 一次只改一个参数
– 验证效果后再继续
– 记录每次变更

原则4：权衡利弊
– 优化可能带来副作用
– 考虑维护成本
– 保持系统稳定性

2. 优化方法论

方法1：自顶向下
应用层 -> 数据库层 -> 系统层 -> 硬件层

方法2：自底向上
硬件层 -> 系统层 -> 数据库层 -> 应用层

方法3：瓶颈驱动
识别瓶颈 -> 分析原因 -> 解决瓶颈 -> 验证效果

3. 优化流程

标准优化流程：
+——————-+
| 1. 建立性能基线 |
+——————-+
|
v
+——————-+
| 2. 监控性能指标 |
+——————-+
|
v
+——————-+
| 3. 识别性能瓶颈 |
+——————-+
|
v
+——————-+
| 4. 分析瓶颈原因 |
+——————-+
|
v
+——————-+
| 5. 制定优化方案 |
+——————-+
|
v
+——————-+
| 6. 实施优化方案 |
+——————-+
|
v
+——————-+
| 7. 验证优化效果 |
+——————-+
|
v
+——————-+
| 8. 持续监控优化 |
+——————-+

4. 优化优先级

优化优先级排序：
1. SQL优化（收益最大）
2. 索引优化
3. 表结构优化
4. 参数调优
5. 硬件升级

评估优化收益：
mysql> SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT/1000000000 AS TOTAL_SEC,
AVG_TIMER_WAIT/1000000 AS AVG_MS
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

输出示例：
+——————————–+————+————+——–+
| DIGEST_TEXT | COUNT_STAR | TOTAL_SEC | AVG_MS |
+——————————–+————+————+——–+
| SELECT * FROM `orders` WHERE…| 12345 | 123.45 | 10.00 |
| INSERT INTO `logs` VALUES… | 23456 | 98.76 | 4.21 |
+——————————–+————+————+——–+

1. EXPLAIN命令

使用EXPLAIN分析查询：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’\G

输出示例：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_status
key: idx_status
key_len: 102
ref: const
rows: 1000
filtered: 100.00
Extra: NULL

EXPLAIN输出解读：
– type：访问类型（ALL=全表扫描，index=索引扫描，ref=索引查找）
– key：使用的索引
– rows：预估扫描行数
– Extra：额外信息

2. Performance Schema

启用Performance Schema：
mysql> SHOW VARIABLES LIKE ‘performance_schema’;

输出示例：
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| performance_schema | ON |
+——————–+——-+

查看等待事件：
mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000 AS TOTAL_MS
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

输出示例：
+———————————————-+————+———-+
| EVENT_NAME | COUNT_STAR | TOTAL_MS |
+———————————————-+————+———-+
| wait/io/file/innodb/innodb_data_file | 12345 | 50000.0 |
| wait/io/file/innodb/innodb_log_file | 5678 | 30000.0 |
+———————————————-+————+———-+

3. Sys Schema

使用Sys Schema视图：
# 查看语句分析
mysql> SELECT * FROM sys.statement_analysis LIMIT 5\G

输出示例：
*************************** 1. row ***************************
query: SELECT * FROM `orders` WHERE `status` = ?
db: production_db
exec_count: 12345
total_latency: 00:02:03.45
avg_latency: 10.00 ms

# 查看表统计
mysql> SELECT * FROM sys.schema_table_statistics LIMIT 5;

输出示例：
+—————-+————-+————+—————-+
| table_schema | table_name | rows_fetched| rows_inserted |
+—————-+————-+————+—————-+
| production_db | orders | 1234567 | 123456 |
+—————-+————-+————+—————-+

4. 慢查询日志

配置慢查询日志：
mysql> SET GLOBAL slow_query_log = ON;
mysql> SET GLOBAL long_query_time = 2;

输出示例：
Query OK, 0 rows affected (0.00 sec)

分析慢查询：
mysqldumpslow -s t /var/log/mysql/slow.log | head -10

输出示例：
Count: 100 Time=5.00s (500s) Lock=0.00s (0s) Rows=1000.0
SELECT * FROM orders WHERE status = ‘S’

5. MySQL Enterprise Monitor

MySQL Enterprise Monitor功能：
– 实时监控
– 性能仪表板
– 查询分析器
– 告警通知
– 优化建议

6. 第三方工具

常用第三方工具：
– pt-query-digest：Percona查询分析工具
– pt-index-usage：索引使用分析
– MySQLTuner：参数调优建议
– Prometheus + Grafana：监控可视化

使用pt-query-digest：
pt-query-digest /var/log/mysql/slow.log

输出示例：
# 3600s user time, 100ms system time, 30.00M rss, 40.00M vsz
# Current date: 2026-04-01 12:00:00
# Hostname: mysql-server
# Files: /var/log/mysql/slow.log

# Overall: 10k total, 100 unique, 100 QPS, 0.50x concurrency
# Time range: 2026-04-01 00:00:00 to 2026-04-01 01:00:00

# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ============ ============== ==== ======= ===== =========
# 1 0xA1B2C3D4.. 200.0000 40.0% 100 2.0000 0.01 SELECT orders

1. 硬件配置建议

CPU配置：
– 选择高主频CPU
– 多核心支持并发
– 推荐配置：16-64核心

内存配置：
– 内存大小：数据量的20-50%
– 推荐配置：64-256GB
– 使用ECC内存

磁盘配置：
– 使用SSD存储
– RAID配置：RAID10
– IOPS：>10000

网络配置：
– 万兆网卡
– 低延迟网络
– 专用网络

2. 操作系统优化

内核参数优化：
vim /etc/sysctl.conf

添加配置：
# 网络优化
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_keepalive_time = 1200

# 内存优化
vm.swappiness = 1
vm.dirty_ratio = 15
vm.dirty_background_ratio = 5

# 文件描述符
fs.file-max = 655350

应用配置：
sysctl -p

输出示例：
net.core.somaxconn = 65535
…

文件描述符限制：
vim /etc/security/limits.conf

添加配置：
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

3. 文件系统优化

推荐文件系统：
– XFS：推荐用于MySQL
– ext4：备选方案

XFS挂载选项：
vim /etc/fstab

添加配置：
/dev/sda1 /var/lib/mysql xfs defaults,noatime,nodiratime 0 0

重新挂载：
mount -o remount /var/lib/mysql

输出示例：
Mount successful.

4. 磁盘调度优化

查看当前调度器：
cat /sys/block/sda/queue/scheduler

输出示例：
[noop] deadline cfq

设置调度器：
echo deadline > /sys/block/sda/queue/scheduler

永久设置：
vim /etc/default/grub

添加：
GRUB_CMDLINE_LINUX=”… elevator=deadline”

更新GRUB：
grub2-mkconfig -o /boot/grub2/grub.cfg

输出示例：
Generated grub configuration file.

1. 内存参数

InnoDB缓冲池：
# 推荐设置为物理内存的50-80%
innodb_buffer_pool_size = 4G

查看当前设置：
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

输出示例：
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 4294967296 |
+————————-+————+

缓冲池实例：
innodb_buffer_pool_instances = 4

排序缓冲区：
sort_buffer_size = 2M
join_buffer_size = 2M

连接缓冲区：
read_buffer_size = 1M
read_rnd_buffer_size = 1M

2. 连接参数

最大连接数：
max_connections = 500

查看当前设置：
mysql> SHOW VARIABLES LIKE ‘max_connections’;

输出示例：
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 500 |
+—————–+——-+

连接超时：
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800

线程缓存：
thread_cache_size = 100

3. InnoDB参数

日志文件：
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 2

刷新策略：
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

并发控制：
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8

查看InnoDB参数：
mysql> SHOW VARIABLES LIKE ‘innodb%’;

输出示例：
+——————————————+——-+
| Variable_name | Value |
+——————————————+——-+
| innodb_buffer_pool_size | 4G |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT|
+——————————————+——-+

4. 查询缓存

MySQL 8.0+已移除查询缓存，使用应用层缓存替代

5. 慢查询日志

启用慢查询日志：
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

查看配置：
mysql> SHOW VARIABLES LIKE ‘slow_query%’;

输出示例：
+———————+————————–+
| Variable_name | Value |
+———————+————————–+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow.log |
+———————+————————–+

6. 配置文件示例

完整配置示例：
vim /etc/my.cnf

[mysqld]
# 基本配置
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# 连接配置
max_connections = 500
connect_timeout = 10
wait_timeout = 28800
thread_cache_size = 100

# InnoDB配置
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 日志配置
log_error = /var/log/mysql/error.log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

1. 读写分离架构

架构图：
+——————-+
| 应用服务器 |
+——————-+
|
v
+——————-+
| 代理层(Mycat/ProxySQL)|
+——————-+
| |
v v
+———+ +———+
| 主库 | | 从库 |
| (写) | | (读) |
+———+ +———+

配置读写分离：
# 使用ProxySQL
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, ‘192.168.1.100’, 3306);

INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (20, ‘192.168.1.101’, 3306);

输出示例：
Query OK, 1 row affected (0.00 sec)

2. 分库分表架构

垂直分库：
按业务拆分数据库
– 用户库：user_db
– 订单库：order_db
– 商品库：product_db

水平分表：
按数据量拆分表
– orders_202601
– orders_202602
– orders_202603

分表规则：
# 按时间分表
CREATE TABLE orders_202601 LIKE orders;
CREATE TABLE orders_202602 LIKE orders;

输出示例：
Query OK, 0 rows affected (0.02 sec)

3. 缓存架构

多级缓存：
+——————-+
| 应用本地缓存 | (本地内存)
+——————-+
|
v
+——————-+
| 分布式缓存 | (Redis/Memcached)
+——————-+
|
v
+——————-+
| MySQL数据库 |
+——————-+

Redis缓存配置：
# 缓存热点数据
SET cache:key “value”
EXPIRE cache:key 3600

输出示例：
OK

4. 高可用架构

主从复制：
+——————-+ +——————-+
| 主库 | –> | 从库1 |
+——————-+ +——————-+
|
v
+——————-+
| 从库2 |
+——————-+

InnoDB Cluster：
+——————-+
| MySQL Router |
+——————-+
| |
v v
+———+ +———+
| Primary | | Secondary|
+———+ +———+
| |
v v
+———+ +———+
| Secondary| | Secondary|
+———+ +———+

创建InnoDB Cluster：
mysqlsh root@localhost:3306

MySQL> dba.createCluster(‘productionCluster’)

输出示例：
Cluster created successfully.

1. 收集系统信息

收集硬件信息：
# CPU信息
lscpu

输出示例：
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
CPU(s): 32
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 2
Model name: Intel(R) Xeon(R) CPU E5-2680 v4

# 内存信息
free -h

输出示例：
total used free
Mem: 64Gi 32Gi 32Gi
Swap: 8.0Gi 0B 8.0Gi

# 磁盘信息
df -h /var/lib/mysql

输出示例：
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 500G 200G 300G 40% /var/lib/mysql

2. 收集MySQL状态

收集全局状态：
mysql> SHOW GLOBAL STATUS;

输出示例：
+———————————–+————+
| Variable_name | Value |
+———————————–+————+
| Aborted_connects | 0 |
| Bytes_received | 12345678 |
| Bytes_sent | 87654321 |
| Connections | 1000 |
| Questions | 12345678 |
| Threads_connected | 50 |
| Uptime | 86400 |
+———————————–+————+

收集InnoDB状态：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 4294967296
Buffer pool size 262144
Free buffers 1024
Database pages 260000
…

3. 收集查询统计

收集查询统计：
mysql> SELECT DIGEST_TEXT, COUNT_STAR,
SUM_TIMER_WAIT/1000000000 AS TOTAL_SEC
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

输出示例：
+——————————–+————+————+
| DIGEST_TEXT | COUNT_STAR | TOTAL_SEC |
+——————————–+————+————+
| SELECT * FROM `orders` WHERE…| 12345 | 123.45 |
| INSERT INTO `logs` VALUES… | 23456 | 98.76 |
+——————————–+————+————+

4. 建立基线报告

创建基线脚本：
vim /opt/mysql/scripts/baseline_report.sh

脚本内容：
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
REPORT=”/var/log/mysql/baseline_$DATE.txt”

echo “=== MySQL Performance Baseline Report ===” > $REPORT
echo “Date: $(date)” >> $REPORT
echo “” >> $REPORT

echo “=== System Info ===” >> $REPORT
uptime >> $REPORT
free -h >> $REPORT
df -h /var/lib/mysql >> $REPORT

echo “” >> $REPORT
echo “=== MySQL Status ===” >> $REPORT
mysql -u root -p -e “SHOW GLOBAL STATUS” >> $REPORT

echo “” >> $REPORT
echo “=== MySQL Variables ===” >> $REPORT
mysql -u root -p -e “SHOW GLOBAL VARIABLES” >> $REPORT

执行脚本：
chmod +x /opt/mysql/scripts/baseline_report.sh
/opt/mysql/scripts/baseline_report.sh

输出示例：
Baseline report created: /var/log/mysql/baseline_20260401_120000.txt

1. 监控指标定义

关键监控指标：
+——————-+—————————+
| 指标类别 | 具体指标 |
+——————-+—————————+
| 连接指标 | Threads_connected |
| | Threads_running |
| | Aborted_connects |
+——————-+—————————+
| 查询指标 | Questions |
| | Slow_queries |
| | Com_select/insert/update |
+——————-+—————————+
| InnoDB指标 | Innodb_buffer_pool_reads |
| | Innodb_row_lock_waits |
| | Innodb_rows_read |
+——————-+—————————+

2. 监控脚本实现

创建监控脚本：
vim /opt/mysql/scripts/monitor.sh

脚本内容：
#!/bin/bash

MYSQL_USER=”monitor”
MYSQL_PASS=”********”
LOG_FILE=”/var/log/mysql/monitor.log”

# 获取当前时间
TIMESTAMP=$(date ‘+%Y-%m-%d %H:%M:%S’)

# 获取监控指标
mysql -u $MYSQL_USER -p$MYSQL_PASS -N -e ”
SELECT
‘$TIMESTAMP’ AS timestamp,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME=’Threads_connected’) AS connections,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME=’Questions’) AS queries,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME=’Slow_queries’) AS slow_queries,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME=’Innodb_buffer_pool_reads’) AS bp_reads;
” >> $LOG_FILE

设置定时任务：
crontab -e

添加内容：
* * * * * /opt/mysql/scripts/monitor.sh

输出示例：
crontab: installing new crontab

3. 告警配置

配置告警阈值：
vim /opt/mysql/scripts/check_alerts.sh

脚本内容：
#!/bin/bash

MYSQL_USER=”monitor”
MYSQL_PASS=”********”
ALERT_EMAIL=”dba@company.com”

# 检查连接数
CONN=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -N -e ”
SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME=’Threads_connected’
“)

MAX_CONN=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -N -e ”
SELECT VARIABLE_VALUE FROM performance_schema.global_variables
WHERE VARIABLE_NAME=’max_connections’
“)

RATIO=$(echo “scale=2; $CONN / $MAX_CONN * 100” | bc)

if (( $(echo “$RATIO > 80” | bc -l) )); then
echo “ALERT: Connection usage is $RATIO%” | mail -s “MySQL Alert” $ALERT_EMAIL
fi

输出示例：
Alert check completed.

4. 监控可视化

使用Grafana可视化：
1. 安装Prometheus MySQL Exporter
2. 配置Prometheus数据源
3. 导入MySQL Dashboard

安装MySQL Exporter：
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar -xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz

export DATA_SOURCE_NAME=’monitor:password@(localhost:3306)/’
./mysqld_exporter

输出示例：
ts=2026-04-01T12:00:00.000Z caller=mysqld_exporter.go:72 level=info msg=”Starting mysqld_exporter”

1. 慢查询分析

查找慢查询：
mysql> SELECT DIGEST_TEXT, COUNT_STAR,
AVG_TIMER_WAIT/1000000 AS AVG_MS,
SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 2000000
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

输出示例：
+——————————–+————+——–+——————+
| DIGEST_TEXT | COUNT_STAR | AVG_MS | SUM_ROWS_EXAMINED|
+——————————–+————+——–+——————+
| SELECT * FROM `orders` WHERE…| 12345 | 50.00 | 1234567890|
+——————————–+————+——–+——————+

分析执行计划：
mysql> EXPLAIN SELECT * FROM orders WHERE status = ‘pending’\G

输出示例：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ALL
possible_keys: idx_status
key: NULL
rows: 1000000
filtered: 10.00
Extra: Using where

2. 索引使用分析

查看索引使用情况：
mysql> SELECT * FROM sys.schema_index_statistics
WHERE table_schema = ‘production_db’;

输出示例：
+—————-+————-+————+—————-+
| table_schema | table_name | index_name | rows_selected |
+—————-+————-+————+—————-+
| production_db | orders | PRIMARY | 1234567 |
| production_db | orders | idx_status | 234567 |
+—————-+————-+————+—————-+

查看未使用的索引：
mysql> SELECT * FROM sys.schema_unused_indexes;

输出示例：
+—————-+————-+—————-+
| object_schema | object_name | index_name |
+—————-+————-+—————-+
| production_db | orders | idx_old_status |
+—————-+————-+—————-+

3. 锁等待分析

查看锁等待：
mysql> SELECT * FROM sys.innodb_lock_waits\G

输出示例：
*************************** 1. row ***************************
wait_started: 2026-04-01 12:00:00
wait_age: 00:00:05
locked_table: `production_db`.`orders`
locked_index: PRIMARY
waiting_pid: 10
waiting_query: UPDATE orders SET status = ‘processing’
waiting_lock_mode: X
blocking_pid: 5
blocking_query: UPDATE orders SET status = ‘pending’

4. 内存使用分析

查看内存使用：
mysql> SELECT EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS USED_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

输出示例：
+——————————————-+———-+
| EVENT_NAME | USED_MB |
+——————————————-+———-+
| memory/innodb/buf_buf_pool | 1024.5 |
| memory/innodb/log_buffer_memory | 64.0 |
+——————————————-+———-+

# 案例：订单查询慢

# 问题描述：
# 订单列表查询响应时间超过5秒

# 步骤1：分析慢查询
mysql> SELECT DIGEST_TEXT, AVG_TIMER_WAIT/1000000 AS AVG_MS
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE ‘%orders%status%’;

# 输出示例：
# +——————————–+——–+
# | DIGEST_TEXT | AVG_MS |
# +——————————–+——–+
# | SELECT * FROM `orders` WHERE…| 5000.00|
# +——————————–+——–+

# 步骤2：查看执行计划
mysql> EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ AND created_at > ‘2026-01-01’\G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: orders
# type: ALL
# possible_keys: idx_status
# key: NULL
# rows: 1000000
# filtered: 10.00
# Extra: Using where

# 问题分析：
# – type=ALL表示全表扫描
# – key=NULL表示未使用索引
# – 扫描100万行

# 步骤3：创建复合索引
mysql> CREATE INDEX idx_status_created ON orders(status, created_at);

# 输出示例：
# Query OK, 0 rows affected (12.34 sec)

# 步骤4：验证优化效果
mysql> EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ AND created_at > ‘2026-01-01’\G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: orders
# type: range
# possible_keys: idx_status_created
# key: idx_status_created
# rows: 10000
# filtered: 100.00
# Extra: Using index condition

# 步骤5：确认性能提升
mysql> SELECT DIGEST_TEXT, AVG_TIMER_WAIT/1000000 AS AVG_MS
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE ‘%orders%status%’;

# 输出示例：
# +——————————–+——–+
# | DIGEST_TEXT | AVG_MS |
# +——————————–+——–+
# | SELECT * FROM `orders` WHERE…| 50.00|
# +——————————–+——–+

# 性能提升：从5000ms降到50ms，提升100倍

# 案例：缓冲池命中率低

# 问题描述：
# 数据库性能下降，缓冲池命中率只有85%

# 步骤1：检查缓冲池状态
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool%’;

# 输出示例：
# +—————————————+————-+
# | Variable_name | Value |
# +—————————————+————-+
# | Innodb_buffer_pool_pages_total | 65536 |
# | Innodb_buffer_pool_pages_data | 65000 |
# | Innodb_buffer_pool_pages_free | 536 |
# | Innodb_buffer_pool_read_requests | 10000000 |
# | Innodb_buffer_pool_reads | 1500000 |
# +—————————————+————-+

# 步骤2：计算命中率
# 命中率 = 1 – (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
# 命中率 = 1 – (1500000 / 10000000) = 85%

# 步骤3：检查当前缓冲池大小
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

# 输出示例：
# +————————-+————+
# | Variable_name | Value |
# +————————-+————+
# | innodb_buffer_pool_size | 1073741824 |
# +————————-+————+
# 当前大小：1GB

# 步骤4：增加缓冲池大小
mysql> SET GLOBAL innodb_buffer_pool_size = 4294967296;

# 输出示例：
# Query OK, 0 rows affected (0.00 sec)

# 步骤5：验证优化效果
mysql> SHOW STATUS LIKE ‘Innodb_buffer_pool%’;

# 输出示例：
# +—————————————+————-+
# | Variable_name | Value |
# +—————————————+————-+
# | Innodb_buffer_pool_read_requests | 20000000 |
# | Innodb_buffer_pool_reads | 100000 |
# +—————————————+————-+

# 新命中率 = 1 – (100000 / 20000000) = 99.5%

# 性能提升：命中率从85%提升到99.5%

# 案例：磁盘I/O瓶颈

# 问题描述：
# 磁盘I/O等待时间过高，影响数据库性能

# 步骤1：检查I/O状态
iostat -x 1 5

# 输出示例：
# avg-cpu: %user %nice %sys %iowait %idle
# 30.00 0.00 10.00 50.00 10.00
#
# Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s
# sda 0.00 10.00 100.00 200.00 5.00 10.00

# 问题分析：
# – iowait达到50%，存在I/O瓶颈
# – 磁盘读写频繁

# 步骤2：检查MySQL I/O配置
mysql> SHOW VARIABLES LIKE ‘innodb_io%’;

# 输出示例：
# +————————+——-+
# | Variable_name | Value |
# +————————+——-+
# | innodb_io_capacity | 200 |
# | innodb_io_capacity_max | 2000 |
# +————————+——-+

# 步骤3：调整I/O参数
mysql> SET GLOBAL innodb_io_capacity = 2000;
mysql> SET GLOBAL innodb_io_capacity_max = 4000;

# 输出示例：
# Query OK, 0 rows affected (0.00 sec)

# 步骤4：调整刷新策略
mysql> SHOW VARIABLES LIKE ‘innodb_flush%’;

# 输出示例：
# +——————————–+——-+
# | Variable_name | Value |
# +——————————–+——-+
# | innodb_flush_log_at_trx_commit | 1 |
# | innodb_flush_method | fdatasync|
# +——————————–+——-+

# 优化刷新方法
mysql> SET GLOBAL innodb_flush_method = ‘O_DIRECT’;

# 输出示例：
# Query OK, 0 rows affected (0.00 sec)

# 步骤5：验证优化效果
iostat -x 1 5

# 输出示例：
# avg-cpu: %user %nice %sys %iowait %idle
# 50.00 0.00 15.00 10.00 25.00

# 性能提升：iowait从50%降到10%

1. SQL优化最佳实践

– 使用索引覆盖查询
– 避免SELECT *
– 使用LIMIT限制结果集
– 避免在WHERE子句中使用函数
– 使用EXPLAIN分析查询

2. 索引优化最佳实践

– 为高频查询创建索引
– 使用复合索引优化多列查询
– 避免过多索引
– 定期分析索引使用情况
– 删除未使用的索引

3. 表结构优化最佳实践

– 选择合适的数据类型
– 适当进行反范式化
– 分区大表
– 定期优化表

4. 参数优化最佳实践

– 根据硬件配置参数
– 一次只改一个参数
– 记录参数变更
– 验证参数效果

5. 架构优化最佳实践

– 读写分离
– 分库分表
– 使用缓存
– 高可用部署

1. 硬件检查

[ ] CPU使用率是否合理（<80%） [ ] 内存是否充足（>15%空闲）
[ ] 磁盘I/O是否正常（iowait<20%） [ ] 网络带宽是否充足 2. 系统检查 [ ] 操作系统参数是否优化 [ ] 文件系统是否合理 [ ] 磁盘调度器是否正确 3. MySQL检查 [ ] 版本是否最新 [ ] 参数配置是否合理 [ ] 慢查询日志是否启用 [ ] 错误日志是否正常 4. 查询检查 [ ] 是否有慢查询 [ ] 索引是否合理 [ ] 执行计划是否正常 [ ] 是否有锁等待 5. 监控检查 [ ] 监控系统是否部署 [ ] 告警是否配置 [ ] 基线是否建立 [ ] 报表是否生成

1. 过度优化

问题：过度优化可能导致复杂性增加
解决：只优化真正的瓶颈

2. 忽视监控

问题：没有监控就无法发现问题
解决：建立完善的监控体系

3. 盲目调参

问题：不了解参数含义就修改
解决：理解参数作用后再调整

4. 忽视SQL优化

问题：只关注硬件和参数
解决：优先优化SQL语句

5. 缺乏测试

问题：生产环境直接应用优化
解决：先在测试环境验证

6. 没有备份

问题：优化前没有备份
解决：优化前必须备份

7. 忽视副作用

问题：优化带来新的问题
解决：评估优化的全面影响