GBase 8a的Global hash索引查询性能提升测试

本文介绍GBase 8a的为精确查询提供的Global hash索引查询性能提升测试。

测试结果

global hash索引,可以提升简单精确查询的性能10-100倍。 其中重复度越低,性能提升效果越好。

环境

硬件

笔记本的虚拟机, 4C, 4G. redhat 7

版本

9.5.2, 该功能没有做过改动。

数据

2字段表,1.2亿行,其中id有32K个不同值,name 有1亿个不通值。

gbase> desc tt1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> select count(*), count(distinct id),count(distinct name) from tt1;
+-----------+--------------------+----------------------+
| count(*)  | count(distinct id) | count(distinct name) |
+-----------+--------------------+----------------------+
| 127135509 |              32768 |             10000002 |
+-----------+--------------------+----------------------+
1 row in set (Elapsed: 00:06:52.89)

测试过程

无索引查询id

结果集3700行,基本在0.4-0.5秒。

gbase> select count(*) from tt1 where id=10;
+----------+
| count(*) |
+----------+
|     3717 |
+----------+
1 row in set (Elapsed: 00:00:00.88)

gbase> select count(*) from tt1 where id=10;
+----------+
| count(*) |
+----------+
|     3717 |
+----------+
1 row in set (Elapsed: 00:00:00.46)

gbase> select count(*) from tt1 where id=10;
+----------+
| count(*) |
+----------+
|     3717 |
+----------+
1 row in set (Elapsed: 00:00:00.47)

从trc日志看,首次加载了几百个DC,然后扫描,耗时0.4秒。 后续是热数据,时间0.4秒花费纯扫描上。

SELECT  COUNT(1) FROM `testdb`.`tt1_n1`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`id` = 10)
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n1),         63567786 rows,   970 DC
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.id BET. 10 AND 10       (0)
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 08:51:02.062 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.id BET. 10 AND 10
2021-08-16 08:51:02.628 [M: 139M,   0B,D:   0B] [DC:     2,   508] ( 1)cnd(0)   scanned 15859712 rows,  found 469 rows (time used: 0.565s)
2021-08-16 08:51:02.832 [M: 229M,   0B,D:   0B] [DC:     0,   859] ( 2)cnd(0)   scanned 15925248 rows,  found 461 rows (time used: 0.769s)
2021-08-16 08:51:02.873 [M: 247M,   0B,D:   0B] [DC:     0,   947] ( 0)cnd(0)   scanned 15925248 rows,  found 460 rows (time used: 0.811s)
2021-08-16 08:51:02.897 [M: 254M,   0B,D:   0B] [DC:     0,   970] ( 3)cnd(0)   scanned 15857578 rows,  found 477 rows (time used: 0.835s)
2021-08-16 08:51:02.900 [M: 254M,   0B,D:   0B] [DC:     0,   970] cnd(0) Done. scanned 63567786 rows,  found 1867 rows (time used: 0.838s)
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] T0:          total 970 DC,   found 828 DC after scan(with 0 FULL DC).
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] END Scan. (time used: 0.839s)
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] Condition Weight (join):
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] BEGIN Join
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] END Join(time used: 0.000s)
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] BEGIN Scan(after join)
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] END Scan(after join)(time used: 0.000s)
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] CalculatePageSize: TmpCacheSize = 32000000, no obj = 63567786, attr count = 1, record size = 8, page size = 4194304
2021-08-16 08:51:02.901 [M: 255M,  32B,D:   0B] [DC:     0,   970] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     0,   970] BEGIN Serial Aggregation(1867 rows)
2021-08-16 08:51:02.901 [M: 255M,  32B,D:   0B] [DC:     0,   970] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:02.901 [M: 255M,  64B,D:   0B] [DC:     0,   970] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:02.901 [M: 255M,  32B,D:   0B] [DC:     0,   970] BEGIN Aggregation(1867 rows)
2021-08-16 08:51:02.901 [M: 255M,  64B,D:   0B] [DC:     0,   970] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:02.901 [M: 255M,  64B,D:   0B] [DC:     0,   970] TempTable::MultiDimensionalGroupByScan(): aggregate 1867 rows)
2021-08-16 08:51:02.901 [M: 255M,  64B,D:   0B] [DC:     0,   970] CalculatePageSize: TmpCacheSize = 32000000, no obj = 1, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:02.901 [M: 255M,  64B,D:   0B] [DC:     0,   970] CalculatePageSize: TmpCacheSize = 32000000, no obj = 1, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:02.901 [M: 255M,  32B,D:   0B] [DC:     0,   970] END Serial Aggregation. (time used: 0.000s)
2021-08-16 08:51:02.901 [M: 255M,   0B,D:   0B] [DC:     1,   970] Send 1 rows already
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] CalculatePageSize: TmpCacheSize = 32000000, no obj = 65536, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] ResultSender: send 0 rows.
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] output result done.

