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较早的版本里支持，后续更高一点版本又不支持了)。

因此对于低并发的生产环境，个人建议：

如果没有足够的MySQL运维能力，那么不建议为了性能提升而升级MySQL到8.0.x的版本，除非确定生产上有很多无索引的字段作为连接条件(实际上不可能)。但如果要从其他方面(安全性，NOSQL之类)考虑，比如需要使用JSON增强功能，那么可以考虑升级。
如果有足够的MySQL运维能力，可以考虑升级到MySQL8，但是运维需要提供小版本甚至主版本升级的方案与能力，并能持续对MySQL配置进行优化。

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

至于开发或者测试环境，可以尝试一下，做一些技术准备。

1.3 主要性能数据对比

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

以下耗时统计与资源消耗统计均基于本地测试环境与测试数据，不能代表普遍的性能表现。只能用于相同环境与数据下Mysql5与8的性能比较。

1.3.1 耗时比较

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

操作	操作说明	mysql8耗时	mysql5耗时
JDBC连接	-	3毫秒	2毫秒
大表写入	100万条记录分批插入，每批1000条	30秒+	20秒+
大表扫描	单表100万记录，无条件的全表扫描	1秒+	1秒+
索引查询	单表100万记录，普通Btree索引，等值条件查询，命中率1%	0.02~0.05秒	0.02~0.05秒
索引连接	百万记录表与十万记录表连接，连接字段是唯一索引	33秒+	28秒+
无索引连接1	百万记录表与一万记录表连接，连接字段无索引	2秒+	半小时左右
无索引连接2	百万记录表与100记录表连接，连接字段无索引	1.5秒+	17秒+
独立子查询	100记录表作为百万记录表的`IN`条件子查询	0.8秒+	14秒+
关联子查询	100记录表作为百万记录表的`EXISTS`条件子查询	0.8秒+	18秒+
倒序排序	百万记录表建立正序倒序混合索引，并使用它排序	0.4秒+	1.3秒+

注意：

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

1.3.2 资源消耗统计

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

项目	mysql8	mysql5
批量写入百万数据过程中的CPU使用率(%)	90	70
各种查询过程中的CPU使用率(%)	100	100
mysql容器重启之后内存使用量(M)	341.2	205.9
各种操作之后mysql容器内存使用涨幅(M)	130	110

由此可以得出的初步结论：

MySQL8的内存使用量高于MySQL5。
写入数据时，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的注意事项：

使用openJDK11。
测试mysql8或mysql5时，将pom中的mysql-connector-java依赖版本修改为对应版本，然后将MySQLPerformanceTest中的JDBC_URL，JDBC_USER与JDBC_PWD设置为对应的值。注意不要忘记给JDBC_URL的参数添加rewriteBatchedStatements=true，缺少此参数的话，PreparedStatement的executeBatch将无法生效。
该程序每次生成新的随机数据并将其导入数据库。(导入前会自动执行truncate截断相关表。)
该程序除导入数据之外，还执行了四张表的全表查询，以及三个内联查询。
该程序统计了JDBC连接，批量插入，全表查询和内联查询的消耗时间。(具体结果见后续章节)

四、性能测试

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

4.1 JDBC连接

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

MySQL8的JDBC驱动包版本为8.0.22，对应的Driver Class是com.mysql.cj.jdbc.Driver。
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 ;

创建倒序索引前，使用filesort，性能通常比使用index要低。
创建倒序索引后，只有正序字段使用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 ;

创建倒序索引前，使用filesort，性能通常比使用index要低。
创建倒序索引后，全部使用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测试结果

