MySQL多表鏈接查詢(xún)核心優(yōu)化
概述
在一般的項(xiàng)目開(kāi)發(fā)中，對(duì)數(shù)據(jù)表的多表查詢(xún)是必不可少的。而對(duì)于存在大量數(shù)據(jù)量的情況時(shí)（例如百萬(wàn)級(jí)數(shù)據(jù)量），我們就需要從數(shù)據(jù)庫(kù)的各個(gè)方面來(lái)進(jìn)行優(yōu)化，本文就先從多表查詢(xún)開(kāi)始。其他優(yōu)化操作，后續(xù)另外更新，敬請(qǐng)關(guān)注。
數(shù)據(jù)背景
現(xiàn)假設(shè)有一個(gè)中學(xué)學(xué)校，學(xué)校中的年級(jí)有一年級(jí)、二年級(jí)、三年級(jí)，每個(gè)年級(jí)有兩個(gè)班級(jí)。分別為101、102、201、202、301、302.
現(xiàn)在我們要為這個(gè)學(xué)校建立一個(gè)考試成績(jī)統(tǒng)計(jì)系統(tǒng)。為此，我們對(duì)數(shù)據(jù)庫(kù)的設(shè)計(jì)畫(huà)了如下ER圖： 
根據(jù)ER圖，我們?cè)O(shè)計(jì)了數(shù)據(jù)表，結(jié)構(gòu)如下：
class 班級(jí)表：
+------------+---------+------+-----+---------+----------------+
| 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 學(xué)生表：
+------------+-------------+------+-----+---------+----------------+
| 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 成績(jī)表：
+-----------+---------+------+-----+---------+----------------+
| 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 | |
+-----------+---------+------+-----+---------+----------------+
注：關(guān)于本文的數(shù)據(jù)庫(kù)數(shù)據(jù)大家可以在文章最下方的相關(guān)下載中獲取。資源鏈接中有兩個(gè)版本的數(shù)據(jù)庫(kù)，school.sql為初始數(shù)據(jù)庫(kù)，school_2.sql為優(yōu)化后的數(shù)據(jù)庫(kù)。
連接（JOIN）簡(jiǎn)介
內(nèi)連(INNER JOIN)
INNER JOIN 關(guān)鍵字在表中存在至少一個(gè)匹配時(shí)返回行。
我們也用下面的交集維恩圖來(lái)描述內(nèi)連操作：
上面的維恩圖只是表達(dá)了一個(gè)有限制情況（即存在JOIN ON），而對(duì)于沒(méi)有約束的情況下，其實(shí)就是一個(gè)笛卡爾積運(yùn)算。
*注：**INNER JOIN 與 JOIN 是相同的。一般情況下，在SQL語(yǔ)句中可以省略*INNER關(guān)鍵字。
左連接(LEFT JOIN)
LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒(méi)有匹配。如果右表中沒(méi)有匹配，則結(jié)果為 NULL。
使用維恩圖描述內(nèi)連操作：
對(duì)于上面結(jié)果為 NULL的這一條，通過(guò)對(duì)實(shí)際測(cè)試的數(shù)據(jù)表進(jìn)行操作，得到如下的測(cè)試結(jié)果：
+------------+-------+
| class_name | name |
+------------+-------+
| 202 | NULL |
| 301 | Bob |
| 302 | Alice |
+------------+-------+
右連接(RIGHT JOIN)
RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒(méi)有匹配。如果左表中沒(méi)有匹配，則結(jié)果為 NULL。
注：右連接可以理解成左連接的對(duì)稱(chēng)互補(bǔ)，詳細(xì)說(shuō)明可參見(jiàn)左連接。
全連(FULL JOIN)
FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個(gè)表中存在匹配，則返回行.
FULL OUTER JOIN 關(guān)鍵字結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果。
聯(lián)合(UNION)
UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集。
請(qǐng)注意，UNION 內(nèi)部的每個(gè) SELECT 語(yǔ)句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類(lèi)型。同時(shí)，每個(gè) SELECT 語(yǔ)句中的列的順序必須相同。
MySQL的JOIN實(shí)現(xiàn)原理
在MySQL 中，只有一種Join 算法，就是大名鼎鼎的Nested Loop Join，他沒(méi)有其他很多數(shù)據(jù)庫(kù)所提供的Hash Join，也沒(méi)有Sort Merge Join。顧名思義，Nested Loop Join 實(shí)際上就是通過(guò)驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條的通過(guò)該結(jié)果集中的數(shù)據(jù)作為過(guò)濾條件到下一個(gè)表中查詢(xún)數(shù)據(jù)，然后合并結(jié)果。如果還有第三個(gè)參與Join，則再通過(guò)前兩個(gè)表的Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，再一次通過(guò)循環(huán)查詢(xún)條件到第三個(gè)表中查詢(xún)數(shù)據(jù)，如此往復(fù)。
– 《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》
多表查詢(xún)實(shí)戰(zhàn)
查詢(xún)各個(gè)班級(jí)的班長(zhǎng)姓名
優(yōu)化分析
對(duì)于這個(gè)多表的查詢(xún)使用where是可以很好地完成查詢(xún)，而查詢(xún)的結(jié)果從表面上看，完全沒(méi)什么問(wèn)題，如下：
+------------+---------+
| class_name | name |
+------------+---------+
| 101 | William |
| 102 | Peter |
| 201 | Judy |
| 202 | Polly |
| 301 | Grace |
| 302 | Sunny |
+------------+---------+
可是，由于我們使用的是where，這個(gè)與內(nèi)連接在有條件限制的情況下是一樣的，其維恩圖也可以一并參考?？墒牵绻F(xiàn)在我們假設(shè)，有一個(gè)新的班級(jí)303，或是這個(gè)303的班級(jí)暫時(shí)還沒(méi)有班長(zhǎng)。這個(gè)時(shí)候通過(guò)where就無(wú)法完成查詢(xún)了。上面的結(jié)果中就已經(jīng)很好地給出解釋。
這個(gè)時(shí)候，我們就需要通過(guò)外連接中的左連接（如果采用右連接，那么相應(yīng)的表位置也要進(jìn)行替換）來(lái)進(jìn)行查詢(xún)了。在左連的查詢(xún)中，因?yàn)槭前恕弊蟊怼暗娜啃?，所以?duì)于未選出班長(zhǎng)的303來(lái)說(shuō)，這個(gè)很有必要。采用左連操作的結(jié)果如下：
+------------+---------+
| 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 檢查優(yōu)化器
通過(guò)EXPLAIN我們分別檢查上面WHERE語(yǔ)句和LEFT JOIN的優(yōu)化過(guò)程。結(jié)果如下：
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 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
對(duì)于上面的兩個(gè)結(jié)果，我們可以看到有一個(gè)很明顯的區(qū)別在于Extra。
Using where說(shuō)明進(jìn)行了where的過(guò)濾操作，Using join buffer說(shuō)明進(jìn)行join緩存。
從上面的結(jié)果中，還可以看到每種情況的兩種查詢(xún)操作都是經(jīng)過(guò)了全表掃描。而這對(duì)于大量數(shù)據(jù)而言是很不利的。
現(xiàn)在，我們可以為被驅(qū)動(dòng)表的join字段添加索引，再對(duì)其進(jìn)行EXPLAIN檢查。
添加索引
ALTER TABLE student ADD INDEX index_school_id (school_id);
通過(guò)EXPLAIN我們分別檢查上面WHERE語(yǔ)句和LEFT JOIN的優(yōu)化過(guò)程。結(jié)果如下：
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 | |
+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
現(xiàn)在，可以很明顯地看出rows列的數(shù)值，在被驅(qū)動(dòng)表處都是1，這大降低了查詢(xún)的復(fù)雜度。而且對(duì)于type列，也從一開(kāi)始的ALL變成了現(xiàn)在的ref。還有一些其他的列也被修改了。
查詢(xún)番外
根據(jù)學(xué)號(hào)查詢(xún)一個(gè)學(xué)生的成績(jī)單
WHERE 查詢(xún)
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 查詢(xún)
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;
結(jié)果
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
| 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 | |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+
優(yōu)化總結(jié)
- 對(duì)于要求全面的結(jié)果時(shí)，我們需要使用連接操作（LEFT JOIN / RIGHT JOIN / FULL JOIN）；
- 不要以為使用MySQL的一些連接操作對(duì)查詢(xún)有多么大的改善，核心是索引；
- 對(duì)被驅(qū)動(dòng)表的join字段添加索引；
SQL語(yǔ)句表
創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE school;
創(chuàng)建數(shù)據(jù)表
學(xué)生表
CREATE TABLE student( id INT NOT NULL AUTO_INCREMENT, /* 學(xué)生表id */ school_id INT(11) NOT NULL, /* 學(xué)號(hào) */ name VARCHAR(30) NOT NULL, /* 姓名 */ sex INT NOT NULL, /* 性別 */ age INT NOT NULL, /* 年齡 */ class_name INT NOT NULL, /* 班級(jí)名稱(chēng) */ PRIMARY KEY (id) /* 學(xué)生表主鍵 */ ); INSERT INTO student(school_id, name, sex, age, class_name) VALUES(100005, 'Bob', 1, 17, 301);
班級(jí)表
CREATE TABLE class( id INT NOT NULL AUTO_INCREMENT, /* 班級(jí)表id */ class_name INT NOT NULL, /* 班級(jí)名稱(chēng) */ master_id INT, /* 班長(zhǎng)id */ is_key INT NOT NULL, /* 是否重點(diǎn)班級(jí) */ PRIMARY KEY (id) /* 班級(jí)表主鍵 */ ); 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, /* 課程名稱(chēng) */
grade INT NOT NULL, /* 當(dāng)前課程所屬年級(jí) */
president_id INT, /* 課代表id */
is_neces INT NOT NULL, /* 是否必修課 */
credit INT NOT NULL, /* 學(xué)分 */
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;
成績(jī)表
CREATE TABLE score( id INT NOT NULL AUTO_INCREMENT, /* 成績(jī)表id */ course_id INT NOT NULL, /* 課程id */ school_id INT NOT NULL, /* 學(xué)號(hào) */ score INT, /* 考試成績(jī) */ PRIMARY KEY (id) /* 成績(jī)表主鍵 */ ); INSERT INTO score(course_id, school_id, score) VALUES(1, 100005, 88);
導(dǎo)入導(dǎo)出
/* 導(dǎo)出數(shù)據(jù)庫(kù) */ MYSQLDUMP -u root -p school > F:/Data/MySQL/school.sql /* 導(dǎo)入數(shù)據(jù)庫(kù) */ SOURCE /root/upload/school.sql;
索引操作
/* 添加索引 */ ALTER TABLE class ADD INDEX index_master_id (master_id); /* 刪除索引 */ DROP INDEX index_name ON talbe_name;
查詢(xún)實(shí)戰(zhàn)
查詢(xún)所有課程名稱(chēng)
SELECT course_name FROM course GROUP BY course_name;
查詢(xún)一個(gè)學(xué)生全部課程
/* 子查詢(xún) */ SELECT course_name FROM course WHERE id in (SELECT course_id FROM score WHERE school_id=100005);
統(tǒng)計(jì)每個(gè)班級(jí)有多少學(xué)生
SELECT class_name, count(*) FROM student GROUP BY class_name;
根據(jù)學(xué)號(hào)查詢(xún)一個(gè)學(xué)生的成績(jī)單
/* 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;
查詢(xún)各個(gè)班級(jí)的班長(zhǎng)姓名
/* WHERE */ SELECT cl.class_name, st.name FROM class cl, student st WHERE cl.master_id=st.school_id; /* 子查詢(xún) */ 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;
其他查詢(xún)
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
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
- MySQL中基本的多表連接查詢(xún)教程
- Mysql update多表聯(lián)合更新的方法小結(jié)
- MySQL 多表查詢(xún)實(shí)現(xiàn)分析
- MySQL多表連接查詢(xún)?cè)斀?/a>
- MySQL多表數(shù)據(jù)記錄查詢(xún)?cè)斀?/a>
- MySQL數(shù)據(jù)庫(kù)高級(jí)查詢(xún)和多表查詢(xún)
- mysql多表聯(lián)合查詢(xún)操作實(shí)例分析
- mysql多表連接查詢(xún)實(shí)例講解
- MySQL多表查詢(xún)的具體實(shí)例
- MySQL數(shù)據(jù)庫(kù)多表操作通關(guān)指南(外鍵約束和多表聯(lián)合查詢(xún))
相關(guān)文章
window下mysql 8.0.15 winx64安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了window下mysql 8.0.15 winx64安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-03-03
10個(gè)MySQL性能調(diào)優(yōu)的方法
本文介紹了10個(gè)MySQL性能調(diào)優(yōu)的方法，每個(gè)方法的講解都很細(xì)致，非常實(shí)用，,需要的朋友可以參考下2015-07-07
MySQL學(xué)習(xí)之基礎(chǔ)命令實(shí)操總結(jié)
MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，在WEB應(yīng)用方面MySQL是最好的。本文將為大家詳細(xì)介紹一些MySQL的基礎(chǔ)命令，需要的可以參考一下2022-03-03
這篇文章主要介紹了幾個(gè)MySQL高頻面試題的解答，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2021-01-01
如何將mysql存儲(chǔ)位置遷移到一塊新的磁盤(pán)上
這篇文章主要介紹了如何將mysql存儲(chǔ)位置遷移到一塊新的磁盤(pán)上，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-12-12
這篇文章主要為大家詳細(xì)介紹了MySQL ALTER命令的使用方法，簡(jiǎn)單實(shí)用，感興趣的小伙伴們可以參考一下2016-05-05
MySQL中由load data語(yǔ)句引起死鎖的解決案例
這篇文章主要介紹了MySQL中由load data語(yǔ)句引起死鎖的解決案例,文中講到了InnoDB引擎的數(shù)據(jù)表中一些鎖的機(jī)制,需要的朋友可以參考下2016-01-01
索引是一個(gè)數(shù)據(jù)結(jié)構(gòu),索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序),這篇文章主要給大家介紹了關(guān)于MySQLJSON索引用法的相關(guān)資料,需要的朋友可以參考下2024-01-01
這篇文章主要介紹了MySQL之JSON類(lèi)型字段的使用技巧，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
mysql數(shù)據(jù)庫(kù)遷移至Oracle數(shù)據(jù)庫(kù)
這篇文章主要為大家詳細(xì)介紹了mysql數(shù)據(jù)庫(kù)遷移至Oracle數(shù)據(jù)庫(kù)的方法,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-10-10