第四章 schema與數(shù)據(jù)類型優(yōu)化

數(shù)據(jù)類型：
1簸州、varchar缭付，字符串列的最大長(zhǎng)度比平均長(zhǎng)度大和諾柿估，適合用varchar類型；
2陷猫、char,適合存儲(chǔ)很短的字符串秫舌，或者所有值都接近同一個(gè)長(zhǎng)度；
3绣檬、時(shí)間日期類型足陨，盡量使用TIMESTAMP,它比DATETIME空間效率更高；
1娇未、緩存表 墨缘、匯總表和影子表
2、 除非需要用到某些InnoDB不具備的特性零抬，并且沒有其他辦法可以替代镊讼，否則都應(yīng)該優(yōu)先選擇InnoDB引擎。
3平夜、mysql可以對(duì)整型指定寬度蝶棋，例如：INT(11),對(duì)大多數(shù)應(yīng)用是沒有意義的，整型長(zhǎng)度只對(duì)在mysql一些交互工具中作為顯示整型的長(zhǎng)度忽妒，對(duì)于值計(jì)算來源INT(1)和INT(11)是相同的玩裙。
4、varchar適合場(chǎng)景：字符串列的最大長(zhǎng)度比平均長(zhǎng)度大很多段直；char適合場(chǎng)景：存儲(chǔ)很短的額字符串吃溅，或者所有值都接近同一長(zhǎng)度，例如md5加密后的值坷牛；
5罕偎、大數(shù)據(jù)量alter table會(huì)鎖表并且重建整張表很澄，執(zhí)行時(shí)間可達(dá)幾個(gè)小時(shí)甚至幾天京闰，解決方案：一、現(xiàn)在一臺(tái)不提供服務(wù)的機(jī)器上執(zhí)行alter table操作甩苛，然后和提供服務(wù)的主庫(kù)進(jìn)行切換蹂楣；二、“影子拷貝”讯蒲，即創(chuàng)建一張與源表無關(guān)的新表痊土，然后通過重命名和刪除表的方式交換兩張 表；
6墨林、 mysql disable keys禁用索引赁酝，此操作對(duì)單條索引無效犯祠，只對(duì)多條索引有用；

范式

第一范式：無重復(fù)列酌呆，表中的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)
第二范式：屬性完全依賴于主鍵衡载，不能存在僅依賴于關(guān)鍵一部分的屬性
第三范式：屬性不傳遞依賴于其它非主屬性，非主鍵列必須直接依賴于主鍵隙袁，而不能傳遞依賴痰娱。
反范式：用空間換時(shí)間，將數(shù)據(jù)冗余在多張表中菩收，查詢中無須關(guān)聯(lián)

范式優(yōu)點(diǎn)：
(1) 范式化的更新操作通常比反范式化要快
(2)當(dāng)數(shù)據(jù)較好地范式化時(shí)梨睁，就只有很少或者沒有重復(fù)數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)
(3)范式化的表通常更小坡贺，占用更小的內(nèi)存拴念，所以處理速度更快
(4)很少有多余的數(shù)據(jù)政鼠，意味著檢索列表時(shí)更少需要distinct和group by語(yǔ)句時(shí)間

范式缺點(diǎn)：
符合范式的schema設(shè)計(jì)，查詢時(shí)通常需要關(guān)聯(lián)查詢

schema設(shè)計(jì)簡(jiǎn)單原則

• 盡量避免過度設(shè)計(jì)胡桨，例如會(huì)導(dǎo)致極其復(fù)雜查詢的schema設(shè)計(jì)，或者有很多列的表設(shè)計(jì)刽虹；
• 使用小而簡(jiǎn)單的合適數(shù)據(jù)類型呢诬，除非真是數(shù)據(jù)模型中有確切的需要，否則應(yīng)該盡可能地避免使用NULL值
• 盡量使用相同的數(shù)據(jù)類型存儲(chǔ)相似或相關(guān)的值，尤其是要在關(guān)聯(lián)條件中使用的列初烘；
• 注意可變長(zhǎng)字符串肾筐，其在臨時(shí)表或者排序時(shí)可能悲觀的按最大長(zhǎng)度分配內(nèi)存
• 盡量使用整型標(biāo)識(shí)列
• 避免使用mysql已經(jīng)遺棄的特性剧劝，例如指定浮點(diǎn)數(shù)的精度（可用decimal代替），或者整數(shù)的顯示寬度
• 小心使用ENUM和SET抓歼，盡量避免使用谣妻；避免使用BIT；

