TiDBにおけるSQLチューニング - SQL bindingの紹介

ウェビナー開催日：2023年4月13日

SQLをチューニングするときに実行計画の解析は不可欠な一部となります。通常、DBAやアプリケーション開発者は実行計画を参照して適切なSQLを模索します。しかし、実際に適用される実行計画はSQLの構文だけでなく、実行時点での統計情報によっても変わることがあるため、確実に意図した実行計画に基づいてSQLを実行することが難しい場合があります。

分散型データベースのTiDBには、SQL Plan Management (SPM) の1つにSQL bindingという機能があります。この機能によって、お客様は任意のSQLに対して適宜ヒント句を含めたSQLを紐付けたり、過去の実行計画を選択したりして、既存のSQLを変更することなくSQLの実行計画を最適化することが可能です。本ビデオではこのようなSQL bindingの機能や操作方法を紹介して、TiDBでのSQLチューニングに役立ていただくことを目指します。

2023年3月時点では最新となるv6.6-DMRにおいて、過去の履歴から実行計画を選択する機能がGAとなり、TiDB Dashboard上でも操作可能となりました。本ビデオではGUIでの操作方法のデモも行います。

なお、本スライドは過去に実施したセミナー「TiDBでのSQL実行の仕組み」を事前にご覧いただくと、より一層理解を深めることができるようになります。

トピック：
・SQL Planmanagement (SPM) とは何か
・TiDBにおけるSQLと実行計画
・SQL bindingの機能及び操作方法

アーカイブ動画：https://youtu.be/07RWDz1aQJY

Transcript

1. TiDBにおけるSQLチューニング
   - SQL bindingの紹介
   藤嶋 茂行
   Technical support engineer at PingCAP Japan
   

   View Slide

2. 本日の位置づけ
   ● https://pingcap.co.jp/event/
   ● https://pingcap.co.jp/category/webinar/
   
 

   2023年1月20日(金) 14:00-15:00 PingCAP Education：TiCDC、Dumpling、Lightningの紹介
   2023年2月3日(金) 14:00-15:00 TiDB 6.5 LTS新機能の紹介
   2023年2月17日(金) 14:00-15:00 PingCAP Education：TiDB CloudのIntegration機能の紹介 - Datadog、
   Prometheus、Vercelとの統合
   2023年3月2日(木) 14:00-15:00 TiDB Cloud Serverless TierとChatGPTのSQL生成を試してみよう！
   2023年3月24日(金) 14:00-15:00 TiFlashの紹介
   2023年4月13日(金) 14:00-15:00 TiDBにおけるSQLチューニング - SQL bindingの紹介
   2023年5月18日(木) 14:00-15:00 TiDB 7.0 DMR新機能の紹介
   ※過去開催アーカイブ　：　https://pingcap.co.jp/event-video/
   

   View Slide

3. 本セミナーに関するご注意事項
   ● 本セミナーは2023年4月13日時点のLTSであるv6.5に基づいています。
   ○ LTS: Long-Term Support Releases
   ● 最新の情報は公式ドキュメント https://docs.pingcap.com/tidbcloud/ をご参
   照ください。
   

   View Slide

4. アジェンダ
   ● TiDBにおけるSQLチューニングの考え方
   ● SQL Plan Management (SPM) とは何か
   ● SQL bindingの機能及び操作方法
   

   View Slide

5. TiDBにおけるSQLチューニングの考え方
   

   View Slide

6. TiDBにおけるSQLチューニングの考え方
   ● 実行計画に基づいたチューニング
   ○ 実行計画を理解する
   ○ SQLを見直す
   ○ インデックスを見直す
   ○ 統計情報を最新に更新する
   ● 実行計画を制御する
   ○ Hint句を利用する
   ○ SQL bindingを利用する
   

   View Slide

