Application Scenarios

MySQL parallel query tuning is mainly used in online analytical processing (OLAP) scenarios. In an OLAP scenario, a large amount of data is analyzed and queried from multiple dimensions. Generally, scalability, data consistency, high performance, and high security are required for the scenario. In the scenario, the quick query response and high throughput of the database are critical to ensuring the normal running of applications. Kunpeng BoostKit provides in-depth optimization for databases in terms of OLAP capabilities, and open-sources the optimization feature as a patch package to the Gitee community. Developers need to apply the patch to the MySQL source code, and then compile and install MySQL. For details, see Patch Usage Description.

The MySQL parallel query tuning feature enables parallel data read, and allows executing an SQL statement by using multiple cores and threads, and therefore accelerates the execution of query statements. This feature is mainly used in service scenarios such as data analysis, BI report, and decision-making support. The following types of single-table scan queries can be performed in parallel:

• JT_ALL
• JT_INDEX_SCAN
• JT_REF
• JT_RANGE

In addition to single tables, simple parallel query of multiple tables is allowed. Subquery is not supported. Semi join is supported in some scenarios. The solution works in trustlist mode. The details are as follows:

• Single-table trustlist:

  Select {column name| Aggregate } from table where {=|>| < |>= |<= |like |between…and| in} group by {column name} having {column name}order by {column name| Aggregate } limit x

  Aggregate: SUM MIN MAX AVG COUNT

• Multi-table trustlist:

  Select {column name| Aggregate } from table1 table2 …  where {=|>| < |>= |<= |like |between…and| in} group by {column name} having {column name}order by {column name} limit x

  Parallel query does not take effect for a system table, temporary table, non-InnoDB table, stored procedure, or serialized isolation level.

• Semi-join query:

  Some semi-joins may become common simple queries after being processed by the MySQL optimizer. If the innermost table in the execution plan is a foreign table, such SQL statements also support parallel query.

• Four arithmetic operations of aggregation:

  Four arithmetic operations of aggregation are supported, for example, sum()/sum() and a*sum(), where a is a constant.