2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] SUMMARY
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] elapsed time:                00:00:00.840
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] data loaded from storage:     123M,  2.169s,         970 DC.
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] data decompressed:            134M,  0.028s.
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] temp space IO stats:
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:02.902 [M: 255M,   0B,D:   0B] [DC:     1,   970] ======================================================
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     1,   970] before opt:select count(1) AS "COUNT(1)" from "testdb"."tt1_n1" "vcname000001.testdb.tt1" where ("testdb"."vcname000001.testdb.tt1"."id" = 10)
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     1,   970] after  opt:select count(1) AS "COUNT(1)" from "testdb"."tt1_n1" "vcname000001.testdb.tt1" where ("testdb"."vcname000001.testdb.tt1"."id" = 10)
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n1`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`id` = 10)
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n1),         63567786 rows,   970 DC
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.id BET. 10 AND 10       (0)
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 08:51:07.970 [M: 261M,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.id BET. 10 AND 10
2021-08-16 08:51:08.201 [M: 261M,   0B,D:   0B] [DC:   612,     0] ( 0)cnd(0)   scanned 15925248 rows,  found 460 rows (time used: 0.230s)
2021-08-16 08:51:08.390 [M: 261M,   0B,D:   0B] [DC:   951,     0] ( 2)cnd(0)   scanned 15925248 rows,  found 461 rows (time used: 0.409s)
2021-08-16 08:51:08.393 [M: 261M,   0B,D:   0B] [DC:   953,     0] ( 1)cnd(0)   scanned 15859712 rows,  found 469 rows (time used: 0.422s)
2021-08-16 08:51:08.408 [M: 261M,   0B,D:   0B] [DC:   970,     0] ( 3)cnd(0)   scanned 15857578 rows,  found 477 rows (time used: 0.428s)
2021-08-16 08:51:08.411 [M: 261M,   0B,D:   0B] [DC:   970,     0] cnd(0) Done. scanned 63567786 rows,  found 1867 rows (time used: 0.440s)
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] T0:          total 970 DC,   found 828 DC after scan(with 0 FULL DC).
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] END Scan. (time used: 0.441s)
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] Condition Weight (join):
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] BEGIN Join
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] END Join(time used: 0.000s)
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] BEGIN Scan(after join)
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] END Scan(after join)(time used: 0.000s)
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 63567786, attr count = 1, record size = 8, page size = 4194304
2021-08-16 08:51:08.412 [M: 261M,  32B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   970,     0] BEGIN Serial Aggregation(1867 rows)
2021-08-16 08:51:08.412 [M: 261M,  32B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:08.412 [M: 261M,  64B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:08.412 [M: 261M,  32B,D:   0B] [DC:   970,     0] BEGIN Aggregation(1867 rows)
2021-08-16 08:51:08.412 [M: 261M,  64B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:08.412 [M: 261M,  64B,D:   0B] [DC:   970,     0] TempTable::MultiDimensionalGroupByScan(): aggregate 1867 rows)
2021-08-16 08:51:08.412 [M: 261M,  64B,D:   0B] [DC:   970,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 1, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:08.412 [M: 261M,  64B,D:   0B] [DC:   970,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 1, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:08.412 [M: 262M,  32B,D:   0B] [DC:   970,     0] END Serial Aggregation. (time used: 0.000s)
2021-08-16 08:51:08.412 [M: 262M,   0B,D:   0B] [DC:   971,     0] Send 1 rows already
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   971,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 65536, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   971,     0] ResultSender: send 0 rows.
2021-08-16 08:51:08.412 [M: 261M,   0B,D:   0B] [DC:   971,     0] output result done.

2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] SUMMARY
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] elapsed time:                00:00:00.443
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] data loaded from storage:       0B,  0s,     0 DC.
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] data decompressed:              0B,  0s.
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] temp space IO stats:
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:08.413 [M: 261M,   0B,D:   0B] [DC:   971,     0] ======================================================
2021-08-16 08:51:10.611 [M: 261M,   0B,D:   0B] [DC:   971,     0] before opt:select count(1) AS "COUNT(1)" from "testdb"."tt1_n1" "vcname000001.testdb.tt1" where ("testdb"."vcname000001.testdb.tt1"."id" = 10)
2021-08-16 08:51:10.611 [M: 261M,   0B,D:   0B] [DC:   971,     0] after  opt:select count(1) AS "COUNT(1)" from "testdb"."tt1_n1" "vcname000001.testdb.tt1" where ("testdb"."vcname000001.testdb.tt1"."id" = 10)
2021-08-16 08:51:10.611 [M: 261M,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n1`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`id` = 10)
2021-08-16 08:51:10.611 [M: 261M,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n1),         63567786 rows,   970 DC
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.id BET. 10 AND 10       (0)
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 08:51:10.612 [M: 261M,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.id BET. 10 AND 10
2021-08-16 08:51:10.857 [M: 261M,   0B,D:   0B] [DC:   595,     0] ( 2)cnd(0)   scanned 15925248 rows,  found 461 rows (time used: 0.245s)
2021-08-16 08:51:10.966 [M: 261M,   0B,D:   0B] [DC:   833,     0] ( 0)cnd(0)   scanned 15925248 rows,  found 460 rows (time used: 0.345s)
2021-08-16 08:51:11.025 [M: 261M,   0B,D:   0B] [DC:   933,     0] ( 1)cnd(0)   scanned 15859712 rows,  found 469 rows (time used: 0.403s)
2021-08-16 08:51:11.055 [M: 261M,   0B,D:   0B] [DC:   970,     0] ( 3)cnd(0)   scanned 15857578 rows,  found 477 rows (time used: 0.433s)
2021-08-16 08:51:11.059 [M: 261M,   0B,D:   0B] [DC:   970,     0] cnd(0) Done. scanned 63567786 rows,  found 1867 rows (time used: 0.447s)
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] T0:          total 970 DC,   found 828 DC after scan(with 0 FULL DC).
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] END Scan. (time used: 0.451s)
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] Condition Weight (join):
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] BEGIN Join
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] END Join(time used: 0.000s)
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] BEGIN Scan(after join)
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] END Scan(after join)(time used: 0.000s)
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 63567786, attr count = 1, record size = 8, page size = 4194304
2021-08-16 08:51:11.063 [M: 261M,  32B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:11.063 [M: 261M,   0B,D:   0B] [DC:   970,     0] BEGIN Serial Aggregation(1867 rows)
2021-08-16 08:51:11.064 [M: 261M,  32B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:11.064 [M: 261M,  64B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:11.064 [M: 261M,  32B,D:   0B] [DC:   970,     0] BEGIN Aggregation(1867 rows)
2021-08-16 08:51:11.064 [M: 261M,  64B,D:   0B] [DC:   970,     0] GTBuff: total_size:33554432 has_dist:0 rows_limit:1 width:8
2021-08-16 08:51:11.064 [M: 261M,  64B,D:   0B] [DC:   970,     0] TempTable::MultiDimensionalGroupByScan(): aggregate 1867 rows)
2021-08-16 08:51:11.064 [M: 261M,  64B,D:   0B] [DC:   970,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 1, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:11.064 [M: 261M,  64B,D:   0B] [DC:   970,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 1, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:11.064 [M: 262M,  32B,D:   0B] [DC:   970,     0] END Serial Aggregation. (time used: 0.000s)
2021-08-16 08:51:11.064 [M: 262M,   0B,D:   0B] [DC:   971,     0] Send 1 rows already
2021-08-16 08:51:11.064 [M: 261M,   0B,D:   0B] [DC:   971,     0] CalculatePageSize: TmpCacheSize = 32000000, no obj = 65536, attr count = 1, record size = 8, page size = 65536
2021-08-16 08:51:11.064 [M: 261M,   0B,D:   0B] [DC:   971,     0] ResultSender: send 0 rows.
2021-08-16 08:51:11.064 [M: 261M,   0B,D:   0B] [DC:   971,     0] output result done.

2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] SUMMARY
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] elapsed time:                00:00:00.453
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] data loaded from storage:       0B,  0s,     0 DC.
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] data decompressed:              0B,  0s.
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] temp space IO stats:
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 08:51:11.065 [M: 261M,   0B,D:   0B] [DC:   971,     0] ======================================================

