Shulou(Shulou.com)05/31 Report--

MySQL 8.0 new features of parallel query innodb and parallel read thread is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

For a long time, MySQL does not have parallel queries, and when other databases already exist, MySQL finally began to have its own parallel queries in version 8.0.14, but the usage area is very narrow, which is only applicable to count (*) with parallel clustered indexes and only queries without where conditions.

Mysql > set local innodb_parallel_read_threads=1;Query OK, 0 rows affected (0.00 sec) mysql > select count (*) from ontime;+-+ | count (*) | +-+ | 177920306 | +-+ 1 row in set (2 min 33.93 sec) mysql > set local innodb_parallel_read_threads=DEFAULT;-- 4 is defaultQuery OK, 0 rows affected (0.00 sec) mysql > select count (*) from ontime +-+ | count (*) | +-+ | 177920306 | +-+ 1 row in set (21.85 sec) mysql > set local innodb_parallel_read_threads=32;Query OK, 0 rows affected (0.00 sec) mysql > select count (*) from ontime +-+ | count (*) | +-+ | 177920306 | +-+ 1 row in set (5.35 sec)

Everything is not perfect at the beginning, but persists day after day, which is a good start for MySQL and opens the way for true parallel query execution.

Here are my test results.

Mysql > set local innodb_parallel_read_threads = 1; successful execution, time consuming: 8 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 2275 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 2316 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 2191 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 2196 ms.mysql > set local innodb_parallel_read_threads = 16; time spent: 8 ms.mysql > select count (*) from PARALLELTEST for successful execution +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 594 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 557 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 570 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 594 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+. Time: 582 ms.mysql > set local innodb_parallel_read_threads=32; executed successfully, time: 9 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 265 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 251 ms.mysql > set local innodb_parallel_read_threads=64; executed successfully, time: 9 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 340 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 363 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 306 ms.mysql > set local innodb_parallel_read_threads=32; executed successfully, time: 9 ms.mysql > select count (*) from PARALLELTEST +-+ | count (*) | +-+ | 9175040 | number of rows returned by +-+: [1], time: 276 ms.

It is consistent with the conclusion in the article, but when my parameter is set to 64 360ms, it is slower than 32: 200 ms, which is also in line with expectations, similar to Oracle

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.