HTAP 指的是混合事务与分析处理。云数据库 TiDB 版中的 HTAP 架构由 TiKV（为事务处理设计的行存储引擎）和 TiFlash（为分析处理设计的列存储引擎）组成。您的应用数据首先存储在 TiKV 中，然后通过 Raft 共识算法实时同步到 TiFlash。因此，这是从行存储到列存储的实时复制。本教程将引导您以简单的方式体验云数据库 TiDB 版的混合事务与分析处理（HTAP）特性。内容包括如何将表复制到 TiFlash、如何使用 TiFlash 运行查询，以及如何体验性能提升。 ## 操作步骤 ### 步骤 1 将示例数据表复制到列存储引擎创建 Essential 集群后，TiKV 不会自动把数据复制到 TiFlash。您需要在 TiDB MySQL 客户端执行 DDL 语句，指定要复制的数据表。执行完成后，TiDB 会自动在 TiFlash 中创建该表的副本。以 `games` 表为例，创建 `games` 表的 SQL 语句如下： ```SQL /*!40014 SET FOREIGN_KEY_CHECKS=0*/; /*!40101 SET NAMES binary*/; CREATE TABLE `games` ( `app_id` int NOT NULL AUTO_INCREMENT, `name` varchar(256) DEFAULT NULL, `release_date` datetime DEFAULT NULL, `estimated_owners` varchar(256) DEFAULT NULL, `peak_ccu` int DEFAULT NULL, `required_age` int DEFAULT NULL, `price` decimal(10,0) DEFAULT NULL, `dlc_count` int DEFAULT NULL, `short_description` text DEFAULT NULL, `reviews` text DEFAULT NULL, `header_image` varchar(1024) DEFAULT NULL, `website` varchar(1024) DEFAULT NULL, `support_url` varchar(1024) DEFAULT NULL, `support_email` varchar(1024) DEFAULT NULL, `windows` tinyint(1) DEFAULT NULL, `mac` tinyint(1) DEFAULT NULL, `linux` tinyint(1) DEFAULT NULL, `metacritic_score` int DEFAULT NULL, `metacritic_url` varchar(1024) DEFAULT NULL, `user_score` int DEFAULT NULL, `positive` int DEFAULT NULL, `negative` int DEFAULT NULL, `score_rank` bigint DEFAULT NULL, `achievements` int DEFAULT NULL, `recommendations` int DEFAULT NULL, `notes` text DEFAULT NULL, `average_playtime_forever` int DEFAULT NULL, `average_playtime_two_weeks` int DEFAULT NULL, `median_playtime_forever` int DEFAULT NULL, `median_playtime_two_weeks` int DEFAULT NULL, `screenshots` text DEFAULT NULL, `movies` text DEFAULT NULL, PRIMARY KEY (`app_id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2294012; ``` 执行以下语句将 `games` 表复制到 TiFlash： ```SQL USE game; ``` ```SQL --为 games 表配置 2 份 TiFlash 列存副本 ALTER TABLE games SET TIFLASH REPLICA 2; ``` 检查复制进度，可以执行以下语句： ```SQL SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ID, REPLICA_COUNT, LOCATION_LABELS, AVAILABLE, PROGRESS FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA ='game' AND TABLE_NAME ='games'; ``` ```SQL +--------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+------------+----------+---------------+-----------------+-----------+----------+ | game | games | 88 | 2 | | 1 | 1 | +--------------+------------+----------+---------------+-----------------+-----------+----------+ 1 row in set (0.20 sec) ``` 在上述语句的结果中： * `AVAILABLE` 表示指定表的 TiFlash 副本是否可用。`1` 表示可用，`0` 表示不可用。一旦副本变为可用，该状态不会再变化。 * `PROGRESS` 表示复制的进度。取值范围为 `0` 到 `1`。`1` 表示至少有一个副本已完成复制。 ### 步骤 2 使用 HTAP 查询数据当复制过程完成后，您就可以开始运行查询语句了。例如，您可以统计每年发布的游戏数量，以及平均价格和平均游玩时长： ```SQL SELECT YEAR(`release_date`) AS `release_year`, COUNT(*) AS `games_released`, AVG(`price`) AS `average_price`, AVG(`average_playtime_forever`) AS `average_playtime` FROM `games` GROUP BY `release_year` ORDER BY `release_year` DESC; ``` ### 步骤 3 对比行存储与列存储的查询性能在此步骤中，您可以对比 TiKV（行存储）和 TiFlash（列存储）的执行统计信息。 * 若要获取该查询在 TiKV 上的执行统计信息，执行以下语句： ```SQL EXPLAIN ANALYZE SELECT /*+ READ_FROM_STORAGE(TIKV[games]) */ YEAR(`release_date`) AS `release_year`, COUNT(*) AS `games_released`, AVG(`price`) AS `average_price`, AVG(`average_playtime_forever`) AS `average_playtime` FROM `games` GROUP BY `release_year` ORDER BY `release_year` DESC; ``` 对于拥有 TiFlash 副本的表，TiDB 优化器会根据成本估算自动决定使用 TiKV 还是 TiFlash 副本。在上述 `EXPLAIN ANALYZE` 语句中，`/*+ READ_FROM_STORAGE(TIKV[games]) */` hint 用于强制优化器选择 TiKV，这样您可以查看 TiKV 的执行统计信息。

