これはなに

高速なクエリを重たくする。
なんでそんなことをするのかっていうと、この記事でクエリチューニング環境を作っており、そこでチューニング対象とするような重たいクエリが欲しいから。
どういうテーブル構成なの？　とかは全て上記記事とgithubのリポジトリに上がっているのでそっち見て欲しい。

37. コメントの多い作品を抽出するクエリ

（急に37から始まってますが、気にしないでください、目に止まったやつから遅くしています）

クエリ

SELECT
    w.title,
    COUNT(c.comment_id) AS comment_count
FROM
    works w
    JOIN
        chapters ch
    ON  w.work_id = ch.work_id
    JOIN
        episodes e
    ON  ch.chapter_id = e.chapter_id
    JOIN
        comments c
    ON  e.episode_id = c.episode_id
WHERE
    e.status = 0
GROUP BY
    w.work_id
ORDER BY
    comment_count DESC
LIMIT 10
;

実行時間は0.065sec
(関係ないんだけど、MySQLWorkbenchの自動整形機能、全然いい感じに整形してくれない。これもわざわざWeb上の整形サービス使った。前はできた気がするんだけど。。)

実行計画

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, c, , index, episode_id, episode_id, 4, , 29926, 100.00, Using index; Using temporary; Using filesort
1, SIMPLE, e, , eq_ref, PRIMARY,chapter_id, PRIMARY, 4, querytuning.c.episode_id, 1, 10.00, Using where
1, SIMPLE, ch, , eq_ref, PRIMARY,work_id, PRIMARY, 4, querytuning.e.chapter_id, 1, 100.00, 
1, SIMPLE, w, , eq_ref, PRIMARY,user_id,category_id, PRIMARY, 4, querytuning.ch.work_id, 1, 100.00, 

なぜ高速か？

作品 - 章 - エピソード - コメントという結合が行われている。作品がメインだから作品テーブルから見てるのかと思いきや、コメントテーブルがメインにきている。（row 1のtableがcになっているのはそういうこと）
メインをcommentsにすると、エピソード - 章 - 作品という結合は全て主キーのインデックスを使うことにあり、めちゃくちゃ高速に処理される（ので、row列が全て1になっている）。
そういうわけで、commentsの走査こそindex full scanだが、それ以外がeq_refなのでめっちゃ速い。

いまいち理解できてない点

• e.status = 0でindexの貼られていないカラムで条件づけしてるんだけど、ここはなぜtypeに影響が出ていないのだろうか。commentsを全件舐めて、commentsに紐づくepisodeは常に1なのでそこで判定をしているだけってことか。そうかそりゃ影響ないわな

どうぶち壊すか？

• 案
  • commentsテーブルを膨大なレコード数にして、rowを増やす。optimizerに揺さぶりをかける
    • これでもしもworksがメインになったりすると、e.status = 0もちょっと効いてくるはず

結果

0.440sec
ちょっと遅くなった！

実行計画

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, e, , ALL, PRIMARY,chapter_id, , , , 169303, 10.00, Using where; Using temporary; Using filesort
1, SIMPLE, ch, , eq_ref, PRIMARY,work_id, PRIMARY, 4, querytuning.e.chapter_id, 1, 100.00, 
1, SIMPLE, w, , eq_ref, PRIMARY,user_id,category_id, PRIMARY, 4, querytuning.ch.work_id, 1, 100.00, 
1, SIMPLE, c, , ref, episode_id, episode_id, 4, querytuning.e.episode_id, 18, 100.00, Using index

おっし意図通りepisodesがメインのテーブルになってしかもtypeがALLだ！　これならインデックスはって高速にできるはず。OK。

まとめ

• 親テーブルをメインのテーブルにしてアクセスさせるとtype=ALLとかになって地獄が作れる
  • 子テーブルがメインだと速い理由
    • parents - childrenというテーブルがあったとき、childrenはparent_idを持っている。childrenをメインテーブルにしてparentsを引っ張ってくるとき、children自体はindexで検索できるし、parentsはPKであるparent_idで結合されているので高速なアクセスが可能になる。
  • 親をメインにして遅くなった理由
    • ここでparentsをメインにすると、indexのないchildren.parent_idで検索が必要になり、しんどくなる
  • 技術的な背景
    • optimizerは自由な順番でjoinして、最速の実行計画を選んでいる。今回はparentsがメインになるケースが最速と判断して持ってきた。なぜそうしたのかというと、childrenのレコード数が300万行あってデカすぎ、index full scanするのがしんどいから
      • あれ、じゃあtable=cの行がtype=refになってるのおかしくない？　インデックスで検索してることになってるけど、comments.episode_idにはインデックスはってないよ

わからんところ

• 1, SIMPLE, c, , ref, episode_id, episode_id, 4, querytuning.e.episode_id, 18, 100.00, Using index
  • これがepisode_idにはindexはってないのにtype=refになってる理由。なぜ。
  • 実行計画の読み方がいまいちわかってない気がする

