8.0新特性-並行查詢innodb_parallel_read_threads
原文很好翻譯,直接讀好理解
長久以來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類似
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29990276/viewspace-2685554/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 8.0新特性-並行查詢innodb_parallel_read_threadsMySql並行Parallelthread
- MySQL 8.0 新特性MySql
- DM並行查詢並行
- PostgreSQL並行查詢概述SQL並行
- Oracle並行操作——並行查詢(Parallel Query)Oracle並行Parallel
- 8.0新特性-不可見索引索引
- MySQL 8.0新特性概覽MySql
- 表和索引並行查詢索引並行
- MySQL8.0-新特性彙總MySql
- MySQL8.0-新特性-DescendingIndexMySqlIndex
- MySQL 8.0 新特性梳理彙總MySql
- MySQL·8.0新特性·InvisibleIndexMySqlIndex
- MySQL 8.0新特性更新介紹MySql
- 10g新特性——閃回版本查詢
- oracle表查詢的並行度Oracle並行
- 並行查詢並行度Degree與instances 設定並行
- MySQL8.0 新特性 top10MySql
- Mysql8.0部分新特性MySql
- mysql8.0新特性--隱藏索引MySql索引
- oracle並行查詢一例薦Oracle並行
- 什麼是並行查詢及其原理並行
- 【Oracle】如何查詢並行是否起作用?Oracle並行
- 用並行查詢讓SQL Server加速執行並行SQLServer
- MySQL 8.0新特性-倒敘索引 desc indexMySql索引Index
- MySQL8.0新特性-CTE語法支援MySql
- MySQL·8.0新特性·Newdatadictionary嚐鮮篇MySql
- MySQL8.0 新特性:Partial Update of LOB ColumnMySql
- oracle 並行查詢時並行資源分配追蹤測試Oracle並行
- Oracle Database 10g新特性-閃回版本查詢OracleDatabase
- 總結CSS3新特性(媒體查詢篇)CSSS3
- PostgreSQL並行查詢相關配置引數SQL並行
- union的兩個子查詢是否並行並行
- RAC中的並行查詢 DOP(Degree of Parallelism)並行Parallel
- TDSQL-C 並行查詢技術探索SQL並行
- 並行查詢緩慢的問題分析並行
- RAC環境對並行查詢的支援並行
- MySQL8.0新特性-臨時表的改善MySql
- MySQL 8.0 18個管理相關的新特性MySql