<<高效能mysql>>筆記1

標籤：

轉載請註明： TheViper http://www.cnblogs.com/TheViper  

 <<高效能mysql>>這本書寫的真的很好，只可惜本屌不才，大部分都看不懂，暫且記下與mysql最佳化有關，對自己有用的東西。

測試單位

• 輸送量

輸送量指的是單位時間內的交易處理數，單位tps(transaction per second).這一直是經典的資料庫應用測試的指標。

• 回應時間或延遲

這個指標用於測試工作所需的整體時間

• 並發性

注意，web伺服器並發性不等同於資料庫的並發性。伺服器的高並發一般也會導致資料庫的高並發，但伺服器所用的語言，架構，工具集對此都會有影響。一個設計良好的應用，同時可以開啟成千上百個資料庫伺服器串連，但可能同時只有少數串連在執行查詢。因此，所需要關注的是正在工作中的並行作業，或者是同時工作中的線程數或串連數。

• 可擴充性

可擴充性指的是，給系統增加一倍的工作，理想情況下，會獲得兩倍的輸送量，這時，看實際增加的輸送量是多少。

 效能最佳化的目標--回應時間

很多人認為效能最佳化就是降低cpu利用率。但這是個陷阱，資源就是用來消耗並用來工作的。所以，有時候消耗更多的資源能夠加快查詢速度。很多時候，升級到mysql新版本後，cpu利用率會上升的很厲害。這不代表效能出了問題。相反，說明新版本對資源的利用率上升了。

另外，如果把效能最佳化僅僅看出是提升每秒查詢量，這其實只是輸送量最佳化。輸送量的提升可以減少回應時間的副產品（倒數關係）。

 最佳化資料類型

• 更小的通常更好

一般情況下，應該盡量使用可以正確儲存資料的最小資料類型。但是確保沒有低估需要儲存的值得範圍。

• 簡單就好

單一資料型別的操作通常需要更少的cpu周期。例如，整型比字元操作代價更低，具體的。

1.應該使用mysql內建類型而不是字串儲存日期和時間

2.應該用整型儲存ip地址。

• 盡量避免null

通常情況下，最好指定列為not null,除非真的需要儲存null值。如果查詢中包含可為null的列，對mysql來說會更難最佳化，因為可為null的列使得索引，索引計算和值比較更複雜。另外，可為null的列會使用更多的儲存空間。

下面具體說下資料類型

1. 整數類型

有兩種類型的數字，整數和實數。如果儲存整數，可以使用這幾種整數類型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT，對應8,16,24,32,64位儲存空間。

 整數類型有UNSINGED屬性，表示不允許負數，這大致可以是整數的最大值上限提高一倍。

 mysql可以為整數類型指定寬度，如INT(7),但對大多數應用這時沒意義的。它不會限制值的範圍。對於儲存來說，INT(1)和INT(10)是一樣的。

　　2.實數類型

實數是帶有小數部分的數字。但是，它們不僅可以用來儲存小數部分，還可以使用DECIMAL儲存比BIGINT還大的整數。

浮點類型在儲存同樣範圍的值時，通常比DECIMAL使用更少的空間。FLOAT使用4個位元組儲存，DOUBLE使用8個位元組儲存。mysql使用DOUBLE作為內部浮點計算的類型。

注意，在資料量比較大時，例如儲存財務資料，可以考慮使用BIGINT替代DECIMAL，將需要儲存的貨幣單位，根據小數的位元乘以相應的倍數即可。這樣可以同時避免浮點儲存不精確和DECIMAL精確計算代價高的問題。

 　　3.字串類型

VARCHAR：用於儲存可變長字串。它比定長類型更節省空間的，因為它僅使用必要的空間。

VARCHAR需要使用1或2個額外位元組記錄字串的長度。如果列的最大長度<=255位元組，則使用1個位元組表示，否則使用2個位元組。

由於行是變長的，在update時可能會使行變得比原來長。下面情況下使用VARCHAR比較合適。

1.字串列的最大長度比平均長度大很多

2.列的更新很少，不會出現片段問題。

3.使用了想utf-8這樣複雜的字元集。

4.每個字元都使用不同的位元組數進行儲存。

 CHAR：定長，mysql總是根據定義的字串長度分配足夠的空間。

在儲存時，mysql會刪除所有的末尾空格。CHAR適合儲存很短的字串，或所有值都接近同一長度。

例如，CHAR適合儲存密碼的md5值，因為它是定長的。對於經常變更的資料，CHAR比VARCHAR更好，因為CHAR不容易產生片段。

 　　4.blob和text類型

兩者都是為儲存很大的資料而設計的字串資料型別，分別採用二進位和字元方式儲存。

 　　5.日期，時間類型

 mysql能儲存的最小時間粒紋為秒。