7. TiDBにおけるSQLチューニングの考え方
   ● 実行計画に基づいたチューニング
   ○ 実行計画を理解する
   ○ SQLを見直す
   ○ インデックスを見直す
   ○ 統計情報を最新に更新する
   ● 実行計画を制御する
   ○ Hint句を利用する
   ○ SQL bindingを利用する
   

   View Slide

8. 実行計画に基づいたチューニング - 実行計画を理解する
   ● EXPLAINの結果からSQLがどのように実行されるかを理解する
   ● EXPLAIN ANALYZEの結果から実際の実行状況を理解する
   

   View Slide

9. 実行計画に基づいたチューニング - 実行計画を理解する
   ● EXPLAINの結果からSQLがどのように実行されるかを理解する
   ● EXPLAIN ANALYZEの結果から実際の実行状況を理解する
   処理の概要
   処理されるデータの行数（概算）
   処理が実行される場所 処理に関する補足情報
   https://docs.pingcap.com/tidb/stable/explain-walkthrough
   

   View Slide

10. 実行計画に基づいたチューニング - 実行計画を理解する
    ● EXPLAINの結果からSQLがどのように実行されるかを理解する
    ● EXPLAIN ANALYZEの結果から実際の実行状況を理解する
    ● actRows: 実際に処理されたデータの行数
    ● execution info: 実際の処理状況
    ● memory, disk: 実際に利用されたリソース量
    https://docs.pingcap.com/tidb/stable/explain-walkthrough#assess-the-current-performance
    

    View Slide

11. 過去のウェビナーでTiDBのSQL実行計画について解説しています。
    実行計画に基づいたチューニング - 実行計画を理解する
    https://pingcap.co.jp/event-webinar-edu-sql-execution-thank-you/
    

    View Slide

12. TiDBにおけるSQLチューニングの考え方
    ● 実行計画に基づいたチューニング
    ○ 実行計画を理解する
    ○ SQLを見直す
    ■ WHERE句でのフィルタ条件の追加、SELECT句での列絞り込みなど
    ○ インデックスを見直す
    ○ 統計情報を最新に更新する
    ● 実行計画を制御する
    ○ Hint句を利用する
    ○ SQL bindingを利用する
    

    View Slide

13. TiDBにおけるSQLチューニングの考え方
    ● 実行計画に基づいたチューニング
    ○ 実行計画を理解する
    ○ SQLを見直す
    ○ インデックスを見直す
    ○ 統計情報を最新に更新する
    ● 実行計画を制御する
    ○ Hint句を利用する
    ○ SQL bindingを利用する
    

    View Slide

14. 実行計画に基づいたチューニング - インデックスを見直す
    ● 例えば、EXPLAINの結果からインデックスが利用されているかを見る
    https://docs.pingcap.com/tidb/stable/explain-walkthrough
    

    View Slide

15. 実行計画に基づいたチューニング - インデックスを見直す
    ● 例えば、EXPLAINの結果からインデックスが利用されているかを見る
    https://docs.pingcap.com/tidb/stable/explain-walkthrough
    インデックスを使った処理が無い
    インデックスにアクセスした様子が無い
    

    View Slide

16. 実行計画に基づいたチューニング - インデックスを見直す
    ● 例えば、EXPLAINの結果からインデックスが利用されているかを見る
    https://docs.pingcap.com/tidb/stable/explain-walkthrough
    authors.nameにインデックスを貼って
    ソートした状態で処理したほうが良さそう book_authors.author_idにインデックスがあると良さそう
    

    View Slide

17. 実行計画に基づいたチューニング - インデックスを見直す
    ● 例えば、EXPLAINの結果からインデックスが利用されているかを見る
    https://docs.pingcap.com/tidb/stable/explain-walkthrough
    author.nameでソートされたauthors.idと、book_author.autor_idが
    IndexHashJoinされるようになった
    

    View Slide

18. 実行計画に基づいたチューニング - インデックスを見直す
    過去のウェビナーでTiDBのインデックスチューニングについて解説しています。
    https://pingcap.co.jp/event-webinar-edu-sql-tuning-thank-you/
    

    View Slide

