建站
咨詢
售后
建站咨詢
新聞中心
良好的邏輯設計和物理設計是高性能的基石， 應該根據(jù)系統(tǒng)將要執(zhí)行的查詢語句來設計schema, 這往往需要權衡各種因素。
一、選擇優(yōu)化的數(shù)據(jù)類型
MySQL支持的數(shù)據(jù)類型非常多， 選擇正確的數(shù)據(jù)類型對于獲得高性能至關重要。
更小的通常更好
更小的數(shù)據(jù)類型通常更快， 因為它們占用更少的磁盤、 內存和CPU緩存， 并且處理時需要的CPU周期也更少。
簡單就好
簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。 例如， 整型比字符操作代價更低， 因為字符集和校對規(guī)則（排序規(guī)則 ）使字符比較比整型比較更復雜。
盡量避免NULL
如果查詢中包含可為NULL 的列， 對MySQL來說更難優(yōu)化， 因為可為NULL 的列使得索引、 索引統(tǒng)計和值比較都更復雜。 可為NULL的列會使用更多的存儲空間， 在MySQL里也需要特殊處理。 當可為NULL的列被索引時， 每個索引記錄需要一個額外的字節(jié)， 在MyISAM里甚至還可能導致固定大小的索引（例如只有一個整數(shù)列的索引）變成可變大小的索引。
當然也有例外， 例如InnoDB 使用單獨的位 (bit) 存儲NULL值， 所以對于稀疏數(shù)據(jù)有很好的空間效率。
1.整數(shù)類型
有兩種類型的數(shù)字：整數(shù) (whole number) 和實數(shù) (real number) 。 如果存儲整數(shù)， 可以使用這幾種整數(shù)類型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。分別使用8,16, 24, 32, 64位存儲空間。
整數(shù)類型有可選的 UNSIGNED 屬性，表示不允許負值，這大致可以使正數(shù)的上限提高一倍。 例如 TINYINT. UNSIGNED 可以存儲的范圍是 0 – 255, 而 TINYINT 的存儲范圍是 -128 -127 。
有符號和無符號類型使用相同的存儲空間，并具有相同的性能 ， 因此可以根據(jù)實際情況選擇合適的類型。
你的選擇決定 MySQL 是怎么在內存和磁盤中保存數(shù)據(jù)的。 然而， 整數(shù)計算一般使用64 位的 BIGINT 整數(shù)， 即使在 32 位環(huán)境也是如此。（ 一些聚合函數(shù)是例外， 它們使用DECIMAL 或 DOUBLE 進行計算）。
MySQL 可以為整數(shù)類型指定寬度， 例如 INT(11), 對大多數(shù)應用這是沒有意義的：它不會限制值的合法范圍，只是規(guī)定了MySQL 的一些交互工具（例如 MySQL 命令行客戶端）用來顯示字符的個數(shù)。 對于存儲和計算來說， INT(1) 和 INT(20) 是相同的。
2.實數(shù)類型
實數(shù)是帶有小數(shù)部分的數(shù)字。 然而， 它們不只是為了存儲小數(shù)部分，也可以使用DECIMAL 存儲比 BIGINT 還大的整數(shù)。
FLOAT和DOUBLE類型支持使用標準的浮點運算進行近似計算。
DECIMAL類型用于存儲精確的小數(shù)。
浮點和DECIMAL類型都可以指定精度。 對于DECIMAL列， 可以指定小數(shù)點前后所允許的最大位數(shù)。這會影響列的空間消耗。
有多種方法可以指定浮點列所需要的精度， 這會使得MySQL選擇不同的數(shù)據(jù)類型，或者在存儲時對值進行取舍。 這些精度定義是非標準的，所以我們建議只指定數(shù)據(jù)類型，不指定精度。
浮點類型在存儲同樣范圍的值時， 通常比DECIMAL使用更少的空間。FLOAT使用4個字節(jié)存儲。DOUBLE占用8個字節(jié)，相比FLOAT有更高的精度和更大的范圍。和整數(shù)類型一樣， 能選擇的只是存儲類型； MySQL使用DOUBLE作為內部浮點計算的類型。
因為需要額外的空間和計算開銷，所以應該盡量只在對小數(shù)進行精確計算時才使用DECIMAL。但在數(shù)據(jù)最比較大的時候， 可以考慮使用BIGINT代替DECIMAL, 將需要存儲的貨幣單位根據(jù)小數(shù)的位數(shù)乘以相應的倍數(shù)即可。
3.字符串類型
VARCHAR
用于存儲可變?字符串，長度支持到65535 需要使用1或2個額外字節(jié)記錄字符串的長度 適合：字符串的最大?度比平均?度?很多；更新很少
CHAR
定?，?度范圍是1~255 適合：存儲很短的字符串，或者所有值接近同一個長度；經(jīng)常變更
慷慨是不明智的
使用VARCHAR(5)和VARCHAR(200)存儲’hello’的空間開銷是一樣的。 那么使用更短的列有什么優(yōu)勢嗎？
事實證明有很大的優(yōu)勢。 更長的列會消耗更多的內存， 因為MySQL通常會分配固定大小的內存塊來保存內部值。 尤其是使用內存臨時表進行排序或操作時會特別糟糕。 在利用磁盤臨時表進行排序時也同樣糟糕。
所以最好的策略是只分配真正需要的空間。
4.BLOB和TEXT類型
BLOB和 TEXT都是為存儲很大的數(shù)據(jù)而設計的字符串數(shù)據(jù)類型， 分別采用 二進制和字符方式存儲 。
與其他類型不同， MySQL把每個BLOB和TEXT值當作一個獨立的對象處理。 存儲引擎在存儲時通常會做特殊處理。 當BLOB和TEXT值太大時，InnoDB會使用專門的 “外部“存儲區(qū)域來進行存儲， 此時每個值在行內需要1 – 4個字節(jié)存儲 存儲區(qū)域存儲實際的值。
BLOB 和 TEXT 之間僅有的不同是 BLOB 類型存儲的是二進制數(shù)據(jù)， 沒有排序規(guī)則或字符集， 而 TEXT類型有字符集和排序規(guī)則
5.日期和時間類型
大部分時間類型都沒有替代品， 因此沒有什么是最佳選擇的問題。 唯一的問題是保存日期和時間的時候需要做什么。 MySQL提供兩種相似的日期類型： DATE TIME和 TIMESTAMP。
但是目前我們更建議存儲時間戳的方式，因此該處不再對 DATE TIME和 TIMESTAMP做過多說明。
6.其他類型
6.1選擇標識符
在可以滿足值的范圍的需求， 井且預留未來增長空間的前提下， 應該選擇最小的數(shù)據(jù)類型。
整數(shù)類型
整數(shù)通常是標識列最好的選擇， 因為它們很快并且可以使用AUTO_INCREMENT。
ENUM和SET類型
對于標識列來說，EMUM和SET類型通常是一個糟糕的選擇， 盡管對某些只包含固定狀態(tài)或者類型的靜態(tài) ”定義表” 來說可能是沒有問題的。ENUM和SET列適合存儲固定信息， 例如有序的狀態(tài)、 產(chǎn)品類型、 人的性別。
字符串類型
如果可能， 應該避免使用字符串類型作為標識列， 因為它們很消耗空間， 并且通常比數(shù)字類型慢。
對于完全 “隨機” 的字符串也需要多加注意， 例如 MDS() 、 SHAl() 或者 UUID() 產(chǎn)生的字符串。 這些函數(shù)生成的新值會任意分布在很大的空間內， 這會導致 INSERT 以及一些SELECT語句變得很慢。如果存儲 UUID 值， 則應該移除 “-“符號。
6.2特殊類型數(shù)據(jù)
某些類型的數(shù)據(jù)井不直接與內置類型一致。 低千秒級精度的時間戳就是一個例子，另一個例子是以個1Pv4地址，人們經(jīng)常使用VARCHAR(15)列來存儲IP地址，然而， 它們實際上是32位無符號整數(shù)， 不是字符串。用小數(shù)點將地址分成四段的表示方法只是為了讓人們閱讀容易。所以應該用無符號整數(shù)存儲IP地址。MySQL提供INET_ATON()和INET_NTOA()函數(shù)在這兩種表示方法之間轉換。
二、表結構設計
1.范式和反范式
對于任何給定的數(shù)據(jù)通常都有很多種表示方法， 從完全的范式化到完全的反范式化， 以及兩者的折中。 在范式化的數(shù)據(jù)庫中， 每個事實數(shù)據(jù)會出現(xiàn)并且只出現(xiàn)一次。 相反， 在反范式化的數(shù)據(jù)庫中， 信息是冗余的， 可能會存儲在多個地方。
范式的優(yōu)點和缺點
為性能提升考慮時，經(jīng)常會被建議對 schema 進行范式化設計，尤其是寫密集的場景。
范式化的更新操作通常比反范式化要快。 當數(shù)據(jù)較好地范式化時，就只有很少或者沒有重復數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)。 范式化的表通常更小，可以更好地放在內存里，所以執(zhí)行操作會更快。 很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時更少需要 DISTINCT 或者 GROUP BY語句。
反范式的優(yōu)點和缺點
不需要關聯(lián)表，則對大部分查詢最差的情況——即使表沒有使用索引——是全表掃描。 當數(shù)據(jù)比內存大時這可能比關聯(lián)要快得多，因為這樣避免了隨機I/0。
單獨的表也能使用更有效的索引策略。
混用范式化和反范式化
在實際應用中經(jīng)常需要混用，可能使用部分范式化的 schema 、 緩存表，以及其他技巧。
表適當增加冗余字段，如性能優(yōu)先，但會增加復雜度?？杀苊獗黻P聯(lián)查詢。
簡單熟悉數(shù)據(jù)庫范式
第一范式(1NF)：字段值具有原子性,不能再分(所有關系型數(shù)據(jù)庫系統(tǒng)都滿足第一范式);例如：姓名字段,其中姓和名是一個整體,如果區(qū)分姓和名那么必須設立兩個獨立字段;
第二范式(2NF)：一個表必須有主鍵,即每行數(shù)據(jù)都能被唯一的區(qū)分; 備注：必須先滿足第一范式;
第三范式(3NF)：一個表中不能包涵其他相關表中非關鍵字段的信息,即數(shù)據(jù)表不能有沉余字段; 備注：必須先滿足第二范式;
2.表字段少精
I/O高效 字段分開維護簡單 單表1G體積 500W?行評估 單?行不超過200Byte 單表不超過50個INT字段 單表不超過20個CHAR(10)字段 建議單表字段數(shù)控制在20個以內 拆分TEXT/BLOB，TEXT類型處理性能遠低于VARCHAR，強制生成硬盤臨時表浪費更多空間。
當前標題：構建MySQL高性能表
轉載源于：http://m.fisionsoft.com.cn/article/codseod.html
