编程知识 cdmana.com

Performance comparison between mysql8 and mysql8

One 、 summary

Say first conclusion , If your MySQL The database runs in a highly concurrent environment , that MySQL8 It has a lot of advantages , Upgrade to MySQL8 Is a good choice ; but If your MySQL The running environment is low concurrency , that MySQL8 The advantages are not obvious , I suggest not to upgrade to MySQL8, You can wait .

This article aims at Low concurrency environment Under the MySQL8 And MySQL5 Performance comparison .

1.1 background

According to some of the online uses sysbench It's done MySQL8 According to the results of the benchmark test ,MySQL8 relative MySQL5 The performance advantage of is more reflected in the high concurrency environment ( If the number of connections reaches 1024 even to the extent that 2048) Next , Unit time processing quantity ( for example InnoDB The number of lines or transactions processed ) Greatly improved . namely , High and sent TPS indicators ,MySQL8 relative MySQL5 There are great advantages .

You can refer to this article : MySQL Performance Benchmarking: MySQL 5.7 vs MySQL 8.0

But in the actual production environment , There are also many systems that are not running in high concurrency environments , Their database connections often do not exceed the default maximum number of connections 151, They don't even need to be independent MySQL The server . For this scenario , Whether it is necessary to put MySQL Upgrade to 8 Well ?

This paper aims at MySQL5.7.28 And MySQL8.0.22 Of docker Mirror version , On the basis of no performance optimization configuration , In the same host environment , Under the same table structure and the same amount of data , They've done something exactly the same , Performance testing on a single connection , And carries on the data statistics and the analysis to it .

namely , This paper is not concerned with performance in high concurrency environments , But in a low concurrency environment , Performance on a single connection . At this time, we mainly focus on various kinds of SQL Operation time and resource consumption .

1.2 Single connection performance comparison conclusion

After statistical analysis of the performance test results of a single connection , The following conclusions are drawn :

  • because MySQL8 Yes hash join Support for , For multi table join queries without any index on the join field ,MySQL8 Has an overwhelming performance advantage .
  • If you can use an inverted index ,MySQL8 It has certain performance advantages .
  • On the performance of other scenarios , Such as single table reading and writing , Multi table index join query and so on ,MySQL8 Basic and MySQL5 There's no big difference , It's not even as good as .
  • MySQL8 Consumption of resources , Such as CPU And memory , than MySQL5 A little more .

1.3 Whether to upgrade to MySQL8 The advice of

about Low concurrent In terms of environment ,MySQL8 The biggest performance improvement comes from the support of hash join . But actually because of the index , There are not so many scenarios where hashing can be used . Especially in the production environment which has been running stably for a period of time , If there is no index on the join field and there is a large amount of data , Performance problems should have been exposed long ago ; and MySQL8 The version of is still being updated iteratively , The compatibility of some functions is not very stable ( Some functions are in 8.0.x Earlier versions support , Later higher versions do not support ).

