MySQLで個人的に気になった事を調べたメモです
「インデックス」と「外部キー」を貼った場合の「insert/delete」性能の違いはどんなものか簡単に調べてみました。
また、外部キーに「ON DELETE CASCADE句」を定義した場合の比較もしました。
ちなみにON DELETE CASCADE句は親テーブルのデータを削除すると子テーブルのデータも一緒に削除する外部キーの機能です。
https://dev.mysql.com/doc/refman/8.0/ja/create-table-foreign-keys.html
計測用コード
指定のテーブルに「10万件のデータをinsertするスクリプト」と「insertしたデータをdeleteするスクリプト」を用意
ソースコード
insertスクリプト
package main
import (
"database/sql"
"flag"
"fmt"
"strconv"
_ "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?autocommit=0")
if err != nil {
panic(err)
}
defer db.Close()
tx, err := db.Begin()
if err != nil {
panic(err)
}
ins1, _ := db.Prepare(fmt.Sprintf("INSERT INTO %s_oya(id, name) VALUES(?,?)", *f))
stmt1 := tx.Stmt(ins1)
ins2, _ := db.Prepare(fmt.Sprintf("INSERT INTO %s_ko(%s_oya_id, name) VALUES(?,?)", *f, *f))
stmt2 := tx.Stmt(ins2)
for i := 1; i <= 10000; i++ {
_, err := stmt1.Exec(i, fmt.Sprintf("name:%s", strconv.Itoa(i)))
if err != nil {
panic(err)
}
for j := 1; j <= 10; j++ {
_, err := stmt2.Exec(i, fmt.Sprintf("name:%s-%s", strconv.Itoa(i), strconv.Itoa(j)))
if err != nil {
panic(err)
}
}
}
tx.Commit()
}
deleteスクリプト
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 {
panic(err)
}
defer db.Close()
tx, err := db.Begin()
if err != nil {
panic(err)
}
del1, err := db.Prepare(fmt.Sprintf("DELETE FROM %s_ko", *f))
if err != nil {
panic(err)
}
stmt1 := tx.Stmt(del1)
stmt1.Exec()
del2, err := db.Prepare(fmt.Sprintf("DELETE FROM %s_oya", *f))
if err != nil {
panic(err)
}
stmt2 := tx.Stmt(del2)
stmt2.Exec()
tx.Commit()
}
準備
データ格納先のテーブルを用意
indexが存在しないテーブル
CREATE TABLE `sample1_oya` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sample1_ko` (
`sample1_oya_id` int NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
indexのみ定義したテーブル
CREATE TABLE `sample2_oya` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sample2_ko` (
`sample2_oya_id` int NOT NULL,
`name` varchar(255) NOT NULL,
INDEX `idx_sample2_oya_id`(`sample2_oya_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
外部キーを定義したテーブル
CREATE TABLE `sample3_oya` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sample3_ko` (
`sample3_oya_id` int NOT NULL,
`name` varchar(255) NOT NULL,
CONSTRAINT `fk_sample3_oya_id` FOREIGN KEY (`sample3_oya_id`) REFERENCES `sample3_oya` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
外部キーに「ON DELETE CASCADE」句を定義したテーブル
CREATE TABLE `sample4_oya` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sample4_ko` (
`sample4_oya_id` int NOT NULL,
`name` varchar(255) NOT NULL,
CONSTRAINT `fk_sample4_oya_id` FOREIGN KEY (`sample4_oya_id`) REFERENCES `sample4_oya` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
計測
データを投入
/app/mysql # time go run insert.go --table=sample1
table:sample1
real 0m 55.45s
user 0m 5.32s
sys 0m 15.85s
/app/mysql # time go run insert.go --table=sample2
table:sample2
real 0m 58.96s
user 0m 5.93s
sys 0m 15.86s
/app/mysql # time go run insert.go --table=sample3
table:sample3
real 1m 3.21s
user 0m 5.91s
sys 0m 15.64s
/app/mysql # time go run insert.go --table=sample4
table:sample4
real 1m 4.17s
user 0m 5.92s
sys 0m 15.74s
データの削除
/app/mysql # time go run delete.go --table=sample1
table:sample1
real 0m 2.49s
user 0m 0.18s
sys 0m 0.13s
/app/mysql # time go run delete.go --table=sample2
table:sample2
real 0m 3.59s
user 0m 0.19s
sys 0m 0.14s
/app/mysql # time go run delete.go --table=sample3
table:sample3
real 0m 5.70s
user 0m 0.18s
sys 0m 0.14s
/app/mysql # time go run delete.go --table=sample4
table:sample4
real 0m 5.61s
user 0m 0.19s
sys 0m 0.13s
まとめ
今回試した結果を見ると以下の雰囲気を感じました。
- インデックスより外部キーの方がデータの追加・削除コストは高い
- 外部キーにON DELETE CASCADE句を追加しても単純な追加・削除の性能面での大きな影響はない