MySQL多表链接查询核心优化-巨人网络通讯

MySQL多表链接查询核心优化

概述

在一般的项目开发中，对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时（例如百万级数据量），我们就需要从数据库的各个方面来进行优化，本文就先从多表查询开始。其他优化操作，后续另外更新，敬请关注。

数据背景

现假设有一个中学学校，学校中的年级有一年级、二年级、三年级，每个年级有两个班级。分别为101、102、201、202、301、302.

现在我们要为这个学校建立一个考试成绩统计系统。为此，我们对数据库的设计画了如下ER图：

根据ER图，我们设计了数据表，结构如下：
class 班级表：

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| class_name | int(11) | NO   |     | NULL    |              |
| master_id | int(11) | YES |     | NULL    |                |
| is_key     | int(11) | NO   |     | NULL    |                 |
+------------+---------+------+-----+---------+----------------+

student 学生表：

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| school_id | int(11)     | NO   |     | NULL    |                |
| name       | varchar(30) | NO   |     | NULL    |                |
| sex        | int(11)     | NO   |     | NULL    |                |
| age        | int(11)     | NO   |     | NULL    |                |
| class_name | int(11)     | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

course 课程表：

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(10) | NO   |     | NULL    |                |
| grade        | int(11)     | NO   |     | NULL    |                |
| president_id | int(11)     | YES |     | NULL    |                |
| is_neces     | int(11)     | NO   |     | NULL    |                |
| credit       | int(11)     | NO   |     | NULL    |                |
| class_name   | int(11)     | YES |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

score 成绩表：

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| course_id | int(11) | NO   |     | NULL    |                |
| school_id | int(11) | NO   |     | NULL    |                |
| score     | int(11) | YES |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

注：关于本文的数据库数据大家可以在文章最下方的相关下载中获取。资源链接中有两个版本的数据库，school.sql为初始数据库，school_2.sql为优化后的数据库。

连接（JOIN）简介

内连(INNER JOIN)

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

我们也用下面的交集维恩图来描述内连操作：

上面的维恩图只是表达了一个有限制情况（即存在JOIN ON），而对于没有约束的情况下，其实就是一个笛卡尔积运算。

*注：**INNER JOIN 与 JOIN 是相同的。一般情况下，在SQL语句中可以省略*INNER关键字。

左连接(LEFT JOIN)

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

使用维恩图描述内连操作：

对于上面结果为 NULL的这一条，通过对实际测试的数据表进行操作，得到如下的测试结果：

+------------+-------+
| class_name | name |
+------------+-------+
|        202 | NULL |
|        301 | Bob   |
|        302 | Alice |
+------------+-------+

右连接(RIGHT JOIN)

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

注：右连接可以理解成左连接的对称互补，详细说明可参见左连接。

全连(FULL JOIN)

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

联合(UNION)

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

MySQL的JOIN实现原理

在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库所提供的Hash Join，也没有Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　– 《MySQL性能调优与架构设计》

多表查询实战

查询各个班级的班长姓名

优化分析

对于这个多表的查询使用where是可以很好地完成查询，而查询的结果从表面上看，完全没什么问题，如下：

+------------+---------+
| class_name | name    |
+------------+---------+
|        101 | William |
|        102 | Peter   |
|        201 | Judy    |
|        202 | Polly   |
|        301 | Grace   |
|        302 | Sunny   |
+------------+---------+

可是，由于我们使用的是where，这个与内连接在有条件限制的情况下是一样的，其维恩图也可以一并参考。可是，如果现在我们假设，有一个新的班级303，或是这个303的班级暂时还没有班长。这个时候通过where就无法完成查询了。上面的结果中就已经很好地给出解释。

这个时候，我们就需要通过外连接中的左连接（如果采用右连接，那么相应的表位置也要进行替换）来进行查询了。在左连的查询中，因为是包含了”左表“的全部行，所以对于未选出班长的303来说，这个很有必要。采用左连操作的结果如下：