35. 人気タグのランキング

クエリ

SELECT t.tag_name, COUNT(wt.work_id) AS tag_count
FROM ms_tags t
JOIN work_tags wt ON t.tag_id = wt.tag_id
GROUP BY t.tag_id
ORDER BY tag_count DESC
LIMIT 10; -- 上位10タグを取得

実行時間: 0.028sec

実行計画

なぜ高速か？

• クエリの概要
  • タグTBLと作品-タグTBLを結合して、人気タグ（作品が多いタグ）を10件抽出している
  • タグTBLをメインにして、作品-タグTBLのtag_idで結合してる。work_tags.tag_idはindexなので実行計画もtype=refとなる
  • 作品-タグTBLのrowsが4000件強になるのはタグTBLの1レコードに紐づく作品-タグTBLの平均がこれくらいってこと

いまいち理解できてない点

あんまないかな？

どうぶち壊すか？

• 案
  • まずcount()でindexカラムを見てるので、ここをあえてindexじゃないカラムでのカウントにしてやる
  • 作品-タグTBLがrefになってるのをどうにかしてALLとかにしてやる
    • rangeならWHEREつければできそう。ひとまずそうしてみるか。

ぶち壊しクエリ

EXPLAIN
SELECT t.tag_name, COUNT(wt.create_user_id) AS tag_count
FROM ms_tags t
JOIN work_tags wt ON t.tag_id = wt.tag_id and wt.created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY t.tag_id
ORDER BY tag_count DESC
LIMIT 10; -- 上位10タグを取得

結果

実行時間: 0.121sec
よしよし。ちなみにふたつの施策で遅くしたけど、片方ずつにしてもだいたい0.110secとかだった。

逆クエリチューニング後の実行計画

まとめ

• count()などの計算に使うカラムを非indexにすると遅くなる
• 非indexカラムでの検索も遅くなる
  • using indexが消えてusing whereになってる。
    • using indexはカバリングインデックスのやつだ。それが消えてwhereになってるから遅いんだな。
    • けどcreated_atは範囲検索しているし、ここの改善は難しいかもしれないな

わからんところ

• いまだにいまいちtypeのことわかってないな
  • インデックスを使ってアクセスする、インデックスがある限りはそれで結合してインデックス経由でストレージの（？）他のカラムにアクセスする。それはわかる
    • ここ、ストレージにあったのかメモリにあったかによって速度変わるかな

no.39 クエリ概要

クエリ

/*
39. ユーザーのフォロワー数の取得
ユーザーごとのフォロワー数を取得し、ユーザーの影響力や人気度を示します。
*/

SELECT u.pen_name, COUNT(f.follow_to) AS follower_count
FROM users u
LEFT JOIN follows f ON u.user_id = f.follow_to
GROUP BY u.user_id
ORDER BY follower_count DESC
LIMIT 10; -- 上位10ユーザーを取得

