超長文，慎入！
這是我大二暑假讀《高性能MySQL》的筆記，當時分篇記錄在了blogspot的博客上，現(xiàn)在那個博客已經(jīng)廢棄，今天爬籬笆的時候無意間看到，就把它整理了一下放到這里來。

MySQL查詢性能優(yōu)化

查詢性能低下的最基本原因就是訪問了太多數(shù)據(jù)。在分析性能欠佳的查詢的時候，下面兩個步驟比較有用：
1.查明應用程序是否正在獲取超過需要的數(shù)據(jù)。這通常意味著訪問了過多的行或列。
2.查明MySQL服務器是否分析了超過需要的行。

當我們找到有問題的查詢語句時，我們就應該重構(gòu)它，下面是一些重構(gòu)查詢的技巧及使用它們的時機。
1.縮短查詢
一種處理查詢的辦法是分治法，讓查詢的本質(zhì)上不變，但是每次只執(zhí)行一小部分，以減少受影響的行數(shù)。
比如，下面是一個巨大的查詢：

應該用類似于下面的偽代碼的查詢替換它：

足夠短的任務對服務器的影響最小。在DELETE語句中加入休眠語句也是一個好主意，它可以分攤負載，并且減少鎖住資源的時間。
2.分解聯(lián)接
許多高性能的網(wǎng)站都用了“分解聯(lián)接”技術(shù)，可以把一個多表聯(lián)接分解成多個單個查詢，然后在應用程序端實現(xiàn)聯(lián)接操作。

可以用下面的語句代替：

這種重構(gòu)方式有下面這些重大的性能優(yōu)勢：
a.緩存的效率更高。如果只有一個表經(jīng)常改變，那么分解聯(lián)接就可以減少緩存失效的次數(shù)。
b.對于MyISAM表來說，每個表一個查詢可以更有效地利用表鎖，因為查詢會鎖住單個表較短時間，而不是把所有表長時間鎖住。
c.在應用程序端進行聯(lián)接可以更方便地擴展數(shù)據(jù)庫，把不同的表放在不同的服務器上面。
d.查詢本身會更高效。
e.可以減少多余的行訪問。在應用程序端進行聯(lián)接意味著對每行數(shù)據(jù)只會訪問一次，而聯(lián)接從本質(zhì)上來說是非正則化的，它會反復地訪問同一行數(shù)據(jù)?；谕瑯拥脑?，這種重構(gòu)方式可以減少網(wǎng)絡流量和內(nèi)存消耗。

想得到高性能，最佳的方式就是學習MySQL如何優(yōu)化和執(zhí)行查詢。
MySQL使用的是基于開銷（Cost）的優(yōu)化器，這意味著它會預測不同執(zhí)行計劃的開銷，并且選擇開銷最小的一個。開銷的單位是一次對大小為4KB的頁面的隨機讀取。
優(yōu)化器不會考慮任何緩存因素，它認為每次讀取都會有相同的IO開銷。
由于種種原因，優(yōu)化器并不總是能選擇最好的方案：
1.統(tǒng)計數(shù)據(jù)可能是錯誤的。
2.開銷指標和運行查詢的實際開銷并不精確相等。
3.MySQL的優(yōu)化并不總是和我們想的一樣。它只考慮開銷。
4.MySQL不會考慮正在并發(fā)運行的其他查詢，而并發(fā)查詢會影響查詢運行的速度。
5.MySQL并不總是根據(jù)開銷來進行優(yōu)化，有時候它僅僅遵從一些原則。
6.優(yōu)化器不會考慮不受它控制的操作的開銷，比如執(zhí)行存儲函數(shù)和用戶定義的函數(shù)。
7.優(yōu)化器不會總是估算每一個可能的執(zhí)行計劃，所以它可能錯過優(yōu)化方案。
MySQL優(yōu)化器有兩個基本方案：靜態(tài)優(yōu)化和動態(tài)優(yōu)化。靜態(tài)優(yōu)化可以簡單地通過探測解析樹（Parse Tree）來完成。靜態(tài)優(yōu)化和值無關(guān)。它可以被應用一次，然后始終都有效，即使用不同的參數(shù)重新執(zhí)行查詢也不會改變。動態(tài)優(yōu)化根據(jù)上下文而定，和很多因素有關(guān)。每次查詢執(zhí)行的時候都會重新執(zhí)行優(yōu)化。
下面是MySQL能夠處理的一些優(yōu)化類型：
1.對聯(lián)接中的表進行重新排序。
2.將外聯(lián)接轉(zhuǎn)換成內(nèi)聯(lián)接。
3.代數(shù)等價法則。
4.優(yōu)化COUNT()，MIN()和MAX()。
5.計算和減少常量表達式。
6.覆蓋索引。
7.子查詢優(yōu)化。
8.早期終結(jié)。
9.相等傳遞。
10.比較IN()里面的數(shù)據(jù)。

表和索引統(tǒng)計
服務器層有查詢優(yōu)化器，卻沒有保存數(shù)據(jù)和索引的統(tǒng)計數(shù)據(jù)。存儲引擎負責統(tǒng)計這些數(shù)據(jù)。
因為服務器層沒有保存統(tǒng)計數(shù)據(jù)，所有MySQL優(yōu)化器就不得不向存儲引擎詢問查詢所使用的表的統(tǒng)計數(shù)據(jù)。存儲引擎會向優(yōu)化器提供每個表或索引的頁面數(shù)量、表和索引的基數(shù)性、鍵和行的長度及鍵的分布信息。優(yōu)化器可以使用這些信息來決定最佳的執(zhí)行計劃。

MySQL的聯(lián)接執(zhí)行策略
MySQL在執(zhí)行過程中非常廣泛地使用了“聯(lián)接”這一術(shù)語?？偟貋碚f，它將每個查詢都看成一個聯(lián)接，這并不僅僅是指在兩個表中查找匹配的查詢，而是指每一個查詢語句，包括子查詢，甚或針對單個表的SELECT語句。
MySQL的聯(lián)接執(zhí)行策略很簡單，它把每個聯(lián)接都看成一個嵌套循環(huán)。
從本質(zhì)上說，MySQL以同樣的方式執(zhí)行每一種查詢。但是這種方式并不是對所有的合法查詢都有效。

執(zhí)行計劃
MySQL不會產(chǎn)生字節(jié)碼（Byte-Code）來執(zhí)行查詢。實際上，MySQL執(zhí)行計劃是樹形結(jié)構(gòu)。MySQL的執(zhí)行計劃總是采用“左深度樹（Left Deep Tree）”。

聯(lián)接優(yōu)化器
MySQL優(yōu)化器中最重要的部分是聯(lián)接優(yōu)化器，它決定了多表查詢的最佳執(zhí)行順序。
聯(lián)接優(yōu)化器試著產(chǎn)生最低開銷的查詢計劃。在可能的時候，它會從單表計劃開始，檢查所有可能的子樹的組合。
對于n個表聯(lián)接，那么要檢查組合的數(shù)量就是n的階乘。當搜索空間非常巨大的時候，優(yōu)化耗費的時間就會非常長，這時服務器就不會執(zhí)行完整的分析。當表的數(shù)量超過optimizer_search_depth的值時，它就會走捷徑。

