【原】MySQL連接查詢&索引介紹

貪挽懶月 2022-06-20 發(fā)布于廣東

展開(kāi)全文

一、常見(jiàn)的join查詢：

join太多張表，也會(huì)導(dǎo)致查詢速度變慢。下面就來(lái)分析一下join語(yǔ)句。

1. 執(zhí)行順序：

我們寫(xiě)一條join查詢語(yǔ)句一般都是：

select * from tableA left join tableB on <join condition> where <select condition>

但是MySQL執(zhí)行的時(shí)候，并不是按順序執(zhí)行的，MySQL執(zhí)行sql語(yǔ)句是從from開(kāi)始執(zhí)行的，上面這條語(yǔ)句的執(zhí)行順序是：

from tableA
on <join condition>
left join tableB
where <select condition>

2. 內(nèi)連接inner join：

如圖所示，A和B分別代表兩張表，C是它們共同的部分，inner join查出來(lái)的就是C，即表A和表B的共同部分。

select * from A inner join B on A.key = B.key

3. 左連接left join：

還是上面那張圖，A是左表的獨(dú)有部分，C是AB的共有部分，left join就是左表的獨(dú)有加上兩表的共有，即左表的全部。所以left join查出來(lái)的是A表的全部。

select * from A left join B on A.key = B.key

4. 右連接right join：

右連接就是以右表為主，right join查出來(lái)的就是B的全部。

select * from A right join B on A.key = B.key

5. 只查詢A的獨(dú)占部分：

查詢A的全部就是左連接，那么查詢A獨(dú)占就是：

select * from A left join B on A.key = B.key where B.key is null;

6. 只查詢B的獨(dú)占部分：

select * from A right join B on A.key = B.key where A.key is null;

7. 全連接：

全連接就是查詢 A + B + C，即圖中所有部分：

select * from A full outer join B on A.key = B.key;

8. 查詢A + B的獨(dú)占部分，即排除C：

select * from A full outer join B on A.key = B.key where A.key is null or B.key is null;

二、索引

1. 是什么？

簡(jiǎn)單地說(shuō)，索引是一種數(shù)據(jù)結(jié)構(gòu)，幫助sql高效獲取數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)，可類(lèi)比字典的索引，可理解為排好序的快速查找的數(shù)據(jù)結(jié)構(gòu)。所以索引會(huì)影響where后面的條件以及order by排序。

比如現(xiàn)在要查找col2為91的這條記錄，首先找到34，發(fā)現(xiàn)91比它大，往它的右邊找，找到89，91還是比它大，繼續(xù)往89右邊找，就找到了91。

一般來(lái)說(shuō)索引本身也很大，不會(huì)全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在硬盤(pán)上。

2. 索引的優(yōu)勢(shì)：

提高數(shù)據(jù)檢索效率，降低了數(shù)據(jù)庫(kù)的IO成本；
通過(guò)索引列隊(duì)數(shù)據(jù)進(jìn)行排序，降低了數(shù)據(jù)排序的成本，降低了CPU的消耗。

3. 索引的劣勢(shì)：

索引也要占用空間；
雖然提高了讀數(shù)據(jù)的速度，但是降低了寫(xiě)數(shù)據(jù)的速度，因?yàn)閷?xiě)數(shù)據(jù)的時(shí)候還要維護(hù)索引；
索引只是提高效率的一個(gè)因素，如果有大量的表，就需花時(shí)間研究索引怎么建，才是最優(yōu)

4. 索引分類(lèi)：

假如現(xiàn)有一張user表，有id、name、email等字段。

單值索引：就是用一張表的某一個(gè)字段做索引，比如：create index idx_user_name on user(name)，這就是在name字段上建了一個(gè)單值索引。
唯一索引：索引列的值必須唯一，但允許有空值。
復(fù)合索引：用表的某幾個(gè)字段做索引，比如：create index idx_user_nameEmail on user(name, email)，表示用name和email這兩個(gè)字段來(lái)建立索引。

那么問(wèn)題來(lái)了，我分別在name和email上建單值索引，和建立一個(gè)name和email的復(fù)合索引，有什么區(qū)別？這兩種方式都可以達(dá)到效果，但是復(fù)合索引的開(kāi)銷(xiāo)更小，因此建一個(gè)name和email的復(fù)合索引，比在name和email上分別建單值索引更優(yōu)。

5. 基本語(yǔ)法：

創(chuàng)建：create [unique] index indexName on myTable(columnName(length)); 或者alter myTable add [unique] index indexName on (columnName(length));
刪除：drop index indexName on myTable;
查看：show index from myTable;

6. mysql的索引結(jié)構(gòu)：

MySQL的索引總共有四種，分別是BTree索引、Hash索引、full-text全文索引和R-tree索引，最常用的就是Btree索引。