Clustered Index による会計レポート機能のパフォーマンス改善

Transcript

1. Clustered Index による会計レポート機能の パフォーマンス改善 中村⽂彌 2025年11⽉30⽇
2. 中村文彌
 2023年4月入社。2025年6月まで 会計レポート機能の開発を担当した のち、2025年7月からWF基盤の開 発チームに異動。
 統合モジュール開発部 エンジニア
3. 　 会計レポート機能が速くなりました
4. 　 会計レポート機能が速くなった 大規模事業所の p99 レイテンシ

5. 　 会計レポート機能が速くなった 大規模事業所の p99 レイテンシ
 30s 5s
6. 　 会計レポート機能が速くなった 全事業所の p99 レイテンシ

7. 　 会計レポート機能が速くなった 全事業所の p99 レイテンシ
 15s 5s
8. 　 今⽇のゴール 会計レポート機能が速くなった 技術的な背景を理解する
9. 1. レポート機能とは？
10. 　 レポート機能とは？ ⽇々⼊⼒される仕訳を集計し、 財務状況‧経営状況をチェックする機能
11. 　 レポート機能とは？
12. 2. 何が遅かったのか？
13. 　 レポート機能の仕組み 仕訳
 仕訳
 仕訳
 仕訳
 仕訳

14. 　 レポート機能の仕組み 仕訳
 仕訳
 仕訳
 仕訳
 集計テーブル
 集計テーブル
 集計テーブル
 更新のたびに
 非同期で集計
 仕訳

15. 　 レポート機能の仕組み 仕訳
 仕訳
 仕訳
 仕訳
 集計テーブル
 集計テーブル
 集計テーブル
 レポート
 更新のたびに
 非同期で集計
 リクエストのたびに
 同期的に集計
 仕訳

16. 　 レポート機能の仕組み 仕訳
 仕訳
 仕訳
 仕訳
 集計テーブル
 集計テーブル
 集計テーブル
 レポート
 更新のたびに
 非同期で集計
 リクエストのたびに
 同期的に集計
 仕訳
 ここが遅かった

17. 　 どんなクエリ？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目

18. 　 どんなクエリ？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目
 ①会計年度・勘定科目で絞り込む

19. 　 どんなクエリ？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目
 ②会計年度・勘定科目でグループ化

20. 　 どんなクエリ？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目
 ③金額カラムを足し合わせる

21. 　 EXPLAIN (MySQL) *************************** 1. row *************************** id: 1 select_type: SIMPLE table: 集計テーブル partitions: NULL type: ref possible_keys: <(会計年度, 勘定科目, その他)のユニークなセカンダリインデックス > key: <(会計年度, 勘定科目, その他)のユニークなセカンダリインデックス > key_len: 16 ref: const,const rows: 110164 filtered: 100.00 Extra: NULL
22. 　 EXPLAIN (MySQL) *************************** 1. row *************************** id: 1 select_type: SIMPLE table: 集計テーブル partitions: NULL type: ref possible_keys: <(会計年度, 勘定科目, その他)のユニークなセカンダリインデックス > key: <(会計年度, 勘定科目, その他)のユニークなセカンダリインデックス > key_len: 16 ref: const,const rows: 110164 filtered: 100.00 Extra: NULL rows の値は⼤きいが、それ以外は⼀⾒すると問題なさそう
23. 3. なぜ遅かったのか？
24. 　 クエリのパフォーマンス特性 SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目

25. 　 クエリのパフォーマンス特性 ⼤量のレコードを集計するため、 Buffer Pool に乗り切らない SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目

26. 　 クエリのパフォーマンス特性 ⼤量のレコードを集計するため、 Buffer Pool に乗り切らない →ディスクI/Oを最適化できるのではないか？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目

27. 　 B+Tree Indexを⽤いたレコードの検索
 Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Tom)
 rec(Rose)
 rec(Eric)
 rec(Bob)
 rec(Alice)
 rec(Jim)
 実レコードへのポインタ
 実レコードの並び

28. 　 B+Tree Indexを⽤いたレコードの検索
 Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Tom)
 rec(Rose)
 rec(Eric)
 rec(Bob)
 rec(Alice)
 rec(Jim)
 実レコードへのポインタ
 Eric以上Tom以下の レコードが欲しい！
 実レコードの並び

29. 　 B+Tree Indexを⽤いたレコードの検索
 Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Tom)
 rec(Rose)
 rec(Eric)
 rec(Bob)
 rec(Alice)
 rec(Jim)
 実レコードへのポインタ
 実レコードの並び
 Eric以上Tom以下の レコードが欲しい！

30. 　 B+Tree Indexを⽤いたレコードの検索
 Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Tom)
 rec(Rose)
 rec(Eric)
 rec(Bob)
 rec(Alice)
 rec(Jim)
 実レコードへのポインタ
 実レコードの並び
 Eric以上Tom以下の レコードが欲しい！

31. 　 B+Tree Indexを⽤いたレコードの検索
 Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Tom)
 rec(Rose)
 rec(Eric)
 rec(Bob)
 rec(Alice)
 rec(Jim)
 実レコードの並び
 実レコードへのポインタ
 ディスクへのランダムアクセスに なってしまう！ 
 Eric以上Tom以下の レコードが欲しい！

