场景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语句进行优化