59、數(shù)據(jù)庫連接（LEFT）操作
??首先定義兩個(gè)表t1和t2
| ID | NAME |
|---|---|
| 1 | aaa |
| 2 | bbb |
| ID | AGE |
|---|---|
| 1 | 20 |
| 3 | 30 |
??內(nèi)連接（inner join）：只顯示符合連接條件的記錄
??select * from t1 inner join t2 on t1.id = t2.id;
| ID | NAME | ID | AGE |
|---|---|---|---|
| 1 | aaa | 1 | 20 |
??外連接分為左外連接募谎、右外連接数冬、全外連接三種
??1）搀庶、左外連接（LEFT JOIN或LEFT OUTER JOIN）：即以左表為基準(zhǔn)，到右表找到匹配的數(shù)據(jù)秸架，找不到匹配的用NULL補(bǔ)齊咆蒿。 最后顯示左表的全部記錄以及右表符合條件的記錄。
??select * from t1 left join t2 on t1.id = t2.id;
| ID | NAME | ID | AGE |
|---|---|---|---|
| 1 | aaa | 1 | 20 |
| 2 | bbb | NULL | NULL |
??2）缭黔、右外連接（RIGHT JOIN 或 RIGHT OUTER JOIN）：即以右表為基準(zhǔn)馏谨，到左表找匹配的數(shù)據(jù)附迷，找不到匹配的用 NULL 補(bǔ)齊喇伯。 顯示右表的全部記錄及左表符合連接條件的記錄。
??select * from t1 right join t2 on t1.id = t2.id;
| ID | NAME | ID | AGE |
|---|---|---|---|
| 1 | aaa | 1 | 20 |
| NULL | NULL | 3 | 30 |
??3）管宵、全外連接（FULL JOIN 或 FULL OUTER JOIN）：除了顯示符合連接條件的記錄外攀甚，在 2 個(gè)表中的其他記錄也顯示出來秋度。
??inner join 和 left join的性能比較钱床。
??從理論上來分析，確實(shí)是 inner join 的性能要好事期，因?yàn)槭沁x出 2 個(gè)表都有的記錄，而 left join 會(huì)出來左邊表的所有記錄绎橘、滿足 on 條件的右邊表的記錄唠倦。
??1稠鼻、在解析階段，左連接是內(nèi)連接的下一階段熙暴，內(nèi)連接結(jié)束后慌盯，把存在于左輸入而未存在于右輸入的集润匙，加回總的結(jié)果集，因此如果少了這一步效率應(yīng)該要高些匠楚。
??2厂财、在編譯的優(yōu)化階段，如果左連接的結(jié)果集和內(nèi)連接一樣時(shí)与斤，左連接查詢會(huì)轉(zhuǎn)換成內(nèi)連接查詢撩穿，即編譯優(yōu)化器認(rèn)為內(nèi)連接要比左連接高效谒撼。
60、分組查詢
60.1抵皱、MySQL對(duì)數(shù)據(jù)表進(jìn)行分組查詢（GROUP BY）
??GROUP BY關(guān)鍵字可以將查詢結(jié)果按照某個(gè)字段或多個(gè)字段進(jìn)行分組。字段中值相等的為一組移盆∥吨停基本的語法格式如下：
??GROUP BY 屬性名 [HAVING 條件表達(dá)式] [WITH ROLLUP]
??屬性名：是指按照該字段的值進(jìn)行分組钮呀。
??HAVING 條件表達(dá)式：用來限制分組后的顯示，符合條件表達(dá)式的結(jié)果將被顯示蚁署。
??WITH ROLLUP：將會(huì)在所有記錄的最后加上一條記錄光戈。加上的這一條記錄是上面所有記錄的總和遂赠。
??GROUP BY關(guān)鍵字可以和GROUP_CONCAT()函數(shù)一起使用。GROUP_CONCAT()函數(shù)會(huì)把每個(gè)分組中指定的字段值都顯示出來筷弦。
??同時(shí)抑诸，GROUP BY關(guān)鍵字通常與集合函數(shù)一起使用蜕乡。集合函數(shù)包括COUNT()函數(shù)、SUM()函數(shù)号醉、AVG()函數(shù)辛块、MAX()函數(shù)和MIN()函數(shù)等憨降。
??COUNT()函數(shù)：用于統(tǒng)計(jì)記錄的條數(shù)该酗。
??SUM()函數(shù)：用于計(jì)算字段的值的總和士嚎。
??AVG()函數(shù)：用于計(jì)算字段的值的平均值莱衩。
??MAX()函數(shù)：用于查詢字段的最大值娇澎。
??MIN()函數(shù)：用于查詢字段的最小值趟庄。
??如果GROUP BY不與上述函數(shù)一起使用，那么查詢結(jié)果就是字段取值的分組情況奋单。字段中取值相同的記錄為一組猫十，但是只顯示該組的第一條記錄拖云。
60.2、單獨(dú)使用GROUP BY關(guān)鍵字進(jìn)行分組
??如果單獨(dú)使用GROUP BY關(guān)鍵字乏苦，查詢結(jié)果只顯示一個(gè)分組的一條記錄尤筐。
??查詢結(jié)果進(jìn)行比較叔磷，GROUP BY關(guān)鍵字只顯示每個(gè)分組的一條記錄。這說明繁疤，GROUP BY關(guān)鍵字單獨(dú)使用時(shí)稠腊，只能查詢出每個(gè)分組的一條記錄鸣哀，這樣做的意義不大我衬。因此饰恕，一般在使用集合函數(shù)時(shí)才使用GROUP BY關(guān)鍵字井仰。
60.3俱恶、GROUP BY關(guān)鍵字與GROUP_CONCAT()函數(shù)一起使用
??GROUP BY關(guān)鍵字與GROUP_CONCAT()函數(shù)一起使用時(shí)，每個(gè)分組中指定的字段值會(huì)全部顯示出來了罪。
60.4捶惜、GROUP BY關(guān)鍵字與集合函數(shù)一起使用
??GROUP BY關(guān)鍵字與集合函數(shù)一起使用時(shí)荔烧，可以通過集合函數(shù)計(jì)算分組中的總記錄鹤竭、最大值、最小值等吝岭。
??提示：通常情況下窜管，GROUP BY關(guān)鍵字與集合函數(shù)一起使用稚机，先使用GROUP BY關(guān)鍵字將記錄分組赖条，然后每組都使用集合函數(shù)進(jìn)行計(jì)算。在統(tǒng)計(jì)時(shí)經(jīng)常需要使用GROUP BY關(guān)鍵字和集合函數(shù)碱茁。
60.5仿贬、GROUP BY關(guān)鍵字與HAVING一起使用
??使用GROUP BY關(guān)鍵字時(shí)，如果加上“HAVING 條件表達(dá)式”蜓氨，則可以限制輸出的結(jié)果语盈。只有符合條件表達(dá)式的結(jié)果才會(huì)顯示缰泡。
??提示：“HAVING 條件表達(dá)式”與“WHERE 條件表達(dá)式”都是用于限制顯示的棘钞。但是宜猜，兩者起作用的地方不一樣。
??WHERE 條件表達(dá)式：作用于表或者視圖绅喉，是表和視圖的查詢條件叫乌。
??HAVING 條件表達(dá)式：作用于分組后的記錄（having只能用于group by）憨奸，用于選擇符合條件的組。
60.6似芝、按照多個(gè)字段進(jìn)行分組
??在MySQL中板甘，還可以按照多個(gè)字段進(jìn)行分組盐类。例如，employee表按照d_id字段和sex字段進(jìn)行分組蝇闭。分組過程中呻引，先按照d_id字段進(jìn)行分組吐咳，遇到d_id字段的值相等的情況時(shí)，再把d_id值相等的記錄按照sex字段進(jìn)行分組童谒。
60.7饥伊、GROUP BY關(guān)鍵字與WITH ROLLUP一起使用
??使用WITH ROLLUP時(shí)，將會(huì)在所有記錄的最后加上一條記錄愉豺。這條記錄是上面所有記錄的總和茫因。
61冻押、數(shù)據(jù)庫查詢優(yōu)化
??1洛巢、使用索引
??應(yīng)盡量避免全表掃描，首先應(yīng)考慮在where及order by箱熬，group by涉及的列上建立索引城须。
??2米苹、優(yōu)化SQL語句
??1）、通過 explain(查詢優(yōu)化神器)用來查看 SQL 語句的執(zhí)行效果良瞧，可以幫助選擇更好的索引和優(yōu)化查詢語句褥蚯，寫出更好的優(yōu)化語句况增。通常我們可以對(duì)比較復(fù)雜的尤其是涉及到多表的 SELECT 語句，把關(guān)鍵字 EXPLAIN 加到前面歧强，查看執(zhí)行計(jì)劃摊册。例如：explain select * from news;
??2）茅特、任何地方都不要使用 select * from t，用具體的字段列表代替“*”饭庞，不要返回用不到的任何字段戒悠。
??3）熬荆、不在索引列做運(yùn)算或者使用函數(shù)。
??4）绸狐、查詢盡可能使用 limit 減少返回的行數(shù)卤恳，減少數(shù)據(jù)傳輸時(shí)間和帶寬浪費(fèi)。
??3寒矿、優(yōu)化數(shù)據(jù)庫對(duì)象
??1）突琳、使用procedure analyse()函數(shù)對(duì)表進(jìn)行分析，該函數(shù)可以對(duì)表中列的數(shù)據(jù)提出優(yōu)化建議符相。能小就用小。表數(shù)據(jù)類型第一個(gè)原則是：使用能正確的表示和存儲(chǔ)數(shù)據(jù)的最短類型啊终。這樣可以減少對(duì)磁盤空間镜豹、內(nèi)存、cpu緩存的使用蓝牲。
??使用方法：select * from 表名 procedure analyse();
??2）趟脂、對(duì)表進(jìn)行拆分可以提高訪問的效率
??垂直拆分：把主鍵和一些列放在一個(gè)表中，然后把主鍵和另外的列放在另外一個(gè)表中例衍。如果一個(gè)表中某些列常用昔期，而另外一些不常用，則可以使用垂直拆分佛玄。
??水平拆分：根據(jù)一列或者多列數(shù)據(jù)的值把數(shù)據(jù)行放在第二個(gè)獨(dú)立的表中硼一。
??3）、創(chuàng)建中間表來提高查詢速度：中間表的結(jié)構(gòu)和原表完全相同梦抢，轉(zhuǎn)移要統(tǒng)計(jì)的數(shù)據(jù)到中間表般贼，然后在中間表上進(jìn)行統(tǒng)計(jì)，得出想要的結(jié)果。
??4具伍、硬件優(yōu)化
??1）翅雏、CPU優(yōu)化，選擇多核和主頻高的CPU人芽；
??2）望几、內(nèi)存優(yōu)化，使用更大的內(nèi)存萤厅，將盡量多的內(nèi)存分配給MYSQL做緩存橄抹；
??3）、磁盤IO優(yōu)化惕味，使用磁盤陣列楼誓，選擇合適的磁盤調(diào)度算法，減少磁盤的尋道時(shí)間名挥。
??5疟羹、MYSQL自身優(yōu)化
??對(duì)Mysql自身的優(yōu)化主要是對(duì)其配置文件my.cnf中的各項(xiàng)參數(shù)進(jìn)行優(yōu)化調(diào)整。如指定MySQL查詢緩沖區(qū)的大小禀倔，指定MySQL允許的最大連接進(jìn)程數(shù)等榄融。
??6、應(yīng)用層面的優(yōu)化
??1）救湖、使用數(shù)據(jù)庫連接池
??2）愧杯、使用查詢緩存，它的作用是存儲(chǔ)select查詢的文本及其相應(yīng)結(jié)果鞋既。如果隨后收到一個(gè)相同的查詢力九，服務(wù)器會(huì)從查詢緩存中直接得到查詢結(jié)果。查詢緩存適用的對(duì)象是更新不頻繁的表邑闺，到表中數(shù)據(jù)更改后跌前，查詢緩存中的相關(guān)條目就會(huì)被清空。
62检吆、大訪問量到數(shù)據(jù)庫時(shí)舒萎，如何優(yōu)化
??1、使用優(yōu)化查詢方法（數(shù)據(jù)庫查詢優(yōu)化）
??2蹭沛、主從復(fù)制臂寝，讀寫分離，負(fù)載均衡
??目前摊灭，大部分的主流關(guān)系型數(shù)據(jù)庫都提供了主從復(fù)制的功能咆贬，通過配置兩臺(tái)（或多臺(tái)）數(shù)據(jù)庫的主從關(guān)系，可以將一臺(tái)數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)更新同步到另一臺(tái)服務(wù)器上帚呼。網(wǎng)站可以利用數(shù)據(jù)庫的這一功能掏缎，實(shí)現(xiàn)數(shù)據(jù)庫的讀寫分離皱蹦，從而改善數(shù)據(jù)庫的負(fù)載壓力。一個(gè)系統(tǒng)的讀操作遠(yuǎn)遠(yuǎn)多于寫操作眷蜈，因此寫操作發(fā)向 master沪哺，讀操作發(fā)向 slaves 進(jìn)行操作（簡(jiǎn)單的輪循算法來決定使用哪個(gè) slave）。
??利用數(shù)據(jù)庫的讀寫分離酌儒，Web 服務(wù)器在寫數(shù)據(jù)的時(shí)候辜妓，訪問主數(shù)據(jù)庫（Master），主數(shù)據(jù)庫通過主從復(fù)制機(jī)制將數(shù)據(jù)更新同步到從數(shù)據(jù)庫（Slave）忌怎，這樣當(dāng) Web 服務(wù)器讀數(shù)據(jù)的時(shí)候籍滴，就可以通過從數(shù)據(jù)庫獲得數(shù)據(jù)。 這一方案使得在大量讀操作的 Web 應(yīng)用可以輕松地讀取數(shù)據(jù)榴啸，而主數(shù)據(jù)庫也只會(huì)承受少量的寫入操作孽惰，還可以實(shí)現(xiàn)數(shù)據(jù)熱備份，可謂是一舉兩得的方案鸥印。
??主從復(fù)制的原理：
??影響 MySQL-A 數(shù)據(jù)庫的操作勋功，在數(shù)據(jù)庫執(zhí)行后，都會(huì)寫入本地的日志系統(tǒng) A 中辅甥。假設(shè)酝润，實(shí)時(shí)的將變化了的日志系統(tǒng)中的數(shù)據(jù)庫事件操作燎竖，通過網(wǎng)絡(luò)發(fā)給 MYSQL-B璃弄。MYSQL-B 收到后，寫入本地日志系統(tǒng) B构回，然后一條條的將數(shù)據(jù)庫事件在數(shù)據(jù)庫中完成夏块。那么，MYSQL-A 的變化纤掸，MYSQL-B 也會(huì)變化脐供，這樣就是所謂的 MYSQL 的復(fù)制。
??在上面的模型中借跪，MYSQL-A 就是主服務(wù)器政己，即 master，MYSQL-B 就是從服務(wù)器掏愁，即slave歇由。
??日志系統(tǒng) A，其實(shí)它是 MYSQL 的日志類型中的二進(jìn)制日志果港，也就是專門用來保存修改數(shù)據(jù)庫表的所有動(dòng)作沦泌，即 bin log⌒谅樱【注意 MYSQL 會(huì)在執(zhí)行語句之后谢谦，釋放鎖之前释牺，寫入二進(jìn)制日志，確保事務(wù)安全】回挽；
??日志系統(tǒng) B没咙，并不是二進(jìn)制日志，由于它是從 MYSQL-A 的二進(jìn)制日志復(fù)制過來的千劈，并不是自己的數(shù)據(jù)庫變化產(chǎn)生的镜撩，有點(diǎn)接力的感覺，稱為中繼日志队塘，即 relay log袁梗。
??可以發(fā)現(xiàn)，通過上面的機(jī)制憔古，可以保證 MYSQL-A 和 MYSQL-B 的數(shù)據(jù)庫數(shù)據(jù)一致遮怜，但是時(shí)間上肯定有延遲，即 MYSQL-B 的數(shù)據(jù)是滯后的鸿市。
??簡(jiǎn)化版：
??mysql 主(稱 master)從(稱 slave)復(fù)制的原理：
??(1)锯梁、master 將數(shù)據(jù)改變記錄到二進(jìn)制日志(binary log)中，也即是配置文件log-bin 指定的文件(這些記錄叫做二進(jìn)制日志事件焰情，binary log events)陌凳；
??PS：從圖中可以看出，Slave服務(wù)器中有一個(gè)I/O線程(I/O Thread)在不停地監(jiān)聽Master的二進(jìn)制日志(Binary Log)是否有更新：如果沒有它會(huì)睡眠等待 Master 產(chǎn)生新的日志事件内舟；
??如果有新的日志事件(Log Events)合敦，則會(huì)將其拷貝至 Slave 服務(wù)器中的中繼日志(Relay Log)。
??(2)验游、slave 將 master 的二進(jìn)制日志事件（binary log events）拷貝到它的中繼日志(relay log)充岛；
??(3)、slave 重做中繼日志中的事件耕蝉，將 Master 上的改變反映到它自己的數(shù)據(jù)庫中崔梗，所以兩端的數(shù)據(jù)是完全一樣的。
??PS：從圖中可以看出垒在，Slave 服務(wù)器中有一個(gè) SQL 線程(SQL Thread)從中繼日志讀取事件蒜魄，并重做其中的事件，從而更新 Slave 的數(shù)據(jù)场躯，使其與 Master 中的數(shù)據(jù)一致谈为。只要該線程與 I/O 線程保持一致，中繼日志通常會(huì)位于 OS 的緩存中推盛，所以中繼日志的開銷很小峦阁。附簡(jiǎn)要原理圖：
??主從復(fù)制的幾種方式：
??（1）、同步復(fù)制
??主服務(wù)器在將更新的數(shù)據(jù)寫入它的二進(jìn)制日志（Bin log）文件中后耘成，必須等待驗(yàn)證所有的從服務(wù)器的更新數(shù)據(jù)是否已經(jīng)復(fù)制到其中榔昔，之后才可以自由處理其它進(jìn)入的事務(wù)處理請(qǐng)求驹闰。
??（2）、異步復(fù)制
??主服務(wù)器在將更新的數(shù)據(jù)寫入它的二進(jìn)制日志（Bin log）文件中后撒会，無需等待驗(yàn)證更新數(shù)據(jù)是否已經(jīng)復(fù)制到從服務(wù)器中嘹朗，就可以自由處理其它進(jìn)入的事務(wù)處理請(qǐng)求诵肛。
??（3）、半同步復(fù)制
??主服務(wù)器在將更新的數(shù)據(jù)寫入它的二進(jìn)制日志（Bin log）文件中后乙埃，只需等待驗(yàn)證其中一臺(tái)從服務(wù)器的更新數(shù)據(jù)是否已經(jīng)復(fù)制到其中甫何，就可以自由處理其它進(jìn)入的事務(wù)處理請(qǐng)求赃额，其他的從服務(wù)器不用管竹勉。
??3、數(shù)據(jù)庫分表，分區(qū)炼鞠，分庫
??分表見上面描述（垂直拆分赃阀、水平拆分）驮俗。
??分區(qū)就是把一張表的數(shù)據(jù)分成多個(gè)區(qū)塊提佣，這些區(qū)塊可以在一個(gè)磁盤上，也可以在不同的磁盤上荤崇，分區(qū)后拌屏，表面上還是一張表，但數(shù)據(jù)散列在多個(gè)位置术荤，這樣一來倚喂，多塊硬盤同時(shí)處理不同的請(qǐng)求，從而提高磁盤 I/O 讀寫性能瓣戚，實(shí)現(xiàn)比較簡(jiǎn)單端圈。包括水平分區(qū)和垂直分區(qū)。
??分庫是根據(jù)業(yè)務(wù)不同把相關(guān)的表切分到不同的數(shù)據(jù)庫中子库，比如 web舱权、bbs、blog 等庫仑嗅。
