10秒かかるようになった画面
サポート担当が、お客様のチケットの一覧を開きます。最初の日は、一瞬で表示されます。1年後、チケットは何百万件になり、同じ画面に10秒かかります。
クエリは誰も変えていません。表が大きくなり、データベースが全部のチケットを読んで、そのお客様の分を探しているのです。
対策の定番がインデックスです。要るかどうかは、EXPLAIN で分かります。
このレッスンのゴール
メモ
インデックスでクエリを速くし、EXPLAIN を読んで確かめられる。
重要な言葉
| ことば | いみ | れい |
|---|---|---|
| インデックス | 表のそばに置く並べ替え済みの一覧。全部の行を読まずに、行を見つけられる | |
| 全件スキャン | 表の全部の行を読むこと | EXPLAIN の type ALL |
| EXPLAIN | クエリを実行せずに、どう実行するつもりかを見せる命令 | |
| 実行計画 | クエリに答えるために、データベースが選ぶ手順 | |
| 見積もり | データベース自身の大まかな予想で、正確な件数ではない |
学生が入力するセルは、このプレビューでは空白です。
遅さを感じられる大きさの表
ラボのショップは商品が数十件しかなく、どのクエリも一瞬です。インデックスの働きを見るには、もっと行が要ります。
次の Run で、自分のデータベースに5000行の表 tickets を作ります。ショップには触れません。
やり直したいときは、何度でも最初から Run できます。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
クライアント用の短い名前
alias db='mariadb -h db -u student -pstudent -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
自分のデータベースを作る
db "CREATE DATABASE IF NOT EXISTS student_depot"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
古い表を捨てる
db "DROP TABLE IF EXISTS student_depot.tickets"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
表を作る
db "CREATE TABLE student_depot.tickets (id INT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(10) NOT NULL, opened_on DATE NOT NULL)"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
5000件のチケットを入れる
db "SET SESSION cte_max_recursion_depth = 10000; INSERT INTO student_depot.tickets WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000) SELECT i, 1 + (i * 37) % 800, IF(i % 5 = 0, 'open', 'closed'), DATE_ADD('2024-01-01', INTERVAL i % 365 DAY) FROM n"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
件数を数える
db "SELECT COUNT(*) AS rows_in_table FROM student_depot.tickets"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
EXPLAIN は計画を見せる
クエリの前に EXPLAIN と書きます。データベースはクエリを実行せず、どう実行するかを見せます。
大事な列は3つです。type は、表の読み方です。key は、使うインデックスの名前で、なければ NULL です。rows は、読む行数の見積もりです。
あるお客様のチケットについて聞いてみます。
インデックスなしの計画
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE customer_id = 42"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
例: 計画を読む
| 列 | 見えた値 | 意味 |
|---|---|---|
| type | ALL | 全件スキャン。全部の行を読む |
| key | NULL | インデックスを使わない |
| rows | 約5000 | 表の全体を読むつもり |
| Extra | Using where | 1行ずつ読んで、customer_id を調べる |
学生が入力するセルは、このプレビューでは空白です。
インデックスは、並べ替え済みの近道
本の索引は、言葉をページ番号つきで順に並べています。1つの言葉を探すのに、本を全部読みません。
データベースのインデックスも同じです。customer_id の値を順に並べ、それぞれに行の場所を付けます。
CREATE INDEX で作ります。名前、表、列を渡します。
customer_id にインデックスを作る
db "CREATE INDEX idx_customer ON student_depot.tickets (customer_id)"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
もう一度、計画を聞く
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE customer_id = 42"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
5000行から、約6行へ
今度は、type が ref、key が idx_customer、rows が6です。データベースは、そのお客様のチケットへ直接行きます。
クエリは変わっていません。変わったのは計画だけです。
主キーには、もうインデックスがあります。外部キーにもあります。MySQL が自分で作ります。
orders 表のインデックスを見る
mariadb -h db -u student -pstudent shop -t -e "SHOW INDEX FROM orders"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
インデックスには代金がある
インデックスは、列の2つ目の写しを、順に並べて持つものです。場所を取ります。
INSERT、UPDATE、DELETE のたびに、インデックスも直す必要があります。インデックスが1つ増えるごとに、書き込みは少し遅くなります。
インデックスは、よく聞く質問のために付けます。全部の列には付けません。
インデックスの大きさを見る
db "ANALYZE TABLE student_depot.tickets; SELECT data_length, index_length FROM information_schema.tables WHERE table_schema = 'student_depot' AND table_name = 'tickets'"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
インデックスは作れば必ず使われる、と思う人が多くいます。そうではありません。クエリが列を関数の中に隠すと、データベースは並べ替え済みの一覧を使えません。思い込まず、計画を見ましょう。
開いた日にインデックスを作る
db "CREATE INDEX idx_opened ON student_depot.tickets (opened_on)"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
列に関数を使った計画
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE YEAR(opened_on) = 2024"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
列に範囲を使った計画
db "EXPLAIN SELECT * FROM student_depot.tickets WHERE opened_on >= '2024-03-01' AND opened_on < '2024-03-08'"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。