So for low concurrency production environments , Personal advice :

  1. If not enough MySQL Operation and maintenance capacity , that Upgrading for performance improvement is not recommended MySQL To 8.0.x Version of , Unless you make sure that there are a lot of unindexed fields in production as join conditions ( It's practically impossible ). But if you want to do something else ( Security ,NOSQL And so on ) consider , For example, you need to use JSON Enhancements , Then consider upgrading .
  2. If there is enough MySQL Operation and maintenance capacity , Consider upgrading to MySQL8, However, operation and maintenance needs to provide solutions and capabilities for upgrading small or even major versions , And be able to keep up with MySQL Configuration for optimization .

In a word , Wait for production first , wait until 8.1 Let's look at it later .

As for development or test environments , You can try it , Do some technical preparation .

1.3 Comparison of main performance data

The performance comparison of this paper mainly depends on the time-consuming of various operations ( Or response time ), And the resources during the execution of the operation (CPU With memory ) Consume .

The following time consumption statistics and resource consumption statistics are based on local test environment and test data , It doesn't represent general performance . It can only be used in the same environment and data Mysql5 And 8 Performance comparison .

1.3.1 Time consuming comparison

Yes MySQL8 And MySQL5 The following operations are carried out respectively :

operation Operation instructions mysql8 Time consuming mysql5 Time consuming
JDBC Connect - 3 millisecond 2 millisecond
Large tables write 100 Ten thousand records are inserted in batches , Each batch 1000 strip 30 second + 20 second +
Big watch scan Single table 100 Ten thousand records , Unconditional full table scan 1 second + 1 second +
Index query Single table 100 Ten thousand records , Ordinary Btree Indexes , Equivalent condition query , shooting 1% 0.02~0.05 second 0.02~0.05 second
Index join Millions of records linked to 100000 records , The join field is the only index 33 second + 28 second +
No index join 1 One million records are linked to ten thousand records , The join field has no index 2 second + About half an hour
No index join 2 Million records and 100 Log table connection , The join field has no index 1.5 second + 17 second +
Independent subquery 100 A record sheet as a million records IN Conditional subquery 0.8 second + 14 second +
Associated subquery 100 A record sheet as a million records EXISTS Conditional subquery 0.8 second + 18 second +
Reverse sort Million record table building positive and reverse mixed index , And use it to sort 0.4 second + 1.3 second +

Be careful :

  1. Refer to the following sections for a detailed description of each test .
  2. No index join 1, No index join 2, Independent subquery as well as Associated subquery in ,mysql8 The obvious advantage is due to the support of hash join . That is to say , The join fields of the tables in these test cases are non indexed fields .
  3. Associated subquery stay MySQL8 There is also a semi join optimization in , But the advantages are not obvious .
  4. 500 For a single table with more than 10000, partition or sub table should be considered , This scenario is not considered here .
  5. Performance comparison between index join and hash join , We can't generalize who performs better , It depends on the specific table structure and the amount of data . This point has nothing to do with this article , But there are also discussions in the following chapters .

1.3.2 Resource consumption statistics

During the test , Yes CPU And memory consumption for simple statistics , give the result as follows :

project mysql8 mysql5
In the process of writing millions of data in bulk CPU Usage rate (%) 90 70
In the process of various queries CPU Usage rate (%) 100 100
mysql Memory usage after container restart (M) 341.2 205.9
After various operations mysql Container memory usage increases (M) 130 110

The preliminary conclusion can be drawn :

  1. MySQL8 The memory usage of is higher than MySQL5.
  2. When writing data ,MySQL8 More is needed CPU resources .

In short ,MySQL8 Than MySQL5 More consumption CPU And memory resources .

Two 、 Performance test environment

Used in this test docker Mirror image , Two of them have been launched locally mysql Containers , There are no resource constraints , There is no special performance optimization configuration .

  • MySQL5 edition : 5.7.28
  • MySQL8 edition : 8.0.22
  • The installation files : mysql Official docker Mirror image
  • Docker The host machine OS : Linux Mint 19.1 Tessa
  • Docker The host machine CPU : Intel(R) Core(TM) i7-8550U CPU @ 1.80GHz 4 core 8 process
  • Docker Host memory : 32G
  • Docker Host disk : SSD
  • MySQL5 To configure :
[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 To configure :
[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 Container resource limit : unlimited

3、 ... and 、 Test data

3.1 DDL

Respectively in MySQL5 And MySQL8 Create the following database and table in the instance of :

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 ' The order no. ',
  `custom_number` varchar(20) NOT NULL COMMENT ' Customer number ',
  `product_number` varchar(20) NOT NULL COMMENT ' Product id ',
  `warehouse_number` varchar(20) NOT NULL COMMENT ' Warehouse number ',
  `ord_status` tinyint NOT NULL COMMENT ' The order status ',
  `order_time` datetime NOT NULL COMMENT ' Order time ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_order_unique01` (`ord_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = ' The order sheet ';

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 ' Customer number ',
  `custom_name` varchar(50) NOT NULL COMMENT ' Name of customer ',
  `custom_phone` varchar(20) NOT NULL COMMENT ' Customer's mobile phone number ',
  `custom_address` varchar(200) NOT NULL COMMENT ' Customer address ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_custom_unique01` (`custom_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = ' Customer list ';

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 id ',
  `product_name` varchar(50) NOT NULL COMMENT ' Name of commodity ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = ' Commodity list ';

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 number ',
  `warehouse_name` varchar(50) NOT NULL COMMENT ' Warehouse name ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = ' Warehouse table ';

3.2 Test data

This paper develops a simple java The application is used to import data , And perform a partial query operation .

Because most application development uses JDBC drive , and MySQL8 be relative to MySQL5 It also provides a brand new driver package . So we also need to consider the impact of different versions of driver packages .

Test program code can be from github or gitee Pull it by yourself :

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

or

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

function mysql-test Precautions for :

  1. Use openJDK11.
  2. test mysql8 or mysql5 when , take pom Medium mysql-connector-java Change the dependent version to the corresponding version , And then MySQLPerformanceTest Medium JDBC_URL,JDBC_USER And JDBC_PWD Set to the corresponding value . Be careful not to forget to give JDBC_URL Add the parameters of rewriteBatchedStatements=true, If this parameter is missing ,PreparedStatement Of executeBatch It will not work .
  3. The program generates new random data each time and imports it into the database .( It will be executed automatically before importing truncate Truncate related tables .)
  4. In addition to importing data , We also performed a full table query of four tables , And three inline queries .
  5. The program counts JDBC Connect , Batch insert , The time consumed by full table queries and inline queries .( The specific results are shown in the following chapters )

Four 、 Performance testing

Respectively for MySQL8 and MySQL5 The following performance tests have been carried out , The statistical results are as follows :

4.1 JDBC Connect

according to mysql-test Program test results ,MySQL8 and MySQL5 Of JDBC The connection time is basically the same .

  1. MySQL8 Of JDBC The driver package version is 8.0.22, Corresponding Driver Class yes com.mysql.cj.jdbc.Driver.
  2. MySQL5 Of JDBC The driver package version is 5.1.47, Corresponding Driver Class yes com.mysql.jdbc.Driver.
Some sources say MySQL8 If you use 5.X Of JDBC The driver will have performance problems . There's no test case here , Normally , Applications should also be upgraded JDBC drive , Otherwise, there will be a warning .

4.2 Large tables write

Reference resources mysql-test programmatic insertOrder Method , towards tb_order The table inserts in batches 100 Ten thousand data , Every time 1000 Insert the bar once .

  • mysql8 The average time taken : 33389 millisecond ,CPU Usage rate in 90% Up and down .
  • mysql5 The average time taken : 23446 millisecond ,CPU Usage rate in 70% Up and down .

You can see ,mysql8 When writing data , It will consume more CPU, It takes a little more time .

MySQL8 You may need to optimize the performance related configuration .

4.3 Big watch scan

Reference resources mysql-test programmatic selectOrders Method , To a piece of 100 Ten thousand data table tb_order An unconditional query was made ,MySQL Did a full scan ,mysql8 And mysql5 The implementation plan is the same .

  • mysql8 The average time taken : 1182 millisecond ,CPU Usage rate in 100% Up and down .
  • mysql5 The average time taken : 1311 millisecond ,CPU Usage rate in 100% Up and down .

You can see , Both take time and CPU The consumption is basically the same ,MySQL8 A little bit more time-consuming .

4.4 Index query

Is a table tb_order Create a normal index , And use it in an equivalent query .

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

mysql5 In the implementation of :

--  It takes about 0.04 second 
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 In the implementation of :

--  It takes about 0.05 second 
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)

so , For normal index queries ,mysql5 And mysql8 The performance is basically the same .

4.5 Index join

Reference resources mysql-test programmatic selectOrderJoinCustom Method , To a piece of 100 Ten thousand data table and one 10 Ten thousand data table for connection query , A unique index is created on the join field . here , No matter what MySQL8 still MySQL5, The optimizer will choose the index join strategy .

  • mysql8 The average time taken : 3335 millisecond ,CPU Usage rate in 100% Up and down .
  • mysql5 The average time taken : 2860 millisecond ,CPU Usage rate in 100% Up and down .

You can see , both CPU The consumption is basically the same , but MySQL8 It takes a little more time than MySQL5.

The execution plan is consistent , The table structure is consistent with the amount of data ,MySQL8 But slower , Still need to do some optimization on performance related configuration .

Look at the implementation plans of both , Both use index joins : take tb_order As the main table , Traverse each record of its result set , Then use the unique index on the join field tb_custom_unique01 From the table tb_custom Find the corresponding record in . namely ,Nested loop + eq_ref.

mysql8 Implementation plan of :

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 sentence ] yes MySQL8.0.21 Version of the new syntax , You can see some additional detailed execution plan information .

mysql5 Implementation plan of :

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 No index join

Reference resources mysql-test programmatic selectOrderJoinProduct Methods and selectOrderJoinWarehouse Method , Here, the following two cases of data volume are tested without index connection :

  • 100 Ten thousand records and 1 Ten thousand records are linked to query , The join field has no index . Corresponding selectOrderJoinProduct Method , chapter 1.3.1 Time consuming comparison Medium No index join 1.
  • 100 Ten thousand records and 100 Record table join query , The join field has no index . Corresponding selectOrderJoinWarehouse Method , chapter 1.3.1 Time consuming comparison Medium No index join 2.

here MySQL8 The performance advantage of :

  • 100 Wan Lian 1 ten thousand ,mysql8 The average time taken : 2029 millisecond ,CPU Usage rate in 100% Up and down .
  • 100 Wan Lian 1 ten thousand ,mysql5 The average time taken : 1771556 millisecond ,CPU Usage rate in 100% Up and down .
  • 100 Wan Lian 100,mysql8 The average time taken : 1583 millisecond ,CPU Usage rate in 100% Up and down .
  • 100 Wan Lian 100,mysql5 The average time taken : 17042 millisecond ,CPU Usage rate in 100% Up and down .

Why the join field is not indexed ,MySQL8 That's a huge advantage ? This is it. MySQL8 Start to support the hash connection .

selectOrderJoinProduct stay mysql8 Implementation plan of :

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 stay mysql5 Implementation plan of :

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)

