MySQL Execution PlanBlock Nested Loop执行计划异常

问题描述

在排查某次业务慢SQL时，发现SQL执行计划异常，业务表结构为：

CREATE TABLE `xxxx_user_cancellation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册用户id',
  `nickname` varchar(20) NOT NULL DEFAULT '' COMMENT '昵称',
  `user_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '用户类型',
  `identify_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '实名认证ID',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='xxxx用户注销表';


CREATE TABLE `xxxx_register_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `nickname` varchar(20) NOT NULL DEFAULT '' COMMENT '昵称',
  `user_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '用户类型 1.游客 2.房东 3.游客房东',
  `user_phone` varchar(16) NOT NULL DEFAULT '' COMMENT '注册手机号',
  `identify_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '实名认证ID',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_identify_id` (`identify_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=11904740 DEFAULT CHARSET=utf8mb4 COMMENT='xxxx注册用户';

业务查询SQL为：

SELECT *
FROM xxxx_register_user r
LEFT JOIN xxxx_user_cancellation uc
ON r.id = uc.user_id
ORDER BY r.id DESC
LIMIT 0, 20;

对应执行计划为：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4213143
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: uc
   partitions: NULL
         type: ALL
possible_keys: idx_user_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

问题分析

表上xxxx_user_cancellation上存在索引idx_user_id(user_id)，对应关联列xxxx_register_user.id的列类型和列编码都相同，理论上完全可以使用该索引，但由于表xxxx_user_cancellation上仅有1条记录，MySQL查询优化器评估通过JOIN BUFER使用Block Nested Loop算法"能有效提升两表关联的性能"，Block Nested Loop算法需要"buffer"外表一部分数据后再与内表进行JOIN操作：

For each tuple r in R do                             -- 扫描外表R
    store used columns as p from R in Join Buffer    -- 将部分或者全部R的记录保存到Join Buffer中，记为p
    For each tuple s in S do                         -- 扫描内表S
        If p and s satisfy the join condition        -- p与s满足join条件
            Then output the tuple                    -- 返回为结果集

由于使用Block Nested Loop算法，导致查询被转换为：

SELECT * FROM (
    SELECT *
    FROM xxxx_register_user r
    LEFT JOIN xxxx_user_cancellation uc
    ON r.id = uc.user_id
) AS T1
ORDER BY T1.id DESC
LIMIT 0, 20;

由于外表数据量量较大(421万行)，对外表的全表扫描导致整个SQL执行耗时极高。

在当前查询语句中使用LEFT JOIN + LIMIT 0, 20语句，理论上外表最多仅需要扫描20行记录即可返回，伪代码为：

def get_left_join_result():
	join_rows = list()
	for register_user_row in xxxx_register_user(order by id desc):
		cancel_user_rows = get_rows(
			"""
			SELECT * 
			FROM xxxx_user_cancellation uc 
			WHERE uc.user_id = {}
			""".format(register_user_row.id)
		)
		if len(cancel_user_rows)==0:
			cancel_user_rows.append(None)
		for cancel_user_row in cancel_user_rows:
			join_row=(register_user_row,cancel_user_row)
			join_rows.append(join_row)
			if len(join_rows) > 20:
				return join_rows

解决办法

使用强制索引提示，如：

SELECT
*
FROM anban_register_user r
LEFT JOIN anban_user_cancellation uc FORCE INDEX(idx_user_id)
ON r.id = uc.user_id
ORDER BY r.id DESC
LIMIT 0, 20;

使用查询提示关闭Block Nested Loop算法：

SELECT /*+ no_bnl() */ *
FROM anban_register_user r
LEFT JOIN anban_user_cancellation uc
ON r.id = uc.user_id
ORDER BY r.id DESC
LIMIT 0, 20;

使用优化器开关来关闭Block Nested Loop算法：
```
SET GLOBAL optimizer_switch='block_nested_loop=off'
```

MySQL Execution PlanBlock Nested Loop执行计划异常

问题描述

问题分析

解决办法

相关资料