## モジュール 3: 分析ワークロードのためのカラム型エンジン ![demo_203-tidb-x-multi-modal-ja.jpg 85%](https://lab-static.pingcap.com/images/2025/12/2/f6a3c7948187578cfcf233c960f61e048eed94fc.jpg) TiFlash は TiDB のためのカラムナーストレージエンジンで、大規模なデータセットの高速なスキャンと処理を可能にし、分析クエリを大幅に高速化します。これにより、複雑な集計や分析ワークロードに最適です。カラム型ストレージは行ストア(Rows)とリアルタイムで自動的に同期されるため、手動でのETL処理は不要です。 ### 手順 1. 分析クエリを実行し、実行計画を確認する - [ターミナル 1](ssh://monitor1-ssh#1) にて、まだ接続していない場合は TiDB に接続してください。 ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \ -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} ``` - 次の集計クエリの実行計画を確認します。`access object` フィールドが **table:books** の行に注目してください。`task` フィールドが **cop[tikv]** と表示されていれば、**TableFullScan** が TiKV(行ストア)で実行されていることを示します。 ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; EXPLAIN SELECT SUM(stock), AVG(stock) FROM books GROUP BY type ORDER BY type; ``` ```output +--------------------------------+---------+-----------+---------------+----------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+---------------+----------------------------------+ ... | └─TableFullScan_15 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+---------------+----------------------------------+ 6 rows in set (0.01 sec) ``` ### 手順 2. 行ストアからカラムストアへのデータ同期を有効化する - `books` テーブルのデータが TiKV 行ストアから TiFlash カラムストアに複製され、常に同期されるように設定します。レプリカ数は 2 に設定します。 ```sql ALTER TABLE books SET TIFLASH REPLICA 2; ``` ```output ... Query OK, 0 rows affected, 1 warning (0.20 sec) ``` - 以下のクエリで同期状況を確認できます。 ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}'; ``` - 同期が完了するまで待ちます。`PROGRESS` および `AVAILABLE` の両方が 1 になれば完了しています。例: ```output +-------------------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +-------------------------+------------+----------+---------------+-----------------+-----------+----------+ | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} | books | 138 | 2 | | 1 | 1 | +-------------------------+------------+----------+---------------+-----------------+-----------+----------+ 2 rows in set (0.01 sec) ``` ### 手順 3. テーブルサイズ増加時に再度実行計画を確認する - テーブル統計情報を収集します。 ```sql ANALYZE TABLE books; ``` - クエリを再度実行し、実行計画を確認します。もし実行計画が変わらない場合、それは books テーブル内のデータ量がまだ少なすぎるため、コストベースのオプティマイザが TiFlash カラムストアよりも TiKV 行ストアを利用する方が効率的と判断した結果です。なお、books テーブルの行数が非常に少ない場合でも、オプティマイザが TiFlash を選択することもあり、その場合でもパフォーマンスの違いはほとんどありません。 ```sql EXPLAIN SELECT SUM(stock), AVG(stock) FROM books; ``` ```output +--------------------------------+---------+-----------+---------------+----------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+---------+-----------+---------------+----------------------------------------+ ... | └─TableFullScan_15 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +--------------------------------+---------+-----------+---------------+----------------------------------------+ 6 rows in set (0.01 sec) ``` - オプティマイザが自動で TiFlash を選ぶようテーブルを大きくしましょう。books テーブルを13回2倍に増やします。これにより行数は 819200 となります。 ```sql INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; INSERT INTO books (title, type, published_at, stock, price) SELECT title, type, published_at, stock, price FROM books; ``` ```output ... Query OK, 409600 rows affected (4.87 sec) Records: 409600 Duplicates: 0 Warnings: 0 ``` - テーブル統計情報を再度収集します。 ```sql ANALYZE TABLE books; ``` - 再度実行計画を確認します。今度は **table:books** について `mpp[tiflash]` (カラムストア)が使われているのが確認できます。今回はTiFlash カラムストアによるスキャンの方がコストが低いとオプティマイザが判断しました。 ```sql EXPLAIN SELECT SUM(stock), AVG(stock) FROM books; ``` ```output +----------------------------------+-----------+--------------+---------------+-------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+-----------+--------------+---------------+-------------------------------------------+ ... | └─TableFullScan_19 | 301456.00 | mpp[tiflash] | table:books | keep order:false, stats:pseudo | +----------------------------------+-----------+--------------+---------------+-------------------------------------------+ 7 rows in set, 2 warnings (0.02 sec) ``` - モジュール1の複雑なクエリでもカラムストアが使われるか試してみましょう。 ```sql EXPLAIN SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; ``` - このクエリでは TiFlash カラムストアは利用されません。主な理由は、元々 `books` と `ratings` テーブルをJOINしていますが、最初の100冊を除く追加挿入分には対応する ratings 行が無いためです。そのため、分析に参加する行数が依然として少なく、オプティマイザは TiKV 行ストアを選びます。 ```output +------------------------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+ ... | └─TableRangeScan_38 | 849.00 | cop[tikv] | table:b | range: decided by [lab_bgl_migrate_6840006.ratings.book_id], keep order:false | +------------------------------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+ 24 rows in set (0.02 sec) ``` - このクエリの `books` テーブル（エイリアス b）に対してTiFlash利用をオプティマイザに指示するヒントも付与できます。ただし必要が無い限りヒントの使用は推奨されません。 ```sql EXPLAIN SELECT /*+ READ_FROM_STORAGE(TIFLASH[b])*/ b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; ``` ```output +-----------------------------------------------------------+-------------+--------------+---------------+-------------------+ | id | estRows | task | access object | operator info | +-----------------------------------------------------------+-------------+--------------+---------------+-------------------+ ... | └─TableFullScan_52 | 1120556.00 | mpp[tiflash] | table:b | keep order:false | +-----------------------------------------------------------+-------------+--------------+---------------+-------------------+ 25 rows in set (0.01 sec) ``` ### 手順 4. ヒントを使って分析クエリのパフォーマンスを比較 - ヒントによってオプティマイザの選択を上書きし、同じクエリを行ストアとカラムストアで比較してみましょう。 ```sql SELECT /*+ READ_FROM_STORAGE(TIFLASH[books])*/ SUM(stock), AVG(stock) FROM books; SELECT /*+ READ_FROM_STORAGE(TIKV[books])*/ SUM(stock), AVG(stock) FROM books; ``` - 最初（カラムストア利用）の結果は2番目（行ストア利用）よりも高速です。 ```output ... +----------------+---------------+ | SUM(stock) | AVG(stock) | +----------------+---------------+ | 45180795265024 | 55152337.9700 | +----------------+---------------+ 1 row in set (0.26 sec) ... +----------------+---------------+ | SUM(stock) | AVG(stock) | +----------------+---------------+ | 45180795265024 | 55152337.9700 | +----------------+---------------+ 1 row in set (0.60 sec) ``` ### 手順 5. クリーンアップ - 最初の100冊だけを残し、`books` テーブルの追加行を削除します。 ```sql DELETE FROM books WHERE id > 100; ALTER TABLE books SET TIFLASH REPLICA 0; ANALYZE TABLE books; ``` ```output Query OK, 819100 rows affected (4.74 sec) ... Query OK, 0 rows affected (1.07 sec) ... Query OK, 0 rows affected, 1 warning (2.21 sec) ``` - このモジュールでは、特定のシナリオに応じてクエリオプティマイザが自動的に行ストレージとカラムストアのどちらを選択して分析クエリを最適にサポートできるかを観察しました。これは TiDB の HTAP (Hybrid Transactional and Analytical Processing) 能力の一例です。クエリオプティマイザの詳細を知りたい方は [クエリオプティマイザー](/labs/dba_307_lab_ff2) をご覧ください。次のモジュールへ進みましょう。