mysql的多表关联原理和自己的见解

问题简述

在最近的一次开发中，我遇到一个mysql调优的问题。主要情况我就用测试表简单说明一下。
1.有一张student的学生信息表，如下图。因为是测试表所以字段比较简单，不要介意。本表存放学生的基础信息。

2.重点来了，第二张表是一张lesson表。这张表采用了竖表结构存储字段。用student_id作为关联字段，关联上面的student表。这张表可以理解为每个学生都可以自己选课，每个学生的课程多少不受限制，所以也存在有的学生没有课的情况（纯属瞎说，理解就好）。

3.因为student和lesson是一对多的关系，所以lesson表的数据是student的好几倍。我一开始就用student为主表去关联lesson表，发现查询速度非常非常慢。于是我就上网查询了mysql关联的相关文档。

select * from student s
left join lesson l on s.student_id = s.id
where s.dender = '男'

问题调查

在网上找了一圈，看到别人写的原理分析，我稍微整理了一下。

简单来说，就是student表的每行数据都要到lesson表中查询一遍，如果查不到就补上一个NULL。
那么想要提高查询效率，因为主表的筛选放在最后执行，所以最简单直接的方法就是减少关联lesson表的数据量。但是lesson表又没有可以筛选的条件，那么就换个思路。`

select * from lesson l
left join student s on l.student_id = s.id and s.dender = '男'
-- 这种关联会出现很多lesson找不到对应的student，然后出现null，所以最后加个条件过滤
where s.id is not null

这样查询的效率会一下子提升上去，但也会有另一个问题。如果student没有选任何一门课，那么用lesson作为主表的话，这个学生就永远不会被查出来。所以这个SQL还需要再加上一点数据。

	（
		select * from lesson l
		left join student s on l.student_id = s.id and s.dender = '男'
		-- 这种关联会出现很多lesson找不到对应的student，然后出现null，所以最后加个条件过滤
		where s.id is not null
	）
	union
	（
		select * from student s
		left join （select * from lesson group by student_id） l on s.student_id = s.id
		where l.student_id is null
	）

这样就可以将数据完成的查出来，并且效率特提高了很多。

补充

使用expalin extended 函数可以查看mysql执行步骤。举个例子：

表e为驱动表，表d为被驱动表 。总共需要查询26195112142次；

改为右关联，则驱动表改变，并且只需要执行 1121421次；查询效率提高。

可以使用expalin extended进行sql语句优化的辅助手段。

最后

关于mysql的SQL语句优化还有很多东西需要学习，关于以上的内容其实还存在很多疑问。今天，我就先把调查出来的内容和我用的思路写下来。以后还会SQL的原理进行学习和总结，如果有朋友了解SQL语句实现原理和方法，欢迎留言。