有索引查询id

带索引查询,基本在0.02秒。比无索引的快了10倍。

gbase> select count(*) from tt1 where id=10;
+----------+
| count(*) |
+----------+
|     3717 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

gbase> select count(*) from tt1 where id=1000;
+----------+
| count(*) |
+----------+
|     4402 |
+----------+
1 row in set (Elapsed: 00:00:00.02)

查看trc, 可以看到采用了global hash索引扫描,而不是dc扫描

2021-08-16 14:58:11.969 [M:   3M,   0B,D:   0B] [DC:     0,     0] Global hash index is used:

完整trc

2021-08-16 14:54:35.423 [M:   0B,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n1`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`id` = 10)
2021-08-16 14:54:35.423 [M:   0B,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n1),         63567786 rows,   970 DC
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.id BET. 10 AND 10       (0)
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 14:54:35.424 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.id BET. 10 AND 10
2021-08-16 14:54:35.433 [M:   0B,  64B,D:   0B] [DC:     0,     0] Global hash index is used:
2021-08-16 14:54:35.433 [M: 516K,  64B,D:   0B] [DC:     0,     0] cnd(0)       scanned 1867 rows,      found 1867 rows (time used: 0.000s)
2021-08-16 14:54:35.433 [M: 516K,  32B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 828 DC after scan(with 0 FULL DC).
2021-08-16 14:54:35.433 [M: 516K,  32B,D:   0B] [DC:     0,     0] END Scan. (time used: 0.010s)
2021-08-16 14:54:35.434 [M:   0B,   0B,D:   0B] [DC:     0,     0] Condition Weight (join):
2021-08-16 14:54:35.434 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Join
2021-08-16 14:54:35.434 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Join(time used: 0.000s)
2021-08-16 14:54:35.434 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Scan(after join)
2021-08-16 14:54:35.434 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Scan(after join)(time used: 0.000s)
2021-08-16 14:54:35.434 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(1867 rows)
2021-08-16 14:54:35.434 [M:   0B,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(1867 rows)
2021-08-16 14:54:35.434 [M:   0B,  64B,D:   0B] [DC:     0,     0] TempTable::MultiDimensionalGroupByScan(): aggregate 1867 rows)
2021-08-16 14:54:35.435 [M: 516K,  32B,D:   0B] [DC:     0,     0] END Serial Aggregation. (time used: 0.001s)
2021-08-16 14:54:35.435 [M: 516K,   0B,D:   0B] [DC:     1,     0] Send 1 rows already
2021-08-16 14:54:35.435 [M:   0B,   0B,D:   0B] [DC:     1,     0] ResultSender: send 0 rows.
2021-08-16 14:54:35.435 [M:   0B,   0B,D:   0B] [DC:     1,     0] output result done.

2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] SUMMARY
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] elapsed time:                00:00:00.013
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] data loaded from storage:       0B,  0s,     0 DC.
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] data decompressed:              0B,  0s.
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] temp space IO stats:
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:54:35.436 [M:   0B,   0B,D:   0B] [DC:     1,     0] ======================================================
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n1`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`id` = 1000)
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n1),         63567786 rows,   970 DC
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.id BET. 1000 AND 1000   (0)
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 14:55:26.397 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.id BET. 1000 AND 1000
2021-08-16 14:55:26.404 [M:   0B,   0B,D:   0B] [DC:     0,     0] Global hash index is used:
2021-08-16 14:55:26.404 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0)       scanned 2180 rows,      found 2180 rows (time used: 0.000s)
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 882 DC after scan(with 0 FULL DC).
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Scan. (time used: 0.007s)
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] Condition Weight (join):
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Join
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Join(time used: 0.000s)
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Scan(after join)
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Scan(after join)(time used: 0.000s)
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(2180 rows)
2021-08-16 14:55:26.405 [M:   0B,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(2180 rows)
2021-08-16 14:55:26.405 [M:   0B,  64B,D:   0B] [DC:     0,     0] TempTable::MultiDimensionalGroupByScan(): aggregate 2180 rows)
2021-08-16 14:55:26.405 [M: 516K,  32B,D:   0B] [DC:     0,     0] END Serial Aggregation. (time used: 0.000s)
2021-08-16 14:55:26.405 [M: 516K,   0B,D:   0B] [DC:     1,     0] Send 1 rows already
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     1,     0] ResultSender: send 0 rows.
2021-08-16 14:55:26.405 [M:   0B,   0B,D:   0B] [DC:     1,     0] output result done.

