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,多表,链接,查询,核心,...

400电话应用主要针对哪些企业

400电话应用主要针对哪些企业 400号码为企业专属客服号码。仅供企事业单位或个体工商...

河南营销智能外呼系统品牌（河南营销智能外呼系统品牌

本篇文章给大家谈谈河南营销智能外呼系统品牌，以及河南营销智能外呼系统品牌名单...

中山智能电话机器人好用吗（中山智能电话机器人好用吗

本篇文章给我们谈谈中山智能电话机器人好用吗，以及中山智能电话机器人好用吗知乎...

传工信部总工程师苏金生接受有关部门调查

3月23日消息，搜狐IT独家获悉，工业和信息化部总工程师苏金生日前接受相关部门调查...

crm带外呼系统方案（CRM呼叫中心方案）

本文目录一览： 1、现公司事务需求，有谁知道有比较好的CRM,ERP，外呼体系2、CRM怎样与...

电销专用卡办理-电销卡哪里买

电销专用卡办理-电销卡哪里买科技有限公司主营企业通信业务，专售电销卡、电销专...

哪款电销机器人比较好（机器人电销和人工电销）

今日给各位共享哪款电销机器人比较好的常识，其间也会对机器人电销和人工电销进行...

邵阳外呼系统线路（邵阳网站呼叫中心联系人）

今日给各位共享邵阳外呼体系线路的常识，其间也会对邵阳网站呼叫中心联络人进行解...

网络电话开发接口

网络电话是企业营销外呼的重要方式，为了解决电销以及客服电话容易被封号的问题，...

技术商业时代的服务外包变革

本报讯昨天，在第十一届软交会举行期间，作为主题论坛之一的2013中国国际软件和信息...

400电话是怎么收费的400电话收费标准 400电话怎么收费

400电话实际上是一种通信工具，可以通过打电话来分担费用，这是一个可以给企业带来...

一百句经典的电话销售语录

文化大革命时期，那时候，毛主席语录是那么的畅销。而如今，销售界的语录，也是非...

河南人工外呼系统（河南呼叫中心系统）

今日给各位共享河南人工外呼体系的常识，其间也会对河南呼叫中心体系进行解说，假...

AI电话机器人系统满足客户关系管理需求

随着科技的飞速发展和新技术的应用，过往的十年是企业信息化全面普及和加深发展的...

物联卡套餐该怎么查询？两种方法让你了解物联卡怎么查

在办理物联卡时，我们一般只关心的是价格、价格，还是价格，对于物联卡的充值、查...

MySQL多表链接查询核心优化

全 部 栏 目

全部栏目