关于mysql:mysql8与mysql5的单连接性能比较

一、概述

先说论断，如果你的MySQL数据库运行在一个高并发的环境下，那么MySQL8劣势很大，降级到MySQL8是一个很好的抉择；但如果你的MySQL运行环境是低并发的，那么MySQL8劣势并不显著，集体倡议不要当初就降级到MySQL8，能够等一等。

本文针对的是低并发环境下的MySQL8与MySQL5的性能比拟。

1.1 背景

依据网上一些应用sysbench做的MySQL8的性能基准测试的后果来看，MySQL8绝对MySQL5的性能劣势更多体现在高并发环境(如连接数达到1024甚至2048)下，单位工夫解决数量(例如InnoDB解决行数或处理事务数量)的极大进步。即，高并发下的TPS指标，MySQL8绝对MySQL5有很大的劣势。

能够参考这篇文章 : MySQL Performance Benchmarking: MySQL 5.7 vs MySQL 8.0

但理论的生产环境上，也有很多零碎并未运行在高并发环境下，它们的数据库连接数往往不会超过默认的最大连接数151，它们甚至不须要独立的MySQL服务器。对于这种场景，生产环境上是否有必要将MySQL降级到8呢？

本文针对MySQL5.7.28与MySQL8.0.22的docker镜像版本，在各自都没有做性能优化配置的根底上，在雷同的宿主机环境下，在雷同的表构造与雷同的数据量下，对它们进行了一些完全相同的，单个连贯上的性能测试，并对其进行数据统计与剖析。

即，本文思考的不是高并发环境下的性能体现，而是低并发环境下，单个连贯上的性能体现。此时次要关注各种SQL操作的耗时和资源耗费。

1.2 单连贯的性能比拟论断

对单个连贯的性能测试后果进行统计分析之后，得出以下论断：

• 因为MySQL8对hash join的反对，对于连贯字段上没有任何索引的多表连贯查问，MySQL8具备压倒性的性能劣势。
• 能够应用倒序索引的话，MySQL8具备肯定性能劣势。
• 在其余场景的性能体现上，如单表读写，多表索引连贯查问等等，MySQL8根本与MySQL5没有太大区别，甚至略有不如。
• MySQL8对资源的耗费，如CPU和内存，要比MySQL5多一些。

1.3 低并发环境下是否降级到MySQL8的倡议

对于低并发环境来说，MySQL8对性能的最大晋升来自于哈希连贯的反对。但实际上因为索引的存在，理论能用到哈希连贯的场景并不是那么多。尤其是曾经稳固运行了一段时间的生产环境上，如果连贯字段上齐全没有索引且数据量较大的话，性能问题应该早就裸露进去了；而且MySQL8的版本还在不停迭代降级中，一些性能的兼容性还不是很稳固(有些性能在8.0.x较早的版本里反对，后续更高一点版本又不反对了)。

因而对于低并发的生产环境，集体倡议：

1. 如果没有足够的MySQL运维能力，那么不倡议为了性能晋升而降级MySQL到8.0.x的版本，除非确定生产上有很多无索引的字段作为连贯条件(实际上不可能)。但如果要从其余方面(安全性，NOSQL之类)思考，比方须要应用JSON加强性能，那么能够思考降级。
2. 如果有足够的MySQL运维能力，能够思考降级到MySQL8，然而运维须要提供小版本甚至主版本升级的计划与能力，并能继续对MySQL配置进行优化。

简而言之一句话，生产上先等等，等到8.1版本当前再看看。

至于开发或者测试环境，能够尝试一下，做一些技术筹备。

1.3 次要性能数据比照

本文的性能比拟次要看各种操作的耗时(或者说响应工夫)，以及在操作执行期间的资源(CPU与内存)耗费。

以下耗时统计与资源耗费统计均基于本地测试环境与测试数据，不能代表广泛的性能体现。只能用于雷同环境与数据下Mysql5与8的性能比拟。

1.3.1 耗时比拟

对MySQL8与MySQL5别离进行了以下操作：

留神：

1. 各个测试的具体阐明参考后续章节。
2. 无索引连贯1，无索引连贯2，独立子查问以及关联子查问中，mysql8劣势显著的起因都是哈希连贯的反对。就是说，这几个测试案例中的表的连贯字段都是没有索引的字段。
3. 关联子查问在MySQL8中还多一个半连贯优化，但劣势不显著。
4. 500万以上的单表就应该思考分区或分表了，这里不思考这种场景。
5. 对于索引连贯与哈希连贯的性能比照，不能一概而论谁性能体现更好，而是取决于具体的表构造与数据量。这个点与本文其实无关，但后续章节也有探讨。

