MySQLのインデックスと外部キーによるデータの追加・削除コストの違いを調べてみた

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句を追加しても単純な追加・削除の性能面での大きな影響はない