clickhouse,tidb,mysql 读取速度对比

张映 发表于 2021-09-10

分类目录: clickhouse, tidb

标签:, ,

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

 



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/tidb/2533.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   7X4=?