1.3.2 资源耗费统计

在测试过程中，对CPU与内存耗费进行了简略的统计，后果如下：

由此能够得出的初步论断：

1. MySQL8的内存使用量高于MySQL5。
2. 写入数据时，MySQL8须要更多的CPU资源。

简而言之，MySQL8比MySQL5更耗费CPU与内存资源。

二、性能测试环境

本次测试应用docker镜像，在本地启动了两个mysql容器，均没有资源限度，也没有非凡的性能优化配置。

• MySQL5版本 : 5.7.28
• MySQL8版本 : 8.0.22
• 安装文件 : mysql官网提供的docker镜像
• Docker宿主机OS : Linux Mint 19.1 Tessa
• Docker宿主机CPU : Intel(R) Core(TM) i7-8550U CPU @ 1.80GHz 4 core 8 process
• Docker宿主机内存 : 32G
• Docker宿主机磁盘 : SSD
• MySQL5配置 :

[client]default-character-set=utf8mb4[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_citransaction_isolation = READ-COMMITTED[mysql]default-character-set=utf8mb4

• MySQL8配置 :

[mysqld]pid-file        = /var/run/mysqld/mysqld.pidsocket          = /var/run/mysqld/mysqld.sockdatadir         = /var/lib/mysqlsecure-file-priv= NULLdefault_authentication_plugin = mysql_native_passwordtransaction_isolation = READ-COMMITTED

• Docker容器资源限度 : 无限度

三、测试数据

3.1 DDL

别离在MySQL5与MySQL8的实例中创立如下数据库与表：

CREATE DATABASE `db_mysql_test1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;USE `db_mysql_test1`;DROP TABLE IF EXISTS `db_mysql_test1`.`tb_order`;CREATE TABLE IF NOT EXISTS `db_mysql_test1`.`tb_order` (  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',  `ord_number` varchar(20) NOT NULL COMMENT '订单编号',  `custom_number` varchar(20) NOT NULL COMMENT '客户编号',  `product_number` varchar(20) NOT NULL COMMENT '商品编号',  `warehouse_number` varchar(20) NOT NULL COMMENT '仓库编号',  `ord_status` tinyint NOT NULL COMMENT '订单状态',  `order_time` datetime NOT NULL COMMENT '下单工夫',  PRIMARY KEY (`id`),  UNIQUE KEY `tb_order_unique01` (`ord_number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '订单表';DROP TABLE IF EXISTS `db_mysql_test1`.`tb_custom`;CREATE TABLE IF NOT EXISTS `db_mysql_test1`.`tb_custom` (  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',  `custom_number` varchar(20) NOT NULL COMMENT '客户编号',  `custom_name` varchar(50) NOT NULL COMMENT '客户姓名',  `custom_phone` varchar(20) NOT NULL COMMENT '客户手机号',  `custom_address` varchar(200) NOT NULL COMMENT '客户地址',  PRIMARY KEY (`id`),  UNIQUE KEY `tb_custom_unique01` (`custom_number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '客户表';DROP TABLE IF EXISTS `db_mysql_test1`.`tb_product`;CREATE TABLE IF NOT EXISTS `db_mysql_test1`.`tb_product` (  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',  `product_number` varchar(20) NOT NULL COMMENT '商品编号',  `product_name` varchar(50) NOT NULL COMMENT '商品名称',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '商品表';DROP TABLE IF EXISTS `db_mysql_test1`.`tb_warehouse`;CREATE TABLE IF NOT EXISTS `db_mysql_test1`.`tb_warehouse` (  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',  `warehouse_number` varchar(20) NOT NULL COMMENT '仓库编号',  `warehouse_name` varchar(50) NOT NULL COMMENT '仓库名称',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '仓库表';

3.2 测试数据

本文开发了一个简略的java应用程序用于导入数据，并执行局部查问操作。

因为大部分利用开发会用到JDBC驱动，而MySQL8绝对于MySQL5也提供了一个全新的驱动包。因而咱们也须要思考不同版本驱动包的影响。

测试程序代码能够从github或gitee自行拉取：

https://github.com/zhaochunin...

或

https://gitee.com/XiaTangShao...

运行mysql-test的注意事项：

1. 应用openJDK11。
2. 测试mysql8或mysql5时，将pom中的mysql-connector-java依赖版本批改为对应版本，而后将MySQLPerformanceTest中的JDBC_URL，JDBC_USER与JDBC_PWD设置为对应的值。留神不要遗记给JDBC_URL的参数增加rewriteBatchedStatements=true，短少此参数的话，PreparedStatement的executeBatch将无奈失效。
3. 该程序每次生成新的随机数据并将其导入数据库。(导入前会主动执行truncate截断相干表。)
4. 该程序除导入数据之外，还执行了四张表的全表查问，以及三个内联查问。
5. 该程序统计了JDBC连贯，批量插入，全表查问和内联查问的耗费工夫。(具体后果见后续章节)

四、性能测试

别离对MySQL8和MySQL5进行了以下性能测试，并统计后果如下：

4.1 JDBC连贯

依据mysql-test程序测试后果，MySQL8和MySQL5的JDBC连接时间基本相同。

1. MySQL8的JDBC驱动包版本为8.0.22，对应的Driver Class是com.mysql.cj.jdbc.Driver。
2. MySQL5的JDBC驱动包版本为5.1.47，对应的Driver Class是com.mysql.jdbc.Driver。

某些材料上说MySQL8如果用5.X的JDBC驱动会有性能问题。这里没有测试这种案例，失常来说，应用程序也应该会降级JDBC驱动，否则会出正告。

4.2 大表写入

参考 mysql-test程序的insertOrder办法，向tb_order表分批插入了100万条数据，每1000条插入一次。

• mysql8的均匀耗时 : 33389毫秒，CPU使用率在90%高低。
• mysql5的均匀耗时 : 23446毫秒，CPU使用率在70%高低。

能够看到，mysql8在写入数据时，会耗费更多的CPU，耗时也更多一点。

MySQL8可能须要性能相干配置上做一些优化。

4.3 大表扫描

参考 mysql-test程序的selectOrders办法，对一张100万数据的表tb_order做了一次无条件查问，MySQL做了全表扫描，mysql8与mysql5的执行打算是一样的。

• mysql8的均匀耗时 : 1182毫秒，CPU使用率在100%高低。
• mysql5的均匀耗时 : 1311毫秒，CPU使用率在100%高低。

能够看到，两者耗时和CPU耗费基本相同，MySQL8在耗时上略占优势。

4.4 索引查问

为表tb_order创立一个一般索引，并在一个等值查问中应用它。

CREATE INDEX `tb_order_idx02` ON `db_mysql_test1`.`tb_order` (`warehouse_number`, `product_number`);

mysql5中执行：

-- 耗时大概0.04秒SELECT * FROM tb_order where warehouse_number = 'whs_0000000075';mysql> explain SELECT * FROM tb_order where warehouse_number = 'whs_0000000075';+----+-------------+----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+| id | select_type | table    | partitions | type | possible_keys  | key            | key_len | ref   | rows  | filtered | Extra |+----+-------------+----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | tb_order | NULL       | ref  | tb_order_idx02 | tb_order_idx02 | 82      | const | 19526 |   100.00 | NULL  |+----+-------------+----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.01 sec)

mysql8中执行：

-- 耗时大概0.05秒SELECT * FROM tb_order where warehouse_number = 'whs_0000000075';mysql> explain SELECT * FROM tb_order where warehouse_number = 'whs_0000000075';+----+-------------+----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+| id | select_type | table    | partitions | type | possible_keys  | key            | key_len | ref   | rows  | filtered | Extra |+----+-------------+----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+|  1 | SIMPLE      | tb_order | NULL       | ref  | tb_order_idx02 | tb_order_idx02 | 82      | const | 19526 |   100.00 | NULL  |+----+-------------+----------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)

可见，对于一般索引查问来说，mysql5与mysql8性能体现基本一致。

4.5 索引连贯

参考 mysql-test程序的selectOrderJoinCustom办法，对一张100万数据的表和一张10万数据的表做连贯查问，连贯字段上建设了惟一索引。此时，无论MySQL8还是MySQL5，其优化器都会抉择索引连贯策略。

• mysql8的均匀耗时 : 3335毫秒，CPU使用率在100%高低。
• mysql5的均匀耗时 : 2860毫秒，CPU使用率在100%高低。

能够看到，两者CPU耗费基本相同，但MySQL8在耗时上略多于MySQL5。

执行打算统一，表构造与数据量也统一，MySQL8却慢一点，还是须要在性能相干配置上做一些优化。

查看两者的执行打算可知，两者都采纳了索引连贯：将tb_order作为主表，遍历其后果集的每条记录，再应用连贯字段上的惟一索引tb_custom_unique01从表tb_custom中查找对应记录。即，Nested loop + eq_ref。

mysql8的执行打算：

mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.custom_number, b.custom_name FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------+| id | select_type | table | partitions | type   | possible_keys      | key                | key_len | ref                            | rows   | filtered | Extra |+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------+|  1 | SIMPLE      | a     | NULL       | ALL    | NULL               | NULL               | NULL    | NULL                           | 994365 |   100.00 | NULL  ||  1 | SIMPLE      | b     | NULL       | eq_ref | tb_custom_unique01 | tb_custom_unique01 | 82      | db_mysql_test1.a.custom_number |      1 |   100.00 | NULL  |+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> mysql> explain format=tree SELECT a.ord_number, a.ord_status, a.order_time, b.custom_number, b.custom_name FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN                                                                                                                                                                                                                        |+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Nested loop inner join  (cost=616793.16 rows=994365)    -> Table scan on a  (cost=100902.25 rows=994365)    -> Single-row index lookup on b using tb_custom_unique01 (custom_number=a.custom_number)  (cost=0.42 rows=1) |+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

explain format=tree [SQL语句] 是MySQL8.0.21版本开始新增的语法，能够查看到一些额定的具体的执行打算信息。

mysql5的执行打算：

mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.custom_number, b.custom_name FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------+| id | select_type | table | partitions | type   | possible_keys      | key                | key_len | ref                            | rows   | filtered | Extra |+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------+|  1 | SIMPLE      | a     | NULL       | ALL    | NULL               | NULL               | NULL    | NULL                           | 994365 |   100.00 | NULL  ||  1 | SIMPLE      | b     | NULL       | eq_ref | tb_custom_unique01 | tb_custom_unique01 | 82      | db_mysql_test1.a.custom_number |      1 |   100.00 | NULL  |+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------+2 rows in set, 1 warning (0.00 sec)

4.6 无索引连贯

参考 mysql-test程序的selectOrderJoinProduct办法与selectOrderJoinWarehouse办法，这里别离对上面两种数据量的案例做了无索引连贯的测试：

• 100万条记录表与1万条记录表连贯查问，连贯字段无索引。对应selectOrderJoinProduct办法，章节1.3.1 耗时比拟中的无索引连贯1。
• 100万条记录表与100条记录表连贯查问，连贯字段无索引。对应selectOrderJoinWarehouse办法，章节1.3.1 耗时比拟中的无索引连贯2。

此时MySQL8的性能劣势极大：

• 100万连1万，mysql8的均匀耗时 : 2029毫秒，CPU使用率在100%高低。
• 100万连1万，mysql5的均匀耗时 : 1771556毫秒，CPU使用率在100%高低。
• 100万连100，mysql8的均匀耗时 : 1583毫秒，CPU使用率在100%高低。
• 100万连100，mysql5的均匀耗时 : 17042毫秒，CPU使用率在100%高低。

为何连贯字段无索引的状况下，MySQL8的劣势如此微小？这就是MySQL8开始反对的哈希连贯的功绩了。

selectOrderJoinProduct在mysql8的执行打算：

mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   9999 |   100.00 | NULL                                       ||  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+2 rows in set, 1 warning (0.00 sec)mysql> mysql> explain format=tree SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN                                                                                                                                                                                                        |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| -> Inner hash join (a.product_number = b.product_number)  (cost=994283853.13 rows=994265578)    -> Table scan on a  (cost=2.72 rows=994365)    -> Hash        -> Table scan on b  (cost=1057.73 rows=9999) |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

selectOrderJoinProduct在mysql5的执行打算：

mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   9999 |   100.00 | NULL                                               ||  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec)

