clickhouse,tidb,mysql 读取速度对比

clickhouse，tidb，mysql，这三个数据库都是现在在用的。准备了一些测试数据，测试一下读取数据的速度。

一，硬件说明

1，mysql环境，普通的台机，没人用。4核32G

2，tidb正式环境，官网建议的硬件配置，价格在40w左右

3，tidb测试环境，总共7台机器，价格在5W左右

4，clickhouse测试环境，用了tidb测试环境中的四台机器，价格在3W左右

二，单表测试

//mysql
mysql> select count(1) from `tanktest`.`material_upload`;
+----------+
| count(1) |
+----------+
|  4176296 |
+----------+
1 row in set (1.13 sec)

//tidb测试
mysql> select count(1) from `saas`.`material_upload` ;
+----------+
| count(1) |
+----------+
|  4177813 |
+----------+
1 row in set (0.33 sec)

//tidb正式
mysql> select count(1) from `saas`.`material_upload` ;
+----------+
| count(1) |
+----------+
|  4515864 |
+----------+
1 row in set (0.06 sec)

//clickhouse测试
testticdc :] select count(1) from `tanktest`.`material_upload_all`;

SELECT count(1)
FROM tanktest.material_upload_all

┌─count(1)─┐
│  4176296 │
└──────────┘

1 rows in set. Elapsed: 0.010 sec.

clickhouse>tidb正式>tidb测试>mysql

三，联表查询

//mysql
mysql> select count(1) from `tanktest`.`material_upload` as `a` left join `tanktest`.`material_video` as `b` on `a`.`video_id` = `b`.`video_id`;
+----------+
| count(1) |
+----------+
|  4176296 |
+----------+
1 row in set (6.06 sec)

//正式tidb
mysql> select count(1) from `saas`.`material_upload` as `a` left join `saas`.`material_video` as `b` on `a`.`video_id` = `b`.`video_id`;
+----------+
| count(1) |
+----------+
|  4513815 |
+----------+
1 row in set (0.85 sec)

//测试tidb
mysql> select count(1) from `saas`.`material_upload` as `a` left join `saas`.`material_video` as `b` on `a`.`video_id` = `b`.`video_id`;
+----------+
| count(1) |
+----------+
|  4176296 |
+----------+
1 row in set (1.75 sec)

//clickhouse测试环境，大表在左，快
testticdc :] select count(1) from `tanktest`.`material_upload_all` as `a` left join `tanktest`.`material_video_all` as `b` on `a`.`video_id` = `b`.`video_id`;

SELECT count(1)
FROM tanktest.material_upload_all AS a
LEFT JOIN tanktest.material_video_all AS b ON a.video_id = b.video_id

┌─count(1)─┐
│  4176296 │
└──────────┘

1 rows in set. Elapsed: 0.244 sec. Processed 5.78 million rows, 144.61 MB (23.66 million rows/s., 592.34 MB/s.) 

//clickhouse，小表在左，慢
testticdc :] select count(1) from `tanktest`.`material_video_all` as `a` left join `tanktest`.`material_upload_all` as `b` on `a`.`video_id` = `b`.`video_id`;

SELECT count(1)
FROM tanktest.material_video_all AS a
LEFT JOIN tanktest.material_upload_all AS b ON a.video_id = b.video_id

┌─count(1)─┐
│  4254696 │
└──────────┘

1 rows in set. Elapsed: 0.485 sec. Processed 9.15 million rows, 228.77 MB (18.89 million rows/s., 472.06 MB/s.)

clickhouse左边大表>clickhouse左边小表>tidb正式>tidb测试>mysql