MySQLは定義の型サイズによって性能が異なるのか

本日は個人的に気になっていた事を調べてみました。
それは**MySQLは定義の型サイズによって性能が異なるのか？**です
というのも会社でenum値のカラムの型にvarchar(255)が散見されるため、これを短くする事は有効なのか気になった次第です。

ちなみに、今回はデータ長ではなく定義の長さが性能に与える影響を調べました
ストレージサイズについてはデータ長と型サイズで変化します
https://dev.mysql.com/doc/refman/8.0/ja/storage-requirements.html

## 準備

まずは2つのテーブルを定義します

```sql
 CREATE TABLE `sample1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `icol` int NOT NULL,
  `v16col` varchar(16) NOT NULL,
  `v64col` varchar(64) NOT NULL,
  `v255col` varchar(255) NOT NULL,
  `v256col` varchar(256) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_icol` (`icol`),
  KEY `idx_v16col` (`v16col`),
  KEY `idx_v64col` (`v64col`),
  KEY `idx_v255col` (`v255col`),
  KEY `idx_v256col` (`v256col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 CREATE TABLE `sample2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `icol` int NOT NULL,
  `v16col` varchar(16) NOT NULL,
  `v64col` varchar(64) NOT NULL,
  `v255col` varchar(255) NOT NULL,
  `v256col` varchar(256) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_icol` (`icol`),
  KEY `idx_v16col` (`v16col`),
  KEY `idx_v64col` (`v64col`),
  KEY `idx_v255col` (`v255col`),
  KEY `idx_v256col` (`v256col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

初期データとして10万件のデータを投入

```bash
/home/go/app/mysql # go run insert.go --table=sample1
table:sample1
/home/go/app/mysql # go run insert.go --table=sample2
table:sample2
/home/go/app/mysql # 
```


<details><summary>ソースコード</summary><div>

```golang
/home/go/app/mysql # cat insert.go 
package main

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

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

func main() {
	f := flag.String("table", "sample1", "target table")
	flag.Parse()
	fmt.Printf("table:%s\n", *f)

	db, err := sql.Open("mysql", "root:mypass@(go.mysql.local:3307)/test")
	if err != nil {
		fmt.Println("db error.")
		panic(err)
	}
	defer db.Close()
	ins, err := db.Prepare(fmt.Sprintf("INSERT INTO %s(icol,v16col,v64col,v255col,v256col) VALUES(?,?,?,?,?)", *f))
	if err != nil {
		fmt.Println(err)
		panic(err)
	}
	for i := 1; i < 100000; i++ {
		ins.Exec(i, i, i, i, i)
	}
}
```

</div></details>

## indexのサイズ

indexのサイズはint型とvarchar型で異なりますが、定義長は影響しないようです。

```sql
SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb  FROM innodb_index_stats WHERE stat_name = 'size' AND index_name like "idx_%";
+------------+-------------+------------+
| stat_value | index_name  | size_mb    |
+------------+-------------+------------+
|         97 | idx_icol    | 1.51562500 |
|        225 | idx_v16col  | 3.51562500 |
|        225 | idx_v255col | 3.51562500 |
|        225 | idx_v256col | 3.51562500 |
|        225 | idx_v64col  | 3.51562500 |
|         97 | idx_icol    | 1.51562500 |
|        225 | idx_v16col  | 3.51562500 |
|        225 | idx_v255col | 3.51562500 |
|        225 | idx_v256col | 3.51562500 |
|        225 | idx_v64col  | 3.51562500 |
+------------+-------------+------------+
10 rows in set (0.03 sec)
```

## selectのWHEREキーとしての性能

selectのWHEREキーとしての性能を計測しました。
実行する度に振れ幅はありますがint型が多少早く、varchar型は定義長によっての大きな差異は感じませんでした

**実行結果**

```bash
/home/go/app/mysql # time go run select.go --col=icol
col:icol
real	0m 6.23s
user	0m 0.87s
sys	0m 1.97s
/home/go/app/mysql # time go run select.go --col=v16col
col:v16col
real	0m 6.53s
user	0m 0.75s
sys	0m 2.04s
/home/go/app/mysql # time go run select.go --col=v64col
col:v64col
real	0m 6.55s
user	0m 0.81s
sys	0m 1.98s
/home/go/app/mysql # time go run select.go --col=v255col
col:v255col
real	0m 6.57s
user	0m 0.76s
sys	0m 2.04s
/home/go/app/mysql # time go run select.go --col=v256col
col:v256col
real	0m 6.58s
user	0m 0.85s
sys	0m 1.92s
```

<details><summary>ソースコード</summary><div>

```golang
/home/go/app/mysql # cat select.go 
package main

import (
	"database/sql"
	"flag"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"strconv"
)

type Sample struct {
	Id      int
	Icol    int
	V16col  string
	V64col  string
	V255col string
	V256col string
}

func main() {
	f := flag.String("col", "icol", "col")
	flag.Parse()
	fmt.Printf("col:%s\n", *f)

	db, err := sql.Open("mysql", "root:mypass@(go.mysql.local:3307)/test")
	if err != nil {
		fmt.Println("db error.")
		panic(err)
	}

	defer db.Close()
	for i := 1; i < 10000; i++ {
		var sample = Sample{}
		err = db.QueryRow(fmt.Sprintf("SELECT * FROM sample1 WHERE %s = ?", *f), strconv.Itoa(i)).Scan(&sample.Id, &sample.Icol, &sample.V16col, &sample.V64col, &sample.V255col, &sample.V256col)
		if err != nil {
			fmt.Println("Query error.")
			panic(err)
		}
	}
}
```

</div></details>

## selectの結合キーとしての性能

selectの結合キーとしての性能を計測しました。
こちらもint型が多少早く、varchar型は定義長によっての大きな差異は感じませんでした

**実行結果**

```bash
/home/go/app/mysql # time go run select-join.go --col=icol
col:icol
real	0m 7.69s
user	0m 0.85s
sys	0m 2.00s
/home/go/app/mysql # time go run select-join.go --col=v16col
col:v16col
real	0m 8.15s
user	0m 0.82s
sys	0m 2.02s
/home/go/app/mysql # time go run select-join.go --col=v64col
col:v64col
real	0m 8.20s
user	0m 0.78s
sys	0m 2.06s
/home/go/app/mysql # time go run select-join.go --col=v255col
col:v255col
real	0m 8.34s
user	0m 0.81s
sys	0m 2.02s
/home/go/app/mysql # time go run select-join.go --col=v256col
col:v256col
real	0m 8.32s
user	0m 0.79s
sys	0m 1.99s
```

<details><summary>ソースコード</summary><div>

```golang
/home/go/app/mysql # cat select-join.go 
package main

import (
	"database/sql"
	"flag"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"strconv"
)

type Sample struct {
	Id      int
	Icol    int
	V16col  string
	V64col  string
	V255col string
	V256col string
}

func main() {
	f := flag.String("col", "icol", "col")
	flag.Parse()
	fmt.Printf("col:%s\n", *f)

	db, err := sql.Open("mysql", "root:mypass@(go.mysql.local:3307)/test")
	if err != nil {
		fmt.Println("db error.")
		panic(err)
	}

	defer db.Close()
	for i := 1; i < 10000; i++ {
		var sample = Sample{}
		err = db.QueryRow(fmt.Sprintf("SELECT s2.* FROM sample1 s1 INNER JOIN sample2 s2 ON s1.%s = s2.%s WHERE s1.%s = ?",
			*f, *f, *f), strconv.Itoa(i)).Scan(&sample.Id, &sample.Icol, &sample.V16col, &sample.V64col, &sample.V255col, &sample.V256col)
		if err != nil {
			fmt.Println("Query error.")
			panic(err)
		}
	}
}
```

</div></details>

## updateの対象のキーとしての性能

updateの更新対象キーとしての性能を計測しました。
定義での差異はあまり出ませんでした

**実行結果**

```bash
/home/go/app/mysql # time go run update.go --col=icol
col:icol
real	5m 5.44s
user	0m 4.47s
sys	0m 17.68s
/home/go/app/mysql # time go run update.go --col=v16col
col:v16col
real	5m 9.94s
user	0m 4.83s
sys	0m 17.40s
/home/go/app/mysql # time go run update.go --col=v64col
col:v64col
real	5m 11.56s
user	0m 4.59s
sys	0m 17.44s
/home/go/app/mysql # time go run update.go --col=v255col
col:v255col
real	5m 9.64s
user	0m 4.54s
sys	0m 17.29s
/home/go/app/mysql # time go run update.go --col=v256col
col:v256col
real	5m 20.83s
user	0m 4.77s
sys	0m 17.86s
/home/go/app/mysql # 
```


<details><summary>ソースコード</summary><div>

```golang
/home/go/app/mysql # cat update.go 
package main

import (
	"database/sql"
	"flag"
	"fmt"
	"strconv"

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

func main() {
	f := flag.String("col", "icol", "col")
	flag.Parse()
	fmt.Printf("col:%s\n", *f)

	db, err := sql.Open("mysql", "root:mypass@(go.mysql.local:3307)/test")
	if err != nil {
		fmt.Println("db error.")
		panic(err)
	}

	defer db.Close()
	upd, err := db.Prepare(fmt.Sprintf("UPDATE sample1 SET %s = ? WHERE %s = ?", *f, *f))
	if err != nil {
		fmt.Println(err)
		panic(err)
	}
	for i := 1; i < 100000; i++ {
		upd.Exec(i+100000, strconv.Itoa(i))
		upd.Exec(i, strconv.Itoa(i+100000))
	}
}
```

</div></details>


## 結論

という事で検索条件としては大きな影響はないので、積極的に定義を変更しなくても良いと思います。
ただ、追加する時にはint型にする事も一考してみると良いかなと思いました！