MySQL5.7の機能で高速化した話

Transcript

1. MySQL5.7機能で高速化した話 Akatsuki サーバサイドエンジニア 柴原
2. 自己紹介 •名前：柴原 •職種：20新卒サーバエンジニア •趣味：アイマス，コーヒー •近況：ダイエットしてます(食事制限に慣れるまでが辛い •ブーム：お酒を飲みながら 　　　　 友人とアイマスライブ観戦 1/56
3. どれくらい速くなったの？ •ユーザのミッション進捗を返すAPI •アクセス数による誤差をなくすため ほぼ同じアクセス数の平均レスポンスを比較すると... 2/56
4. どれくらい速くなったの？ •ユーザのミッション進捗を返すAPI •アクセス数による誤差をなくすため ほぼ同じアクセス数の平均レスポンスを比較すると... 3/56 処理時間が半分になりました！！
5. どれくらい速くなったの？ •ユーザのミッション進捗を返すAPI •アクセス数による誤差をなくすため ほぼ同じアクセス数の平均レスポンスを比較すると... 4/56 処理時間が半分になりました！！
6. どんな感じで高速化したの？ 5/56
7. どんな感じで高速化したの？ 6/56 実際に計測されたデータを確認
8. どんな感じで高速化したの？ 7/56 実際に計測されたデータを確認 問題点の抽出
9. どんな感じで高速化したの？ 8/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定
10. どんな感じで高速化したの？ 9/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
11. 手順１ 10/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
12. 実際に計測されたデータを確認 11/56 •見るべきポイント • 何が遅いのか • 大体DB関連で遅い • 呼び出されている回数 • N+1問題が眠っているかもしれない • 詳しくは弊社の過去のGeek Liveの資料を参照してもらえると分かります
13. 実際に計測されたデータを確認 12/56 •見るべきポイント • 何が遅いのか • 大体DB関連で遅い • 呼び出されている回数 • N+1問題が眠っているかもしれない • 詳しくは弊社の過去のGeek Liveの資料を参照してもらえると分かります ユーザミッションを探すクエリが遅いことが判明
14. 手順２ 13/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
15. 問題点の抽出 14/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった
16. 問題点の抽出 15/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった
17. 問題点の抽出 16/56 •クエリが複雑だった • デイリーミッションのようなミッションが重複して記録されている id user_id mission_id 1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1
18. 問題点の抽出 17/56 •クエリが複雑だった • デイリーミッションのようなミッションが重複して記録されている • 重複しているミッションの中から最新のidを取得する必要があった id user_id mission_id 1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1
19. 問題点の抽出 18/56 •クエリが複雑だった • デイリーミッションのようなミッションが重複して記録されている • 重複しているミッションの中から最新のidを取得する必要があった id user_id mission_id 1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1
20. 問題点の抽出 19/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった
21. 問題点の抽出 20/56 •返すミッションの量が多かった • 終了した過去のミッションまで返していた ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
22. 問題点の抽出 21/56 •返すミッションの量が多かった • 終了した過去のミッションまで返していた • 赤の部分はひたすらに増えていく ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
23. 問題点の抽出 22/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった 複雑なクエリ×返すミッション量＝Slow
24. 手順３ 23/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
25. 実装方針の決定 24/56 •複雑なクエリ •大量のミッション
26. 実装方針の決定 25/56 •複雑なクエリ •大量のミッション
27. 実装方針の決定 26/56 •複雑なクエリ
28. 実装方針の決定 27/56 •複雑なクエリ • １ミッション１レコード id user_id mission_id 1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1
29. 実装方針の決定 28/56 •複雑なクエリ • １ミッション１レコード • user_idとmission_idをUNIQUEに id user_id mission_id 1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1 id user_id mission_id 2 123456789 2 4 123456789 1
30. 実装方針の決定 29/56 •複雑なクエリ •大量のミッション
31. 実装方針の決定 30/56 •大量のミッション ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
32. 実装方針の決定 31/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね → ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
33. 実装方針の決定 32/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね →仕様を変えてもらうことで解決を図りました ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
34. 実装方針の決定 33/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね →仕様を変えてもらうことで解決を図りました ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
35. 実装方針の決定 34/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね →仕様を変えてもらうことで解決を図りました • 赤の部分でひたすらに増えるところを減らせばいいじゃないと ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
36. ただし... 35/56
37. 実装方針の決定 36/56 •大量のミッション • 緑色の場合は開催中のmission_idを指定できる →indexが効かせた高速な検索ができる • 赤色の部分の関係で全てのミッションを検索する必要が出てしまう ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
38. そこで使ったのが... 37/56
39. Virtual Generated Column Index 38/56
40. 実装方針の決定 39/56 Generated Column MySQL5.7にてGenerated Column(生成カラム)が実装された あるカラムの値を別のカラムの値から自動的に計算する機能 JSON型のデータの検索などを速くするために誕生した (汎用的にも使える)
41. 実装方針の決定 40/56 Generated Column-例 CREATE TABLE triangle ( side_a DOUBLE, side_b DOUBLE, # c = √a2 + b2 side_c DOUBLE AS (SQRT(side_a * side_a + side_b *side_b)) );
42. 実装方針の決定 41/56 Generated Column-例 INSERT INTO triangle (side_a, side_b)　VALUES(3,4),(5,12),(1,1)
43. 実装方針の決定 42/56 Generated Column-例 INSERT INTO triangle (side_a, side_b)　VALUES(3,4),(5,12),(1,1) SELECT * FROM triangle
44. 実装方針の決定 43/56 Generated Column-例 INSERT INTO triangle (side_a, side_b)　VALUES(3,4),(5,12),(1,1) SELECT * FROM triangle side_a side_b side_c 3 4 5 5 12 13 1 1 1.41421356…...
45. 実装方針の決定 44/56 Generated Column-例 INSERT INTO triangle (side_a, side_b)　VALUES(3,4),(5,12),(1,1) SELECT * FROM triangle side_a side_b side_c 3 4 5 5 12 13 1 1 1.41421356…...
46. 実装方針の決定 45/56 VIRTUAL VS STORED VIRUTAL: カラムにアクセスした時に値を計算する STORED : 計算した値を実際に格納しておく 項目 VIRTUAL STORED アクセス速度 若干劣る 速い CPU消費 大 小 メモリ・ディスク消費 小 大 クラスタ，Rツリー，フルテキ ストインデックス No Yes セカンダリインデックス Yes Yes
47. 実装方針の決定 46/56 Virtual Generated Column Indexとは Virtual Generated Column →アクセス時に計算しデータを格納しない生成カラム
48. 実装方針の決定 47/56 Virtual Generated Column Indexとは Virtual Generated Column →アクセス時に計算しデータを格納しない生成カラム Virtual Generated Column Index →唯一許可されている生成カラムを含むインデックスのこと
49. 実装方針の決定 48/56 Virtual Generated Column Indexとは Virtual Generated Column →アクセス時に計算しデータを格納しない生成カラム Virtual Generated Column Index →唯一許可されている生成カラムを含むインデックスのこと ただし，Virtualだと本来は値はselect時に計算されるが， Virtual Generated Column Indexにした場合はindexとして値が格納されていることに注意
50. 実装方針の決定 49/56 結局どうしたかというと
51. 実装方針の決定 50/56 結局どうしたかというと 報酬受取情報 IS NULL AND 達成日時 IS NOT NULLを条件にした Virtual Generated Columnを作成 ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
52. 実装方針の決定 51/56 結局どうしたかというと 報酬受取情報 IS NULL AND 達成日時 IS NOT NULLを条件にした Virtual Generated Columnを作成 user_idと作成した生成カラムを含む複合インデックスを貼って ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
53. 実装方針の決定 52/56 結局どうしたかというと 報酬受取情報 IS NULL AND 達成日時 IS NOT NULLを条件にした Virtual Generated Columnを作成 user_idと作成した生成カラムを含む複合インデックスを貼って 青い四角の部分をindexを使って高速に検索！！！ ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
54. 手順４ 53/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
55. どれくらい改善されたか計測 54/56 •本番環境に出すまでに開発環境で負荷テストをします •この段階でN＋1をなくしたり他の問題がないか •遅くないか確認します
56. どれくらい改善されたか計測 55/56 •本番環境に出すまでに開発環境で負荷テストをします •この段階でN＋1をなくしたり他の問題がないか •遅くないか確認します 本番環境にリリース！
57. まとめ データ構造を変えるだけなく，仕様を変えてもらうことで ある程度の問題を解決し， さらにMySQL5.7で実装された新機能を使って ミッションを返すAPIを高速しました 結果としてクエリも速くなり50%以上の高速化に成功しました 56/56