2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] SUMMARY
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] elapsed time:                00:00:00.009
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] data loaded from storage:       0B,  0s,     0 DC.
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] data decompressed:              0B,  0s.
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] temp space IO stats:
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 14:55:26.406 [M:   0B,   0B,D:   0B] [DC:     1,     0] ======================================================

无索引name列测试

结果集15行,基本在6秒以上。重复执行,因为内存不足,还是要从磁盘读取数据。


gbase> select count(*) from tt1 where name='7ce4c628-3c8b-495f-b44c-ed4fefc97f59';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (Elapsed: 00:00:10.65)

gbase> select count(*) from tt1 where name='7ce4c628-3c8b-495f-b44c-ed4fefc97f59';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (Elapsed: 00:00:06.59)

gbase> select count(*) from tt1 where name='83812e39-5336-4b3f-b376-50dd485de980';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (Elapsed: 00:00:06.89)

gbase> select count(*) from tt1 where name='83812e39-5336-4b3f-b376-50dd485de980';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (Elapsed: 00:00:06.47)

trc日志

2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n2`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`name` = '7ce4c628-3c8b-495f-b44c-ed4fefc97f59')
2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n2),         63567723 rows,   970 DC
2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'      (0)
2021-08-16 15:38:40.299 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 15:38:40.301 [M:   0B,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 15:38:40.301 [M:   0B,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.001s)
2021-08-16 15:38:40.301 [M:   0B,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 15:38:40.301 [M:   0B,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'
2021-08-16 15:38:50.372 [M:2.48G,   0B,D:   0B] [DC:     2,   937] ( 2)cnd(0)   scanned 15925248 rows,  found 1 rows (time used: 10.072s)
2021-08-16 15:38:50.621 [M:2.61G,   0B,D:   0B] [DC:     2,   958] ( 0)cnd(0)   scanned 15925248 rows,  found 1 rows (time used: 10.320s)
2021-08-16 15:38:50.735 [M:2.65G,   0B,D:   0B] [DC:     0,   964] ( 3)cnd(0)   scanned 15857515 rows,  found 0 rows (time used: 10.435s)
2021-08-16 15:38:50.842 [M:2.70G,   0B,D:   0B] [DC:     0,   970] ( 1)cnd(0)   scanned 15859712 rows,  found 2 rows (time used: 10.541s)
2021-08-16 15:38:50.842 [M:2.70G,   0B,D:   0B] [DC:     0,   970] cnd(0) Done. scanned 63567723 rows,  found 4 rows (time used: 10.542s)
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] T0:          total 970 DC,   found 4 DC after scan(with 0 FULL DC).
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] END Scan. (time used: 10.542s)
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] Condition Weight (join):
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] BEGIN Join
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] END Join(time used: 0.000s)
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] BEGIN Scan(after join)
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] END Scan(after join)(time used: 0.000s)
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     0,   970] BEGIN Serial Aggregation(4 rows)
2021-08-16 15:38:50.843 [M:2.70G,  32B,D:   0B] [DC:     0,   970] BEGIN Aggregation(4 rows)
2021-08-16 15:38:50.843 [M:2.70G,  64B,D:   0B] [DC:     0,   970] TempTable::MultiDimensionalGroupByScan(): aggregate 4 rows)
2021-08-16 15:38:50.843 [M:2.70G,  32B,D:   0B] [DC:     0,   970] END Serial Aggregation. (time used: 0.000s)
2021-08-16 15:38:50.843 [M:2.70G,   0B,D:   0B] [DC:     1,   970] Send 1 rows already
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] ResultSender: send 0 rows.
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] output result done.

2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] SUMMARY
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] elapsed time:                00:00:10.578
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] data loaded from storage:    2.02G,  28.915s,        970 DC.
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] data decompressed:           2.72G,  4.281s.
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] temp space IO stats:
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:38:50.877 [M:2.71G,   0B,D:   0B] [DC:     1,   970] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:38:50.878 [M:2.71G,   0B,D:   0B] [DC:     1,   970] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:38:50.878 [M:2.71G,   0B,D:   0B] [DC:     1,   970] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:38:50.878 [M:2.71G,   0B,D:   0B] [DC:     1,   970] ======================================================
2021-08-16 15:38:55.867 [M:2.72G,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n2`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`name` = '7ce4c628-3c8b-495f-b44c-ed4fefc97f59')
2021-08-16 15:38:55.867 [M:2.72G,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 15:38:55.868 [M:2.72G,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 15:38:55.868 [M:2.72G,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n2),         63567723 rows,   970 DC
2021-08-16 15:38:55.868 [M:2.72G,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 15:38:55.868 [M:2.72G,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'      (0)
2021-08-16 15:38:55.868 [M:2.72G,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 15:38:55.869 [M:2.72G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 15:38:55.869 [M:2.72G,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.001s)
2021-08-16 15:38:55.869 [M:2.72G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 15:38:55.869 [M:2.72G,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'
2021-08-16 15:39:01.755 [M:2.03G,   0B,D:   0B] [DC:   255,   646] ( 2)cnd(0)   scanned 15925248 rows,  found 1 rows (time used: 5.885s)
2021-08-16 15:39:02.170 [M:2.33G,   0B,D:   0B] [DC:   254,   696] ( 1)cnd(0)   scanned 15859712 rows,  found 2 rows (time used: 6.301s)
2021-08-16 15:39:02.314 [M:2.42G,   0B,D:   0B] [DC:   254,   710] ( 0)cnd(0)   scanned 15925248 rows,  found 1 rows (time used: 6.445s)
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] ( 3)cnd(0)   scanned 15857515 rows,  found 0 rows (time used: 6.514s)
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] cnd(0) Done. scanned 63567723 rows,  found 4 rows (time used: 6.537s)
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] T0:          total 970 DC,   found 4 DC after scan(with 0 FULL DC).
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] END Scan. (time used: 6.537s)
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] Condition Weight (join):
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] BEGIN Join
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] END Join(time used: 0.000s)
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] BEGIN Scan(after join)
2021-08-16 15:39:02.406 [M:2.47G,   0B,D:   0B] [DC:   254,   716] END Scan(after join)(time used: 0.000s)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   254,   716] BEGIN Serial Aggregation(4 rows)
2021-08-16 15:39:02.407 [M:2.47G,  32B,D:   0B] [DC:   254,   716] BEGIN Aggregation(4 rows)
2021-08-16 15:39:02.407 [M:2.47G,  64B,D:   0B] [DC:   254,   716] TempTable::MultiDimensionalGroupByScan(): aggregate 4 rows)
2021-08-16 15:39:02.407 [M:2.47G,  32B,D:   0B] [DC:   254,   716] END Serial Aggregation. (time used: 0.000s)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] Send 1 rows already
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] ResultSender: send 0 rows.
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] output result done.

