重要
このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

HTAPクエリ

HTAPは、Hybrid Transactional andAnalyticalProcessingの略です。従来、データベースはトランザクションシナリオまたは分析シナリオ用に設計されていることが多いため、データプラットフォームをトランザクション処理と分析処理に分割し、分析クエリにすばやく応答するためにデータをトランザクションデータベースから分析データベースに複製する必要があります。 TiDBデータベースは、トランザクションタスクと分析タスクの両方を実行できます。これにより、データプラットフォームの構築が大幅に簡素化され、ユーザーはより新しいデータを分析に使用できるようになります。

TiDBは、オンライントランザクション処理(OLTP)に行ベースのストレージエンジンであるTiKVを使用し、オンライン分析処理(OLAP)に列型ストレージエンジンであるTiFlashを使用します。 HTAPには、行ベースのストレージエンジンと列型ストレージエンジンが共存します。どちらのストレージエンジンもデータを自動的に複製し、強力な一貫性を保つことができます。行ベースのストレージエンジンはOLTPパフォーマンスを最適化し、列指向ストレージエンジンはOLAPパフォーマンスを最適化します。

テーブルを作成するのセクションでは、TiDBのHTAP機能を有効にする方法を紹介します。以下では、HTAPを使用してデータをより高速に分析する方法について説明します。

データの準備

開始する前に、さらにサンプルデータをインポートできますtiup demoコマンドを介して 。例えば:

tiup demo bookshop prepare --users=200000 --books=500000 --authors=100000 --ratings=1000000 --orders=1000000 --host 127.0.0.1 --port 4000 --drop-tables

または、 TiDB Cloudのインポート機能を使用するを使用して、事前に準備されたサンプルデータをインポートすることもできます。

ウィンドウ関数

データベースを使用する場合、データの保存とアプリケーション機能(注文や評価の本など)の提供に加えて、データベース内のデータを分析して、さらに操作や決定を行う必要がある場合があります。

単一のテーブルからデータをクエリするのドキュメントでは、集計クエリを使用してデータ全体を分析する方法を紹介しています。より複雑なシナリオでは、複数の集計クエリの結果を1つのクエリに集約したい場合があります。特定の本の注文量の履歴トレンドを知りたい場合は、各月のすべての注文データについてsumを集計し、 sumの結果を集計して履歴トレンドを取得できます。

このような分析を容易にするために、TiDB v3.0以降、TiDBはウィンドウ関数をサポートしています。この関数は、データの各行について、複数の行にまたがるデータにアクセスする機能を提供します。通常の集計クエリとは異なり、ウィンドウ関数は結果セットを1つの行にマージせずに行を集計します。

集計関数と同様に、ウィンドウ関数を使用する場合も、固定された構文セットに従う必要があります。

SELECT
    window_function() OVER ([partition_clause] [order_clause] [frame_clause]) AS alias
FROM
    table_name

ORDER BY

集約ウィンドウ関数sum()を使用すると、特定の書籍の注文金額の履歴トレンドを分析できます。例えば:

WITH orders_group_by_month AS (
  SELECT DATE_FORMAT(ordered_at, '%Y-%c') AS month, COUNT(*) AS orders
  FROM orders
  WHERE book_id = 3461722937
  GROUP BY 1
)
SELECT
month,
SUM(orders) OVER(ORDER BY month ASC) as acc
FROM orders_group_by_month
ORDER BY month ASC;

sum()関数は、 OVER節のORDER BYステートメントで指定された順序でデータを累積します。結果は次のとおりです。

+---------+-------+
| month   | acc   |
+---------+-------+
| 2011-5  |     1 |
| 2011-8  |     2 |
| 2012-1  |     3 |
| 2012-2  |     4 |
| 2013-1  |     5 |
| 2013-3  |     6 |
| 2015-11 |     7 |
| 2015-4  |     8 |
| 2015-8  |     9 |
| 2017-11 |    10 |
| 2017-5  |    11 |
| 2019-5  |    13 |
| 2020-2  |    14 |
+---------+-------+
13 rows in set (0.01 sec)

上記のデータを、横軸に時間を、縦軸に累積注文量をとった折れ線グラフで視覚化します。傾きを変えることで、本の歴史的な注文傾向を簡単に知ることができます。

PARTITION BY

さまざまな種類の本の過去の注文傾向を分析し、それを複数のシリーズの同じ折れ線グラフで視覚化するとします。

PARTITION BY句を使用して、本をタイプ別にグループ化し、タイプごとに履歴オーダーを個別にカウントできます。

WITH orders_group_by_month AS (
    SELECT
        b.type AS book_type,
        DATE_FORMAT(ordered_at, '%Y-%c') AS month,
        COUNT(*) AS orders
    FROM orders o
    LEFT JOIN books b ON o.book_id = b.id
    WHERE b.type IS NOT NULL
    GROUP BY book_type, month
), acc AS (
    SELECT
        book_type,
        month,
        SUM(orders) OVER(PARTITION BY book_type ORDER BY book_type, month ASC) as acc
    FROM orders_group_by_month
    ORDER BY book_type, month ASC
)
SELECT * FROM acc;

結果は次のとおりです。

+------------------------------+---------+------+
| book_type                    | month   | acc  |
+------------------------------+---------+------+
| Magazine                     | 2011-10 |    1 |
| Magazine                     | 2011-8  |    2 |
| Magazine                     | 2012-5  |    3 |
| Magazine                     | 2013-1  |    4 |
| Magazine                     | 2013-6  |    5 |
...
| Novel                        | 2011-3  |   13 |
| Novel                        | 2011-4  |   14 |
| Novel                        | 2011-6  |   15 |
| Novel                        | 2011-8  |   17 |
| Novel                        | 2012-1  |   18 |
| Novel                        | 2012-2  |   20 |
...
| Sports                       | 2021-4  |   49 |
| Sports                       | 2021-7  |   50 |
| Sports                       | 2022-4  |   51 |
+------------------------------+---------+------+
1500 rows in set (1.70 sec)

