Mysql应用MySQL多表链接查询核心优化

《Mysql应用MySQL多表链接查询核心优化》要点：
本文介绍了Mysql应用MySQL多表链接查询核心优化，希望对您有用。如果有疑问，可以联系我们。

概述MYSQL必读

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

数据背景MYSQL必读

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

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

根据ER图,我们设计了数据表,结构如下：
class 班级表：MYSQL必读

+------------+---------+------+-----+---------+----------------+
| 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    |                   |
+------------+---------+------+-----+---------+----------------+
MYSQL必读

student 学生表：
MYSQL必读

+------------+-------------+------+-----+---------+----------------+
| 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    |                |
+------------+-------------+------+-----+---------+----------------+
MYSQL必读

course 课程表：
MYSQL必读

+--------------+-------------+------+-----+---------+----------------+
| 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    |                |
+--------------+-------------+------+-----+---------+----------------+
MYSQL必读

score 成绩表：
MYSQL必读

+-----------+---------+------+-----+---------+----------------+
| 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    |                |
+-----------+---------+------+-----+---------+----------------+
MYSQL必读

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

连接(JOIN)简介MYSQL必读

内连(INNER JOIN)MYSQL必读

INNER JOIN 关键字在表中存在至少一个匹配时返回行.
MYSQL必读

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

上面的维恩图只是表达了一个有限制情况(即存在JOIN ON),而对于没有约束的情况下,其实就是一个笛卡尔积运算.
MYSQL必读

*注：**INNER JOIN 与 JOIN 是相同的.一般情况下,在SQL语句中可以省略*INNER关键字.MYSQL必读

左连接(LEFT JOIN)MYSQL必读

LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配.如果右表中没有匹配,则结果为 NULL.
MYSQL必读

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

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

+------------+-------+
| class_name | name  |
+------------+-------+
|        202 | NULL  |
|        301 | Bob   |
|        302 | Alice |
+------------+-------+MYSQL必读

右连接(RIGHT JOIN)MYSQL必读

RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配.如果左表中没有匹配,则结果为 NULL.

注：右连接可以理解成左连接的对称互补,详细说明可参见左连接.MYSQL必读

全连(FULL JOIN)MYSQL必读

FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行.
MYSQL必读

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

MYSQL必读

联合(UNION)MYSQL必读

UNION 操作符用于合并两个或多个 SELECT 语句的结果集.
MYSQL必读

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

MySQL的JOIN实现原理MYSQL必读

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

多表查询实战MYSQL必读

查询各个班级的班长姓名MYSQL必读

优化分析MYSQL必读

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

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

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

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

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

SQL展示MYSQL必读

朴素的WHERE
MYSQL必读

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

INNER JOIN
MYSQL必读

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

LEAF JOIN
MYSQL必读

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

RIGHT JOIN
MYSQL必读

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

利用 EXPLAIN 检查优化器MYSQL必读

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

WHERE
MYSQL必读

+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
MYSQL必读

LEFT JOIN
MYSQL必读

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 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 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
MYSQL必读

对于上面的两个结果,我们可以看到有一个很明显的区别在于Extra.
MYSQL必读

Using where说明进行了where的过滤操作,Using join buffer说明进行join缓存.
MYSQL必读

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

现在,我们可以为被驱动表的join字段添加索引,再对其进行EXPLAIN检查.MYSQL必读

添加索引MYSQL必读

ALTER TABLE student ADD INDEX index_school_id (school_id);

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

WHEREMYSQL必读

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| 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 |       |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
MYSQL必读

LEFT JOINMYSQL必读

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| 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 |       |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
MYSQL必读

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

查询番外MYSQL必读

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

WHERE 查询
MYSQL必读

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 查询
MYSQL必读

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;

结果
MYSQL必读

+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
| 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 |       |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
MYSQL必读

优化总结MYSQL必读

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

SQL语句表MYSQL必读

创建数据库MYSQL必读

CREATE DATABASE school;

创建数据表MYSQL必读

学生表MYSQL必读

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);

班级表MYSQL必读

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);

课程表MYSQL必读

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;

成绩表MYSQL必读

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);

导入导出MYSQL必读

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

索引操作MYSQL必读

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

查询实战MYSQL必读

查询所有课程名称MYSQL必读

SELECT course_name FROM course GROUP BY course_name;

查询一个学生全部课程MYSQL必读

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

统计每个班级有多少学生MYSQL必读

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

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

/* 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;

查询各个班级的班长姓名MYSQL必读

/* 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;

其他查询MYSQL必读

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

《Mysql应用MySQL多表链接查询核心优化》是否对您有启发，欢迎查看更多与《Mysql应用MySQL多表链接查询核心优化》相关教程，学精学透。维易PHP学院为您提供精彩教程。

转载请注明本页网址：
http://www.vephp.com/jiaocheng/13045.html