qidao123.com技术社区-IT企服评测·应用市场

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

作者: 去皮卡多 时间: 2025-5-5 14:00
标题: 从实列中学习linux shell12 通过Shell脚本来优化MySQL数据库性能，特别是慢SQL跟踪和索引优化
在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 mysql
echo "慢查询日志已启用，日志路径：$SLOW_LOG_PATH"

复制代码

2. 分析慢查询日志

目标：利用工具分析日志并天生报告。

# 安装Percona Toolkit（如未安装）
if ! command -v pt-query-digest &> /dev/null; then
echo "安装Percona Toolkit..."
sudo apt-get install percona-toolkit
fi
# 生成慢查询分析报告
REPORT_FILE="slow_query_report.txt"
pt-query-digest $SLOW_LOG_PATH > $REPORT_FILE
echo "慢查询分析报告已生成：$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_REPORT
echo "索引优化建议已生成：$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; do
TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW TABLES FROM $DB;" | tail -n +2)
for TABLE in $TABLES; do
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "ANALYZE TABLE $DB.$TABLE;"
done
done
echo "表统计信息已更新"

复制代码

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).txt
mysql -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定时任务，比方天天凌晨执行。

注意事项

权限：确保MySQL用户有权限访问sys库和修改全局变量。
日志管理：定期清算慢查询日志，制止磁盘空间不足。
生产环境：审慎在高峰时段启用日志，制止I/O压力。

通过上述脚本，可自动化实现MySQL性能监控与优化，明显提拔数据库服从。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 qidao123.com技术社区-IT企服评测·应用市场 (https://dis.qidao123.com/)

Powered by Discuz! X3.4