排序優(yōu)化
當MySQL不能使用索引時，它就必須自己對結(jié)果進行排序。如果MySQL不能在內(nèi)存中排序，它就必須自己對結(jié)果進行排序。排序可以在內(nèi)存或磁盤中進行，但是這個過程叫文件排序（Filesort），盡管它實際沒有使用文件。
如果待排序的值的數(shù)量和排序緩存的大小相當，MySQL就可以在內(nèi)存中利用快速排序?qū)λ袛?shù)據(jù)排序。如果MySQL不能在內(nèi)存中排序，它就會在磁盤上對數(shù)據(jù)進行分塊，并且對每一塊數(shù)據(jù)都使用快速排序算法，然后把所有塊的數(shù)據(jù)合并到結(jié)果中。
有兩種文件排序方法：
1.雙路排序（Two Passes）–表算法
讀取行指針和ORDER BY列，對它們進行排序，然后掃描已經(jīng)排好序的列表，按照列表中的值重新從表中讀取對應的行進行輸出。
2.單路排序（Single Pass）–新算法
讀取查詢需要的所有列，按照ORDER BY列對它們進行排序，然后掃描排序后的列表并且輸出特定的列。

MySQL架構(gòu)優(yōu)化及索引

MySQL支持很多種不同的數(shù)據(jù)類型，并且選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要。不管選擇何種類型，下面的簡單原則都會有助于做出更好的選擇：
1.更小通常更好。
2.簡單就好。
3.盡量避免NULL。

索引的基礎(chǔ)知識
B-Tree索引
B-Tree通常意味著數(shù)據(jù)存儲是有序的，比且每個葉子頁（Leaf Page）到根的距離是一樣的。
B-Tree索引加速了數(shù)據(jù)訪問，因為存儲引擎不會掃描整個表得到需要的數(shù)據(jù)。相反，它從根節(jié)點開始。根節(jié)點保存了指向子節(jié)點的指針，并且存儲引擎會根據(jù)指針尋找數(shù)據(jù)。它通過查找節(jié)點頁中的值找到正確的指針，節(jié)點頁包含子節(jié)點中值的上界和下界。最后，存儲引擎可能無法找到需要的數(shù)據(jù)，也可能成功地找到包含數(shù)據(jù)的葉子頁面（Leaf Page）。
葉子頁面是特殊的，因為它們含有指向被索引的數(shù)據(jù)的指針，而沒有指向其他頁面的指針。
索引保存數(shù)據(jù)的順序等于CREATE TABLE命令中列的順序。
能使用B-Tree所有的查詢類型：
B-Tree索引能很好地用于全鍵值、鍵值范圍或鍵前綴查找。它們只有在查找使用了索引的最左前綴（Leftmost Prefix）的時候才有用。
1.匹配全名
2.匹配最左前綴
3.匹配列前綴
4.匹配范圍值
5.精確匹配一部分并且匹配某個范圍中的另一部分
6.只訪問索引的查詢
由于樹的節(jié)點是排好序的，它們可以用于查找（查找值）和ORDER BY查詢（以排序的方式查找值）。通常來說，如果B-Tree能以某種特殊的方式找到某行，那么它也能以同樣的方式對行進行排序。因此，上面討論的所有查找方式也可以同等地應用于ORDER BY。
下面是B-Tree索引的一些局限：
1.如果查找沒有從索引列的最左邊開始，它就沒什么用處。
2.不能跳過索引中的列。
3.存儲引擎不能優(yōu)化訪問任何在第一個范圍條件右邊的列。
哈希索引
哈希索引（Hash Index）建立在哈希表的基礎(chǔ)上，它只對使用了索引中的每一列的精確查找有用。對于每一行，存儲引擎計算出可被索引列的哈希碼（Hash Code），它是一個較小值，并且有可能和其他行的哈希碼不同。它把哈希碼保存在索引中，并且保存了一個指向哈希表中每一行的指針。
哈希索引有一些局限：
1.因為索引值包含了哈希碼和行指針，而不是值本身，MySQL不能使用索引中的值來避免讀取行。
2.MySQL不能使用哈希索引進行排序，因為它們不會按序保存行。
3.哈希索引不支持部分鍵匹配，因為它們是由被索引的全部值計算出來的。
4.哈希索引只支持使用了=、IN()、和 < =>的相等比較。
5.訪問哈希索引中的數(shù)據(jù)非?？?，除非碰撞率很高（很多值有相同的哈希碼）。當發(fā)生碰撞的時候，存儲引擎必須訪問鏈表中的每一行指針，然后逐行進行數(shù)據(jù)比較，以確定正確的數(shù)據(jù)。
6.如果有很多碰撞，一些索引維護操作有可能會變慢。
InnoDB存儲引擎有一個特別的功能，叫自適應哈希索引（Adaptive Hash Index）。當InnoDB注意到一些索引值被很頻繁地訪問的時候，它就會在B-Tree的頂端為這些值建立起內(nèi)存中的索引。這個過程是全自動的，既不能控制，也不能配置它。