咱们能够分明的看到，MySQL8应用的是hash join，而MySQL5应用的是Block Nested Loop，块嵌套循环，BNL，该策略从MySQL 8.0.20开始不再应用。

hash join就是将较小的那张表的数据集做成哈希数据集，而后遍历较大的表的数据集，对每条记录，依据连贯字段间接从哈希数据集中获取小表对应记录。其工夫复杂度为O(m+n)，m与n别离是大表与小表的数据量。

BNL就是双层嵌套循环，通常将小表作为主表，遍历其数据集，对每条记录再遍历大表数据集查找对应记录。其工夫复杂度为O(m*n)。

即便连贯的两张表有其余非连贯字段上的过滤条件，且有索引能够应用，大部分状况下也仍然是hash join效率更高。

4.7 独立子查问

在MySQL中执行以下应用IN的独立子查问，并查看其执行打算：

SELECT ord_number, warehouse_number from tb_order where warehouse_number in (SELECT warehouse_number from tb_warehouse);explain SELECT ord_number, warehouse_number from tb_order where warehouse_number in (SELECT warehouse_number from tb_warehouse);show warnings;

留神查看完执行打算之后，要立刻执行show warnings;，不然看不到semi join半连贯优化。

查看执行后果，MySQL8劣势极大。查看执行打算会发现，起因还是哈希连贯的应用。

