MySQL查詢優(yōu)化:用子查詢代替非主鍵連接查詢實(shí)例介紹
更新時(shí)間：2013年04月19日 15:08:51 作者：
對(duì)多的兩張表，一般是一張表的外鍵關(guān)聯(lián)到另一個(gè)表的主鍵，接下來(lái)為大家介紹下用子查詢代替非主鍵連接查詢，感興趣的朋友可以參考下哈，希望對(duì)你有所幫助
一對(duì)多的兩張表，一般是一張表的外鍵關(guān)聯(lián)到另一個(gè)表的主鍵。但也有不一般的情況，也就是兩個(gè)表并非通過(guò)其中一個(gè)表的主鍵關(guān)聯(lián)。
例如：
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表是一對(duì)多的關(guān)系，team的tname是唯一的，people的pname也是唯一的，people表中外鍵team_name和team表的tname關(guān)聯(lián)，并不是和主鍵id關(guān)聯(lián)。
（PS：先不說(shuō)這樣的設(shè)計(jì)合不合理，但如果真的攤上這事兒…..很多表的設(shè)計(jì)是每個(gè)表有一個(gè)id和uuid，id作為主鍵，uuid作關(guān)聯(lián)，和上面情況類似）
現(xiàn)在要查詢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;
執(zhí)行一下，可以查詢出結(jié)果，但是如果數(shù)據(jù)量大的情況下，效率很低，執(zhí)行很慢。
對(duì)于這種連接查詢，用子查詢來(lái)代替，查詢結(jié)果相同，但會(huì)效率更高：
SELECT * FROM (SELECT * FROM t_people WHERE pname='xxg' LIMIT 1) p, t_team t WHERE t.tname=p.team_name LIMIT 1;
子查詢中過(guò)濾了大量的數(shù)據(jù)（僅保留一條），再將結(jié)果來(lái)連接查詢，效率會(huì)大大提高。
(PS:另外，使用LIMIT 1也可以提高查詢效率，詳細(xì)：http://blog.csdn.net/xiao__gui/article/details/8726272 )
本人通過(guò)3條SQL測(cè)試兩種查詢方式的效率：
準(zhǔn)備1萬(wàn)條team數(shù)據(jù)，準(zhǔn)備100萬(wàn)條people數(shù)據(jù)。
造數(shù)據(jù)的存儲(chǔ)過(guò)程：
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語(yǔ)句執(zhí)行效率：
連接查詢
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
例如：
復(fù)制代碼 代碼如下:
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表是一對(duì)多的關(guān)系，team的tname是唯一的，people的pname也是唯一的，people表中外鍵team_name和team表的tname關(guān)聯(lián)，并不是和主鍵id關(guān)聯(lián)。
（PS：先不說(shuō)這樣的設(shè)計(jì)合不合理，但如果真的攤上這事兒…..很多表的設(shè)計(jì)是每個(gè)表有一個(gè)id和uuid，id作為主鍵，uuid作關(guān)聯(lián)，和上面情況類似）
現(xiàn)在要查詢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;
執(zhí)行一下，可以查詢出結(jié)果，但是如果數(shù)據(jù)量大的情況下，效率很低，執(zhí)行很慢。
對(duì)于這種連接查詢，用子查詢來(lái)代替，查詢結(jié)果相同，但會(huì)效率更高：
SELECT * FROM (SELECT * FROM t_people WHERE pname='xxg' LIMIT 1) p, t_team t WHERE t.tname=p.team_name LIMIT 1;
子查詢中過(guò)濾了大量的數(shù)據(jù)（僅保留一條），再將結(jié)果來(lái)連接查詢，效率會(huì)大大提高。
(PS:另外，使用LIMIT 1也可以提高查詢效率，詳細(xì)：http://blog.csdn.net/xiao__gui/article/details/8726272 )
本人通過(guò)3條SQL測(cè)試兩種查詢方式的效率：
準(zhǔn)備1萬(wàn)條team數(shù)據(jù)，準(zhǔn)備100萬(wàn)條people數(shù)據(jù)。
造數(shù)據(jù)的存儲(chǔ)過(guò)程：
復(fù)制代碼 代碼如下:
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語(yǔ)句執(zhí)行效率：
連接查詢
復(fù)制代碼 代碼如下:
SELECT * FROM t_team t,t_people p WHERE t.tname=p.team_nameAND p.pname='people20000' LIMIT 1;
Time:12.594 s
連接查詢
復(fù)制代碼 代碼如下:
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
子查詢
復(fù)制代碼 代碼如下:
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
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.12 winx64下載安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-09-09
sql與各個(gè)nosql數(shù)據(jù)庫(kù)使用場(chǎng)景的講解
今天小編就為大家分享一篇關(guān)于sql與各個(gè)nosql數(shù)據(jù)庫(kù)使用場(chǎng)景的講解，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-03-03
復(fù)制記錄,采用自查詢方式2009-12-12
最近在看項(xiàng)目,搭建本地環(huán)境時(shí)候,忘記mysql的密碼,怎么修改密碼,網(wǎng)上找了半天,終于配合著幾個(gè)帖子搞定了,下面這篇文章主要給大家介紹了關(guān)于MySQL修改root密碼的3種實(shí)用方法,需要的朋友可以參考下2023-11-11
ubuntu安裝mysql數(shù)據(jù)庫(kù)方法
ubuntu基于linux的免費(fèi)開源桌面PC操作系統(tǒng)，十分契合英特爾的超極本定位，支持x86、64位和ppc架構(gòu)。這篇文章給大家介紹ubuntu安裝mysql數(shù)據(jù)庫(kù)方法，非常不錯(cuò)，需要的朋友參考下吧2019-08-08
修改了my.cnf，需要重啟MySQL服務(wù),正確重啟MYSQL方法請(qǐng)看下面的文章2013-11-11
這篇文章主要介紹了Mysql索引常見(jiàn)問(wèn)題匯總，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2020-10-10
ktl工具實(shí)現(xiàn)mysql向mysql同步數(shù)據(jù)方法
在本篇內(nèi)容里我們給大家介紹了用ktl工具實(shí)現(xiàn)mysql向mysql同步數(shù)據(jù)的具體步驟，有需要的朋友們跟著學(xué)習(xí)參考下。2019-03-03
這篇文章主要介紹了mysql解壓縮方式安裝和徹底刪除的方法，只有mysql徹底刪除干凈了，才可以裝另外新的版本,需要的朋友可以參考下2018-01-01
MySQL數(shù)據(jù)庫(kù)8——數(shù)據(jù)庫(kù)中函數(shù)的應(yīng)用詳解
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)8——數(shù)據(jù)庫(kù)中函數(shù)的應(yīng)用，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03