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

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

PingCAP-Japan
September 30, 2022

 【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

PingCAP-Japan

September 30, 2022
Tweet

More Decks by PingCAP-Japan

Other Decks in Technology

Transcript

  1. PingCAP Education:
    TiDBでのSQL実行の仕組み

    本多康夫
    Technical support engineer at PingCAP Japan

    View full-size slide

  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

    View full-size slide

  3. アジェンダ
    ● TiDBでSQL(DML、DDL)の実行プロセスを理解する
    ● TiDBでのSQL実行計画を理解する
    ● TiDBでのSQL実行計画を管理する方法 (オプティマイザーヒント、 binding)の紹介

    View full-size slide

  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
    ...

    View full-size slide

  5. TiDBでのDDL実行を理解する

    View full-size slide

  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実行

    View full-size slide

  7. TiDBでのSQL実行

    View full-size slide

  8. TiDBでのDDLの種類
    ● Meta-Only change
    ○ メタデータのみの変更
    ○ かかる時間はテーブルの行数に依存しません
    ● Reorg-Data Change
    ○ データの再編成を伴う変更
    ○ 処理時間がデータの量、行数に依存します
    ● MODIFY COLUMN | TiDB SQL Statement Reference | PingCAP Docs

    View full-size slide

  9. カラムを追加する場合
    ● カラム追加はメタデータのみの変更(Meta-Only change)のため、かかる時間はテーブルの行数に依存しません “Meta-Only
    change”
    ○ MODIFY COLUMN | TiDB SQL Statement Reference | PingCAP Docs
    ● 追加されたカラムにデフォルト値があった場合:
    ○ 新しく追加されたカラムにデフォルト値が合った場合、original default value という列に保存します
    ○ その後、その列に読み込みが発生し、追加された列の値がnullだった場合は、TiKVはレスポンスにoriginal default
    valueの値を入れて処理をします(デフォルト値をカラム追加時に前もって埋めることはしません)

    View full-size slide

  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 が設定可能です

    View full-size slide

  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:, ErrCount:0, SnapshotVersion:404749908941733890
    SELF_ID: 1a1c4174-0fcd-4ba0-add9-12d08c4077dc

    View full-size slide

  12. TiDBでのSQL実行計画を理解する

    View full-size slide

  13. ● 文法: EXPLAIN SQL
    ○ Explainのあとに記述したSQLは実行されません
    ○ 実行計画はツリー上に表示されます
    実行計画の例 : explain

    View full-size slide

  14. 実行計画列の詳細
    列 説明
    id オペレーターのIDを示します。実行計画全体からオペレーターを一意に識別できるようにしています。
    count オペレーターによって返される
    行数を示します。この行は統計情報に基づく推測です。
    task オペレーターが実行されている場所を示します。これは
    root task または cop task のどちらかです。
    operator info オペレーターに関する詳細情報を示します。オペレータによって表示される内容は異なります。

    View full-size slide

  15. ● Coprocessor タスク: TiKVにpushdownされたタスク
    ● Root タスク: TiDBで実行されるタスク
    root タスクと coprocessor タスク

    View full-size slide

  16. ● TiKVで実行されるオペレーター
    ○ TableFullScan: テーブルの全件スキャン
    ○ TableRangeScan : テーブルの範囲スキャン
    ○ TableRowIDScan : RowIDに基づくテーブルのスキャン
    ● TiDBで実行されるオペレーター
    ○ TableReader : TiKVのTableFullScanなどから渡されるデータの集計
    ● TiDB,TiKV共通で実行されるオペレーター
    ○ Selection : whereなどで指定されたデータのフィルター
    ○ HashAgg : group byなどで指定されたデータの集計
    オペレーターの種類

    View full-size slide

  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

    View full-size slide

  18. estRows、Execution info列
    列名 Description
    time このオペレーターにかかった
    合計時間 を示します。
    loops このオペレーターが実行された
    回数を示します。
    actRows このオペレーターによって
    実際に返された行数
    を示します。

    View full-size slide

  19. Selectionオペレーター
    Selectionオペレーターが行う操作 :
    ● WHERE,HAVING,ONなどに由来するフィルター

    View full-size slide

  20. Aggregationオペレーター
    Aggregation オペレーターが行う操作 :
    ● 集約操作を行います (HashAggとStreamAggの2種類あります)
    ● Group by
    ● 集計関数(sumなど)
    Note: 列cにはインデックスがあり (順序が確定できる)、列bにはインデックスがない環境です

    View full-size slide

  21. Joinオペレーター
    Join オペレーターが行う操作 :
    ● Joinアルゴリズム : Hash/Merge/IndexLookup)
    ● Joinの種類(InnerJoin, OuterJoin, SemiJoin)

    View full-size slide

  22. TableReaderオペレーター
    TableReaderオペレーターが行う操作 :
    ● where句で指定される範囲スキャンとフィルター
    ● その結果がソートされている必要があるかどうか (keep order: true)

    View full-size slide

  23. SQL Optimization

    View full-size slide

  24. SQL optimization
    ● インデックスの最適化
    ● 統計情報を最新化する (analyze table)

    View full-size slide

  25. 例1: インデックスの最適化
    Bikeshare Example Database の例:
    ● インデックスを追加してスキャンする行数を減らす

    View full-size slide

  26. 例2: 統計情報を最新化する
    Explain analyzeを利用して、estRows(統計)とactRows(実測値)が乖離していないか確認する
    ● 統計情報が古い場合 (“drop stats trips”によって意図的に統計情報を削除しています )
    ● 統計情報が最新の場合 (“analyze table trips”)

    View full-size slide

  27. 統計情報補足
    ● tidb_analyze_version システム変数
    ○ TiDB 5.3以降のデフォルトは2
    ○ メモリ使用量が大きくなる場合は、 1(TiDB 5.2以前のデフォルト値)の利用も可能です
    ● show stats_healty
    ○ テーブルの統計情報の健全さ (実態と合っているか )を確認可能です

    View full-size slide

  28. オプティマイザーヒントとbinding

    View full-size slide

  29. オプティマイザーヒントとbinding
    ● 適切なインデックスの付与、あるいは統計情報の最新化などで対応を推奨します
    ● どうしても実行計画を固定化したい場合に :
    ○ オプティマイザーヒント
    ■ オプティマイザに対して指示を与える機能
    ○ バインディング
    ■ SQL Plan Management(SPM)の機能

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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;

    View full-size slide

  33. Q&A、アンケートのお願い

    View full-size slide

  34. PingCAP定期ウェビナー
    https://pingcap.co.jp/event/
    ※過去開催アーカイブ : https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists 


    View full-size slide

  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によるデータ整合性の実現

    View full-size slide

  36. 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の理解を深めることができます。

    View full-size slide

  37. TiDB Cloud : 無償トライアルのご案内
    https://tidbcloud.com/signup
    Developer TierではTiDB Cloudを無償で1年間ご利用頂けます。
    ※容量制限はありますが
    本番と同等の機能を
    提供しているため、
    アプリとの接続試験
    などが容易にできます。

    View full-size slide

  38. クラウドアプリのスタートをご支援!
    TiDB Cloud 1年無料キャンペーン
    日本進出から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 予定数に達し次第終了する可能性があります。


    View full-size slide

  39. OSS Insight : 活発度が分かる分析サービス
    46億を超えるGitHub上のイベント分析するデータベースとしてTiDB (TiFlash)を活用
    https://ossinsight.io/
    ①各イベント発生の推移
    ①GitHubイベント
    データを1分おきに同期 +TiFlash
    https://ossinsight.io/ ②Pull requests地域の表示
    ②分析クエリを
    TiFlash(カラムストア )
    で高速処理
    ③ジャンル内での比較(人気言語等)
    etc…

    View full-size slide

  40. TiDB / TiDB Cloud
    日本語ドキュメント
    https://docs.pingcap.com/ja/tidb/stable
    日本語訳問題の報告は
    こちらからお願いします。
    https://github.com/pingcap/docs/issues
    issue作成時のLabel:
    i18n-ja
    type/bug-fix

    View full-size slide

  41. @PingCAP_Japan
    Twitterのフォローをお願いします。

    View full-size slide

  42. Thank You!
    https://www.pingcap.co.jp
    [email protected]

    View full-size slide