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
   

   View Slide

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

   View Slide

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

   View Slide

4. 　質問です 1
   4
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

20. 　質問です 2
    20
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

26. 26
    要件
    

    View Slide

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

    View Slide

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

    View Slide

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;
    

    View Slide

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 は数千万レコード
    

    View Slide

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;
    特定せずにユーザーの
    一覧を表示する (管理画面)
    

    View Slide

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句で
    絞り込めない
    

    View Slide

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;
    複数レコードを
    ユーザー単位に
    グルーピング
    

    View Slide

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;
    グルーピングした結果で
    更新の降順に
    ユーザー一覧をソート
    

    View Slide

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違反
    です
    

    View Slide

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 違反
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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)
    

    View Slide

51. 51
    データ検証
    結果検証
    +------------------+----------------------------+---------------------+
    | nickname | email | created |
    +------------------+----------------------------+---------------------+
    | nickname_2177557 | test+2177557@lancers.co.jp | 2020-02-14 21:10:26 |
    +------------------+----------------------------+---------------------+
    狙いとは
    別のレコードの
    created
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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 を
    サブクエリで
    グルーピング
    

    View Slide

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() で
    最大値を指定
    

    View Slide

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
    

    View Slide

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
    最新日付で
    ソート
    

    View Slide

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)
    

    View Slide

66. 66
    データ検証
    結果検証
    +------------------+----------------------------+---------------------+
    | nickname | email | created |
    +------------------+----------------------------+---------------------+
    | nickname_2177557 | test+2177557@lancers.co.jp | 2020-04-17 16:16:21 |
    +------------------+----------------------------+---------------------+
    要件通りの
    created
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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

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)
    

    View Slide

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)
    作成した
    インデックスが
    選択されている
    

    View Slide

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)
    ルースインデックススキャン
    が適用されている
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

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 の
    スロークエリが
    改善された
    

    View Slide

81. 81
    まとめ
    

    View Slide

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

    View Slide

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

    View Slide

84. 84
    終わりに
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

90. View Slide

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

    View Slide