关于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 = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
transaction_isolation = READ-COMMITTED

[mysql]
default-character-set=utf8mb4

• MySQL8 配置 :

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

default_authentication_plugin = mysql_native_password
transaction_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 join
explain 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 切实太过耗时，这里只测试了三轮。