2. 数据压缩：
– 开启表压缩：使用ROW_FORMAT=COMPRESSED
– 合理设置压缩级别：根据数据特点选择合适的压缩级别
– 监控压缩效果：使用SHOW TABLE STATUS查看Data_length和Compressed_data

3. 碎片整理：
– 定期优化表：使用OPTIMIZE TABLE命令
– 重建表：使用ALTER TABLE … ENGINE=InnoDB
– 监控碎片情况：使用SHOW TABLE STATUS查看Data_free

4. 存储引擎选择：
– 对于事务型应用：使用InnoDB
– 对于只读应用：可以考虑MyISAM
– 对于临时表：使用MEMORY

5. 表结构优化：
– 合理设计表结构：选择合适的数据类型
– 避免使用TEXT和BLOB：尽量使用VARCHAR
– 合理设置索引：避免过度索引

6. 分区表：
– 对于大表：使用分区表
– 选择合适的分区策略：根据业务需求选择RANGE、LIST或HASH分区
– 监控分区性能：定期检查分区使用情况

7. 存储设备选择：
– 使用SSD：提高IO性能
– 使用RAID：提高可靠性和性能
– 合理配置文件系统：使用ext4或XFS

8. 备份策略：
– 定期备份：使用mysqldump或xtrabackup
– 备份验证：定期验证备份的可用性
– 增量备份：减少备份时间和空间

2. SQL优化：
– 避免使用SELECT *：只选择需要的列
– 合理使用JOIN：避免过多的表连接
– 避免使用子查询：尽量使用JOIN代替子查询
– 避免使用OR：尽量使用IN代替OR
– 合理使用LIMIT：限制返回的数据量

3. 执行计划分析：
– 使用EXPLAIN分析执行计划
– 关注type、key、rows等字段
– 优化执行计划：根据执行计划调整SQL或索引

4. 缓存优化：
– 利用InnoDB缓冲池：设置合适的缓冲池大小
– 利用查询缓存：对于频繁执行的查询
– 利用应用层缓存：如Redis、Memcached

5. 并发控制：
– 合理设置事务隔离级别：根据业务需求选择合适的隔离级别
– 避免长事务：减少事务持有锁的时间
– 使用乐观锁：减少锁冲突

6. 分区表优化：
– 对于大表：使用分区表
– 选择合适的分区键：根据查询模式选择
– 避免跨分区查询：尽量在单个分区内查询

7. 统计信息：
– 定期更新统计信息：使用ANALYZE TABLE
– 监控统计信息：使用SHOW TABLE STATUS

8. 查询监控：
– 监控慢查询：开启慢查询日志
– 分析慢查询：使用pt-query-digest
– 优化慢查询：根据分析结果优化SQL

# 查看CPU核心数
nproc

# 查看磁盘空间
df -h

# 步骤2：修改MySQL配置文件
# 编辑my.cnf文件
vim /etc/my.cnf

# 配置InnoDB参数
[mysqld]
# 缓冲池大小（建议为内存的50-80%）
innodb_buffer_pool_size = 8G

# 缓冲池实例数（建议为CPU核心数）
innodb_buffer_pool_instances = 8

# 重做日志文件大小（建议为256MB-1GB）
innodb_log_file_size = 1G

# 重做日志文件数量
innodb_log_files_in_group = 2

# 日志刷新策略（1：最安全，0：最高性能）
innodb_flush_log_at_trx_commit = 1

# 独立表空间
innodb_file_per_table = 1

# IO线程数（建议为CPU核心数）
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 并发线程数（建议为CPU核心数的2-4倍）
innodb_thread_concurrency = 16

# 双写缓冲
innodb_doublewrite = 1

# 自适应哈希索引
innodb_adaptive_hash_index = 1

# 排序缓冲大小
sort_buffer_size = 256K

# 连接缓冲大小
join_buffer_size = 256K

# 步骤3：重启MySQL服务
systemctl restart mysqld

# 步骤4：验证配置
# 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

# 查看InnoDB配置
SHOW VARIABLES LIKE ‘innodb%’;

# 步骤5：监控性能
# 监控缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 监控IO性能
SHOW GLOBAL STATUS LIKE ‘Innodb_os%’;

# 监控事务情况
SHOW GLOBAL STATUS LIKE ‘Innodb_trx%’;

