Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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 を同時に使いたくなったら」
のスライドです

mamy1326

June 02, 2020
Tweet

More Decks by mamy1326

Other Decks in Programming

Transcript

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

    ー MySQLクエリチューニング外伝 Jun 2, 2020 @ 吉祥寺.pm22 まみー (@mamy1326) / Lancers
  2. ✔ MySQL ユーザー   →他の RDBMS だと 標準SQL違反 ✔ 巨大なデータ を扱う人

      →1000万件以上 の テーブルJOIN ✔ グループ化とソート を一緒にやりたい   →1 対 n のテーブルJOIN 想定オーディエンスのみなさま 3
  3. 起きていたこと ✔ スロークエリ ( 180 sec )   →1 対 n

    のJOIN ✔ インデックスで 速度改善せず   →別の複合インデックスが選択された 8
  4. 起きていたこと ✔ スロークエリ ( 180 sec )   →1 対 n

    のJOIN ✔ インデックスで 速度改善せず   →別の複合インデックスが選択された ✔ そもそも クエリが間違って いた   →ONLY_FULL_GROUP_BY 違反 (後述) 9
  5. 起きていたこと ✔ スロークエリ ( 180 sec )   →1 対 n

    のJOIN ✔ インデックスで 速度改善せず   →別の複合インデックスが選択された ✔ そもそも クエリが間違って いた   →ONLY_FULL_GROUP_BY 違反 (後述) 10 データで検証し 解決までをお話しします
  6. 環境 Amazon Aurora 17 mysql> select AURORA_VERSION(); +------------------+ | AURORA_VERSION()

    | +------------------+ | 2.04.5 | +------------------+ 1 row in set (0.00 sec)
  7. 環境 MySQL Engine 18 mysql> select version(); +------------+ | version()

    | +------------+ | 5.7.12-log | +------------+ 1 row in set (0.00 sec)
  8. 環境 スロークエリ検出時間 19 mysql> show variables like 'long%'; +-----------------+-----------+ |

    Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.01 sec)
  9. 動いていたクエリ (標準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;
  10. 動いていたクエリ (標準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 は数千万レコード
  11. 動いていたクエリ (標準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; 特定せずにユーザーの 一覧を表示する (管理画面)
  12. 動いていたクエリ (標準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句で 絞り込めない
  13. 動いていたクエリ (標準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; 複数レコードを ユーザー単位に グルーピング
  14. 動いていたクエリ (標準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; グルーピングした結果で 更新の降順に ユーザー一覧をソート
  15. 動いていたクエリ (標準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違反 です
  16. 動いていたクエリ (標準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 違反
  17. 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
  18. 間違った前提条件 ✔ 集約・非集約カラムの 考慮漏れ   →GROUP BY 対象外のカラムは不定   →ゆえに 集約関数を使う必要 がある

    ✔ 標準SQL違反を (僕が) 知らなかった   →MySQL 5.6.5 より前では動く   →他の RDBMS ではエラー   →MySQL 5.6.5 以降では設定次第でエラー 38
  19. データ検証 クエリ 43 FROM users LEFT JOIN uploads ON uploads.user_id

    = users.id ✔ 1テーブルのJOIN  →ユーザーとファイルのテーブル
  20. データ検証 クエリ 44 GROUP BY uploads.user_id ORDER BY uploads.created DESC

    LIMIT 20; ✔ ONLY_FULL_GROUP_BY 違反  →この条件でどんな結果が返るのか
  21. 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回に分けて更新されている  →想定は最新のレコードの日付が欲しい
  22. 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回に分けて更新されている  →想定は最新のレコードの日付が欲しい スライドの都合上 ユーザーで 絞って表示
  23. 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枚画像を アップロード
  24. 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枚画像を アップロード
  25. 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回に分けて更新されている  →想定は最新のレコードの日付が欲しい ユーザーとして 最新の更新日付
  26. 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 | [email protected] | 2020-02-14 21:10:26 | +------------------+----------------------------+---------------------+ 1 row in set (0.01 sec)
  27. 51 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created

    | +------------------+----------------------------+---------------------+ | nickname_2177557 | [email protected] | 2020-02-14 21:10:26 | +------------------+----------------------------+---------------------+ 狙いとは 別のレコードの created
  28. 52 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created

    | +------------------+----------------------------+---------------------+ | nickname_2177557 | [email protected] | 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) 欲しかった レコード
  29. 53 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created

    | +------------------+----------------------------+---------------------+ | nickname_2177557 | [email protected] | 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) このうちの どれかになる
  30. データ検証まとめ ✔ 非集約カラムの値は 不定   →何が返るかわからない (ソートされない) ✔ クエリは 解釈順 が決まっている

      →GROUP BY の 後に ORDER BY ✔ 要件を 満たせない   →クエリを書き換える必要がある 54
  31. 要件の再整理 ✔ ユーザーを 更新順で降順 ソート   →uploads.created の最新で ORDER BY ✔

    GRUOP BY で グルーピング   →複数レコードを1つに ✔ 不定カラムは集約 して一定に   →RDBMS が返す値を正しく指定 57
  32. GROUP BY と 集約関数 不定な値のカラムを一定に ✔ 同じ値のカラムで グルーピング   →複数のデータを集約した計算結果が欲しい ✔

    不定な値の 返し方を指定   →最大値はMAX、最小値はMIN、集計はSUM、平均値はAVG、など RDBMS が 返せる値 になる 58
  33. 結果 修正したクエリ 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
  34. 結果 修正したクエリ 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 を サブクエリで グルーピング
  35. 結果 修正したクエリ 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() で 最大値を指定
  36. 結果 修正したクエリ 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
  37. 結果 修正したクエリ 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 最新日付で ソート
  38. データ検証 実行結果 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 | [email protected] | 2020-04-17 16:16:21 | +------------------+----------------------------+---------------------+ 1 row in set (1.30 sec)
  39. 66 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created

    | +------------------+----------------------------+---------------------+ | nickname_2177557 | [email protected] | 2020-04-17 16:16:21 | +------------------+----------------------------+---------------------+ 要件通りの created
  40. 67 データ検証 結果検証 +------------------+----------------------------+---------------------+ | nickname | email | created

    | +------------------+----------------------------+---------------------+ | nickname_2177557 | [email protected] | 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) 欲しかった カラム値
  41. 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 に複合インデックス作成  →インデックスのみでグルーピング、   集約を想定
  42. スロークエリ改善 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
  43. スロークエリ改善 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)
  44. スロークエリ改善 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)
  45. スロークエリ改善 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) 作成した インデックスが 選択されている
  46. スロークエリ改善 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) ルースインデックススキャン が適用されている
  47. スロークエリ改善 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
  48. スロークエリ改善 実際の実行結果 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)
  49. スロークエリ改善 実際の実行結果 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 の スロークエリが 改善された
  50. 終わりに ✔ 必ず 検証・計測 しよう   →運用保守を見越して品質を保証 ✔ 正しい クエリの解釈 をしよう

      →使い方を知ることで間違いを正す ✔ クエリを書ける ようになろう   →クエリありきでORMを利用 85
  51. 大切!日々の運用改善 運用は愛 ・ 後から変更は大変 ・ インフラは他人事じゃない ・ 0 -> 1

    以降も大事 ・ データの寿命はアプリより長い 88 100 から 1000 にするには 育てる愛情が必要
  52. 90