MySQL是一个复杂的数据库系统，了解其架构是优化的基础。MySQL主要由以下几个核心组件构成：
负责客户端连接处理、授权认证和安全管理。每个连接都会创建一个线程，这也是为什么连接数会影响性能的原因。
在MySQL 8.0中已被移除，但在5.7及之前版本中，查询缓存存储SELECT语句及其结果集。当相同查询再次执行时，可直接返回缓存结果，但任何表变更都会使相关缓存失效。
将SQL语句解析成内部数据结构，检查SQL语法是否正确。
决定如何执行查询，选择最佳执行计划，包括索引选择、JOIN顺序确定等。
执行SQL语句，调用存储引擎API获取数据。
MySQL的核心组件，负责数据的存储和提取。常见的存储引擎包括：
InnoDB存储引擎的物理文件主要包括：
SHOW PROCESSLIST 显示当前正在执行的线程，可以发现长时间运行的查询。
sqlSHOW FULL PROCESSLIST;
SHOW STATUS 展示服务器状态变量，可以查看QPS、TPS等关键指标。
sqlSHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW VARIABLES 查看MySQL配置参数。
sqlSHOW VARIABLES LIKE 'innodb_buffer_pool_size';
性能模式（Performance Schema） MySQL 5.5引入，提供底层服务器执行情况监控。
sqlSELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
慢查询日志
记录执行时间超过long_query_time的查询。
sqlSHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
EXPLAIN 分析SQL执行计划。
sqlEXPLAIN SELECT * FROM users WHERE user_id = 1;
MySQL Workbench 官方GUI工具，提供性能仪表盘和查询分析。
Percona Toolkit 包含多种实用工具，如pt-query-digest（分析慢查询日志）。
SysBench 基准测试工具，可模拟各种负载场景。
innotop 类似top命令的MySQL监控工具，实时显示服务器状态。
Prometheus + Grafana 监控和可视化解决方案，可创建MySQL性能仪表盘。
症状：
诊断方法：
bash# 查看CPU使用率
top -bn1 | grep "Cpu(s)"
# 查看MySQL线程CPU使用情况
ps -eo pid,pcpu,cmd | grep mysqld
案例分析：
某电商系统在促销活动期间，CPU使用率飙升至95%，响应时间从50ms增至500ms。通过SHOW PROCESSLIST发现大量复杂统计查询。优化方案：将统计查询改为预计算并缓存结果，CPU使用率降至60%，响应时间恢复正常。
症状：
诊断方法：
sql-- 查看InnoDB缓冲池命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 计算命中率
-- (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100%
案例分析： 某数据分析系统InnoDB缓冲池命中率仅为75%，查询性能差。分析发现buffer_pool_size仅设置为4GB，而工作集数据约8GB。将buffer_pool_size增加到12GB后，命中率提升至98%，查询性能提升3倍。
症状：
诊断方法：
bash# 查看磁盘I/O情况
iostat -xd 1
# 查看MySQL文件I/O
SHOW GLOBAL STATUS LIKE '%innodb_data_read%';
案例分析： 某CRM系统随机I/O延迟高达30ms，影响交易处理。通过iostat发现普通SATA磁盘IOPS仅为100。迁移到SSD后，随机I/O延迟降至0.5ms，系统吞吐量提升10倍。
症状：
诊断方法：
sql-- 查看当前锁等待
SHOW ENGINE INNODB STATUS\G
-- 查看锁等待情况（MySQL 5.7+）
SELECT * FROM performance_schema.data_lock_waits;
案例分析：
某订单系统在高峰期出现大量锁等待，TPS从1000降至200。分析发现更新操作使用了SELECT ... FOR UPDATE语句但未使用索引，导致表级锁。修改为使用主键索引的行锁后，TPS恢复至950。
B+树索引
哈希索引
全文索引
空间索引
InnoDB的B+树索引结构：
最左前缀匹配原则 复合索引只有使用从左到右的连续字段才能命中索引。
选择性原则
选择区分度高的列作为索引，计算公式：count(distinct column_value) / count(*)，比值越接近1越好。
覆盖索引原则 尽量使用索引覆盖查询，避免回表操作。
索引列独立原则 索引列不参与计算或函数操作。
控制索引数量 过多索引会增加维护成本和写入负担。
前缀索引 对于长字符串，可以只索引前几个字符，节省空间。
避免冗余索引 如已有(a,b)索引，则(a)索引是冗余的。
问题：某电商平台订单查询接口响应慢，平均耗时2秒。
原SQL：
sqlSELECT * FROM orders
WHERE user_id = 10001
AND order_status = 'PAID'
AND create_time > '2023-01-01'
ORDER BY create_time DESC
LIMIT 20;
EXPLAIN分析：
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 1000K| Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
优化方案：
sqlCREATE INDEX idx_user_status_time ON orders(user_id, order_status, create_time);
+----+-------------+--------+-------+-------------------+-------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-------------------+-------------------+---------+------+------+-------------+ | 1 | SIMPLE | orders | range | idx_user_status_time | idx_user_status_time | 259 | NULL | 200 | Using index | +----+-------------+--------+-------+-------------------+-------------------+---------+------+------+-------------+
效果：查询时间从2秒降至20ms，提升100倍。
问题：用户搜索功能响应慢，CPU使用率高。
原SQL：
sqlSELECT * FROM products
WHERE CONCAT(product_name, '') = 'iPhone'
AND category_id = 3;
EXPLAIN分析：虽然product_name和category_id都有索引，但CONCAT函数导致索引失效。
优化方案：
sqlSELECT * FROM products
WHERE product_name = 'iPhone'
AND category_id = 3;
效果：查询时间从1.5秒降至30ms，CPU使用率降低40%。
问题：商品列表页面加载慢，数据库I/O高。
原SQL：
sqlSELECT id, name, price, stock FROM products
WHERE category_id = 5
ORDER BY create_time DESC
LIMIT 0, 20;
优化方案：
sql-- 创建覆盖索引
CREATE INDEX idx_category_time_cover ON products(category_id, create_time, id, name, price, stock);
效果：查询时间从800ms降至15ms，I/O减少95%。
只查询需要的列
避免SELECT *，减少网络传输和内存使用。
减少数据扫描量 使用索引、分区和限制结果集大小。
避免全表扫描 确保查询条件使用了索引。
合理使用临时表 复杂查询可以分解为多个简单查询。
优化排序操作 利用索引排序，避免filesort。
优化GROUP BY 可以使用索引优化，避免临时表。
使用合适的数据类型 选择最小满足需求的数据类型。
避免使用OR 可能导致索引失效，考虑使用UNION ALL替代。
内连接（INNER JOIN） 只返回两表中匹配的行。
左连接（LEFT JOIN） 返回左表所有行和右表匹配行。
右连接（RIGHT JOIN） 返回右表所有行和左表匹配行。
小表驱动大表 让小表做驱动表，减少嵌套循环次数。
使用索引 JOIN条件列必须有索引。
减少JOIN表数量 一般不超过5个表的JOIN。
使用JOIN缓冲区
调整join_buffer_size参数。
拆分复杂JOIN 将多表JOIN拆分为多个简单查询。
问题：订单报表查询缓慢，涉及5个表JOIN。
原SQL：
sqlSELECT o.id, o.order_no, u.username, p.product_name, o.quantity, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN payment_records pr ON o.id = pr.order_id
WHERE o.create_time BETWEEN '2023-01-01' AND '2023-01-31';
优化方案：
sql-- 优化后的SQL
SELECT o.id, o.order_no, u.username, p.product_name, o.quantity, o.total_amount
FROM (
SELECT id, order_no, user_id, quantity, total_amount
FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31'
) o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN payment_records pr ON o.id = pr.order_id;
效果：查询时间从15秒降至0.8秒。
执行效率低 子查询可能导致临时表创建和额外扫描。
优化器限制 MySQL优化器对子查询的处理不够智能。
使用JOIN替代子查询 特别是IN子查询。
使用EXISTS替代IN 当外表大内表小时。
使用临时表 将子查询结果存入临时表再JOIN。
问题：查找购买了指定商品的用户列表查询慢。
原SQL：
sqlSELECT * FROM users
WHERE id IN (
SELECT DISTINCT user_id FROM orders
WHERE id IN (
SELECT order_id FROM order_items
WHERE product_id = 1001
)
);
优化方案：
sqlSELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id = 1001;
效果：查询时间从3.5秒降至0.2秒。
sqlSELECT * FROM products ORDER BY id LIMIT 10000, 20;
这种查询会扫描10020行，但只返回20行，当偏移量大时性能极差。
sqlSELECT * FROM products
WHERE id > (
SELECT id FROM products ORDER BY id LIMIT 10000, 1
)
ORDER BY id LIMIT 20;
sqlSELECT p.* FROM products p
JOIN (
SELECT id FROM products ORDER BY id LIMIT 10000, 20
) AS tmp USING(id);
记住上次查询的最后一个ID，下次从该ID开始查询：
sqlSELECT * FROM products
WHERE id > 10234 -- 上次查询的最后一个ID
ORDER BY id LIMIT 20;
问题：电商平台商品列表第500页（偏移量10000）加载时间超过3秒。
原SQL：
sqlSELECT * FROM products
WHERE category_id = 15
ORDER BY create_time DESC
LIMIT 10000, 20;
优化方案：
sql-- 记录上次查询的最后一条记录的create_time和id
SELECT * FROM products
WHERE category_id = 15
AND (create_time < '2023-01-15 14:35:22'
OR (create_time = '2023-01-15 14:35:22' AND id < 54321))
ORDER BY create_time DESC, id DESC
LIMIT 20;
效果：查询时间从3.2秒降至0.05秒。
作用：InnoDB缓冲池大小，缓存表数据和索引。
建议值：服务器物理内存的50%-80%。
调优案例： 某数据库服务器32GB内存，初始设置buffer_pool为4GB。系统监控显示磁盘读I/O高，缓冲池命中率只有70%。将buffer_pool增加到24GB后，命中率提升至98%，I/O降低80%。
sqlSET GLOBAL innodb_buffer_pool_size = 24 * 1024 * 1024 * 1024; -- 24GB
作用：将缓冲池分成多个实例，减少并发访问的内部竞争。
建议值：
sqlSET GLOBAL innodb_buffer_pool_instances = 8;
作用：MyISAM表索引缓冲区大小。
建议值：如果使用MyISAM表，设置为物理内存的25%左右。
作用：所有线程打开表的缓存数量。
建议值：根据max_connections和应用打开的表数量确定。
调优公式：table_open_cache = max_connections * 表/线程
作用：每个需要排序的线程分配的缓冲区大小。
建议值：默认值通常足够，除非有大量排序操作。
注意：这是每个连接独立分配的，设置过大会消耗过多内存。
作用：允许的最大客户端连接数。
建议值：根据硬件资源和应用需求设置，通常200-1000。
监控方法：
sqlSHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
调优案例： 某Web应用在高峰期出现"Too many connections"错误。监控显示Max_used_connections经常接近max_connections（默认151）。将max_connections增加到500，并优化连接池配置后，错误消失，系统稳定性提高。
作用：InnoDB内核允许的最大线程数。
建议值：
sqlSET GLOBAL innodb_thread_concurrency = 16; -- 8核CPU
作用：缓存多少空闲线程以备重用。
建议值：8 + (max_connections / 100)，通常设置为16-64。
监控方法：
sqlSHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';
如果Threads_created/Connections比率高，说明线程缓存太小。
调优案例： 某应用服务器Threads_created/Connections比率为0.25，表明每4个连接就要创建一个新线程。增加thread_cache_size从8到32后，比率降至0.05，减少了线程创建开销。
作用：InnoDB重做日志文件大小，影响崩溃恢复速度和事务性能。
建议值：
监控方法：
sqlSHOW ENGINE INNODB STATUS\G
-- 查看Log sequence number和Last checkpoint at的差距
调优案例： 某OLTP系统在高峰期出现间歇性卡顿，监控发现日志文件频繁切换。将innodb_log_file_size从64MB增加到512MB后，写入性能提升30%，卡顿现象消失。
作用：InnoDB日志缓冲区大小，减少磁盘I/O。
建议值：
sqlSET GLOBAL innodb_log_buffer_size = 32 * 1024 * 1024; -- 32MB
sync_binlog
作用：控制二进制日志写入磁盘的频率。
建议值：
调优案例： 某支付系统为保证数据安全，设置sync_binlog=1，但TPS只有800。在增加了电池供电缓存的RAID卡后，将sync_binlog调整为100，TPS提升至2000，同时保持了可接受的安全性。
作用：启用慢查询日志。
相关参数：
建议配置：
sqlSET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5; -- 500毫秒
SET GLOBAL log_queries_not_using_indexes = ON;
共享锁（S锁）：
SELECT ... LOCK IN SHARE MODE获取排他锁（X锁）：
SELECT ... FOR UPDATE或UPDATE、DELETE语句获取意向共享锁（IS锁）：
意向排他锁（IX锁）：
记录锁（Record Lock）：
间隙锁（Gap Lock）：
临键锁（Next-Key Lock）：
| 已有锁\请求锁 | IS | IX | S | X |
|---|---|---|---|---|
| IS | 兼容 | 兼容 | 兼容 | 不兼容 |
| IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
| S | 兼容 | 不兼容 | 兼容 | 不兼容 |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
死锁是指两个或多个事务互相持有对方需要的锁，导致这些事务永远等待。常见原因：
SHOW ENGINE INNODB STATUS
sqlSHOW ENGINE INNODB STATUS\G
-- 查看LATEST DETECTED DEADLOCK部分
information_schema.INNODB_TRX
sqlSELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT';
performance_schema
sqlSELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
MySQL企业版监视器 提供更详细的锁信息和可视化界面。
收集死锁信息
分析事务模式
检查索引使用情况
模拟复现
问题描述： 电商平台订单处理系统频繁出现死锁，影响订单处理速度。
死锁日志：
*** (1) TRANSACTION: SELECT * FROM orders WHERE id = 10001 FOR UPDATE; SELECT * FROM order_items WHERE order_id = 10001 FOR UPDATE; *** (2) TRANSACTION: SELECT * FROM order_items WHERE order_id = 10001 FOR UPDATE; SELECT * FROM orders WHERE id = 10001 FOR UPDATE;
原因分析： 两个事务访问相同资源但顺序不同，形成环路等待。
解决方案：
java// 优化前
public void processOrder(int orderId) {
// 事务1可能先锁orders，事务2可能先锁order_items
if (random.nextBoolean()) {
lockOrders(orderId);
lockOrderItems(orderId);
} else {
lockOrderItems(orderId);
lockOrders(orderId);
}
}
// 优化后
public void processOrder(int orderId) {
// 统一顺序：始终先锁orders再锁order_items
lockOrders(orderId);
lockOrderItems(orderId);
}
效果： 实施后死锁减少99%，订单处理速度提升30%。
问题描述： 用户积分更新操作在高峰期出现大量锁等待，系统吞吐量下降。
问题SQL：
sqlUPDATE user_points SET points = points + 100 WHERE username = 'user123';
原因分析： username列没有索引，导致MySQL使用表锁而非行锁。
解决方案：
sqlCREATE INDEX idx_username ON user_points(username);
sql-- 先查询用户ID
SELECT id FROM user_points WHERE username = 'user123';
-- 再使用主键更新
UPDATE user_points SET points = points + 100 WHERE id = 10001;
效果： 锁等待减少95%，系统吞吐量提升5倍。
问题描述： 报表系统在生成日报的同时，无法插入新的统计数据，出现锁等待。
问题SQL：
sql-- 事务1：报表查询
SELECT * FROM daily_stats WHERE stat_date BETWEEN '2023-01-01' AND '2023-01-31' FOR UPDATE;
-- 事务2：插入新数据
INSERT INTO daily_stats(stat_date, pv, uv) VALUES('2023-01-15', 10000, 5000);
原因分析： REPEATABLE READ隔离级别下，范围查询使用Next-Key锁，锁定了范围内的所有记录和间隙。
解决方案：
sqlSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
sqlSELECT * FROM daily_stats WHERE stat_date BETWEEN '2023-01-01' AND '2023-01-31' LOCK IN SHARE MODE;
效果： 报表生成过程中仍可插入新数据，系统并发性提升50%。
适用场景：
示例：
sqlCREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION future VALUES LESS THAN MAXVALUE
);
优化效果： 某电商平台订单表按月分区后，月度报表查询从15秒降至2秒，历史数据清理操作从小时级降至分钟级。
适用场景：
示例：
sqlCREATE TABLE regional_sales (
id INT NOT NULL,
country VARCHAR(20) NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY LIST (country) (
PARTITION p_asia VALUES IN ('China', 'Japan', 'Korea'),
PARTITION p_europe VALUES IN ('Germany', 'France', 'UK'),
PARTITION p_america VALUES IN ('USA', 'Canada', 'Brazil')
);
优化效果： 某跨国企业CRM系统按地区分区后，区域销售报表查询性能提升4倍。
适用场景：
示例：
sqlCREATE TABLE page_views (
id INT NOT NULL,
url VARCHAR(255) NOT NULL,
view_date DATE
)
PARTITION BY HASH (id) PARTITIONS 8;
优化效果： 某内容平台用户行为分析系统采用HASH分区后，数据加载并行度提高，导入速度提升3倍。
适用场景：
示例：
sqlCREATE TABLE sessions (
id INT NOT NULL,
user_id INT,
session_data BLOB
)
PARTITION BY KEY (id, user_id) PARTITIONS 4;
添加分区：
sql-- 为RANGE分区表添加新分区
ALTER TABLE sales ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));
删除分区：
sql-- 删除旧数据分区（同时删除数据）
ALTER TABLE sales DROP PARTITION p2021;
案例： 某日志系统每月自动添加下个月分区并删除一年前分区，维护成本降低90%，表大小保持稳定。
拆分分区：
sql-- MySQL 8.0+支持
ALTER TABLE sales REORGANIZE PARTITION p2023 INTO (
PARTITION p2023h1 VALUES LESS THAN ('2023-07-01'),
PARTITION p2023h2 VALUES LESS THAN ('2024-01-01')
);
合并分区：
sqlALTER TABLE sales REORGANIZE PARTITION p2021, p2022 INTO (
PARTITION p2021_2022 VALUES LESS THAN (2023)
);
案例： 某金融系统将当年分区按季度细分，历史年份分区合并，查询性能提升40%，存储空间减少15%。
局部索引：
全局索引：
最佳实践：
案例： 某电商平台订单表按月分区，并确保常用查询条件包含订单日期，实现了分区裁剪，查询性能提升8倍。
MVCC（Multi-Version Concurrency Control）是InnoDB实现并发控制的核心机制，通过保存数据的多个版本，允许读写操作并行执行，提高并发性能。
版本链：
快照读：
当前读：
当事务对记录进行修改时，InnoDB会：
示例：
最新记录: [DB_TRX_ID=50, DB_ROLL_PTR=指向版本2, data=C] 版本2: [DB_TRX_ID=30, DB_ROLL_PTR=指向版本1, data=B] 版本1: [DB_TRX_ID=10, DB_ROLL_PTR=NULL, data=A]
Read View包含以下信息：
事务通过Read View判断记录版本可见性：
如果当前版本不可见，沿着版本链找到可见的历史版本。
READ COMMITTED：
REPEATABLE READ：
案例： 某支付系统从SERIALIZABLE降级到REPEATABLE READ后，TPS提升5倍，同时保证了事务一致性。
长事务会导致：
优化策略：
sqlSELECT * FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 10 MINUTE;
sqlSET GLOBAL innodb_lock_wait_timeout = 50; -- 默认50秒
案例： 某报表系统实施事务拆分和定时提交策略后，长事务减少90%，系统整体响应时间提升40%。
幻读：事务执行过程中，其他事务插入满足当前事务查询条件的新记录。
解决方案：
案例： 某库存系统在高并发下出现超卖问题，通过在库存检查时使用SELECT FOR UPDATE锁定记录，消除了幻读问题，系统正确性得到保证。
性能指标：
资源指标：
存储指标：
开源监控方案：
Prometheus + Grafana
部署示例：
yaml# docker-compose.yml
version: '3'
services:
prometheus:
image: prom/prometheus
volumes:
- ./prometheus.yml:/etc/prometheus/prometheus.yml
ports:
- "9090:9090"
mysqld-exporter:
image: prom/mysqld-exporter
environment:
- DATA_SOURCE_NAME=user:password@(mysql:3306)/
ports:
- "9104:9104"
grafana:
image: grafana/grafana
ports:
- "3000:3000"
depends_on:
- prometheus
Zabbix
PMM (Percona Monitoring and Management)
自研监控系统：
针对特定业务场景的自定义监控，通常包括：
多级预警：
告警渠道：
降噪策略：
案例： 某电商平台通过多级预警系统，在大促前发现连接数增长异常，提前扩容数据库连接池，避免了系统崩溃，保障了业务连续性。
慢查询分析脚本：
bash#!/bin/bash
# 分析最近一小时的慢查询日志
SLOW_LOG="/var/lib/mysql/slow.log"
OUTPUT_DIR="/var/log/mysql/reports"
DATE_SUFFIX=$(date +"%Y%m%d_%H%M")
# 创建输出目录
mkdir -p $OUTPUT_DIR
# 使用pt-query-digest分析慢查询
pt-query-digest --limit=20 \
--since="1h" \
--output=$OUTPUT_DIR/slow_report_$DATE_SUFFIX.txt \
$SLOW_LOG
# 发送报告
cat $OUTPUT_DIR/slow_report_$DATE_SUFFIX.txt | \
mail -s "MySQL慢查询小时报告" dba@example.com
连接监控脚本：
bash#!/bin/bash
# 监控MySQL连接数并预警
MAX_CONN=$(mysql -e "SHOW VARIABLES LIKE 'max_connections'" -s | awk '{print $2}')
CURR_CONN=$(mysql -e "SHOW STATUS LIKE 'Threads_connected'" -s | awk '{print $2}')
# 计算连接使用率
CONN_USAGE=$(awk "BEGIN {printf \"%.2f\", $CURR_CONN/$MAX_CONN*100}")
# 根据使用率发送不同级别的告警
if [ $(echo "$CONN_USAGE > 90" | bc) -eq 1 ]; then
echo "[紧急] MySQL连接数使用率达到 ${CONN_USAGE}%" | \
mail -s "MySQL连接数告警" dba@example.com
elif [ $(echo "$CONN_USAGE > 80" | bc) -eq 1 ]; then
echo "[警告] MySQL连接数使用率达到 ${CONN_USAGE}%" | \
mail -s "MySQL连接数告警" dba@example.com
fi
表空间监控脚本：
bash#!/bin/bash
# 监控表空间增长并预测未来空间需求
OUTPUT_FILE="/tmp/mysql_space_$(date +%Y%m%d).txt"
# 获取数据库表空间信息
mysql -e "SELECT table_schema, \
ROUND(SUM(data_length+index_length)/1024/1024,2) AS size_mb, \
ROUND(SUM(data_free)/1024/1024,2) AS free_mb \
FROM information_schema.tables \
GROUP BY table_schema" > $OUTPUT_FILE
# 检查增长率（与昨日比较）
YESTERDAY_FILE="/tmp/mysql_space_$(date -d "yesterday" +%Y%m%d).txt"
if [ -f "$YESTERDAY_FILE" ]; then
# 计算增长率并记录增长超过10%的数据库
awk 'NR==FNR{a[$1]=$2; next} \
($1 in a) && ($2 > a[$1]*1.1) \
{printf "%s: 增长率 %.2f%%\n", $1, ($2-a[$1])/a[$1]*100}' \
$YESTERDAY_FILE $OUTPUT_FILE > /tmp/growth.txt
# 如果有异常增长，发送告警
if [ -s "/tmp/growth.txt" ]; then
cat /tmp/growth.txt | mail -s "MySQL表空间异常增长告警" dba@example.com
fi
fi
索引优化检测脚本：
bash#!/bin/bash
# 检测未使用的索引和冗余索引
# 获取未使用的索引
mysql -e "SELECT t.table_schema, t.table_name, s.index_name, s.column_name \
FROM information_schema.statistics s \
JOIN information_schema.tables t ON s.table_schema = t.table_schema \
AND s.table_name = t.table_name \
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage i \
ON i.object_schema = t.table_schema \
AND i.object_name = t.table_name \
AND i.index_name = s.index_name \
WHERE t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys') \
AND i.count_star IS NULL OR i.count_star = 0 \
ORDER BY t.table_schema, t.table_name, s.index_name;" > /tmp/unused_indexes.txt
# 发送报告
if [ -s "/tmp/unused_indexes.txt" ]; then
echo "以下索引在监控期内未被使用，建议评估是否需要删除：" | \
cat - /tmp/unused_indexes.txt | mail -s "MySQL未使用索引报告" dba@example.com
fi
bash#!/bin/bash
# MySQL自动备份脚本
# 配置
BACKUP_DIR="/data/mysql/backup"
MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
DATE=$(date +"%Y%m%d")
RETENTION_DAYS=7
# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE
# 获取所有数据库列表（排除系统库）
DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")
# 备份每个数据库
for db in $DATABASES; do
echo "备份数据库: $db"
mysqldump -u$MYSQL_USER -p$MYSQL_PASS --single-transaction --routines --triggers --events $db | gzip > $BACKUP_DIR/$DATE/$db.sql.gz
done
# 备份全局对象（用户、函数等）
mysqldump -u$MYSQL_USER -p$MYSQL_PASS --routines --triggers --events --no-data --no-create-db --no-create-info --all-databases | gzip > $BACKUP_DIR/$DATE/global_objects.sql.gz
# 删除过期备份
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
# 检查备份是否成功
if [ $? -eq 0 ]; then
echo "MySQL备份完成: $DATE" | mail -s "MySQL备份成功" dba@example.com
else
echo "MySQL备份失败: $DATE" | mail -s "MySQL备份失败" dba@example.com
fi
bash#!/bin/bash
# 自动优化表和分析表
# 配置
MYSQL_USER="maint_user"
MYSQL_PASS="maint_password"
LOG_FILE="/var/log/mysql/maintenance_$(date +"%Y%m%d").log"
# 获取所有用户表
TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SELECT CONCAT(table_schema, '.', table_name) \
FROM information_schema.tables \
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys') \
AND table_type = 'BASE TABLE';" | grep -v "table_schema")
# 记录开始时间
echo "维护开始: $(date)" > $LOG_FILE
# 对每个表执行优化
for table in $TABLES; do
echo "处理表: $table" >> $LOG_FILE
# 检查表
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "CHECK TABLE $table" >> $LOG_FILE 2>&1
# 分析表
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "ANALYZE TABLE $table" >> $LOG_FILE 2>&1
# 如果是MyISAM表，执行优化
engine=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SELECT engine FROM information_schema.tables \
WHERE CONCAT(table_schema, '.', table_name) = '$table';" | grep -v "engine")
if [ "$engine" = "MyISAM" ]; then
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "OPTIMIZE TABLE $table" >> $LOG_FILE 2>&1
fi
done
# 记录结束时间
echo "维护结束: $(date)" >> $LOG_FILE
# 发送报告
cat $LOG_FILE | mail -s "MySQL表维护报告" dba@example.com
案例： 某电商平台通过自动化脚本体系，实现了数据库性能的持续优化。在业务高峰期前，系统自动检测并优化热点表，清理未使用索引，预测空间增长并提前扩容。这套自动化体系使DBA团队工作效率提升200%，系统稳定性显著增强，平均故障恢复时间从小时级降至分钟级。
本文作者:大哥吕
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！