MySQL 8.0 Left Join性能远慢于5.6的问题求助

表结构信息 #

MySQL 5.6版本建表语句 #

CREATE TABLE `mkt_coupon_item` (
  `rec_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ps_id` int(10) NOT NULL COMMENT,
  `goods_id` bigint(20) DEFAULT NULL,
  `item_number` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`rec_id`),
  KEY `item_number` (`item_number`),
  KEY `ps_id` (`ps_id`) USING BTREE,
  KEY `idx_goods_id` (`goods_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24596468 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC 

MySQL 8.0版本建表语句 #

CREATE TABLE `mkt_coupon_item` (
  `rec_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ps_id` int NOT NULL,
  `goods_id` bigint DEFAULT NULL,
  `item_number` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`rec_id`),
  KEY `item_number` (`item_number`),
  KEY `ps_id` (`ps_id`) USING BTREE,
  KEY `idx_goods_id` (`goods_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17693330 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

两张表数据量一致，约1400万条记录。

问题SQL语句 #

需求：获取两个ps_id对应的item_number的差异

SELECT
        a.rec_id,
        a.item_number 
FROM
        mkt_coupon_item AS a
        LEFT JOIN ( SELECT ps_id, item_number FROM mkt_coupon_item WHERE ps_id = 6446 ) AS c ON c.item_number = a.item_number 
WHERE
        a.ps_id = 6463
        AND c.ps_id IS NULL 
ORDER BY
        a.rec_id;

执行耗时对比 #

• MySQL 5.6：约2秒
• MySQL 8.0：约35秒

执行计划对比 #

MySQL 5.6执行计划 #

+----+------------+----------------+------+--------------+-------------+---------+-------------------------+-------+----------------------+
| id | select_type | table           | type | possible_keys | key         | key_len | ref                     | rows | Extra                |
+----+------------+----------------+------+--------------+-------------+---------+-------------------------+-------+----------------------+
|  1 | PRIMARY   | a              | ref  | ps_id        | ps_id       | 4       | const                   | 58212 | Using where          |
|  1 | PRIMARY   | <derived2>      | ref  | <auto_key1>  | <auto_key1> | 48      | a.item_number |    10 | Using where; Not exists |
|  2 | DERIVED   | mkt_coupon_item | ref  | ps_id        | ps_id       | 4       | const                   | 62360 | NULL                 |
+----+------------+----------------+------+--------------+-------------+---------+-------------------------+-------+----------------------+

MySQL 8.0执行计划 #

常规执行计划

+----+------------+----------------+----------+------+-----------------+-------------+---------+-------------------------+-------+--------+-------------+
| id | select_type | table           | partitions | type | possible_keys    | key         | key_len | ref                     | rows | filtered | Extra       |
+----+------------+----------------+----------+------+-----------------+-------------+---------+-------------------------+-------+--------+-------------+
|  1 | SIMPLE     | a              | NULL     | ref  | ps_id           | ps_id       | 4       | const                   | 56434 | 100.00 | NULL        |
|  1 | SIMPLE     | mkt_coupon_item | NULL     | ref  | item_number,ps_id | item_number | 63      | a.item_number |    78 |  10.00 | Using where |
+----+------------+----------------+----------+------+-----------------+-------------+---------+-------------------------+-------+--------+-------------+

Tree格式执行计划

explain format=tree SELECT
        a.rec_id,
        a.item_number 
FROM
        mkt_coupon_item AS a
        LEFT JOIN ( SELECT ps_id, item_number FROM mkt_coupon_item WHERE ps_id = 6446 ) AS c ON c.item_number = a.item_number 
WHERE
        a.ps_id = 6463
        AND c.ps_id IS NULL 
ORDER BY
        a.rec_id;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (mkt_coupon_item.ps_id is null)  (cost=4848182.12 rows=440673)
    -> Nested loop left join  (cost=4848182.12 rows=440673)
        -> Index lookup on a using ps_id (ps_id=6463)  (cost=61302.33 rows=56434)
        -> Filter: (mkt_coupon_item.ps_id = 6446)  (cost=77.01 rows=8)
            -> Index lookup on mkt_coupon_item using item_number (item_number=a.item_number)  (cost=77.01 rows=78)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

关键差异点：MySQL 8.0使用Nested loop left join，成本估算远高于5.6版本的派生表连接方式。

目标ps_id记录数统计 #

MySQL 5.6 #

mysql> select count(*) from mkt_coupon_item WHERE ps_id = 6446;
+---------+
| count(*) |
+---------+
|   30159 |
+---------+
1 row in set (0.59 sec)

mysql> select count(*) from mkt_coupon_item WHERE ps_id = 6463;
+---------+
| count(*) |
+---------+
|   30163 |
+---------+
1 row in set (1.12 sec)

MySQL 8.0 #

mysql> select count(*) from mkt_coupon_item WHERE ps_id = 6446;
+---------+
| count(*) |
+---------+
|   30159 |
+---------+
1 row in set (0.55 sec)

mysql> select count(*) from mkt_coupon_item WHERE ps_id = 6463;
+---------+
| count(*) |
+---------+
|   30163 |
+---------+
1 row in set (0.52 sec)

已自行排查两天未找到解决方案，寻求帮助。

内容的提问来源于stack exchange，提问作者tianwei