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

TiDBにおけるテーブル設計と最適化の事例

Cygames
May 10, 2024

 TiDBにおけるテーブル設計と最適化の事例

2024/03/08 TiDB GAME DAY 2024

Cygames

May 10, 2024
Tweet

More Decks by Cygames

Other Decks in Technology

Transcript

  1. 13/46 検証環境のテーブルとスペック Field Type Extra id bigint auto_increment user_id bigint

    ※アカウント作成時にランダムな値を発行 item_id bigint item_count bigint received tinyint(1) create_time datetime delete_time datetime PrimaryKey id, delete_time INDEX user_id, create_time TiDB Cloud TiDB (16 vCPU, 32 GiB) x2台, TiKV (16 vCPU, 64 GiB) x3台 Aurora db.r6g.4xlarge (16vCPU, 128GB) x1台
  2. 17/46 書き込みのボトルネック解消 Field Type Extra id bigint auto_random user_id bigint

    ※アカウント作成時にランダムな値を発行 item_id bigint item_count bigint received tinyint(1) create_time datetime delete_time datetime auto_increment から auto_random に変更 TiDBのベストプラクティスに変更
  3. 19/46 auto_randomの結果(書き込み) 書き込みのパフォーマンスが若干改善 1374.97 904.88 1205.89 0 500 1000 1500

    Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) プレゼント書き込み (QPS)
  4. 20/46 auto_randomの結果(読み込み) 読み込みのパフォーマンスはまだ課題 4422.81 728.27 794.38 0 1000 2000 3000

    4000 5000 Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) プレゼント参照 (QPS)
  5. 21/46 実行計画の確認 id actRows access object time Limit_13 100 70.7ms

    └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present Explain Analyze の結果(一部抜粋) select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500; TableRowIDScanの時間が多い
  6. 23/46 実行計画の確認 id actRows access object time Limit_13 100 70.7ms

    └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present Explain Analyze の結果(一部抜粋) 1 ①IndexからRowIDを取得 select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500;
  7. 24/46 実行計画の確認 Explain Analyze の結果(一部抜粋) id actRows access object time

    Limit_13 100 70.7ms └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present 2 ②RowIDをもとにレコードを1件ずつ取得 select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500;
  8. 25/46 実行計画の確認 Explain Analyze の結果(一部抜粋) id actRows access object time

    Limit_13 100 70.7ms └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present 3 ③取得したレコードを集計する select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500;
  9. 28/46 TableReaderになるように変更 Field Type Extra user_id bigint ※アカウント作成時にランダムな値を発行 ulid char(26)

    item_id bigint item_count bigint received tinyint(1) create_time datetime delete_time datetime PrimaryKey user_id, ulid, delete_time ①user_idで絞り込めるようにPKの先頭に追加 ②ulidで時間順に保存されるようにPKに含める
  10. 29/46 変更後の実行計画(ulid1) id actRows access object time Limit_12 100 13.3ms

    └─TableReader_25 600 13.3ms └─Limit_24 704 └─Selection_23 704 └─TableRangeScan_22 704 table:user_present 実行時間の短縮に成功! Explain Analyze の結果(一部抜粋) select * from user_present where user_id = ? and not received and delete_time = ? order by ulid desc limit 100 offset 500;
  11. 30/46 変更後の実行計画(ulid2) id actRows access object time Limit_11 100 4.1ms

    └─TableReader_20 100 4.09ms └─Limit_19 100 └─Selection_18 100 └─TableRangeScan_17 100 table:user_present 検索行数が減りスキャンがより効率的に ※仕様によっては適用できない Explain Analyze の結果(一部抜粋) select * from user_present where user_id = ? and ulid < ? not received and delete_time = ? order by ulid desc limit 100;
  12. 31/46 ulidの結果(読み込み) 読み込みのパフォーマンスが改善! 4422.81 728.27 794.38 17516.38 24294.22 0 10000

    20000 30000 Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) TiDB (ulid1) TiDB (ulid2) プレゼント参照 (QPS)
  13. 32/46 1374.97 904.88 1205.89 4078.99 0 2000 4000 6000 Aurora

    (auto_increment) TiDB (auto_increment) TiDB (auto_random) TiDB (ulid) プレゼント書き込み (QPS) ulidの結果(書き込み) 書き込みのパフォーマンスも改善! ULIDで改善したのは、INDEXがなくなったのが理由の一つと考えられる
  14. 35/46 ランキングの結果 104634.95 30797.64 77305.51 41360.63 user_idで一件取得 rankで範囲取得 QPS PRIMARY

    KEY rank UNIQUE KEY user_id ranking rank bigint user_id bigint score bigint PKがrank の場合 TableRangeScan になるので効率が良い UniqueKey より PrimaryKey の方が効率が良い PRIMARY KEY user_id INDEX rank 重視するクエリの効率が良い設計を選択