Shulou(Shulou.com)06/01 Report--

Infobright data warehouse can compress and store a large amount of data in high-intensity compression. Usually, the process of continuous query is the process of data decompression. Of course, the specific details have been mentioned before, and we will not do the process introduction here (https://blog.51cto.com/jim123/1975029). All the original data types of MySQL are supported in infobright, in which the efficiency of × × is higher than that of other types, which is similar to MySQL. The more efficient types in infobright are as follows:

1 、 TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

2. DECIMAL (minimize the accuracy after the decimal point)

3 、 DATE, TIME

These three types of data can have a relatively high compression ratio and query speed, while the less efficient data types are not recommended:

1. BINARY VARBINARY (binary type)

2 、 FLOAT

3 、 DOUBLE

4 、 VARCHAR

5. TINYTEXT TEXT (non-Unicode type with variable length)

These data types are inefficient in use and the compression ratio is not very high. VARCHAR field is not as efficient as CHAR field in MySQL. Of course, in some business scenarios, CHAR (VARCHAR) may have to be used and frequent queries are required, but the number of records of data is not very long (no more than 10000 rows, and more than 10 data types, similar to provinces, UUID and other data). You can use comment lookup to create the DDL when creating the table to improve the efficiency of the usual query as follows:

# original table DDLCREATE TABLE `test_ default` (`dstphone` varchar (11) DEFAULT NULL, `gateid` varchar (255) DEFAULT NULL) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;#comment lookup create table DDLCREATE TABLE `test_ lookup` (`dstphone` varchar (11) DEFAULT NULL COMMENT 'lookup', `gateid` varchar (255) DEFAULT NULL COMMENT' lookup') ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8

It should be noted that at present, comment lookup can only be used in CHAR (VARCHAR). Secondly, the cost of bringing higher query efficiency is disk overhead, because infobright is doing the decompression process when querying, so the way to use comment lookup is to reduce the compression ratio and decompress data more quickly when querying. You can see that the compression ratio of comment lookup is different from that of the default table creation.

The query efficiency is as follows:

You can look at the disk space occupied under the same data, but the corresponding query record cannot exceed 10000 rows, otherwise it will reduce its efficiency:

Therefore, in the process of use, we still need to choose according to the actual situation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.