個(gè)人中心
退出
sqlbench：一個(gè)測(cè)量和比較 SQL 語句執(zhí)行效率的工具
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
sqlbench 安裝
使用案例
命令行參數(shù)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家介紹一個(gè)可以用于測(cè)量和比較不同 SQL 語句執(zhí)行時(shí)間的開源工具：sqlbench。如果覺得文章對(duì)你有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??
sqlbench 是一個(gè)使用 Go 語言開發(fā)的 SQL 性能測(cè)試工具，主要用于開發(fā)環(huán)境中不同 SQL 語句執(zhí)行效率（CPU）的測(cè)量和比較。sqlbench 遵循 MIT 開源協(xié)議，支持 Windows、Linux 以及 macOS 平臺(tái)，目前支持 PostgreSQL 數(shù)據(jù)庫。
sqlbench 安裝
sqlbench 提供了二進(jìn)制安裝包，可以在 GitHub 上直接下載。如果已經(jīng)安裝了 Go 1.13 或者更高版本，也可以通過源碼安裝或者升級(jí) sqlbench：
$ go get -u github.com/felixge/sqlbench
使用案例
假如我們需要比較三種不同查詢語句計(jì)算從 1 到 1000 累計(jì)總和的性能。第一種方法是使用窗口函數(shù)：
SELECT i, sum(i) OVER (ORDER BY i) AS sum
FROM generate_series(1, 1000) g(i);
第二種方法是使用遞歸通用表表達(dá)式（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 個(gè)文件：window.sql、recursive.sql、gauss.sql；或者使用 sqlbench 源碼 examples 目錄中提供的示例文件?，F(xiàn)在我們想要比較它們的性能差異，輸入以下命令分別運(yùn)行 1000 次上述 3 個(gè)查詢語句，返回執(zhí)行時(shí)間的統(tǒng)計(jì)、PostgreSQL 版本以及執(zhí)行的 SQL 語句等：
$ ./sqlbench -c postgres://tony:Pswd!1234@192.168.56.104:5432/hrdb -v -n 1000 ./examples/sum/*.sql
其中，-c 用于指定數(shù)據(jù)庫連接，tony:Pswd!1234 是用戶名和密碼，192.168.56.104 是 PostgreSQL 服務(wù)器地址，5432 是服務(wù)端口，hrdb 是連接的數(shù)據(jù)庫名；-v 用于打印詳細(xì)信息，包括所有的 SQL 查詢和 PostgreSQL 版本；-n 表示運(yùn)行查詢的次數(shù)；最后是要執(zhí)行的 SQL 腳本文件。
執(zhí)行以上命令會(huì)在終端不停刷新統(tǒng)計(jì)數(shù)據(jù)，最終返回以下結(jié)果：
| 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;
輸出結(jié)果返回了執(zhí)行次數(shù) n，最小執(zhí)行時(shí)間 min，最大執(zhí)行時(shí)間 max，平均執(zhí)行時(shí)間 mean，執(zhí)行時(shí)間標(biāo)準(zhǔn)差 stddev，執(zhí)行時(shí)間中位數(shù) median，百分之九十的執(zhí)行時(shí)間 p90 和百分之九十五的執(zhí)行時(shí)間 p95。最左邊的列是平均執(zhí)行時(shí)間最少的查詢，右邊的列顯示了和最快查詢的比較。從執(zhí)行的結(jié)果可以看出，高斯求和公式的性能最好，然后是窗口函數(shù)，遞歸通用表表達(dá)式的性能最差。
默認(rèn)情況下，sqlbench 為查詢語句加上了 EXPLAIN (ANALYZE, TIMING OFF) 前綴并且記錄總的執(zhí)行時(shí)間。如果增加 -m client 參數(shù)，可以基于 sqlbench 客戶端的時(shí)間統(tǒng)計(jì)不同 SQL 語句的性能，包括了網(wǎng)絡(luò)傳輸?shù)臅r(shí)間消耗：
$ ./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.
從輸出結(jié)果可以看出，客戶端統(tǒng)計(jì)的查詢性能整體上更慢，而且三種語句的性能差不多，說明網(wǎng)絡(luò)傳輸?shù)挠绊懕容^大。
另外，我們可以創(chuàng)建兩個(gè)特殊的文件：init.sql 和 destroy.sql，它們會(huì)在測(cè)試開始和結(jié)束時(shí)分別運(yùn)行一次，可以用于測(cè)試用例的初始化（創(chuàng)建表和索引、生成數(shù)據(jù)）和清除（刪除表）。sqlbench 源代碼中的 examples/unique/ 目錄提供了一個(gè)相關(guān)的示例。
命令行參數(shù)
使用 --help 參數(shù)可以查看 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 用于指定數(shù)據(jù)庫連接字符串，也可以使用 PostgreSQL 環(huán)境變量；
-i 可以用于指定一個(gè) CSV 格式的性能基準(zhǔn)文件，隨后的測(cè)試將會(huì)顯示和該基準(zhǔn)比較的結(jié)果；
-m 用于指定測(cè)量執(zhí)行時(shí)間的方法，explain（默認(rèn)值）表示使用 EXPLAIN ANALYZE 語句統(tǒng)計(jì)執(zhí)行時(shí)間，client 表示使用客戶端的發(fā)送/接收時(shí)間；
-n 表示循環(huán)執(zhí)行的次數(shù)。默認(rèn)值為 -1，表示一直運(yùn)行，直到手動(dòng)停止；
-o 表示將每次執(zhí)行的結(jié)果寫入指定的 CSV 文件；
-p 表示在統(tǒng)計(jì)中包含查詢計(jì)劃消耗的時(shí)間。對(duì)于 -m explain 模式，表中在統(tǒng)計(jì)結(jié)果中增加“Planning Time”部分；對(duì)于 -m client 模式，表示不使用預(yù)編譯語句；
-s 用于非交互的靜默模式，只在運(yùn)行結(jié)束后打印統(tǒng)計(jì)結(jié)果；
-t 表示運(yùn)行指定的時(shí)間后終止測(cè)試，默認(rèn)為 -1；
-v 用于打印詳細(xì)信息，包括所有的 SQL 查詢和 PostgreSQL 版本；
-version 用于打印 sqlbench 版本并退出。
以下是一些測(cè)試命令的示例，涉及的 SQL 腳本可以通過 sqlbench 源碼進(jìn)行下載：
# 運(yùn)行測(cè)試直到手動(dòng)輸入 ctrl+c，實(shí)時(shí)顯示輸出結(jié)果。
./sqlbench ./examples/sum/*.sql
# 基于客戶端時(shí)間運(yùn)行測(cè)試直到手動(dòng)輸入 ctrl+c。
./sqlbench -m client ./examples/sum/*.sql
# 持續(xù)運(yùn)行測(cè)試 3 秒鐘，結(jié)束后打印測(cè)試結(jié)果。
./sqlbench -t 3 -s ./examples/sum/*.sql
# 持續(xù)運(yùn)行測(cè)試 1000 次，結(jié)束后打印詳細(xì)結(jié)果。
./sqlbench -n 1000 -s -v ./examples/sum/*.sql
# 將 1000 次測(cè)試的詳細(xì)結(jié)果打印到一個(gè) CSV 文件中。
./sqlbench -n 1000 -o baseline.csv ./examples/sum/*.sql
# 將 1000 次測(cè)試的結(jié)果和基準(zhǔn)性能進(jìn)行比較。
./sqlbench -n 1000 -i baseline.csv ./examples/sum/*.sql
分類導(dǎo)航