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

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

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

    View full-size slide

  2. Twitter
    Work
    Like
    ✔ 2017年の趣味:MySQL
    ✔ 2018年の趣味:DNS
    ✔ 2020年の趣味:業務に没頭 (CakePHP4)
    :@mamy1326(まみー)
    :Lancers,Inc. @ PHPer
    :cune.jp
    自己紹介
    2

    View full-size slide

  3. ✔ MySQL ユーザー
      →他の RDBMS だと 標準SQL違反
    ✔ 巨大なデータ を扱う人
      →1000万件以上 の テーブルJOIN
    ✔ グループ化とソート を一緒にやりたい
      →1 対 n のテーブルJOIN
    想定オーディエンスのみなさま
    3

    View full-size slide

  4.  質問です 1
    4

    View full-size slide

  5. 5
    1 対 n で JOIN
    したことある人✋

    View full-size slide

  6. 6
    n 側 で GROUP BY
    したことある人✋

    View full-size slide

  7. 起きていたこと
    ✔ スロークエリ ( 180 sec )
      →1 対 n のJOIN
    7

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  10. 起きていたこと
    ✔ スロークエリ ( 180 sec )
      →1 対 n のJOIN
    ✔ インデックスで 速度改善せず
      →別の複合インデックスが選択された
    ✔ そもそも クエリが間違って いた
      →ONLY_FULL_GROUP_BY 違反 (後述)
    10
    データで検証し
    解決までをお話しします

    View full-size slide

  11. 間違いに気づいた理由
    11

    View full-size slide

  12. 間違いに気づいた理由
    12
    クエリと
    インデックスの解釈が
    間違っていた

    View full-size slide

  13. 間違いに気づいた理由
    13
    以前の職場の同僚で
    凄腕DBA

    View full-size slide

  14. 間違いに気づいた理由
    14
    ポジティブフィードバックに
    全力で感謝

    View full-size slide

  15. 今日の目標
    やろう!データ検証
    直そう!間違ったクエリ
    改善!スロークエリ
    15

    View full-size slide

  16. 今日の目標
    16
    やろう!データ検証
    直そう!間違ったクエリ
    改善!スロークエリ

    View full-size slide

  17. 環境
    Amazon Aurora
    17
    mysql> select AURORA_VERSION();
    +------------------+
    | AURORA_VERSION() |
    +------------------+
    | 2.04.5 |
    +------------------+
    1 row in set (0.00 sec)

    View full-size slide

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

    View full-size slide

  19. 環境
    スロークエリ検出時間
    19
    mysql> show variables like 'long%';
    +-----------------+-----------+
    | Variable_name | Value |
    +-----------------+-----------+
    | long_query_time | 10.000000 |
    +-----------------+-----------+
    1 row in set (0.01 sec)

    View full-size slide

  20.  質問です 2
    20

    View full-size slide

  21. 21
    正直
    雰囲気で ORM を
    使っている!✋

    View full-size slide

  22. 22
    正直
    本番と同じ構成で
    データ検証していない✋

    View full-size slide

  23. 23
    そうだ
    データ検証 (検証環境)
    しよう

    View full-size slide

  24. データ検証の目的
    ✔ 要件通りのデータが取れているか
      →自作のテストデータは 必ず抜け漏れる
    24

    View full-size slide

  25. データ検証の目的
    ✔ 要件通りのデータが取れているか
      →自作のテストデータは 必ず抜け漏れる
    ✔ 速度が著しく低下しないか
      →本番で 動くクエリが ジャスティスだ
    25

    View full-size slide

  26. 要件
    ✔ ユーザーを 最新更新順 にソート
      →users テーブルが親
    ✔ ファイルはユーザー情報 の一部
      →uploads テーブルが子
    27

    View full-size slide

  27. 28
    検証の前に
    間違ったクエリ解説

    View full-size slide

  28. 動いていたクエリ (標準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;

    View full-size slide

  29. 動いていたクエリ (標準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 は数千万レコード

    View full-size slide

  30. 動いていたクエリ (標準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;
    特定せずにユーザーの
    一覧を表示する (管理画面)

    View full-size slide

  31. 動いていたクエリ (標準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句で
    絞り込めない

    View full-size slide

  32. 動いていたクエリ (標準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;
    複数レコードを
    ユーザー単位に
    グルーピング

    View full-size slide

  33. 動いていたクエリ (標準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;
    グルーピングした結果で
    更新の降順に
    ユーザー一覧をソート

    View full-size slide

  34. 動いていたクエリ (標準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違反
    です

    View full-size slide

  35. 動いていたクエリ (標準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 違反

    View full-size slide

  36. 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

    View full-size slide

  37. 間違った前提条件
    ✔ 集約・非集約カラムの 考慮漏れ
      →GROUP BY 対象外のカラムは不定
      →ゆえに 集約関数を使う必要 がある
    ✔ 標準SQL違反を (僕が) 知らなかった
      →MySQL 5.6.5 より前では動く
      →他の RDBMS ではエラー
      →MySQL 5.6.5 以降では設定次第でエラー
    38

    View full-size slide

  38. 39
    そもそも
    クエリが間違って いた

    View full-size slide

  39. 40
    標準SQL違反でも
    パッと見 は
    データが 正しく見えた

    View full-size slide

  40. 41
    まずは
    間違った クエリを
    検証 します

    View full-size slide

  41. データ検証
    クエリ
    42
    SELECT
    users.nickname,
    users.email,
    uploads.created
    ✔ 便宜上、必要そうなカラム指定
     →実際は利用カラムのみ指定

    View full-size slide

  42. データ検証
    クエリ
    43
    FROM
    users
    LEFT JOIN uploads
    ON uploads.user_id = users.id
    ✔ 1テーブルのJOIN
     →ユーザーとファイルのテーブル

    View full-size slide

  43. データ検証
    クエリ
    44
    GROUP BY
    uploads.user_id
    ORDER BY
    uploads.created DESC
    LIMIT 20;
    ✔ ONLY_FULL_GROUP_BY 違反
     →この条件でどんな結果が返るのか

    View full-size slide

  44. 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回に分けて更新されている
     →想定は最新のレコードの日付が欲しい

    View full-size slide

  45. 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回に分けて更新されている
     →想定は最新のレコードの日付が欲しい
    スライドの都合上
    ユーザーで
    絞って表示

    View full-size slide

  46. 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枚画像を
    アップロード

    View full-size slide

  47. 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枚画像を
    アップロード

    View full-size slide

  48. 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回に分けて更新されている
     →想定は最新のレコードの日付が欲しい
    ユーザーとして
    最新の更新日付

    View full-size slide

  49. 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)

    View full-size slide

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

    View full-size slide

  51. 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)
    欲しかった
    レコード

    View full-size slide

  52. 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)
    このうちの
    どれかになる

    View full-size slide

  53. データ検証まとめ
    ✔ 非集約カラムの値は 不定
      →何が返るかわからない (ソートされない)
    ✔ クエリは 解釈順 が決まっている
      →GROUP BY の 後に ORDER BY
    ✔ 要件を 満たせない
      →クエリを書き換える必要がある
    54

    View full-size slide

  54. 55
    間違っている ことが
    わかった ので…

    View full-size slide

  55. 今日の目標
    56
    やろう!データ検証
    直そう!間違ったクエリ
    改善!スロークエリ

    View full-size slide

  56. 要件の再整理
    ✔ ユーザーを 更新順で降順 ソート
      →uploads.created の最新で ORDER BY
    ✔ GRUOP BY で グルーピング
      →複数レコードを1つに
    ✔ 不定カラムは集約 して一定に
      →RDBMS が返す値を正しく指定
    57

    View full-size slide

  57. GROUP BY と 集約関数
    不定な値のカラムを一定に
    ✔ 同じ値のカラムで グルーピング
      →複数のデータを集約した計算結果が欲しい
    ✔ 不定な値の 返し方を指定
      →最大値はMAX、最小値はMIN、集計はSUM、平均値はAVG、など
    RDBMS が 返せる値 になる
    58

    View full-size slide

  58. やること
    ✔ サブクエリを使う
    ✔ uploads をグルーピング・集約
    ✔ 結果を users と JOIN
    ✔ uploads.created で ORDER BY
    59

    View full-size slide

  59. 結果
    修正したクエリ
    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

    View full-size slide

  60. 結果
    修正したクエリ
    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 を
    サブクエリで
    グルーピング

    View full-size slide

  61. 結果
    修正したクエリ
    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() で
    最大値を指定

    View full-size slide

  62. 結果
    修正したクエリ
    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

    View full-size slide

  63. 結果
    修正したクエリ
    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
    最新日付で
    ソート

    View full-size slide

  64. データ検証
    実行結果
    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)

    View full-size slide

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

    View full-size slide

  66. 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)
    欲しかった
    カラム値

    View full-size slide

  67. 68
    無事に
    要件を満たした✨

    View full-size slide

  68. 69
    しかしまだ
    課題 がある

    View full-size slide

  69. やろう!データ検証
    直そう!間違ったクエリ
    改善!スロークエリ
    今日の目標
    70

    View full-size slide

  70. 71
    お時間の都合で
    ポイントのみ
    お話しします

    View full-size slide

  71. 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 に複合インデックス作成
     →インデックスのみでグルーピング、
      集約を想定

    View full-size slide

  72. スロークエリ改善
    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

    View full-size slide

  73. スロークエリ改善
    EXPLAIN 結果
    74
    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY
    table:
    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)

    View full-size slide

  74. スロークエリ改善
    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)

    View full-size slide

  75. スロークエリ改善
    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)
    作成した
    インデックスが
    選択されている

    View full-size slide

  76. スロークエリ改善
    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)
    ルースインデックススキャン
    が適用されている

    View full-size slide

  77. スロークエリ改善
    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

    View full-size slide

  78. スロークエリ改善
    実際の実行結果
    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)

    View full-size slide

  79. スロークエリ改善
    実際の実行結果
    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 の
    スロークエリが
    改善された

    View full-size slide

  80. まとめ
    ✔ データ検証を通して 要件を検証
      →結果が間違っていることを証明
    ✔ 間違いを修正し要件を達成
      →標準SQL違反の理解と正しいクエリ実装
    ✔ インデックスでパフォーマンス改善
      →180 sec -> 1.3 sec
    82

    View full-size slide

  81. 今日の目標 (ふりかえり)
    やろう!データ検証
    直そう!間違ったクエリ
    改善!スロークエリ
    83

    View full-size slide

  82. 84
    終わりに

    View full-size slide

  83. 終わりに
    ✔ 必ず 検証・計測 しよう
      →運用保守を見越して品質を保証
    ✔ 正しい クエリの解釈 をしよう
      →使い方を知ることで間違いを正す
    ✔ クエリを書ける ようになろう
      →クエリありきでORMを利用
    85

    View full-size slide

  84. 86
    ポジティブフィードバックは
    人生の宝物 ✨

    View full-size slide

  85. 大切!日々の運用改善
    運用は愛
    ・ 後から変更は大変
    ・ インフラは他人事じゃない
    ・ 0 -> 1 以降も大事
    ・ データの寿命はアプリより長い
    87

    View full-size slide

  86. 大切!日々の運用改善
    運用は愛
    ・ 後から変更は大変
    ・ インフラは他人事じゃない
    ・ 0 -> 1 以降も大事
    ・ データの寿命はアプリより長い
    88
    100 から 1000 にするには
    育てる愛情が必要

    View full-size slide

  87. 愛を持って
    運用改善しましょう!
    89

    View full-size slide

  88. MySQL! MySQL!
    #F0F0F0 #666666 #E6855E #5EC84E
    #F0F0F0  あいうえおかきくけこさしすせそ
    #E6855E   あいうえおかきくけこさしすせそ
    #5EC84E  あいうえおかきくけこさしすせそ

    View full-size slide