MySQL查詢優化:用子查詢代替非主鍵連接查詢實例介紹

一對多的兩張表，一般是一張表的外鍵關聯到另一個表的主鍵。但也有不一般的情況，也就是兩個表并非通過其中一個表的主鍵關聯。
例如：
代碼如下:
create table t_team
(
tid int primary key,
tname varchar(100)
);
create table t_people
(
pid int primary key,
pname varchar(100),
team_name varchar(100)
);

team表和people表是一對多的關系，team的tname是唯一的，people的pname也是唯一的，people表中外鍵team_name和team表的tname關聯，并不是和主鍵id關聯。
（PS：先不說這樣的設計合不合理，但如果真的攤上這事兒…..很多表的設計是每個表有一個id和uuid，id作為主鍵，uuid作關聯，和上面情況類似）
現在要查詢pname是"xxg"的people和team信息:
SELECT * FROM t_team t,t_people p WHERE t.tname=p.team_name AND p.pname='xxg' LIMIT 1;
或
SELECT * FROM t_team t INNER JOIN t_people p ON t.tname=p.team_name WHERE p.pname='xxg' LIMIT 1;
執行一下，可以查詢出結果，但是如果數據量大的情況下，效率很低，執行很慢。
對于這種連接查詢，用子查詢來代替，查詢結果相同，但會效率更高：
SELECT * FROM (SELECT * FROM t_people WHERE pname='xxg' LIMIT 1) p, t_team t WHERE t.tname=p.team_name LIMIT 1;
子查詢中過濾了大量的數據（僅保留一條），再將結果來連接查詢，效率會大大提高。
(PS:另外，使用LIMIT 1也可以提高查詢效率，詳細：http://blog.csdn.net/xiao__gui/article/details/8726272 )
本人通過3條SQL測試兩種查詢方式的效率：
準備1萬條team數據，準備100萬條people數據。
造數據的存儲過程：
代碼如下:
BEGIN
DECLARE i INT;
START TRANSACTION;
SET i=0;
WHILE i<10000 DO
INSERT INTO t_team VALUES(i+1,CONCAT('team',i+1));
SET i=i+1;
END WHILE;
SET i=0;
WHILE i<1000000 DO
INSERT INTO t_people VALUES(i+1,CONCAT('people',i+1),CONCAT('team',i%10000+1));
SET i=i+1;
END WHILE;
COMMIT;
END

SQL語句執行效率：
連接查詢
代碼如下:
SELECT * FROM t_team t,t_people p WHERE t.tname=p.team_nameAND p.pname='people20000' LIMIT 1;

Time:12.594 s
連接查詢
代碼如下:
SELECT * FROM t_team t INNER JOIN t_peoplep ON t.tname=p.team_name WHERE p.pname='people20000' LIMIT 1;

Time:12.360 s
子查詢
代碼如下:
SELECT * FROM (SELECT * FROM t_people WHEREpname='people20000' LIMIT 1) p, t_team t WHERE t.tname=p.team_name LIMIT 1;

Time:0.016 s

您可能感興趣的文章:

MySQL中基本的多表連接查詢教程

詳解MySQL中的分組查詢與連接查詢語句

mysql連接查詢（左連接，右連接，內連接）

MySQL查詢優化:連接查詢排序淺談

MySQL筆記之連接查詢詳解

MySQL優化之使用連接(join)代替子查詢

經典mysql連接查詢例題

MySQL中對表連接查詢的簡單優化教程

mysql多表連接查詢實例講解

mysql多個left join連接查詢用法分析

MySQL連接查詢實例詳解

Mysql自連接查詢實例詳解

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com