MySQL多表查詢核心優化

概述

在一般的項目開發中，對數據表的多表查詢是必不可少的。而對於存在大量數據量的情況時（例如百萬級數據量），我們就需要從數據庫的各個方面來進行優化，本文就先從多表查詢開始。其他優化操作，後續另外更新，敬請關注。

版權說明

著作權歸作者所有。
商業轉載請聯系作者獲得授權，非商業轉載請注明出處。
作者：Coding-Naga
發表日期： 2016年3月22日
鏈接：http://blog.csdn.net/lemon_tree12138/article/details/50921193
來源：CSDN
更多內容：分類 >> 數據庫

數據背景

現假設有一個中學學校，學校中的年級有一年級、二年級、三年級，每個年級有兩個班級。分別為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。

<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwPjxjb2RlIGNsYXNzPQ=="hljs objectivec">注：右連接可以理解成左連接的對稱互補，詳細說明可參見左連接。

全連(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

+----+-------------+-------+------+-----------------+-----------------+---------+---------------------+------+-------+
| 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。還有一些其他的列也被修改了。關於type字段的說明可參考這裡。

查詢番外

根據學號查詢一個學生的成績單
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 |       |
+----+-------------+-------+--------+---------------------------------------+--------------------+---------+---------------------+------+-------+

優化總結

Ref

相關資源