• mysql8的耗时 : 0.84秒。
• mysql5的耗时 : 14.69秒。

mysql5的执行后果及其执行打算：

-- 14.69秒SELECT ord_number, warehouse_number from tb_order where warehouse_number in (SELECT warehouse_number from tb_warehouse);mysql> explain SELECT ord_number, warehouse_number from tb_order where warehouse_number in (SELECT warehouse_number from tb_warehouse);+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+|  1 | SIMPLE       | <subquery2>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   NULL |   100.00 | NULL                                               ||  1 | SIMPLE       | tb_order     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (Block Nested Loop) ||  2 | MATERIALIZED | tb_warehouse | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |   100.00 | NULL                                               |+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+3 rows in set, 1 warning (0.00 sec)mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message                                                                                                                                                                                                                                                                                                                    |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note  | 1003 | /* select#1 */ select `db_mysql_test1`.`tb_order`.`ord_number` AS `ord_number`,`db_mysql_test1`.`tb_order`.`warehouse_number` AS `warehouse_number` from `db_mysql_test1`.`tb_order` semi join (`db_mysql_test1`.`tb_warehouse`) where (`db_mysql_test1`.`tb_order`.`warehouse_number` = `<subquery2>`.`warehouse_number`) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

能够看到，对于应用IN的独立子查问，MySQL5抉择了Semi-join半连贯和Materialization物化的优化策略，将子查问改为半连贯，并物化为长期表。