高性能索引策略
1.隔離列
如果在查詢中沒有隔離索引的列，MySQL通常不會使用索引?！案綦x”列意味著它不是表達式的一部分，也沒有位于函數(shù)中。
2.前綴索引和索引選擇性
有時候需要索引很長的字符列，它會使索引變大并且變慢。一個策略就是模擬哈希索引。另一個策略是索引開始的幾個字符，而不是全部值，以節(jié)約空間并得到好的性能。
索引選擇性（Index Selectivity）是不重復的索引值（也叫基數(shù)（Cardinality））和表（#T）中所有行的比值，它的值在1/#T和1之間。
前綴索引能很好地減少索引的大小及提高速度，但是它也有壞處：MySQL不能在ORDER BY或GROUP BY查詢中使用前綴索引，也不能把它們用作覆蓋索引（Covering Index）。
3.聚集索引
聚集索引（Clustered Index）不是一種單獨的索引類型，而是一直存儲數(shù)據(jù)的方式。
InnoDB的聚集索引實際上在同樣的結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。
當表有聚集索引的時候，它的數(shù)據(jù)行實際保存在索引的葉子頁（Leaf Page）中。術(shù)語“聚集”指實際的數(shù)據(jù)行和相關(guān)的鍵值都保存在一起。每個表只能有一個聚集索引，因為不能一次把行保存在兩個地方。
InnoDB按照主鍵（Primary Key）進行聚集。如果沒有定義主鍵，InnoDB會試著使用唯一的非空索引（Unique Nonnullable Index）來代替。如果沒有這種索引，InnoDB就會定義隱藏的主鍵然后在上面進行聚集。
如果正在使用InnoDB并且不需要任何特定的聚集，就可以定義一個代理鍵（Surrogate Key）。它是一種主鍵，但是值和應用程序無關(guān)。最簡單的辦法是使用AUTO_INCREMENT列。這會保證行是順序插入的并且能提高使用主鍵聯(lián)接的性能。
4.覆蓋索引
包含（或者“覆蓋”）所有滿足查詢需要的數(shù)據(jù)的索引叫覆蓋索引（Covering Index）。
覆蓋索引必須保存它包含的列的數(shù)據(jù)。哈希、空間和全文索引不會保存這些值，因此MySQL只能使用B-Tree索引來覆蓋查詢。并且，不同的存儲引擎實現(xiàn)覆蓋索引的方式不一樣，不是所有的存儲引擎都支持覆蓋索引。
當發(fā)起一個被索引覆蓋的查詢（索引覆蓋查詢（Index-covered Query））時，會在解釋器（EXPLAIN）的Extra列看到“使用索引（Using Index）”。
5.為排序使用索引掃描
MySQL有兩種產(chǎn)生排序結(jié)果的方式：使用文件排序（Filesort），或者掃描有序的索引。
掃描索引本身是很快的，因為它只需要從一條索引記錄移到另外一條記錄。然后，如果MySQL沒有使用索引覆蓋掃描，就不得不查找在索引中發(fā)現(xiàn)的每一行。這基本是隨機I/O的，因此以索引順序讀取數(shù)據(jù)通常比順序掃描表慢得多，尤其對于I/O密集的工作負載。
按照索引對結(jié)果進行排序，只有當索引的順序和ORDER BY子句中的順序完全一致，并且所有列排序的方向（升序或降序）一樣才可以。如果查詢聯(lián)接了多個表，只有在ORDER BY子句的所有列引用的是第一個表才可以。查找查詢中的ORDER BY子句也有同樣的局限：它要使用索引的最左前綴。在其他所有情況下，MySQL使用文件排序。
ORDER BY無須定義索引的最左前綴的一種情況是前導列為常量。如果WHERE子句和JOIN子句為這些列定義了常量，它們就能彌補索引的缺陷。
按照索引進行排序的一個最重要的用途是有ORDER BY和LIMIT子句的查詢。
6.壓縮（前綴壓縮）索引
MyISAM使用前綴壓縮（Prefix Compression）以減小索引大小、運行更多索引被裝入內(nèi)存，以及在某些情況下極大地提高性能。它在默認情況下會壓縮字符串，但是可以讓它壓縮整數(shù)。
MyISAM在對索引塊排序的時候，首先對第一個值進行全排序，然后記錄下有相同前綴的字節(jié)數(shù)，加上不同的值作為后綴。MyISAM也能對相鄰行指針使用前綴壓縮。
測試表面壓縮后的鍵使MyISAM表上的索引查找對CPU密集的負載會慢幾倍，因為掃描須隨機查找。反向掃描甚至更慢。權(quán)衡壓縮的標準是CPU內(nèi)存資源和磁盤資源的折中。
可以在使用CREATE TABLE命令時使用PACK_KEYS控制索引壓縮的方式。
7.多余和重復索引
MySQL允許你在同一個列上創(chuàng)建多個索引，它不會注意到你的錯誤，也不會為錯誤提供保護。MySQL不得不單獨維護每一個索引，并且查詢優(yōu)化器在優(yōu)化查詢的時候會逐個考慮它們。這行嚴重影響性能。
重復索引（Duplicate Index）是類型相同，以同樣的順序在同樣的列上創(chuàng)建的索引。應該避免創(chuàng)建重復索引，并且在發(fā)現(xiàn)它的時候把它移除掉。
多余索引（Redundant Index）和重復索引有一些不同。如果列(A,B)上有索引，那么另外一個列(A)上的索引就是多余的。這就是說，(A,B)上的索引能被當成(A)上的索引。（這種多余值適合于B-Tree索引。）然而，(B,A)上的索引不會是索引的，(B)上的索引也不是，因為列B不是列(A,B)的最左前綴。還有，不同類型的索引對于B-Tree索引不是多余的，無論它們針對的是哪一列。
8.索引和鎖定
索引對InnoDB有很重要的作用，因為它會讓查詢鎖定更少的列。
如果查詢永遠不會訪問不需要的行，它們就會鎖定更少的行，有兩個原因使它更有利于性能。首先，盡管InnoDB的行鎖定效率更高，使用的內(nèi)存很少，但是鎖定的時候還是有一些開銷。其次，鎖定超過需要的行會增加鎖競爭（Lock Contention）和減少并發(fā)。
InnoDB只有在訪問行的時候才鎖定它們，并且索引能減少InnoDB訪問的行數(shù)，從而減少鎖定。然而，只有當InnoDB能在存儲引擎級過濾不需要的行的時候才能起作用。如果索引不允許InnoDB那么做，MySQL就不得不在InnoDB取得行并且把它們返回給服務器級之后再使用WHERE子語句。這時，已經(jīng)無法避免鎖定行了：InnoDB已經(jīng)鎖定了它們，并且服務器不能解鎖。
InnoDB能在第二索引上放置共享（讀?。╂i。但是獨占（寫入）鎖要求訪問主鍵。這消除了使用覆蓋索引的可能性，并且能導致SELECT FOR UPDATE比LOCK IN SHARE MODE或非鎖定查詢慢得多。

MySQL查詢緩存

MySQL檢查緩存命中的方式相當簡單快捷。緩存就是一個查找表（Lookup Table）。查找的鍵就是查詢文本、當前數(shù)據(jù)庫、客戶端協(xié)議的版本，以及其他少數(shù)會影響實際查詢結(jié)果的因素之哈希值。
在檢查緩存的時候，MySQL不會對語句進行解析、正則化或者參數(shù)化，它精確地使用客戶端傳來的查詢語句和其他數(shù)據(jù)。只要字符大小寫、空格或者注釋有一點點不同，查詢緩存就任務這是一個不同的查詢。
查詢緩存不會存儲有不確定結(jié)果的查詢。因此，任何一個包含不確定函數(shù)的查詢不會被緩存。查詢緩存不會緩存引用了用戶自定義函數(shù)、存儲函數(shù)、用戶自定義變量、臨時表、mysql數(shù)據(jù)庫中的表或者任何一個有列級權(quán)限的表的查詢。
MySQL只有在解析查詢的時候才知道里面是否有不確定函數(shù)。緩存查找發(fā)生在解析之前。
MySQL一旦發(fā)現(xiàn)有阻止緩存的元素存在，它里面就把查詢標記為不可緩存，并且產(chǎn)生的結(jié)果也不會被保持下來。
因為查詢緩存只針對服務器第一次收到的完整SELECT語句，所以查詢里面的子查詢或視圖不能使用緩存，存儲過程中的查詢也不能使用緩存。
當事務內(nèi)部的語句更改了表，即使InnoDB的多版本機制應當對其他語句隱藏事務的變化，服務器也會使所有引用了該表的查詢緩存失效。直到事務提交之前，該表會全局地不可緩存。
除了用于自身的40KB內(nèi)存，查詢緩存的內(nèi)存池被分為大小可變的塊。每一塊都知道自己的類型、大小、數(shù)據(jù)量和指向前一個和后一個邏輯塊和物理塊的指針。內(nèi)存塊可以分為存儲查詢結(jié)果、查詢使用的表的列表、查詢文本等類型。然而，不同類型的塊的處理方式都一樣，所以對查詢緩存調(diào)優(yōu)的時候不用區(qū)分它們的類型。
服務器啟動的時候會初始化查詢緩存使用的內(nèi)存。內(nèi)存池最開始只有一個塊。它的大小是被配置用于緩存的內(nèi)存大小減去自身需要的40KB。
檢查是否從查詢緩存中受益的最簡單的辦法就是檢查查詢緩存命中率。它是緩存提供的查詢結(jié)果的數(shù)量，而不是服務器執(zhí)行的數(shù)量。當服務器收到SELECT語句的時候，Qcache_hits和Com_select這兩個變量會根據(jù)查詢緩存的情況進行遞增，查詢緩存命中率的計算公式是：Qcache_hits / (Qcache_hits Com_select)。
任何一個不在緩存中存在的查詢都是緩存未命中的。
緩存可能會因為碎片、內(nèi)存不足或數(shù)據(jù)改變而失效。如果已經(jīng)給緩存分配了足夠的內(nèi)存，并且把query_cache_min_res_unit調(diào)整到了合適的值，那么大部分緩存失效的原因都應該是由數(shù)據(jù)改變引起的?？梢酝ㄟ^檢查Com_*的值知道到底有多少查詢修改了數(shù)據(jù)。也可以通過檢查Qcache_lowmem_prunes的值了解有多少查詢因為內(nèi)存不足而失效。
如果在服務器收到同一個查詢語句之前，緩存就失效了，那么保持結(jié)果就只能是浪費時間和內(nèi)存。檢查Com_select和Qcache_inserts的相對大小可以確認這種情況是否發(fā)生。如果差不多所有的SELECT語句都是緩存未命中（Com_select會因此而增加），并且接下來把結(jié)果保存到了緩存中，那么Qcache_inserts就會和Com_select差不多大小。因此，Qcache_inserts一般會比Com_select小得多，至少在緩存被正確地激活后是這樣的。