+------------+---------+
| class_name | name    |
+------------+---------+
|        101 | William |
|        102 | Peter   |
|        201 | Judy    |
|        202 | Polly   |
|        301 | Grace   |
|        302 | Sunny   |
|        303 | NULL    |
+------------+---------+

SQL展示

朴素的WHERE

SELECT cl.class_name, st.name
FROM class cl, student st
WHERE cl.master_id=st.school_id;

INNER JOIN

SELECT cl.class_name, st.name
FROM class cl
JOIN student st
ON cl.master_id=st.school_id;

LEAF JOIN

SELECT cl.class_name, st.name
FROM class cl
LEFT JOIN student st
ON cl.master_id=st.school_id;

RIGHT JOIN

SELECT cl.class_name, st.name
FROM student st
RIGHT JOIN class cl
ON cl.master_id=st.school_id;

利用 EXPLAIN 检查优化器

通过EXPLAIN我们分别检查上面WHERE语句和LEFT JOIN的优化过程。结果如下：

WHERE

+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| 1 | SIMPLE      | cl    | ALL | NULL          | NULL | NULL    | NULL |    7 |                                |
| 1 | SIMPLE      | st    | ALL | NULL          | NULL | NULL    | NULL | 301 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+

LEFT JOIN

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | cl | ALL | NULL | NULL | NULL | NULL | 7 | |
| 1 | SIMPLE | st | ALL | NULL | NULL | NULL | NULL | 301 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

对于上面的两个结果，我们可以看到有一个很明显的区别在于Extra。

Using where说明进行了where的过滤操作，Using join buffer说明进行join缓存。

从上面的结果中，还可以看到每种情况的两种查询操作都是经过了全表扫描。而这对于大量数据而言是很不利的。

现在，我们可以为被驱动表的join字段添加索引，再对其进行EXPLAIN检查。

添加索引

ALTER TABLE student ADD INDEX index_school_id (school_id);

通过EXPLAIN我们分别检查上面WHERE语句和LEFT JOIN的优化过程。结果如下：

WHERE

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref                 | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| 1 | SIMPLE      | cl    | ALL | NULL            | NULL            | NULL    | NULL                |    7 |       |
| 1 | SIMPLE      | st    | ref | index_school_id | index_school_id | 4       | school.cl.master_id |    1 |       |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+

LEFT JOIN

现在，可以很明显地看出rows列的数值，在被驱动表处都是1，这大降低了查询的复杂度。而且对于type列，也从一开始的ALL变成了现在的ref。还有一些其他的列也被修改了。

查询番外

根据学号查询一个学生的成绩单

WHERE 查询

EXPLAIN SELECT st.name, co.course_name, sc.score
FROM student st, score sc, course co
WHERE sc.school_id=st.school_id
AND co.id=sc.course_id
AND st.school_id=100005;

JOIN 查询

EXPLAIN SELECT st.name, co.course_name, sc.score
FROM student st
JOIN score sc ON sc.school_id=st.school_id
JOIN course co ON co.id=sc.course_id
WHERE st.school_id=100005;

结果

+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
| id | select_type | table | type   | possible_keys                         | key                | key_len | ref                 | rows | Extra |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
| 1 | SIMPLE      | st    | ref    | index_school_id                       | index_school_id    | 4       | const               |    1 |       |
| 1 | SIMPLE      | sc    | ref    | index_school_id_sc,index_course_id_sc | index_school_id_sc | 4       | const               |    3 |       |
| 1 | SIMPLE      | co    | eq_ref | PRIMARY                               | PRIMARY            | 4       | school.sc.course_id |    1 |       |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+

优化总结

对于要求全面的结果时，我们需要使用连接操作（LEFT JOIN / RIGHT JOIN / FULL JOIN）；
不要以为使用MySQL的一些连接操作对查询有多么大的改善，核心是索引；
对被驱动表的join字段添加索引；