We can see clearly that ,MySQL8 It uses hash join, and MySQL5 It uses Block Nested Loop, Block nesting loop ,BNL, The strategy starts with MySQL 8.0.20 Start not using .

hash join It is to make the data set of the smaller table into a hash data set , Then traverse the data set of the larger table , For every record , According to the join field, the corresponding records of the small table can be obtained directly from the hash data set . Its time complexity is O(m+n),m And n It is the data volume of large table and small table respectively .

BNL It's a double nested loop , The small table is usually used as the main table , Traversing its dataset , For each record, traverse the large table data set to find the corresponding record . Its time complexity is O(m*n).

Even if the two joined tables have filter conditions on other non join fields , And there's an index to use , Most of the time it's still hash join More efficient .

4.7 Independent subquery

stay MySQL To perform the following use IN Independent subqueries of , And look at its execution plan :

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;
Pay attention to check the execution plan after , Do it immediately show warnings;, Or you can't see semi join Semi join optimization .

View the execution results ,MySQL8 Great advantage . Look at the execution plan and you'll see , The reason is the use of hash connections .

  • mysql8 Time consuming : 0.84 second .
  • mysql5 Time consuming : 14.69 second .

mysql5 And its implementation plan :

-- 14.69 second 
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)
You can see , For the use of IN Independent subqueries of ,MySQL5 I chose Semi-join Half connected and Materialization Optimization strategy of materialization , Change subquery to semi join , And materialize it into a temporary table .