轮次	1	2	3	4	5	平均值
prepareData 耗时(毫秒)	10258	10892	10871	10502	9413	10387
truncateTables 耗时(毫秒)	553	569	581	527	552	556
insertOrder 耗时(毫秒)	35756	31831	34257	30403	34697	33389
insertCustom 耗时(毫秒)	3349	2781	2795	2613	2944	2896
insertProduct 耗时(毫秒)	317	231	275	198	294	263
insertWarehouse 耗时(毫秒)	6	15	8	14	8	10
selectOrders conn 耗时(毫秒)	1	3	3	6	3	3
selectOrders executeQuery 耗时(毫秒)	1399	1123	1150	1057	1180	1182
selectOrders ResultSet遍历耗时(毫秒)	104	76	80	78	85	85
selectOrders 总耗时(毫秒)	1507	1205	1234	1143	1269	1272
selectOrders 件数	1000000	1000000	1000000	1000000	1000000	1000000
selectCustoms conn 耗时(毫秒)	2	2	3	2	2	2
selectCustoms executeQuery 耗时(毫秒)	124	140	115	76	107	112
selectCustoms ResultSet遍历耗时(毫秒)	51	66	74	18	50	52
selectCustoms 总耗时(毫秒)	179	210	193	98	161	168
selectCustoms 件数	100000	100000	100000	100000	100000	100000
selectProducts conn 耗时(毫秒)	4	4	4	2	5	4
selectProducts executeQuery 耗时(毫秒)	15	13	12	9	10	12
selectProducts ResultSet遍历耗时(毫秒)	5	13	12	2	7	8
selectProducts 总耗时(毫秒)	25	31	29	15	23	25
selectProducts 件数	10000	10000	10000	10000	10000	10000
selectWarehouses conn 耗时(毫秒)	3	3	3	3	3	3
selectWarehouses executeQuery 耗时(毫秒)	1	1	1	1	1	1
selectWarehouses ResultSet遍历耗时(毫秒)	0	0	0	0	0	0
selectWarehouses 总耗时(毫秒)	5	5	5	4	5	5
selectWarehouses 件数	100	100	100	100	100	100
selectOrderJoinCustom conn 耗时(毫秒)	3	3	3	2	3	3
selectOrderJoinCustom executeQuery 耗时(毫秒)	3586	3506	3684	3084	2816	3335
selectOrderJoinCustom ResultSet遍历耗时(毫秒)	66	99	102	52	73	78
selectOrderJoinCustom 总耗时(毫秒)	3657	3611	3791	3140	2894	3419
selectOrderJoinCustom 件数	1000000	1000000	1000000	1000000	1000000	1000000
selectOrderJoinProduct conn 耗时(毫秒)	2	3	3	2	2	2
selectOrderJoinProduct executeQuery 耗时(毫秒)	2424	1704	1943	1709	2364	2029
selectOrderJoinProduct ResultSet遍历耗时(毫秒)	55	74	69	51	56	61
selectOrderJoinProduct 总耗时(毫秒)	2482	1782	2016	1763	2424	2093
selectOrderJoinProduct 件数	1000000	1000000	1000000	1000000	1000000	1000000
selectOrderJoinWarehouse conn 耗时(毫秒)	2	2	2	2	2	2
selectOrderJoinWarehouse executeQuery 耗时(毫秒)	1466	2269	1542	1107	1529	1583
selectOrderJoinWarehouse ResultSet遍历耗时(毫秒)	62	135	74	52	50	75
selectOrderJoinWarehouse 总耗时(毫秒)	1531	2411	1619	1162	1582	1661
selectOrderJoinWarehouse 件数	1000000	1000000	1000000	1000000	1000000	1000000

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

5.2 mysql5测试结果