# 配置调整最佳实践
1. 缓冲池大小：设置为服务器内存的50-80%
2. 重做日志大小：设置为256MB-1GB
3. 缓冲池实例数：设置为CPU核心数
4. IO线程数：设置为CPU核心数
5. 并发线程数：设置为CPU核心数的2-4倍
6. 日志刷新策略：生产环境建议设置为1
7. 独立表空间：开启，便于管理
8. 双写缓冲：开启，提高数据可靠性
9. 自适应哈希索引：开启，提高查询性能
10. 定期监控：定期检查InnoDB状态，及时调整配置

# 查看表空间使用情况
SHOW TABLE STATUS LIKE ‘users’;

# 步骤2：数据压缩
# 创建压缩表
CREATE TABLE users_compressed (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

# 查看压缩效果
SHOW TABLE STATUS LIKE ‘users_compressed’;

# 步骤3：碎片整理
# 优化表
OPTIMIZE TABLE users;

# 重建表
ALTER TABLE users ENGINE=InnoDB;

# 查看碎片情况
SHOW TABLE STATUS LIKE ‘users’;

# 步骤4：分区表
# 创建分区表
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027)
);

# 查看分区信息
SHOW CREATE TABLE orders;

# 步骤5：存储设备优化
# 使用SSD存储
# 配置RAID
# 优化文件系统

# 步骤6：备份策略
# 全量备份
mysqldump -u root -p test > test_backup.sql

# 增量备份
mysqlbinlog –start-position=123456 /var/lib/mysql/binlog.000001 > incremental_backup.sql

# 存储优化最佳实践
1. 开启独立表空间：便于管理和维护
2. 合理使用数据压缩：对于大表可以考虑压缩
3. 定期碎片整理：每季度优化一次表
4. 合理使用分区表：对于大表使用分区表
5. 选择合适的存储设备：使用SSD提高IO性能
6. 制定合理的备份策略：定期备份，确保数据安全
7. 监控存储使用情况：定期检查表空间使用情况
8. 合理设计表结构：选择合适的数据类型，避免使用大字段
9. 优化索引：合理设计索引，避免过度索引
10. 定期维护：定期检查和优化存储结构

# 开启Performance Schema
SET GLOBAL performance_schema = 1;

# 步骤2：监控缓冲池
# 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 查看缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 步骤3：监控IO性能
# 查看IO等待情况
SHOW GLOBAL STATUS LIKE ‘Innodb_os%’;

# 查看IO线程状态
SHOW GLOBAL STATUS LIKE ‘Innodb_%io%’;

# 步骤4：监控事务
# 查看事务状态
SHOW ENGINE INNODB STATUS\G

# 查看活跃事务
SELECT * FROM information_schema.innodb_trx;

# 步骤5：监控锁
# 查看锁等待
SELECT * FROM information_schema.innodb_locks;

# 查看锁等待超时
SELECT * FROM information_schema.innodb_lock_waits;

# 步骤6：监控查询性能
# 查看慢查询
SELECT * FROM mysql.slow_log;

# 使用pt-query-digest分析慢查询
pt-query-digest /var/log/mysql/slow.log

# 步骤7：监控系统资源
# 查看CPU使用情况
top

# 查看内存使用情况
free -m

# 查看磁盘IO
iostat -x

# 步骤8：使用监控工具
# 使用Prometheus + Grafana监控MySQL
# 使用MySQL Enterprise Monitor
# 使用Nagios或Zabbix

# 性能监控最佳实践
1. 开启慢查询日志：及时发现慢查询
2. 启用Performance Schema：监控MySQL内部性能
3. 定期检查缓冲池：确保缓冲池命中率高于95%
4. 监控IO性能：避免IO瓶颈
5. 监控事务：避免长事务和死锁
6. 监控锁：避免锁冲突
7. 监控系统资源：确保服务器资源充足
8. 使用专业监控工具：如Prometheus + Grafana
9. 建立监控预警：设置性能阈值，及时预警
10. 定期分析性能数据：找出性能瓶颈，及时优化

# 步骤2：数据损坏修复
# 使用innodb_force_recovery参数
# 编辑my.cnf文件
[mysqld]
innodb_force_recovery = 1

# 重启MySQL
systemctl restart mysqld

# 导出数据
mysqldump -u root -p test > test_backup.sql

# 重新创建数据库
DROP DATABASE test;
CREATE DATABASE test;

