Mysql 8.0新特性中并行查询innodb及并行读取线程是怎样的,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。长久以来my
Mysql 8.0新特性中并行查询innodb及并行读取线程是怎样的,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
长久以来mysql没有并行查询,并且在其他数据库已经有了的情况下,Mysql终于在8.0.14版本开始有了自己的并行查询,但使用面非常的窄,只适用于并行聚集索引的count(*) 并且只是在没有where条件的情况下的查询
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 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来说,这是一个很好的开端,并为真正的并行查询执行开辟了一条道路。
下面是我的测试结果
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.
和文章中的结论一致,但是我参数设置到64的360ms 时反而比32时200多ms慢,也是符合预期的,与oracle类似
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注编程网数据库频道,感谢您对编程网的支持。
--结束END--
本文标题: MySQL 8.0新特性中并行查询innodb及并行读取线程是怎样的
本文链接: https://www.lsjlt.com/news/61461.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0