但并不是说做了半连贯物化优化就肯定更快，优化器会依据具体的表统计信息(表构造与表数据量等)估算并比拟不同的优化策略，抉择一个估算性能体现最好的策略。

同时咱们应该留神到，尽管IN语句做了肯定的优化，但tb_order与物化的长期表之间连贯形式仍然是Block Nested Loop，该语句仍然较慢的起因次要是这个。

mysql8的执行后果及其执行打算：

-- 0.84秒SELECT ord_number, warehouse_number from tb_order where warehouse_number in (SELECT warehouse_number from tb_warehouse);mysql> explain SELECT ord_number, warehouse_number from tb_order where warehouse_number in (SELECT warehouse_number from tb_warehouse);+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+|  1 | SIMPLE       | <subquery2>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   NULL |   100.00 | NULL                                       ||  1 | SIMPLE       | tb_order     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (hash join) ||  2 | MATERIALIZED | tb_warehouse | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |   100.00 | NULL                                       |+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+3 rows in set, 1 warning (0.00 sec)mysql> mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message                                                                                                                                                                                                                                                                                                                    |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note  | 1003 | /* select#1 */ select `db_mysql_test1`.`tb_order`.`ord_number` AS `ord_number`,`db_mysql_test1`.`tb_order`.`warehouse_number` AS `warehouse_number` from `db_mysql_test1`.`tb_order` semi join (`db_mysql_test1`.`tb_warehouse`) where (`db_mysql_test1`.`tb_order`.`warehouse_number` = `<subquery2>`.`warehouse_number`) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

MySQL8也做了半连贯semi join和物化MATERIALIZED优化，但不再应用BNL，而是换成了hash join。

4.8 关联子查问

在MySQL中执行以下应用EXISTS的关联子查问，并查看其执行打算：

SELECT ord_number, warehouse_number from tb_order where EXISTS (SELECT * from tb_warehouse where warehouse_number = tb_order.warehouse_number );explain SELECT ord_number, warehouse_number from tb_order where EXISTS (SELECT * from tb_warehouse where warehouse_number = tb_order.warehouse_number );show warnings;

留神查看完执行打算之后，要立刻执行show warnings;，不然看不到semi join半连贯优化。

查看执行后果，MySQL8劣势极大。查看执行打算会发现，起因次要是对EXISTS子句进行半连贯+物化优化后能够应用哈希连贯。

• mysql8的耗时 : 0.83秒。
• mysql5的耗时 : 18.02秒。

mysql5中的执行后果和执行打算：

