HTAP クエリ
HTAP は Hybrid Transactional and Analytical Processing の略です。従来、データベースは多くの場合、トランザクションまたは分析のシナリオ向けに設計されているため、データ プラットフォームは多くの場合、トランザクション処理と分析処理に分割する必要があり、データをトランザクション データベースから分析データベースに複製して、分析クエリに迅速に応答する必要があります。 TiDB データベースはトランザクション タスクと分析タスクの両方を実行できるため、データ プラットフォームの構築が大幅に簡素化され、ユーザーはより新しいデータを分析に使用できるようになります。
TiDB は、行ベースのストレージ エンジンである TiKV を Online Transactional Processing (OLTP) に使用し、列指向のストレージ エンジンである TiFlash を Online Analytical Processing (OLAP) に使用します。 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 つのテーブルからデータをクエリするのドキュメントでは、集計クエリを使用してデータ全体を分析する方法を紹介しています。より複雑なシナリオでは、複数の集計クエリの結果を 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 の方がクエリ速度が大幅に向上していることがわかります (データ量が多いほど向上します)。これは、ウィンドウ関数は通常、一部の列の完全なテーブル スキャンに依存しており、列ベースの TiFlash は行ベースの TiKV よりもこの種の分析タスクの処理に適しているためです。 TiKV の場合、主キーまたはインデックスを使用してクエリ対象の行数を減らすと、クエリも高速になり、TiFlash と比較して消費するリソースが少なくなります。
クエリ エンジンを指定する
TiDB は Cost Based Optimizer (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 レプリカを読み取るを参照してください。