轮次	1	2	3	平均值
prepareData 耗时(毫秒)	12377	9073	9204	10218
truncateTables 耗时(毫秒)	627	475	451	518
insertOrder 耗时(毫秒)	24152	24193	21994	23446
insertCustom 耗时(毫秒)	1912	1916	1941	1923
insertProduct 耗时(毫秒)	137	147	156	147
insertWarehouse 耗时(毫秒)	4	4	8	5
selectOrders conn 耗时(毫秒)	2	3	3	3
selectOrders executeQuery 耗时(毫秒)	1181	1513	1238	1311
selectOrders ResultSet遍历耗时(毫秒)	112	96	106	105
selectOrders 总耗时(毫秒)	1297	1614	1349	1420
selectOrders 件数	1000000	1000000	1000000	1000000
selectCustoms conn 耗时(毫秒)	2	2	2	2
selectCustoms executeQuery 耗时(毫秒)	82	113	116	104
selectCustoms ResultSet遍历耗时(毫秒)	28	23	31	27
selectCustoms 总耗时(毫秒)	114	141	151	135
selectCustoms 件数	100000	100000	100000	100000
selectProducts conn 耗时(毫秒)	2	3	4	3
selectProducts executeQuery 耗时(毫秒)	13	10	17	13
selectProducts ResultSet遍历耗时(毫秒)	3	2	6	4
selectProducts 总耗时(毫秒)	20	15	29	21
selectProducts 件数	10000	10000	10000	10000
selectWarehouses conn 耗时(毫秒)	2	2	3	2
selectWarehouses executeQuery 耗时(毫秒)	0	0	1	0
selectWarehouses ResultSet遍历耗时(毫秒)	0	0	0	0
selectWarehouses 总耗时(毫秒)	4	3	4	4
selectWarehouses 件数	100	100	100	100
selectOrderJoinCustom conn 耗时(毫秒)	2	2	2	2
selectOrderJoinCustom executeQuery 耗时(毫秒)	3156	2548	2876	2860
selectOrderJoinCustom ResultSet遍历耗时(毫秒)	47	52	61	53
selectOrderJoinCustom 总耗时(毫秒)	3207	2604	2941	2917
selectOrderJoinCustom 件数	1000000	1000000	1000000	1000000
selectOrderJoinProduct conn 耗时(毫秒)	2	2	2	2
selectOrderJoinProduct executeQuery 耗时(毫秒)	1655023	1756847	1902797	1771556
selectOrderJoinProduct ResultSet遍历耗时(毫秒)	43	51	222	105
selectOrderJoinProduct 总耗时(毫秒)	1655069	1756902	1903023	1771665
selectOrderJoinProduct 件数	1000000	1000000	1000000	1000000
selectOrderJoinWarehouse conn 耗时(毫秒)	2	2	7	4
selectOrderJoinWarehouse executeQuery 耗时(毫秒)	16264	16030	18831	17042
selectOrderJoinWarehouse ResultSet遍历耗时(毫秒)	35	50	609	231
selectOrderJoinWarehouse 总耗时(毫秒)	16303	16083	19448	17278
selectOrderJoinWarehouse 件数	1000000	1000000	1000000	1000000

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

mysql8与mysql5的单连接性能比较

一、概述

1.1 背景

1.2 单连接的性能比较结论

1.3 低并发环境下是否升级到MySQL8的建议

1.3 主要性能数据对比

1.3.1 耗时比较

1.3.2 资源消耗统计

二、性能测试环境

三、测试数据

3.1 DDL

3.2 测试数据

四、性能测试

4.1 JDBC连接

4.2 大表写入

4.3 大表扫描

4.4 索引查询

4.5 索引连接

4.6 无索引连接

4.7 独立子查询

4.8 关联子查询

4.9 倒序排序

4.10 索引连接与哈希连接的性能对比

4.10.1 mysql8强制开启或关闭hash join

4.10.2 哈希连接与索引连接性能比较

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

5.1 mysql8测试结果

5.2 mysql5测试结果

下塘烧饼

引用和评论

Java多线程梳理之一_多线程开发入门

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 亿级数据平滑迁移实战

使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

MongoDB 8.0已全面可用

mysql8与mysql5的单连接性能比较

一、概述

1.1 背景

1.2 单连接的性能比较结论

1.3 低并发环境下是否升级到MySQL8的建议

1.3 主要性能数据对比

1.3.1 耗时比较

1.3.2 资源消耗统计

二、性能测试环境

三、测试数据

3.1 DDL

3.2 测试数据

四、性能测试

4.1 JDBC连接

4.2 大表写入

4.3 大表扫描

4.4 索引查询

4.5 索引连接

4.6 无索引连接

4.7 独立子查询

4.8 关联子查询

4.9 倒序排序

4.10 索引连接与哈希连接的性能对比

4.10.1 mysql8强制开启或关闭hash join

4.10.2 哈希连接与索引连接性能比较

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

5.1 mysql8测试结果

5.2 mysql5测试结果

下塘烧饼

引用和评论

Java多线程梳理之一_多线程开发入门

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 亿级数据平滑迁移实战

使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

MongoDB 8.0已全面可用

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