数据库

MySQL多表链接查询核心优化
数据库 2024/11/5 佚名
3 2

概述

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

数据背景

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

现在我们要为这个学校建立一个考试成绩统计系统。为此，我们对数据库的设计画了如下ER图： MySQL多表链接查询核心优化

根据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 关键字在表中存在至少一个匹配时返回行。

我们也用下面的交集维恩图来描述内连操作：
MySQL多表链接查询核心优化
上面的维恩图只是表达了一个有限制情况（即存在JOIN ON），而对于没有约束的情况下，其实就是一个笛卡尔积运算。

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

左连接(LEFT JOIN)

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

使用维恩图描述内连操作：
MySQL多表链接查询核心优化
对于上面结果为 NULL的这一条，通过对实际测试的数据表进行操作，得到如下的测试结果：

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

右连接(RIGHT JOIN)

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
MySQL多表链接查询核心优化
注：右连接可以理解成左连接的对称互补，详细说明可参见左连接。

全连(FULL JOIN)

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

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
MySQL多表链接查询核心优化

联合(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

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| 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 |       |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+

现在，可以很明显地看出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大数据查询优化,mysql优化查询速度
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
白云城资源网 Copyright www.dyhadc.com

评论“MySQL多表链接查询核心优化”

暂无“MySQL多表链接查询核心优化”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源
一句话新闻
一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5

这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。

一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。

另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com