Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

よくみるインデックス 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`); あるいは

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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);

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

複合インデックス 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`; ⭕ ⭕ ❌

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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); ⭕ ❌

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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');

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

• 演算結果の値に対するインデックスを作成できる • 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`));

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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