19. ● 実行計画に基づいたチューニング
    ○ 実行計画を理解する
    ○ SQLを見直す
    ○ インデックスを見直す
    ○ 統計情報を最新に更新する
    ■ ANALYZE TABLE
    ● 実行計画を制御する
    ○ Hint句を利用する
    ○ SQL bindingを利用する
    TiDBにおけるSQLチューニングの考え方
    

    View Slide

20. ● 実行計画に基づいたチューニング
    ○ 実行計画を理解する
    ○ SQLを見直す
    ○ インデックスを見直す
    ○ 統計情報を最新に更新する
    ● 実行計画を制御する
    ○ Hint句を利用する
    ■ Optimizerが任意の実行計画を生成するように誘導する
    ○ SQL bindingを利用する
    TiDBにおけるSQLチューニングの考え方
    

    View Slide

21. 過去のウェビナーでHint句について解説しています。
    実行計画に基づいたチューニング - 実行計画を理解する
    https://pingcap.co.jp/event-webinar-edu-sql-execution-thank-you/
    

    View Slide

22. ● 実行計画に基づいたチューニング
    ○ 実行計画を理解する
    ○ SQLを見直す
    ○ インデックスを見直す
    ○ 統計情報を最新に更新する
    ● 実行計画を制御する
    ○ Hint句を利用する
    ○ SQL bindingを利用する
    ■ SQL Plan Management (SPM)の１つ
    ● SQL binding, baseline capturing, そして baseline evolutionの３つ
    で構成される
    ■ アプリケーションやSQLを改変することなく、実行計画を強制する
    TiDBにおけるSQLチューニングの考え方
    

    View Slide

23. SQL Plan Management (SPM) とは何か
    

    View Slide

24. SQL Plan Management (SPM) とは何か
    ● SQLの実行計画に干渉するための機能
    ● SQL binding, baseline capturing, baseline evolutionの３つ
    ○ SQL binding
    ■ SQLに対して、任意の実行計画をbindする
    ○ Baseline capturing
    ■ キャプチャ条件を満たしている場合に、生成されたSQLの実行計画の履
    歴に基づいて自動的にbindを行う
    ○ Baseline evolution
    ■ 以前にbindした実行計画が最適でなくなると、自動的に最適化を行う
    ■ 現在は利用できない
    https://docs.pingcap.com/tidb/stable/sql-plan-management
    

    View Slide

25. SQL bindingの機能及び操作方法
    

    View Slide

26. SQL bindingの機能
    SQL bindingとは
    ● SQLに対して、Hint句を含めたSQLを割り当てることで任意の実行計画を生成さ
    せる
    ● アプリケーション側のSQLを修正することなく、SQLの挙動を変えることができる
    ● 過去に生成された実行計画から割り当てる実行計画を選ぶことができる
    https://docs.pingcap.com/tidb/stable/sql-plan-management#sql-binding
    

    View Slide

27. SQL bindingの機能
    https://docs.pingcap.com/tidb/stable/sql-plan-management#sql-binding
    投入されたSQLから実行計画を生成するまでの過程
    https://docs.pingcap.com/tidb/stable/sql-optimization-concepts/
    ● BindしたSQLへの置き換えはLogical
    Optimizeよりも前に行われる
    ● 正規化したSQLのhashをキーにして
    BindしたSQLをキャッシュの中から検索
    し、該当するSQLがあればASTの中にあ
    るHint句が置き換えられる
    SQLの正規化の例
    

    View Slide

28. SQL bindingの操作方法 - SQLを指定して作成
    ● bind可能なSQLは”SELECT”、 “DELETE”、 “UPDATE”、サブクエリ付き
    の”INSERT”または”REPLACE”の４つ
    ● Globalスコープで作成したbindingはmysqlスキーマの中のbind_infoの中に保
    存される
    ● SessionスコープのbindingはGlobalスコープのbindingをブロックする
    https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-sql-statement
    CREATE [GLOBAL|SESSION] BINDING for
    
    using
    
    

    View Slide

