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

本日の位置づけ ● 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/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Plan Management (SPM) とは何か

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

SQL bindingの機能及び操作方法

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

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の正規化の例

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

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

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';

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

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です）

● 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';

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

● “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`

まとめ

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

Q&A

Thank You!
 https://www.pingcap.com/
 [email protected]