【NewSQL】無料トレーニング：TiDBでのSQL実行の仕組み

このスライドでは、TiDBでのSQLを実行する仕組みについて、TiDBではどのようにすればSQL(DMLやDDL)を実行しているのか、TiDBで作成される実行計画とは何か、またオプティマイザーヒントを用いたTiDBでのSQL実行計画の管理方法ついて説明します。

トピック：
TiDBでSQL(DML、DDL)の実行プロセスを理解する
TiDBでのSQL実行計画を理解する
TiDBでのSQL実行計画を管理する方法(オプティマイザーヒント、binding)の紹介

録画：https://youtu.be/s1joByxCXfc

Transcript

1. PingCAP Education： TiDBでのSQL実行の仕組み
 本多康夫 Technical support engineer at PingCAP Japan
2. 本日の位置づけ https://pingcap.co.jp/event/ 
 
 2022年6月16日(木) 14:00-15:00 PingCAP Education：TiKV- RocksDBについて 2022年6月23日(木) 14:00-15:00 アプリケーション開発観点からの TiDB - PHP編 2022年6月30日(木) 14:00-15:00 HTAPを体験！TiDBのカラムナストアで分析してみよう！ 2022年7月14日(木) 14:00-15:30 TiDB Cloudの紹介・デモ 2022年7月21日(木) 14:00-15:00 TiDB Cloud リリースノートについて 2022年7月28日(木) 14:00-15:00 PingCAP Education：TiDBでの表設計 2022年9月9日(金) 14:00-15:00 PingCAP Education：TiKV-トランザクション＆MVCC 2022年9月30日(金) 14:00-15:00 PingCAP Education：TiDBでのSQL実行の仕組み ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists
