Infobright的MySQL数据仓库方案测试

〇〇

group by 查询，比oracle还是慢一些
mysql> select substr(ID,1,4) ID1, r05,count(*)cnt,r03
    -> from ren41
    -> group by substr(ID,1,4),r05,r03 ;
+------+------+---------+------+
| ID1  | r05  | cnt     | r03  |
+------+------+---------+------+
| 4101 | 01   | 3387921 | 1    |
....

| 4114 | 27   |       1 | 2    |
| 4114 | 37   |       1 | 2    |
+------+------+---------+------+
1313 rows in set (2 min 29.40 sec)

mysql>

oracle
SQL> conn rk/rk
已连接。
SQL> select /*+ parallel(ren41,16)*/ substr(ID,1,4) ID, r05,count(*)cnt,r03
  2  from ren41
  3  group by substr(ID,1,4),r05,r03 ;

ID       R0        CNT R
-------- -- ---------- -
4105     1          27 2
4107     28         25 2
....
4106     16          6 2
4114     38          3 2
4103     28          1 2

已选择1313行。

已用时间:  00: 00: 52.10

〇〇

oracle压缩
create table hu41c parallel compress as select * from hu41 where 1=0;
insert /*+ parallel (hu41c,16)*/ into hu41c select /*+parallel (hu41,16)*/ * from hu41;
commit;
select /*+ parallel(ren41c,16)*/ substr(ID,1,4) ID, r05,count(*)cnt,r03
from ren41c
group by substr(ID,1,4),r05,r03 ;

ID       R0        CNT R
-------- -- ---------- -
4106     04         19 1
4104     10         88 1
....
4117     16        106 2
4103     30         19 2
4106     1          15 2

已选择1313行。

已用时间:  00: 00: 16.58

〇〇

group by改为2列

select substr(ID,1,4) ID1, r05,count(*)cnt
from ren41
group by substr(ID,1,4),r05;

| 4114 | 27   |       1 |
| 4114 | 37   |       1 |
+------+------+---------+
771 rows in set (1 min 23.23 sec)

1列
select substr(ID,1,4) ID1, count(*)cnt
from ren41
group by substr(ID,1,4);

| 4116 | 9741283 |
| 4114 | 7753844 |
+------+---------+
17 rows in set (1 min 10.29 sec)

〇〇

不用substr
select r03, r05,count(*)cnt
from ren41
group by r03,r05;

| 1    | 6    |        3 |
| 1    | 8    |        1 |
| 1    | 39   |        3 |
+------+------+----------+
131 rows in set (30.23 sec)

这个还是比oracle慢

select /*+ parallel(ren41c,16)*/ r03, r05,count(*)cnt
from ren41c
group by r03,r05;

2 40         21
1 54          4

已选择131行。

已用时间:  00: 00: 13.26

〇〇

书上说
Efficient Data Types
The following data types are identified as being more efficient within Infobright:

Columns stored as integers are much more efficient (easier to sort, better described by Knowledge Nodes, etc.) than other data types. So if possible, store data as any of the following:

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
DECIMAL — note: storing as (10,0) will be much faster than (10,5)
DATE, TIME

Special case data types:

CHAR, VARCHAR — these types are covered in the Knowledge Grid, but where possible should be replaced numeric values, as they are better optimized and faster to decompress

Less optimized data types:

BINARY, VARBINARY
FLOAT
DOUBLE
TINYTEXT, TEXT

DECIMAL is a different, relatively less efficient PHYSICAL representation of INTEGER. DECIMAL is much more efficient than FLOAT or DOUBLE and therefore, should be used in place of FLOAT or DOUBLE if possible.

〇〇

把varchar(1)(2)->tinyint
varchar(3)(4)->smallint
varchar(18)->bigint

hu41文件夹 106 MB (111,721,454 字节)

〇〇

mysql> load data infile ‘f:/soft/hu41.csv’ into table hu41 fields terminated by ‘,’ ;
Query OK, 24590997 rows affected (6 min 46.38 sec)
Records: 24590997 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile ‘f:/soft/ren41.csv’ into table ren41 fields terminated by ‘,’ ;
Query OK, 91236854 rows affected (22 min 26.47 sec)
Records: 91236854 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select r03, r05,count(*)cnt
  -> from ren41
  -> group by r03,r05;
+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+
|  1 |  1 | 46468976 |
|  1 |  3 |  478488 |
...
+———+———+—————+
114 rows in set (30.26 sec)

〇〇

ren41.bht 357 MB (374,849,237 字节)

〇〇

求count(distinct)相当快

D:\Infobright>bin\mysql -uroot -P 5028
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log build number (revision)=IB_3.4.2_r8940_9191(ice)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from t;
ERROR 1046 (3D000): No database selected
mysql> use test
Database changed
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.13 sec)

mysql> select count(distinct c1) from t;
+--------------------+
| count(distinct c1) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.02 sec)

mysql> select count(distinct c4) from t;
+--------------------+
| count(distinct c4) |
+--------------------+
|                113 |
+--------------------+
1 row in set (0.02 sec)