2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] SUMMARY
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] elapsed time:                00:00:06.539
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] data loaded from storage:    1.49G,  3.12s,  716 DC.
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] data decompressed:           2.01G,  6.736s.
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] temp space IO stats:
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] ======================================================

2021-08-16 15:39:02.407 [M:2.47G,   0B,D:   0B] [DC:   255,   716] ======================================================
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n2`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`name` = '83812e39-5336-4b3f-b376-50dd485de980')
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n2),         63567723 rows,   970 DC
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.name BET. '83812e39-5336-4b3f-b376-50dd485de980' AND '83812e39-5336-4b3f-b376-50dd485de980'      (0)
2021-08-16 15:39:45.569 [M:2.48G,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 15:39:45.571 [M:2.48G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 15:39:45.571 [M:2.48G,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.001s)
2021-08-16 15:39:45.571 [M:2.48G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 15:39:45.571 [M:2.48G,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.name BET. '83812e39-5336-4b3f-b376-50dd485de980' AND '83812e39-5336-4b3f-b376-50dd485de980'
^[[B2021-08-16 15:39:51.726 [M:2.00G,   0B,D:   0B] [DC:   254,   690] ( 3)cnd(0)       scanned 15857515 rows,  found 1 rows (time used: 6.146s)
2021-08-16 15:39:51.887 [M:2.12G,   0B,D:   0B] [DC:   253,   710] ( 1)cnd(0)   scanned 15859712 rows,  found 2 rows (time used: 6.317s)
2021-08-16 15:39:51.896 [M:2.12G,   0B,D:   0B] [DC:   252,   712] ( 2)cnd(0)   scanned 15925248 rows,  found 2 rows (time used: 6.315s)
2021-08-16 15:39:51.989 [M:2.18G,   0B,D:   0B] [DC:   252,   718] ( 0)cnd(0)   scanned 15925248 rows,  found 1 rows (time used: 6.397s)
2021-08-16 15:39:51.991 [M:2.18G,   0B,D:   0B] [DC:   252,   718] cnd(0) Done. scanned 63567723 rows,  found 6 rows (time used: 6.420s)
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] T0:          total 970 DC,   found 6 DC after scan(with 0 FULL DC).
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] END Scan. (time used: 6.423s)
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] Condition Weight (join):
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] BEGIN Join
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] END Join(time used: 0.000s)
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] BEGIN Scan(after join)
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] END Scan(after join)(time used: 0.000s)
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   252,   718] BEGIN Serial Aggregation(6 rows)
2021-08-16 15:39:51.994 [M:2.18G,  32B,D:   0B] [DC:   252,   718] BEGIN Aggregation(6 rows)
2021-08-16 15:39:51.994 [M:2.18G,  64B,D:   0B] [DC:   252,   718] TempTable::MultiDimensionalGroupByScan(): aggregate 6 rows)
2021-08-16 15:39:51.994 [M:2.18G,  32B,D:   0B] [DC:   252,   718] END Serial Aggregation. (time used: 0.000s)
2021-08-16 15:39:51.994 [M:2.18G,   0B,D:   0B] [DC:   253,   718] Send 1 rows already
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] ResultSender: send 0 rows.
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] output result done.

2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] SUMMARY
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] elapsed time:                00:00:06.425
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] data loaded from storage:    1.50G,  2.549s,         718 DC.
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] data decompressed:           2.02G,  6.868s.
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] temp space IO stats:
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:39:51.995 [M:2.18G,   0B,D:   0B] [DC:   253,   718] ======================================================
2021-08-16 15:39:56.093 [M:2.33G,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n2`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`name` = '83812e39-5336-4b3f-b376-50dd485de980')
2021-08-16 15:39:56.093 [M:2.33G,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 15:39:56.093 [M:2.33G,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 15:39:56.093 [M:2.33G,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n2),         63567723 rows,   970 DC
2021-08-16 15:39:56.093 [M:2.33G,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 15:39:56.093 [M:2.33G,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.name BET. '83812e39-5336-4b3f-b376-50dd485de980' AND '83812e39-5336-4b3f-b376-50dd485de980'      (0)
2021-08-16 15:39:56.094 [M:2.33G,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 15:39:56.095 [M:2.33G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 15:39:56.095 [M:2.33G,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.001s)
2021-08-16 15:39:56.095 [M:2.33G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 15:39:56.095 [M:2.33G,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.name BET. '83812e39-5336-4b3f-b376-50dd485de980' AND '83812e39-5336-4b3f-b376-50dd485de980'
2021-08-16 15:40:02.225 [M:2.11G,   0B,D:   0B] [DC:   252,   701] ( 2)cnd(0)   scanned 15925248 rows,  found 2 rows (time used: 6.107s)
2021-08-16 15:40:02.351 [M:2.19G,   0B,D:   0B] [DC:   253,   712] ( 0)cnd(0)   scanned 15925248 rows,  found 1 rows (time used: 6.256s)
2021-08-16 15:40:02.404 [M:2.23G,   0B,D:   0B] [DC:   252,   718] ( 1)cnd(0)   scanned 15859712 rows,  found 2 rows (time used: 6.309s)
2021-08-16 15:40:02.410 [M:2.22G,   0B,D:   0B] [DC:   252,   718] ( 3)cnd(0)   scanned 15857515 rows,  found 1 rows (time used: 6.315s)
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] cnd(0) Done. scanned 63567723 rows,  found 6 rows (time used: 6.316s)
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] T0:          total 970 DC,   found 6 DC after scan(with 0 FULL DC).
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] END Scan. (time used: 6.316s)
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] Condition Weight (join):
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] BEGIN Join
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] END Join(time used: 0.000s)
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] BEGIN Scan(after join)
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] END Scan(after join)(time used: 0.000s)
2021-08-16 15:40:02.411 [M:2.22G,   0B,D:   0B] [DC:   252,   718] BEGIN Serial Aggregation(6 rows)
2021-08-16 15:40:02.411 [M:2.22G,  32B,D:   0B] [DC:   252,   718] BEGIN Aggregation(6 rows)
2021-08-16 15:40:02.411 [M:2.22G,  64B,D:   0B] [DC:   252,   718] TempTable::MultiDimensionalGroupByScan(): aggregate 6 rows)
2021-08-16 15:40:02.411 [M:2.22G,  32B,D:   0B] [DC:   252,   718] END Serial Aggregation. (time used: 0.000s)
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] Send 1 rows already
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] ResultSender: send 0 rows.
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] output result done.