3. アジェンダ • TiDBでSQL(DML、DDL)の実行プロセスを理解する • TiDBでのSQL実行計画を理解する • TiDBでのSQL実行計画を管理する方法 (オプティマイザーヒント、 binding)の紹介
4. • TiDBアーキテクチャ • TiDBとTiKV, PDが分離 • 各コンポーネントも複数 前提 TiDB TiDB TiKV Cluster (Storage) Metadata TiKV TiKV TiKV MySQL Clients TiKV TiDB TSO/Data Location TiDB TiDB ... ... DistSQL API PD PD PD Cluster TiKV TiKV TiDB PD MySQL Clients ...
5. TiDBでのDDL実行を理解する
6. • DDLを受け取ったサーバー (TiDB)とデータをKVとして保存するサーバー (TiKV)が異なる a. DDLをDDL jobとしてカプセル化し、 KV層にあるjob queueにDDL jobを保存します b. バックグラウンドワーカーが job queueにあるDDL jobを確認します i. もし、それがownerロールであれば、jobを即座に実行します ii. Ownerのワーカーがjobの実行を終了したら、その jobをqueueから削除し、job history queue に移動します c. Jobがhistory queueにあることを確認したら、 DDL操作は終了していることがわかります d. レスポンスをMySQLクライアントに返します TiDBでのDDL実行
7. TiDBでのSQL実行
8. TiDBでのDDLの種類 • Meta-Only change ◦ メタデータのみの変更 ◦ かかる時間はテーブルの行数に依存しません • Reorg-Data Change ◦ データの再編成を伴う変更 ◦ 処理時間がデータの量、行数に依存します • MODIFY COLUMN | TiDB SQL Statement Reference | PingCAP Docs
9. カラムを追加する場合 • カラム追加はメタデータのみの変更(Meta-Only change)のため、かかる時間はテーブルの行数に依存しません “Meta-Only change” ◦ MODIFY COLUMN | TiDB SQL Statement Reference | PingCAP Docs • 追加されたカラムにデフォルト値があった場合: ◦ 新しく追加されたカラムにデフォルト値が合った場合、original default value という列に保存します ◦ その後、その列に読み込みが発生し、追加された列の値がnullだった場合は、TiKVはレスポンスにoriginal default valueの値を入れて処理をします(デフォルト値をカラム追加時に前もって埋めることはしません)
10. インデックスを追加する場合 • 実際にデータを変更する (Reorg-Data Change)であるため、かかる時間はデータ量に依存します • Reorg-Data Change関連のシステム変数 ◦ tidb_ddl_reorg_worker_cnt ▪ Reorgを行うワーカーの数 ▪ デフォルト : 4 ▪ 1から256の範囲で設定可能です ◦ tidb_ddl_reorg_batch_size ▪ ワーカーが一度に処理する行数 ▪ デフォルト : 256 ▪ 32から10240の範囲で設定可能です ◦ tidb_ddl_reorg_priority ▪ Reorg data changeのプライオリティ ▪ デフォルト値 : PRIORITY_LOW, PRIORITY_NORMAL or PRIORITY_HIGH. ▪ PRIORITY_LOW PRIORITY_NORMAL PRIORITY_HIGH が設定可能です
11. DDL実行状況のモニタリング • ADMIN SHOW DDL文で現在実行中のDDLの状況を表示できます ◦ ADMIN SHOW DDL ▪ ADMIN SHOW DDL JOBS ▪ ADMIN SHOW DDL JOB QUERIES job_id [, job_id] … ▪ ADMIN CANCEL DDL JOBS 'job_id' [, 'job_id'] … tidb> admin show ddl\G; *************************** 1. row *************************** SCHEMA_VER: 140 OWNER: 1a1c4174-0fcd-4ba0-add9-12d08c4077dc RUNNING_JOBS: ID:121, Type:add index, State:running, SchemaState:write reorganization, SchemaID:1, TableID:118, RowCount:77312, ArgLen:0, start time: 2018-12-05 16:26:10.652 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:404749908941733890 SELF_ID: 1a1c4174-0fcd-4ba0-add9-12d08c4077dc
12. TiDBでのSQL実行計画を理解する
13. • 文法: EXPLAIN SQL ◦ Explainのあとに記述したSQLは実行されません ◦ 実行計画はツリー上に表示されます 実行計画の例 : explain
14. 実行計画列の詳細 列 説明 id オペレーターのIDを示します。実行計画全体からオペレーターを一意に識別できるようにしています。 count オペレーターによって返される 行数を示します。この行は統計情報に基づく推測です。 task オペレーターが実行されている場所を示します。これは root task または cop task のどちらかです。 operator info オペレーターに関する詳細情報を示します。オペレータによって表示される内容は異なります。
15. • Coprocessor タスク: TiKVにpushdownされたタスク • Root タスク: TiDBで実行されるタスク root タスクと coprocessor タスク
16. • TiKVで実行されるオペレーター ◦ TableFullScan: テーブルの全件スキャン ◦ TableRangeScan : テーブルの範囲スキャン ◦ TableRowIDScan : RowIDに基づくテーブルのスキャン • TiDBで実行されるオペレーター ◦ TableReader : TiKVのTableFullScanなどから渡されるデータの集計 • TiDB,TiKV共通で実行されるオペレーター ◦ Selection : whereなどで指定されたデータのフィルター ◦ HashAgg : group byなどで指定されたデータの集計 オペレーターの種類
17. • 文法: EXPLAIN ANALYZE ◦ Explain analyzeのあとに記述したSQLが実行されます ◦ Explainの列に加え、”actRows”, ”execution info”などの実行時の詳細情報が出力されます • MySQL [bikeshare]> EXPLAIN ANALYZE SELECT * FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59' order by start_date; 実行計画の例 : explain analyze
18. estRows、Execution info列 列名 Description time このオペレーターにかかった 合計時間 を示します。 loops このオペレーターが実行された 回数を示します。 actRows このオペレーターによって 実際に返された行数 を示します。
19. Selectionオペレーター Selectionオペレーターが行う操作 : • WHERE,HAVING,ONなどに由来するフィルター
20. Aggregationオペレーター Aggregation オペレーターが行う操作 : • 集約操作を行います (HashAggとStreamAggの2種類あります) • Group by • 集計関数(sumなど) Note: 列cにはインデックスがあり (順序が確定できる)、列bにはインデックスがない環境です
21. Joinオペレーター Join オペレーターが行う操作 : • Joinアルゴリズム : Hash/Merge/IndexLookup) • Joinの種類(InnerJoin, OuterJoin, SemiJoin)
22. TableReaderオペレーター TableReaderオペレーターが行う操作 : • where句で指定される範囲スキャンとフィルター • その結果がソートされている必要があるかどうか (keep order: true)
23. SQL Optimization
24. SQL optimization • インデックスの最適化 • 統計情報を最新化する (analyze table)
25. 例1: インデックスの最適化 Bikeshare Example Database の例: • インデックスを追加してスキャンする行数を減らす
26. 例2: 統計情報を最新化する Explain analyzeを利用して、estRows(統計)とactRows(実測値)が乖離していないか確認する • 統計情報が古い場合 (“drop stats trips”によって意図的に統計情報を削除しています ) • 統計情報が最新の場合 (“analyze table trips”)
27. 統計情報補足 • tidb_analyze_version システム変数 ◦ TiDB 5.3以降のデフォルトは2 ◦ メモリ使用量が大きくなる場合は、 1(TiDB 5.2以前のデフォルト値)の利用も可能です • show stats_healty ◦ テーブルの統計情報の健全さ (実態と合っているか )を確認可能です
28. オプティマイザーヒントとbinding
29. オプティマイザーヒントとbinding • 適切なインデックスの付与、あるいは統計情報の最新化などで対応を推奨します • どうしても実行計画を固定化したい場合に : ◦ オプティマイザーヒント ▪ オプティマイザに対して指示を与える機能 ◦ バインディング ▪ SQL Plan Management(SPM)の機能
30. 利用できるオプティマイザーヒント • MERGE_JOIN ◦ ソートマージジョインを利用するようにオプティマイザに指示する ◦ select /*+ MERGE_JOIN(t1, t2) */ * from t1, t2 where t1.id = t2.id; • HASH_JOIN ◦ ハッシュジョインを利用するようにオプティマイザに指示する ◦ select /*+ HASH_JOIN(t1, t2) */ * from t1, t2 where t1.id = t2.id; • USE_INDEX ◦ 指定されたインデックスを利用するようにオプティマイザに指示する ◦ SELECT /*+ USE_INDEX(t1, idx1, idx2) */ * FROM t1; • IGNORE_INDEX ◦ 指定されたインデックスを無視するようにオプティマイザに指示する ◦ select /*+ IGNORE_INDEX(t1, idx1, idx2) */ * from t t1; ▪ 同様の効果に invisible index もあります • https://docs.pingcap.com/ja/tidbcloud/optimizer-hints
31. 利用できるオプティマイザーヒント • MAX_EXECUTION_TIME(N) ◦ SQLの処理時間に制限を加える (単位はミリ秒) ◦ select /*+ MAX_EXECUTION_TIME(1000) */ * from t1 inner join t2 where t1.id = t2.id; • MEMORY_QUOTA(N) ◦ SQLで利用できるメモリ量に制限を加える ◦ select /*+ MEMORY_QUOTA(1024 MB) */ * from t; • https://docs.pingcap.com/ja/tidbcloud/optimizer-hints
32. バインディング • 特定のSQLが実行された場合に、ヒント付きの SQLに置き換えて実行することが可能です CREATE SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123 USING SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123; 例 ANALYZE TABLE t1; EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123; CREATE SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123 USING SELECT * FROM t1 IGNORE INDEX (b) WHERE b = 123; EXPLAIN ANALYZE SELECT * FROM t1 WHERE b = 123; DROP SESSION BINDING FOR SELECT * FROM t1 WHERE b = 123;
33. Q&A、アンケートのお願い
34. PingCAP定期ウェビナー https://pingcap.co.jp/event/ ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists 

