MySQLでGROUP BY と ORDER BY を同時に使いたくなったら/If_you_want_to_use_GROUP_BY_and_ORDER_BY_at_the_same_time_in_mysql

https://kichijojipm.connpass.com/event/177459/
吉祥寺.pm でお話しする
「MySQLでGROUP BY と ORDER BY を同時に使いたくなったら」
のスライドです

Transcript

1. MySQL で GROUP BY と ORDER BY を 同時に 使いたくなったら ー MySQLクエリチューニング外伝 Jun 2, 2020 @ 吉祥寺.pm22 まみー (@mamy1326) / Lancers
2. Twitter Work Like ✔ 2017年の趣味：MySQL ✔ 2018年の趣味：DNS ✔ 2020年の趣味：業務に没頭 (CakePHP4) ：@mamy1326（まみー） ：Lancers,Inc. @ PHPer ：cune.jp 自己紹介 2
3. ✔ MySQL ユーザー 　　→他の RDBMS だと 標準SQL違反 ✔ 巨大なデータ を扱う人 　　→1000万件以上 の テーブルJOIN ✔ グループ化とソート を一緒にやりたい 　　→1 対 n のテーブルJOIN 想定オーディエンスのみなさま 3
4. 　質問です 1 4
5. 5 1 対 n で JOIN したことある人✋
6. 6 n 側 で GROUP BY したことある人✋
7. 起きていたこと ✔ スロークエリ ( 180 sec ) 　　→1 対 n のJOIN 7
8. 起きていたこと ✔ スロークエリ ( 180 sec ) 　　→1 対 n のJOIN ✔ インデックスで 速度改善せず 　　→別の複合インデックスが選択された 8
9. 起きていたこと ✔ スロークエリ ( 180 sec ) 　　→1 対 n のJOIN ✔ インデックスで 速度改善せず 　　→別の複合インデックスが選択された ✔ そもそも クエリが間違って いた 　　→ONLY_FULL_GROUP_BY 違反 (後述) 9
10. 起きていたこと ✔ スロークエリ ( 180 sec ) 　　→1 対 n のJOIN ✔ インデックスで 速度改善せず 　　→別の複合インデックスが選択された ✔ そもそも クエリが間違って いた 　　→ONLY_FULL_GROUP_BY 違反 (後述) 10 データで検証し 解決までをお話しします
11. 間違いに気づいた理由 11
12. 間違いに気づいた理由 12 クエリと インデックスの解釈が 間違っていた
13. 間違いに気づいた理由 13 以前の職場の同僚で 凄腕DBA
14. 間違いに気づいた理由 14 ポジティブフィードバックに 全力で感謝
15. 今日の目標 やろう！データ検証 直そう！間違ったクエリ 改善！スロークエリ 15
16. 今日の目標 16 やろう！データ検証 直そう！間違ったクエリ 改善！スロークエリ
17. 環境 Amazon Aurora 17 mysql> select AURORA_VERSION(); +------------------+ | AURORA_VERSION() | +------------------+ | 2.04.5 | +------------------+ 1 row in set (0.00 sec)
18. 環境 MySQL Engine 18 mysql> select version(); +------------+ | version() | +------------+ | 5.7.12-log | +------------+ 1 row in set (0.00 sec)
19. 環境 スロークエリ検出時間 19 mysql> show variables like 'long%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.01 sec)
20. 　質問です 2 20
21. 21 正直 雰囲気で ORM を 使っている！✋
22. 22 正直 本番と同じ構成で データ検証していない✋
23. 23 そうだ データ検証 (検証環境) しよう
24. データ検証の目的 ✔ 要件通りのデータが取れているか 　　→自作のテストデータは 必ず抜け漏れる 24
25. データ検証の目的 ✔ 要件通りのデータが取れているか 　　→自作のテストデータは 必ず抜け漏れる ✔ 速度が著しく低下しないか 　　→本番で 動くクエリが ジャスティスだ 25
26. 26 要件
27. 要件 ✔ ユーザーを 最新更新順 にソート 　　→users テーブルが親 ✔ ファイルはユーザー情報 の一部 　　→uploads テーブルが子 27
28. 28 検証の前に 間違ったクエリ解説
29. 動いていたクエリ (標準SQL違反) 全体像 29 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20;
30. 動いていたクエリ (標準SQL違反) 全体像 30 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; ユーザー 1 対 ファイル n uploads は数千万レコード
31. 動いていたクエリ (標準SQL違反) 全体像 31 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; 特定せずにユーザーの 一覧を表示する (管理画面)
32. 動いていたクエリ (標準SQL違反) 全体像 32 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; 特定しないので WHERE句で 絞り込めない
33. 動いていたクエリ (標準SQL違反) 全体像 33 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; 複数レコードを ユーザー単位に グルーピング
34. 動いていたクエリ (標準SQL違反) 全体像 34 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; グルーピングした結果で 更新の降順に ユーザー一覧をソート
35. 動いていたクエリ (標準SQL違反) 全体像 35 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; それ 標準SQL違反 です
36. 動いていたクエリ (標準SQL違反) 全体像 36 SELECT users.nickname, users.email, uploads.created FROM users LEFT JOIN uploads ON uploads.user_id = users.id GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; ONLY_FULL_GROUP _BY 違反
37. ONLY_FULL_GROUP_BY 違反 ✔ 公式 　https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html#sql-mode-setting ✔ 引用 　・ONLY_FULL_GROUP_BY GROUP BY 句で名前が指定されていない 非集約カラムを、選択リスト、HAVING 条件、 または (MySQL 5.6.5 以降で) ORDER リストが 参照するクエリーを拒否します。 37
38. 間違った前提条件 ✔ 集約・非集約カラムの 考慮漏れ 　　→GROUP BY 対象外のカラムは不定 　　→ゆえに 集約関数を使う必要 がある ✔ 標準SQL違反を (僕が) 知らなかった 　　→MySQL 5.6.5 より前では動く 　　→他の RDBMS ではエラー 　　→MySQL 5.6.5 以降では設定次第でエラー 38
39. 39 そもそも クエリが間違って いた
40. 40 標準SQL違反でも パッと見 は データが 正しく見えた
41. 41 まずは 間違った クエリを 検証 します
42. データ検証 クエリ 42 SELECT users.nickname, users.email, uploads.created ✔ 便宜上、必要そうなカラム指定 　→実際は利用カラムのみ指定
43. データ検証 クエリ 43 FROM users LEFT JOIN uploads ON uploads.user_id = users.id ✔ 1テーブルのJOIN 　→ユーザーとファイルのテーブル
44. データ検証 クエリ 44 GROUP BY uploads.user_id ORDER BY uploads.created DESC LIMIT 20; ✔ ONLY_FULL_GROUP_BY 違反 　→この条件でどんな結果が返るのか
45. 45 データ検証 データ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) ✔ 2回に分けて更新されている 　→想定は最新のレコードの日付が欲しい
46. 46 データ検証 データ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) ✔ 2回に分けて更新されている 　→想定は最新のレコードの日付が欲しい スライドの都合上 ユーザーで 絞って表示
47. 47 データ検証 データ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) ✔ 2回に分けて更新されている 　→想定は最新のレコードの日付が欲しい 1回目に 3枚画像を アップロード
48. 48 データ検証 データ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) ✔ 2回に分けて更新されている 　→想定は最新のレコードの日付が欲しい 2回目に 1枚画像を アップロード
49. 49 データ検証 データ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) ✔ 2回に分けて更新されている 　→想定は最新のレコードの日付が欲しい ユーザーとして 最新の更新日付
50. 50 データ検証 クエリ実行 mysql> SELECT -> users.nickname, -> users.email, -> uploads.created -> FROM -> users -> LEFT JOIN uploads -> ON uploads.user_id = users.id -> WHERE -> users.id = 2177557 -> GROUP BY -> uploads.user_id -> ORDER BY -> uploads.created DESC -> LIMIT 20; +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-02-14 21:10:26 | +------------------+----------------------------+---------------------+ 1 row in set (0.01 sec)
51. 51 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-02-14 21:10:26 | +------------------+----------------------------+---------------------+ 狙いとは 別のレコードの created
52. 52 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-02-14 21:10:26 | +------------------+----------------------------+---------------------+ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) 欲しかった レコード
53. 53 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-02-14 21:10:26 | +------------------+----------------------------+---------------------+ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) このうちの どれかになる
54. データ検証まとめ ✔ 非集約カラムの値は 不定 　　→何が返るかわからない (ソートされない) ✔ クエリは 解釈順 が決まっている 　　→GROUP BY の 後に ORDER BY ✔ 要件を 満たせない 　　→クエリを書き換える必要がある 54
55. 55 間違っている ことが わかった ので…
56. 今日の目標 56 やろう！データ検証 直そう！間違ったクエリ 改善！スロークエリ
57. 要件の再整理 ✔ ユーザーを 更新順で降順 ソート 　　→uploads.created の最新で ORDER BY ✔ GRUOP BY で グルーピング 　　→複数レコードを1つに ✔ 不定カラムは集約 して一定に 　　→RDBMS が返す値を正しく指定 57
58. GROUP BY と 集約関数 不定な値のカラムを一定に ✔ 同じ値のカラムで グルーピング 　　→複数のデータを集約した計算結果が欲しい ✔ 不定な値の 返し方を指定 　　→最大値はMAX、最小値はMIN、集計はSUM、平均値はAVG、など RDBMS が 返せる値 になる 58
59. やること ✔ サブクエリを使う ✔ uploads をグルーピング・集約 ✔ 結果を users と JOIN ✔ uploads.created で ORDER BY 59
60. 結果 修正したクエリ 60 SELECT users.nickname, users.email, uploads.created FROM users INNER JOIN ( SELECT user_id, MAX(created) as created FROM uploads GROUP BY user_id ) as uploads ON users.id = uploads.user_id WHERE users.id = 2177557 ORDER BY uploads.created DESC
61. 結果 修正したクエリ 61 SELECT users.nickname, users.email, uploads.created FROM users INNER JOIN ( SELECT user_id, MAX(created) as created FROM uploads GROUP BY user_id ) as uploads ON users.id = uploads.user_id WHERE users.id = 2177557 ORDER BY uploads.created DESC uploads を サブクエリで グルーピング
62. 結果 修正したクエリ 62 SELECT users.nickname, users.email, uploads.created FROM users INNER JOIN ( SELECT user_id, MAX(created) as created FROM uploads GROUP BY user_id ) as uploads ON users.id = uploads.user_id WHERE users.id = 2177557 ORDER BY uploads.created DESC 集約関数 MAX() で 最大値を指定
63. 結果 修正したクエリ 63 SELECT users.nickname, users.email, uploads.created FROM users INNER JOIN ( SELECT user_id, MAX(created) as created FROM uploads GROUP BY user_id ) as uploads ON users.id = uploads.user_id WHERE users.id = 2177557 ORDER BY uploads.created DESC users と JOIN
64. 結果 修正したクエリ 64 SELECT users.nickname, users.email, uploads.created FROM users INNER JOIN ( SELECT user_id, MAX(created) as created FROM uploads GROUP BY user_id ) as uploads ON users.id = uploads.user_id WHERE users.id = 2177557 ORDER BY uploads.created DESC 最新日付で ソート
65. データ検証 実行結果 65 mysql> SELECT -> users.nickname, -> users.email, -> uploads.created -> FROM -> users -> INNER JOIN -> ( -> SELECT -> user_id, -> MAX(created) as created -> FROM -> uploads -> GROUP BY -> user_id -> ) as uploads -> ON users.id = uploads.user_id -> WHERE -> users.id = 2177557 -> ORDER BY -> uploads.created DESC; +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-04-17 16:16:21 | +------------------+----------------------------+---------------------+ 1 row in set (1.30 sec)
66. 66 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-04-17 16:16:21 | +------------------+----------------------------+---------------------+ 要件通りの created
67. 67 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created | +------------------+----------------------------+---------------------+ | nickname_2177557 | test+2177557@lancers.co.jp | 2020-04-17 16:16:21 | +------------------+----------------------------+---------------------+ mysql> select id, user_id, created from uploads where user_id=2177557; +----------+---------+---------------------+ | id | user_id | created | +----------+---------+---------------------+ | 21551405 | 2177557 | 2020-02-14 21:10:26 | | 21551406 | 2177557 | 2020-02-14 21:10:26 | | 21551407 | 2177557 | 2020-02-14 21:10:26 | | 21519333 | 2177557 | 2020-04-17 16:16:21 | +----------+---------+---------------------+ 4 rows in set (0.00 sec) 欲しかった カラム値
68. 68 無事に 要件を満たした✨
69. 69 しかしまだ 課題 がある
70. やろう！データ検証 直そう！間違ったクエリ 改善！スロークエリ 今日の目標 70
71. 71 お時間の都合で ポイントのみ お話しします
72. 72 スロークエリ改善 インデックス作成 mysql> ALTER TABLE uploads ADD INDEX user_id_created(user_id, created); Query OK, 0 rows affected (1 min 39.44 sec) Records: 0 Duplicates: 0 Warnings: 0 ✔ uploads に複合インデックス作成 　→インデックスのみでグルーピング、 　　集約を想定
73. スロークエリ改善 EXPLAIN 実行 73 mysql> EXPLAIN SELECT -> users.nickname, -> users.email, -> uploads.created -> FROM -> users -> INNER JOIN -> ( -> SELECT -> user_id, -> MAX(created) as created -> FROM -> uploads -> GROUP BY -> user_id -> ) as uploads -> ON users.id = uploads.user_id -> ORDER BY -> uploads.created DESC -> LIMIT 20\G
74. スロークエリ改善 EXPLAIN 結果 74 *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 683462 filtered: 100.00 Extra: Using filesort *************************** 2. row *************************** id: 1 select_type: PRIMARY table: users partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: uploads.user_id rows: 1 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: uploads partitions: NULL type: range possible_keys: user_id_belong_to,user_id_created key: user_id_created key_len: 4 ref: NULL rows: 683462 filtered: 100.00 Extra: Using index for group-by 3 rows in set, 1 warning (0.00 sec)
75. スロークエリ改善 EXPLAIN 結果 - 注目ポイント 75 *************************** 3. row *************************** id: 2 select_type: DERIVED table: uploads partitions: NULL type: range possible_keys: user_id_belong_to,user_id_created key: user_id_created key_len: 4 ref: NULL rows: 683462 filtered: 100.00 Extra: Using index for group-by 3 rows in set, 1 warning (0.00 sec)
76. スロークエリ改善 EXPLAIN 結果 - 注目ポイント 76 *************************** 3. row *************************** id: 2 select_type: DERIVED table: uploads partitions: NULL type: range possible_keys: user_id_belong_to,user_id_created key: user_id_created key_len: 4 ref: NULL rows: 683462 filtered: 100.00 Extra: Using index for group-by 3 rows in set, 1 warning (0.00 sec) 作成した インデックスが 選択されている
77. スロークエリ改善 EXPLAIN 結果 - 注目ポイント 77 *************************** 3. row *************************** id: 2 select_type: DERIVED table: uploads partitions: NULL type: range possible_keys: user_id_belong_to,user_id_created key: user_id_created key_len: 4 ref: NULL rows: 683462 filtered: 100.00 Extra: Using index for group-by 3 rows in set, 1 warning (0.00 sec) ルースインデックススキャン が適用されている
78. スロークエリ改善 EXPLAIN 結果 - 注目ポイント 78 *************************** 3. row *************************** id: 2 select_type: DERIVED table: uploads partitions: NULL type: range possible_keys: user_id_belong_to,user_id_created key: user_id_created key_len: 4 ref: NULL rows: 683462 filtered: 100.00 Extra: Using index for group-by 3 rows in set, 1 warning (0.00 sec) ルースインデックススキャン (公式URL) https://dev.mysql.com/doc/refman/5.6/ja/group-by- optimization.html
79. スロークエリ改善 実際の実行結果 79 mysql> SELECT -> users.nickname, -> users.email, -> uploads.created -> FROM -> users -> INNER JOIN -> ( -> SELECT -> user_id, -> MAX(created) as created -> FROM -> uploads -> GROUP BY -> user_id -> ) as uploads -> ON users.id = uploads.user_id -> ORDER BY -> uploads.created DESC -> LIMIT 20\G (தུ) 20 rows in set (1.27 sec)
80. スロークエリ改善 実際の実行結果 80 mysql> SELECT -> users.nickname, -> users.email, -> uploads.created -> FROM -> users -> INNER JOIN -> ( -> SELECT -> user_id, -> MAX(created) as created -> FROM -> uploads -> GROUP BY -> user_id -> ) as uploads -> ON users.id = uploads.user_id -> ORDER BY -> uploads.created DESC -> LIMIT 20\G (தུ) 20 rows in set (1.27 sec) 180 sec の スロークエリが 改善された
81. 81 まとめ
82. まとめ ✔ データ検証を通して 要件を検証 　　→結果が間違っていることを証明 ✔ 間違いを修正し要件を達成 　　→標準SQL違反の理解と正しいクエリ実装 ✔ インデックスでパフォーマンス改善 　　→180 sec -> 1.3 sec 82
83. 今日の目標 (ふりかえり) やろう！データ検証 直そう！間違ったクエリ 改善！スロークエリ 83
84. 84 終わりに
85. 終わりに ✔ 必ず 検証・計測 しよう 　　→運用保守を見越して品質を保証 ✔ 正しい クエリの解釈 をしよう 　　→使い方を知ることで間違いを正す ✔ クエリを書ける ようになろう 　　→クエリありきでORMを利用 85
86. 86 ポジティブフィードバックは 人生の宝物 ✨
87. 大切！日々の運用改善 運用は愛 ・ 後から変更は大変 ・ インフラは他人事じゃない ・ 0 -> 1 以降も大事 ・ データの寿命はアプリより長い 87
88. 大切！日々の運用改善 運用は愛 ・ 後から変更は大変 ・ インフラは他人事じゃない ・ 0 -> 1 以降も大事 ・ データの寿命はアプリより長い 88 100 から 1000 にするには 育てる愛情が必要
89. 愛を持って 運用改善しましょう！ 89
91. MySQL! MySQL! #F0F0F0 #666666 #E6855E #5EC84E #F0F0F0 　あいうえおかきくけこさしすせそ #E6855E 　　あいうえおかきくけこさしすせそ #5EC84E 　あいうえおかきくけこさしすせそ