2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] SUMMARY
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] elapsed time:                00:00:06.318
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] data loaded from storage:    1.50G,  2.679s,         718 DC.
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] data decompressed:           2.02G,  6.304s.
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] temp space IO stats:
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:40:02.412 [M:2.22G,   0B,D:   0B] [DC:   253,   718] ======================================================

有索引name列测试

有索引测试,基本在0.05秒,比无索引快了100倍。

gbase> select count(*) from tt1 where name='7ce4c628-3c8b-495f-b44c-ed4fefc97f59';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (Elapsed: 00:00:00.05)

gbase> select count(*) from tt1 where name='83812e39-5336-4b3f-b376-50dd485de980';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (Elapsed: 00:00:00.04)

trace日志

2021-08-16 15:36:26.571 [M:2.65G,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n2`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`name` = '7ce4c628-3c8b-495f-b44c-ed4fefc97f59')
2021-08-16 15:36:26.571 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 15:36:26.571 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n2),         63567723 rows,   970 DC
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'      (0)
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Global hash index is used:
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] cnd(0)       scanned 4 rows,         found 4 rows (time used: 0.000s)
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 4 DC after scan(with 0 FULL DC).
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Scan. (time used: 0.031s)
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Condition Weight (join):
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Join
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Join(time used: 0.000s)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan(after join)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Scan(after join)(time used: 0.000s)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(4 rows)
2021-08-16 15:36:26.603 [M:2.65G,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(4 rows)
2021-08-16 15:36:26.603 [M:2.65G,  64B,D:   0B] [DC:     0,     0] TempTable::MultiDimensionalGroupByScan(): aggregate 4 rows)
2021-08-16 15:36:26.603 [M:2.65G,  32B,D:   0B] [DC:     0,     0] END Serial Aggregation. (time used: 0.000s)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] Send 1 rows already
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] ResultSender: send 0 rows.
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] output result done.

2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] SUMMARY
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] elapsed time:                00:00:00.031
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] data loaded from storage:       0B,  0s,     0 DC.
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] data decompressed:              0B,  0s.
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] temp space IO stats:
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] ======================================================

2021-08-16 15:36:26.571 [M:2.65G,   0B,D:   0B] [DC:     0,     0] SQL Statement:
SELECT  COUNT(1) FROM `testdb`.`tt1_n2`  `vcname000001.testdb.tt1` WHERE (`vcname000001.testdb.tt1`.`name` = '7ce4c628-3c8b-495f-b44c-ed4fefc97f59')
2021-08-16 15:36:26.571 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Start Query Execution
2021-08-16 15:36:26.571 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Tables:
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] T0:          vcname000001.testdb.tt1(testdb.tt1_n2),         63567723 rows,   970 DC
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Condition Weight (non-join):
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] cnd(0):      vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'      (0)
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Smart Scan
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 970 DC to scan(with 0 FULL DC).
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Smart Scan. (time used: 0.000s)
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan
2021-08-16 15:36:26.572 [M:2.65G,   0B,D:   0B] [DC:     0,     0] cnd(0) vcname000001.testdb.tt1.name BET. '7ce4c628-3c8b-495f-b44c-ed4fefc97f59' AND '7ce4c628-3c8b-495f-b44c-ed4fefc97f59'
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Global hash index is used:
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] cnd(0)       scanned 4 rows,         found 4 rows (time used: 0.000s)
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] T0:          total 970 DC,   found 4 DC after scan(with 0 FULL DC).
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Scan. (time used: 0.031s)
2021-08-16 15:36:26.602 [M:2.65G,   0B,D:   0B] [DC:     0,     0] Condition Weight (join):
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Join
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Join(time used: 0.000s)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Scan(after join)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] END Scan(after join)(time used: 0.000s)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     0,     0] BEGIN Serial Aggregation(4 rows)
2021-08-16 15:36:26.603 [M:2.65G,  32B,D:   0B] [DC:     0,     0] BEGIN Aggregation(4 rows)
2021-08-16 15:36:26.603 [M:2.65G,  64B,D:   0B] [DC:     0,     0] TempTable::MultiDimensionalGroupByScan(): aggregate 4 rows)
2021-08-16 15:36:26.603 [M:2.65G,  32B,D:   0B] [DC:     0,     0] END Serial Aggregation. (time used: 0.000s)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] Send 1 rows already
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] ResultSender: send 0 rows.
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] output result done.

2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] SUMMARY
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] elapsed time:                00:00:00.031
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] data loaded from storage:       0B,  0s,     0 DC.
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] data decompressed:              0B,  0s.
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] temp space IO stats:
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] CB   write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] SRT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] GDC  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] MAT  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] HSJ  write(   0B, 0time, 0sec),       read(   0B, 0time, 0sec)
2021-08-16 15:36:26.603 [M:2.65G,   0B,D:   0B] [DC:     1,     0] ======================================================

总结

global hash索引,只适合重复值少的列,比如电话号码,身份证,imsi, msisdn等,不适合重复度高,比如民族,城市等。另外,只针对精确等值查询,对范围查询无效。

发表评论

您的电子邮箱地址不会被公开。