《Mysql應(yīng)用MySQL多表鏈接查詢核心優(yōu)化》要點(diǎn)：
本文介紹了Mysql應(yīng)用MySQL多表鏈接查詢核心優(yōu)化，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

概述MYSQL必讀

在一般的項(xiàng)目開發(fā)中,對(duì)數(shù)據(jù)表的多表查詢是必不可少的.而對(duì)于存在大量數(shù)據(jù)量的情況時(shí)(例如百萬級(jí)數(shù)據(jù)量),我們就需要從數(shù)據(jù)庫(kù)的各個(gè)方面來進(jìn)行優(yōu)化,本文就先從多表查詢開始.其他優(yōu)化操作,后續(xù)另外更新,敬請(qǐng)關(guān)注.MYSQL必讀

數(shù)據(jù)背景MYSQL必讀

現(xiàn)假設(shè)有一個(gè)中學(xué)學(xué)校,學(xué)校中的年級(jí)有一年級(jí)、二年級(jí)、三年級(jí),每個(gè)年級(jí)有兩個(gè)班級(jí).分別為101、102、201、202、301、302.
MYSQL必讀

現(xiàn)在我們要為這個(gè)學(xué)校建立一個(gè)考試成績(jī)統(tǒng)計(jì)系統(tǒng).為此,我們對(duì)數(shù)據(jù)庫(kù)的設(shè)計(jì)畫了如下ER圖：
MYSQL必讀

根據(jù)ER圖,我們?cè)O(shè)計(jì)了數(shù)據(jù)表,結(jié)構(gòu)如下：
class 班級(jí)表：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 學(xué)生表：
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 成績(jī)表：
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必讀

注：關(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ù).
MYSQL必讀

連接(JOIN)簡(jiǎn)介MYSQL必讀

內(nèi)連(INNER JOIN)MYSQL必讀

INNER JOIN 關(guān)鍵字在表中存在至少一個(gè)匹配時(shí)返回行.
MYSQL必讀

我們也用下面的交集維恩圖來描述內(nèi)連操作：

上面的維恩圖只是表達(dá)了一個(gè)有限制情況(即存在JOIN ON),而對(duì)于沒有約束的情況下,其實(shí)就是一個(gè)笛卡爾積運(yùn)算.
MYSQL必讀

*注：**INNER JOIN 與 JOIN 是相同的.一般情況下,在SQL語(yǔ)句中可以省略*INNER關(guān)鍵字.MYSQL必讀

左連接(LEFT JOIN)MYSQL必讀

LEFT JOIN 關(guān)鍵字從左表(table1)返回所有的行,即使右表(table2)中沒有匹配.如果右表中沒有匹配,則結(jié)果為 NULL.
MYSQL必讀

使用維恩圖描述內(nèi)連操作：

對(duì)于上面結(jié)果為 NULL的這一條,通過對(duì)實(shí)際測(cè)試的數(shù)據(jù)表進(jìn)行操作,得到如下的測(cè)試結(jié)果：MYSQL必讀

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

右連接(RIGHT JOIN)MYSQL必讀

RIGHT JOIN 關(guān)鍵字從右表(table2)返回所有的行,即使左表(table1)中沒有匹配.如果左表中沒有匹配,則結(jié)果為 NULL.

注：右連接可以理解成左連接的對(duì)稱互補(bǔ),詳細(xì)說明可參見左連接.MYSQL必讀

全連(FULL JOIN)MYSQL必讀

FULL OUTER JOIN 關(guān)鍵字只要左表(table1)和右表(table2)其中一個(gè)表中存在匹配,則返回行.
MYSQL必讀

FULL OUTER JOIN 關(guān)鍵字結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果.

MYSQL必讀

聯(lián)合(UNION)MYSQL必讀

UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集.
MYSQL必讀

請(qǐng)注意,UNION 內(nèi)部的每個(gè) SELECT 語(yǔ)句必須擁有相同數(shù)量的列.列也必須擁有相似的數(shù)據(jù)類型.同時(shí),每個(gè) SELECT 語(yǔ)句中的列的順序必須相同.MYSQL必讀

MySQL的JOIN實(shí)現(xiàn)原理MYSQL必讀

在MySQL 中,只有一種Join 算法,就是大名鼎鼎的Nested Loop Join,他沒有其他很多數(shù)據(jù)庫(kù)所提供的Hash Join,也沒有Sort Merge Join.顧名思義,Nested Loop Join 實(shí)際上就是通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù),然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個(gè)表中查詢數(shù)據(jù),然后合并結(jié)果.如果還有第三個(gè)參與Join,則再通過前兩個(gè)表的Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù),再一次通過循環(huán)查詢條件到第三個(gè)表中查詢數(shù)據(jù),如此往復(fù).
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　C 《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》
MYSQL必讀

多表查詢實(shí)戰(zhàn)MYSQL必讀

查詢各個(gè)班級(jí)的班長(zhǎng)姓名MYSQL必讀

