实用且高频的 SQL 工具脚本代码示例以及脚本工具

🤟致敬读者

• 🟩感谢阅读🟦笑口常开🟪生日快乐⬛早点睡觉

📘博主相关

• 🟧博主信息🟨博客首页🟫专栏推荐🟥活动信息

---

---

📃文章前言

• 🔷文章均为学习工作中整理的笔记。
• 🔶如有错误请指正，共同学习进步。

---

实用且高频的 SQL 工具脚本代码示例以及脚本工具

一、实用且高频的 SQL 工具脚本代码示例

以下是一些实用且高频的 SQL 工具脚本代码示例，涵盖数据库维护、性能优化、数据操作等场景，适用于 MySQL、PostgreSQL 等主流数据库：

---

1. 数据库维护类

1.1 数据库备份与恢复

-- MySQL 备份单表（导出结构和数据）
mysqldump -u 用户名 -p 数据库名 表名 > backup_table.sql

-- PostgreSQL 备份整个数据库
pg_dump -U 用户名 -d 数据库名 > backup_db.sql

-- 恢复备份（通用）
mysql -u 用户名 -p 数据库名 < backup.sql
psql -U 用户名 -d 数据库名 -f backup.sql

1.2 批量生成表结构文档

-- MySQL 查询所有表结构
SELECT 
    TABLE_NAME AS 表名,
    COLUMN_NAME AS 字段名,
    DATA_TYPE AS 数据类型,
    COLUMN_COMMENT AS 描述
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = '数据库名';

---

2. 性能优化类

2.1 索引分析与优化

-- MySQL 查看未使用索引的表
SELECT 
    object_schema, object_name, index_name
FROM 
    performance_schema.table_io_waits_summary_by_index_usage
WHERE 
    index_name IS NOT NULL
    AND count_star = 0;

-- PostgreSQL 查找缺失索引
SELECT 
    relname AS 表名,
    seq_scan - idx_scan AS 全表扫描次数差,
    CASE 
        WHEN seq_scan - idx_scan > 0 THEN '建议添加索引'
        ELSE '无需优化'
    END AS 建议
FROM 
    pg_stat_all_tables
WHERE 
    schemaname = 'public';

2.2 检测长时间运行的查询

-- MySQL 查看正在执行的慢查询
SHOW FULL PROCESSLIST;
-- 或通过慢查询日志分析（需提前开启配置）

-- PostgreSQL 监控活跃查询
SELECT 
    pid, query, now() - query_start AS 执行时长
FROM 
    pg_stat_activity
WHERE 
    state = 'active'
    AND query NOT LIKE '%pg_stat_activity%';

---

3. 数据操作类

3.1 批量插入/更新

-- 批量插入（MySQL/PostgreSQL通用）
INSERT INTO users (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 28);

-- 批量更新（MySQL）
UPDATE users
SET status = CASE id
    WHEN 1 THEN 'active'
    WHEN 2 THEN 'inactive'
END
WHERE id IN (1, 2);

-- 批量更新（PostgreSQL）
UPDATE users AS u
SET status = tmp.status
FROM (VALUES
    (1, 'active'),
    (2, 'inactive')
) AS tmp(id, status)
WHERE u.id = tmp.id;

3.2 数据去重

-- 通用：删除重复记录（保留id最大的一条）
DELETE FROM orders
WHERE id NOT IN (
    SELECT MAX(id)
    FROM orders
    GROUP BY order_no
);

3.3 分页查询优化

-- MySQL（LIMIT-OFFSET）
SELECT * FROM products 
ORDER BY id
LIMIT 10 OFFSET 20;  -- 第3页，每页10条

-- PostgreSQL（窗口函数高效分页）
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM products
) AS tmp
WHERE row_num BETWEEN 21 AND 30;

---

4. 监控与安全类

4.1 用户权限检查

-- MySQL 查看用户权限
SHOW GRANTS FOR '用户名'@'主机';

-- PostgreSQL 列出所有用户角色
SELECT rolname, rolcreaterole, rolsuper 
FROM pg_roles;

4.2 表空间监控

-- MySQL 查看表大小
SELECT 
    TABLE_NAME AS 表名,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 大小(MB)
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = '数据库名'
ORDER BY 
    (DATA_LENGTH + INDEX_LENGTH) DESC;

-- PostgreSQL 查看数据库大小
SELECT 
    pg_size_pretty(pg_database_size('数据库名')) AS 大小;

---

5. 动态 SQL 与数据清洗

5.1 动态生成条件查询

