bad-indexlookup.go

package main

import (
	"database/sql"
	"flag"
	"fmt"
	"sync"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var (
	dsn  = flag.String("dsn", "root:@tcp(127.0.0.1:4000)/test", "connect dsn")
	mode = flag.String("mode", "prepare", "running mode, prepare or run or run-opt")
)

func init() {
	flag.Parse()
}

const CONC = 250
const TOTAL = 1000000
const BATCH = 100
const ROUNT_TIME = 100

func main() {
	switch *mode {
	case "prepare":
		prepare()
	case "run", "run-opt":
		run(*mode)
	}
}

func prepare() {
	db, err := sql.Open("mysql", *dsn)
	db.SetMaxOpenConns(CONC)
	MustNil(err)
	MustExec(db, "drop table if exists t")
	MustExec(db, "create table t(id int primary key, v int, c1 varchar(40), index i(c1))")
	start := time.Now()
	var wg sync.WaitGroup
	for i := 0; i < CONC; i++ {
		wg.Add(1)
		go func(i int) {
			txn := MustBegin(db)
			for j := 0; j < TOTAL/CONC; j++ {
				if (j+1)%BATCH == 0 {
					MustCommit(txn)
					txn = MustBegin(db)
				}
				num := j*CONC + i
				c1 := "no"
				if num%1000 == 0 {
					c1 = "yes"
				}
				MustExecTxn(txn, fmt.Sprintf("insert into t values(%d, %d, \"%s\")", num, num, c1))
			}
			MustCommit(txn)
			wg.Done()
		}(i)
	}
	wg.Wait()
	fmt.Println("cost:", time.Since(start))
	MustExec(db, "split table t between (0) and (1000000) regions 1000")
	// run `explain analyze select * from t where c1 = "yes"` with different `tidb_distsql_scan_concurrency`
}

func run(mode string) {
	db, err := sql.Open("mysql", *dsn)
	db.SetMaxOpenConns(1)
	MustNil(err)
	if mode == "run-opt" {
		round(db, 0)
		return
	}
	cases := []int{1, 2, 4, 8, 16, 32, 64, 128, 256}
	for _, c := range cases {
		round(db, c)
	}
}

func round(db *sql.DB, conc int) {
	if conc > 0 {
		MustExec(db, fmt.Sprintf("set session tidb_distsql_scan_concurrency = %d", conc))
	}
	start := time.Now()
	for i := 0; i < ROUNT_TIME; i++ {
		MustExec(db, "select * from t where c1 = \"yes\"")
	}
	elapsed := time.Since(start)
	fmt.Printf("conc: %d, avg: %s\n", conc, elapsed/ROUNT_TIME)
}

func MustNil(i interface{}) {
	if i != nil {
		panic(fmt.Sprintf("%v not nil", i))
	}
}

func MustExec(db *sql.DB, sql string) {
	_, err := db.Exec(sql)
	MustNil(err)
}

func MustExecTxn(txn *sql.Tx, sql string) {
	_, err := txn.Exec(sql)
	MustNil(err)
}

func MustBegin(db *sql.DB) *sql.Tx {
	txn, err := db.Begin()
	MustNil(err)
	return txn
}

func MustCommit(txn *sql.Tx) {
	MustNil(txn.Commit())
}