35. Webinar : 今後の予定 https://pingcap.co.jp/event/ ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists 
 
 2022年10月21日(金) 14:00-15:00 PingCAP Education：TiKV - Raftによるデータ整合性の実現
37. PingCAP Education トレーニング トレーニング全体リンク 　　https://en.pingcap.com/education/ 無償コース（推奨） 　　TiDB Technical Essentials 101: 内容 　　- TiDB全体のアーキテクチャ 　　- TiDBの各コンポーネントのアーキテクチャ 　　- HTAP Overview & TiFlashのアーキテクチャ 　　- TiDB Cloudの始め方 　　- TiDB 6.0情報 Course: TiDB Essentials 101 トレーニングを通じ、TiDBの理解を深めることができます。
38. TiDB Cloud : 無償トライアルのご案内 https://tidbcloud.com/signup Developer TierではTiDB Cloudを無償で1年間ご利用頂けます。 ※容量制限はありますが 本番と同等の機能を 提供しているため、 アプリとの接続試験 などが容易にできます。
39. クラウドアプリのスタートをご支援！ TiDB Cloud 1年無料キャンペーン 日本進出から１周年を記念し、TiDB Cloudをご利用いただいたことがないお客様を対象にTiDB Cloud1年間相当の値引きキャンペーンを実施します。 キャンペーン期間 2022年5月18日～2023年3月31日　申込み分まで キャンペーン内容 最大700万円相当（TiDB Cloud典型構成で1年分相当）の値引きを提供します。 対象・適用条件 下記条件をみたすお客様 ※1（最大10社※2） 　- TiDB Cloudを使用したことがないお客様 　- 事例・プロモーションにご協力いただけるお客様 　- 設立から1年以上経過しているお客様 適用方法 フォームにご入力の上、キャンペーンにお申し込みください。審査結果およびキャンペーン の詳細について担当者より連絡いたします。 URL https://pingcap.co.jp/start-dash-202205/ ※1 別途所定の審査あり
 ※2 予定数に達し次第終了する可能性があります。

40. OSS Insight : 活発度が分かる分析サービス 46億を超えるGitHub上のイベント分析するデータベースとしてTiDB (TiFlash)を活用 https://ossinsight.io/ ①各イベント発生の推移 ①GitHubイベント データを1分おきに同期 +TiFlash https://ossinsight.io/ ②Pull requests地域の表示 ②分析クエリを TiFlash(カラムストア ) で高速処理 ③ジャンル内での比較（人気言語等） etc…
41. TiDB / TiDB Cloud 日本語ドキュメント https://docs.pingcap.com/ja/tidb/stable 日本語訳問題の報告は こちらからお願いします。 https://github.com/pingcap/docs/issues issue作成時のLabel： i18n-ja type/bug-fix
42. @PingCAP_Japan Twitterのフォローをお願いします。
43. Thank You! https://www.pingcap.co.jp pingcapjp@pingcap.com