MySQL中join語句的基本使用教程及其欄位對效能的影響

fondtiger發表於2021-09-09
MySql

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 語句如下：

?

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

返回查詢結果如下：


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

?

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

CROSS JOIN

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

?

1 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 效果是一樣的：

平板檢視列印？

?

12345 ... FROM table1 INNER JOIN table2 ... FROM table1 CROSS JOIN table2 ... FROM table1 JOIN table2


join的欄位字符集編碼對效能的影響

先來看一下示例程式碼：

建utf-8編碼的表 t1:

?

1234 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少了哪些記錄。

語句很簡單，如下：

?

12345 SELECT SQL_NO_CACHE t1.name, t2.nameFROM t1LEFT JOIN t2 ON t1.name = t2.nameWHERE t2.name IS NULLLIMIT 0 , 30

注意加入 SQL_NO_CACHE ，禁用mysql快取。

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

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

?

12345 SELECT SQL_NO_CACHE t1.name, t3.nameFROM t1LEFT JOIN t3 ON t1.name = t3.nameWHERE t2.name IS NULLLIMIT 0 , 30

phpMyAdmin執行結果：

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

查詢語句解釋：


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/2508/viewspace-2805853/，如需轉載，請註明出處，否則將追究法律責任。

相關文章