對查詢緩存進行維護和調(diào)優(yōu)
Query_cache_type
這個選項表示緩存是否被激活。具體選項是OFF、ON或DEMAND。DEMAND的意思是只有包含了SQL_CACHE選項的查詢才能被緩存。它既是會話級變量，也是全局性變量。
Query_cache_size
分配給查詢的總內(nèi)存，以字節(jié)為單位。它必須是1024的倍數(shù)。
Query_cache_min_res_unit
分配緩存塊的最小值。
Query_cache_limit
這個選項限制了MySQL存儲的最大結(jié)果。如果查詢的結(jié)果比這個值大，那么就不會被緩存。要知道的是服務器在產(chǎn)生結(jié)果的同時進行緩存，它無法預先知道結(jié)果是否會超過這一限制。如果在緩存的過程中發(fā)現(xiàn)已經(jīng)超過了限制，MySQL會增加Qcache_not_cached的值，并且丟掉已經(jīng)緩存過的值。如果知道會發(fā)生這樣的事，那么給查詢加上SQL_NO_CACHE，可以避免這種開銷。
Query_cache_wlock_invalidate
這個選項指是否緩存其他聯(lián)接已經(jīng)鎖定了的表。默認值是OFF，可以讓你從其他聯(lián)接已經(jīng)鎖定了的表中讀取緩存過的數(shù)據(jù)，這改變了服務器的語義。因為這種讀取通常是不被允許的。把它改成ON會阻止讀取數(shù)據(jù)，但有可能增加鎖等待。它對于大多數(shù)程序都沒有影響，所以通常保持默認值就可以了。

仔細地選擇query_cache_min_res_unit可以避免在查詢緩存中造成大量的內(nèi)存浪費?？梢杂檬褂玫膬?nèi)存（大致等于query_cache_size – Qcache_free_memory）除以Qcache_queries_in_cache得到查詢的平均大小。如果緩存由大結(jié)果和小結(jié)果混合而成，可以通過降低query_cache_limit的值阻止緩存大結(jié)果。
可以通過檢查Qcache_free_blocks的值來探測緩存中碎片的情況，它可以顯示緩存中有多少內(nèi)存塊處于FREE狀態(tài)。如果Qcache_free_blocks大致等于Qcache_total_blocks / 2，則說明碎片非常嚴重。如果Qcache_lowmem_prunes的值正在增加，并且有大量的自由塊，這意味著碎片導致查詢正被從緩存中永久刪除。
可以使用FLUSH QUERY CACHE命令移除碎片。這個命令會把所有的存儲塊向上移動，并把自由塊移到底部。當它運行的時候，它會阻止訪問查詢緩存，這鎖定了整個服務器。它不會從緩存中移除查詢，RESET QUERY CACHE才會這么做。
當服務器清理查詢的時候，Qcache_lowmem_prunes的值會增加。如果它的值增加得很快，那么可能有兩個原因：
1.如果有很多自由塊，那么問題可能是由碎片引起的。
2.如果自由塊比較少，那么這可能意味著工作負載使用的內(nèi)存大小超過了所分配的內(nèi)存?？梢詸z查Qcache_free_memory知道未使用的內(nèi)存數(shù)量。

MySQL通用調(diào)優(yōu)原則

內(nèi)存使用調(diào)優(yōu)：
1.決定MySQL能使用的內(nèi)存的絕對上限。
2.決定MySQL會為每個連接使用多少內(nèi)存，比如排序緩沖區(qū)和臨時表。
3.決定操作系統(tǒng)需要多少內(nèi)存來很好地運行自身，包括機器上的其他程序，比如周期性工作。
4.假設上面的工作都已完成，就可以把剩余的內(nèi)存分配給MySQL的緩存，比如InnoDB的緩沖池。

為緩存分配內(nèi)存：
對于大部分用戶來說，下面的這些緩存是最重要的：
1.操作系統(tǒng)為MyISAM的數(shù)據(jù)提供的緩存。
2.MyISAM鍵緩存。
3.InnoDB緩沖池。
4.查詢緩存。

MyISAM鍵緩存
MyISAM鍵緩存也被叫做鍵緩沖區(qū)。MyISAM自身只緩存了索引，沒有數(shù)據(jù)（它讓操作系統(tǒng)緩存數(shù)據(jù)）。
最重要的選項是key_buffer_size，它的值應該占到所有保留內(nèi)存的25%到50%。
在默認情況下，MyISAM將所有索引緩存在默認的鍵緩沖中，但是可以創(chuàng)建多個命名鍵緩沖區(qū)。為了床架名為key_buffer_1和key_buffer_2，大小都為1GB的鍵緩沖區(qū)，可以在配置文件中加入下面兩行：
key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G
現(xiàn)在就有3個緩沖區(qū)了，兩個顯示創(chuàng)建的和一個默認的?？梢允褂肅ACHE INDEX命令把表映射到緩存。也可以通過下面的命令告訴MySQL把表t1和t2的索引保存到key_buffer_1：

還可以使用LOAD INDEX把表的索引預加載到緩存中：

可以把這個SQL語句放到一個MySQL啟動時執(zhí)行的文件里面。文件名字必須在init_file選項中定義，并且文件可以包含多個SQL命令，每個命令一行，而且不能有注釋。MySQL第一次訪問.MYI文件時，任何沒有顯式地映射到鍵緩沖區(qū)的索引都會被放到默認緩沖區(qū)中。
可以使用SHOW STATUS和SHOW VARIABLES監(jiān)視鍵緩沖區(qū)的使用情況和性能。
緩存命中率：100 – （ （Key_reads * 100） / Key_read_requests ）
緩存使用百分比：100 – （ （Key_blocks_unused * Key_cache_block_size） * 100 / key_buffer_size ）
每秒內(nèi)緩存未命中的數(shù)量：Key_reads / Uptime
在決定給鍵緩存分配多少內(nèi)存的時候，沒有必要讓鍵緩存大于數(shù)據(jù)的大小?？梢允褂孟旅娴拿钫页龃鎯λ饕奈募笮。?

要記住MyISAM使用操作系統(tǒng)緩存數(shù)據(jù)文件，它通常大于索引的大小。因此，留給操作系統(tǒng)緩存的內(nèi)存比留給鍵緩存的要多也是很正常的。最后，即使沒有使用MyISAM表，也要給key_buffer_size設置少量的內(nèi)存。MySQL有時會使用MyISAM執(zhí)行一些內(nèi)部操作。