-- 18.02秒+SELECT ord_number, warehouse_number from tb_order where EXISTS (SELECT * from tb_warehouse where warehouse_number = tb_order.warehouse_number );mysql> explain SELECT ord_number, warehouse_number from tb_order where EXISTS (SELECT * from tb_warehouse where warehouse_number = tb_order.warehouse_number );+----+--------------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+| id | select_type        | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |+----+--------------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+|  1 | PRIMARY            | tb_order     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |   100.00 | Using where ||  2 | DEPENDENT SUBQUERY | tb_warehouse | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |    10.00 | Using where |+----+--------------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)mysql> show warnings;+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                     |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note  | 1276 | Field or reference 'db_mysql_test1.tb_order.warehouse_number' of SELECT #2 was resolved in SELECT #1                                                                                                                                                                                                                                                                        || Note  | 1003 | /* select#1 */ select `db_mysql_test1`.`tb_order`.`ord_number` AS `ord_number`,`db_mysql_test1`.`tb_order`.`warehouse_number` AS `warehouse_number` from `db_mysql_test1`.`tb_order` where exists(/* select#2 */ select 1 from `db_mysql_test1`.`tb_warehouse` where (`db_mysql_test1`.`tb_warehouse`.`warehouse_number` = `db_mysql_test1`.`tb_order`.`warehouse_number`)) |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

能够看到，对于应用EXISTS的关联子查问，MySQL5没有做Semi-join Materialization优化，相比IN语句性能略有不如。

mysql8中的执行后果和执行打算：

-- 0.83秒+SELECT ord_number, warehouse_number from tb_order where EXISTS (SELECT * from tb_warehouse where warehouse_number = tb_order.warehouse_number );mysql> explain SELECT ord_number, warehouse_number from tb_order where EXISTS (SELECT * from tb_warehouse where warehouse_number = tb_order.warehouse_number );+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+| id | select_type  | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+|  1 | SIMPLE       | <subquery2>  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   NULL |   100.00 | NULL                                       ||  1 | SIMPLE       | tb_order     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (hash join) ||  2 | MATERIALIZED | tb_warehouse | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |   100.00 | NULL                                       |+----+--------------+--------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+3 rows in set, 2 warnings (0.00 sec)mysql> mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message                                                                                                                                                                                                                                                                                                                    |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note  | 1276 | Field or reference 'db_mysql_test1.tb_order.warehouse_number' of SELECT #2 was resolved in SELECT #1                                                                                                                                                                                                                       || Note  | 1003 | /* select#1 */ select `db_mysql_test1`.`tb_order`.`ord_number` AS `ord_number`,`db_mysql_test1`.`tb_order`.`warehouse_number` AS `warehouse_number` from `db_mysql_test1`.`tb_order` semi join (`db_mysql_test1`.`tb_warehouse`) where (`db_mysql_test1`.`tb_order`.`warehouse_number` = `<subquery2>`.`warehouse_number`) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

性能相比mysql5有极大晋升，但咱们要留神，该案例性能晋升的最次要起因是因为半连贯优化，导致可能应用hash join了。

4.9 倒序排序

MySQL8真正反对创立倒序索引，而不是以前那样伪装创立倒序索引，但理论还是正序索引。这使得某些场景下性能有所晋升。

比方这样的案例，对tb_order表查问时，应用custom_number与product_number排序，其中product_number须要倒序。失常来说，应该创立上面的索引：

CREATE INDEX `tb_order_idx01` ON `db_mysql_test1`.`tb_order` (`custom_number`, `product_number` DESC);

但同样的索引，在MySQL8中失效，无效进步了性能；而在MySQL5中并未失效，性能仍然不高。

• 百万数据混合排序在mysql8的耗时 : 0.44秒。
• 百万数据混合排序在mysql5的耗时 : 1.34秒。

mysql5中执行：

-- 删除倒序索引mysql> alter table tb_order drop index tb_order_idx01;mysql> explain SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+|  1 | SIMPLE      | tb_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |   100.00 | Using filesort |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+1 row in set, 1 warning (0.00 sec)-- 创立倒序索引，自身也是组合索引，局部升序，局部降序CREATE INDEX `tb_order_idx01` ON `db_mysql_test1`.`tb_order` (`custom_number`, `product_number` DESC);mysql> explain SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;+----+-------------+----------+------------+-------+---------------+----------------+---------+------+--------+----------+-----------------------------+| id | select_type | table    | partitions | type  | possible_keys | key            | key_len | ref  | rows   | filtered | Extra                       |+----+-------------+----------+------------+-------+---------------+----------------+---------+------+--------+----------+-----------------------------+|  1 | SIMPLE      | tb_order | NULL       | index | NULL          | tb_order_idx01 | 164     | NULL | 994365 |   100.00 | Using index; Using filesort |+----+-------------+----------+------------+-------+---------------+----------------+---------+------+--------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)-- 查问100万条数据须要 1.34秒mysql> SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;

1. 创立倒序索引前，应用filesort，性能通常比应用index要低。
2. 创立倒序索引后，只有正序字段应用index，倒序局部仍然要应用filesort，因为MySQL5的倒序索引是假的。

mysql8中执行：

