id生成器 mysql优化_数据库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();

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

因为一张表只能有一个自增字段，所以一个表只能做一个独立的 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

}

}

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 = ":@(:)/"

下面让我们看看原始方案和我们改进的方案有什么不同：

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 的小工具，也算是有所得吧。