Benchmark mysql with simple read, update and insert json by JSON type and text type

jsonbench.go

package jsonbench

import (
	"database/sql"
	"fmt"
	"math/rand"
)

var (
	jsonPattern = "{\"value\": \"%s\"}"
)

type Data struct {
	ID       int32
	Document string
}

const letterBytes = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

func RandString(n int) string {
	b := make([]byte, n)
	for i := range b {
		b[i] = letterBytes[rand.Intn(len(letterBytes))]
	}
	return string(b)
}

func InsertDataText(db *sql.DB) {
	_, err := db.Exec("INSERT INTO data_with_text (document) VALUES (?)", fmt.Sprintf(jsonPattern, RandString(32)))
	if err != nil {
		panic("InsertDataText error: " + err.Error())
	}
}

func InsertDataJson(db *sql.DB) {
	_, err := db.Exec("INSERT INTO data_with_json (document) VALUES (?)", fmt.Sprintf(jsonPattern, RandString(32)))
	if err != nil {
		panic("InsertDataText error: " + err.Error())
	}
}

func UpdateDataText(db *sql.DB) {
	_, err := db.Exec("UPDATE data_with_text SET document = ? WHERE id=1", fmt.Sprintf(jsonPattern, RandString(32)))
	if err != nil {
		panic("UpdateDataText error: " + err.Error())
	}
}

func UpdateDataJson(db *sql.DB) {
	_, err := db.Exec("UPDATE data_with_json SET document = ? WHERE id=1", fmt.Sprintf(jsonPattern, RandString(32)))
	if err != nil {
		panic("UpdateDataJson error: " + err.Error())
	}
}

func ReadDataText(db *sql.DB) {
	_, err := db.Query("SELECT * FROM data_with_text LIMIT 10")
	if err != nil {
		panic("ReadDataText error: " + err.Error())
	}
}

func ReadDataJson(db *sql.DB) {
	_, err := db.Query("SELECT * FROM data_with_json LIMIT 10")
	if err != nil {
		panic("ReadDataJson error: " + err.Error())
	}
}

jsonbench_test.go

package jsonbench

import (
	"database/sql"
	"testing"

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

var db *sql.DB

func init() {
	var err error
	db, err = sql.Open("mysql", "root@/test_json")
	if err != nil {
		panic("failed to establish a database connection: " + err.Error())
	}
	db.SetMaxOpenConns(1000)
	db.SetMaxIdleConns(100)
}

func BenchmarkInsertDataText(b *testing.B) {
	for i := 0; i < b.N; i++ {
		InsertDataText(db)
	}
}

func BenchmarkInsertDataJson(b *testing.B) {
	for i := 0; i < b.N; i++ {
		InsertDataJson(db)
	}
}

func BenchmarkUpdateDataText(b *testing.B) {
	for i := 0; i < b.N; i++ {
		UpdateDataText(db)
	}
}

func BenchmarkUpdateDataJson(b *testing.B) {
	for i := 0; i < b.N; i++ {
		UpdateDataJson(db)
	}
}

func BenchmarkReadDataText(b *testing.B) {
	for i := 0; i < b.N; i++ {
		ReadDataText(db)
	}
}

func BenchmarkReadDataJson(b *testing.B) {
	for i := 0; i < b.N; i++ {
		ReadDataJson(db)
	}
}

result.txt

 go test -bench=. 
goos: darwin
goarch: amd64
pkg: mysql-json-benchmark
cpu: Intel(R) Core(TM) i7-9750H CPU @ 2.60GHz
BenchmarkInsertDataText-12          1281            938660 ns/op
BenchmarkInsertDataJson-12          1246            936762 ns/op
BenchmarkUpdateDataText-12          1083           1055003 ns/op
BenchmarkUpdateDataJson-12          1164           1025521 ns/op
BenchmarkReadDataText-12        ^Csignal: interrupt
FAIL    mysql-json-benchmark    54.450s