32. 　 ディスクへのランダムアクセスは遅い
 ⼀般に、ディスクへのアクセスは⼀定サイズのブロック単位で⾏われる。 シーケンシャル ランダム ディスクI/Oの回数 O(N/B) O(N) 定性的な説明 ⼀つのブロックに欲しいデータ がすべて含まれている ⼀つのブロックに欲しいデータ が⼀つしかない ディスクI/Oの回数（N: レコード数、B: ブロックサイズ）
33. 4. どうすれば速くなるか？
34. 　 Clustered Index 実レコードをインデックスのキーと同じ順序で並べることで、 実レコードへのアクセスをシーケンシャルにする
35. 　 Clustered Index Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Alice)
 rec(Bob)
 rec(Eric)
 rec(Jim)
 rec(Rose)
 rec(Tom)
 実レコードへのポインタ
 実レコードの並び
 Eric以上Tom以下の レコードが欲しい！

36. 　 Clustered Index Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Alice)
 rec(Bob)
 rec(Eric)
 rec(Jim)
 rec(Rose)
 rec(Tom)
 実レコードへのポインタ
 実レコードの並び
 Eric以上Tom以下の レコードが欲しい！

37. 　 Clustered Index Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 key(Alice)
 key(Bob)
 key(Eric)
 key(Jim)
 key(Rose)
 key(Tom)
 rec(Alice)
 rec(Bob)
 rec(Eric)
 rec(Jim)
 rec(Rose)
 rec(Tom)
 実レコードへのポインタ
 ディスクへのアクセスが 
 シーケンシャルになる！ 
 実レコードの並び
 Eric以上Tom以下の レコードが欲しい！

38. 　 MySQLにおけるClustered Index MySQLでは、主キーがClustered Indexとなり、 leaf nodeに実レコードが格納される形で実現される
39. 　 MySQLにおけるClustered Index Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 rec(Alice)
 rec(Bob)
 rec(Eric)
 rec(Jim)
 rec(Rose)
 rec(Tom)
 実レコード
 Eric以上Tom以下の レコードが欲しい！

40. 　 MySQLにおけるClustered Index Alice
 Woo
 Zark
 Alice
 Eric
 Rose
 Alice
 Bob
 Eric
 Jim
 Rose
 Tom
 rec(Alice)
 rec(Bob)
 rec(Eric)
 rec(Jim)
 rec(Rose)
 rec(Tom)
 実レコード
 leaf nodeだけでクエリの結果を 返すことができる！ 
 Eric以上Tom以下の レコードが欲しい！

41. 　 今回のケースでClustered Indexを使⽤できるか？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目

42. 　 今回のケースでClustered Indexを使⽤できるか？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目
 集計テーブルには (会計年度, 勘定科⽬, その他) のユニークなセカンダリイ ンデックスが張られている
43. 　 今回のケースでClustered Indexを使⽤できるか？ SELECT SUM(残高・発生額) FROM 集計テーブル WHERE 会計年度 = ? AND 勘定科目 IN (?, ..., ?) GROUP BY 会計年度, 勘定科目
 集計テーブルには (会計年度, 勘定科⽬, その他) のユニークなセカンダリイ ンデックスが張られている → (会計年度, 勘定科⽬, その他) を主キーとする新しいテーブルを作れば良 い
44. 　 新しい集計テーブルの導⼊ 仕訳
 仕訳
 仕訳
 仕訳
 集計テーブル
 集計テーブル
 集計テーブル
 レポート
 更新のたびに
 非同期で集計
 リクエストのたびに
 同期的に集計
 仕訳

45. 　 新しい集計テーブルの導⼊ 仕訳
 仕訳
 仕訳
 仕訳
 集計テーブル
 集計テーブル
 集計テーブル
 レポート
 更新のたびに
 非同期で集計
 リクエストのたびに
 同期的に集計
 仕訳
 集計テーブルV2
 集計テーブルV2
 集計テーブルV2

46. 　 新しい集計テーブルの導⼊ 仕訳
 仕訳
 仕訳
 仕訳
 集計テーブル
 集計テーブル
 集計テーブル
 レポート
 更新のたびに
 非同期で集計
 リクエストのたびに
 同期的に集計
 仕訳
 集計テーブルV2
 集計テーブルV2
 集計テーブルV2
 新旧テーブルに double write し つつ、レポートの集計は新テーブ ルに切り替える

47. 5. 結果
48. 　 会計レポート機能が速くなった 大規模事業所の p99 レイテンシ

49. 　 会計レポート機能が速くなった 大規模事業所の p99 レイテンシ
 30s 5s
50. 　 会計レポート機能が速くなった 全事業所の p99 レイテンシ

51. 　 会計レポート機能が速くなった 全事業所の p99 レイテンシ
 15s 5s
52. 6. まとめ
53. 　 まとめ 会計レポート機能におけるパフォーマンス改善の事例を紹介した この事例から学べること • EXPLAIN の結果が問題ないように⾒えても、ディスクI/Oを最適化でき る可能性がある • ディスクI/Oを最適化するための⼿法として、Clustered Index がある ◦ 実は Covering Index という⼿法もあるが、時間の都合で省略した