SQL语句表

创建数据库

CREATE DATABASE school;

创建数据表

学生表

CREATE TABLE student(
id INT NOT NULL AUTO_INCREMENT, /* 学生表id */
school_id INT(11) NOT NULL, /* 学号 */
name VARCHAR(30) NOT NULL, /* 姓名 */
sex INT NOT NULL, /* 性别 */
age INT NOT NULL, /* 年龄 */
class_name INT NOT NULL, /* 班级名称 */
PRIMARY KEY (id) /* 学生表主键 */
);
INSERT INTO student(school_id, name, sex, age, class_name) VALUES(100005, 'Bob', 1, 17, 301);

班级表

CREATE TABLE class(
id INT NOT NULL AUTO_INCREMENT, /* 班级表id */
class_name INT NOT NULL, /* 班级名称 */
master_id INT, /* 班长id */
is_key INT NOT NULL, /* 是否重点班级 */
PRIMARY KEY (id) /* 班级表主键 */
);
INSERT INTO class(class_name, master_id, is_key) VALUES(301, 100001, 1);

课程表

CREATE TABLE course(
id INT NOT NULL AUTO_INCREMENT, /* 课程表id */
course_name VARCHAR(10) NOT NULL, /* 课程名称 */
grade INT NOT NULL, /* 当前课程所属年级 */
president_id INT, /* 课代表id */
is_neces INT NOT NULL, /* 是否必修课 */
credit INT NOT NULL, /* 学分 */
PRIMARY KEY (id) /* 课程表主键 */
);
INSERT INTO course(course_name, grade, president_id, is_neces, credit) VALUES('math', 3, 100214, 1, 4);
ALTER table course ADD column class_name INT;

成绩表

CREATE TABLE score(
id INT NOT NULL AUTO_INCREMENT, /* 成绩表id */
course_id INT NOT NULL, /* 课程id */
school_id INT NOT NULL, /* 学号 */
score INT, /* 考试成绩 */
PRIMARY KEY (id) /* 成绩表主键 */
);
INSERT INTO score(course_id, school_id, score) VALUES(1, 100005, 88);

导入导出

/* 导出数据库 */
MYSQLDUMP -u root -p school > F:/Data/MySQL/school.sql
/* 导入数据库 */
SOURCE /root/upload/school.sql;

索引操作

/* 添加索引 */
ALTER TABLE class ADD INDEX index_master_id (master_id);
/* 删除索引 */
DROP INDEX index_name ON talbe_name;

查询实战

查询所有课程名称

SELECT course_name FROM course GROUP BY course_name;

查询一个学生全部课程

/* 子查询 */
SELECT course_name FROM course WHERE id in (SELECT course_id FROM score WHERE school_id=100005);

统计每个班级有多少学生

SELECT class_name, count(*) FROM student GROUP BY class_name;

根据学号查询一个学生的成绩单

/* WHERE */
SELECT st.name, co.course_name, sc.score
FROM student st, score sc, course co
WHERE sc.school_id=st.school_id
AND co.id=sc.course_id
AND st.school_id=100005;
/* JOIN */
SELECT st.name, co.course_name, sc.score
FROM student st
JOIN score sc ON sc.school_id=st.school_id
JOIN course co ON co.id=sc.course_id
AND st.school_id=100005;

查询各个班级的班长姓名

/* WHERE */
SELECT cl.class_name, st.name
FROM class cl, student st
WHERE cl.master_id=st.school_id;
/* 子查询 */
SELECT st.class_name, st.name
FROM student st
WHERE st.school_id in
(SELECT master_id FROM class);
/* JOIN */
SELECT cl.class_name, st.name
FROM class cl
JOIN student st
ON cl.master_id=st.school_id;
/* LEFT JOIN */
SELECT cl.class_name, st.name
FROM class cl
LEFT JOIN student st
ON cl.master_id=st.school_id;
/* RIGHT JOIN */
SELECT cl.class_name, st.name
FROM student st
RIGHT JOIN class cl
ON cl.master_id=st.school_id;