# 导入数据
mysql -u root -p test < test_backup.sql # 恢复正常配置 # 编辑my.cnf文件，移除innodb_force_recovery参数 # 步骤3：死锁处理 # 查看死锁信息 SHOW ENGINE INNODB STATUS\G # 查看活跃事务 SELECT * FROM information_schema.innodb_trx; # 终止死锁事务 KILL [trx_id]; # 步骤4：磁盘空间不足 # 查看磁盘空间 df -h # 清理二进制日志 PURGE BINARY LOGS BEFORE '2024-01-01'; # 清理慢查询日志 > /var/log/mysql/slow.log

# 步骤5：连接数过多
# 查看当前连接数
SHOW GLOBAL STATUS LIKE ‘Threads%’;

# 查看最大连接数
SHOW VARIABLES LIKE ‘max_connections’;

# 调整最大连接数
SET GLOBAL max_connections = 1000;

# 步骤6：内存不足
# 查看内存使用情况
free -m

# 调整缓冲池大小
SET GLOBAL innodb_buffer_pool_size = 4G;

# 步骤7：IO瓶颈
# 查看IO性能
iostat -x

# 优化IO配置
# 调整innodb_io_capacity参数
SET GLOBAL innodb_io_capacity = 2000;

# 故障处理最佳实践
1. 定期备份：确保数据安全
2. 开启慢查询日志：及时发现性能问题
3. 监控系统资源：避免资源不足
4. 合理配置参数：根据硬件和业务需求调整
5. 定期检查InnoDB状态：及时发现问题
6. 建立故障恢复预案：制定详细的故障恢复流程
7. 培训运维人员：提高故障处理能力
8. 使用专业监控工具：及时发现和预警故障
9. 定期演练故障恢复：确保故障处理流程有效
10. 保持MySQL版本更新：获取最新的bug修复和性能改进

# 问题描述
# 发现查询响应时间长，缓冲池命中率低

# 解决方案
## 步骤1：分析缓冲池使用情况
# 查看缓冲池大小
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

# 预期输出：
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_buffer_pool_size | 134217728 |
+————————-+———+

# 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 预期输出：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| Innodb_buffer_pool_reads | 100000 |
| Innodb_buffer_pool_read_requests | 1000000 |
| Innodb_buffer_pool_write_requests | 500000 |
| Innodb_buffer_pool_pages_data | 8000 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_pages_free | 192 |
+—————————————+————-+

## 步骤2：计算缓冲池命中率
# 计算缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 预期输出：
+———————+
| buffer_pool_hit_rate |
+———————+
| 90.0000 |
+———————+

## 步骤3：调整缓冲池大小
# 编辑my.cnf文件
vim /etc/my.cnf

# 修改缓冲池大小
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8

# 步骤4：重启MySQL服务
systemctl restart mysqld

## 步骤5：验证缓冲池优化效果
# 查看缓冲池大小
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;

# 查看缓冲池使用情况
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

# 计算缓冲池命中率
SELECT (
1 – (
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_reads’) /
(SELECT variable_value FROM information_schema.global_status WHERE variable_name = ‘Innodb_buffer_pool_read_requests’)
)
) * 100 AS buffer_pool_hit_rate;

# 预期输出：
+———————+
| buffer_pool_hit_rate |
+———————+
| 99.5000 |
+———————+

## 步骤6：测试查询响应时间
# 测试查询
SELECT * FROM users WHERE id = 1;
# 优化前：0.1秒
# 优化后：0.01秒

# 处理效果
# 成功优化了InnoDB缓存
# 缓冲池命中率从90%提高到99.5%
# 查询响应时间从0.1秒减少到0.01秒
# 提高了系统性能

# 问题描述
# 发现表空间占用过大，查询响应时间长

# 解决方案
## 步骤1：分析表空间使用情况
# 查看表空间使用情况
SHOW TABLE STATUS LIKE ‘users’;

# 预期输出：
+——-+——–+———+————+——–+—————-+————-+—————–+————–+———–+—————-+———————+———————+————+——————-+———-+—————-+———+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+——-+——–+———+————+——–+—————-+————-+—————–+————–+———–+—————-+———————+———————+————+——————-+———-+—————-+———+
| users | InnoDB | 10 | Dynamic | 1000000 | 200 | 200000000 | 0 | 16384 | 4194304 | 1000001 | 2024-01-01 00:00:00 | 2024-01-01 00:00:00 | NULL | utf8mb4_0900_ai_ci | NULL | | |
+——-+——–+———+————+——–+—————-+————-+—————–+————–+———–+—————-+———————+———————+————+——————-+———-+—————-+———+

