Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL5.7の機能で高速化した話
Search
akatsukinewgrad
December 17, 2021
0
950
MySQL5.7の機能で高速化した話
akatsukinewgrad
December 17, 2021
Tweet
Share
More Decks by akatsukinewgrad
See All by akatsukinewgrad
2023/1/25_QAテスター meet up!
akatsukinewgrad
0
100
成果発表資料.pdf
akatsukinewgrad
0
1.9k
広大なフィールドを気持ちよく駆け抜けるための技術.pdf
akatsukinewgrad
0
460
正規表現とReDoS.pdf
akatsukinewgrad
0
470
Unityで大量のオブジェクト_を吹き飛ばしたい.pdf
akatsukinewgrad
0
490
新卒2年目が思う1年目の学び.pdf
akatsukinewgrad
0
440
障害訓練の取り組みについて.pdf
akatsukinewgrad
0
560
7分でわかるアカツキゲームス
akatsukinewgrad
0
480
Bitcoinだけでスマートコントラクト.pdf
akatsukinewgrad
1
740
Featured
See All Featured
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
226
22k
The Straight Up "How To Draw Better" Workshop
denniskardys
232
140k
How to Ace a Technical Interview
jacobian
276
23k
Code Review Best Practice
trishagee
64
17k
Making Projects Easy
brettharned
115
5.9k
The Invisible Side of Design
smashingmag
298
50k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
38
1.8k
Optimizing for Happiness
mojombo
376
70k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
31
2.7k
Java REST API Framework Comparison - PWX 2021
mraible
PRO
28
8.2k
A designer walks into a library…
pauljervisheath
204
24k
Producing Creativity
orderedlist
PRO
341
39k
Transcript
MySQL5.7機能で高速化した話 Akatsuki サーバサイドエンジニア 柴原
自己紹介 •名前:柴原 •職種:20新卒サーバエンジニア •趣味:アイマス,コーヒー •近況:ダイエットしてます(食事制限に慣れるまでが辛い •ブーム:お酒を飲みながら 友人とアイマスライブ観戦 1/56
どれくらい速くなったの? •ユーザのミッション進捗を返すAPI •アクセス数による誤差をなくすため ほぼ同じアクセス数の平均レスポンスを比較すると... 2/56
どれくらい速くなったの? •ユーザのミッション進捗を返すAPI •アクセス数による誤差をなくすため ほぼ同じアクセス数の平均レスポンスを比較すると... 3/56 処理時間が半分になりました!!
どれくらい速くなったの? •ユーザのミッション進捗を返すAPI •アクセス数による誤差をなくすため ほぼ同じアクセス数の平均レスポンスを比較すると... 4/56 処理時間が半分になりました!!
どんな感じで高速化したの? 5/56
どんな感じで高速化したの? 6/56 実際に計測されたデータを確認
どんな感じで高速化したの? 7/56 実際に計測されたデータを確認 問題点の抽出
どんな感じで高速化したの? 8/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定
どんな感じで高速化したの? 9/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
手順1 10/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
実際に計測されたデータを確認 11/56 •見るべきポイント • 何が遅いのか • 大体DB関連で遅い • 呼び出されている回数 •
N+1問題が眠っているかもしれない • 詳しくは弊社の過去のGeek Liveの資料を参照してもらえると分かります
実際に計測されたデータを確認 12/56 •見るべきポイント • 何が遅いのか • 大体DB関連で遅い • 呼び出されている回数 •
N+1問題が眠っているかもしれない • 詳しくは弊社の過去のGeek Liveの資料を参照してもらえると分かります ユーザミッションを探すクエリが遅いことが判明
手順2 13/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
問題点の抽出 14/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった
問題点の抽出 15/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった
問題点の抽出 16/56 •クエリが複雑だった • デイリーミッションのようなミッションが重複して記録されている id user_id mission_id 1 123456789
1 2 123456789 2 3 123456789 1 4 123456789 1
問題点の抽出 17/56 •クエリが複雑だった • デイリーミッションのようなミッションが重複して記録されている • 重複しているミッションの中から最新のidを取得する必要があった id user_id mission_id
1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1
問題点の抽出 18/56 •クエリが複雑だった • デイリーミッションのようなミッションが重複して記録されている • 重複しているミッションの中から最新のidを取得する必要があった id user_id mission_id
1 123456789 1 2 123456789 2 3 123456789 1 4 123456789 1
問題点の抽出 19/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった
問題点の抽出 20/56 •返すミッションの量が多かった • 終了した過去のミッションまで返していた ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み
開催中 終了
問題点の抽出 21/56 •返すミッションの量が多かった • 終了した過去のミッションまで返していた • 赤の部分はひたすらに増えていく ミッション期間 未挑戦 挑戦中
報酬未受取 報酬受取済み 開催中 終了
問題点の抽出 22/56 •なぜ時間がかかっているのか • クエリが複雑だった • 返すミッションの量が多かった 複雑なクエリ×返すミッション量=Slow
手順3 23/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
実装方針の決定 24/56 •複雑なクエリ •大量のミッション
実装方針の決定 25/56 •複雑なクエリ •大量のミッション
実装方針の決定 26/56 •複雑なクエリ
実装方針の決定 27/56 •複雑なクエリ • 1ミッション1レコード id user_id mission_id 1 123456789
1 2 123456789 2 3 123456789 1 4 123456789 1
実装方針の決定 28/56 •複雑なクエリ • 1ミッション1レコード • 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
実装方針の決定 29/56 •複雑なクエリ •大量のミッション
実装方針の決定 30/56 •大量のミッション ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
実装方針の決定 31/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね → ミッション期間 未挑戦 挑戦中 報酬未受取
報酬受取済み 開催中 終了
実装方針の決定 32/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね →仕様を変えてもらうことで解決を図りました ミッション期間 未挑戦 挑戦中 報酬未受取
報酬受取済み 開催中 終了
実装方針の決定 33/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね →仕様を変えてもらうことで解決を図りました ミッション期間 未挑戦 挑戦中 報酬未受取
報酬受取済み 開催中 終了
実装方針の決定 34/56 •大量のミッション • 単純にどんどん増えてくるミッションを返したくないよね →仕様を変えてもらうことで解決を図りました • 赤の部分でひたすらに増えるところを減らせばいいじゃないと ミッション期間 未挑戦
挑戦中 報酬未受取 報酬受取済み 開催中 終了
ただし... 35/56
実装方針の決定 36/56 •大量のミッション • 緑色の場合は開催中のmission_idを指定できる →indexが効かせた高速な検索ができる • 赤色の部分の関係で全てのミッションを検索する必要が出てしまう ミッション期間 未挑戦
挑戦中 報酬未受取 報酬受取済み 開催中 終了
そこで使ったのが... 37/56
Virtual Generated Column Index 38/56
実装方針の決定 39/56 Generated Column MySQL5.7にてGenerated Column(生成カラム)が実装された あるカラムの値を別のカラムの値から自動的に計算する機能 JSON型のデータの検索などを速くするために誕生した (汎用的にも使える)
実装方針の決定 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)) );
実装方針の決定 41/56 Generated Column-例 INSERT INTO triangle (side_a, side_b) VALUES(3,4),(5,12),(1,1)
実装方針の決定 42/56 Generated Column-例 INSERT INTO triangle (side_a, side_b) VALUES(3,4),(5,12),(1,1) SELECT
* FROM triangle
実装方針の決定 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…...
実装方針の決定 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…...
実装方針の決定 45/56 VIRTUAL VS STORED VIRUTAL: カラムにアクセスした時に値を計算する STORED : 計算した値を実際に格納しておく
項目 VIRTUAL STORED アクセス速度 若干劣る 速い CPU消費 大 小 メモリ・ディスク消費 小 大 クラスタ,Rツリー,フルテキ ストインデックス No Yes セカンダリインデックス Yes Yes
実装方針の決定 46/56 Virtual Generated Column Indexとは Virtual Generated Column →アクセス時に計算しデータを格納しない生成カラム
実装方針の決定 47/56 Virtual Generated Column Indexとは Virtual Generated Column →アクセス時に計算しデータを格納しない生成カラム
Virtual Generated Column Index →唯一許可されている生成カラムを含むインデックスのこと
実装方針の決定 48/56 Virtual Generated Column Indexとは Virtual Generated Column →アクセス時に計算しデータを格納しない生成カラム
Virtual Generated Column Index →唯一許可されている生成カラムを含むインデックスのこと ただし,Virtualだと本来は値はselect時に計算されるが, Virtual Generated Column Indexにした場合はindexとして値が格納されていることに注意
実装方針の決定 49/56 結局どうしたかというと
実装方針の決定 50/56 結局どうしたかというと 報酬受取情報 IS NULL AND 達成日時 IS NOT
NULLを条件にした Virtual Generated Columnを作成 ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
実装方針の決定 51/56 結局どうしたかというと 報酬受取情報 IS NULL AND 達成日時 IS NOT
NULLを条件にした Virtual Generated Columnを作成 user_idと作成した生成カラムを含む複合インデックスを貼って ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
実装方針の決定 52/56 結局どうしたかというと 報酬受取情報 IS NULL AND 達成日時 IS NOT
NULLを条件にした Virtual Generated Columnを作成 user_idと作成した生成カラムを含む複合インデックスを貼って 青い四角の部分をindexを使って高速に検索!!! ミッション期間 未挑戦 挑戦中 報酬未受取 報酬受取済み 開催中 終了
手順4 53/56 実際に計測されたデータを確認 問題点の抽出 実装方針の決定 どれくらい改善されたか計測
どれくらい改善されたか計測 54/56 •本番環境に出すまでに開発環境で負荷テストをします •この段階でN+1をなくしたり他の問題がないか •遅くないか確認します
どれくらい改善されたか計測 55/56 •本番環境に出すまでに開発環境で負荷テストをします •この段階でN+1をなくしたり他の問題がないか •遅くないか確認します 本番環境にリリース!
まとめ データ構造を変えるだけなく,仕様を変えてもらうことで ある程度の問題を解決し, さらにMySQL5.7で実装された新機能を使って ミッションを返すAPIを高速しました 結果としてクエリも速くなり50%以上の高速化に成功しました 56/56