良好的邏輯設計和物理設計是高性能的基石,
應該根據(jù)系統(tǒng)將要執(zhí)行的查詢語句來設計schema,
這往往需要權(quán)衡各種因素。本文和大家分享的就是MySQL
高性能表設計的一些
規(guī)范,一起來看看吧,希望對大家
學習mysql有所幫助。
一、選擇優(yōu)化的數(shù)據(jù)類型
MySQL
支持的數(shù)據(jù)類型非常多, 選擇正確的數(shù)據(jù)類型對千獲得高性能至關重要。
更小的通常更好
更小的數(shù)據(jù)類型通常更快,
因為它們占用更少的磁盤、
內(nèi)存和CPU
緩存, 并且處理時需要的
CPU
周期也更少。
簡單就好
簡單數(shù)據(jù)類型的操作通常需要更少的CPU
周期。 例如, 整型比字符操作代價更低, 因為字符集和校對規(guī)則(排序規(guī)則 )使字符比較比整型比較更復雜。
盡量避免NULL
如果查詢中包含可為NULL
的列, 對
MySQL
來說更難優(yōu)化, 因為可為
NULL
的列 使得索引、 索引統(tǒng)計和值比較都更復雜。 可為
N ULL
的列會使用更多的存儲空間, 在
MySQL
里也需要特殊處理。 當可為
NULL
的列被索引時, 每個索引記錄需要一個額 外的字節(jié), 在
MyISAM
里甚至還可能導致固定大小的索引(例如只有一個整數(shù)列的索引)變成可變大小的索引。
當然也有例外,
例如值得一提的是, lnnoDB
使用單獨的位
(bit)
存儲
NULL
值, 所以對于稀疏數(shù)據(jù)注
4
有很好的空間效率。
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
是怎么在內(nèi)存和磁盤中保存數(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作為內(nèi)部浮點計算的類型。
因為需要額外的空間和計算開銷,所以應該盡扯只在對小數(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)勢。
更長的列會消耗更多的內(nèi)存,
因為MySQL
通常會分配固定大小的內(nèi)存塊來保存內(nèi)部值。 尤其是使用內(nèi)存臨時表進行排序或操作時會特別糟糕。 在利用磁盤臨時表進行排序時也同樣糟糕。
所以最好的策略是只分配真正需要的空間。
4.BLOB和TEXT類型
BLOB
和
TEXT
都是為存儲很大的數(shù)據(jù)而設計的字符串數(shù)據(jù)類型, 分別采用 二進制和字符方式存儲 。
與其他類型不同, MySQL
把每個
BLOB
和
TEXT
值當作一個獨立的對象處理。 存儲引擎 在存儲時通常會做特殊處理。 當
BLOB
和
TEXT
值太大時,
InnoDB
會使用專門的
“
外部
“
存儲區(qū)域來進行存儲, 此時每個值在行內(nèi)需要
1 – 4
個字節(jié)存儲 存儲區(qū)域存儲實際的值。
BLOB
和
TEXT
之間僅有的不同是
BLOB
類型存儲的是二進制數(shù)據(jù), 沒有排序規(guī)則或字符集, 而
TEXT
類型有字符集和排序規(guī)則
5.日期和時間類型
大部分時間類型
都沒有替代品,
因此沒有什么是最佳選擇的問題。
唯一的問題是保存日期和時間的時候需要做什么。 MySQL
提供兩種相似的日期類型:
DATE TIME
和
TIMESTAMP
。
但是目前我們更建議存儲時間戳的方式,因此該處不再對 DATE TIME
和
TIMESTAMP
做過多說明。
5.其他類型
5.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ù)生成的新值會任意分布在很大的空間內(nèi), 這會導致
INSERT
以及一些
SELECT
語句變得很慢。如果存儲
UUID
值, 則應該移除
“-“
符號。
5.2特殊類型數(shù)據(jù)
某些類型的數(shù)據(jù)井不直接與內(nèi)置類型一致。
低千秒級精度的時間戳就是一個例子,另一個例子是以個1Pv4
地址,人們經(jīng)常使用
VARCHAR(15)
列來存儲
IP
地址,然而, 它們實際上是
32
位無符號整數(shù), 不是字符串。用小數(shù)點將地址分成四段的表示方法只是為了讓人們閱讀容易。所以應該用無符號整數(shù)存儲
IP
地址。
MySQL
提供
INET_ATON()
和
INET_NTOA()
函數(shù)在這兩種表示方法之間轉(zhuǎn)換。
二、表結(jié)構(gòu)設計
1.范式和反范式
對千任何給定的數(shù)據(jù)通常都有很多種表示方法,
從完全的范式化到完全的反范式化,
以及兩者的折中。
在范式化的數(shù)據(jù)庫中,
每個事實數(shù)據(jù)會出現(xiàn)并且只出現(xiàn)一次。
相反,
在反范式化的數(shù)據(jù)庫中,
信息是冗余的,
可能會存儲在多個地方。
范式的優(yōu)點和缺點
為性能提升考慮時,經(jīng)常會被建議對 schema
進行范式化設計,尤其是寫密集的場景。
·
范式化的更新操作通常比反范式化要快。
·
當數(shù)據(jù)較好地范式化時,就只有很少或者沒有重復數(shù)據(jù),所以只需要修改更少的數(shù)據(jù)。
·
范式化的表通常更小,可以更好地放在內(nèi)存里,所以執(zhí)行操作會更快。
·
很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時更少需要
DISTINCT
或者
GROUP BY
語句。
反范式的優(yōu)點和缺點
不需要關聯(lián)表,則對大部分查詢最差的情況——
即使表沒有使用索引
——
是全表掃描。 當數(shù)據(jù)比內(nèi)存大時這可能比關聯(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
個以內(nèi)
·
拆分
TEXT/BLOB
,
TEXT
類型處理性能遠低于
VARCHAR
,強制生成硬盤臨時表浪費更多空間。
來源:伯樂在線