繼續(xù)這一系列，上篇的簡單查詢優(yōu)化并沒講完，第二點還有查詢注意點以及多表查詢優(yōu)化呢??！
文章結(jié)構(gòu)：（1）多表查詢步步優(yōu)化；（2）查詢編寫的注意點。
本系列：demo下載
（一）MySQL優(yōu)化筆記（一）--庫與表基本操作以及數(shù)據(jù)增刪改
（二）MySQL優(yōu)化筆記（二）--查找優(yōu)化（1）（非索引設(shè)計）
（三）MySQL優(yōu)化筆記（二）--查找優(yōu)化（2）（外連接、多表聯(lián)合查詢以及查詢注意點）
（四） MySQL優(yōu)化筆記（三）--索引的使用、原理和設(shè)計優(yōu)化
（五） MySQL優(yōu)化筆記（四）--表的設(shè)計與優(yōu)化（單表、多表）
（六）MySQL優(yōu)化筆記（五）--數(shù)據(jù)庫存儲引擎
文章目錄：
（1）多表查詢步步優(yōu)化
基本連接方法（內(nèi)連接、外連接以及交叉連接）
- 內(nèi)連接：用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的行（=或>、<）
- 外連接之左連接
- 外連接之右連接
- 外連接之全外連接
- 交叉連接超大型數(shù)據(jù)盡可能盡力不要寫子查詢，使用連接（JOIN）去替換它（基礎(chǔ)講完，講優(yōu)化）
（3）使用聯(lián)合(UNION)來代替手動創(chuàng)建的臨時表
建立索引（下一篇將詳講）
（2）查詢編寫的注意點
大概有9點，詳情見下文。
一、多表查詢步步優(yōu)化：（使用還是之前的數(shù)據(jù)庫的表，商品分類表以及商品詳情表）
這里寫圖片描述
給出的數(shù)據(jù)庫有基本的數(shù)據(jù)框架，剩下的幾個假數(shù)據(jù)我們就自己創(chuàng)建吧。注意此兩表是有外鍵約束的。
這里寫圖片描述
（1）基本連接方法（內(nèi)連接、外連接以及交叉連接）：
一）內(nèi)連接：用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的行（=或>、<）
//意思是：檢索商品分類表和商品表“分類描述”相同的行
select
d.Good_ID ,
d.Classify_ID,
d.Good_Name
from
Commodity_list d
inner join commodity_classification c
on d.Classify_Description=c.Good_kinds_Name
得到的滿足某一條件的是A，B內(nèi)部的數(shù)據(jù)；正因為得到的是內(nèi)部共有數(shù)據(jù)，所以連接方式稱為內(nèi)連接。
這里寫圖片描述
很容器看出是兩者都滿足才查出
這里寫圖片描述
二）外連接之左連接
//意思：查得商品分類表的所有數(shù)據(jù)，以及滿足條件的商品詳情表的數(shù)據(jù)
select
*
from
commodity_classification c
left join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
這里寫圖片描述
可以看到，首先是左表數(shù)據(jù)全部羅列，然后有滿足條件的右表數(shù)據(jù)都會全部羅列出。若兩條右表數(shù)據(jù)對左表一條數(shù)據(jù)，則會用對應(yīng)好的左表數(shù)據(jù)補足作為一條記錄。
這里寫圖片描述
左連接升級：
[left join 或者left outer join(等同于left join)] + [where B.column is null]
//就是只查分類表數(shù)據(jù)，但是減去跟商品詳情表有聯(lián)系的數(shù)據(jù)。
select
*
from
commodity_classification c
left join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
where d.Classify_Description is null
這里寫圖片描述
這里寫圖片描述
三）外連接之右連接
//意思是查得商品詳情表的所有數(shù)據(jù)以及在分類描述相同條件下的商品分類表數(shù)據(jù)
select
*
from
commodity_classification c
right join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
這里寫圖片描述
與左連恰恰相反，首先是右表數(shù)據(jù)全部羅列，然后有滿足條件的左表數(shù)據(jù)都會全部羅列出。若兩條左表數(shù)據(jù)對右表一條數(shù)據(jù)，則會用對應(yīng)好的右表數(shù)據(jù)補足作為一條記錄。
這里寫圖片描述
右連接升級：
//意思：查詢商品詳情表的所有數(shù)據(jù)，但是要減去和商品分類表有聯(lián)系的數(shù)據(jù)
select
*
from
commodity_classification c
right join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
where c.Good_kinds_Name is null
這里寫圖片描述
四）外連接之全外連接：
full join （mysql不支持，但是可以用 left join union right join代替）
select
*
from
commodity_classification c
left join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
union
select
*
from
commodity_classification c
right join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
這種場景下得到的是滿足某一條件的公共記錄，和獨有的記錄
這里寫圖片描述
全外連接升級：
select
*
from
commodity_classification c
left join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
where d.Classify_Description is null
union
select
*
from
commodity_classification c
right join commodity_list d
on d.Classify_Description=c.Good_kinds_Name
where c.Good_kinds_Name is null
這種場景下得到的是A，B中不滿足某一條件的記錄之和.
這里寫圖片描述
全部一起列出：消除重復(fù)項
這里寫圖片描述
五）交叉連接：
交叉聯(lián)接返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉聯(lián)接也稱作笛卡爾積。 FROM 子句中的表或視圖可通過內(nèi)聯(lián)接或完整外部聯(lián)接按任意順序指定；但是，用左或右向外聯(lián)接指定表或視圖時，表或視圖的順序很重要。有關(guān)使用左或右向外聯(lián)接排列表的更多信息，請參見使用外聯(lián)接。
有兩種情況，顯式的和隱式的，不帶ON子句，返回的是兩表的乘積，也叫笛卡爾積。
1. 第一種方式（顯式的交叉連接）：A，B表記錄的排列組合，即笛卡兒積。
//可以看到
select
*
from
commodity_classification c
cross join commodity_list d
這里寫圖片描述
補充：cross join可指定條件 （where）
select
*
from
commodity_classification c
cross join commodity_list d
where c.Good_kinds_Name=d.Classify_Description
相當(dāng)于實現(xiàn)內(nèi)連接功能了。
這里寫圖片描述
2. 第二種方式：（隱式的交叉連接，沒有CROSS JOIN）
就跟正上面的效果一樣的語句啦！
select
*
from
commodity_classification c,
commodity_list d
where c.Good_kinds_Name=d.Classify_Description
交叉連接存在的問題遺漏： 參考此博主此博文
mysql對sql語句的容錯問題，即在sql語句不完全符合書寫建議的情況，mysql會允許這種情況，盡可能解釋它：
1)一般cross join后面加上where條件，但是用cross join+on也是被解釋為cross join+where；
2)一般內(nèi)連接都需要加上on限定條件，如上面場景一；如果不加會被解釋為交叉連接；
3)如果連接表格使用的是逗號，會被解釋為交叉連接；
注：sql標(biāo)準(zhǔn)中還有union join和natural inner join，mysql不支持，而且本身也沒有多大意義，其結(jié)果可以用上面的幾種連接方式得到。
（2）超大型數(shù)據(jù)盡可能盡力不要寫子查詢，使用連接（JOIN）去替換它（基礎(chǔ)講完，講優(yōu)化）：
子查詢：
定義：在一個表表達(dá)中可以調(diào)用另一個表表達(dá)式，這個被調(diào)用的表表達(dá)式叫做子查詢（subquery），也稱作子選擇（subselect）或內(nèi)嵌選擇（inner select）。子查詢的結(jié)果傳遞給調(diào)用它的表表達(dá)式繼續(xù)處理。
//很簡單的意思：就是根據(jù)商品id查商品詳情表，然后用查出來的ID去查商品分類表。
select
*
from
commodity_classification c
where Classify_ID IN(select Classify_ID from commodity_list where Good_ID='tb10025584930')
使用JOIN進行優(yōu)化：
select
*
from
commodity_classification c
left join commodity_list d on d.Classify_ID=c.Classify_ID
where d.Good_ID='tb10025584930'
（3）使用聯(lián)合(UNION)來代替手動創(chuàng)建的臨時表
UNION是會把結(jié)果排序的?。?！
union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合并的一個查詢中（即把兩次或多次查詢結(jié)果合并起來。）。在客戶端的查詢會話結(jié)束的時候，臨時表會被自動刪除，從而保證數(shù)據(jù)庫整齊、高效。使用union來創(chuàng)建查詢的時候，我們只需要用UNION作為關(guān)鍵字把多個select語句連接起來就可以了，要注意的是所有select語句中的字段數(shù)目要想同。
要求：兩次查詢的列數(shù)必須一致
推薦：列的類型可以不一樣，但推薦查詢的每一列，想對應(yīng)的類型以一樣
可以來自多張表的數(shù)據(jù)：多次sql語句取出的列名可以不一致，此時以第一個sql語句的列名為準(zhǔn)。
如果不同的語句中取出的行，有完全相同(這里表示的是每個列的值都相同)，那么union會將相同的行合并，最終只保留一行。也可以這樣理解，union會去掉重復(fù)的行。
如果不想去掉重復(fù)的行，可以使用union all。
如果子句中有order by,limit，需用括號()包起來。推薦放到所有子句之后，即對最終合并的結(jié)果來排序或篩選。
select
Classify_ID,Good_kinds_Name
from
commodity_classification
union
select
Classify_ID,Classify_Description
from
commodity_list
//加條件
(select
Classify_ID,Good_kinds_Name
from
commodity_classification
order by Classify_ID)
union
(select
Classify_ID,Classify_Description
from
commodity_list
order by Good_ID)
//經(jīng)常操作的含義：列出所有在中國和美國的不同的雇員名
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
注意：
1、UNION 結(jié)果集中的列名總是等于第一個 SELECT 語句中的列名
2、UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條 SELECT 語句中的列的順序必須相同
UNION ALL的作用和語法：
默認(rèn)地，UNION 操作符選取不同的值。如果允許重復(fù)的值，請使用 UNION ALL。當(dāng) ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復(fù)行。
select
Classify_ID,Good_kinds_Name
from
commodity_classification
union ALL
select
Classify_ID,Classify_Description
from
commodity_list
不刪除重復(fù)數(shù)據(jù)
這里寫圖片描述
（4）建立索引（下一篇將詳講）
二、查詢編寫的注意點：
（1）對查詢進行優(yōu)化，要盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。（索引的注意點在下篇將詳講）
（2）應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描，如：
//最好不要給數(shù)據(jù)庫留NULL，盡可能的使用 NOT NULL填充數(shù)據(jù)庫.
select id from t where num is null
備注、描述、評論之類的可以設(shè)置為 NULL，其他最好不要使用NULL。
不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時，空間就固定了， 不管是否插入值（NULL也包含在內(nèi)），都是占用 100個字符的空間的，如果是varchar這樣的變長字段， null 不占用空間。
可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：
select id from t where num = 0
（3）in 和 not in 也要慎用，否則會導(dǎo)致全表掃描，如：
select id from t where num in(1,2,3)
對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b)
//用這個去替換
select num from a where exists(select 1 from b where num=a.num)
（4）下面的查詢也將導(dǎo)致全表掃描：
select id from t where name like ‘%abc%’
若要提高效率，可以考慮全文檢索。
（5）盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連 接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。
（6）任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。
（7）盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）。
（8）在Join表的時候使用相當(dāng)類型的例，并將其索引
如果你的應(yīng)用程序有很多 JOIN 查詢，你應(yīng)該確認(rèn)兩個表中Join的字段是被建過索引的。這樣，MySQL內(nèi)部會啟動為你優(yōu)化Join的SQL語句的機制。
而且，這些被用來Join的字段，應(yīng)該是相同的類型的。例如：如果你要把 DECIMAL 字段和一個 INT 字段Join在一起，MySQL就無法使用它們的索引。對于那些STRING類型，還需要有相同的字符集才行。（兩個表的字符集有可能不一樣）
//在state中查找company
SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id"
//兩個 state 字段應(yīng)該是被建過索引的，而且應(yīng)該是相當(dāng)?shù)念愋?，相同的字符?