在說明如何基準測試之前，我想聊聊我為什麼要做這個事兒，話說最近做某背景的時候需要一個 ID 生成器，我不太想用 snowflake 等複雜的解決方案，也不太想用 redis 來實作，因為我手頭隻有 mysql，是以我琢磨着就用 mysql 實作吧。
實際上當初 flickr 就是這麼幹的，利用 LAST_INSERT_ID 傳回最新插入的 id：
mysql> CREATE TABLE `Tickets64` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`stub` char(1) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM;
mysql> REPLACE INTO Tickets64 (stub) VALUES ('a');
mysql> SELECT LAST_INSERT_ID(); 複制
不過我沒有直接拷貝此方案，因為看上去它至少有兩個可以優化的地方：
- 因為一張表隻能有一個自增字段，是以一個表隻能做一個獨立的 id 生成器。
- REPLACE 實際上相當于先 DELETE 再 INSERT，也就是兩步操作。
按照文檔描述 LAST_INSERT_ID 支援表達式參數，如此說來我們可以通過它來自行維護 id，進而去掉對 auto_increment 的依賴，進而不再需要 REPLACE，直接 UPDATE 即可：
mysql> CREATE TABLE `seq` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
mysql> INSERT INTO seq (id, name) VALUES (0, 'global');
mysql> INSERT INTO seq (id, name) VALUES (0, 'another');
mysql> UPDATE seq SET id = LAST_INSERT_ID(id+1) WHERE name = 'global';
mysql> SELECT LAST_INSERT_ID(); 複制
确定了解決方案，我琢磨着得 Benchmark 看看這條 SQL 語句的性能怎麼樣，其實 MySQL 本身有一個 Benchmark 函數，但是它隻能用來測試 SELECT 這樣的讀操作 SQL，不能用來測試 UPDATE，REPLACE 這樣的寫操作 SQL，于是我到處找 SQL 性能測試工具，結果發現雖然有 mysqlslap、tpcc-mysql 之類的重量級測試工具，但是卻不符合我的需求：我隻想要一個能壓力測試一條 SQL 的小工具！
既然沒有現成的，那麼我們不妨自己實作一個：
package main
import (
"database/sql"
"fmt"
"log"
"os"
"sync"
"time"
"github.com/spf13/cobra"
"github.com/spf13/viper"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
var number, concurrency int
var cmd = &cobra.Command{
Use: "benchmark sql",
Short: "a sql benchmark tool",
Args: func(cmd *cobra.Command, args []string) error {
if len(args) != 1 {
cmd.Usage()
os.Exit(1)
}
return nil
},
Run: func(cmd *cobra.Command, args []string) {
b := benchmark{
sql: args[0],
number: number,
concurrency: concurrency,
}
b.run()
},
}
func init() {
cobra.OnInitialize(config)
cmd.Flags().IntVarP(&number, "number", "n", 100, "number")
cmd.Flags().IntVarP(&concurrency, "concurrency", "c", 1, "concurrency")
cmd.Flags().SortFlags = false
}
func config() {
viper.AddConfigPath(".")
viper.SetConfigName("db")
viper.SetConfigType("toml")
err := viper.ReadInConfig()
if err != nil {
log.Fatal(err)
}
driver := viper.GetString("driver")
dsn := viper.GetString("dsn")
db, err = sql.Open(driver, dsn)
if err != nil {
log.Fatal(err)
}
}
func main() {
if err := cmd.Execute(); err != nil {
log.Fatal(err)
}
}
type benchmark struct {
sql string
number int
concurrency int
duration chan time.Duration
start time.Time
end time.Time
}
func (b *benchmark) run() {
b.duration = make(chan time.Duration, b.number)
b.start = time.Now()
b.runWorkers()
b.end = time.Now()
b.report()
}
func (b *benchmark) runWorkers() {
var wg sync.WaitGroup
wg.Add(b.concurrency)
for i := 0; i < b.concurrency; i++ {
go func() {
defer wg.Done()
b.runWorker(b.number / b.concurrency)
}()
}
wg.Wait()
close(b.duration)
}
func (b *benchmark) runWorker(num int) {
for i := 0; i < num; i++ {
start := time.Now()
b.request()
end := time.Now()
b.duration <- end.Sub(start)
}
}
func (b *benchmark) request() {
if _, err := db.Exec(b.sql); err != nil {
log.Fatal(err)
}
}
func (b *benchmark) report() {
sum := 0.0
num := float64(len(b.duration))
for duration := range b.duration {
sum += duration.Seconds()
}
qps := int(num / b.end.Sub(b.start).Seconds())
tpq := sum / num * 1000
fmt.Printf("qps: %d [#/sec]\n", qps)
fmt.Printf("tpq: %.3f [ms]\n", tpq)
} 複制
代碼是用 Golang 寫的，運作前記得在指令同級目錄編輯好資料庫配置檔案 db.toml：
driver = "mysql"
dsn = "<username>:<passwrod>@<protocol>(<host>:<port>)/<database>" 複制
下面讓我們看看原始方案和我們改進的方案有什麼不同：
shell> /path/to/benchmark -n 100000 -c 10 "
REPLACE INTO Tickets64 (stub) VALUES ('a')
"
shell> /path/to/benchmark -n 100000 -c 10 "
UPDATE seq SET id = LAST_INSERT_ID(id+1) WHERE name = 'global'
" 複制
結果令人大吃一驚，所謂的改進方案比原始方案慢得多！仔細對比兩個方案的表結構，發現原始方案資料引擎使用的是 MyISAM，而改進方案使用的是 InnoDB，于是我把資料引擎統一改成 MyISAM，重新測試，性能終于上來了，不過兩者性能差異并不大，甚至 REPLACE 的性能還要比 UPDATE 好一點，具體原因我沒有深究，就留給讀者去探索吧。
雖然有一些小問題懸而未決，好在搞出一個壓測 SQL 的小工具，也算是有所得吧。