注意

5.7.7 之前的 MySQL 命令行客户端默认会去除优化器 Hint。如果您在这些早期版本中使用 Hint 语法，启动客户端时请加上 --comments 选项。例如：mysql -h 127.0.0.1 -P 4000 -uroot --comments。

在输出结果中，您可以从 `execution info` 列获取执行时间。 ```SQL id | estRows | actRows | task | access object | execution info | operator info | memory | disk ---------------------------+----------+---------+-----------+---------------+--------------------------------------------+-----------------------------------------------+---------+--------- Sort_5 | 4019.00 | 28 | root | | time:672.7ms, loops:2, RU:1159.679690 | Column#36:desc | 18.0 KB | 0 Bytes └─Projection_7 | 4019.00 | 28 | root | | time:672.7ms, loops:6, Concurrency:5 | year(game.games.release_date)->Column#36, ... | 35.5 KB | N/A └─HashAgg_15 | 4019.00 | 28 | root | | time:672.6ms, loops:6, partial_worker:... | group by:Column#38, funcs:count(Column#39)... | 56.7 KB | N/A └─TableReader_16 | 4019.00 | 28 | root | | time:672.4ms, loops:2, cop_task: {num:... | data:HashAgg_9 | 3.60 KB | N/A └─HashAgg_9 | 4019.00 | 28 | cop[tikv] | | tikv_task:{proc max:300ms, min:0s, avg... | group by:year(game.games.release_date), ... | N/A | N/A └─TableFullScan_14 | 68223.00 | 68223 | cop[tikv] | table:games | tikv_task:{proc max:290ms, min:0s, avg... | keep order:false | N/A | N/A (6 rows) ``` * 若要获取该查询在 TiFlash 上的执行统计信息，执行以下语句： ```SQL EXPLAIN ANALYZE SELECT YEAR(`release_date`) AS `release_year`, COUNT(*) AS `games_released`, AVG(`price`) AS `average_price`, AVG(`average_playtime_forever`) AS `average_playtime` FROM `games` GROUP BY `release_year` ORDER BY `release_year` DESC; ``` 在输出结果中，您可以从 `execution info` 列获取执行时间。 ```SQL id | estRows | actRows | task | access object | execution info | operator info | memory | disk -------------------------------------+----------+---------+--------------+---------------+-------------------------------------------------------+--------------------------------------------+---------+--------- Sort_5 | 4019.00 | 28 | root | | time:222.2ms, loops:2, RU:25.609675 | Column#36:desc | 3.77 KB | 0 Bytes └─TableReader_39 | 4019.00 | 28 | root | | time:222.1ms, loops:2, cop_task: {num: 2, max: 0s,... | MppVersion: 1, data:ExchangeSender_38 | 4.64 KB | N/A └─ExchangeSender_38 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:214.8ms, loops:1, threads:1} | ExchangeType: PassThrough | N/A | N/A └─Projection_8 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:214.8ms, loops:1, threads:1} | year(game.games.release_date)->Column#3... | N/A | N/A └─Projection_34 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:214.8ms, loops:1, threads:1} | Column#33, div(Column#34, cast(case(eq(... | N/A | N/A └─HashAgg_35 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:214.8ms, loops:1, threads:1} | group by:Column#63, funcs:sum(Column#64... | N/A | N/A └─ExchangeReceiver_37 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:214.8ms, loops:1, threads:8} | | N/A | N/A └─ExchangeSender_36 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:210.6ms, loops:1, threads:1} | ExchangeType: HashPartition, Compressio... | N/A | N/A └─HashAgg_33 | 4019.00 | 28 | mpp[tiflash] | | tiflash_task:{time:210.6ms, loops:1, threads:1} | group by:Column#75, funcs:count(1)->Col... | N/A | N/A └─Projection_40 | 68223.00 | 68223 | mpp[tiflash] | | tiflash_task:{time:210.6ms, loops:2, threads:8} | game.games.price, game.games.price, gam... | N/A | N/A └─TableFullScan_23 | 68223.00 | 68223 | mpp[tiflash] | table:games | tiflash_task:{time:210.6ms, loops:2, threads:8}, ... | keep order:false | N/A | N/A (11 rows) ```

注意

由于示例数据量较小，且本文档中的查询非常简单，如果您已经强制优化器选择 TiKV 并再次运行相同查询，TiKV 可能会复用其缓存，因此查询速度可能会更快。如果数据经常更新，则缓存会失效。