Description:
# When we select some data in MySQL 8.0.28, we need to scan 370 million lines of data.
# But in MySQL 5.7, we just need 370,000 lines of data.
# a thousand times the difference.
# the query is :
SELECT
distinct a.*
FROM
sp_supplier AS a
LEFT JOIN sp_productline_partner AS b on a.supplier_code = b.supplier_code
LEFT JOIN sp_supplier_payinfo AS d on d.supplier_code = a.supplier_code
LEFT JOIN sp_product_line AS c ON b.productId = c.id
LEFT JOIN sp_supplier_op_log AS e on a.supplier_code = e.supplier_code
LEFT JOIN sp_user_relation AS f on a.supplier_code = f.supplier_code
WHERE
b.is_delete = 0
order by
a.addtime desc,
a.supplier_code desc
LIMIT
0, 20;
# the table structure is in the next module 'How to repeat'
How to repeat:
// show create table sp_supplier
CREATE TABLE `sp_supplier` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`supplier_cate` tinyint(1) DEFAULT NULL COMMENT '?????(1:???2:???3:???)',
`supplier_code` int NOT NULL COMMENT '???code',
`supplier_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '?????',
`supplier_alias` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '?????',
`supplier_source` tinyint(1) NOT NULL COMMENT '???????',
`supplier_degreen` tinyint(1) NOT NULL COMMENT '???????',
`supplier_level` tinyint(1) NOT NULL DEFAULT '1' COMMENT '??(1,2,3,4...)',
`supplier_status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '?????(1:??,2:??,3:?????,4:?????,5:?????,6:??,7:???)',
`product_line` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '???',
`category_id` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '??',
`express_currency` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '??????????????????',
`legalman` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
`regcapital` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '????',
`delivery` int NOT NULL DEFAULT '0' COMMENT '??(?)',
`priceadvantage` decimal(10,3) NOT NULL DEFAULT '0.000' COMMENT '????(%)',
`linkman` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
`link_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
`link_province` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
`link_city` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
`link_district` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
`link_address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???????',
`tel` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??(????)',
`mobile` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??',
`wechat` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
`QQ` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'QQ?',
`aliWW` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??',
`province` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?',
`city` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?',
`district` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?',
`address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
`shop_link` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '????(?json????)',
`taxpayer_date` bigint DEFAULT NULL COMMENT '?????????',
`sku_prefix` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '??????(??Id)',
`limitmoney` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '??,??',
`alertmoney` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '????,??',
`is_signed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`expired` bigint NOT NULL DEFAULT '0' COMMENT '??????',
`limitleft` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '????',
`is_sendsms` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`is_sendemail` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`auditor` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
`audittime` int NOT NULL DEFAULT '0' COMMENT '????',
`auditnote` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
`note` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??????',
`overseaAddr` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '?????????,json???',
`is_taxpayer` tinyint(1) NOT NULL COMMENT '?????(1:?,0:?)',
`is_brand` tinyint(1) DEFAULT NULL COMMENT '?????(1:?,0:?)',
`pur_patterns` tinyint DEFAULT NULL COMMENT '????id',
`adduser` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
`addtime` int NOT NULL DEFAULT '0' COMMENT '????',
`updateuser` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???????',
`updatetime` int NOT NULL DEFAULT '0' COMMENT '????????',
`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`inspect_time` int NOT NULL DEFAULT '0' COMMENT '????????',
`is_insalert` tinyint(1) NOT NULL DEFAULT '0' COMMENT '??????(1:?,0:?)',
`module_id` tinyint NOT NULL DEFAULT '0' COMMENT '????',
`cate_manager` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '????',
`main_manager` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '?????',
`main_manager_info` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '???????',
`second_manager_info` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '???????',
`pur_status` tinyint unsigned NOT NULL COMMENT '????',
`manage_type` int unsigned NOT NULL COMMENT '????',
`supplier_shortName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
`is_entry_date` tinyint(1) DEFAULT NULL COMMENT '??????????(0:?,1:?)',
`supplier_data_submitter` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '????????',
`corporatemobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '??????',
`supplierStore` tinyint DEFAULT '1' COMMENT '1:??? 2:???',
`oalink` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'oa??',
PRIMARY KEY (`id`),
UNIQUE KEY `supplier_code` (`supplier_code`),
KEY `supplier_status` (`supplier_status`) USING BTREE,
KEY `pur_status` (`pur_status`) USING BTREE,
KEY `supplier_name` (`supplier_name`)
) ENGINE=InnoDB AUTO_INCREMENT=49726 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
// show create table sp_productline_partner
CREATE TABLE `sp_productline_partner` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`productId` int NOT NULL COMMENT '??id,sp_product_line?id',
`partnerId` int DEFAULT '0' COMMENT '???id',
`supplier_code` int NOT NULL COMMENT '?????',
`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(0:?,1:?)',
PRIMARY KEY (`id`),
KEY `catid` (`productId`),
KEY `partnerid` (`partnerId`),
KEY `isdel` (`is_delete`),
KEY `sucode` (`supplier_code`)
) ENGINE=InnoDB AUTO_INCREMENT=58173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
// show create table sp_supplier_payinfo
CREATE TABLE `sp_supplier_payinfo` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`supplier_code` int NOT NULL COMMENT '???code',
`entity` smallint DEFAULT NULL COMMENT '??id',
`billtype` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:??,2:??,3:??)',
`paycycle` int NOT NULL DEFAULT '0' COMMENT '????(sp_supplier_paycycle?id)',
`currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
`isDrawback` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`invoicepoint` decimal(4,2) NOT NULL DEFAULT '0.00' COMMENT '????,??',
`compoint` decimal(4,2) NOT NULL DEFAULT '0.00' COMMENT '??????,??',
`paymethod` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????(??)',
`is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????????(1:?,0:?)',
`receiveInfo` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '??????(json)',
`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`is_available` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`idcardInfo` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '?????',
`whiteList` tinyint DEFAULT '0' COMMENT '??? 0:? 1:?',
PRIMARY KEY (`id`),
KEY `code` (`supplier_code`),
KEY `entity` (`entity`) USING BTREE,
KEY `is_delete` (`is_delete`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=117465 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
// show create table sp_product_line
CREATE TABLE `sp_product_line` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '????',
`lineName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '?????',
`lineCode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '?????',
`response` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '???',
`members` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '????',
`type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1soft,2hard',
`is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '????',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
// show create table sp_supplier_op_log
CREATE TABLE `sp_supplier_op_log` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`partnerId` int DEFAULT NULL COMMENT '??????id',
`supplier_code` int NOT NULL COMMENT '?????',
`newstatus` tinyint(1) NOT NULL COMMENT '?????(???)',
`oldstatus` tinyint(1) NOT NULL COMMENT '?????(???)',
`optype` tinyint(1) NOT NULL COMMENT '????(1:?????,2??????,3:??,4:??,5:??)',
`opuser` int NOT NULL COMMENT '???',
`optime` int NOT NULL DEFAULT '0' COMMENT '????',
`opnote` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '????(?????, ????)',
`is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
`change` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '??????',
PRIMARY KEY (`id`),
KEY `optype` (`optype`),
KEY `optime` (`optime`),
KEY `opuser` (`opuser`),
KEY `suppliercode` (`supplier_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1308054 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
// show create table sp_user_relation
CREATE TABLE `sp_user_relation` (
`id` int NOT NULL AUTO_INCREMENT,
`supplier_code` int NOT NULL,
`module_id` tinyint NOT NULL,
`user_id` int NOT NULL,
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`is_special` tinyint NOT NULL DEFAULT '0',
`is_delete` tinyint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `supplier_code` (`supplier_code`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1610881 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
// the count(*) of tables is as follows:
select count(*) from sp_supplier;
+----------+
| count(*) |
+----------+
| 49259 |
+----------+
1 row in set (0.00 sec)
select count(*) from sp_productline_partner;
+----------+
| count(*) |
+----------+
| 57957 |
+----------+
1 row in set (0.01 sec)
select count(*) from sp_supplier_payinfo;
+----------+
| count(*) |
+----------+
| 116058 |
+----------+
1 row in set (0.02 sec)
select count(*) from sp_product_line;
+----------+
| count(*) |
+----------+
| 34 |
+----------+
1 row in set (0.00 sec)
select count(*) from sp_supplier_op_log;
+----------+
| count(*) |
+----------+
| 1237805 |
+----------+
1 row in set (0.12 sec)
select count(*) from sp_user_relation;
+----------+
| count(*) |
+----------+
| 1598289 |
+----------+
1 row in set (0.18 sec)
// the explain of MySQL 5.7 and 8.0 is the same:
mysql> explain SELECT
-> distinct a.*
-> FROM
-> sp_supplier AS a
-> LEFT JOIN sp_productline_partner AS b on a.supplier_code = b.supplier_code
-> LEFT JOIN sp_supplier_payinfo AS d on d.supplier_code = a.supplier_code
-> LEFT JOIN sp_product_line AS c ON b.productId = c.id
-> LEFT JOIN sp_supplier_op_log AS e on a.supplier_code = e.supplier_code
-> LEFT JOIN sp_user_relation AS f on a.supplier_code = f.supplier_code
-> WHERE
-> b.is_delete = 0
-> order by
-> a.addtime desc,
-> a.supplier_code desc
-> LIMIT
-> 0, 20;
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------------------+-------+----------+---------------------------------+
| 1 | SIMPLE | a | NULL | ALL | supplier_code | NULL | NULL | NULL | 48331 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ref | isdel,sucode | sucode | 4 | supplier_new.a.supplier_code | 1 | 50.00 | Using where; Distinct |
| 1 | SIMPLE | d | NULL | ref | code | code | 4 | supplier_new.a.supplier_code | 2 | 100.00 | Using index; Distinct |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | supplier_new.b.productId | 1 | 100.00 | Using index; Distinct |
| 1 | SIMPLE | e | NULL | ref | suppliercode | suppliercode | 4 | supplier_new.a.supplier_code | 25 | 100.00 | Using index; Distinct |
| 1 | SIMPLE | f | NULL | ref | supplier_code | supplier_code | 4 | supplier_new.a.supplier_code | 44 | 100.00 | Using index; Distinct |
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------------------+-------+----------+---------------------------------+
// But in fact, MySQL 8.0 needs scan 370 million lines, but MySQL 5.7 needs 0.37 million lines.
// The optimizer_switch in MySQL 8.0 is:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=off,hash_join=off,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=off
// The optimizer_switch in MySQL 5.7 is:
SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
// we can see, the optimizer_switch is the same in MySQL 8.0 and MySQL 5.7
// and the query plan is the same.
// but in fact, MySQL 8.0 need 1000 times more data than MySQL 5.7
// The trace of MySQL 5.7 and 8.0 is in the .txt file.
// why the query plan is consistent, but the trace is inconsistent
// I think this is a bug of optimizer