本日は個人的に気になっていた事を調べてみました。
それは**MySQLは定義の型サイズによって性能が異なるのか？**です
というのも会社でenum値のカラムの型にvarchar(255)が散見されるため、これを短くする事は有効なのか気になった次第です。
ちなみに、今回はデータ長ではなく定義の長さが性能に与える影響を調べました
ストレージサイズについてはデータ長と型サイズで変化します
https://dev.mysql.com/doc/refman/8.0/ja/storage-requirements.html
準備
まずは2つのテーブルを定義します
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万件のデータを投入
/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 #
ソースコード
/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)
}
}
indexのサイズ
indexのサイズはint型とvarchar型で異なりますが、定義長は影響しないようです。
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型は定義長によっての大きな差異は感じませんでした
実行結果
/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
ソースコード
/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)
}
}
}
selectの結合キーとしての性能
selectの結合キーとしての性能を計測しました。
こちらもint型が多少早く、varchar型は定義長によっての大きな差異は感じませんでした
実行結果
/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
ソースコード
/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)
}
}
}
updateの対象のキーとしての性能
updateの更新対象キーとしての性能を計測しました。
定義での差異はあまり出ませんでした
実行結果
/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 #
ソースコード
/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))
}
}
結論
という事で検索条件としては大きな影響はないので、積極的に定義を変更しなくても良いと思います。
ただ、追加する時にはint型にする事も一考してみると良いかなと思いました！