第五章 創(chuàng)建高性能的索引

1、 不同的存儲(chǔ)引擎的所在的工作方式并不一樣份企，也不是所有得存儲(chǔ)引擎支持所有類型的索引司志；
2腰根、 索引的優(yōu)點(diǎn)：索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量瘸恼；索引可以幫助服務(wù)器避免排序和臨時(shí)表岩睁；索引可以將隨機(jī)IO變?yōu)轫樞騃O;

高性能的索引策略

1、獨(dú)立的列，索引列不能是表達(dá)式的一部分抢肛；
2、選擇合適的索引順序福稳，將選擇性高的索引放在最前面涎拉；
3、聚蔟索引的圆，包含B-Tree索引和數(shù)據(jù)行鼓拧；優(yōu)點(diǎn)：可以將相關(guān)數(shù)據(jù)保存在一起，數(shù)據(jù)訪問更快越妈，使用聚簇索引掃描的查詢可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值季俩；缺點(diǎn)：數(shù)據(jù)插入速度依賴于順序插入，聚蔟索引可能導(dǎo)致全表掃描梅掠；
4种玛、覆蓋索引，如果一個(gè)索引包含所有需查詢的字段的值瓤檐，該索引為覆蓋索引赂韵。覆蓋索引可以極大的提高查詢性能。在大多數(shù)存儲(chǔ)引擎中挠蛉，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢祭示。
5、所用索引掃描來做排序谴古，只有當(dāng)所用的列順序和order by子句的順序完全一致质涛，并且所有列的排序方向都一樣，mysql才能使用索引來對(duì)結(jié)果進(jìn)行排序掰担；
6汇陆、冗余和重復(fù)索引，重復(fù)索引是指相同的列上按照相同個(gè)順序創(chuàng)建的相同類型的索引带饱；要盡量不使用冗余索引毡代，盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引阅羹；
7、要?jiǎng)h除未使用的索引教寂，可提升insert update執(zhí)行效率捏鱼；

維護(hù)索引和表

1、找到并修復(fù)損壞的表酪耕；
2导梆、更新索引統(tǒng)計(jì)信息，show index from table可查詢索引信息迂烁；
3看尼、減少索引和數(shù)據(jù)的碎片，optimize table可有效整理數(shù)據(jù)盟步，去除數(shù)據(jù)碎片狡忙；例如：delete table后，optimize一下址芯，可有效降低數(shù)據(jù)占用空間灾茁；

哈希索引的限制：

1、 哈希索引只包含哈希值和行指針谷炸，而不存儲(chǔ)字段值北专，所以不能使用索引中的值來避免讀取行；
2旬陡、 哈希索引數(shù)據(jù)并不是按照索引值順序存儲(chǔ)的拓颓，所以也就無法用于排序；
3描孟、 哈希索引頁(yè)不支持部門索引列匹配查找驶睦，因?yàn)楣Ｋ饕冀K是使用索引列的全部?jī)?nèi)容來計(jì)算哈希值的；
4匿醒、哈希索引只支持等值比較查詢场航；不支持范圍查詢；

選擇索引的三個(gè)原則：

1. 單行訪問很慢的廉羔。如果服務(wù)器從存儲(chǔ)中讀取一個(gè)數(shù)據(jù)塊只是為了獲取其中一行溉痢，那么久浪費(fèi)了很多工作。最好讀取的塊中能包含盡可能多所需要的行憋他。使用索引創(chuàng)建位置引用可提升效率孩饼。
2. 按順序訪問范圍數(shù)據(jù)是很快的，這有兩個(gè)原因竹挡。第一镀娶，順序IO不需要多次磁盤尋道，所以比隨機(jī)IO要快很多揪罕。第二梯码，如果服務(wù)器能夠按需要順序讀取數(shù)據(jù)宝泵，那么就不再需要額外的排序操作，并且group by查詢也無須再做排序和將行按組進(jìn)行聚合計(jì)算了忍些。
3. 索引覆蓋查詢是很快的鲁猩。如果一個(gè)索引包含了查詢需要的所有列坎怪，那么存儲(chǔ)引擎就不需要再回標(biāo)查找行罢坝。這避免了大量的單行訪問。

