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/gxOHwf6pPFA

Transcript

1. TiDBにおけるSQLチューニング - SQL bindingの紹介 藤嶋 茂行 Technical support engineer at PingCAP Japan
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/
3. 本セミナーに関するご注意事項 • 本セミナーは2023年4月13日時点のLTSであるv6.5に基づいています。 ◦ LTS: Long-Term Support Releases • 最新の情報は公式ドキュメント https://docs.pingcap.com/tidbcloud/ をご参 照ください。
4. アジェンダ • TiDBにおけるSQLチューニングの考え方 • SQL Plan Management (SPM) とは何か • SQL bindingの機能及び操作方法
5. TiDBにおけるSQLチューニングの考え方
6. TiDBにおけるSQLチューニングの考え方 • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ◦ インデックスを見直す ◦ 統計情報を最新に更新する • 実行計画を制御する ◦ Hint句を利用する ◦ SQL bindingを利用する
7. TiDBにおけるSQLチューニングの考え方 • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ◦ インデックスを見直す ◦ 統計情報を最新に更新する • 実行計画を制御する ◦ Hint句を利用する ◦ SQL bindingを利用する
8. 実行計画に基づいたチューニング - 実行計画を理解する • EXPLAINの結果からSQLがどのように実行されるかを理解する • EXPLAIN ANALYZEの結果から実際の実行状況を理解する
9. 実行計画に基づいたチューニング - 実行計画を理解する • EXPLAINの結果からSQLがどのように実行されるかを理解する • EXPLAIN ANALYZEの結果から実際の実行状況を理解する 処理の概要 処理されるデータの行数（概算） 処理が実行される場所 処理に関する補足情報 https://docs.pingcap.com/tidb/stable/explain-walkthrough
10. 実行計画に基づいたチューニング - 実行計画を理解する • EXPLAINの結果からSQLがどのように実行されるかを理解する • EXPLAIN ANALYZEの結果から実際の実行状況を理解する • actRows: 実際に処理されたデータの行数 • execution info: 実際の処理状況 • memory, disk: 実際に利用されたリソース量 https://docs.pingcap.com/tidb/stable/explain-walkthrough#assess-the-current-performance
11. 過去のウェビナーでTiDBのSQL実行計画について解説しています。 実行計画に基づいたチューニング - 実行計画を理解する https://pingcap.co.jp/event-webinar-edu-sql-execution-thank-you/
12. TiDBにおけるSQLチューニングの考え方 • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ▪ WHERE句でのフィルタ条件の追加、SELECT句での列絞り込みなど ◦ インデックスを見直す ◦ 統計情報を最新に更新する • 実行計画を制御する ◦ Hint句を利用する ◦ SQL bindingを利用する
13. TiDBにおけるSQLチューニングの考え方 • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ◦ インデックスを見直す ◦ 統計情報を最新に更新する • 実行計画を制御する ◦ Hint句を利用する ◦ SQL bindingを利用する
14. 実行計画に基づいたチューニング - インデックスを見直す • 例えば、EXPLAINの結果からインデックスが利用されているかを見る https://docs.pingcap.com/tidb/stable/explain-walkthrough
15. 実行計画に基づいたチューニング - インデックスを見直す • 例えば、EXPLAINの結果からインデックスが利用されているかを見る https://docs.pingcap.com/tidb/stable/explain-walkthrough インデックスを使った処理が無い インデックスにアクセスした様子が無い
16. 実行計画に基づいたチューニング - インデックスを見直す • 例えば、EXPLAINの結果からインデックスが利用されているかを見る https://docs.pingcap.com/tidb/stable/explain-walkthrough authors.nameにインデックスを貼って ソートした状態で処理したほうが良さそう book_authors.author_idにインデックスがあると良さそう
17. 実行計画に基づいたチューニング - インデックスを見直す • 例えば、EXPLAINの結果からインデックスが利用されているかを見る https://docs.pingcap.com/tidb/stable/explain-walkthrough author.nameでソートされたauthors.idと、book_author.autor_idが IndexHashJoinされるようになった
18. 実行計画に基づいたチューニング - インデックスを見直す 過去のウェビナーでTiDBのインデックスチューニングについて解説しています。 https://pingcap.co.jp/event-webinar-edu-sql-tuning-thank-you/
19. • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ◦ インデックスを見直す ◦ 統計情報を最新に更新する ▪ ANALYZE TABLE • 実行計画を制御する ◦ Hint句を利用する ◦ SQL bindingを利用する TiDBにおけるSQLチューニングの考え方
20. • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ◦ インデックスを見直す ◦ 統計情報を最新に更新する • 実行計画を制御する ◦ Hint句を利用する ▪ Optimizerが任意の実行計画を生成するように誘導する ◦ SQL bindingを利用する TiDBにおけるSQLチューニングの考え方
21. 過去のウェビナーでHint句について解説しています。 実行計画に基づいたチューニング - 実行計画を理解する https://pingcap.co.jp/event-webinar-edu-sql-execution-thank-you/
22. • 実行計画に基づいたチューニング ◦ 実行計画を理解する ◦ SQLを見直す ◦ インデックスを見直す ◦ 統計情報を最新に更新する • 実行計画を制御する ◦ Hint句を利用する ◦ SQL bindingを利用する ▪ SQL Plan Management (SPM)の１つ • SQL binding, baseline capturing, そして baseline evolutionの３つ で構成される ▪ アプリケーションやSQLを改変することなく、実行計画を強制する TiDBにおけるSQLチューニングの考え方
23. SQL Plan Management (SPM) とは何か
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
25. SQL bindingの機能及び操作方法
26. SQL bindingの機能 SQL bindingとは • SQLに対して、Hint句を含めたSQLを割り当てることで任意の実行計画を生成さ せる • アプリケーション側のSQLを修正することなく、SQLの挙動を変えることができる • 過去に生成された実行計画から割り当てる実行計画を選ぶことができる https://docs.pingcap.com/tidb/stable/sql-plan-management#sql-binding
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の正規化の例
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 <bindの対象となるSQL> using <bindしたいHint句付きのSQL>
29. SQL bindingの操作方法 - SQLを指定して作成 https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-sql-statement
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';
31. SQL bindingの操作方法 - 履歴から作成 https://docs.pingcap.com/tidbcloud/sql-plan-management#create-a-binding-according-to-a-historical-execution-plan
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です）
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';
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]
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`
36. まとめ
37. まとめ • TiDBにおけるSQLチューニングの復習をしました ◦ 実行計画に基づいたチューニングを実施してください ◦ 過去のウェビナーが参考になります • SQL Plan Management の SQL binding を使って実行計画を固定させることがで きます ◦ SQLやアプリケーションを書き換えることなく、SQLの動作を変えることができ ます
38. Q&A
39. Thank You!
 https://www.pingcap.com/
 info@pingcap.com