本文主要給大家介紹MySQL如何優(yōu)化Schema與數(shù)據(jù)類型性能，文章內(nèi)容都是筆者用心摘選和編輯的，具有一定的針對性，對大家的參考意義還是比較大的，下面跟筆者一起了解下MySQL如何優(yōu)化Schema與數(shù)據(jù)類型性能吧。
10年的札達網(wǎng)站建設(shè)經(jīng)驗，針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時及時工作處理。成都營銷網(wǎng)站建設(shè)的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動調(diào)整札達建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計，從而大程度地提升瀏覽體驗。成都創(chuàng)新互聯(lián)從事“札達網(wǎng)站設(shè)計”,“札達網(wǎng)站推廣”以來，每個客戶項目都認(rèn)真落實執(zhí)行。
良好的邏輯設(shè)計和物理設(shè)計是高性能的基石，應(yīng)該根據(jù)系統(tǒng)將要執(zhí)行的查詢語句來設(shè)計schema。
反范式的設(shè)計可以加快某些類型的查詢，單同時可能使另一類型的查詢變慢，比如添加計數(shù)表和匯總表是一種很好的優(yōu)化查詢的方式，但這些表的維護成本可能會很高。
應(yīng)該盡量使用可以正確存儲數(shù)據(jù)的最小類型，更小的數(shù)據(jù)類型通常更快，因為他們占用更少的磁盤，內(nèi)存和CPU緩存，并且處理時需要的CPU周期更少。
更簡單的數(shù)據(jù)類型的操作通常需要更少的CPU周期。例如，整型數(shù)字比字符操作代價更低，因為字符集和校對規(guī)則（排序規(guī)則）使字符比較相對整型數(shù)字比較更復(fù)雜。比如，應(yīng)使用INTERGER存儲IP地址（inet_aton）
通常情況下，最好指定列為NOT NULL。如果查詢中包含可為NULL的列，對MySQL來說更難優(yōu)化，因為可為NULL的列使得索引，索引統(tǒng)計和值比較非常復(fù)雜，可為NULL的列會使用更多的存儲空間，當(dāng)可謂NULL的列被索引時，每個索引記錄需要一個額外的字節(jié)。但是把可為NULL的列改成NOT NULL帶來的性能提升比較小，但如果計劃在列上創(chuàng)建索引，就應(yīng)該避免設(shè)計成可為NULL的列。
| 整數(shù)類型 | 占用空間 | 范圍 |
|---|---|---|
| TINYINT | 8 | [-2^7,2^7-1] |
| SMALLINT | 16 | [-2^15,2^15-1] |
| MEDIUMINT | 24 | [-2^23,2^23-1] |
| INT | 32 | [-2^31,2^31-1] |
| BIGINT | 64 | [-2^63,2^63-1] |
整型類型有可選的UNSIGNED屬性，表示不允許負(fù)值，可以使原本正數(shù)的上線提高一倍。有符號和無符號類型使用相同的存儲空間，并具有相同的性能。整型之間相互計算，是以64位的BIGINT作為中間類型進行計算的。
實數(shù)是帶有小數(shù)部分的數(shù)字，可以使用DECIMAL存儲比BIGINT還大的整數(shù)。
DECIMAL類型用于存儲精確的小數(shù)，支持精確計算。例如，DECIMAL(18，9)小數(shù)點兩邊將各存儲9個數(shù)字，一共使用9個字節(jié)，其中小數(shù)點前面的數(shù)字使用。DECIMAL最多允許65個數(shù)字。
浮點類型在存儲同樣范圍的值時，通常比DECIMAL占用更少的空間，內(nèi)部計算時采用DOUBLE作為計算類型。
因為需要額外的空間和計算開銷，盡量只在對小鼠進行精確計算時才使用DECIMAL，在數(shù)據(jù)量比較大的時候，可以考慮使用BIGINT代替DECIMAL，講需要存儲的貨幣單位根據(jù)小數(shù)的位數(shù)乘以相應(yīng)的倍數(shù)即可。
varchar類型用于存儲可變長字符串，比定長更節(jié)省空間，varchar需要使用1個或2個額外字節(jié)記錄字符串的長度，如果列的最大長度小于或等于255個字節(jié)，則只是用1個字節(jié)表示，否則使用2個字節(jié)。varchar節(jié)省了存儲空間，所以對性能也有幫助。但是，由于行是變長的，如果在UPDATE時增加了該邊長列的實際存儲長度，這就導(dǎo)致需要額外的工作，如果一個行占用的空間增長，并且在頁內(nèi)沒有更多的存儲空間可以存儲，在這種情況下，InnoDB需要分裂頁來使行可以放進頁內(nèi)。
varchar使用場合：1.字符串列的最大長度比平均長度大很多，列的更新很少
2.使用了UTF-8這種復(fù)合的字符集（每個字符都使用不同的字節(jié)數(shù)存儲）
MySQL在存儲和檢索時會保留varchar尾部的空格。InnoDB可以把過長的VARCHAR存儲為BLOB。
定長字符串，MySQL在存儲時會去除char尾部的空格。會造成“A ”與“A”產(chǎn)生唯一性沖突。數(shù)據(jù)如何存儲取決于存儲引擎，填充和截取空格的行為是在MySQL服務(wù)層進行的。
更長的列會消耗更高的內(nèi)存，MySQL通常會分配固定大小的內(nèi)存來保存內(nèi)部值，尤其是使用內(nèi)存臨時表進行排序或操作總是會特別糟糕。
采用二進制的方式存儲，沒有排序規(guī)則和字符集。包含tinyblob,blob,mediumblob,longblob
采用字符串的方式存儲，有排序規(guī)則和字符集，包含tinytext,text,mediumtext,longtext。
與其他類型不同，MySQL把每個BLOB值和TEXT值當(dāng)作一個獨立的對象處理，存儲引擎在存儲時通常會做特殊處理，當(dāng)BLOB和TEXT值太大時，InnoDB會使用專門的“外部”存儲區(qū)域來存儲，在原本的行中使用指針指向外部的存儲區(qū)域。同事這兩種數(shù)據(jù)格式最多只能建立前綴索引。
枚舉不推薦使用（想了解可以參考原書）
現(xiàn)在推薦使用DATETIME，范圍更大，與時區(qū)無關(guān)，占用8個字節(jié)
InnoDB為每個BIT列使用一個足夠存儲的最小整數(shù)類型來存放，使用BIT類型并不能節(jié)省太多的存儲空間，MySQL把BIT當(dāng)作字符串類型，當(dāng)檢索BIT(1)的值時，結(jié)果是一個包含二進制0或者1的字符串。
MySQL的存儲引擎API在工作的時需要在云服務(wù)器層和存儲引擎層通過行緩沖格式拷貝數(shù)據(jù)，然后在云服務(wù)器層將行緩沖內(nèi)容解碼成各個列。從行緩沖中將編碼過的列轉(zhuǎn)換成行結(jié)構(gòu)的操作代價非常的高，轉(zhuǎn)換的代價依賴于列的數(shù)量。
一個粗略的經(jīng)驗法則，如果希望查詢執(zhí)行的快且并發(fā)性好，單個查詢最好在12個表內(nèi)做關(guān)聯(lián)
需要存儲一個事實上的“空值”到列表中時，可以使用0，某個特殊值，或者空字符串代替。MySQL會在索引中存儲NULL值，而Oracle則不會。
在范式化的數(shù)據(jù)庫中，每個事實數(shù)據(jù)只會出現(xiàn)一次，
反范式化的數(shù)據(jù)庫中，信息是冗余的，可能會存儲在多個地方。
范式化的更新操作更快，只需要更改較少的數(shù)據(jù)。
范式化的表更小，可以更好的放在內(nèi)存里，執(zhí)行操作會更快。
沒有多余的數(shù)據(jù)，可以減少distinct或GROUP BY的操作。
通常需要關(guān)聯(lián)，關(guān)聯(lián)代價昂貴，也可能使一些索引策略無效。
所有的數(shù)據(jù)都在一張表中，可以避免關(guān)聯(lián)。
不關(guān)聯(lián)的時候即使全表掃描，也是順序IO。
冗余的多余數(shù)據(jù)，更新更慢
表大，放到內(nèi)存中，占用大，容易擠出熱數(shù)據(jù)
為了提升讀查詢的速度，經(jīng)常會建一些額外索引，增加冗余列，甚至是創(chuàng)建緩存表和匯總表，這些方法會增加寫查詢的負(fù)擔(dān)。
寫操作變慢并不是讀操作變得更快所付出的唯一代價，還可能同時增加了讀操作和寫操作的并發(fā)難度。
ALTER TABLE操作對特大表來說，是個大問題。
MySQL執(zhí)行大部分修改表結(jié)構(gòu)的步驟：
1.用新結(jié)構(gòu)創(chuàng)建一個空表
2.從舊表中查出所有數(shù)據(jù)插入新表
3.刪除舊表
一般而言，大部分ALTER TABLE操作將導(dǎo)致MySQL服務(wù)對該表的訪問中斷。
對于常見的場景，常見的技巧有兩種：
1.現(xiàn)在一臺不提供服務(wù)的機器上執(zhí)行ALTER TABLE操作，然后切換
2.影子拷貝，即和原來的步驟一樣，但是通過觸發(fā)器的方式更新新表舊表數(shù)據(jù)，然后重命名
所有的MODIFY COLUMN操作，都會導(dǎo)致表重建。
下面這些操作是有可能不需要重建的：
移除一個列的AUTO_INCREMENT屬性
增加，移除，或更改ENUM和SET常量
步驟（本操作是火中取栗）：
1.創(chuàng)建一張有相同結(jié)構(gòu)的空表，進行所需要的修改
2.執(zhí)行FLUSH TABLES WITH READ LOCK。關(guān)閉所有正在使用的表，并且禁止表被打開
3.交換frm文件
4.執(zhí)行UNLOCK TABLES來釋放第二步的讀鎖。
1.避免設(shè)計過度復(fù)雜的數(shù)據(jù)庫模式
2.使用小而簡單的合適數(shù)據(jù)類型，盡可能避免使用NULL值
3.盡量使用相同的數(shù)據(jù)類型存儲相似或者相關(guān)的值。
4.可變長字符串在臨時表和排序時有可能悲觀的按照最大長度分配內(nèi)存。
5.盡量使用自增整數(shù)列定義主鍵
6.避免使用MySQL不再推薦的特性
7.謹(jǐn)慎對待BIT，ENUM,SET
看完以上關(guān)于MySQL如何優(yōu)化Schema與數(shù)據(jù)類型性能，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業(yè)知識信息 ，可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。
重慶分公司
重慶分公司