29. SQL bindingの操作方法 - SQLを指定して作成
    https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-sql-statement
    

    View Slide

30. SQL bindingの操作方法 - 履歴から作成
    ● “plan_digest”にはinformation_schemaスキーマのstatements_summaryテーブ
    ルなどにある”PLAN_DIGEST”の列の値をセットする
    ● statements_summaryテーブル以外では、以下のテーブルからplan_digestを検
    索することができる
    ○ statements_summary_history
    ○ cluster_statements_summary
    ○ cluster_statements_summary_history
    ○ statements_summary_evicted
    https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-historical-execution-plan
    CREATE [GLOBAL | SESSION] BINDING FROM
    HISTORY
    USING PLAN DIGEST 'plan_digest';
    

    View Slide

31. SQL bindingの操作方法 - 履歴から作成
    https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-historical-execution-plan
    

    View Slide

32. SQL bindingの操作方法 - 履歴から作成
    https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-historical-execution-plan
    ● v6.6以降ではTiDB Dashboardで実行計画の履歴を指定してbindingすることが
    可能（Experimentalです）
    

    View Slide

33. ● Sessionスコープのbindingと重複するGlobalスコープのbindingがあり、Sessionスコー
    プのbindingを削除した場合、Globalスコープのbindingは適用されないままとなる
    ● “plan_digest”にはinformation_schemaスキーマのstatements_summaryテーブルな
    どにある”PLAN_DIGEST”の列の値をセットする
    SQL bindingの操作方法 - 削除
    https://docs.pingcap.com/tidbcloud/sql-plan-management#remove-a-binding
    DROP [GLOBAL | SESSION] BINDING FOR BindableStmt;
    --- または
    DROP [GLOBAL | SESSION] BINDING FOR SQL DIGEST 'sql_digest';
    

    View Slide

34. ● 登録したBindingsに関する様々な情報を閲覧する
    ● LIKE ‘%foo%’ や WHERE default_db = ‘test’ などを指定して絞り込むことが可能
    SQL bindingの操作方法 - 閲覧
    https://docs.pingcap.com/tidbcloud/sql-plan-management#view-bindings
    SHOW [GLOBAL | SESSION] BINDINGS [ShowLikeOrWhere]
    

    View Slide

35. ● “SELECT @@[SESSION.]last_plan_from_binding;”では、直前に実行したSQLの実行計
    画がbindingしたものかどうかを知ることができる（0: false, 1: true）
    SQL bindingの操作方法 - 確認
    https://docs.pingcap.com/tidbcloud/sql-plan-management#troubleshoot-a-binding
    SELECT @@[SESSION.]last_plan_from_binding;
    EXPLAIN format = 'verbose' SELECT * FROM t;
    ● EXPLAIN実行後、bindingが使われた場合にwarningメッセージとして確認することが
    できる
    ○ 例: Note (Code 1105): Using the bindSQL: SELECT /*+ merge_join(`t1`,
    `t2`)*/ * FROM (`test`.`t1`) JOIN `test`.`t2` WHERE `t1`.`id` = `t2`.`id`
    

    View Slide

36. まとめ
    

    View Slide

37. まとめ
    ● TiDBにおけるSQLチューニングの復習をしました
    ○ 実行計画に基づいたチューニングを実施してください
    ○ 過去のウェビナーが参考になります
    ● SQL Plan Management の SQL binding を使って実行計画を固定させることがで
    きます
    ○ SQLやアプリケーションを書き換えることなく、SQLの動作を変えることができ
    ます
    

    View Slide

38. Q&A
    

    View Slide

39. Thank You!

    https://www.pingcap.com/

    info@pingcap.com

    

    View Slide