tiflash列式存储，tikv是行式存储，存储上就占有优势。tidb中创建表，默认都是tikv的。
1，检查tiflash是不是开启
$ tiup ctl pd -u http://10.0.10.17:2379 -i //-i交互模式
Found ctl newer version:
The latest version: v5.0.0-rc
Local installed version: v4.0.9
Update current component: tiup update ctl
Update all components: tiup update --all
Starting component `ctl`: /home/tidb/.tiup/components/ctl/v4.0.9/ctl pd -u http://10.0.10.17:2379 -i
» config show replication
{
"max-replicas": 3,
"location-labels": "",
"strictly-match-label": "false",
"enable-placement-rules": "true" //如果为ture就开启了
}
» config placement-rules enable //开启
» config placement-rules disable //关闭
» config show all //能看到集群中很多配置，但是新手不建议调整
2，创建测试表
mysql> CREATE TABLE `user` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
-> `sex` int(1) NOT NULL DEFAULT '0' COMMENT 'sex',
-> `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='user';
Query OK, 0 rows affected (0.19 sec)
mysql> insert into user(sex,name)values(1,'tank');
Query OK, 1 row affected (0.04 sec)
mysql> desc select * from user;
+------------------------+----------+-----------+---------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+----------+-----------+---------------+------------------------------------------------+
| TableReader_5 | 10000.00 | root | | data:TableRangeScan_4 |
| └─TableRangeScan_4 | 10000.00 | cop[tikv] | table:user | range:[0,+inf], keep order:false, stats:pseudo |
+------------------------+----------+-----------+---------------+------------------------------------------------+
2 rows in set (0.00 sec)
cop[tikv]，走的存储引擎是tikv
3，创建tiflash副本
mysql> ALTER TABLE user SET TIFLASH REPLICA 2; Query OK, 0 rows affected (0.29 sec) mysql> desc select * from user; +------------------------+---------+-----------+---------------+------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+-----------+---------------+------------------------------------------------+ | TableReader_5 | 1.00 | root | | data:TableRangeScan_4 | | └─TableRangeScan_4 | 1.00 | cop[tikv] | table:user | range:[0,+inf], keep order:false, stats:pseudo | +------------------------+---------+-----------+---------------+------------------------------------------------+ 2 rows in set (0.00 sec)
创建tiflash副本后，还是走的tikv。看看官方说明，对于创建了 TiFlash 副本的表，TiDB 优化器会自动根据代价估算选择是否使用 TiFlash 副本。具体有没有选择 TiFlash 副本，可以通过 desc 或 explain analyze 语句查看。
也就是说用不用tiflash，tidb自己选择
4，查看tiflash副本是不是创建成功
mysql> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'test' and TABLE_NAME = 'user'; +--------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+------------+----------+---------------+-----------------+-----------+----------+ | test | user | 9062 | 2 | | 1 | 1 | +--------------+------------+----------+---------------+-----------------+-----------+----------+ 1 row in set (0.00 sec) mysql> SELECT * FROM information_schema.tiflash_replica; +--------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+------------+----------+---------------+-----------------+-----------+----------+ | test | user | 9062 | 2 | | 1 | 1 | +--------------+------------+----------+---------------+-----------------+-----------+----------+ 1 row in set (0.00 sec)
AVAILABLE表示可用，PROGRESS表示生成副本的进度
mysql> select @@tidb_isolation_read_engines; //读的引擎有3种 +-------------------------------+ | @@tidb_isolation_read_engines | +-------------------------------+ | tikv, tiflash, tidb | +-------------------------------+ 1 row in set (0.00 sec) mysql> desc select /*+ read_from_storage(tikv[user] )*/ * from user; //强制使用tikv +------------------------+---------+-----------+---------------+------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+-----------+---------------+------------------------------------------------+ | TableReader_5 | 1.00 | root | | data:TableRangeScan_4 | | └─TableRangeScan_4 | 1.00 | cop[tikv] | table:user | range:[0,+inf], keep order:false, stats:pseudo | +------------------------+---------+-----------+---------------+------------------------------------------------+ 2 rows in set (0.00 sec) mysql> desc select /*+ read_from_storage(tiflash[user] )*/ * from user; //强制使用tiflash +------------------------+---------+--------------+---------------+------------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+--------------+---------------+------------------------------------------------+ | TableReader_5 | 1.00 | root | | data:TableRangeScan_4 | | └─TableRangeScan_4 | 1.00 | cop[tiflash] | table:user | range:[0,+inf], keep order:false, stats:pseudo | +------------------------+---------+--------------+---------------+------------------------------------------------+ 2 rows in set (0.00 sec)
5，正式数据测试速度
mysql> desc select count(1) from video_report; +----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+ | id | estRows | task | access object | operator info | +----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+ | StreamAgg_24 | 1.00 | root | | funcs:count(Column#88)->Column#77 | | └─IndexReader_25 | 1.00 | root | | index:StreamAgg_8 | | └─StreamAgg_8 | 1.00 | cop[tikv] | | funcs:count(1)->Column#88 | | └─IndexFullScan_23 | 8123356.00 | cop[tikv] | table:video_report, index:video_report_signature_index(signature) | keep order:false | +----------------------------+------------+-----------+-------------------------------------------------------------------+-----------------------------------+ 4 rows in set (0.00 sec) mysql> select count(1) from video_report; +----------+ | count(1) | +----------+ | 8218538 | +----------+ 1 row in set (0.61 sec) mysql> ALTER TABLE video_report SET TIFLASH REPLICA 2; Query OK, 0 rows affected (0.25 sec) mysql> SELECT * FROM information_schema.tiflash_replica; +--------------+--------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+--------------+----------+---------------+-----------------+-----------+----------+ | result_ziwei | video_report | 9030 | 2 | | 0 | 0.05 | | test | user | 9062 | 2 | | 1 | 1 | +--------------+--------------+----------+---------------+-----------------+-----------+----------+ 2 rows in set (0.00 sec) mysql> select count(1) from video_report; +----------+ | count(1) | +----------+ | 8218538 | +----------+ 1 row in set (0.05 sec)
要等一会，等到AVAILABLE变成1，PROGRESS变成1才能测试，测试的结果800多万，tiflash比tikv要快10倍左右。
6，删除tiflash副本
mysql> ALTER TABLE user SET TIFLASH REPLICA 0; Query OK, 0 rows affected (0.18 sec) mysql> SELECT * FROM information_schema.tiflash_replica; +--------------+--------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+--------------+----------+---------------+-----------------+-----------+----------+ | result_ziwei | video_report | 9030 | 2 | | 1 | 1 | +--------------+--------------+----------+---------------+-----------------+-----------+----------+ 1 row in set (0.00 sec)
转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/tidb/2508.html