But it doesn't mean that if we do the semi join optimization, it will be faster , The optimizer uses specific table statistics ( Table structure and table data amount, etc ) Estimate and compare different optimization strategies , Choose a strategy that estimates the best performance .

At the same time, we should pay attention to , although IN The statement has been optimized to some extent , but tb_order The connection to the materialized temporary table is still Block Nested Loop, The main reason why the sentence is still slow is this .

mysql8 And its implementation plan :

-- 0.84 second 
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 Also made a semi connection semi join And materialization MATERIALIZED Optimize , But no longer used BNL, It's changed to hash join.

4.8 Associated subquery

stay MySQL To perform the following use EXISTS Associated subquery of , And look at its execution plan :

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;
Pay attention to check the execution plan after , Do it immediately show warnings;, Or you can't see semi join Semi join optimization .

View the execution results ,MySQL8 Great advantage . Look at the execution plan and you'll see , The main reason is that EXISTS Clause to carry on Half a connection + Physicochemical After optimization, hash connection can be used .

  • mysql8 Time consuming : 0.83 second .
  • mysql5 Time consuming : 18.02 second .

mysql5 Implementation results and implementation plan in :

-- 18.02 second +
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)

You can see , For the use of EXISTS Associated subquery of ,MySQL5 Didn't do Semi-join Materialization Optimize , comparison IN Sentence performance is slightly worse than .

mysql8 Implementation results and implementation plan in :

-- 0.83 second +
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)
Performance comparison mysql5 There's a big boost , But we have to pay attention to , The main reason for the performance improvement of this case is due to semi join optimization , To be able to use hash join 了 .

