MySQL查詢優化:用子查詢代替非主鍵連接查詢實例介紹_MySQL教程
推薦:MySQL查詢優化:LIMIT 1避免全表掃描提高查詢效率在某些情況下,如果明知道查詢結果只有一個,SQL語句中使用LIMIT 1會提高查詢效率,感興趣的朋友可以了解下哈,希望對你優化mysql查詢有所幫助
一對多的兩張表,一般是一張表的外鍵關聯到另一個表的主鍵。但也有不一般的情況,也就是兩個表并非通過其中一個表的主鍵關聯。例如:
復制代碼 代碼如下:www.wf0088.com
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數據。
造數據的存儲過程:
復制代碼 代碼如下:www.wf0088.com
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語句執行效率:
連接查詢
復制代碼 代碼如下:www.wf0088.com
SELECT * FROM t_team t,t_people p WHERE t.tname=p.team_nameAND p.pname='people20000' LIMIT 1;
Time:12.594 s
連接查詢
復制代碼 代碼如下:www.wf0088.com
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
子查詢
復制代碼 代碼如下:www.wf0088.com
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高性能備份方案解決數據不間斷訪問備份特點: 1、在大多數情況下,這種方式幾乎算得上是熱備。它無需關閉服務,只需要設置只讀或者類似這樣的限制。 2、支持所有基于本地磁盤的存儲引擎,比如MYISAM、InnoDB和BDB,還支持Solid、PrimeXT和Faction。 3、備份速度最快,因為你只需要拷貝相關的二進制數據
相關MySQL教程:
- MSSQL清空日志刪除日志文件
- 關于數據庫中保留小數位的問題
- 解析mysql與Oracle update的區別
- mysql 導入導出數據庫以及函數、存儲過程的介紹
- MySQL——修改root密碼的4種方法(以windows為例)
- 解決MYSQL出現Can''t create/write to file ''#sql_5c0_0.MYD''的問題
- 深入理解SQL的四種連接-左外連接、右外連接、內連接、全連接
- 解析:內聯,左外聯,右外聯,全連接,交叉連接的區別
- mysql出現“Incorrect key file for table”處理方法
- mysql重裝后出現亂碼設置為utf8可解決
- 淺析一個MYSQL語法(在查詢中使用count)的兼容性問題
- 解析MySQL中INSERT INTO SELECT的使用
- 相關鏈接:
- 教程說明:
MySQL教程-MySQL查詢優化:用子查詢代替非主鍵連接查詢實例介紹。