非集計ウィンドウ関数

TiDBは、より多くの分析ステートメントのために、いくつかの非集計ウィンドウ関数も提供します。

たとえば、 ページネーションクエリのドキュメントでは、 row_number()の関数を使用して効率的なページ付けバッチ処理を実現する方法を紹介しています。

ハイブリッドワークロード

ハイブリッド負荷シナリオでリアルタイムのオンライン分析処理にTiDBを使用する場合、データへのTiDBのエントリポイントを提供するだけで済みます。 TiDBは、特定のビジネスに基づいてさまざまな処理エンジンを自動的に選択します。

TiFlashレプリカを作成する

TiDBは、デフォルトで行ベースのストレージエンジンであるTiKVを使用します。列指向ストレージエンジンであるTiFlashを使用するには、 HTAP機能を有効にするを参照してください。 TiFlashを介してデータをクエリする前に、次のステートメントを使用してbooksおよびordersテーブルのTiFlashレプリカを作成する必要があります。

ALTER TABLE books SET TIFLASH REPLICA 1;
ALTER TABLE orders SET TIFLASH REPLICA 1;

次のステートメントを使用して、TiFlashレプリカの進行状況を確認できます。

SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'bookshop' and TABLE_NAME = 'books';
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'bookshop' and TABLE_NAME = 'orders';

1のPROGRESS列は、進行状況が100%完了していることを示し、1のAVAILABLE列は、レプリカが現在使用可能であることを示します。

+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| bookshop     | books      |      143 |             1 |                 |         1 |        1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.07 sec)
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| bookshop     | orders     |      147 |             1 |                 |         1 |        1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.07 sec)

レプリカを追加した後、 EXPLAINステートメントを使用して、上記のウィンドウ関数PARTITION BYの実行プランを確認できます。実行プランにcop[tiflash]が表示されている場合は、TiFlashエンジンが動作を開始したことを意味します。

次に、 PARTITION BYのサンプルSQLステートメントを再度実行します。結果は次のとおりです。

+------------------------------+---------+------+
| book_type                    | month   | acc  |
+------------------------------+---------+------+
| Magazine                     | 2011-10 |    1 |
| Magazine                     | 2011-8  |    2 |
| Magazine                     | 2012-5  |    3 |
| Magazine                     | 2013-1  |    4 |
| Magazine                     | 2013-6  |    5 |
...
| Novel                        | 2011-3  |   13 |
| Novel                        | 2011-4  |   14 |
| Novel                        | 2011-6  |   15 |
| Novel                        | 2011-8  |   17 |
| Novel                        | 2012-1  |   18 |
| Novel                        | 2012-2  |   20 |
...
| Sports                       | 2021-4  |   49 |
| Sports                       | 2021-7  |   50 |
| Sports                       | 2022-4  |   51 |
+------------------------------+---------+------+
1500 rows in set (0.79 sec)

2つの実行結果を比較すると、TiFlashを使用するとクエリ速度が大幅に向上することがわかります(大量のデータを使用すると、向上がより顕著になります)。これは、ウィンドウ関数は通常、一部の列の全表スキャンに依存しており、列ベースのTiKVよりも列型TiFlashの方がこのタイプの分析タスクを処理するのに適しているためです。 TiKVの場合、主キーまたはインデックスを使用してクエリする行数を減らすと、クエリも高速になり、TiFlashと比較して消費するリソースが少なくなります。

クエリエンジンを指定する

TiDBは、コストベースオプティマイザー(CBO)を使用して、コスト見積もりに基づいてTiFlashレプリカを使用するかどうかを自動的に選択します。ただし、クエリがトランザクションであるか分析であるかが確実な場合は、 オプティマイザーのヒントで使用するクエリエンジンを指定できます。

クエリで使用するエンジンを指定するには、次のステートメントのように/*+ read_from_storage(engine_name[table_name]) */ヒントを使用できます。

ノート:

  • テーブルにエイリアスがある場合は、ヒントでテーブル名の代わりにエイリアスを使用してください。そうでない場合、ヒントは機能しません。
  • read_from_storageヒントは共通テーブル式では機能しません。
WITH orders_group_by_month AS (
    SELECT
        /*+ read_from_storage(tikv[o]) */
        b.type AS book_type,
        DATE_FORMAT(ordered_at, '%Y-%c') AS month,
        COUNT(*) AS orders
    FROM orders o
    LEFT JOIN books b ON o.book_id = b.id
    WHERE b.type IS NOT NULL
    GROUP BY book_type, month
), acc AS (
    SELECT
        book_type,
        month,
        SUM(orders) OVER(PARTITION BY book_type ORDER BY book_type, month ASC) as acc
    FROM orders_group_by_month mo
    ORDER BY book_type, month ASC
)
SELECT * FROM acc;

EXPLAINステートメントを使用して、上記のSQLステートメントの実行プランを確認できます。 cop[tiflash]cop[tikv]が同時にタスク列に表示される場合は、TiFlashとTiKVの両方がこのクエリを完了するようにスケジュールされていることを意味します。 TiFlashとTiKVストレージエンジンは通常異なるTiDBノードを使用するため、2つのクエリタイプは相互に影響を受けないことに注意してください。

TiDBがTiFlashの使用を選択する方法の詳細については、 TiDBを使用してTiFlashレプリカを読み取るを参照してください。

続きを読む