/*

実行時間: 0.657sec
む。そこそこ遅いな

実行計画

（画像ちっちぇ〜ので二段にしました）

なるほど、全てのユーザーを舐めて、それぞれフォローされている数を計算しているのね。
確かに遅くなりそう。フォロワー数ランキングだとあんまりないかもだけど、例えばお気に入りされている作品のランキングとかだったありうるし、もっと高速にうごてくれないとまずい気がする。

どう高速化するか？

• フォロワー数カラム追加
  • 安易に思いつくのはこれ。で非同期に集計させるとか夜間バッチとかで一日に一回集計するとか。フォロワー一覧だったら特定のユーザーを狙えるのでindexではなくconstとかでできるはず。
  • ただ、これでコードが増えるのはどうなのみたいな意見もありそうなので他の案がないか考えてみる
• 全てのユーザーIDを見る必要はない
  • followsテーブルをメインにして、follow_toにインデックス、かつえーっと関数インデックスみたいなので順序をつける、あーいや全部見ないと過去よりたくさんみたいなケース除けないからダメか？
  • 関数インデックスってどこまでできるんだ？

関数インデックスでできること

• 読むやつ
  • MySQLで関数インデックス（functional indexes）を使いこなそう - Oracle
• メモ
  • 8.0.13から使えるらしい！
  • index((col1 + col2))見たいな感じに使える
• 感想
  • あんま情報はないけど、使えるのは嬉しい
  • あとただこれめっちゃこうメモリ食いそう
  • 関数使うとインデックス効かないみたいなのあった気がするけど、これも解消されるってことかな？

再度取り組む

いろいろ忙しくてやりっぱなしになってた。
いま見直すと、うーん、関数インデックスでは少なくともないな。んで降順とかそういうインデックスにしても、一時テーブルを使っているのでそこにはインデックスが張れなくて無理なのではないかと思う。
重たくなってそうなのはまずfilesortかなあ。using indexもこれ確かインデックスフルスキャンなんだよな。ただ今回はカウントしてるのでフルスキャンになるのはしょうがない気がする。
filesortについていうと、これはCOUNT()の結果に対してソートをしてるので、うーん、indexかけられるのかな。これはうーん、どうなんだ、follows.follow_toのCOUNT()で関数インデックスを使うとか？　あ結局関数インデックスじゃん。
やってみるか。

関数インデックスを試す

作業前のインデックス

show index from follows;

インデックスをはる

できん！！　集計関数はむりっぽい

じゃあむしろ中間テーブルを明示的に作る？

一旦followsでCOUNT(follow_to)のやつ作って、それを降順で10件持って、それと結合させる。メインがusersでそこに全件マッチさせた上で集計関数をやるのは効率が悪い。

SELECT u.pen_name, follows_c.follower_count
FROM users u
RIGHT JOIN (
	SELECT follow_to, COUNT(follow_to) AS follower_count
    FROM follows
    GROUP BY follow_to
    ORDER BY follower_count DESC
    LIMIT 10
) AS follows_c ON u.user_id = follows_c.follow_to
;

実行時間: 0.200sec
速くなった！　先に集計させる方がいい、ってのはなんとなくオプティマイザの敗北って感じがあるが...

実行計画

うーん、Using filesortになっちゃってる理由がわかんない。インデックスみるかんじfilesortする必要なさそうだけど...

わからんポイント

• ORDER BYに使っているfollow_toにはインデックスが張られているはずなのにusing filesortになる理由
• using indexってフルスキャンなんだっけ？　それともカバリングインデックス？

インターネットの恵み

yoku0825さんからコメントをいただいた。ありがとうございます！
https://x.com/yoku0825/status/1774459460163088883

言われてみると確かにその通り。COUNT()の結果に対してソートかけているんだから、ソート狙いのインデックスが貼れないのはその通りだ。

結論

テーブル設計をいじって集計結果のカラムを作るのが一番良さそう。シンプルだしね。

メモ

複合インデックスの順序

https://use-the-index-luke.com/ja/sql/where-clause/the-equals-operator/concatenated-keys

fillteredは雑な値

fillteredに出てくる数はちゃんとした統計ではなくて定数で帰ってきている、雑な数
https://twitter.com/yoku0825/status/1764976122260038117
https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/item.h#L91-L106

テンプレ

# no. クエリ概要

## クエリ
- ```sql
- ```

実行時間: sec

## 実行計画
画像添付

## なぜ高速か？

## いまいち理解できてない点

# どうぶち壊すか？

## ぶち壊しクエリ

# 結果
実行時間: sec

## 逆クエリチューニング後の実行計画
画像

## まとめ

## わからんところ

no.29 クエリ概要

クエリ

/*
29. 特定ユーザーのフォロワー数の取得
機能の説明: 特定のユーザーのフォロワー数を取得する。
このクエリは、特定のユーザーのフォロワー数を取得しています。フォローの情報を特定のユーザーごとに集計しているため、データ量が増えるにつれてクエリの実行時間が増加する可能性があります。
*/
SELECT follow_to, COUNT(follow_from) AS follower_count
FROM follows
WHERE follow_to = 555
GROUP BY follow_to;

実行時間: 0.0011sec

実行計画

画像添付

なぜ高速か？

いまいち理解できてない点

どうぶち壊すか？

ぶち壊しクエリ

結果

実行時間: sec

逆クエリチューニング後の実行計画

画像

まとめ

わからんところ

SHOW INDEX FROM T_XXX; の読み方

これの読み方がわからん。
例えばfollowsのインデックスをshow index from follows;すると、

これどう見るのか。これはchatgptに聞いても良さげなので聞く。

Table: インデックスが存在するテーブルの名前。
Non_unique: インデックスが一意でない場合は0、一意の場合は1が表示されます。
Key_name: インデックスの名前。
Seq_in_index: インデックス内のカラムの位置（1から始まります）。
Column_name: インデックス内のカラムの名前。
Collation: インデックスの照合順序（Collation）。
Cardinality: インデックスのカーディナリティ（ユニークな値の数）。
Sub_part: インデックスの部分長。NULLは全体の長さを意味します。
Packed: インデックスのパッキング形式。
Null: インデックスがNULL値を許可するかどうかを示します。"YES"は許可することを意味し、"NO"は許可しないことを意味します。
Index_type: インデックスのタイプ（例: BTREE）。
Comment: インデックスに関する追加情報。

わかったような、わからんような。
ただ確認できたのは、複合インデックスは今回使われてなくて、個別のインデックスがあるだけっぽいこと。いやほんまか？
２行目のカーディナリティ見た感じ、これが複合インデックスなのでは？　と思うが。。

公式をみよ

公式。
https://dev.mysql.com/doc/refman/8.0/ja/show-index.html

別途複合インデックスを貼ってみて、どう表示されるのか確認する。