-- 删除倒序索引mysql> alter table tb_order drop index tb_order_idx01;mysql> explain SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+|  1 | SIMPLE      | tb_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |   100.00 | Using filesort |+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+1 row in set, 1 warning (0.00 sec)-- 创立倒序索引CREATE INDEX `tb_order_idx01` ON `db_mysql_test1`.`tb_order` (`custom_number`, `product_number` DESC);mysql> explain SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;+----+-------------+----------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+| id | select_type | table    | partitions | type  | possible_keys | key            | key_len | ref  | rows   | filtered | Extra       |+----+-------------+----------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+|  1 | SIMPLE      | tb_order | NULL       | index | NULL          | tb_order_idx01 | 164     | NULL | 100000 |   100.00 | Using index |+----+-------------+----------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+1 row in set, 1 warning (0.00 sec)-- 查问100万条数据须要 0.44秒mysql> SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;

1. 创立倒序索引前，应用filesort，性能通常比应用index要低。
2. 创立倒序索引后，全副应用index，倒序索引失效。

4.10 索引连贯与哈希连贯的性能比照

当初咱们晓得，哈希连贯只在连贯字段上没有任何索引时起效，大部分业务场景里，连贯字段上都是有各种索引的，这时Mysql应用的是索引连贯，即，遍历主表数据后果集，对每一条记录，应用索引去副表后果集中查找。即，Nested Loop + 索引。留神，这不是Block Nested LoopBNL块嵌套循环，BNL是以前的Mysql在连贯字段上没有索引时采纳的连贯策略。

目前mysql在连贯字段上有索引的状况下，默认应用索引连贯。但这并不是说索引连贯就肯定比哈希连贯快。这取决于具体的数据量和表构造。

<!-- ### 4.10.1 MySQL8的哈希连贯有时比MySQL5的索引连贯快
mysql5中的体现：

-- tb_order有100万数据，tb_product有1万数据，耗时在半小时左右SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   9999 |   100.00 | NULL                                               ||  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec)-- 增加惟一索引CREATE UNIQUE INDEX `tb_product_unique01` ON `db_mysql_test1`.`tb_product` (`product_number`);mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+--------+---------------------+---------------------+---------+---------------------------------+--------+----------+-------+| id | select_type | table | partitions | type   | possible_keys       | key                 | key_len | ref                             | rows   | filtered | Extra |+----+-------------+-------+------------+--------+---------------------+---------------------+---------+---------------------------------+--------+----------+-------+|  1 | SIMPLE      | a     | NULL       | ALL    | NULL                | NULL                | NULL    | NULL                            | 994365 |   100.00 | NULL  ||  1 | SIMPLE      | b     | NULL       | eq_ref | tb_product_unique01 | tb_product_unique01 | 82      | db_mysql_test1.a.product_number |      1 |   100.00 | NULL  |+----+-------------+-------+------------+--------+---------------------+---------------------+---------+---------------------------------+--------+----------+-------+2 rows in set, 1 warning (0.00 sec)-- 2.26秒SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);

mysql8中的体现：

-- tb_order有100万数据，tb_product有1万数据，耗时在1.3秒左右SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);mysql> explain SELECT a.ord_number, a.ord_status, a.order_time, b.product_number, b.product_name FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   9999 |   100.00 | NULL                                       ||  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+2 rows in set, 1 warning (0.00 sec)

4.10.1 mysql8强制开启或敞开hash join

-- 增加惟一索引CREATE UNIQUE INDEX `tb_product_unique01` ON `db_mysql_test1`.`tb_product` (`product_number`);-- 连贯字段上有索引时，默认不应用hash joinexplain SELECT count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);-- mysql8.0.19之前的版本应该反对这种强制应用 hashjoin的写法，但从MySQL 8.0.19开始应该就不反对了。explain SELECT /*+ HASH_JOIN(a,b)  */ count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);-- 要强制应用hash join的话，必须让索引不起作用，能够选用上面任意一种形式：explain SELECT /*+ NO_JOIN_INDEX(b tb_product_unique01)  */ count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);explain SELECT /*+ NO_INDEX(b tb_product_unique01)  */ count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);SELECT count(*) FROM tb_order a inner join tb_product b ignore index(tb_product_unique01) on(a.product_number = b.product_number);

官网文档说能够用NO_BNL强制走Hash Join，然而我试了下，如果连贯字段没有索引的话，默认就走hash join了；如果有索引的话，那么必须疏忽掉该索引才会走hash join。

