使用以下命令安装数据生成工具：
tiup install bench
使用以下命令生成数据：
tiup bench tpch --sf=1 prepare
当命令行输出 Finished 时，表示数据生成完毕。
generate customers table done
generating suppliers table
generate suppliers table done
generating part/partsupplier tables
generate part/partsupplier tables done
generating orders/lineitem tables
Finished
generate orders/lineitem tables done
Finished
运行以下 SQL 语句查看生成的数据：
[root@VM-3-11-centos ~]# mysql -h 10.0.3.11 -P 4000 -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.25-TiDB-v5.2.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', table_rows AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'test';
+---------------+----------------+-----------+------------+---------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+---------------+----------------+-----------+------------+---------+
| test.nation | 25 | 0.0000G | 0.0000G | 0.0000G |
| test.region | 5 | 0.0000G | 0.0000G | 0.0000G |
| test.part | 209216 | 0.0254G | 0.0000G | 0.0254G |
| test.supplier | 10000 | 0.0014G | 0.0000G | 0.0014G |
| test.partsupp | 800000 | 0.1174G | 0.0119G | 0.1293G |
| test.customer | 150000 | 0.0242G | 0.0000G | 0.0242G |
| test.orders | 1509216 | 0.1688G | 0.0000G | 0.1688G |
| test.lineitem | 6055487 | 0.7742G | 0.0902G | 0.8644G |
+---------------+----------------+-----------+------------+---------+
8 rows in set (0.01 sec)
这是一个商业订购系统的数据库。其中，test.nation 表是国家信息、test.region 表是地区信息、test.part 表是零件信息、test.supplier 表是供货商信息、test.partsupp 表是供货商的零件信息、test.customer 表是消费者信息、test.orders 表是订单信息、test.lineitem 表是在线商品的信息
第 3 步：使用行存查询数据
执行以下 SQL 语句，你可以体验当只使用行存（大多数数据库）时 TiDB 的表现：
–执行聚合查询看执行完成是2.17秒
MySQL [test]> SELECT
l_orderkey,
SUM(
l_extendedprice * (1 - l_discount)
) AS revenue,
o_orderdate,
o_shippriority
FROM
customer,
orders,
lineitem
WHERE
c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1996-01-01'
AND l_shipdate > DATE '1996-02-01'
GROUP BY
l_orderkey,
o_orderdate,
o_shippriority
ORDER BY
revenue DESC,
o_orderdate
limit 10;
+------------+-------------+-------------+----------------+
| l_orderkey | revenue | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
| 5828933 | 386117.1688 | 1995-12-03 | 0 |
| 1648647 | 373096.8642 | 1995-12-06 | 0 |
| 1364641 | 352640.6056 | 1995-12-19 | 0 |
| 3949606 | 347750.4435 | 1995-12-23 | 0 |
| 4792161 | 347715.0509 | 1995-12-30 | 0 |
| 4340739 | 347490.5251 | 1995-12-06 | 0 |
| 1609574 | 342497.8886 | 1995-12-31 | 0 |
| 3076934 | 338202.3259 | 1995-12-24 | 0 |
| 3232933 | 337349.2536 | 1995-12-26 | 0 |
| 2345058 | 335142.6104 | 1995-12-31 | 0 |
+------------+-------------+-------------+----------------+
10 rows in set (2.17 sec)
这是一个运送优先权查询，用于给出在指定日期之前尚未运送的订单中收入最高订单的优先权和潜在的收入。潜在的收入被定义为 l_extendedprice * (1-l_discount) 的和。订单按照收入的降序列出。在本示例中，此查询将列出潜在查询收入在前 10 的尚未运送的订单。
第 4 步：同步列存数据
TiFlash 部署完成后并不会自动同步 TiKV 数据，你可以在 MySQL 客户端向 TiDB 发送以下 DDL 命令指定需要同步到 TiFlash 的表。指定后，TiDB 将创建对应的 TiFlash 副本。
MySQL [test]> ALTER TABLE test.customer SET TIFLASH REPLICA 1;
Query OK, 0 rows affected (0.10 sec)
MySQL [test]> ALTER TABLE test.orders SET TIFLASH REPLICA 1;
Query OK, 0 rows affected (0.13 sec)
MySQL [test]> ALTER TABLE test.lineitem SET TIFLASH REPLICA 1;
Query OK, 0 rows affected (0.10 sec)
如需查询 TiFlash 表的同步状态
MySQL [test]> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'customer';
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test | customer | 63 | 1 | | 1 | 1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.01 sec)
MySQL [test]> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'orders';
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test | orders | 65 | 1 | | 1 | 1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)
MySQL [test]> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'lineitem';
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test | lineitem | 67 | 1 | | 0 | 0.07 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)
以上查询结果中：
AVAILABLE 字段表示该表的 TiFlash 副本是否可用。1 代表可用，0 代表不可用。副本状态变为可用之后就不再改变，如果通过 DDL 命令修改副本数则会重新计算同步进度。
PROGRESS 字段代表同步进度，在 0.0~1.0 之间，1 代表至少 1 个副本已经完成同步。
--刚才查询lineitem表副本不可用，再次查询已经可用
MySQL [test]> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'lineitem';
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| test | lineitem | 67 | 1 | | 1 | 1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.00 sec)
把数据同步到TiFlash后测试一下查询的速度据说TiFlash是列式存储，聚合查询，sum,count,avg这类的查询应该很快的
–查询速度为0.23秒确实提高很快
MySQL [test]> SELECT
-> l_orderkey,
-> SUM(
-> l_extendedprice * (1 - l_discount)
-> ) AS revenue,
-> o_orderdate,
-> o_shippriority
-> FROM
-> customer,
-> orders,
-> lineitem
-> WHERE
-> c_mktsegment = 'BUILDING'
-> AND c_custkey = o_custkey
-> AND l_orderkey = o_orderkey
-> AND o_orderdate < DATE '1996-01-01'
-> AND l_shipdate > DATE '1996-02-01'
-> GROUP BY
-> l_orderkey,
-> o_orderdate,
-> o_shippriority
-> ORDER BY
-> revenue DESC,
-> o_orderdate
-> limit 10;
+------------+-------------+-------------+----------------+
| l_orderkey | revenue | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
| 5828933 | 386117.1688 | 1995-12-03 | 0 |
| 1648647 | 373096.8642 | 1995-12-06 | 0 |
| 1364641 | 352640.6056 | 1995-12-19 | 0 |
| 3949606 | 347750.4435 | 1995-12-23 | 0 |
| 4792161 | 347715.0509 | 1995-12-30 | 0 |
| 4340739 | 347490.5251 | 1995-12-06 | 0 |
| 1609574 | 342497.8886 | 1995-12-31 | 0 |
| 3076934 | 338202.3259 | 1995-12-24 | 0 |
| 3232933 | 337349.2536 | 1995-12-26 | 0 |
| 2345058 | 335142.6104 | 1995-12-31 | 0 |
+------------+-------------+-------------+----------------+
10 rows in set (0.23 sec)
查看你tidb执行计划需要加上explain analyze，很像oracle的执行计划，执行步骤之间有缩进，也可以明显看到tiflash关键字说明使用了tiflash,
explain analyze SELECT
l_orderkey,
SUM(
l_extendedprice * (1 - l_discount)
) AS revenue,
o_orderdate,
o_shippriority
FROM
customer,
orders,
lineitem
WHERE
c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1996-01-01'
AND l_shipdate > DATE '1996-02-01'
GROUP BY
l_orderkey,
o_orderdate,
o_shippriority
ORDER BY
revenue DESC,
o_orderdate
limit 10;
+----------------------------------------------------------------+------------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------------------------------+------------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_14 | 10.00 | 10 | root | | time:446.3ms, loops:2, Concurrency:OFF | test.lineitem.l_orderkey, Column#35, test.orders.o_orderdate, test.orders.o_shippriority | 2.48 KB | N/A |
| └─TopN_18 | 10.00 | 10 | root | | time:446.3ms, loops:2 | Column#35:desc, test.orders.o_orderdate, offset:0, count:10 | 1.20 KB | N/A |
| └─TableReader_142 | 10.00 | 10 | root | | time:446.3ms, loops:2, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_141 | N/A | N/A |
| └─ExchangeSender_141 | 10.00 | 10 | batchCop[tiflash] | | tiflash_task:{time:442.7ms, loops:1, threads:8} | ExchangeType: PassThrough | N/A | N/A |
| └─TopN_140 | 10.00 | 10 | batchCop[tiflash] | | tiflash_task:{time:442.7ms, loops:1, threads:1} | Column#35:desc, test.orders.o_orderdate, offset:0, count:10 | N/A | N/A |
| └─Projection_136 | 1054519.51 | 7664 | batchCop[tiflash] | | tiflash_task:{time:441.7ms, loops:1, threads:8} | Column#35, test.orders.o_orderdate, test.orders.o_shippriority, test.lineitem.l_orderkey | N/A | N/A |
| └─HashAgg_137 | 1054519.51 | 7664 | batchCop[tiflash] | | tiflash_task:{time:440.7ms, loops:1, threads:1} | group by:test.lineitem.l_orderkey, test.orders.o_orderdate, test.orders.o_shippriority, funcs:sum(Column#52)->Column#35, funcs:firstrow(test.orders.o_orderdate)->test.orders.o_orderdate, funcs:firstrow(test.orders.o_shippriority)->test.orders.o_shippriority, funcs:firstrow(test.lineitem.l_orderkey)->test.lineitem.l_orderkey | N/A | N/A |
| └─ExchangeReceiver_139 | 1054519.51 | 7664 | batchCop[tiflash] | | tiflash_task:{time:435.7ms, loops:1, threads:8} | | N/A | N/A |
| └─ExchangeSender_138 | 1054519.51 | 7664 | batchCop[tiflash] | | tiflash_task:{time:429.2ms, loops:1, threads:8} | ExchangeType: HashPartition, Hash Cols: [name: test.lineitem.l_orderkey, collate: N/A], [name: test.orders.o_orderdate, collate: N/A], [name: test.orders.o_shippriority, collate: N/A] | N/A | N/A |
| └─HashAgg_134 | 1054519.51 | 7664 | batchCop[tiflash] | | tiflash_task:{time:429.2ms, loops:1, threads:1} | group by:Column#57, Column#58, Column#59, funcs:sum(Column#56)->Column#52 | N/A | N/A |
| └─Projection_143 | 1225116.95 | 16655 | batchCop[tiflash] | | tiflash_task:{time:426.2ms, loops:98, threads:8} | mul(test.lineitem.l_extendedprice, minus(1, test.lineitem.l_discount))->Column#56, test.lineitem.l_orderkey, test.orders.o_orderdate, test.orders.o_shippriority | N/A | N/A |
| └─HashJoin_123 | 1225116.95 | 16655 | batchCop[tiflash] | | tiflash_task:{time:423.2ms, loops:98, threads:8} | inner join, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)] | N/A | N/A |
| ├─ExchangeReceiver_55(Build) | 302319.37 | 184087 | batchCop[tiflash] | | tiflash_task:{time:361.2ms, loops:8, threads:8} | | N/A | N/A |
| │ └─ExchangeSender_54 | 302319.37 | 184087 | batchCop[tiflash] | | tiflash_task:{time:353.7ms, loops:28, threads:3} | ExchangeType: HashPartition, Hash Cols: [name: test.orders.o_orderkey, collate: N/A] | N/A | N/A |
| │ └─HashJoin_47 | 302319.37 | 184087 | batchCop[tiflash] | | tiflash_task:{time:353.7ms, loops:28, threads:3} | inner join, equal:[eq(test.customer.c_custkey, test.orders.o_custkey)] | N/A | N/A |
| │ ├─ExchangeReceiver_51(Build) | 20180.00 | 30142 | batchCop[tiflash] | | tiflash_task:{time:262.7ms, loops:2, threads:8} | | N/A | N/A |
| │ │ └─ExchangeSender_50 | 20180.00 | 30142 | batchCop[tiflash] | | tiflash_task:{time:243.7ms, loops:3, threads:1} | ExchangeType: Broadcast | N/A | N/A |
| │ │ └─Selection_49 | 20180.00 | 30142 | batchCop[tiflash] | | tiflash_task:{time:243.7ms, loops:3, threads:1} | eq(test.customer.c_mktsegment, "BUILDING") | N/A | N/A |
| │ │ └─TableFullScan_48 | 150000.00 | 150000 | batchCop[tiflash] | table:customer | tiflash_task:{time:241.7ms, loops:3, threads:1} | keep order:false | N/A | N/A |
| │ └─Selection_53(Probe) | 939804.27 | 909968 | batchCop[tiflash] | | tiflash_task:{time:283.7ms, loops:28, threads:3} | lt(test.orders.o_orderdate, 1996-01-01) | N/A | N/A |
| │ └─TableFullScan_52 | 1509216.00 | 1500000 | batchCop[tiflash] | table:orders | tiflash_task:{time:269.7ms, loops:28, threads:3} | keep order:false | N/A | N/A |
| └─ExchangeReceiver_59(Probe) | 4273327.69 | 2432047 | batchCop[tiflash] | | tiflash_task:{time:381.2ms, loops:98, threads:8} | | N/A | N/A |
| └─ExchangeSender_58 | 4273327.69 | 2432047 | batchCop[tiflash] | | tiflash_task:{time:318.9ms, loops:104, threads:8} | ExchangeType: HashPartition, Hash Cols: [name: test.lineitem.l_orderkey, collate: N/A] | N/A | N/A |
| └─Selection_57 | 4273327.69 | 2432047 | batchCop[tiflash] | | tiflash_task:{time:318.9ms, loops:104, threads:8} | gt(test.lineitem.l_shipdate, 1996-02-01) | N/A | N/A |
| └─TableFullScan_56 | 6055487.00 | 6001215 | batchCop[tiflash] | table:lineitem | tiflash_task:{time:291.9ms, loops:104, threads:8} | keep order:false | N/A | N/A |
+----------------------------------------------------------------+------------+---------+-------------------+----------------+----------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
25 rows in set (0.64 sec)
3299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
