第四章 Schema與數(shù)據(jù)類(lèi)型優(yōu)化
4.1選擇優(yōu)化的數(shù)據(jù)類(lèi)型
（1）更小通常更好脏里。
（2）簡(jiǎn)單就好。使用Mysql內(nèi)建的類(lèi)型存儲(chǔ)日期和時(shí)間虹曙，而不是用字符串迫横。用整型存儲(chǔ)IP地址。
（3）盡量避免NULL酝碳。如果查詢(xún)中包含可為NULL的列矾踱，對(duì)MySQL來(lái)說(shuō)更難優(yōu)化，因?yàn)榭蔀镹ULL的列使得索引疏哗，索引統(tǒng)計(jì)和值比較都更復(fù)雜呛讲。會(huì)使用更多的存儲(chǔ)空間，在MySQL里面需要特殊處理返奉。當(dāng)可為NULL的列被被索引時(shí)贝搁，每個(gè)索引記錄需要一個(gè)額外的字節(jié)。
通常把可為NULL修改為NOT NULL帶來(lái)的性能提升比較小芽偏，一般不優(yōu)先優(yōu)化這個(gè)雷逆。
TIMESTAMP和DATETIME:都可以存儲(chǔ)相同類(lèi)型的數(shù)據(jù)，時(shí)間和日期污尉，精確到秒膀哲，但是TIMESTAMP占用DATETIME一般的存儲(chǔ)空間，會(huì)根據(jù)時(shí)區(qū)變化被碗，并且有特殊的自動(dòng)更新能力某宪，但是允許的時(shí)間范圍要小得多。
4.1.1整數(shù)類(lèi)型
TINYINT(8位）锐朴、SMALLINT(16位)兴喂、MEDIUMINT(24位)、INT(32位)焚志、BIGINT(64位)
如果加上UNSIGNED屬性衣迷，表示不允許有負(fù)數(shù)，可以使正數(shù)的上限提高一倍娩嚼。
指定寬度,如INT(11）蘑险、INT(20),對(duì)于應(yīng)用是沒(méi)有意義的，不會(huì)限制范圍岳悟，只會(huì)改變顯示的位數(shù)佃迄。
4.1.2實(shí)數(shù)類(lèi)型
實(shí)數(shù)是帶小數(shù)部分的數(shù)字。
DECIMAL(可以精確計(jì)算)贵少、FLOAT和DOUBLE（近似計(jì)算）
浮點(diǎn)型和DECIMAL都可以指定精度呵俏。
盡量只在對(duì)小數(shù)進(jìn)行精確計(jì)算的時(shí)候才采用DECIMAL.在數(shù)據(jù)量大的時(shí)候也可以將數(shù)據(jù)乘以一個(gè)很大的倍數(shù)，存到BIGINT類(lèi)型滔灶，這樣可以避免浮點(diǎn)型計(jì)算不精確以及DECIMAL精確計(jì)算代價(jià)高的問(wèn)題普碎。
4.1.3字符串類(lèi)型
字符串的字符集，排序規(guī)則都會(huì)影響性能录平。
VARCHAR和CHAR
VARCHAR存儲(chǔ)可變長(zhǎng)字符串麻车，比定長(zhǎng)類(lèi)型更節(jié)省空間缀皱，使用1或2個(gè)額外字節(jié)記錄字符串的長(zhǎng)度。缺點(diǎn)就是更新的時(shí)候會(huì)使行變得比原來(lái)長(zhǎng)动猬，如果一個(gè)行占用的空間增長(zhǎng)啤斗，在頁(yè)內(nèi)沒(méi)有更多空間，InnoDB引擎的處理是分裂頁(yè)使行可以放進(jìn)頁(yè)內(nèi)赁咙。會(huì)保留字符串末尾的空格钮莲。
CHAR是定長(zhǎng)字符串。存儲(chǔ)和檢索時(shí)會(huì)刪除末尾空格彼水。
CHAR適合存儲(chǔ)很短的字符串崔拥，如密碼的MD5值。經(jīng)常變更的字符串凤覆，因?yàn)镃HAR類(lèi)型不易產(chǎn)生碎片链瓦。
BINARY和VARBINARY存儲(chǔ)二進(jìn)制字符串（字節(jié)碼）。
使用VARCHAR(5)和VARCHAR(200)存儲(chǔ)‘hello’的空間開(kāi)銷(xiāo)是一樣的叛赚，但是更長(zhǎng)的列會(huì)消耗更多的內(nèi)存澡绩，如果在使用內(nèi)存臨時(shí)表進(jìn)行排序和操作時(shí)會(huì)消耗跟更多的內(nèi)存。
BLOB（二進(jìn)制）和TEXT（字符）類(lèi)型：
存儲(chǔ)很大的數(shù)據(jù)的字符串類(lèi)型俺附。
查詢(xún)的時(shí)候盡量避免使用BLOB和TEXT的列作為條件肥卡，如果確實(shí)要用，應(yīng)該使用部分字符串 SUBSTRING(列事镣，長(zhǎng)度)步鉴。如果EXPLAIN分析里面有Using temporary說(shuō)明查詢(xún)用到了隱式臨時(shí)表。
可以使用枚舉ENUM代替會(huì)經(jīng)常重復(fù)字符串的列璃哟，實(shí)際上會(huì)存儲(chǔ)為整數(shù)氛琢。排序也是使用整數(shù)排序的。
4.1.4日期和時(shí)間類(lèi)型
DATETIME:從1001年到9999年随闪，精度為秒阳似，格式Y(jié)YYYMMDDHHMMSS，與時(shí)區(qū)無(wú)關(guān)铐伴，使用8個(gè)字節(jié)的存儲(chǔ)空間撮奏。如2008-01-16 22:37:08
TIMESTAMP：保存1970年1月1日以來(lái)的秒數(shù)。只占4個(gè)字節(jié)当宴，范圍比DATETIME小畜吊。使用FROM_UNIXTIME()函數(shù)可以把UNIX時(shí)間戳轉(zhuǎn)換成日期，UNIX_TIMESTAMP()函數(shù)把日期轉(zhuǎn)為UNIX時(shí)間戳户矢。也以DATETIME的格式顯示時(shí)間玲献，但只是顯示格式上的區(qū)別。TIMESTAMP可以設(shè)置一些特殊屬性，插入時(shí)可以默認(rèn)插入當(dāng)前時(shí)間戳捌年。
無(wú)特殊行為瓢娜，盡量使用TIMESTAMP,空間效率更高。
如果要顯示微妙級(jí)別的延窜，可以使用自己的存儲(chǔ)格式恋腕，用BIGINT類(lèi)型存儲(chǔ)微妙級(jí)別的時(shí)間戳抹锄。
4.1.5位數(shù)據(jù)類(lèi)型
BIT:MySQL把BIT當(dāng)做字符串類(lèi)型逆瑞，是包含二進(jìn)制0，1的字符串伙单，直接檢索時(shí)得到的是二進(jìn)制字符串對(duì)應(yīng)的ASCII碼的字符获高，如果包含數(shù)字的上下文，則得到的是二進(jìn)制字符串對(duì)應(yīng)的數(shù)字吻育。如 b'00111001',直接查結(jié)果是57對(duì)應(yīng)的字符‘9’念秧，如果查‘a(chǎn)+0’,則結(jié)果是57.
這種機(jī)制比較迷惑，所以應(yīng)該盡量避免使用這種類(lèi)型布疼。
4.1.6選擇標(biāo)識(shí)符
在關(guān)聯(lián)的表中摊趾，關(guān)聯(lián)的列使用相同的數(shù)據(jù)類(lèi)型，避免操作時(shí)的隱式轉(zhuǎn)換或者直接出錯(cuò)游两，導(dǎo)致性能問(wèn)題砾层。
整數(shù)類(lèi)型是標(biāo)識(shí)列最好的選擇南捂，并且可以使用自增AUTO_INCREMENT.
應(yīng)該避免使用字符串作為標(biāo)識(shí)列甫菠，很消耗空間负芋，比數(shù)字類(lèi)型慢劲赠。
對(duì)于隨機(jī)產(chǎn)生的字符串吱晒，如MD5(),SHA1(),UUID()產(chǎn)生的字符串蔼紧，這些函數(shù)產(chǎn)生的新值會(huì)分布在很大的空間內(nèi)怠惶，這會(huì)導(dǎo)致INSERT和一些SELECT語(yǔ)句變得很慢回季。
4.2MySQL schema設(shè)計(jì)中的陷阱
4.3范式和反范式
范式化的schema有優(yōu)點(diǎn)瘩燥，比如更新操作要快秕重，重復(fù)數(shù)據(jù)更少，可以減少DISTINCT和group by操作厉膀。缺點(diǎn)是稍微復(fù)雜的查詢(xún)可能都需要至少一次關(guān)聯(lián)溶耘，這樣代價(jià)高，并且容易使索引失效站蝠。
例子：假如有一個(gè)網(wǎng)站汰具，允許用戶(hù)發(fā)送信息，并且一些是付費(fèi)用戶(hù)菱魔，查詢(xún)付費(fèi)用戶(hù)的最近10條消息留荔。
范式化schema的查詢(xún)：關(guān)聯(lián)查詢(xún)
反范式化schema，比如將accout_type和published字段合并到message表中，創(chuàng)建復(fù)合索引（account_type,published）便可以不用關(guān)聯(lián)聚蝶，高效的查詢(xún)杰妓。
4.3.3混用范式化和反范式化
在不同的表中存儲(chǔ)相同的特定列，有利于高效查詢(xún)碘勉，但又不至于完全反范式化的插入和刪除問(wèn)題巷挥。
4.4匯總表和緩存表
在同一張表中保存衍生的冗余數(shù)據(jù)可以提升性能。
緩存表指可以簡(jiǎn)單的從schema其他表中獲取的數(shù)據(jù)的表验靡。
匯總表保存的是GROUP BY語(yǔ)句聚合數(shù)據(jù)的表倍宾。
例子：獲取24小時(shí)內(nèi)獲取的消息數(shù)，通過(guò)實(shí)時(shí)維護(hù)message_cnt字段會(huì)效率很低胜嗓，實(shí)時(shí)計(jì)算統(tǒng)計(jì)是很昂貴的操作高职，可以每小時(shí)生成一個(gè)匯總表，以每小時(shí)的匯總表為基礎(chǔ)辞州，查詢(xún)怔锌。
緩存表是對(duì)優(yōu)化搜索和檢索查詢(xún)語(yǔ)句很有效。
4.4.1物化視圖
預(yù)先計(jì)算并存儲(chǔ)在磁盤(pán)上的表变过，可以通過(guò)各種策略進(jìn)行刷新和更新埃元。
4.4.2計(jì)數(shù)器表
單獨(dú)創(chuàng)建一張計(jì)數(shù)表，緩存朋友數(shù)量媚狰，網(wǎng)站點(diǎn)擊數(shù)量等岛杀，可以更快的查詢(xún)并且不會(huì)讓緩存失效。
為了獲得更高的并發(fā)性能哈雏，可以預(yù)先在表上增加100行數(shù)據(jù)楞件，每次更新的額時(shí)候隨機(jī)選擇一個(gè)槽進(jìn)行更新。
為了提升讀查詢(xún)的速度裳瘪，經(jīng)常會(huì)需要建一些額外的索引土浸，增加冗余列，甚至創(chuàng)建緩存表和匯總表彭羹。雖然寫(xiě)操作變慢了黄伊，但更加顯著的提高了讀操作的性能。
4.5加快ALTER TABLE操作的速度
對(duì)大表派殷，alter table的操作可能需要數(shù)個(gè)小時(shí)甚至數(shù)天才能完成还最。一般來(lái)說(shuō)，alter table操作會(huì)導(dǎo)致mysql服務(wù)中斷毡惜，解決方法有：先在一臺(tái)不服務(wù)的機(jī)器上完成修改表的操作拓轻，再和主庫(kù)進(jìn)行切換；另一種方法是影子拷貝经伙，創(chuàng)建一張表扶叉，通過(guò)重命名和刪表操作交換兩張表。
如果是只改變某個(gè)列的默認(rèn)值，可以使用alter column的操作直接修改.frm文件里列的默認(rèn)值枣氧，不用拷貝整個(gè)表溢十。
4.5.1只修改.frm文件
在某些情況下，只修改.frm文件可以達(dá)到修改表的目的达吞，而不用復(fù)制整張表：
比如：移除一個(gè)列的自增屬性张弛、增加修改ENUM或SET常量
步驟：1、創(chuàng)建相同結(jié)構(gòu)的額空表酪劫，并進(jìn)行修改2吞鸭、執(zhí)行FLUSH TABLE WITH READ LOCK,關(guān)閉所有正在使用的表，禁止任何表打開(kāi)3契耿、交換.frm文件4瞒大、執(zhí)行UNLOCK.
4.5.2快速創(chuàng)建MyISAM索引
創(chuàng)建新的表加入索引，獲取讀鎖搪桂，重命名交換，釋放讀鎖盯滚，重建索引
4.6總結(jié)
1踢械、使用小而簡(jiǎn)單的數(shù)據(jù)類(lèi)型
2、關(guān)聯(lián)條件中使用的列用相同的類(lèi)型
3魄藕、注意可變長(zhǎng)字符串
4内列、盡量使用整型定義標(biāo)識(shí)列
5、小心使用ENUM和SET,避免使用BIT
6背率、混用范式和反范式
7话瞧、ALTER TABLE 的一些快速的方法。
第五章 創(chuàng)建高性能的索引
索引是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)寝姿。索引優(yōu)化是對(duì)查詢(xún)性能優(yōu)化最好的手段交排，索引能夠輕易將查詢(xún)性能提高幾個(gè)數(shù)量級(jí)。
在在索引中找到對(duì)應(yīng)值饵筑，再根據(jù)匹配的索引記錄找到對(duì)應(yīng)的數(shù)據(jù)行埃篓。
索引可以包含一個(gè)或多個(gè)列的值，如果包含多個(gè)列根资，列的順序?十分重要架专，因?yàn)镸ySQL只能高效的使用索引的最左前綴列。
即便是使用ORM框架也需要關(guān)心索引玄帕。
5.1索引基礎(chǔ)
5.1.1索引類(lèi)型
B-Tree索引
所有的值都是按照順序存儲(chǔ)的部脚，每一個(gè)葉子頁(yè)到根的距離相同。
存儲(chǔ)引擎不需要再掃描全表裤纹，從索引的根節(jié)點(diǎn)開(kāi)始搜索委刘，根據(jù)指針往下層節(jié)點(diǎn)找。葉子節(jié)點(diǎn)的指針指向的是被索引的數(shù)據(jù)，而不是其他節(jié)點(diǎn)頁(yè)钱雷。
索引對(duì)于多個(gè)值的排序的依據(jù)是定義索引時(shí)的列的順序骂铁。
可以使用索引的查詢(xún)類(lèi)型：
1、全值匹配：包含索引中的所有列
2罩抗、匹配最左前綴
3拉庵、匹配列前綴
4、匹配范圍值
5套蒂、精確匹配某一列并范圍匹配另一列
6钞支、只訪問(wèn)索引的查詢(xún)（覆蓋索引）
索引除了安值查找之外還可以用于查詢(xún)中的ORDER BY操作。
使用索引的限制：
1操刀、如果不是按照索引的最左列開(kāi)始查找烁挟，則無(wú)法使用索引。
2骨坑、不能跳過(guò)索引中的列
3撼嗓、如果查詢(xún)中有某個(gè)列是范圍查詢(xún)，則其右邊的所有列無(wú)法用索引優(yōu)化查找欢唾。
哈希索引
空間數(shù)據(jù)索引
不會(huì)使用
全文索引
查找文本中的關(guān)鍵詞且警，不是直接比較索引中的值。
5.2索引的優(yōu)點(diǎn)
1礁遣、索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
2斑芜、索引可以幫助服務(wù)器避免排序和臨時(shí)表
3、索引可以將隨機(jī)IO變?yōu)轫樞騃O.
對(duì)非常小的表祟霍，全表掃描更合適杏头，對(duì)中大型表，索引非常有效沸呐，特大表醇王，索引的建立和使用代價(jià)也會(huì)增長(zhǎng)。
5.3高性能的索引策略
正確創(chuàng)建和使用索引是實(shí)現(xiàn)高性能查詢(xún)的基礎(chǔ)垂谢。
5.3.1獨(dú)立的列
索引列不能是表達(dá)式的一部分厦画，也不能是函數(shù)的參數(shù)。
5.3.2前綴索引和索引選擇性
有時(shí)需要索引很長(zhǎng)的字符列滥朱，會(huì)讓索引變得大且慢根暑。通常可以索引開(kāi)始的部分字符徙邻，這樣可以大大節(jié)約索引空間排嫌，提高索引效率，但同時(shí)也會(huì)降低索引的選擇性缰犁。選擇性只不重復(fù)的索引值與數(shù)據(jù)表記錄總數(shù)的比值淳地。索引選擇性越高怖糊，查詢(xún)的效率越高。因?yàn)檫x擇性高的索引可以過(guò)濾掉更多的行颇象。唯一索引的選擇性是1.性能最好伍伤。
對(duì)于BLOB、TEXT或者很長(zhǎng)的VARCHAR,必須使用列前綴索引遣钳，通常也是足以滿(mǎn)足查詢(xún)性能的扰魂。
保證選擇性的同時(shí)又不能太長(zhǎng)≡誊睿可以先找最常見(jiàn)值的列表劝评，然后和最常見(jiàn)的前綴列表進(jìn)行比較。
逐步調(diào)試倦淀，找到合適的前綴長(zhǎng)度達(dá)到比較好的選擇性蒋畜。
前綴索引能使索引更小更快，但缺點(diǎn)是無(wú)法用前綴索引做order by和group by,也無(wú)法用前綴索引做覆蓋掃描撞叽。
5.3.3多列索引
為每個(gè)列創(chuàng)建獨(dú)立的索引是不正確的姻成，大部分情況不能提高查詢(xún)性能。
在MySQL5.0之后查詢(xún)可以同時(shí)使用多個(gè)單列索引進(jìn)行掃描能扒，并將結(jié)果合并佣渴。OR,AND
索引合并是一種優(yōu)化優(yōu)化，但也說(shuō)明了當(dāng)前的索引建的很糟糕初斑。
5.3.4選擇合適的索引列順序
正確的順序依賴(lài)于使用該索引的查詢(xún)，并且同時(shí)要考慮如何滿(mǎn)足排序和分組的需要膨处。
經(jīng)驗(yàn)法則是见秤，當(dāng)不需要考慮排序和分組的時(shí)候，將選擇性最高的列放在前面通常是很好的真椿。
根據(jù)哪些運(yùn)行頻率最高的查詢(xún)來(lái)調(diào)整索引列的順序鹃答，這種情況索引的選擇性是最高的。
5.3.5聚簇索引
聚簇索引不是一種單獨(dú)的索引類(lèi)型而是一種數(shù)據(jù)存儲(chǔ)方式突硝，InnoDB的聚簇索引在同一結(jié)構(gòu)中保存了BTree索引和數(shù)據(jù)行测摔。
當(dāng)表有聚簇索引時(shí)，數(shù)據(jù)行實(shí)際上就是存放在索引的葉子頁(yè)中解恰。一個(gè)表只能有一個(gè)聚簇索引锋八。InnoDB通過(guò)主鍵聚集索引。
使用聚簇索引的優(yōu)點(diǎn)：
1护盈、減少磁盤(pán)IO
2挟纱、數(shù)據(jù)訪問(wèn)更快
3、使用覆蓋索引掃描的查詢(xún)可以直接使用頁(yè)節(jié)點(diǎn)中的主鍵值
缺點(diǎn)：
1腐宋、如果數(shù)據(jù)都在內(nèi)存里紊服，都是隨機(jī)IO,訪問(wèn)順序沒(méi)那么重要檀轨，就沒(méi)必要用聚簇索引了。
2欺嗤、插入的速度嚴(yán)重依賴(lài)于插入的順序参萄，按照主鍵順序插入是最快的方式。
3煎饼、代價(jià)高讹挎，會(huì)強(qiáng)制InnoDB將每個(gè)被更新的行移動(dòng)到新的位置。
4腺占、基于聚簇索引的表在插入新行或者主鍵被更新需要移動(dòng)行時(shí)可能會(huì)導(dǎo)致“頁(yè)分裂”的問(wèn)題淤袜。將一行插入到一個(gè)已滿(mǎn)的頁(yè)時(shí)，存儲(chǔ)引擎會(huì)將該頁(yè)分裂成兩個(gè)頁(yè)面衰伯，頁(yè)分裂會(huì)導(dǎo)致表占用更多的磁盤(pán)空間铡羡。
5、可能導(dǎo)致全表掃描變慢意鲸，行稀疏的時(shí)候烦周。
6、二級(jí)索引更大怎顾，因?yàn)槎?jí)索引的葉子節(jié)點(diǎn)包含了引用行的主鍵读慎。
7、二級(jí)索引需要兩次索引槐雾。因?yàn)槎?jí)索引的葉節(jié)點(diǎn)存的是主鍵值夭委，再通過(guò)主鍵值到聚簇索引中找對(duì)應(yīng)的行。
避免隨機(jī)的聚簇索引募强，使用UUID做聚簇索引會(huì)很糟糕株灸。因?yàn)檫@會(huì)導(dǎo)致隨機(jī)插入，使數(shù)據(jù)沒(méi)有聚集特性擎值。按照順序插入慌烧，可以直接插到后面，隨機(jī)的插入花銷(xiāo)更大鸠儿。
隨機(jī)插入的缺點(diǎn)：
1屹蚊、寫(xiě)入的目標(biāo)頁(yè)可能已經(jīng)刷到磁盤(pán)，導(dǎo)致要重新從磁盤(pán)讀取目標(biāo)頁(yè)进每，導(dǎo)致了大量的隨機(jī)IO
2汹粤、寫(xiě)入順序是亂的，InnoDB不得不頻繁做頁(yè)分裂操作品追，導(dǎo)致數(shù)據(jù)的移動(dòng)玄括，降低效率
3、頁(yè)分裂會(huì)導(dǎo)致碎片的產(chǎn)生
所以肉瓦，應(yīng)該盡可能的按照主鍵順序插入數(shù)據(jù)遭京。
順序主鍵在高并發(fā)的負(fù)載下會(huì)產(chǎn)生爭(zhēng)用胃惜。主鍵的上界會(huì)成為熱點(diǎn)。并發(fā)插入會(huì)導(dǎo)致間隙鎖競(jìng)爭(zhēng)哪雕，另外一個(gè)是自增的鎖機(jī)制船殉。這個(gè)問(wèn)題要重新設(shè)計(jì)表或者更改innodb_autoinc_lock_mode配置。
5.6.3覆蓋索引
通過(guò)索引直接獲取列數(shù)據(jù)斯嚎，不用讀取數(shù)據(jù)行利虫。對(duì)于二級(jí)索引，可以減少對(duì)主鍵的二次查詢(xún)堡僻。
如果使用了覆蓋索引糠惫，EXPLAIN分析會(huì)顯示Using index
如果索引不能覆蓋查詢(xún)所需的全部列，那就不得不掃描一條記錄就回表查詢(xún)一次對(duì)應(yīng)的行钉疫。
覆蓋索引的陷阱：MySQL查詢(xún)優(yōu)化器會(huì)判斷是否有一個(gè)索引能進(jìn)行覆蓋硼讽，如果不能覆蓋，就會(huì)找數(shù)據(jù)行牲阁。
例子：
查詢(xún)所有列固阁，無(wú)法使用覆蓋索引，like關(guān)鍵字在使用索引時(shí)也只支持最左前綴匹配城菊。
巧妙設(shè)置索引备燃，重寫(xiě)查詢(xún)。將索引覆蓋三個(gè)數(shù)據(jù)列（artist,title,prod_id）
這種歌方法叫延遲關(guān)聯(lián)凌唬，延遲了對(duì)列的訪問(wèn)并齐。第一階段，在from子句客税，通過(guò)覆蓋索引查找所有的符合條件的prod_id,然后根據(jù)prod_id在外層查詢(xún)匹配所有的列值冀膝，雖然不能索引覆蓋整個(gè)查詢(xún)，但比完全無(wú)法使用索引覆蓋好霎挟。
因?yàn)槎?jí)索引的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵，所以二級(jí)索引可以對(duì)主鍵列做覆蓋查詢(xún)麻掸。
5.3.7 使用索引掃描做排序
MySQl有兩種方法生成有序的結(jié)果：1酥夭、排序操作；2脊奋、按照索引順序掃描
如果使用了按索引順序掃描熬北，那么EXPLAIN的type的結(jié)果就是index，這和extra項(xiàng)里的Using index不一樣诚隙，Using index是說(shuō)明使用了覆蓋索引讶隐。
只有當(dāng)索引的列和順序和Order by子句中的順序完全一致，并且所有列的排序方向（ASC,DESC）都一樣時(shí)久又。這樣可以使用索引對(duì)結(jié)果進(jìn)行排序巫延。如果需要關(guān)聯(lián)多個(gè)表時(shí)效五，order by子句引用的字段必須全為第一個(gè)表的字段才能做索引排序。order by也要滿(mǎn)足最左前綴
有種特殊情況就是如果order by不滿(mǎn)足最左前綴炉峰，但是排序列的前導(dǎo)列是常量畏妖，這樣也可以使用索引來(lái)排序。
5.3.8壓縮索引
5.3.9冗余和重復(fù)索引
重復(fù)索引是在相同的列上按照相同的順序創(chuàng)建相同類(lèi)型的索引疼阔。
冗余索引就是如果有了復(fù)合索引（col1,col2）戒劫，那個(gè)索引（col1）就是冗余的。
（col1,ID）這種也是冗余的婆廊，因?yàn)槎?jí)索引包含了主鍵列迅细。
where A=5 order by ID.可以用索引（A）排序,但不能用索引（A,B）排序。
5.3.10未使用的索引
5.3.11索引和鎖
索引可以讓查詢(xún)鎖定更少的行淘邻。InnoDB只有在訪問(wèn)行的時(shí)候才會(huì)對(duì)行加鎖茵典，而索引能減少I(mǎi)nnoDB訪問(wèn)的行數(shù)，從而減少鎖的數(shù)量列荔。如果在存儲(chǔ)引擎層不能過(guò)濾掉的行到了MySQL服務(wù)層就不能避免鎖定行了敬尺。
extra出現(xiàn)using where 表明MySQL服務(wù)器將存儲(chǔ)引擎返回行后再應(yīng)用where過(guò)濾。底層存儲(chǔ)引擎只根據(jù)id<5,過(guò)濾了其他的行贴浙，返回給服務(wù)層的是id<5的行砂吞，服務(wù)層再用后面的where條件再進(jìn)行過(guò)濾，所以id=1的行也是被加上了排他鎖的崎溃。
說(shuō)明即使使用了索引也會(huì)導(dǎo)致多余的行鎖定蜻直，如果不適用索引，全表掃描袁串，那將導(dǎo)致鎖定所有的行概而。InnoDB在二級(jí)索引上使用讀鎖，即共享鎖囱修，在主鍵索引赎瑰，即查找聚簇索引是使用寫(xiě)鎖，即排他鎖破镰。
5.4索引案例學(xué)習(xí)
案例：一個(gè)在線交友網(wǎng)站餐曼，用戶(hù)信息表有很多列，包括國(guó)家鲜漩、地區(qū)源譬、城市、性別孕似、眼睛顏色等踩娘，網(wǎng)站必須支持上面這些特征的各種組合來(lái)搜索用戶(hù)，還允許根據(jù)用戶(hù)最后在線時(shí)間喉祭、其他會(huì)員的評(píng)分等對(duì)用戶(hù)進(jìn)行排序和對(duì)結(jié)果限制养渴。
首先要考慮用索引排序還是先檢索數(shù)據(jù)再排序雷绢，用索引排序?qū)?huì)嚴(yán)格限制索引和排序的設(shè)計(jì)。
5.4.1支持多種過(guò)濾條件
首先厚脉，國(guó)家和性別這兩列選擇性都不高习寸，但是在查詢(xún)中會(huì)經(jīng)常用到∩倒ぃ可以將（性別霞溪，國(guó)家）作為索引前綴。這與我們之前說(shuō)的選擇性地的列不適合索引沖突了嗎中捆？其實(shí)當(dāng)我們?nèi)绻恍枰x擇性別鸯匹，我們可以使用IN的操作，來(lái)讓查詢(xún)跳過(guò)性別這一項(xiàng)泄伪，同時(shí)能用到索引之后的列殴蓬。比如可以在查詢(xún)條件里加入SEX IN('m','f')，讓查詢(xún)選擇到該索引蟋滴，這樣寫(xiě)不會(huì)過(guò)濾任何行染厅，但是加上這一列的條件會(huì)讓MySQL匹配索引的最左前綴。
最常用的范圍查詢(xún)的列放在索引的最后
當(dāng)設(shè)計(jì)索引時(shí)津函，應(yīng)該考慮表上所有的選項(xiàng)肖粮，不要只為現(xiàn)有的查詢(xún)考慮需要哪些索引。應(yīng)該同時(shí)優(yōu)化查詢(xún)和索引找到最佳的平衡尔苦。
5.6總結(jié)
選擇索引和利用這些索引時(shí)的三個(gè)原則：
1涩馆、單行訪問(wèn)是很慢的。
2允坚、按順序訪問(wèn)范圍數(shù)據(jù)是很快的魂那，因?yàn)轫樞騃O不需要多次磁盤(pán)尋道，其次按照順序讀取數(shù)據(jù)不需要額外的排序操作稠项。
3涯雅、索引覆蓋查詢(xún)是很快的，因?yàn)椴恍枰乇聿檎倚小?/p>
如何判斷一個(gè)系統(tǒng)創(chuàng)建的索引是否合理展运？
按照響應(yīng)時(shí)間對(duì)查詢(xún)進(jìn)行分析斩芭，找出消耗時(shí)間最長(zhǎng)的查詢(xún)，分析查詢(xún)是否掃描了太多行乐疆，是否做了額外排序或使用了臨時(shí)表，是否使用了隨機(jī)IO訪問(wèn)數(shù)據(jù)贬养，或者是否有太多的回表查詢(xún)哪些不在索引中的列的操作挤土。
第六章查詢(xún)性能優(yōu)化
查詢(xún)優(yōu)化、索引優(yōu)化误算、庫(kù)表結(jié)構(gòu)優(yōu)化要齊頭并進(jìn)仰美。
6.1為什么查詢(xún)速度會(huì)慢
真正重要的是響應(yīng)時(shí)間迷殿。查詢(xún)由一系列子任務(wù)組成，優(yōu)化查詢(xún)咖杂，要么消除一些子任務(wù)庆寺，要么減少子任務(wù)的次數(shù)，要么讓子任務(wù)更快運(yùn)行诉字。
查詢(xún)的生命周期：從客戶(hù)端懦尝，到服務(wù)器，解析壤圃，生成執(zhí)行計(jì)劃陵霉，執(zhí)行，返回客戶(hù)端伍绳。
執(zhí)行里面包括了存儲(chǔ)引擎的調(diào)用踊挠，以及調(diào)用后數(shù)據(jù)的處理（排序、分組等）冲杀。
查詢(xún)要在不同的地方花費(fèi)時(shí)間效床，包括網(wǎng)絡(luò)、CPU計(jì)算权谁、生成統(tǒng)計(jì)信息和執(zhí)行計(jì)劃剩檀，等待鎖等。在存儲(chǔ)引擎檢索數(shù)據(jù)的時(shí)候闯传，需要操作內(nèi)存谨朝、CPU,內(nèi)存不足的時(shí)候，IO也要消耗更多的時(shí)間甥绿。
6.2慢查詢(xún)基礎(chǔ)
對(duì)低效的查詢(xún)字币，主要分析：1、確定應(yīng)用程序是否檢索了大量超過(guò)需要的數(shù)據(jù)共缕；2洗出、確定MySQL服務(wù)器是否分析了大量超過(guò)需要的數(shù)據(jù)行。
6.2.1是否向服務(wù)器請(qǐng)求了不需要的數(shù)據(jù)
查詢(xún)了不需要的記錄
比如說(shuō)图谷，只查詢(xún)某一頁(yè)數(shù)據(jù)翩活，但是實(shí)際查詢(xún)了所有的，只顯示第一頁(yè)便贵。解決辦法是加LIMIT.
多表關(guān)聯(lián)時(shí)返回了全部的列
返回多個(gè)表的全部列是不需要的菠镇。
總是取出全部列
每次都select *,首先可能有的列不是必須的，其次查詢(xún)所有列將不能使用覆蓋索引優(yōu)化查詢(xún)承璃。
如果清楚這樣做的代價(jià)利耍，并使用了其他的解決方法，也是可以的。比如使用了緩存的機(jī)制隘梨，查詢(xún)了所有列有時(shí)也會(huì)帶來(lái)方便程癌。
重復(fù)查詢(xún)相同的數(shù)據(jù)
比如用戶(hù)頭像的URL,可以不用每次都查詢(xún)，可以使用緩存轴猎。
6.2.2MySQL是否在掃描額外的記錄
對(duì)MySQL嵌莉，簡(jiǎn)單的衡量查詢(xún)開(kāi)銷(xiāo)的三個(gè)指標(biāo)：
1、響應(yīng)時(shí)間捻脖；2锐峭、掃描的行數(shù)；3郎仆、返回的行數(shù)
這三個(gè)指標(biāo)會(huì)記錄到慢查詢(xún)?nèi)罩纠锩妗?/p>
響應(yīng)時(shí)間
響應(yīng)時(shí)間=服務(wù)時(shí)間（真正查詢(xún)使用的時(shí)間）+排隊(duì)時(shí)間（等待IO,等待鎖的時(shí)間）
掃描的行數(shù)和返回的行數(shù)
理性狀態(tài)只祠，掃描行和返回行應(yīng)該是1比1，但是通常做一個(gè)關(guān)聯(lián)查詢(xún)時(shí)必須要掃描多行才能得到一行結(jié)果扰肌，比值在1:1到10:1抛寝，甚至更大。
掃描的行數(shù)和訪問(wèn)類(lèi)型
訪問(wèn)類(lèi)型就是EXPLAIN測(cè)試中的type的類(lèi)型：掃描的范圍從大到小的順序是：全表掃描（ALL）曙旭、索引掃描(index)盗舰、范圍掃描(range)、唯一索引掃描(ref)桂躏、常數(shù)引用(const)等钻趋。
索引可以讓存儲(chǔ)引擎掃描更少的數(shù)據(jù)行，以提高查詢(xún)性能.
MySQL使用where條件有三種方式剂习，從好到壞：
1蛮位、在索引中使用where條件，這樣在存儲(chǔ)引擎層就能過(guò)濾不匹配的記錄鳞绕，使掃描的數(shù)據(jù)行減少失仁。
2、使用索引覆蓋掃描返回記錄们何，直接在索引中過(guò)濾過(guò)濾不需要的記錄萄焦，而且不用回表再查詢(xún)，這是在MySQL服務(wù)層實(shí)現(xiàn)的冤竹。
3拂封、從數(shù)據(jù)表返回?cái)?shù)據(jù)，再再M(fèi)ySQL服務(wù)層使用where條件過(guò)濾鹦蠕，這是最差的情況冒签，掃描的行數(shù)是最多的，在extra選項(xiàng)钟病，會(huì)出現(xiàn)using where.
6.3重構(gòu)查詢(xún)的方式
1镣衡、可以將查詢(xún)改變一種寫(xiě)法霜定，讓他返回一樣的結(jié)果但是性能更好。
2廊鸥、在業(yè)務(wù)層修改代碼，使用另一種查詢(xún)方式辖所。達(dá)到一樣的目的惰说。
6.3.1一個(gè)復(fù)雜查詢(xún)還是多個(gè)簡(jiǎn)單查詢(xún)
6.3.2切分查詢(xún)
6.3.3分解關(guān)聯(lián)查詢(xún)
對(duì)關(guān)聯(lián)查詢(xún)進(jìn)行分解，將一個(gè)關(guān)聯(lián)查詢(xún)分解成多個(gè)單表的查詢(xún)會(huì)有以下的好處：
1缘回、更有效的使用緩存吆视。關(guān)聯(lián)查詢(xún)一但有表發(fā)生變化，緩存將失效酥宴。分成多個(gè)單表查詢(xún)可以盡可能保留一些可用的緩存啦吧。
2、查詢(xún)分解之后拙寡，執(zhí)行單個(gè)查詢(xún)可以減少鎖的競(jìng)爭(zhēng)授滓。
3、在應(yīng)用層做關(guān)聯(lián)肆糕，更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分般堆，更容易做到高性能和可擴(kuò)展。
4诚啃、減少冗余的查詢(xún)
在很多場(chǎng)景下淮摔，通過(guò)重構(gòu)查詢(xún)將關(guān)聯(lián)放在應(yīng)用程序里會(huì)更加高效。
6.4查詢(xún)執(zhí)行的基礎(chǔ)
執(zhí)行查詢(xún)的過(guò)程：先查緩存始赎，緩存沒(méi)有的話(huà)到解析器和橙，解析SQL,預(yù)處理器，到查詢(xún)優(yōu)化器造垛，生成執(zhí)行計(jì)劃魔招，再到存儲(chǔ)引擎調(diào)用API返回結(jié)果。
6.4.1MySQL客戶(hù)端/服務(wù)器通信協(xié)議
“半雙工”
MySQL通常需要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶(hù)端才能釋放這條查詢(xún)占用的資源筋搏，所以接收全部結(jié)果并緩存通称桶伲可以減少服務(wù)器的壓力，讓查詢(xún)能早點(diǎn)結(jié)束，早點(diǎn)釋放響應(yīng)的資源铜幽。
當(dāng)需要返回一個(gè)很大的結(jié)果集時(shí)沪铭，緩存到內(nèi)存會(huì)花費(fèi)很多時(shí)間并消耗內(nèi)存，這時(shí)可以直接處理峦朗，不用緩存記錄結(jié)果，但是缺點(diǎn)是排龄，服務(wù)端要一直等待查詢(xún)完成波势。
查詢(xún)狀態(tài)
查詢(xún)線程的狀態(tài)：
Sleep:等待客戶(hù)端發(fā)送請(qǐng)求
Query:正在執(zhí)行查詢(xún)或發(fā)送結(jié)果給客戶(hù)端
locked:等待表鎖，等待InnoDB的行鎖不會(huì)體現(xiàn)在線程狀態(tài)里
Analyzing and statistics:統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃
Copying to tmp table [on disk]:將查詢(xún)結(jié)果放到臨時(shí)表，通常GROUP BY ,文件排序尺铣，或者UNION操作.
Sorting result:線程對(duì)查詢(xún)結(jié)果排序
Sending data：可能在多個(gè)狀態(tài)發(fā)送數(shù)據(jù)拴曲，或者在生成結(jié)果集，或者向客戶(hù)端返回結(jié)果凛忿。
6.4.2查詢(xún)緩存
如果查詢(xún)命中緩存澈灼，如果權(quán)限沒(méi)有問(wèn)題，將不執(zhí)行后面的步驟店溢，直接從緩存中拿結(jié)果返回叁熔。
6.4.3查詢(xún)優(yōu)化處理
將SQL轉(zhuǎn)換成一個(gè)執(zhí)行計(jì)劃，與存儲(chǔ)引擎交互床牧。包括解析SQL荣回、預(yù)處理、優(yōu)化SQL執(zhí)行計(jì)劃
語(yǔ)法解析器和預(yù)處理：生成一顆解析數(shù)戈咳，語(yǔ)法規(guī)則驗(yàn)證和解析查詢(xún)心软，預(yù)處理會(huì)驗(yàn)證權(quán)限
查詢(xún)優(yōu)化器
一條查詢(xún)會(huì)有多種執(zhí)行計(jì)劃，優(yōu)化器找到最好的一種除秀。優(yōu)化器會(huì)預(yù)測(cè)成本糯累，依次來(lái)選擇，計(jì)算成本的時(shí)候不會(huì)考慮緩存册踩。
MySQL的優(yōu)化器選擇的最優(yōu)與我們想要的最優(yōu)可能不一樣泳姐，而且它不會(huì)考慮并發(fā)執(zhí)行的查詢(xún)對(duì)當(dāng)前查詢(xún)的影響。優(yōu)化器的策略有靜態(tài)優(yōu)化和動(dòng)態(tài)優(yōu)化暂吉。
MySQL可以處理的優(yōu)化類(lèi)型：
1胖秒、重新定義關(guān)聯(lián)表的順序
2、將外連接轉(zhuǎn)化為內(nèi)連接
3慕的、使用等價(jià)變換規(guī)則
4阎肝、優(yōu)化count(*)、min()和max()
5肮街、預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式
6风题、覆蓋索引掃描
7、子查詢(xún)優(yōu)化
8嫉父、提前終止查詢(xún)：LIMIT
9沛硅、等值傳播
10、列表IN()的比較：IN()和多個(gè)OR意思是相同的绕辖，但是MySQL對(duì)IN()的處理是先對(duì)IN（）列表排序摇肌，再使用二分查找確定是否滿(mǎn)足，時(shí)間復(fù)雜度logn,比OR的n快仪际。
數(shù)據(jù)和索引的統(tǒng)計(jì)信息
數(shù)據(jù)和索引的額統(tǒng)計(jì)信息是在存儲(chǔ)引擎里的围小，生成執(zhí)行計(jì)劃時(shí)要向存儲(chǔ)引擎獲取這些信息昵骤。
MySQL如何執(zhí)行關(guān)聯(lián)查詢(xún)
UNION的例子。
先將單個(gè)查詢(xún)放到一個(gè)臨時(shí)表肯适，然后再重新讀出臨時(shí)表數(shù)據(jù)完成UNION查詢(xún)变秦，執(zhí)行嵌套循環(huán)關(guān)聯(lián)。
執(zhí)行計(jì)劃
生成一顆指令樹(shù)框舔，執(zhí)行指令樹(shù)返回結(jié)果伴栓。
關(guān)聯(lián)查詢(xún)優(yōu)化器
選擇代價(jià)最小的關(guān)聯(lián)順序，進(jìn)行更少的循環(huán)嵌套和回溯操作雨饺。
排序優(yōu)化
應(yīng)避免排序或避免對(duì)大量數(shù)據(jù)排序。
如果不能用索引生成排序結(jié)果惑淳，就要執(zhí)行排序filesort额港。
如果數(shù)據(jù)量小于“排序緩沖區(qū)”，就直接在內(nèi)存快速排序歧焦，如果大于緩沖區(qū)移斩，要先分塊，各個(gè)排序然后合并绢馍，再返回向瓷。
MySQL的兩種排序算法：
（1）兩次傳輸排序（舊版本）：讀取航指針和需要排序的列，排序完了再回表讀數(shù)據(jù)行舰涌。第二次回表是隨機(jī)IO,效率低猖任。
（2）單次傳輸排序（新版本使用）：先讀取所有查詢(xún)需要的列，對(duì)需要排序的列排序瓷耙，然后直接返回朱躺。只有一次順序IO.但會(huì)占用更多的空間「橥矗可以設(shè)置參數(shù)max_length_for_sort_data.只要所有列不超過(guò)這個(gè)空間长搀，使用單次傳輸是更有效的，否則也會(huì)出現(xiàn)分塊鸡典，分別排序的操作源请，降低效率。
MySQL在進(jìn)行文件排序時(shí)需要的臨時(shí)存儲(chǔ)空間比想象的要大得多彻况。
關(guān)聯(lián)查詢(xún)中如果group by子句的所有列都來(lái)自關(guān)聯(lián)的第一個(gè)表谁尸，那么MySQL在關(guān)聯(lián)查詢(xún)第一個(gè)表時(shí)就會(huì)filesort,除此之外是放到一個(gè)臨時(shí)表，在關(guān)聯(lián)結(jié)束后再filesort,這時(shí)extra會(huì)有Using temporary疗垛，Using filesort.
查詢(xún)執(zhí)行引擎
執(zhí)行計(jì)劃生成后症汹，調(diào)用存儲(chǔ)引擎的handler API接口，逐步執(zhí)行
返回結(jié)果給客戶(hù)端
查詢(xún)結(jié)果返回給客戶(hù)端贷腕，如果查詢(xún)可以被緩存背镇，那么也會(huì)把結(jié)果存放到緩存里
6.5MySQL查詢(xún)優(yōu)化器的局限性
6.5.1關(guān)聯(lián)子查詢(xún)
如何用好關(guān)聯(lián)子查詢(xún)
6.5.2UNION的限制
6.5.3索引合并優(yōu)化
6.5.4等值傳遞
6.5.5并行執(zhí)行
MySQL無(wú)法利用多核特性來(lái)并行執(zhí)行查詢(xún)
6.5.6哈希關(guān)聯(lián)
MySQL不支持
6.5.7松散索引掃描
MySQL不支持
6.5.8最大值和最小值優(yōu)化
例如
first_name 沒(méi)有建索引咬展，將會(huì)全表掃描，對(duì)主鍵掃描瞒斩，理論上找到第一條滿(mǎn)足條件的就返回了最小值破婆，因?yàn)橹麈I是按大小排序的。但是MySQL還是會(huì)做全表掃描胸囱。
可以換一種寫(xiě)法：
雖然不能再SQL語(yǔ)句上看到我們查詢(xún)的意圖祷舀，但是可以減少掃描的行數(shù)。
6.5.9同一個(gè)表上的查詢(xún)與更新
改成生成表的形式
6.7優(yōu)化特定類(lèi)型的查詢(xún)
6.7.1優(yōu)化count()查詢(xún)
COUNT()的作用
統(tǒng)計(jì)不為NULL的列值的數(shù)量或者統(tǒng)計(jì)行數(shù)烹笔。
如果COUNT()的括號(hào)中有列和列的表達(dá)式裳扯，則表示的是統(tǒng)計(jì)表達(dá)式有值的結(jié)果數(shù)。
COUNT（*）不會(huì)擴(kuò)展到所有的列谤职，會(huì)直接統(tǒng)計(jì)行數(shù)饰豺。
如果MySQL知道某個(gè)列不可能為NULL,就會(huì)把COUNT（列）優(yōu)化為COUNT（*）
MyISAM對(duì)COUNT（*）的性能優(yōu)勢(shì)只體現(xiàn)在沒(méi)有where條件的情況。
有時(shí)查詢(xún)id>某個(gè)值允蜈，可以轉(zhuǎn)換成查詢(xún)id<某個(gè)值的查詢(xún)來(lái)轉(zhuǎn)換冤吨，減少掃描的行數(shù)。
6.7.2優(yōu)化關(guān)聯(lián)查詢(xún)
1饶套、確保ON或者Using子句中的列上有索引漩蟆。一般來(lái)說(shuō)，除非有其他理由妓蛮，否則只需要在關(guān)聯(lián)順序中的第二個(gè)表的相應(yīng)列上創(chuàng)建索引怠李。
2、確保任何group by和order by中的表達(dá)式只涉及到一個(gè)表中的列仔引，這樣才有可能用到索引扔仓。
3、升級(jí)的時(shí)候注意
6.7.3優(yōu)化子查詢(xún)
子查詢(xún)盡可能的用關(guān)聯(lián)查詢(xún)代替
6.7.4優(yōu)化GROUP BY 和DISTINCT
如果無(wú)法只用索引咖耘，group by使用兩種策略完成：使用臨時(shí)表或者文件排序做分組翘簇。
如果需要對(duì)關(guān)聯(lián)查詢(xún)做分組，并且是按照查找表的某個(gè)列進(jìn)行分組儿倒，那么通常采用查找表的標(biāo)識(shí)列分組的效率會(huì)比其他列更高版保，例如：
這么做的前提是查詢(xún)的列是直接依賴(lài)于分組的列，并且是唯一的夫否，不會(huì)影響結(jié)果彻犁。
在group by之后不要排序，默認(rèn)不寫(xiě)order by的話(huà)會(huì)導(dǎo)致默認(rèn)的排序凰慈，所以可以加上order by null,分組后不再排序汞幢。
“延遲關(guān)聯(lián)”：讓MySQL先掃描盡可能少的頁(yè)面，再回原表查詢(xún)所有列微谓。
6.7.7優(yōu)化UNION查詢(xún)
第七章MySQL高級(jí)特性
7.1分區(qū)表
分區(qū)表是一個(gè)獨(dú)立的邏輯表森篷，底層由多個(gè)物理子表組成输钩。
分區(qū)的一個(gè)主要目的就是將數(shù)據(jù)按照一個(gè)較粗的粒度分在不同的表中。這樣可以將相關(guān)的數(shù)據(jù)存放在一起仲智，另外买乃，如果想一次批量刪除整個(gè)分區(qū)的數(shù)據(jù)也會(huì)變的很方便。
分區(qū)表的限制：
1钓辆、一個(gè)表最多只能有1024個(gè)分區(qū)
2剪验、如果分區(qū)表中有主鍵或者唯一索引的列，那么所有的唯一索引列都必須包含進(jìn)來(lái)
3前联、分區(qū)表無(wú)法使用外鍵約束功戚。
7.1.1分區(qū)表的原理
7.1.2分區(qū)表的類(lèi)型
最多的是根據(jù)范圍分區(qū)，分區(qū)表達(dá)式可以是列也可以是包含列的表達(dá)式似嗤。
MySQL還支持鍵值疫铜、哈希和列表分區(qū)。
7.1.3如何使用分區(qū)表
在數(shù)據(jù)量超大的時(shí)候双谆，B-Tree索引就無(wú)法起作用的，除非是覆蓋索引席揽，否則根據(jù)索引回表查詢(xún)將差生大量的隨機(jī)IO,索引的維護(hù)成本也非常大顽馋。所以要分區(qū)，以非常小的代價(jià)定位到一小片區(qū)域幌羞，在這片區(qū)域可以順序掃描寸谜，可以建索引，可以全部緩存到內(nèi)存属桦。
因?yàn)榉謪^(qū)無(wú)須額外的數(shù)據(jù)結(jié)構(gòu)記錄分區(qū)有哪些數(shù)據(jù)熊痴，分區(qū)也不需要精確定位每一條數(shù)據(jù)位置，所以代價(jià)非常低聂宾。
為保證大數(shù)據(jù)量的可擴(kuò)展性果善，有兩種策略：
1、全量掃描數(shù)據(jù)系谐，不要任何索引
2巾陕、索引數(shù)據(jù)，并分離熱點(diǎn)纪他。將這部分?jǐn)?shù)據(jù)單獨(dú)放在一個(gè)分區(qū)中鄙煤，讓這個(gè)分區(qū)的數(shù)據(jù)有機(jī)會(huì)緩存到內(nèi)存中。這樣查詢(xún)只用訪問(wèn)一個(gè)很小的分區(qū)表茶袒，能夠使用索引梯刚，也能有效使用緩存。
7.1.4什么情況下會(huì)出問(wèn)題
上面的兩個(gè)分區(qū)策略基于兩個(gè)假設(shè)：查詢(xún)能夠過(guò)濾大量的分區(qū)薪寓，分區(qū)本身代價(jià)很小亡资。
但是在一下情況會(huì)出問(wèn)題：
1澜共、NULL值會(huì)使分區(qū)過(guò)濾無(wú)效
因?yàn)榉謪^(qū)表達(dá)式的值可以是NULL,有第一個(gè)分區(qū)是專(zhuān)門(mén)存分區(qū)表達(dá)式是NULL或者非法值的地方。所以查找分區(qū)的時(shí)候會(huì)找到兩個(gè)分區(qū)沟于，一個(gè)是第一個(gè)分區(qū)咳胃，一個(gè)是符合過(guò)濾條件的分區(qū)。這時(shí)旷太，入股第一個(gè)分區(qū)非常大展懈，沒(méi)有索引就會(huì)代價(jià)很大。為了解決可以創(chuàng)建一個(gè)無(wú)用的分區(qū)供璧，這樣即使要檢測(cè)它也是空的存崖，MySQL5.5之后可以直接使用列本身分區(qū)。
2睡毒、分區(qū)列和索引列不匹配
3来惧、掃描分區(qū)的索引可能很高
4、打開(kāi)并鎖住底層表的成本可能很高
5演顾、維護(hù)分區(qū)的成本可能很高
7.1.5查詢(xún)優(yōu)化
分區(qū)給查詢(xún)優(yōu)化帶來(lái)了新的思路供搀，用粗粒度的索引使查詢(xún)掃描更少的行。很重要的一點(diǎn)就是查詢(xún)的時(shí)候要帶上分區(qū)列钠至，即使多余也要帶上葛虐。如果過(guò)濾條件是列的表達(dá)式，那也不能過(guò)濾分區(qū)棉钧。
即便在創(chuàng)建分區(qū)的時(shí)候使用了列的表達(dá)式屿脐，但是查詢(xún)的時(shí)候必須使用列來(lái)過(guò)濾
7.1.6合并表
7.2視圖
視圖本身是虛擬的表，不存放任何數(shù)據(jù)宪卿，它返回的數(shù)據(jù)是MySQL從其他表中生成的的诵。
實(shí)現(xiàn)視圖最簡(jiǎn)單的方法就是將select的結(jié)果放在臨時(shí)表中，當(dāng)訪問(wèn)視圖的時(shí)候直接仿問(wèn)這個(gè)臨時(shí)表就可以了西疤。
MySQL可以使用兩種方法處理視圖休溶，合并算法和臨時(shí)表算法。如果可能盡量使用合并算法管跺，如果使用了臨時(shí)表算法禾进，EPLAIN中會(huì)顯示派生表（DERIVED）
7.2.1可更新視圖
可以通過(guò)更新視圖來(lái)更新涉及的表。
7.2.2視圖對(duì)性能的影響
某些情況可以提升性能
7.2.3視圖的限制
不會(huì)保存視圖定義的原始SQL
7.3外鍵約束
InnoDB是MySQL中唯一支持外鍵的內(nèi)置存儲(chǔ)引擎艇拍。
使用外鍵是有成本的。在每次修改數(shù)據(jù)時(shí)都要在另一張表執(zhí)行一次查找操作卸夕，雖然InnoDB強(qiáng)制外鍵索引层释，但是還是會(huì)有開(kāi)銷(xiāo)，如果外鍵的選擇性很低快集，效率將更低贡羔。
如果想要確保兩個(gè)相關(guān)的表始終有一致的數(shù)據(jù)，使用外鍵會(huì)比在應(yīng)用程序中檢查一致性性能要高的多个初。此外外鍵在相關(guān)的數(shù)據(jù)的刪除和更新上也比在應(yīng)用層中維護(hù)要高效乖寒，不過(guò)外鍵操作時(shí)逐行進(jìn)行的。
外鍵約束會(huì)在查詢(xún)時(shí)會(huì)對(duì)另外的表的記錄加鎖院溺，這樣會(huì)導(dǎo)致額外的鎖等待楣嘁，可能導(dǎo)致一些死鎖，并且難以排查珍逸，有時(shí)可以用觸發(fā)器代替外鍵逐虚。對(duì)于相關(guān)數(shù)據(jù)的同時(shí)更新，外鍵更加合適谆膳，但如果只是約束數(shù)值痊班，可以用觸發(fā)器，也可以直接用ENUM類(lèi)型摹量。
如果只是用外鍵約束，那可以不用外鍵馒胆，在應(yīng)用層實(shí)現(xiàn)約束缨称。外鍵會(huì)帶來(lái)很大的額外消耗。
7.4在MySQL內(nèi)部存儲(chǔ)代碼
在MySQL里面可以用觸發(fā)器祝迂、存儲(chǔ)過(guò)程睦尽、函數(shù)的形式存儲(chǔ)代碼。
優(yōu)點(diǎn)：1型雳、存儲(chǔ)代碼就在服務(wù)器內(nèi)部執(zhí)行当凡，離數(shù)據(jù)最近，可以節(jié)省帶寬和網(wǎng)絡(luò)延遲
2朴则、代碼重用乌妒，方便統(tǒng)一業(yè)務(wù)規(guī)則
3古掏、簡(jiǎn)化代碼維護(hù) 和更新
4槽唾、提升安全，做更細(xì)粒度的權(quán)限控制挂绰。通過(guò)存儲(chǔ)過(guò)程訪問(wèn)那些沒(méi)有權(quán)限的表。
缺點(diǎn)：
1践付、編寫(xiě)存儲(chǔ)代碼沒(méi)有很好的工具
2永高、存儲(chǔ)過(guò)程中的代碼功能要比應(yīng)用層代碼差
3命爬、給部署帶來(lái)復(fù)雜性
4、消耗服務(wù)器資源嗜价，調(diào)試?yán)щy
7.4.1存儲(chǔ)過(guò)程和函數(shù)
7.4.2觸發(fā)器
可以讓你在Insert家淤、Update或者Delete的時(shí)候執(zhí)行一些特殊操作媒鼓，可以指定觸發(fā)器在SQL之前還是之后觸發(fā)疚沐，可以讀取或者改變SQL語(yǔ)句所影響的數(shù)據(jù)亮蛔。所以可以用觸發(fā)器實(shí)現(xiàn)一些強(qiáng)制限制，或者一些業(yè)務(wù)邏輯芬探。用觸發(fā)器設(shè)計(jì)簡(jiǎn)單的業(yè)務(wù)邏輯可以減少客戶(hù)服務(wù)端之間的通信偷仿，提高性能。
但是觸發(fā)器别智，一個(gè)表的一個(gè)事件只能有一個(gè)觸發(fā)器薄榛，而且觸發(fā)器是針對(duì)于行的觸發(fā)，就是一條記錄。
在InnoDB表上的觸發(fā)器是在同一個(gè)事務(wù)中完成的倚评，她的執(zhí)行操作是原子的天梧，與原SQL操作同時(shí)成功或失敗『笤ィ可以用觸發(fā)器做一些數(shù)據(jù)更改的日志的記錄操作构眯。但是在維護(hù)數(shù)據(jù)一致性的時(shí)候惫霸，還是不要使用觸發(fā)器，小心MVCC,稍不小心就會(huì)得到錯(cuò)誤的結(jié)果硅卢。
7.4.3事件
CALL調(diào)用老赤。
7.5游標(biāo)
7.6綁定變量
7.7用戶(hù)自定義函數(shù)
7.8插件
7.9字符集和校對(duì)
字符集是指一種從二進(jìn)制編碼到某類(lèi)字符符號(hào)的映射抬旺。每個(gè)字符集可能有多種校對(duì)規(guī)則
7.10全文索引
關(guān)鍵字匹配進(jìn)行查詢(xún)過(guò)濾，非精確過(guò)濾责鳍。
自然語(yǔ)言的全文索引
MATCH AGAINST
布爾全文索引
7.11分布式事務(wù)（XA）
分布式事務(wù)讓存儲(chǔ)引擎級(jí)別的ACID可以擴(kuò)展到數(shù)據(jù)庫(kù)層面。甚至可以擴(kuò)展到多個(gè)數(shù)據(jù)庫(kù)之間恤溶。需要兩個(gè)階段提交實(shí)現(xiàn)：
1、是需要一個(gè)事務(wù)協(xié)調(diào)器保證所有事務(wù)的參與者都完成準(zhǔn)備工作
2帐姻、協(xié)調(diào)器收到準(zhǔn)備好的消息剥纷，通知所有事務(wù)提交筷畦。
7.11.1內(nèi)部分布式事務(wù)
跨存儲(chǔ)引擎
7.11.2外部分布式事務(wù)
是一種在多個(gè)服務(wù)器之間同步數(shù)據(jù)的方法
7.12查詢(xún)緩存
可以緩存查詢(xún)的執(zhí)行計(jì)劃逆航。
MySQL還可以緩存select的查詢(xún)結(jié)果因俐。查詢(xún)緩存會(huì)跟蹤查詢(xún)中涉及的表撑帖，一旦表變化胡嘿，緩存將失效衷敌。這種實(shí)現(xiàn)代價(jià)小。
查詢(xún)緩存被發(fā)現(xiàn)是一個(gè)影響服務(wù)器擴(kuò)展性的因素。很多時(shí)候默認(rèn)應(yīng)該關(guān)閉緩存舌界，或者配置一個(gè)很小的緩存空間（幾十兆）
7.12.1MySQL如何判斷緩存命中
緩存放在一個(gè)引用表禀横，通過(guò)哈希值引用屁药，哈希值包含查詢(xún)粥血、數(shù)據(jù)庫(kù)柏锄、版本等可能會(huì)影響返回結(jié)果的信息。
如果查詢(xún)中包含任何不確定的函數(shù)复亏，那么在查詢(xún)緩存中是不可能找到緩存結(jié)果的趾娃。
緩存對(duì)于讀寫(xiě)都有性能影響，然后查詢(xún)緩存的操作是一個(gè)排他的操作缔御，會(huì)帶來(lái)額外的消耗。
在事務(wù)運(yùn)行時(shí)會(huì)限制緩存的使用上祈。
7.12.2查詢(xún)緩存如何使用內(nèi)存
完全存儲(chǔ)在內(nèi)存中。緩存的管理維護(hù)的數(shù)據(jù)結(jié)構(gòu)大概花40KB的內(nèi)存資源。服務(wù)器啟動(dòng)的時(shí)候先初始化緩存需要的內(nèi)存傅寡，當(dāng)有查詢(xún)結(jié)果需要緩存的時(shí)候，MySQL無(wú)法為每個(gè)查詢(xún)結(jié)果分配合適的空間。而且分配空間的操作會(huì)鎖住內(nèi)存塊。
7.12.3什么情況下緩存能發(fā)揮作用
打開(kāi)或者關(guān)閉系統(tǒng)緩存讯壶，來(lái)比較系統(tǒng)效率匀借。
7.12.5InnoDB和查詢(xún)緩存
InnoDB有自己的MVCC.
只有大于表的計(jì)數(shù)器的事務(wù)才可以使用查詢(xún)緩存肤舞。
InnoDB讓所有有加鎖操作的事務(wù)都不使用任何查詢(xún)緩存充择。
7.12.6通用查詢(xún)緩存優(yōu)化
第十章復(fù)制
MySQL的復(fù)制功能是構(gòu)建基于MySQL的大規(guī)模、高性能應(yīng)用的基礎(chǔ)谜疤。
可以為服務(wù)器配置一個(gè)或多個(gè)備庫(kù)的方式绵跷，進(jìn)行數(shù)據(jù)同步像啼，有利于實(shí)現(xiàn)高可用性、可擴(kuò)展性赶么、容災(zāi)恢復(fù)匾寝、備份以及數(shù)據(jù)倉(cāng)庫(kù)等工作乔外。
1莲组、復(fù)制如何工作
2迈着、復(fù)制服務(wù)搭建
3、復(fù)制的配置以及管理優(yōu)化
10.1復(fù)制概述
MySQL有兩種復(fù)制方式：基于行的復(fù)制和基于語(yǔ)句的復(fù)制扫腺。都是在主庫(kù)上記錄二進(jìn)制日志，在備庫(kù)上異步重現(xiàn)熙宇，實(shí)現(xiàn)異步的數(shù)據(jù)復(fù)制鳖擒。