## 步骤2：优化表结构
# 分析数据分布
SELECT LENGTH(name), LENGTH(email), LENGTH(address) FROM users LIMIT 10;

# 优化表结构
ALTER TABLE users MODIFY name VARCHAR(30), MODIFY email VARCHAR(80), MODIFY address VARCHAR(150);

## 步骤3：数据压缩
# 创建压缩表
CREATE TABLE users_compressed LIKE users;
ALTER TABLE users_compressed ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

# 导入数据
INSERT INTO users_compressed SELECT * FROM users;

# 查看压缩效果
SHOW TABLE STATUS LIKE ‘users_compressed’;

## 步骤4：碎片整理
# 优化表
OPTIMIZE TABLE users;

# 查看优化后的表空间使用情况
SHOW TABLE STATUS LIKE ‘users’;

## 步骤5：验证存储优化效果
# 查看表空间使用情况
SHOW TABLE STATUS LIKE ‘users’;

# 测试查询响应时间
SELECT * FROM users WHERE id = 1;
# 优化前：0.1秒
# 优化后：0.05秒

# 处理效果
# 成功优化了InnoDB存储
# 表空间占用减少了30%
# 查询响应时间从0.1秒减少到0.05秒
# 提高了存储效率和查询性能

# 问题描述
# 发现高并发场景下性能下降，出现锁等待

# 解决方案
## 步骤1：分析并发情况
# 查看活跃事务
SELECT * FROM information_schema.innodb_trx;

# 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

# 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

## 步骤2：调整并发参数
# 编辑my.cnf文件
vim /etc/my.cnf

# 修改并发参数
[mysqld]
innodb_thread_concurrency = 16
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4

# 步骤3：优化事务
# 减少事务持有锁的时间
# 优化前：
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
— 业务逻辑处理（耗时较长）
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;

# 优化后：
— 业务逻辑处理（在事务外）
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;

## 步骤4：使用乐观锁
# 优化前：使用悲观锁
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET amount = amount + 100 WHERE id = 1;
COMMIT;

# 优化后：使用乐观锁
START TRANSACTION;
SELECT id, amount, version FROM orders WHERE id = 1;
— 业务逻辑处理
UPDATE orders SET amount = amount + 100, version = version + 1 WHERE id = 1 AND version = old_version;
IF ROW_COUNT() = 0 THEN
— 处理冲突
END IF;
COMMIT;

## 步骤5：验证并发优化效果
# 模拟高并发测试
# 使用sysbench测试并发性能
sysbench –db-driver=mysql –mysql-host=localhost –mysql-user=root –mysql-password=root –mysql-db=test –table-size=1000000 –threads=16 –time=60 –report-interval=10 oltp_read_write run

# 预期结果：
# 优化前：QPS = 1000
# 优化后：QPS = 2000

# 处理效果
# 成功优化了InnoDB并发性能
# 并发QPS从1000提高到2000
# 减少了锁等待和死锁情况
# 提高了系统的并发处理能力

# 问题描述
# 发现多表连接查询响应时间长

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

# 解决方案
## 步骤1：分析执行计划
EXPLAIN SELECT
u.name,
o.order_id,
o.amount
FROM
users u
JOIN
orders o ON u.id = o.user_id
WHERE
u.age > 18
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
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 |
+—-+————-+——-+————+——+—————+———+———+——————+———+———-+———————————+

## 步骤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);

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

## 步骤4：验证查询优化效果
# 分析优化后的执行计划
EXPLAIN SELECT
u.name,
o.order_id,
o.amount
FROM
users u
JOIN
orders o ON u.id = o.user_id
WHERE
u.age > 18
AND o.created_at BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
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 | range | PRIMARY,idx_age | idx_age | 5 | NULL | 900000 | 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.u.id | 5 | 30.00 | Using index condition |
+—-+————-+——-+————+——+———————————-+———————————-+———+——————+———+———-+—————-+-

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

# 处理效果
# 成功优化了InnoDB查询性能
# 查询响应时间从5.0秒减少到0.1秒
# 提高了系统性能