優(yōu)化分析MYSQL必讀

對(duì)于這個(gè)多表的查詢使用where是可以很好地完成查詢,而查詢的結(jié)果從表面上看,完全沒什么問題,如下：
MYSQL必讀

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

可是,由于我們使用的是where,這個(gè)與內(nèi)連接在有條件限制的情況下是一樣的,其維恩圖也可以一并參考.可是,如果現(xiàn)在我們假設(shè),有一個(gè)新的班級(jí)303,或是這個(gè)303的班級(jí)暫時(shí)還沒有班長(zhǎng).這個(gè)時(shí)候通過where就無法完成查詢了.上面的結(jié)果中就已經(jīng)很好地給出解釋.
MYSQL必讀

這個(gè)時(shí)候,我們就需要通過外連接中的左連接(如果采用右連接,那么相應(yīng)的表位置也要進(jìn)行替換)來進(jìn)行查詢了.在左連的查詢中,因?yàn)槭前恕弊蟊怼暗娜啃?所以對(duì)于未選出班長(zhǎng)的303來說,這個(gè)很有必要.采用左連操作的結(jié)果如下：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 檢查優(yōu)化器MYSQL必讀

通過EXPLAIN我們分別檢查上面WHERE語(yǔ)句和LEFT JOIN的優(yōu)化過程.結(jié)果如下：
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必讀

對(duì)于上面的兩個(gè)結(jié)果,我們可以看到有一個(gè)很明顯的區(qū)別在于Extra.
MYSQL必讀

Using where說明進(jìn)行了where的過濾操作,Using join buffer說明進(jìn)行join緩存.
MYSQL必讀

從上面的結(jié)果中,還可以看到每種情況的兩種查詢操作都是經(jīng)過了全表掃描.而這對(duì)于大量數(shù)據(jù)而言是很不利的.
MYSQL必讀

現(xiàn)在,我們可以為被驅(qū)動(dòng)表的join字段添加索引,再對(duì)其進(jìn)行EXPLAIN檢查.MYSQL必讀

添加索引MYSQL必讀

ALTER TABLE student ADD INDEX index_school_id (school_id);

通過EXPLAIN我們分別檢查上面WHERE語(yǔ)句和LEFT JOIN的優(yōu)化過程.結(jié)果如下：
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必讀

現(xiàn)在,可以很明顯地看出rows列的數(shù)值,在被驅(qū)動(dòng)表處都是1,這大降低了查詢的復(fù)雜度.而且對(duì)于type列,也從一開始的ALL變成了現(xiàn)在的ref.還有一些其他的列也被修改了.
MYSQL必讀

查詢番外MYSQL必讀

根據(jù)學(xué)號(hào)查詢一個(gè)學(xué)生的成績(jī)單
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;

結(jié)果
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必讀

優(yōu)化總結(jié)MYSQL必讀

• 對(duì)于要求全面的結(jié)果時(shí),我們需要使用連接操作(LEFT JOIN / RIGHT JOIN / FULL JOIN)；
• 不要以為使用MySQL的一些連接操作對(duì)查詢有多么大的改善,核心是索引；
• 對(duì)被驅(qū)動(dòng)表的join字段添加索引；

SQL語(yǔ)句表MYSQL必讀

創(chuàng)建數(shù)據(jù)庫(kù)MYSQL必讀

CREATE DATABASE school;

創(chuàng)建數(shù)據(jù)表MYSQL必讀

學(xué)生表MYSQL必讀

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í)名稱 */
PRIMARY KEY (id) /* 學(xué)生表主鍵 */
);
INSERT INTO student(school_id, name, sex, age, class_name) VALUES(100005, 'Bob', 1, 17, 301);

班級(jí)表MYSQL必讀

CREATE TABLE class(
id INT NOT NULL AUTO_INCREMENT, /* 班級(jí)表id */
class_name INT NOT NULL, /* 班級(jí)名稱 */
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);

課程表MYSQL必讀

CREATE TABLE course(
id INT NOT NULL AUTO_INCREMENT, /* 課程表id */
course_name VARCHAR(10) NOT NULL, /* 課程名稱 */
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ī)表MYSQL必讀

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)出MYSQL必讀

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

索引操作MYSQL必讀

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

查詢實(shí)戰(zhàn)MYSQL必讀

查詢所有課程名稱MYSQL必讀

SELECT course_name FROM course GROUP BY course_name;

查詢一個(gè)學(xué)生全部課程MYSQL必讀

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

統(tǒng)計(jì)每個(gè)班級(jí)有多少學(xué)生MYSQL必讀

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

根據(jù)學(xué)號(hào)查詢一個(gè)學(xué)生的成績(jī)單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;

查詢各個(gè)班級(jí)的班長(zhǎng)姓名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應(yīng)用MySQL多表鏈接查詢核心優(yōu)化》是否對(duì)您有啟發(fā)，歡迎查看更多與《Mysql應(yīng)用MySQL多表鏈接查詢核心優(yōu)化》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/13045.html