MyISAM數(shù)據(jù)塊大小
如果鍵數(shù)據(jù)塊太小，就會遇到寫入排隊的情況，也就是操作系統(tǒng)只有等讀取完成之后才能寫入。下面是寫入排隊的例子，假設操作系統(tǒng)的頁面大小是4KB并鍵數(shù)據(jù)塊的大小是1KB：
1.MyISAM從磁盤請求1KB數(shù)據(jù)。
2.操作系統(tǒng)從磁盤讀取4KB數(shù)據(jù)，并緩存起來，然后把需要的1KB數(shù)據(jù)傳給MyISAM。
3.操作系統(tǒng)將緩存中的數(shù)據(jù)丟掉。
4.MyISAM修改了那1KB數(shù)據(jù)并要求操作系統(tǒng)把它寫回磁盤。
5.操作系統(tǒng)從磁盤讀取同樣的4KB數(shù)據(jù)，放入緩存中，修改其中的1KB，并且把這個數(shù)據(jù)塊寫回磁盤。
寫入等待發(fā)生在第五步，也就是MyISAM要求操作系統(tǒng)只寫入其中一部分數(shù)據(jù)的時候。如果MyISAM數(shù)據(jù)塊的大小和操作系統(tǒng)讀取的頁面大小相同，第五步就可以避免。
myisam_block_size變量控制了鍵緩存塊的大小。也可以在CREATE TABLE或CREATE INDEX語句中為每一個鍵定義KEY_BLOCK_SIZE選項來控制鍵的大小。但是由于所有的鍵都存儲在相同的文件中，所以的確需要它們的大小都等于或大于操作系統(tǒng)的值，以避免由于對齊導致的寫入等待問題。

InnoDB緩沖池
InnoDB緩沖池不僅僅保存了索引，它還保存了行數(shù)據(jù)及自適應的哈希索引、插入緩沖區(qū)、鎖及其他的內(nèi)部結(jié)果。MySQL手冊建議在專用服務器上把80%的物理內(nèi)存分配給緩沖池。和MyISAM鍵緩沖區(qū)一樣，可以使用SHOW命令或innotop工具來監(jiān)視InnoDB緩沖池的性能和內(nèi)存使用情況。
在大部分情況下，應該是InnoDB緩沖池和可用內(nèi)存保存一致。但是，在少數(shù)情況下，很大的緩沖池會導致長時間的延遲。
可以改變innodb_max_dirty_pages_pct的值，讓InnoDB改變保留在緩沖池中被修改的頁面的數(shù)量。如果允許保利更多被修改的頁面，InnoDB就需要更長的時間來關(guān)閉，因為它會在關(guān)閉之前把修改的頁面寫入數(shù)據(jù)文件?？梢詮娭扑焖訇P(guān)閉，但是它在重新啟動的時候就會要做更多的恢復工作，所以這實際上不能加快從關(guān)閉到啟動的周期。如果預先知道需要關(guān)閉，就可以把這個變量設置為較小的值，并等待它沖刷線程以清理緩沖池，然后在修改的頁面數(shù)量變小的時候關(guān)閉InnoDB?？梢酝ㄟ^觀察狀態(tài)變量Innodb_buffer_pool_pages_dirty或使用innotop監(jiān)視SHOW INNODB STATUS的值來監(jiān)測修改過的頁面的數(shù)量。
降低innodb_max_dirty_pages_pct的值其實并不會確保InnoDB會在緩沖池里面保存較少的修改的頁面。相反，它控制了InnoDB什么時候開始“變懶”。InnoDB默認的行為是使用后臺線程沖刷修改過的頁面、合并寫入查詢并且順序地執(zhí)行寫入動作。這種行為是“懶惰的”，因為它只會在其他數(shù)據(jù)需要空間的時候才會執(zhí)行沖刷行為。當修改過的頁面超過變量規(guī)定的值，InnoDB會盡快地沖刷緩沖區(qū)，以保持盡可能少的修改過的頁面。這個變量的默認值是90%，所以在默認情況下，InnoDB只會在修改過的頁面已經(jīng)占據(jù)了緩沖池90%空間的時候，才開始沖刷動作。

線程緩存
thread_cache_size定義了MySQL能在緩存中保存的線程數(shù)量?？梢酝ㄟ^觀察thread_created變量的值，以確定線程緩存是否足夠大。如果每秒創(chuàng)建的線程數(shù)量少于10個，緩存的大小就是足夠的。
一個好辦法就是觀察Threads_connected的值，并且把thread_cache_size的值設置得足夠大，以處理波動的負載。
每個在緩存中的線程通常消耗256KB內(nèi)存。通常來說，需要把線程緩存保持得足夠大，以使Threads_created不會經(jīng)常增加。但是如果它的值非常大，那么就應該把它設置得小一點。這是因為操作系統(tǒng)不能很好地處理極其多的線程，即使它們處于睡眠狀態(tài)也不行。

表緩存
緩存中的每個對象都包含了解析表后生成的.frm文件和其他數(shù)據(jù)，對象中其他的東西依賴于表的存儲引擎。
在MySQL5.1中，表緩存被分為了兩個部分：一部分為打開表，另一部分為表的定義（通過table_open_cache和table_definition_cache定義）。因此，表的定義（解析過的.frm文件）和其他資源是隔離的。打開的表仍然基于每個線程、每個使用的表。但是表的定義是全局的，可以在所有的連接中共享。通?？梢园裻able_definition_cache的值設置得足夠高，以緩存所有表的定義。
如果Opened_tables的值很大或者正在上升，那就說表緩存不夠大，應該增加系統(tǒng)變量table_cache的值（在MySQL中是table_open_cache）。將表緩存變得很大的唯一壞處就是在有很多MyISAM表的時候，它會導致較長的關(guān)閉時間，因為要沖刷鍵數(shù)據(jù)塊，而且表要被標記為不再打開。出于同樣的原因，它也會導致FLUSH TABLES WITH READ LOCK需要較長時間才能完成。
如果收到MySQL不能打開更多文件的錯誤提示，那就應該增加MySQL允許保存開啟的文件數(shù)量?？梢栽趍y.cnf文件中設定open_files_limit解決這個問題。

InnoDB數(shù)據(jù)字典
InnoDB自己有對每個表的緩存，叫做“表定義緩存”或者“數(shù)據(jù)字典”，它是不可配置的。
主要的性能問題，除了內(nèi)存需求之外，就是為表打開和計算統(tǒng)計數(shù)據(jù)。InnoDB不會一直在表中保存統(tǒng)計數(shù)據(jù)。它在每次啟動的時候都會重新計算。
如果使用innodb_file_per_table選項，那么對InnoDB任何時候能打開的.ibd文件的數(shù)量還有另外一個限制。這是有InnoDB存儲引擎處理的，而不是MySQL服務器，并且它受innodb_open_files的控制。InnoDB為每個.idbw文件使用了全局文件描述符。如果可以的話，最好把innodb_open_files設置得足夠大，這樣服務器就可以保利所有同時打開的.idb文件。

MySQL I/O 調(diào)優(yōu)

MyISAM I/O 調(diào)優(yōu)
MyISAM通常在每次寫入之后就會把索引的改變刷寫到磁盤上。如果打算對一個表做很多改變，那么把它們組成一個批處理就會快很多。
做到這點的一種辦法是使用LOCK TABLES，它可以把寫入延遲到對表解鎖。
還可以使用delay_key_write變量來延遲索引的寫入。如果使用了它，修改過的鍵緩沖區(qū)塊只有在表關(guān)閉的時候才會被寫入磁盤。
延遲鍵寫入也有一些缺點：
1.如果服務器崩潰并且數(shù)據(jù)塊沒有被刷寫到磁盤上，索引就會損壞。
2.如果許多寫入被延遲了，MySQL就會需要更多的時間來關(guān)閉表，因為它要等待緩沖區(qū)被刷寫到磁盤上。
3.FLUSH TABLES可能會需要很長的時間，原因同上。
4.鍵緩沖區(qū)中未被刷寫的數(shù)據(jù)塊可能不會給將要從磁盤上讀取的新塊留出空間。因此，查詢可能會因為等待鍵緩沖區(qū)釋放空間而停止。
啟動到數(shù)據(jù)文件的內(nèi)存映射訪問是另外一種有用的調(diào)優(yōu)手段。內(nèi)存映射是MyISAM能夠通過操作系統(tǒng)的頁面緩存直接訪問到.MYD文件，避免了代價較高的系統(tǒng)調(diào)用。

