数据库ID生成器基准测试

在说明如何基准测试之前，我想聊聊我为什么要做这个事儿，话说最近做某后台的时候需要一个 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();

不过我没有直接拷贝此方案，因为看上去它至少有两个可以优化的地方：

1. 因为一张表只能有一个自增字段，所以一个表只能做一个独立的 id 生成器。
2. 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 的小工具，也算是有所得吧。