How to trigger Mysql parallel query function to start?

MySQL 8.0.19

mysql> show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_parallel_read_threads | 4     |
+------------------------------+-------+
1 row in set (0.00 sec)

Judging from the execution plan, although the cost value is high, it does not trigger parallel queries.

mysql> explain analyze SELECT count(a.name) FROM b,a WHERE b.id = a.id AND a.id < 10000\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(a.`name`)  (actual time=79199.970..79199.971 rows=1 loops=1)
    -> Inner hash join (b.id = a.id)  (cost=29804029261222.55 rows=29803521726988) (actual time=52129.791..79198.823 rows=9999 loops=1)
        -> Table scan on b  (cost=31.81 rows=18758239) (actual time=0.385..19630.712 rows=20000000 loops=1)
        -> Hash
            -> Filter: (a.id < 10000)  (cost=4909601.51 rows=15888229) (actual time=2.266..52117.583 rows=9999 loops=1)
                -> Table scan on a  (cost=4909601.51 rows=47669458) (actual time=2.262..48985.413 rows=50000000 loops=1)

1 row in set (1 min 19.25 sec)

Solution

The current use cases for parallel reads are very limited. While the documentation

innodb_parallel_read_threads

Defines the number of threads that can be used for parallel clustered index reads. Parallel scanning of partitions is supported as of MySQL 8.0.17. Parallel read threads can improve CHECK TABLE performance.

is a bit vague, the feature documentation describes the current use cases in more detail:

FR1: SELECT COUNT(*) FROM TABLE T; will scan the index in parallel only if the scan is a non-locking scan and --innodb-parallel-read-threads > 1. Otherwise it will fallback to the old row by row scan.

FR2: The second phase of CHECK TABLE T; will also do a parallel scan.

FR3: Support MVCC semantics.

FR4: New session level variable --innodb-parallel-read-threads to control the number of threads to use for parallel SELECT COUNT(*) ...;

Minimum value 1

Default value 4

Maximum value 256.

NFR1: Speed up should be 10x for SELECT COUNT(*) FROM t; on relevant hardware (e.g., tetra02).

In short: you should see an improvement only for select count(*) from table (without any where-clause). And it will also not be mentioned in the execution plan, you can currently only see the effect by comparing execution times with different values of innodb_parallel_read_threads.

This is a new feature, and in its current state basically laying the groundwork for future developments:

Current scope is limited to providing sufficient infrastructure for DDL operations to read the data in parallel. Making the second phase of CHECK TABLE parallel is an added bonus for now. This speeds up CHECK TABLE a little.

The developers will test and gradually extend this feature (e.g. MySQL 8.0.17 added support for partitioned tables, and support for doing this on secondary indexes seems to be in progress), but it might take a while until your query will be able to use it.