How MySQL uses multithreading to improve query performance

MySQL uses multithreading to improve query performance. The crux of the problem is that it can only be executed in a single thread. This solution provides a Multi-thread-query engine for MySQL to use

MySQL uses multithreading to improve query performance. The crux of the problem is that it can only be executed in a single thread. This solution provides a Multi-thread-query engine for MySQL to use

Background

Statistical queries are time-consuming queries. The usage scenario is that QPS is not large. Each Query Needs to access a large amount of data and process a large amount of data for a long time (usually more than seconds ).

Analysis

A large query can be divided into multiple subqueries. If the data to be accessed by the subquery is 1/n of the entire query, the execution time is estimated to be 1/n. However, since a MySQL query can only be executed in a single thread, even if a large query is split into multiple subqueries, multiple subqueries are merged after execution, the time required is n * 1/n = 1, and the performance cannot be improved.

The crux of the problem is that it can only be executed in a single thread. This solution provides a Multi-thread-query engine that allows MySQL to execute multiple subqueries simultaneously with multiple threads. Because a merge operation is required, the query time is slightly greater than the longest query time in n subqueries, that is, the performance is nearly n times.

The last part is an example, n = 3.

Solution description

1. Illustration

2. the user initiates a query on MTQ. MTQ is split into multiple subqueries and multi-threaded queries are initiated to the object table.

3. After MTQ is executed by all threads, other function operations, such as count, avg, and group by, can be performed on this basis.

4. You need to modify the SQL Query format and specify how to split the subquery.

5. This method is applicable when each subquery has a large computing volume and few returned results. (If more results are returned, the merge takes a longer time)

Example

Take a statistical query requirement as an example (the data is constructed, and there is a trojan leak problem)

A normal query is as follows:

Query Result

Use MTQ query syntax on the same machine:

MTQ query results

As you can see, the time is reduced from 3 s to 1.2 s, and the query results are the same. You can directly perform the sum and group by operations on the query statement.