MySQL のインデックスの種類 をおさらいしよう！ 2024/03/09 PHPerKaigi 2024 @okashoi

インデックス、 作成してますか？

よくみるインデックス CREATE TABLE `users` ( `id` VARCHAR(20) NOT NULL PRIMARY KEY, `created_at` BIGINT UNSIGNED NOT NULL, INDEX `users_created_at` (`created_at`) ); CREATE INDEX `users_created_at` ON `users` (`created_at`); あるいは

ふつう、本の中で特定の言葉の出現位置を探す場合 インデックスのイメージ

ふつう、本の中で特定の言葉の出現位置を探す場合 インデックスのイメージ

インデックス = 索引 インデックスのイメージ

インデックス = 索引 インデックスのイメージ

インデックス = 索引 → 少ない時間で出現位置がわかる インデックスのイメージ

実はインデックスには いくつか種類がある

CREATE INDEX 構文 https://dev.mysql.com/doc/refman/8.0/ja/create-index.html

CREATE INDEX 構文 https://dev.mysql.com/doc/refman/8.0/ja/create-index.html

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

• いわゆる「インデックス」と言ったときに指すもの • UNIQUE は重複を許可しない • B+木というデータ構造 • 文字列は完全一致か前方一致（LIKE '〇〇%'）のみ • 本の索引で 2 文字目から探せないのと同じ INDEX, UNIQUE

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

• 全文検索用のインデックス • 自然言語検索とブール検索の 2 つのモード • 転置インデックスという形でデータを持つ • 日本語ではパーサーの指定が必要 • 単語間がスペースで区切られていないため • そこまで MySQL にやらせるか、は要検討か FULLTEXT https://dev.mysql.com/doc/refman/8.0/ja/fulltext-search.html

FULLTEXT 通常のインデックスが効かない検索を高速に行える SELECT * FROM `posts` WHERE MATCH (`title`) AGAINST('PHP' IN NATURAL LANGUAGE MODE); SELECT * FROM `posts` WHERE MATCH (`title`) AGAINST('+PHP -MySQL' IN BOOLEAN MODE);

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

• 空間データ型向けのインデックス • POINT 型, GEOMETRY 型など • R 木というデータ構造 • ISUCON10 予選の解説にも登場 SPATIAL https://dev.mysql.com/doc/refman/8.0/ja/spatial-index-optimization.html

(参考）「ISUCON10 予選問題の解説と講評」より https://isucon.net/archives/55025156.html

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

• 複数のカラムに対するインデックス • AND 検索や ORDER BY での複数指定に対応 • 定義する際のカラムの順番に意味がある • 定義と一致する順番にしか効かない • 本の索引で 2 文字目から探せないのと同じ 複合インデックス https://dev.mysql.com/doc/refman/8.0/ja/multiple-column-indexes.html

複合インデックス CREATE INDEX `users_idx1` ON `users` (`created_at`, `updated_at`); SELECT * FROM `users` ORDER BY `created_at`, `updated_at`; SELECT * FROM `users` ORDER BY `created_at`; SELECT * FROM `users` ORDER BY `updated_at`, `created_at`; ⭕ ⭕ ❌

• 複合インデックスを作成する場合はソート順も考慮 • ソート順の組み合わせが異なる場合は それぞれ別のインデックスとして作成する必要あり 降順インデックス https://dev.mysql.com/doc/refman/8.0/ja/descending-indexes.html

SELECT * FROM `users` ORDER BY `created_at` ASC, `updated_at` DESC; 降順インデックス CREATE INDEX `users_idx1` ON `users` (`created_at` ASC, `updated_at` ASC); CREATE INDEX `users_idx2` ON `users` (`created_at` ASC, `updated_at` DESC); ⭕ ❌

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

1 レコードに対して複数のインデックスレコード （例）JSON 型内の配列 複数値インデックス https://dev.mysql.com/doc/refman/8.0/ja/create-index.html#create-index-multi-valued CREATE TABLE `users` ( `id` VARCHAR(20) NOT NULL PRIMARY KEY, `profile` JSON, INDEX ((CAST(`profile`->'$.zipcode' AS UNSIGNED ARRAY))) ); SELECT * FROM `users` WHERE 94507 MEMBER OF(`profile`->'$.zipcode');

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼目次

• 演算結果の値に対するインデックスを作成できる • MySQL 8.0.13～ • 公式リファレンスの翻訳が不安定 • 「機能キー部品」「関数キー部分」 関数キーパーツ（functional key parts） https://dev.mysql.com/doc/refman/8.0/ja/create-index.html#create-index-functional-key-parts CREATE INDEX `idx1` ON `t1` ((`col1` + `col2`));

インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次

公式リファレンスマニュアルを読もう！ https://dev.mysql.com/doc/refman/8.0/ja/optimization-indexes.html

所属：株式会社ウィルゲート 登壇： 寄稿： 岡田 正平／おかしょい X: @okashoi GitHub: @okashoi PHPerKaigi 2024 シルバースポンサー