个人中心
退出
sqlbench：一个测量和比较 SQL 语句执行效率的工具
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
sqlbench 安装
使用案例
命令行参数
大家好，我是只谈技术不剪发的 Tony 老师。今天给大家介绍一个可以用于测量和比较不同 SQL 语句执行时间的开源工具：sqlbench。如果觉得文章对你有用，欢迎评论📝、点赞👍、推荐🎁
sqlbench 是一个使用 Go 语言开发的 SQL 性能测试工具，主要用于开发环境中不同 SQL 语句执行效率（CPU）的测量和比较。sqlbench 遵循 MIT 开源协议，支持 Windows、Linux 以及 macOS 平台，目前支持 PostgreSQL 数据库。
sqlbench 安装
sqlbench 提供了二进制安装包，可以在 GitHub 上直接下载。如果已经安装了 Go 1.13 或者更高版本，也可以通过源码安装或者升级 sqlbench：
$ go get -u github.com/felixge/sqlbench
使用案例
假如我们需要比较三种不同查询语句计算从 1 到 1000 累计总和的性能。第一种方法是使用窗口函数：
SELECT i, sum(i) OVER (ORDER BY i) AS sum
FROM generate_series(1, 1000) g(i);
第二种方法是使用递归通用表表达式（WITH 子句）：
WITH RECURSIVE sums AS (
SELECT 1 AS i, 1 AS sum
UNION
SELECT i+1, sum+i FROM sums WHERE i <= 1000
)
SELECT * FROM sums;
第三种方法是使用高斯求和公式：
SELECT i, (i * (i + 1)) / 2 AS sum
FROM generate_series(1, 1000) g(i);
我们可以分别将它们保存为 3 个文件：window.sql、recursive.sql、gauss.sql；或者使用 sqlbench 源码 examples 目录中提供的示例文件。现在我们想要比较它们的性能差异，输入以下命令分别运行 1000 次上述 3 个查询语句，返回执行时间的统计、PostgreSQL 版本以及执行的 SQL 语句等：
$ ./sqlbench -c postgres://tony:Pswd!1234@192.168.56.104:5432/hrdb -v -n 1000 ./examples/sum/*.sql
其中，-c 用于指定数据库连接，tony:Pswd!1234 是用户名和密码，192.168.56.104 是 PostgreSQL 服务器地址，5432 是服务端口，hrdb 是连接的数据库名；-v 用于打印详细信息，包括所有的 SQL 查询和 PostgreSQL 版本；-n 表示运行查询的次数；最后是要执行的 SQL 脚本文件。
执行以上命令会在终端不停刷新统计数据，最终返回以下结果：
| gauss | window | recursive
---------+-------+--------------+---------------
n | 1000 | 1000 | 1000
min | 0.18 | 0.64 (3.59x) | 0.95 (5.31x)
max | 0.51 | 1.80 (3.51x) | 3.36 (6.53x)
mean | 0.22 | 0.81 (3.66x) | 1.19 (5.40x)
stddev | 0.05 | 0.18 (3.97x) | 0.26 (5.62x)
median | 0.21 | 0.77 (3.64x) | 1.13 (5.35x)
p90 | 0.24 | 0.85 (3.59x) | 1.29 (5.48x)
p95 | 0.26 | 1.09 (4.23x) | 1.37 (5.34x)
Stopping after 1000 iterations as requested.
postgres version: PostgreSQL 13.0, compiled by Visual C++ build 1914, 64-bit
sqlbench -c postgres://tony:Pswd!1234@192.168.56.104:5432/hrdb -v -n 1000 ./examples/sum/gauss.sql ./examples/sum/recursive.sql ./examples/sum/window.sql
==> ./examples/sum/gauss.sql <==
SELECT i, (i * (i + 1)) / 2 AS sum
FROM generate_series(1, 1000) g(i);
==> ./examples/sum/window.sql <==
SELECT i, sum(i) OVER (ORDER BY i) AS sum
FROM generate_series(1, 1000) g(i);
==> ./examples/sum/recursive.sql <==
WITH RECURSIVE sums AS (
SELECT 1 AS i, 1 AS sum
UNION
SELECT i+1, sum+i FROM sums WHERE i <= 1000
)
SELECT * FROM sums;
输出结果返回了执行次数 n，最小执行时间 min，最大执行时间 max，平均执行时间 mean，执行时间标准差 stddev，执行时间中位数 median，百分之九十的执行时间 p90 和百分之九十五的执行时间 p95。最左边的列是平均执行时间最少的查询，右边的列显示了和最快查询的比较。从执行的结果可以看出，高斯求和公式的性能最好，然后是窗口函数，递归通用表表达式的性能最差。
默认情况下，sqlbench 为查询语句加上了 EXPLAIN (ANALYZE, TIMING OFF) 前缀并且记录总的执行时间。如果增加 -m client 参数，可以基于 sqlbench 客户端的时间统计不同 SQL 语句的性能，包括了网络传输的时间消耗：
$ ./sqlbench -c postgres://tony:Pswd!1234@192.168.56.104:5432/hrdb -m client -n 1000 ./examples/sum/*.sql
| gauss | window | recursive
---------+-------+--------------+---------------
n | 1000 | 1000 | 1000
min | 1.37 | 1.60 (1.17x) | 1.75 (1.28x)
max | 4.57 | 4.44 (0.97x) | 4.94 (1.08x)
mean | 1.91 | 2.18 (1.14x) | 2.34 (1.23x)
stddev | 0.37 | 0.38 (1.03x) | 0.50 (1.34x)
median | 1.83 | 2.09 (1.14x) | 2.21 (1.20x)
p90 | 2.22 | 2.56 (1.15x) | 2.73 (1.23x)
p95 | 2.56 | 3.04 (1.19x) | 3.58 (1.40x)
Stopping after 1000 iterations as requested.
从输出结果可以看出，客户端统计的查询性能整体上更慢，而且三种语句的性能差不多，说明网络传输的影响比较大。
另外，我们可以创建两个特殊的文件：init.sql 和 destroy.sql，它们会在测试开始和结束时分别运行一次，可以用于测试用例的初始化（创建表和索引、生成数据）和清除（删除表）。sqlbench 源代码中的 examples/unique/ 目录提供了一个相关的示例。
命令行参数
使用 --help 参数可以查看 sqlbench 工具的帮助信息：
$ ./sqlbench --help
Usage of sqlbench:
-c string
Connection URL or DSN for connecting to PostgreSQL as understood by pgx [1].
E.g.: postgres://user:secret@localhost:5432/my_db?sslmode=disable
Alternatively you can use standard PostgreSQL environment variables [2] such as
PGHOST, PGPORT, PGPASSWORD, ... .
[1] https://pkg.go.dev/github.com/jackc/pgx/v4/stdlib?tab=doc
[2] https://www.postgresql.org/docs/current/libpq-envars.html
(default "postgres://")
-i string
Input path for CSV file with baseline measurements.
-m string
Method for measuring the query time. One of: "client", "explain" (default "explain")
-n int
Terminate after the given number of iterations. (default -1)
-o string
Output path for writing individual measurements in CSV format.
-p Include the query planning time. For -m explain this is accomplished by adding
the "Planning Time" to the measurement. For -m client this is done by not using
prepared statements.
-s Silent mode for non-interactive use, only prints stats once after terminating.
-t float
Terminate after the given number of seconds. (default -1)
-v Verbose output. Print the content of all SQL queries, as well as the
PostgreSQL version.
-version
Print version and exit.
其中，
-c 用于指定数据库连接字符串，也可以使用 PostgreSQL 环境变量；
-i 可以用于指定一个 CSV 格式的性能基准文件，随后的测试将会显示和该基准比较的结果；
-m 用于指定测量执行时间的方法，explain（默认值）表示使用 EXPLAIN ANALYZE 语句统计执行时间，client 表示使用客户端的发送/接收时间；
-n 表示循环执行的次数。默认值为 -1，表示一直运行，直到手动停止；
-o 表示将每次执行的结果写入指定的 CSV 文件；
-p 表示在统计中包含查询计划消耗的时间。对于 -m explain 模式，表中在统计结果中增加“Planning Time”部分；对于 -m client 模式，表示不使用预编译语句；
-s 用于非交互的静默模式，只在运行结束后打印统计结果；
-t 表示运行指定的时间后终止测试，默认为 -1；
-v 用于打印详细信息，包括所有的 SQL 查询和 PostgreSQL 版本；
-version 用于打印 sqlbench 版本并退出。
以下是一些测试命令的示例，涉及的 SQL 脚本可以通过 sqlbench 源码进行下载：
# 运行测试直到手动输入 ctrl+c，实时显示输出结果。
./sqlbench ./examples/sum/*.sql
# 基于客户端时间运行测试直到手动输入 ctrl+c。
./sqlbench -m client ./examples/sum/*.sql
# 持续运行测试 3 秒钟，结束后打印测试结果。
./sqlbench -t 3 -s ./examples/sum/*.sql
# 持续运行测试 1000 次，结束后打印详细结果。
./sqlbench -n 1000 -s -v ./examples/sum/*.sql
# 将 1000 次测试的详细结果打印到一个 CSV 文件中。
./sqlbench -n 1000 -o baseline.csv ./examples/sum/*.sql
# 将 1000 次测试的结果和基准性能进行比较。
./sqlbench -n 1000 -i baseline.csv ./examples/sum/*.sql
分类导航