DATETIME:這個類型能儲存大範圍的值，從1001年到9999年，精度為秒，它把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數中，與時區不轉換，使用8個位元組的儲存空間。

TIMESTAMP:這個類型儲存了從1970年1月1日0時以來的秒數。它和unix時間戳記相同。它使用4個位元組儲存，因此範圍比DATETIME小很多，只能表示1970年到2038年。TIMESTAMP顯示的值與時區有關。

 建立高效能的索引

 索引可以包含一個或多個列的值。如果索引包含多個列，那列的順序十分重要，因為mysql只能高效的使用索引的最左首碼列。

 最常見的B-Tree索引，按照順序儲存資料，所以mysql可以做order by和group by操作。因為資料是有序的，所以B-Tree也會將相關的列值都儲存在一起。最後，因為索引中儲存了實際的值，所以某些查詢只使用索引，就能夠完成全部查詢。

 策略：

1.獨立的列：索引列不能是運算式的一部分，也不能是函數的參數。比如，

select id from a where id+1=5

select .... where TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10

 應該養成簡化where條件的習慣，始終將索引列單獨放在比較符號的一側。

 2.首碼索引：索引開始的部分字元。

對於像BLOB,TEXT這種很長的列，必須使用首碼索引。因為mysql不允許索引這些列的完整長度。

因此建立首碼索引的關鍵是，選擇足夠長的首碼，以保證較高的選擇性，同時又不能太長。

首碼應該足夠長，以使得首碼索引的選擇性接近於索引整個列。為了找出這個足夠長度，需要找到最常見的值的列表，然後和最常見的首碼列表進行比較。例如

可以看到每個值都在45到65之間，區分度不好。下面取3個前置詞字元

這次區分度就要好點了，下面繼續增加前置長度，最後發現前置長度為7時比較合適

計算合適前置長度的另一個方法是計算完整列的選擇性，並使首碼的選擇性接近於完整列的選擇性，具體的

 3.多列索引

一個常見的錯誤是，為每個列建立一個單獨的索引，或按照錯誤的順序建立多列索引。

 關於索引列的順序，正確的順序依賴於使用該索引的查詢，同時還要考慮是否滿足排序和分組的需要。

一個經驗：將選擇性最高的列放在索引的最前列。這個經驗在不需要考慮排序和分組的時候效果很好。這時候索引的作用只是在最佳化where條件的尋找。

事實上，效能不只是依賴於所有索引列的選擇性，也和查詢條件的具體值有關，也就是和值分布有關，這和前面說的選擇最佳前置長度需要考慮的地方一樣。換句話說，可能需要根據那些運行頻率最高的查詢來調整索引列的順序。

使用索引掃描排序

mysql有兩種方式產生有序結果，通過排序操作或按索引順序掃描。如果explain出來的type列的值為index,說明使用了索引掃描做排序。

掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就回表查詢一次對應的行。這基本上都是隨機的io.因此按索引順序讀取資料的速度通常比順序的全表掃描慢，尤其是在io密集型的工作負載中。

mysql可以使用同一個索引既滿足排序，又用於尋找行。因此，如果可能，設計索引時應該儘可能的滿足這兩種任務最好。

 只有當索引的順序和order by子句的順序完全一致，並且所有列的排列方向都一樣時，mysql才能使用索引對結果排序。

如果查詢需要關聯多張表，則只有當order by子句引用的欄位全部為第一個表時，才能使用索引做排序。

order by子句和尋找性查詢的限制是一樣的，需要滿足索引的最左首碼的要求，否則，mysql都需要執行排序操作。

 有一種情況可以不滿足索引的最左首碼要求，依然可以使用索引排序。那就是當前置量為常數時。例如

在一個表上，建立索引(a,b,c)。

select ... where a=“2014-12-21” order by b,c.

這時索引的第一列被指定為常數，可以使用索引。下面的也可以使用索引

select ... where a>"2014-12-21" order by b

select ... where a>"2014-12-21" order by a,b

這兩個剛好用了索引的首碼，所以也可以。

 下面是一些不能使用索引進行排序的查詢

select ... where a="2014-12-21" order by b DESC,c ESC

查詢使用了兩種不同的排序方向，但是索引列都是正序排序的。

select ... where a="2014-12-21" order by b，d

引用了一個不在索引中的列

select ... where a="2014-12-21" order by c

where和order by中的列無法組成索引的最左首碼，因為跳過了b這個列

select ... where a>"2014-12-21" order by b，c

第一列是範圍查詢

select ... where a>"2014-12-21" and b in(1,2) order by c

b列上有多個等於條件

 

<<高效能mysql>>筆記1