MySQL查詢優(yōu)化:用子查詢代替非主鍵連接查詢實(shí)例介紹
2024-07-24 12:40:52
供稿：網(wǎng)友
一對(duì)多的兩張表，一般是一張表的外鍵關(guān)聯(lián)到另一個(gè)表的主鍵。但也有不一般的情況，也就是兩個(gè)表并非通過其中一個(gè)表的主鍵關(guān)聯(lián)。 例如： 復(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：先不說這樣的設(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ì)于這種連接查詢，用子查詢來代替，查詢結(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; 子查詢中過濾了大量的數(shù)據(jù)（僅保留一條），再將結(jié)果來連接查詢，效率會(huì)大大提高。 (PS:另外，使用LIMIT 1也可以提高查詢效率，詳細(xì)： ) 本人通過3條SQL測(cè)試兩種查詢方式的效率： 準(zhǔn)備1萬條team數(shù)據(jù)，準(zhǔn)備100萬條people數(shù)據(jù)。 造數(shù)據(jù)的存儲(chǔ)過程： 復(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語句執(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
