MySQL中join語句的基本使用教程及其字段對性能的影響

join語句的基本使用

SQL（MySQL） JOIN 用於根據兩個或多個表中的字段之間的關系，從這些表中得到數據。

JOIN 通常與 ON 關鍵字搭配使用，基本語法如下：

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1 通常稱為左表，table2 稱為右表。ON 關鍵字用於設定匹配條件，用於限定在結果集合中想要哪些行。如果需要指定其他條件，後面可以加上 WHERE 條件 或者 LIMIT 以限制記錄返回數目等。

下面以最常見的兩表連接來說明 MySQL JOIN 的用法，關於多表 JOIN 請參見《MySQL JOIN 多表》。

MySQL JOIN 分類

JOIN 按照功能大致分為如下三類：

1. INNER JOIN（內連接）：取得兩個表中存在連接匹配關系的記錄。
2. LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）並無對應匹配記錄。
3. RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）並無匹配對應記錄。

關於 MySQL FULL JOIN 全連接

MySQL 沒有提供 SQL 標准中的 FULL JOIN（全連接）：兩個表記錄都取出，而不管彼此是否有對應記錄。要解決此問題，可以使用 UNION 關鍵字來合並 LEFT JOIN 與 RIGHT JOIN，達到模擬 FULL JOIN 的目的。

MySQL INNER JOIN

INNER JOIN 用於取得兩個表中存在連接匹配關系的記錄。下面是兩個原始數據表：

article 表中文章的所屬用戶是通過 uid 這個字段與 user 表關聯起來的。通過觀察數據不難發現，對於 uid=3 的用戶，並沒有發表任何文章；而文章中 aid=4 卻無法在 uid 表中找到對應記錄（可能是該用戶被刪除而其所屬的文章卻被保留了下來）。

我們列出所用文章與用戶一一對應的數據。

SELECT … INNER JOIN … ON 語句如下：

SELECT article.aid,article.title,user.username FROM article INNER JOIN user ON article.uid = user.uid

返回查詢結果如下：

對於 INNER JOIN，等同與下面的 SQL 語句：

SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid

CROSS JOIN

CROSS JOIN 即交叉連接，在不指定 ON 條件下：

SELECT article.aid,article.title,user.username FROM article CROSS JOIN user

得到的結果是被連接的兩個數據表的乘積，即笛卡爾積。

實際上，在 MySQL 中（僅限於 MySQL） CROSS JOIN 與 INNER JOIN 的表現是一樣的，在不指定 ON 條件得到的結果都是笛卡爾積，反之取得兩個表完全匹配的結果。

INNER JOIN 與 CROSS JOIN 可以省略 INNER 或 CROSS 關鍵字，因此下面的 SQL 效果是一樣的：

平板視圖打印？

... FROM table1 INNER JOIN table2

... FROM table1 CROSS JOIN table2

... FROM table1 JOIN table2

join的字段字符集編碼對性能的影響

先來看一下示例代碼：

建utf-8編碼的表 t1:

CREATE TABLE IF NOT EXISTS `t1` (
 `name` varchar(50) NOT NULL DEFAULT '',
 KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

隨便插入些數據，數量大一點，後面實驗結果更清晰，偷個懶，構造隨機字符串插入語句

insert into t1(name) 
select concat(
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65)
)

每次執行插入一條記錄，用你熟悉的腳本(python,php,shell等都行)寫個循環，執行一萬次以上。

將該表復制成一個新表t2，刪除一部分數據，1000條左右即可。（推薦使用phpMyAdmin）

再將t2復制為t3，並將字段改為gb2312編碼。

使用一個left join語句，寫一個語句，查出t2/t3比t1少了哪些記錄。

語句很簡單，如下：

SELECT SQL_NO_CACHE t1.name, t2.name
FROM t1
LEFT JOIN t2 ON t1.name = t2.name
WHERE t2.name IS NULL 
LIMIT 0 , 30

注意加入 SQL_NO_CACHE ，禁用mysql緩存。

先看編碼一致的t2表，phpMyAdmin裡執行結果：

顯示行 0 - 29 ( 1,129 總計, 查詢花費 0.0010 秒)
平均耗時大概為0.0010秒

SELECT SQL_NO_CACHE t1.name, t3.name
FROM t1
LEFT JOIN t3 ON t1.name = t3.name
WHERE t2.name IS NULL 
LIMIT 0 , 30

phpMyAdmin執行結果：

顯示行 0 - 29 ( 30 總計, 查詢花費 0.1871 秒)
差兩個數量級！

查詢語句解釋：