BNL与NO_BNL原本是用来管制是否应用block nested loop块嵌套循环的。官网文档说从8.0.20开始，Mysql曾经不再应用block nested loop块嵌套循环了，而后又不能立刻删除这两个hint语法，所以就用来强制走不走hash join了。。。但实际上没啥用，因为有索引的话优先用的是索引连贯，没索引默认用hash join。即，只有索引失效，设置NO_BNL就是徒劳的，并不会走hash join。想在有索引的状况下强制走hash join的话，就必须让索引不起作用。

4.10.2 哈希连贯与索引连贯性能比拟

有的案例，哈希连贯慢一点；有的案例，哈希连贯快一点。不能一概而论。

• 案例1，在该测试条件(大表100万，小表1万，连贯字段有惟一索引)下，hash join比索引连贯稍慢：

-- 0.44秒SELECT count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);-- 0.88秒SELECT /*+ NO_JOIN_INDEX(b tb_product_unique01)  */ count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);mysql> explain SELECT count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+--------+---------------------+---------------------+---------+---------------------------------+--------+----------+-------------+| id | select_type | table | partitions | type   | possible_keys       | key                 | key_len | ref                             | rows   | filtered | Extra       |+----+-------------+-------+------------+--------+---------------------+---------------------+---------+---------------------------------+--------+----------+-------------+|  1 | SIMPLE      | a     | NULL       | index  | NULL                | tb_order_idx01      | 164     | NULL                            | 994365 |   100.00 | Using index ||  1 | SIMPLE      | b     | NULL       | eq_ref | tb_product_unique01 | tb_product_unique01 | 82      | db_mysql_test1.a.product_number |      1 |   100.00 | Using index |+----+-------------+-------+------------+--------+---------------------+---------------------+---------+---------------------------------+--------+----------+-------------+2 rows in set, 1 warning (0.00 sec)mysql> mysql> mysql> explain SELECT /*+ NO_JOIN_INDEX(b tb_product_unique01)  */ count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+---------------------------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows   | filtered | Extra                                                   |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+---------------------------------------------------------+|  1 | SIMPLE      | b     | NULL       | index | NULL          | tb_product_unique01 | 82      | NULL |   9999 |   100.00 | Using index                                             ||  1 | SIMPLE      | a     | NULL       | index | NULL          | tb_order_idx01      | 164     | NULL | 994365 |    10.00 | Using where; Using index; Using join buffer (hash join) |+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+--------+----------+---------------------------------------------------------+2 rows in set, 1 warning (0.00 sec)

• 案例2，大表100万，小表10万，连贯字段有惟一索引，hash join比索引连贯稍快一点。

-- 去除其余对性能有加成的索引alter table tb_order drop index tb_order_idx01;-- tb_custom.custom_number上有惟一索引 tb_custom_unique01，默认应用索引连贯-- 1.52秒SELECT count(*) FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);-- 通过hint语法NO_JOIN_INDEX让语句执行时，不再应用指标索引tb_custom_unique01做索引连贯-- 1.12秒SELECT /*+ NO_JOIN_INDEX(b tb_custom_unique01)  */ count(*) FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);mysql> explain SELECT count(*) FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------------+| id | select_type | table | partitions | type   | possible_keys      | key                | key_len | ref                            | rows   | filtered | Extra       |+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------------+|  1 | SIMPLE      | a     | NULL       | ALL    | NULL               | NULL               | NULL    | NULL                           | 994365 |   100.00 | NULL        ||  1 | SIMPLE      | b     | NULL       | eq_ref | tb_custom_unique01 | tb_custom_unique01 | 82      | db_mysql_test1.a.custom_number |      1 |   100.00 | Using index |+----+-------------+-------+------------+--------+--------------------+--------------------+---------+--------------------------------+--------+----------+-------------+2 rows in set, 1 warning (0.00 sec)mysql> explain SELECT /*+ NO_JOIN_INDEX(b tb_custom_unique01)  */ count(*) FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+--------+----------+--------------------------------------------+| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows   | filtered | Extra                                      |+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+--------+----------+--------------------------------------------+|  1 | SIMPLE      | b     | NULL       | index | NULL          | tb_custom_unique01 | 82      | NULL |  99522 |   100.00 | Using index                                ||  1 | SIMPLE      | a     | NULL       | ALL   | NULL          | NULL               | NULL    | NULL | 994365 |    10.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+--------+----------+--------------------------------------------+2 rows in set, 1 warning (0.00 sec)

五、mysql-test程序测试后果记录

mysql-test程序别离对MySQL8和MySQL5进行了数轮测试，统计如下：

具体测试项目请参考程序代码。

5.1 mysql8测试后果

对mysql8进行了5轮测试，取其平均值。

5.2 mysql5测试后果

mysql5的selectOrderJoinProduct切实太过耗时，这里只测试了三轮。