InnoDB I/O 調(diào)優(yōu)
InnoDB使用日志來減少提交事務的開銷。它不是在每次事務提交的時候就把緩沖池刷寫到磁盤上，而是記錄了事務。事務對數(shù)據(jù)和索引做出的改變通常會被映射到表空間的隨機位置，所以將這些改變寫到磁盤上就會引起隨機I/O。作為一條原則，隨機I/O比順序I/O開銷要高得多，因為它需要時間在磁盤上尋找正確的位置，并且還要等磁頭移到相應的位置上。
InnoDB使用自身的日志把隨機I/O轉(zhuǎn)換為順序I/O。一旦日志被記錄到磁盤上，事務就是持久的了，盡管這時候改變還沒有被寫到數(shù)據(jù)文件中。如果一些壞事發(fā)生了，InnoDB可以回放日志并恢復提交了的事務。
InnoDB最終要把改變寫到數(shù)據(jù)文件中，因為日志的大小是固定的。它以循環(huán)的方式寫日志，當記錄到達日志的底部，就會又從頂部開始。它不會覆蓋改變沒有被應用到數(shù)據(jù)文件的記錄，因為這會消除提交的事務唯一持久性的記錄。
InnoDB使用后臺線程智能地把改變寫入到文件中。該線程可以把寫入集中在一起，然后以效率更高的順序?qū)懭氲姆绞綀?zhí)行。實際上，事務日志把隨機數(shù)據(jù)文件I/O轉(zhuǎn)換為順序日志文件和數(shù)據(jù)文件I/O。
日志文件總體大小由innodb_log_file_size和innodb_log_files_in_group控制，并且它們對寫入的性能影響極大。日志文件總大小的上限是4GB。
在InnoDB改變數(shù)據(jù)的時候，它會把這次改動的記錄寫到日志緩沖里面。日志緩沖被保存在內(nèi)存中。緩沖寫滿、事務提交或每一秒鐘，不管哪種情況先發(fā)生，InnoDB都會把緩沖區(qū)寫到磁盤上的日志文件中。如果有大型事務，就可以增加緩存文件（默認是1MB）來減少I/O動作。控制換成大小的變量叫innodb_log_buffer_size。推薦值是1MB到8MB，除非要寫入大量的巨型BLOB記錄。
可以通過檢查SHOW INNODB STATUS命令的LOG部分檢測InnoDB的日志和日志緩沖I/O性能，還可以通過觀察Innodb_os_log_written的值了解InnoDB向日志文件寫入了多少數(shù)據(jù)。一個好的法則就是觀察10秒到100秒時間間隔內(nèi)的數(shù)據(jù)，并且注意最大值。可以使用這個值來判斷日志緩沖大小是否合適。
當InnoDB把日志緩沖寫到磁盤上的日志文件時，它會用一個互斥量鎖定緩存，把緩存寫到應有的位置，然后把剩下的記錄移到緩存的前端。InnoDB有群體提交特性，它可以利用一次I/O操作把所有的請求提交到日志中。
日志緩沖區(qū)必須被刷寫到持久性存儲中，以保證提交了的事務能完全持久化。如果比起持久性，更在意性能，就可以改變innodb_log_at_trx_commit的值來控制日志緩存被刷寫到什么地方及刷寫的頻率。
高性能事務的最佳配置是把flush_log_at_trx_commit設置為1，并且將日志文件放在有備用電池的寫入緩存的RAID上。
innodb_flush_method選項讓你可以配置InnoDB實際與文件系統(tǒng)進行交互的方式。除了寫入數(shù)據(jù)之外，它還可以影響InnoDB如何讀取數(shù)據(jù)。
InnoDB把數(shù)據(jù)保存在表空間中。表空間實際上是跨越了磁盤上的一個或多個文件的虛擬文件系統(tǒng)。它保留了自己的撤銷日志（老的數(shù)據(jù)行的版本）、插入緩存、雙寫緩存，以及表空間的其他內(nèi)部結(jié)構(gòu)。
可以使用innodb_data_file_path定義表空間文件。這些文件都在innodb_data_home_dir定義的目錄中。
Innodb_file_per_table選項使InnoDB為每一個表使用一個文件。將數(shù)據(jù)放在多個文件中能導致浪費更多的存儲空間，因為它把單個InnoDB表空間的碎片都放在了.ibd文件中。即使開啟了innodb_file_per_table選項，還是需要為撤銷日志和其他系統(tǒng)數(shù)據(jù)定義主表空間。
InnoDB的表空間在寫入負荷很重的環(huán)境中會增長得很大。如果事務長時間處于打開狀態(tài)，并且它們正在使用默認的REPEATABLE READ事務隔離層，InnoDB將不能刪除老的數(shù)據(jù)，因為未提交的事務還需要它們。InnoDB將老的數(shù)據(jù)保存在表空間中，所以當更多的數(shù)據(jù)被更新時，它就會繼續(xù)增長。又是問題不是出在沒有提交的事務上，而是出在工作負載上：清理進程是單線程，它跟不上需要被清理的老數(shù)據(jù)的數(shù)量。在這兩種情況下，SHOW INNODB STATUS的輸出有助于鎖定問題。查看TRANSACTIONS的第一行和第二行，它會顯示當前事務的數(shù)量及指出哪個的清理工作已經(jīng)完成了。如果它們的區(qū)別很大，那么就說明有許多沒被清理的事務。
如果有很多未被清理的事務并且表空間因它而增長，就可以強制MySQL變慢，以使清理線程能跟上數(shù)據(jù)的變化。否則，InnoDB就會不停地寫入數(shù)據(jù)并且填充磁盤，直到耗盡磁盤空間或讓表空間達到規(guī)定的上限制。
為了減緩寫入，可以把innodb_max_purge_lag變量設置為0之外的值。這個值表示在等待清理的事務的最大數(shù)量，一旦超過這個值，InnoDB就會延遲更新數(shù)據(jù)的事務。
InnoDB在對頁面進行部分寫入的時候使用了雙緩沖，以防止數(shù)據(jù)損壞。部分寫入發(fā)生在磁盤寫入沒有全部完成，并且只用16KB頁面的一部分被寫入的時候。雙緩沖是表空間中一個特殊的保留區(qū)域，大小足夠在一個連續(xù)塊中容納100個頁面。它在本質(zhì)上是最近寫入頁面的備份。當InnoDB把頁面從緩沖池中清寫到磁盤上時，它會先把它們寫入（或者清寫）到雙緩沖中，然后再寫入到真正的地方?？梢酝ㄟ^把innodb_doublewrite設置為0禁用雙寫緩存。

另外的I/O調(diào)優(yōu)
Sync_binglog選項控制了MySQL如何把二進制日志刷寫到磁盤。

MySQL并發(fā)調(diào)優(yōu)

