优化Using join buffer (Block Nested Loop)

场景1：检查表之间的关联字段有没有添加索引

若右边的表的关联的字段没有索引，整体sql查询也不会走索引

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行sql

EXPLAIN SELECT
	s.id
FROM
	student s
	LEFT JOIN student_grade g ON s.id = g.student_id 
	AND s.class = g.student_class

结果分析

仔细分析发现student_grade的student_id、student_class字段没有添加索引，添加索引即可

场景2：检查表之间的右边的表的数据量

若右边的表的数据量很少，也不会走索引

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `student_class` (`student_class`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

执行sql

EXPLAIN SELECT
	s.id
FROM
	student s
	LEFT JOIN student_grade g ON s.id = g.student_id 
	AND s.class = g.student_class

执行结果

结果分析

仔细检查发现是s.class = g.student_class中关联字段编码一样，然而student_grade的数据量太少，于是在student_grade表增加一条记录，结果走索引了

场景3：检查表之间的关联字段类型是否一致

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条以上的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `student_class` (`student_class`),
  KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

执行sql

EXPLAIN SELECT
	s.id
FROM
	student s
	LEFT JOIN student_grade g ON s.id = g.student_id 
	AND s.class = g.student_class

执行结果

结果分析

仔细检查发现是s.class = g.student_class中关联字段编码不一样，s.class的编码是utf8，g.student_class的编码是utf8mb4
将编码改成一致之后执行sql就走索引了

场景4：添加联合索引

查询的字段和表之间的关联字段之间不是联合索引

表结构

学生表：有10万数据

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `class` (`class`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生评级表：有3条以上的数据

CREATE TABLE `student_grade` (
  `id` bigint(20) NOT NULL COMMENT '序号',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `student_class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`),
  KEY `student_class` (`student_class`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行sql

EXPLAIN
SELECT
	s.id,s.class,s.age,g.create_time
FROM
	student s
	LEFT JOIN student_grade g ON ( s.id = g.student_id AND s.class = g.student_class )

结果分析

查询student的id,class,age之间的字段没有建立联合索引

解决方案

加上联合索引，结果发现走索引了

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '学生id',
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生班级',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`class`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注

以上结果仅供参考，具体根据实际的sql语句进行优化