-- 示例：根据参数动态筛选（伪代码逻辑）
SET @sql = CONCAT('
    SELECT * FROM orders 
    WHERE 1=1
    ', IF(region IS NOT NULL, ' AND region = ?', ''),
    ' ORDER BY create_time DESC'
);
PREPARE stmt FROM @sql;
EXECUTE stmt USING region_value;

5.2 数据清洗（拆分字段）

-- MySQL 拆分逗号分隔的字符串
SELECT 
    id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n), ',', -1) AS tag
FROM 
    products
CROSS JOIN 
    (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3) numbers
WHERE 
    n <= LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1;

-- PostgreSQL 使用 regexp_split_to_table
SELECT 
    id, 
    regexp_split_to_table(tags, ',') AS tag
FROM 
    products;

---

注意事项

1. 数据库差异：部分语法需根据 MySQL、PostgreSQL 等调整（如分页、字符串函数）。
2. 权限控制：部分操作需要管理员权限（如 pg_stat_activity 查询）。
3. 生产环境：慎用 DELETE/UPDATE，操作前先备份数据（如 CREATE TABLE backup AS SELECT * FROM table）。

---

---

二、实用且高频的 SQL 脚本工具

以下是一些实用且高频的SQL脚本工具推荐，涵盖性能优化、审计、分析等场景，结合功能特点与适用场景进行说明：

---

1. MySQLTuner.pl

功能：MySQL性能诊断工具，分析参数配置、存储引擎、日志文件等，提供优化建议。
适用场景：快速定位MySQL内存、连接数、缓存等配置问题。
特点：

• 支持MySQL/MariaDB/Percona Server，覆盖约300项指标。
• 报告标记关键问题（如[!!]），并给出“Recommendations”优化建议。
  使用示例：

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl  
./mysqltuner.pl --socket /var/lib/mysql/mysql.sock  

---

2. pt-query-digest

功能：Percona Toolkit中的慢查询日志分析工具，生成详细报告。
适用场景：分析MySQL慢查询，识别高负载SQL语句。
特点：

• 支持从日志、进程列表或TCP抓包分析查询。
• 提供执行时间分布、TOP SQL排名等统计信息。
  使用示例：

pt-query-digest /var/lib/mysql/slow.log > slow_report.log  
# 分析指定时间范围  
pt-query-digest --since '2025-04-28 00:00:00' --until '2025-04-29 00:00:00' slow.log  

---

3. Yearning

功能：SQL审计平台，规范工单提交与执行流程。
适用场景：团队协作中避免误操作，记录SQL执行历史。
特点：

• 支持工单审核、权限控制、自动生成回滚语句。
• 提供可视化界面，兼容99%的MySQL语法。
  部署：
• 支持自定义审核流程，适合中小团队使用。

---

4. QweryBuilder

功能：多数据库脚本管理工具，支持跨平台操作。
适用场景：管理多种数据库（如SQL Server、Oracle、MySQL）的脚本与架构。
特点：

• 提供差异对比、自动格式化、数据库搜索等功能。
• 集成WinMerge进行对象差异分析，支持自定义代码片段。
  适用性：适合需统一管理异构数据库的环境。

---

5. Percona Toolkit（含pt-variable-advisor）

功能：MySQL参数分析与优化建议。
适用场景：检查变量配置合理性（如缓冲池大小、线程配置）。
特点：

• 识别潜在问题并标记为WARN，如不合理的超时设置。
  使用示例：

pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock  

---

6. tuning-primer.sh

功能：MySQL性能调优脚本，提供针对性建议。
适用场景：快速获取内存、查询缓存等优化建议。
特点：

• 输出红色警告提示关键问题，如未优化的查询缓存配置。
  使用示例：

wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh  
./tuning-primer.sh  

---

总结

• 性能优化：优先使用MySQLTuner.pl和pt-query-digest快速定位问题。
• 团队协作：采用Yearning规范SQL执行流程，避免生产事故。
• 多数据库管理：QweryBuilder适合异构环境脚本统一管理。

更多工具细节可参考各项目文档或试用版本（如Yearning文档）。

---

📜文末寄语

• 🟠关注我，获取更多内容。
• 🟡技术动态、实战教程、问题解决方案等内容持续更新中。
• 🟢《全栈知识库》技术交流和分享社区，集结全栈各领域开发者，期待你的加入。
• 🔵​加入开发者的《专属社群》，分享交流，技术之路不再孤独，一起变强。
• 🟣点击下方名片获取更多内容🍭🍭🍭👇

---