## 模块 3：面向分析型负载的列式引擎 ![demo_203-tidb-x-multi-modal.jpg 85%](https://lab-static.pingcap.com/images/2025/11/27/3486dd500a36fa4012367da02f3e59ceebdc7908.jpg) TiFlash 是 TiDB 的一款列式存储引擎，通过对大规模数据集的快速扫描和处理，大幅提升分析型查询性能，非常适合复杂聚合和分析型场景。列式存储会自动与行存保持实时同步，无需人工进行 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` 表数据同步至 TiFlash 列式存储，并保持实时同步。将副本数设为 1。 ```sql ALTER TABLE books SET TIFLASH REPLICA 2; ``` ```output ... Query OK, 0 rows affected, 1 warning (0.20 sec) ``` - 可通过以下 SQL 检查同步状态： ```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` 表数据量较少，基于成本的优化器判断用 TiKV 行存更高效。虽然表中数据量很小，但是优化器仍有可能选择 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 次自身插入倍增，最终表行数将增长到 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** 所使用的 `task` 已变为 `mpp[tiflash]`（列存）。此时优化器判断 TiFlash 列式存储的 **TableFullScan** 比 TiKV 行存更高效。 ```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` 两表间做关联，但只有前 100 本书才有相应的评分，后续插入的书本没有评分数据。因而实际参与分析的行数仍然很少，优化器认为此时用 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) ``` - 你可以为查询添加 Hint，强制优化器对 `books` 表（别名为 `b`）使用 TiFlash。但除非必要，一般不推荐依赖 Hint。 ```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. 使用 Hint 对比分析型查询性能 - 强制优化器分别用列存、行存执行同一查询，对比性能差异。 ```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; ``` - 第一条（使用列存）查询速度显著快于第二条（使用行存）。 ```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 查询优化器可以根据实际场景，自动在行存与列存之间做出选择，从而更加高效地支持分析型查询。这体现了 TiDB 支持 HTAP（混合事务与分析处理）的能力。如需进一步了解查询优化器，可访问[查询优化器](/labs/dba_307_lab_ff2)。现在，请进入下一个模块。