MySQL中实用且高频的SQL工具与脚本分享!

MySQL中实用且高频的SQL工具与脚本分享!

作者:岫珩
这篇文章主要介绍了一些实用且高频的 SQL 工具脚本代码示例,涵盖数据库维护、性能优化、数据操作等场景,适用于 MySQL、PostgreSQL 等主流数据库,希望对大家有所帮助。

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

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

1. 数据库维护类

1.1 数据库备份与恢复

1
2
3
4
5
6
7
8
9
-- 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 批量生成表结构文档

1
2
3
4
5
6
7
8
9
10
-- MySQL 查询所有表结构
SELECT
TABLE_NAME AS 表名,
COLUMN_NAME AS 字段名,
DATA_TYPE AS 数据类型,
COLUMN_COMMENT AS 描述
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = '数据库名';

2. 性能优化类

2.1 索引分析与优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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 检测长时间运行的查询

1
2
3
4
5
6
7
8
9
10
11
12
-- 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 批量插入/更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 批量插入(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 数据去重

1
2
3
4
5
6
7
-- 通用:删除重复记录(保留id最大的一条)
DELETE FROM orders
WHERE id NOT IN (
SELECT MAX(id)
FROM orders
GROUP BY order_no
);

3.3 分页查询优化

1
2
3
4
5
6
7
8
9
10
11
-- 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 用户权限检查

1
2
3
4
5
6
-- MySQL 查看用户权限
SHOW GRANTS FOR '用户名'@'主机';
-- PostgreSQL 列出所有用户角色
SELECT rolname, rolcreaterole, rolsuper
FROM pg_roles;

4.2 表空间监控

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 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 动态生成条件查询

1
2
3
4
5
6
7
8
9
-- 示例:根据参数动态筛选(伪代码逻辑)
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 数据清洗(拆分字段)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 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;

注意事项

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

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

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

1. MySQLTuner.pl

功能:MySQL性能诊断工具,分析参数配置、存储引擎、日志文件等,提供优化建议。

适用场景:快速定位MySQL内存、连接数、缓存等配置问题。

特点

支持MySQL/MariaDB/Percona Server,覆盖约300项指标。

报告标记关键问题(如[!!]),并给出“Recommendations”优化建议。

使用示例

1
2
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排名等统计信息。

使用示例

1
2
3
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,如不合理的超时设置。

使用示例

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

6. tuning-primer.sh

功能:MySQL性能调优脚本,提供针对性建议。

适用场景:快速获取内存、查询缓存等优化建议。

特点

输出红色警告提示关键问题,如未优化的查询缓存配置。

使用示例

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

总结

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

到此这篇关于MySQL中实用且高频的SQL工具与脚本分享的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。

快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​!

更多技巧, www.excelbook.cn

欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;

Excelbook.cn Excel技巧 SQL技巧 Python 学习!

你将获得:

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群,共享资源。

5、优惠的会员商品。

6、一次付费只需129元,即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称:Excelbook
2、本站永久网址:http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报。
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>