《高效能MySQL》讀書筆記(1)

標籤：

慢查詢

當一個資源變得效率低下的時候，應該瞭解一下為什麼會這樣。有如下可能原因：
1.資源被過度使用，餘量已經不足以正常工作。
2.資源沒有被正確配置
3.資源已經損壞或者失靈

因為慢查詢，太多查詢的實踐過長而導致堆積在邏輯上。
慢查詢到底是原因還是結果？在深入調查前是無法知曉的。記住，在正常的時候這個查詢也是正常啟動並執行。一個查詢需要filesort和建立暫存資料表並不一定意味著就是有問題的。儘管消除filesort和暫存資料表通常來說是“最佳實務”。

MySQL資料類型

一般情況下，應該盡量使用可以正確儲存資料的最小資料類型。更小的資料類型通常更快，因為它們佔用更少的磁碟、記憶體和cpu緩衝，並且處理時需要的cpu周期也更少。
單一資料型別的操作通常需要更少的cpu周期，例如整型比字元操作代價耕地，因為字元集和校對規則（定序）使字元比較比整型比較更複雜，使用整型儲存IP地址。
盡量避免NULL，通常情況瞎最好制定列為NOT NULL，除非真的需要儲存NULL值。如果查詢中包含可為NULL的列，對mysql來說更難最佳化，因為可為NULL的列使得索引、索引統計和值比較都更複雜。

別名

MYSQL為了相容性支援很多別名，例如integer、bool、numeric，他們都只是別名，雖然可能令人不解，但是不會影響效能。如果建表的時候採用資料類型的別名，然後用show create table檢查，會發現mysql報告的是基本類型，而不是別名。

MySQL可以為整數類型指定寬度，例如int(11)，對大多數應用這是沒有意義的：它不會限制值的合法範圍，只是規定了mysql的一些互動工具（例如mysql命令列用戶端）用來顯示字元的個數。對於儲存和計算來說，int(1)和int(20)是相同的。

字元類型

varchar類型選用於儲存可變長字串，需要使用1或者2個額外位元組記錄字串的長度，比定長類型更節省空間的，因為它僅使用必要的空間。
char類型是定長的：MySQL總是根據定義的字串長度分配足夠的空間。
varchar的使用情境：
字串列的最大長度比平均長度大很多；列的更新很少，所以片段不是問題；使用了像utf-8這樣複雜的字元集，每個字元都使用不同的位元組數進行儲存。
char適合於儲存很短的字串，或者所有值都接近同一個長度。例如char非常適合儲存密碼MD5值，因為這是一個定長的值。對於經常變更的資料，char也比varchar更好，因為定長的char類型不容易產生片段。

大欄位字元類型

blob和text都是為了儲存很大的資料而設計的字串資料型別，分別採用二進位和字串方式儲存。MySQL對blob和text列進行排序於其他類型是不同的：它只對每個列的最前max_sort_length位元組而不是整個字串排序。
盡量避免使用text和blob類型，如果實在無法避免，有一個技巧實在所有用到blob欄位的地方都是用substring(column,length)將列值轉換為字串（在order by 子句中也適用），這樣就可以使用記憶體暫存資料表了。但是要確保截取的子字串足夠短，不會使暫存資料表的大小超過max_heap_table_size或tmp_table_size，超過以後Mysql會將記憶體臨時比哦啊轉換為MyISAM磁碟暫存資料表。

時間類型

timestamp類型儲存了從1970年1月1日午夜以來的秒數，timestamp只使用4個位元組的儲存空間，因此它的範圍比datatime小得多：只能表示從1970年到2038年。提供了from_unixtime函數把unix時間戳記轉換為日期、unix_timestamp函數把日期轉換為時間戳記。
有時候人們會將unix時間戳記儲存為整數值，這不會帶來任何收益。用整數儲存時間戳記的格式通常不方便處理，所以我們不推薦這麼做。

標識符（identifier）

整數通常是識別欄位最好的選擇，因為它們很快並且可以使用auto_increment；千萬不要使用enum和set類型作為識別欄位；盡量避免使用字串類型作為識別欄位，因為他們很消耗空間，並且通常比數字類型慢。尤其是在MyISAM表裡使用字串作為識別欄位時要特別小心，因為MyISAM預設對字串使用壓縮索引，這會導致查詢慢得多。

IP地址欄位

人們經常使用varchar(15)來儲存ip地址，然而，它們實際上是32位不帶正負號的整數，不是字串。MySQL提供INET_ATON()和INET_NTOA()函數將ip地址在整數和四段表示形式之間進行轉換。

資料庫中的範式和反範式

在範式化的資料庫中，每個事實資料會出現並且只出現一次；相反，在反範式化的資料庫中，資訊是冗餘的，可能會儲存在多個地方。
範式化設計的schema的缺點是通常需要關聯，較多的關聯可能使得一些索引策略無效，例如，範式化可能將列存放在不同的表中，而這些列如果在一個表中本可以屬於同一個索引。
反範式的schema因為所有資料都在一張表中，可以很好地避免關聯。缺點是update操作的代價高，需要更新多個表，至於這會不會是一個問題，需要考慮更新的頻率以及更新的時間長度，並和執行select查詢的頻率進行比較。
從另一個父表冗餘一些資料到子表的理由通常是排序的需要。
緩衝衍生值也是有用的。如果需要顯示每個使用者發了多少訊息（像很多論壇做的），可以每次執行一個昂貴的子查詢來計算並顯示它；也可以在user表中建一個num_messages列，每當使用者發新訊息時更新這個值。

緩衝表和匯總表

緩衝表和匯總表，即時計算統計值是非常昂貴的操作，因為要麼需要掃描表中的大部分資料，要麼查詢語句只能在某些特定的索引上才能有效運行，而這類特定索引一般會對update操作有影響，所以一般不希望建立這樣的索引。
使用緩衝表和匯總表時，必須決定是即時維護資料還是定期重建，哪個更好依賴於應用程式，但是定期重建並不只是節省資源，也可以保持表不會有很多片段，以及有完全順序組織的索引（這會更加高效）。

計數器應用

如果應用在表中儲存計數器，在更新計數器時可能會碰到並發問題。有一個技巧：將計數器儲存在多行中，更新計數+1的操作改為隨機播放一行進行更新，求計數值的時候，做一個sum求和。

表結構更改

一般而言，大部分alter table操作將導致MySQL服務中斷（鎖表並重建表）。MySQL執行大部分修改表結構的操作方法是用新的結構建立一個空表，從舊錶中查出所有資料插入新表，然後刪除舊錶。這昂操作可能需要花費很長時間。
不是所有的alter table操作都會引起表重建。理論上，MySQL可以跳過建立新表的步驟。列的預設值實際上存在表的.frm檔案中，所以可以直接修改這個檔案而不需要改動表本身。比如使用 alter comlum改變列的預設值：
alert table tablename
alert column col1 set default 5;
這個語句會直接修改.frm檔案而不涉及表資料，所以這個操作是非常快的。

 

第四章以前

《高效能MySQL》讀書筆記(1)