|
|
本帖最后由 jiawang 于 2026-1-4 22:13 编辑
mysql 并行查询_MySQL 8.0新特性-并行查询innodb_parallel_read_threads
本文介绍了MySQL 8.0从8.0.14版本开始引入的并行查询特性，特别是针对无WHERE条件的count(*)操作。通过实验展示了设置不同并行读取线程数对查询性能的影响，揭示了并行查询对性能提升的效果以及潜在的优化限制。
文很好翻译，直接读好理解
下，MySQL终于在8.0.14版本开始有了自己的并行查询，但使用面非常的窄，只适用于并行聚集索引的count(*) 并且只是在没有where条件的情况下的查询
mysql> set local inno
长久以来MySQL没有并行查询，并且在其他数据库已经有了的情况db_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 default
Query 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)
下面是我的测试结果
mysql>set local innodb_parallel_read_threads = 1;
执行成功，耗时：8 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：2275 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：2316 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：2191 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：2196 ms.
mysql>set local innodb_parallel_read_threads = 16;
执行成功，耗时：8 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：594 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：557 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：570 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：594 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：582 ms.
mysql>set local innodb_parallel_read_threads=32;
执行成功，耗时：9 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：265 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：251 ms.
mysql>set local innodb_parallel_read_threads=64;
执行成功，耗时：9 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：340 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：363 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：306 ms.
mysql>set local innodb_parallel_read_threads=32;
执行成功，耗时：9 ms.
mysql>select count(*) from PARALLELTEST;
+--------------------+
| count(*) |
+--------------------+
| 9175040 |
+--------------------+
返回行数：[1]，耗时：276 ms.
|
|
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2026-1-4 18:13:20
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对