4.9 Reverse sort

MySQL8 Real support for creating inverted index , Instead of pretending to create an inverted index as before , But it's actually a positive index . This improves performance in some scenarios .

For example, a case like this , Yes tb_order Table query , Use custom_number And product_number Sort , among product_number It needs to be in reverse order . Normally , The following index should be created :

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

But the same index , stay MySQL8 Enter into force , Effectively improves performance ; And in the MySQL5 It doesn't work , Performance is still not high .

  • Million data mixed sort in mysql8 Time consuming : 0.44 second .
  • Million data mixed sort in mysql5 Time consuming : 1.34 second .

mysql5 In the implementation of :

--  Delete reverse index 
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 an inverted index , It's also a composite index , Partial ascending order , Partial descending 
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)

--  Inquire about 100 Ten thousand pieces of data need  1.34 second 
mysql> SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;
  1. Before creating an inverted index , Use filesort, Performance is usually better than using index Be low .
  2. After creating an inverted index , Only positive order fields use index, The reverse part is still used filesort, because MySQL5 The reverse index of is false .

mysql8 In the implementation of :

--  Delete reverse index 
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 an inverted index 
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)

--  Inquire about 100 Ten thousand pieces of data need  0.44 second 
mysql> SELECT custom_number, product_number from tb_order order by custom_number, product_number DESC ;
  1. Before creating an inverted index , Use filesort, Performance is usually better than using index Be low .
  2. After creating an inverted index , All use index, The reverse index works .

4.10 Performance comparison between index join and hash join

Now we know that , Hash join only works when there is no index on the join field , In most business scenarios , There are various indexes on the join fields , At this time Mysql It uses Index join , namely , Traversing the main table data result set , For every record , Use the index to find the secondary table result set . namely ,Nested Loop + Indexes . Be careful , This is not Block Nested LoopBNL Block nesting loop ,BNL It was before Mysql Join strategy when there is no index on the join field .

at present mysql In the case of an index on the join field , Index join is used by default . But that's not to say that index joins must be faster than hash joins . It depends on the amount of data and the table structure .

<!-- ### 4.10.1 MySQL8 Hash join is sometimes more than MySQL5 The index of the link is fast
mysql5 Performance in :

-- tb_order Yes 100 All the data ,tb_product Yes 1 All the data , It takes about half an hour 
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)


--  Add unique index 
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 second 
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 Performance in :

-- tb_order Yes 100 All the data ,tb_product Yes 1 All the data , Time consuming in 1.3 About seconds 
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 Force on or off hash join

--  Add unique index 
CREATE UNIQUE INDEX `tb_product_unique01` ON `db_mysql_test1`.`tb_product` (`product_number`);

--  When there is an index on the join field , Default not to use hash join
explain SELECT count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);

-- mysql8.0.19 Previous versions should support this mandatory use  hashjoin Writing , But from MySQL 8.0.19 In the beginning, it should not support .
explain SELECT /*+ HASH_JOIN(a,b)  */ count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);

--  Make it mandatory to use hash join Words , The index has to be disabled , You can choose any of the following :
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);
Official documents say it can be used NO_BNL Mandatory go Hash Join, But I tried , If the join field has no index , Leave by default hash join 了 ; If there is an index , Then you have to ignore the index to go hash join.

BNL And NO_BNL It was meant to control whether or not to use block nested loop Block nested loop . Official documents say from 8.0.20 Start ,Mysql No longer in use block nested loop Block nested loop , And then they can't be deleted immediately hint grammar , So it's used to force people to walk hash join 了 ... But it doesn't really work , Because if there is an index, the index join is preferred , If there is no index, the default is hash join. namely , As long as the index works , Set up NO_BNL It's in vain , It's not going to go hash join. I want to be forced to go when there is an index hash join Words , You have to make the index useless .

4.10.2 Performance comparison between hash join and index join

Some cases , Hash connection is slower ; Some cases , Hash connection faster . Can't generalize .

  • Case study 1, Under this test condition ( The big table 100 ten thousand , Watch 1 ten thousand , The join field has a unique index ) Next ,hash join Slightly slower than index join :
-- 0.44 second 
SELECT count(*) FROM tb_order a inner join tb_product b on(a.product_number = b.product_number);

-- 0.88 second 
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)
  • Case study 2, The big table 100 ten thousand , Watch 10 ten thousand , The join field has a unique index ,hash join A little bit faster than index join .