第六章 查詢性能優(yōu)化

查詢生命周期：從客戶端搅窿，到服務(wù)器嘁酿，然后在服務(wù)器上進(jìn)行解析，生成執(zhí)行計(jì)劃男应，執(zhí)行闹司，并返回結(jié)果給客戶端。

慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問

低效查詢分析方法：

• 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)沐飘。通常意味著訪問了太多的行游桩，也有可能訪問太多的列。
• 確認(rèn)mysql服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行耐朴。

低效查詢典型案列：

• 查詢不需要的記錄
• 多表關(guān)聯(lián)時(shí)返回全部列
• 總是取出全部列
• 重復(fù)查詢相同的數(shù)據(jù)

衡量查詢開銷的三個(gè)指標(biāo)：

1. 響應(yīng)時(shí)間
   響應(yīng)時(shí)間包括服務(wù)時(shí)間和排隊(duì)時(shí)間借卧；服務(wù)時(shí)間：是指數(shù)據(jù)庫(kù)處理這個(gè)查詢真正花了多長(zhǎng)時(shí)間，排隊(duì)時(shí)間：服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行查詢的時(shí)間（可能是IO筛峭，行鎖等等）铐刘；
2. 掃描的行數(shù)
3. 返回的行數(shù)
   較短的行的訪問速度更快，內(nèi)存中的行比磁盤中的行訪問速度更快影晓；較短的行數(shù)镰吵，是在內(nèi)存中查詢，當(dāng)行數(shù)較多時(shí)則在磁盤中查詢挂签；

重構(gòu)查詢方式

• 一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢
• 切分查詢（大查詢分為小查詢疤祭，例如：大掃描行數(shù)查詢切分成多個(gè)小掃描行數(shù)的查詢）
• 分解關(guān)聯(lián)查詢，優(yōu)點(diǎn)：讓緩存效率更高饵婆；讓單個(gè)查詢減少鎖競(jìng)爭(zhēng)画株；在應(yīng)用層做關(guān)聯(lián)，容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分啦辐，提高系統(tǒng)性能谓传；減少冗余記錄的查詢；

查詢執(zhí)行的基礎(chǔ)

mysql查詢過程：

1. 客戶端發(fā)送一條查詢給服務(wù)器
2. 服務(wù)器先查詢緩存芹关，如果命中了緩存续挟，直接返回結(jié)果；否則侥衬，進(jìn)入下一步诗祸；
3. 服務(wù)器進(jìn)行sql解析跑芳、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃直颅；
4. mysql根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃博个，再調(diào)用存儲(chǔ)引擎API來執(zhí)行查詢；
5. 將查詢結(jié)果返回給客戶端功偿；

SHOW FULL PROCESSLIST可查看當(dāng)前狀態(tài)盆佣；
sleep:線程正在等待客戶端發(fā)送新的請(qǐng)求；
Query:線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端械荷；
Locked:該線程正在等待表鎖共耍；
Analyzing and statistics:線程正在收集存儲(chǔ)引擎的統(tǒng)計(jì)信息，并生產(chǎn)查詢的執(zhí)行計(jì)劃吨瞎；
Coping to tmp table:線程正在執(zhí)行查詢痹兜，并將其結(jié)果復(fù)制到臨時(shí)表中；
Sorting result:線程正在對(duì)結(jié)果集進(jìn)行排序颤诀；
Sending data:線程可能在多種狀態(tài)之間傳送數(shù)據(jù)字旭，或者正在生成結(jié)果集，或者正在向客戶端發(fā)送數(shù)據(jù)崖叫；

mysql在進(jìn)行文件排序的時(shí)候需要使用的臨時(shí)存儲(chǔ)空間可能比想象的要大得多遗淳。

mysql查詢優(yōu)化器的局限性

1. 不需要聽取那些關(guān)于子查詢的“絕對(duì)真理”；
2. 應(yīng)該用測(cè)試來嚴(yán)重對(duì)子查詢的執(zhí)行計(jì)劃和響應(yīng)時(shí)間的假設(shè)归露；

