当前位置：首页 > news >正文

从实列中学习linux shell12 通过Shell脚本来优化MySQL数据库性能，特别是慢SQL跟踪和索引优化

news 2025/5/5 6:36:37

在Shell脚本中优化MySQL数据库性能，特别是慢SQL跟踪和索引优化

可以通过以下步骤实现。以下是一个结构化的解决方案，包含示例代码和详细说明：

1. 启用慢查询日志

目标：动态启用慢查询日志并配置参数，收集慢SQL数据。

#!/bin/bash# 配置慢查询参数
MYSQL_USER="root"
MYSQL_PASS="your_password"
SLOW_LOG_PATH="/var/log/mysql/mysql-slow.log"
LONG_QUERY_TIME=1  # 记录超过1秒的查询# 动态启用慢查询日志（无需重启）
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = $LONG_QUERY_TIME;
SET GLOBAL slow_query_log_file = '$SLOW_LOG_PATH';
"# 可选：修改配置文件持久化设置（需重启MySQL）
sudo sed -i '/^#slow_query_log/ c\slow_query_log = ON' /etc/mysql/my.cnf
sudo sed -i "/^#long_query_time/ c\long_query_time = $LONG_QUERY_TIME" /etc/mysql/my.cnf
sudo systemctl restart mysqlecho "慢查询日志已启用，日志路径：$SLOW_LOG_PATH"

2. 分析慢查询日志

目标：使用工具分析日志并生成报告。

# 安装Percona Toolkit（如未安装）
if ! command -v pt-query-digest &> /dev/null; thenecho "安装Percona Toolkit..."sudo apt-get install percona-toolkit
fi# 生成慢查询分析报告
REPORT_FILE="slow_query_report.txt"
pt-query-digest $SLOW_LOG_PATH > $REPORT_FILEecho "慢查询分析报告已生成：$REPORT_FILE"

3. 索引优化分析

目标：检查缺失或冗余索引，生成优化建议。

# 生成索引优化建议
INDEX_REPORT="index_optimization_report.txt"# 检查未使用的索引
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;
" >> $INDEX_REPORT# 检查冗余索引
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT table_schema, table_name, index_name, redundant_index_name
FROM sys.schema_redundant_indexes;
" >> $INDEX_REPORTecho "索引优化建议已生成：$INDEX_REPORT"

4. 定期维护任务

目标：自动执行表分析和统计信息更新。

# 分析所有表以更新统计信息
DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")for DB in $DATABASES; doTABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW TABLES FROM $DB;" | tail -n +2)for TABLE in $TABLES; domysql -u$MYSQL_USER -p$MYSQL_PASS -e "ANALYZE TABLE $DB.$TABLE;"done
doneecho "表统计信息已更新"

5. 自动化脚本整合

完整脚本示例：将上述步骤整合为定时任务。

#!/bin/bash# 配置参数
MYSQL_USER="root"
MYSQL_PASS="your_password"
SLOW_LOG_PATH="/var/log/mysql/mysql-slow.log"
LONG_QUERY_TIME=1
REPORT_DIR="/opt/mysql_reports"mkdir -p $REPORT_DIR# 启用慢查询日志
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = $LONG_QUERY_TIME;
SET GLOBAL slow_query_log_file = '$SLOW_LOG_PATH';
"# 等待收集数据（例如1小时）
sleep 3600# 关闭慢查询日志（可选）
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SET GLOBAL slow_query_log = 'OFF';"# 生成慢查询报告
pt-query-digest $SLOW_LOG_PATH > $REPORT_DIR/slow_query_$(date +%F).txt# 生成索引优化报告
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;
" > $REPORT_DIR/index_unused_$(date +%F).txtmysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT table_schema, table_name, index_name, redundant_index_name
FROM sys.schema_redundant_indexes;
" > $REPORT_DIR/index_redundant_$(date +%F).txt# 发送邮件通知（可选）
echo "性能报告已生成" | mail -A "$REPORT_DIR/*_$(date +%F).txt" admin@example.com

关键说明

慢查询日志：
- 动态启用日志无需重启，但需确保log_output=FILE。
- 使用pt-query-digest可生成更详细的报告（比mysqldumpslow更强大）。
索引优化：
- 通过sys.schema_unused_indexes和schema_redundant_indexes快速定位问题。
- 生成报告后，手动审核建议再执行ALTER TABLE操作。
定期维护：
- 使用ANALYZE TABLE更新统计信息，帮助优化器选择更优的执行计划。
- 可将脚本加入cron定时任务，例如每天凌晨执行。