MySQL のインデックスの種類をおさらいしよう！ / overviewing indexes in MySQL

2024/03/07～09 開催の「PHPerKaigi 2024」（https://phperkaigi.jp/2024/ ）の LT 資料です。

詳細：https://fortee.jp/phperkaigi-2024/proposal/a28d42c5-9df5-4c17-bc11-90643425fe25

Transcript

1. MySQL のインデックスの種類 をおさらいしよう！ 2024/03/09 PHPerKaigi 2024 @okashoi
2. インデックス、 作成してますか？
3. よくみるインデックス 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`); あるいは
4. ふつう、本の中で特定の言葉の出現位置を探す場合 インデックスのイメージ
5. ふつう、本の中で特定の言葉の出現位置を探す場合 インデックスのイメージ
6. インデックス = 索引 インデックスのイメージ
7. インデックス = 索引 インデックスのイメージ
8. インデックス = 索引 → 少ない時間で出現位置がわかる インデックスのイメージ
9. 実はインデックスには いくつか種類がある
10. CREATE INDEX 構文 https://dev.mysql.com/doc/refman/8.0/ja/create-index.html
11. CREATE INDEX 構文 https://dev.mysql.com/doc/refman/8.0/ja/create-index.html
12. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
13. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
14. • いわゆる「インデックス」と言ったときに指すもの • UNIQUE は重複を許可しない • B+木というデータ構造 • 文字列は完全一致か前方一致（LIKE '〇〇%'）のみ • 本の索引で 2 文字目から探せないのと同じ INDEX, UNIQUE
15. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
16. • 全文検索用のインデックス • 自然言語検索とブール検索の 2 つのモード • 転置インデックスという形でデータを持つ • 日本語ではパーサーの指定が必要 • 単語間がスペースで区切られていないため • そこまで MySQL にやらせるか、は要検討か FULLTEXT https://dev.mysql.com/doc/refman/8.0/ja/fulltext-search.html
17. 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);
18. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
19. • 空間データ型向けのインデックス • POINT 型, GEOMETRY 型など • R 木というデータ構造 • ISUCON10 予選の解説にも登場 SPATIAL https://dev.mysql.com/doc/refman/8.0/ja/spatial-index-optimization.html
20. (参考）「ISUCON10 予選問題の解説と講評」より https://isucon.net/archives/55025156.html
21. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
22. • 複数のカラムに対するインデックス • AND 検索や ORDER BY での複数指定に対応 • 定義する際のカラムの順番に意味がある • 定義と一致する順番にしか効かない • 本の索引で 2 文字目から探せないのと同じ 複合インデックス https://dev.mysql.com/doc/refman/8.0/ja/multiple-column-indexes.html
23. 複合インデックス 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`; ⭕ ⭕ ❌
24. • 複合インデックスを作成する場合はソート順も考慮 • ソート順の組み合わせが異なる場合は それぞれ別のインデックスとして作成する必要あり 降順インデックス https://dev.mysql.com/doc/refman/8.0/ja/descending-indexes.html
25. 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); ⭕ ❌
26. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
27. 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');
28. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼目次
29. • 演算結果の値に対するインデックスを作成できる • 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`));
30. インデックスの種類 INDEX, UNIQUE, FULLTEXT, SPATIAL インデックスの作成のしかた 複合、降順、複数値、関数キーパーツ ▼ 目次
31. 公式リファレンスマニュアルを読もう！ https://dev.mysql.com/doc/refman/8.0/ja/optimization-indexes.html
32. 所属：株式会社ウィルゲート 登壇： 寄稿： 岡田 正平／おかしょい X: @okashoi GitHub: @okashoi PHPerKaigi 2024 シルバースポンサー