mysql不允許對(duì)同一張表同事進(jìn)行查詢和更新操作洲脂。

優(yōu)化特性類型的查詢

1. 優(yōu)化count()查詢
   簡(jiǎn)單優(yōu)化，反向count(1)剧包，再減去查詢結(jié)果恐锦；使用近似值代替；使用匯總表疆液，定時(shí)匯總數(shù)據(jù)一铅，總匯總表里查詢結(jié)果；
2. 優(yōu)化關(guān)聯(lián)查詢
   確保on和using列上有索引堕油；確保order by和group by只包含一列潘飘；
3. 優(yōu)化子查詢
   盡可能使用關(guān)聯(lián)查詢代替；
4. 優(yōu)化group by和distinct
5. 優(yōu)化limit查詢
   避免過多的offset掉缺；select * from order where id > 10030 order by id desc limit 20;
6. 優(yōu)化union查詢
   如果無重復(fù)數(shù)據(jù)卜录，使用union all 代替union；

優(yōu)化通常都需要三管齊下：不做眶明、少做艰毒、快速地做

第十一章：可擴(kuò)展的MySql

可擴(kuò)展性：當(dāng)增加資源以處理負(fù)載和增加容量時(shí)系統(tǒng)能夠獲得的投資產(chǎn)出率。

向上擴(kuò)展：也叫垂直擴(kuò)展搜囱，購(gòu)買更多性能強(qiáng)悍的硬件丑瞧；
向外擴(kuò)展：向外擴(kuò)展策略劃分為三個(gè)部分：復(fù)制柑土、拆分、以及數(shù)據(jù)分片
通過多實(shí)例擴(kuò)展：每臺(tái)服務(wù)器上運(yùn)行過個(gè)實(shí)例绊汹，然后劃分服務(wù)器的硬件資源稽屏，將其分配給每個(gè)實(shí)例。在一臺(tái)性能強(qiáng)悍的硬件上可以獲得10倍到15倍的合并系數(shù)西乖。你需要平衡管理復(fù)雜度代價(jià)和更有性能的收益狐榔。
通過集群擴(kuò)展：未來典型的集群數(shù)據(jù)庫(kù)可能更像是SQL和NoSQL的混合體，有多重存取機(jī)制來滿足不同的使用需求浴栽。MySQL Cluster(NDB Cluster)
向內(nèi)擴(kuò)展：處理不斷增長(zhǎng)的數(shù)據(jù)和負(fù)載最簡(jiǎn)單的辦法是對(duì)不再需要的數(shù)據(jù)進(jìn)行歸檔和清理荒叼。
做歸檔和清理時(shí)考慮以下幾點(diǎn)：

1. 對(duì)應(yīng)用的影響
2. 要?dú)w檔的行
3. 維護(hù)數(shù)據(jù)一致性
4. 避免數(shù)據(jù)丟失
5. 解除歸檔

保持活躍數(shù)據(jù)獨(dú)立轿偎，即使不真的把老數(shù)據(jù)轉(zhuǎn)移到別的服務(wù)器典鸡，也許應(yīng)用也能受益于活躍數(shù)據(jù)和飛活躍數(shù)據(jù)的隔離』祷蓿可以有一下集中做法：
1萝玷、將表劃分為幾個(gè)部分
2、MySQL分區(qū)
3昆婿、基于時(shí)間的數(shù)據(jù)分區(qū)

負(fù)載均衡
負(fù)載均衡有五個(gè)常見的目的：
可擴(kuò)展性球碉、高效性、可用性仓蛆、透明性睁冬、一致性
一、直接連接
1.1 復(fù)制上的讀寫分離
1.2 修改應(yīng)用的配置
1.3 修改DNS名
1.4 轉(zhuǎn)移IP地址
二看疙、引入中間件
2.1 負(fù)載均衡器
2.2 負(fù)載均衡算法
隨機(jī)豆拨、輪詢、最少連接數(shù)能庆、最快響應(yīng)施禾、哈希、權(quán)重
2.3 在服務(wù)器池中增加和刪除服務(wù)器
三搁胆、一主多備的負(fù)載均衡

Mysql EXPLAIN執(zhí)行計(jì)劃

id列：這一列總是包含一個(gè)編號(hào)弥搞，標(biāo)識(shí)SELECT所屬的行。