MyISAM并發(fā)調(diào)優(yōu)
在了解MyISAM的并發(fā)設置之前，重要的是了解MyISAM如何刪除和插入行。刪除操作不會重新安排整個表，它們只是把行標記為已經(jīng)刪除，并且在表中留下了一些“洞”。MyISAM在可能的情況下會優(yōu)先使用這些“洞”，為插入復用空間。如果表是完整的，它就會把新的行拼接在表的最后。
即使MyISAM有表級別的鎖，它也能在讀取的同時把行拼接到表尾。它通過禁止讀取最后一行做到了這一點。這避免了不連續(xù)的讀取。
但是，當表中間的數(shù)據(jù)改變的時候，要提供連續(xù)讀取就困難得多。MVCC是最通用的解決這個問題的辦法：它在創(chuàng)建新版本數(shù)據(jù)的同時提供老版本數(shù)據(jù)的讀取。
MyISAM不支持MVCC，所以它只有在到達表尾的時候才允許并發(fā)插入。
可以使用concurrent_insert變量配置MyISAM的并發(fā)插入行為。

InnoDB并發(fā)調(diào)優(yōu)
可以經(jīng)常檢查SHOW INNODB STATUS輸出中的SEMAPHORES部分來確認是否發(fā)生了并發(fā)問題。
InnoDB用自己的“線程調(diào)度”程序來控制線程如何進入InnoDB的內(nèi)核訪問數(shù)據(jù)，以及一旦進入內(nèi)核之后可以執(zhí)行的動作?？刂撇l(fā)最基本的方式就是使用innodb_thread_concurrency變量，它限制了一次有多少線程能進入內(nèi)核。
如果內(nèi)核中已經(jīng)有了允許數(shù)量的線程，那么線程就不能再進入內(nèi)核了。InnoDB采用了一種兩階段的過程來保證線程可以盡可能高效地進入內(nèi)核。這種策略減少了操作系統(tǒng)引起的上下文切換帶來的開銷。線程首先睡眠innodb_thread_sleep_delay所規(guī)定的微妙數(shù)，然后再進行嘗試。如果還是不能進入，它就會進入一個等待線程的隊列中并且把控制權(quán)交給操作系統(tǒng)。
第一階段默認的睡眠時間是10000微妙。當有很多線程都處于“正在等待進入隊列”這一狀態(tài)時，改變這個值有助于高并發(fā)性系統(tǒng)。
一旦線程進入了內(nèi)核，它就會得到一個確定的數(shù)字作為“憑據(jù)”，它再次進入內(nèi)核的時候，就不會再進行任何的并發(fā)檢查。該數(shù)字限定了它再次回到等待隊列之前能做多少工作。Innodb_concurrency_tickets選項控制了憑據(jù)的數(shù)量。除非有大量的運行極長時間的查詢，否則我們極少改動這個選項。憑據(jù)只是為每個查詢授權(quán)，而不是為每個事務授權(quán)。一旦查詢結(jié)束，憑據(jù)就會被丟掉。
除了緩沖池和其他結(jié)果的瓶頸，在提交階段還有另外一種形式的并發(fā)瓶頸，也就是刷寫操作造成的密集I/O操作。Innodb_commit_comcurrency變量決定了某一時刻有多少線程能進行提交。

MySQL基于工作負載調(diào)優(yōu)

優(yōu)化BLOB和TEXT
BLOB和TEXT對于MySQL來說是特殊的負載（后面把BLOB和TEXT統(tǒng)稱為BLOB，因為它們術(shù)語同一種數(shù)據(jù)類型）。服務器不能為BLOB使用內(nèi)存中的臨時表。因此，如果某個查詢需要為BLOB值使用數(shù)據(jù)表，不管它是否很小，都會在磁盤上進行。有兩種辦法可以解決這個問題：（1）利用SUBSTRING函數(shù)把值轉(zhuǎn)換為VARCHAR；（2）讓臨時表運行得更快。
讓臨時表更快的最佳方式就是把它們放到基于內(nèi)存的文件系統(tǒng)中（GNU/Linux中的tmpfs）。
控制臨時表位置的服務器設置是tempdir。要監(jiān)控文件系統(tǒng)被寫滿的程度，以保證臨時表有足夠的空間。如果需要，可以定義幾個臨時表位置，MySQL會循環(huán)使用它們。
如果BLOB列非常大，并且使用的是InnoDB，那么就應該增加InnoDB的緩沖區(qū)大小。
對于很長的長度可變的列，InnoDB會在頁面中存儲一個768字節(jié)的前綴。如果列的值大于前綴的長度，InnoDB可能就會分配外部的存儲空間來保存其余的數(shù)據(jù)。InnoDB會為數(shù)據(jù)分配16KB大小的整個頁面，并且每一個列都會有自己的頁面（列不會共享頁面）。InnoDB一次為一個列分配一個頁面，直到頁面達到32個，然后它就會一次性分配64個頁面。
如果行的總長度，包括長列的完整值，小雨InnoDB的最大行長度（稍小于8KB），那么即使長列的值超過了前綴長度，InnoDB也不會分配外部存儲空間。
當InnoDB更新外部存儲空間中的長列時，它不會對它進行原地更新。相反地，它會把值放到外部存儲空間中的新地方，然后把老的數(shù)據(jù)刪除。

為文件排序進行優(yōu)化
MySQL有兩種文件排序算法。如果需要進行排序的列的總大小加上ORDER BY列的大小超過了max_length_for_sort_data定義的字節(jié)，MySQL就會使用雙路排序。當任何需要的列–甚至不是用于ORDER BY的列–是BLOB或TEXT列的時候，也會使用雙路排序。（可以使用SUBSTRING()把這些列轉(zhuǎn)換為可以使用單路排序的列。）
可以通過改變max_length_for_sort_data變量的值來影響MySQL選擇的算法。因為單路排序算法為將要排序的每一行創(chuàng)建了固定大小的緩沖區(qū)，VARCHAR列的最大長度是max_length_for_sort_data規(guī)定的值，而不是排序數(shù)據(jù)的實際大小。
當MySQL不得不對BLOB或TEXT列進行排序時，它只會使用前綴并會忽略掉剩余的值。這是因為它不得不分配固定大小的結(jié)構(gòu)來容納數(shù)據(jù)并且從外部存儲中將前綴拷貝回結(jié)構(gòu)中?？梢允褂胢ax_sort_length定義前綴應該是多大。

