乐趣区

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

jiezi

一、概述

先说论断，如果你的 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 别离进行了以下操作：

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

1.3.2 资源耗费统计

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

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

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

  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 测试后果

轮次 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 切实太过耗时，这里只测试了三轮。

退出移动版