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