DU9u3HJT

1package main
2
3import (
4	"database/sql"
5	"fmt"
6	"math"
7	"math/rand"
8	"strings"
9	"time"
10
11	_ "github.com/go-sql-driver/mysql"
12)
13
14const maxRows = 500000
15
16var createTable = `
17CREATE TABLE bench (
18	c1 BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
19	c2 CHAR(20),
20	c3 CHAR(20),
21	c4 CHAR(20),
22	c5 CHAR(20),
23	c6 CHAR(20),
24	c7 CHAR(20),
25	c8 CHAR(20),
26	c9 CHAR(20),
27	UNIQUE KEY idx_3(c1, c2, c3),
28	UNIQUE KEY idx_5(c1, c2, c3, c4, c5),
29	UNIQUE KEY idx_7(c1, c2, c3, c4, c5, c6, c7),
30	UNIQUE KEY idx_9(c1, c2, c3, c4, c5, c6, c7, c8, c9)
31)
32`
33
34func main() {
35	db, err := sql.Open("mysql", fmt.Sprintf(`root:@(127.0.0.1:4000)/test`))
36	if err != nil {
37		panic(err)
38	}
39
40	prepare(db)
41	for _, c := range []int{1, 3, 5, 7, 9} {
42		for in := 1; in <= 10; in++ {
43			bench(db, c, in*100)
44		}
45	}
46}
47
48func prepare(db *sql.DB) {
49	if _, err := db.Exec("DROP TABLE IF EXISTS `bench`"); err != nil {
50		panic(err)
51	}
52	if _, err := db.Exec(createTable); err != nil {
53		panic(err)
54	}
55
56	const batch = 10000
57	for i := 0; i < maxRows/batch; i++ {
58		var batchRows []string
59		for j := 0; j < batch; j++ {
60			rowId := i*batch + j + 1
61			cols := []string{fmt.Sprintf("%v", rowId)}
62			for r := 1; r < 9; r++ {
63				cols = append(cols, fmt.Sprintf("'%020d'", rowId+r))
64			}
65			batchRows = append(batchRows, "("+strings.Join(cols, ",")+")")
66		}
67		dml := fmt.Sprintf("INSERT INTO bench values %s", strings.Join(batchRows, ","))
68		if _, err := db.Exec(dml); err != nil {
69			panic(err)
70		}
71		println("batch", i, time.Now().String())
72	}
73}
74
75func bench(db *sql.DB, colCount, inCount int) {
76	var elapsed []time.Duration
77	const example = 50
78	var columns []string
79	for c := 0; c < colCount; c++ {
80		columns = append(columns, fmt.Sprintf("c%d", c+1))
81	}
82	s := strings.Join(columns, ",")
83
84	for i := 0; i < example; i++ {
85		var inExprs []string
86		for c := 0; c < inCount; c++ {
87			rowId := rand.Intn(maxRows) + 1
88			cols := []string{fmt.Sprintf("%v", rowId)}
89			for r := 1; r < colCount; r++ {
90				cols = append(cols, fmt.Sprintf("'%020d'", rowId+r))
91			}
92			inExprs = append(inExprs, "("+strings.Join(cols, ",")+")")
93		}
94		dml := fmt.Sprintf("SELECT %s FROM bench WHERE (%s) IN (%s)", s, s, strings.Join(inExprs, ","))
95		start := time.Now()
96		if _, err := db.Exec(dml); err != nil {
97			panic(err)
98		}
99		elapsed = append(elapsed, time.Since(start))
100	}
101	min, max, total := time.Duration(math.MaxInt64), time.Duration(0), time.Duration(0)
102	for _, d := range elapsed {
103		if d < min {
104			min = d
105		}
106		if d > max {
107			max = d
108		}
109		total += d
110	}
111	println("colCount", colCount, "inCount", inCount, "min", min.String(), "max", max.String(), "avg", (total / example).String())
112}