其他查询

SELECT name, class_name FROM student GROUP BY class_name
UNION ALL
SELECT id, class_name FROM class;

原文链接：http://blog.csdn.net/lemon_tree12138/article/details/50921193

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。

您可能感兴趣的文章:

详解MySQL数据库--多表查询--内连接，外连接，子查询，相关子查询
MySQL左联多表查询where条件写法示例
解析Mysql多表查询的实现
MySQL 多表查询实现分析
MySQL中基本的多表连接查询教程
详解Mysql多表联合查询效率分析及优化
浅谈mysql中多表不关联查询的实现方法
MySQL多表数据记录查询详解
mysql多表连接查询实例讲解
MySQL多表查询实例详解【链接查询、子查询等】

上一篇：Linux下彻底卸载mysql详解
下一篇：CentOS6.5下RPM方式安装mysql5.6.33的详细教程

MySQL多表链接查询核心优化

概述在一般的项目开发中，对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时（例如百万级数据量），我们就需要从数据库的各个方面来进行优化，本文就先从多表查MySQL,多表,链接,查询,核心,...

朗玛移动电销卡

朗玛移动电销卡,朗玛移动电销卡代理,朗玛电销卡办理,朗玛电销卡亿卡惠通讯有限公司...

5G新增站址将利用社会资源解决

近日，中国铁塔方面表示称，5G网络建设将实现共享社会资源，85%的5G新增站址将会利用...

北京电销外呼系统中心（电销外呼系统成都）

今天给各位分享北京电销外呼系统中心的知识，其中也会对电销外呼系统成都进行解释...

兰州电话机器人如何使用（电话机器人使用教程）

本文目录一览：1、智能对话机器人有什么功能?2、小笨电话机器人都有哪些功能呢?这东...

电销软件

3.通话全记录，机器人和客户的沟通内容语音和文字全都有记录，可做销售的客户和销...

如何选400号码

【如何选400号码】怎样选择一个优质的400号码呢？巨人网络通讯为您可以提供一点小技...

乌鲁木齐电销机器人电销系统

2.可以根据企业的业务情况，通过机器人设置智能对话以完成多个对话。最大化真实电...

烟台不封号电销卡靠谱（烟台便宜的电话卡）

本文目录一览：1、怎样使用电销电话卡不会被封?这些技巧需要掌握!2、哪家的防封号电...

使用FSO按文件大小浏览文件目录并进行删除操作

%@ Language=VBScript % %Server.ScriptTimeout=50000% HTML HEAD META NAME=GENERATOR Content=Microsoft Visual Stud...

呼和浩特呼叫中心外呼系统（e呼平台外呼系统）

本文目次一览： 1、呼唤易呼唤核心体系如何样？ 2、电销外呼体系哪个好了？市道上的...

石家庄星美通信的防封电话卡好用吗

石家庄星美通信电销公司用什么电话卡，石家庄星美通信电销卡怎么办理？石家庄星美...

“好医生”商标侵权案终审

好医生药业集团诉平安健康互联网股份有限公司(以下简称平安健康)“平安好医生”商...

沈阳400怎么办理（沈阳400电话办理流程）

沈阳400怎么办理（沈阳400电话办理流程）沈阳400电话已成为人们联系各大企业、机构、...

ai电销机器人实战怎么样（ai智能电销机器人哪家费用低

本文目录一览： 1、用智能AI电话机器人作用好吗？2、电话机器人作用好不好？3、电销...

电销行业的高频通话卡是哪种？

电销行业的高频通话卡是哪种？适用于电话销售的高频通话卡，日呼出不限制。稳定防...

MySQL多表链接查询核心优化

全 部 栏 目

全部栏目