mysql 表关联效率 mysql表关联原理

转载

编程梦想家 2023-09-17 18:15:21

文章标签 mysql 表关联效率 mysql SQL 数据 文章分类 MySQL 数据库

问题简述

在最近的一次开发中，我遇到一个mysql调优的问题。主要情况我就用测试表简单说明一下。

1.有一张student的学生信息表，如下图。因为是测试表所以字段比较简单，不要介意。本表存放学生的基础信息。

mysql 表关联效率 mysql表关联原理_数据

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

mysql 表关联效率 mysql表关联原理_数据_02

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 = '男'

问题调查

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

mysql 表关联效率 mysql表关联原理_mysql 表关联效率_03

简单来说，就是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
	）

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