檢查MySQL服務器狀態(tài)變量
安裝工作負載對MySQL進行調(diào)優(yōu)的最有生產(chǎn)率的方式是檢查SHOW GLOBAL STATUS的輸出，以了解哪些設置需要改變。下面是一些最值得檢查的變量：
Aborted_clients
如果這個變量隨時間增加，那么就要確定是否優(yōu)雅地關(guān)閉了連接。如果不是，那就要檢查網(wǎng)絡性能，并且檢查max_allowed_packet配置變量，超過了max_allowed_packet的查詢會被強制地中斷。
Aborted_connections
這個值應該接近于0。不是的話，就可能是網(wǎng)絡的問題。有幾個被中斷的連接是正常的。
Binlog_cache_disk_use和Binlog_cache_use
如果Binlog_cache_disk_use和Binlog_cache_use之間的比率很大，那么就應該增加binglog_cache_size的值。
Bytes_received和Bytes_sent
這兩個值可以幫助你考察問題是由發(fā)送服務器的數(shù)據(jù)過多引起的，還是從服務器讀取的數(shù)據(jù)太多引起的。
Com_*
應該注意不要讓諸如Com_rollback這樣不常見的變量的值超過預期值。
Contentions
這個變量表示了連接意圖的數(shù)量（不是當前連接的數(shù)量，它是Threads_connected）。如果它的值快速增加，那么就應該檢查連接次或調(diào)整操作系統(tǒng)的網(wǎng)絡堆棧。
Created_tmp_disk_tables
如果這個值較高，有兩件事情可能發(fā)生了錯誤：（1）查詢在選擇BLOB或TEXT列的時候創(chuàng)建了臨時表；（2）tmp_table_size和max_heap_table_size可能不夠大。
Created_tmp_table
該值較高的唯一處理辦法就是優(yōu)化查詢。
Handler_read_rnd_next
Handler_read_rnd_next / Handler_read_rnd顯示了全表掃描的大致平均值。如果值較大，那么就應該優(yōu)化架構(gòu)、索引和查詢。
Key_blocks_used
如果Key_blocks_used * key_cache_block_size的值遠小于已經(jīng)充分熱身的服務器上的key_buffer_size值，那么就意味著key_buffer_size的值太大了，內(nèi)存被浪費了。
Key_reads
要注意觀察每秒鐘發(fā)生的讀取次數(shù)，并且將這個值和I/O系統(tǒng)進行匹配，以了解有多接近I/O限制。
Max_used_connections
如果該值和max_connections相同，那么可能是max_connections被設置的過低或者最大負載超過了服務器的上限。
Open_files
注意它不應該和open_files_limit的值接近。如果接近了，那么就應該增加open_files_limit。
Open_tables和opened_tables
應該將該值和table_cache進行對照。如果每秒有太多opened_tables，那么說明table_cache還不夠大。
Qcache_*
參考前面的MySQL查詢緩存。
Select_full_join
全聯(lián)接是無索引聯(lián)接，它是真正的性能殺手。最好能避免全聯(lián)接。如果聯(lián)接沒有索引，那么最好能優(yōu)化查詢和索引。
Select_full_range_join
如果該變量過高，那么就說明運行了許多使用了范圍查詢聯(lián)接表。范圍查詢比較慢，同時也是一個較好的優(yōu)化點。
Select_range_check
該變量記錄了在聯(lián)接時，對每一行數(shù)據(jù)重新檢查索引的查詢計劃的數(shù)量，它的性能開銷很大。如果該值較高或正在增加，那么就意味著一些查詢沒有找到好索引。
Slow_launch_threads
該變量較大說明了某些因素正在延遲聯(lián)接的新線程。它說明了服務器有一些問題，但是它并沒有說明真正的原因。它通常表示系統(tǒng)過載，導致操作系統(tǒng)不能給新創(chuàng)建的線程分配時間片。
Sort_merge_passes
該變量較大說明應該增加sort_buffer_size，也許僅僅只是為某些查詢。檢查查詢并且查明哪一個導致了文件排序。最好的辦法是優(yōu)化查詢。
Table_locks_waited
該變量顯示了有多少表被鎖住了并且導致了服務器級的鎖等待（等待存儲引擎鎖，比如InnoDB的行級鎖，不會使該變量增加）。如果這個值較高并且正在增加，那么就說明了嚴重的并發(fā)瓶頸。
Threads_created
如果該變量較大或正在增加，那么也許就應該增加thread_cache_size的值?？梢酝ㄟ^檢查threads_cached知道有多少線程已經(jīng)在緩存中了。

MySQL復制

在進入詳細設置復制之前，先看看它實際是如何復制數(shù)據(jù)的。
1.主服務器把數(shù)據(jù)更改記錄到二進制日志中。
2.從服務器把主服務器的二進制日志時間拷貝到自己的中繼日志中。
3.從服務器重放中繼日志的事件，把更改應用到自己的數(shù)據(jù)上。
第一步是在主服務器上記錄二進制日志。在每個更新數(shù)據(jù)的事務完成之前，主服務器都會把數(shù)據(jù)更改記錄到二進制日志中。即使事務在執(zhí)行期間是交錯的，MySQL也會串行地把事務寫入到二進制日志中。在把事件寫入二進制日志之后，主服務器告訴存儲引擎提交事務。
下一步是從服務器把主服務器的二進制日志拷貝到自己的硬盤上，進入所謂的“中繼日志（Relay Log）”中。首先，它啟動一個工作線程，叫I/O從線程（I/O Slave Thread）。這個線程開啟一個普通的客戶端連接中，然后啟動一個特殊的二進制日志轉(zhuǎn)儲（Binlog Dump）進程（它沒有相應的SQL命令）。這個轉(zhuǎn)儲進程從主服務器的二進制日志中讀取事件。它不會對時間進行輪詢。如果它跟上了主服務器，就會進入休眠狀態(tài)，并等待有新事件發(fā)生時主服務器發(fā)出的信號。I/O線程把事件寫入從服務器的中繼事件中。
SQL從線程處理了該過程的最后一部分。該線程讀取了中繼日志，并且重放其中的事件，然后更新從服務器的數(shù)據(jù)。由于這個線程能跟上I/O線程，中繼日志通常都在操作系統(tǒng)的緩存中，所以中繼日志的開銷很低。SQL線程執(zhí)行的事件也可以被寫入從服務器自己的二進制日志中。
MySQL有一些特殊的權(quán)限允許復制進程運行。從I/O線程運行在從服務器上，它創(chuàng)建了到主服務器的連接。這意味著必須在主服務器上創(chuàng)建一個用戶賬戶并且給它合適的權(quán)限，這樣I/O線程就可以以這個用戶的身份連接到主服務器，并且讀取它的二進制日志。

復制用戶在主服務器上實際只需要REPLICATION CLIENT權(quán)限，主、從服務器上并不真正需要REPLACTION SLAVE權(quán)限。這樣做有兩個原因：
1.用于監(jiān)視和關(guān)了復制的賬號將需要REPLICATION SLAVE權(quán)限，并且對這兩個目的使用同一個賬號更容易（而不是為這個目的再開一個單獨的賬號）。
2.如果在主服務器上建立了賬號，然后克隆到從服務器上，從服務器也可以成為主服務器，這樣就可以交換主、從服務器的角色。
下一步是在主服務器上開啟一些設置。在主服務器的my.cnf文件中輸入（或者驗證）下面的配置行：

從服務器也需要和主服務器類似的配置：

下面是一個開始復制的基本命令：

運行下面的命令開始復制：

在主服務器上最重要的二進制日志設置是sync_binlog：

這使MySQL在每次提交事務的時候把二進制日志的內(nèi)容同步到磁盤上，所以即使服務器崩潰也會把事件寫入日志中。如果關(guān)閉這個選項，服務器會少做一些工作，但是二進制日志在服務器崩潰后可能就會破壞或丟失信息。在不需要成為主服務器的從服務器上，這個選項帶來了不必要的開銷。它只適用于二進制日志，而不是中繼日志。
如果使用InnoDB，我們強烈推薦在主服務器上使用下面的選項：

在從服務器上，我們推薦開啟下面的配置選項：

Skip_slave_start選項會阻止從服務器在崩潰后自動啟動，它可以給你機會修復服務器。Read_only選項防止大部分用戶更改非臨時表。
如果從服務器落后主服務器很多，從I/O線程就會生成許多中繼日志。從服務器的SQL線程將會在完成中繼之后盡快地刪除它們（可以使用relay_log_purge選項改變這種行為），但是如果它落后太多，I/O線程可能就會寫滿磁盤。解決這個問題的辦法是relay_log_space_limit。如果所有中繼日志的大小超過了該變量規(guī)定的值，I/O線程就會停下來，并且等待SQL線程釋放一些磁盤空間。如果從服務器沒有從主服務器提取到所有的中繼日志，并且主服務器崩潰的話，所有的日志就都會丟失。