Infobright Database Query Optimization

We have analyzed the Infobright architecture and briefly introduced the compression process and working principle of Infobright. Now we will discuss the issue of query optimization.

1) configure the environment

In Linux, you can configure the brighthouse. ini file according to the requirements in README.

2) Select an efficient data type

See data types related to Infobright in detail.

3) use comment lookup

Comment lookup can only be explicitly used on char or varchar. Comment Lookup can reduce the storage space and increase the compression ratio. Using comment lookup for char and varchar fields can improve the query efficiency.

The implementation mechanism of Comment Lookup is similar to Bitmap indexes. In this way, the char field is replaced by a short numeric value to achieve better query performance and compression ratio. The use of CommentLookup not only requires data types, but also requires data. Generally, the total number of data classes is less than 10000 and the number of units/category in the current column is greater than 10. Comment Lookup is suitable for age, gender, and province fields.

The usage of comment lookup is simple. You can define the following when creating a database table:

Act char (15) comment 'lookup ',

Part char (4) comment 'lookup ',

4) import data as orderly as possible

We have analyzed the architecture of Infobright before. Each column is divided into n DSPs, each of which stores some statistics of DP. The orderly import of data can significantly differentiate the data in different dpcs. For example, if data is imported in chronological order, the maxdate of the previous DP is <= min (date) of the next DP, and the suspicious DP can be reduced during query to improve query performance. In other words, the orderly import of data is to make the internal data of DP more centralized, instead of so scattered.

5) use efficient query statements.

The content here is much more, which is summarized as follows:

Use in or union instead.

Reduce I/O operations because the data in infobright is compressed and the decompression process consumes a lot of time.

When querying, try to select statements with more obvious differentiation conditions.

Use the fields in where as much as possible in the Select statement. The reason is that Infobright is processed by column, and each column is processed separately. Therefore, avoid using fields not in the where clause to obtain better performance.

Limit the number of tables in the result, that is, limit the number of tables in the select statement.

Use independent subqueries and join Operations instead of non-independent subqueries.

Try not to use MySQL functions and type conversion characters in the where clause

Avoid using the MySQL optimizer for query operations whenever possible

Query operations across Infobright tables and MySQL tables

Try not to use mathematical operations in group by or subqueries, such as suma * B ).

Remove unnecessary fields from the select statement.

When Infobright executes a query statement, most of the time is spent in the optimization phase. Although the Infobright optimizer is already very powerful, it still requires the attention of programmers when writing query statements.