--  Remove other indexes that have a performance bonus 
alter table tb_order drop index tb_order_idx01;

-- tb_custom.custom_number There's a unique index on  tb_custom_unique01, Index join is used by default 
-- 1.52 second 
SELECT count(*) FROM tb_order a inner join tb_custom b on(a.custom_number = b.custom_number);

--  adopt hint grammar NO_JOIN_INDEX Let the statement execute , The target index is no longer used tb_custom_unique01 Do index join 
-- 1.12 second 
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)

5、 ... and 、mysql-test Program test results record

mysql-test The program is for MySQL8 and MySQL5 Several rounds of testing , Statistics are as follows :

Please refer to the program code for specific test items .

5.1 mysql8 test result

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

5.2 mysql5 test result

Rounds 1 2 3 Average
prepareData Time consuming ( millisecond ) 12377 9073 9204 10218
truncateTables Time consuming ( millisecond ) 627 475 451 518
insertOrder Time consuming ( millisecond ) 24152 24193 21994 23446
insertCustom Time consuming ( millisecond ) 1912 1916 1941 1923
insertProduct Time consuming ( millisecond ) 137 147 156 147
insertWarehouse Time consuming ( millisecond ) 4 4 8 5
selectOrders conn Time consuming ( millisecond ) 2 3 3 3
selectOrders executeQuery Time consuming ( millisecond ) 1181 1513 1238 1311
selectOrders ResultSet Traverse Time consuming ( millisecond ) 112 96 106 105
selectOrders Total time ( millisecond ) 1297 1614 1349 1420
selectOrders number 1000000 1000000 1000000 1000000
selectCustoms conn Time consuming ( millisecond ) 2 2 2 2
selectCustoms executeQuery Time consuming ( millisecond ) 82 113 116 104
selectCustoms ResultSet Traverse Time consuming ( millisecond ) 28 23 31 27
selectCustoms Total time ( millisecond ) 114 141 151 135
selectCustoms number 100000 100000 100000 100000
selectProducts conn Time consuming ( millisecond ) 2 3 4 3
selectProducts executeQuery Time consuming ( millisecond ) 13 10 17 13
selectProducts ResultSet Traverse Time consuming ( millisecond ) 3 2 6 4
selectProducts Total time ( millisecond ) 20 15 29 21
selectProducts number 10000 10000 10000 10000
selectWarehouses conn Time consuming ( millisecond ) 2 2 3 2
selectWarehouses executeQuery Time consuming ( millisecond ) 0 0 1 0
selectWarehouses ResultSet Traverse Time consuming ( millisecond ) 0 0 0 0
selectWarehouses Total time ( millisecond ) 4 3 4 4
selectWarehouses number 100 100 100 100
selectOrderJoinCustom conn Time consuming ( millisecond ) 2 2 2 2
selectOrderJoinCustom executeQuery Time consuming ( millisecond ) 3156 2548 2876 2860
selectOrderJoinCustom ResultSet Traverse Time consuming ( millisecond ) 47 52 61 53
selectOrderJoinCustom Total time ( millisecond ) 3207 2604 2941 2917
selectOrderJoinCustom number 1000000 1000000 1000000 1000000
selectOrderJoinProduct conn Time consuming ( millisecond ) 2 2 2 2
selectOrderJoinProduct executeQuery Time consuming ( millisecond ) 1655023 1756847 1902797 1771556
selectOrderJoinProduct ResultSet Traverse Time consuming ( millisecond ) 43 51 222 105
selectOrderJoinProduct Total time ( millisecond ) 1655069 1756902 1903023 1771665
selectOrderJoinProduct number 1000000 1000000 1000000 1000000
selectOrderJoinWarehouse conn Time consuming ( millisecond ) 2 2 7 4
selectOrderJoinWarehouse executeQuery Time consuming ( millisecond ) 16264 16030 18831 17042
selectOrderJoinWarehouse ResultSet Traverse Time consuming ( millisecond ) 35 50 609 231
selectOrderJoinWarehouse Total time ( millisecond ) 16303 16083 19448 17278
selectOrderJoinWarehouse number 1000000 1000000 1000000 1000000
mysql5 Of selectOrderJoinProduct It's too time consuming , Only three rounds have been tested here .

版权声明
本文为[Xiatang baked cake]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/202012012054356865.html

Scroll to Top