MySQL中实用且高频的SQL工具与脚本分享!
MySQL中实用且高频的SQL工具与脚本分享!
一、实用且高频的 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.pl
和pt-query-digest
快速定位问题。 - 团队协作:采用
Yearning
规范SQL执行流程,避免生产事故。 - 多数据库管理:
QweryBuilder
适合异构环境脚本统一管理。
到此这篇关于MySQL中实用且高频的SQL工具与脚本分享的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。
快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利!
更多技巧, www.excelbook.cn
欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群,共享资源。
5、优惠的会员商品。
6、一次付费只需129元,即可下载本站文章涉及的文件和软件。