這篇文章將為大家詳細(xì)講解有關(guān)MySQL中怎么優(yōu)化Schema，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

創(chuàng)新互聯(lián)建站服務(wù)項(xiàng)目包括民豐網(wǎng)站建設(shè)、民豐網(wǎng)站制作、民豐網(wǎng)頁(yè)制作以及民豐網(wǎng)絡(luò)營(yíng)銷(xiāo)策劃等。多年來(lái)，我們專(zhuān)注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，民豐網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶(hù)以成都為中心已經(jīng)輻射到民豐省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶(hù)的支持與信任！

1. 選擇優(yōu)化的數(shù)據(jù)類(lèi)型

MySQL支持的數(shù)據(jù)類(lèi)型有很多，而如何選擇出正確的數(shù)據(jù)類(lèi)型，對(duì)于性能是至關(guān)重要的。以下幾個(gè)原則能夠幫助確定數(shù)據(jù)類(lèi)型：

更小的通常更好

應(yīng)盡可能使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類(lèi)型，夠用就好。這樣將占用更少的磁盤(pán)、內(nèi)存和緩存，而在處理時(shí)也會(huì)耗時(shí)更少。

簡(jiǎn)單就好

當(dāng)兩種數(shù)據(jù)類(lèi)型都能勝任一個(gè)字段的存儲(chǔ)工作時(shí)，選擇簡(jiǎn)單的那一方，往往是最好的選擇。例如整型和字符串，由于整型的操作代價(jià)要小于字符，所以當(dāng)在兩者之間選擇時(shí)，選擇整型通常能夠獲得更好的性能。

盡量避免NULL

當(dāng)列可為NULL時(shí)，對(duì)于MySQL來(lái)說(shuō)，在索引和值比較等方面需要做更多的工作，雖然對(duì)性能的影響不是很大，但也應(yīng)盡量避免設(shè)計(jì)為可為NULL。

除了以上原則，在選擇數(shù)據(jù)類(lèi)型時(shí)，需遵循的步驟：首先確定合適的大類(lèi)型，例如數(shù)據(jù)、字符串、時(shí)間等;然后再選擇具體的類(lèi)型。下面將討論大類(lèi)型下的一些具體類(lèi)型，首先是數(shù)字，有兩種類(lèi)型：整數(shù)和實(shí)數(shù)。

1.1 整數(shù)類(lèi)型

整數(shù)類(lèi)型和所占用的空間如下：

整數(shù)類(lèi)型	空間大小（bit）
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

整數(shù)類(lèi)型所能存儲(chǔ)的范圍和空間大小有關(guān)：-2^(N-1)至2^(N-1)-1，其中N為空間大小的位數(shù)。

整數(shù)類(lèi)型具有UNSIGNED的可選屬性，當(dāng)聲明時(shí)，表示不允許負(fù)數(shù)，則存儲(chǔ)范圍變?yōu)椋?至2^(N)-1，擴(kuò)大了一倍。

在MySQL中，還可以為整數(shù)類(lèi)型指定寬度，例如INT(1)，但這樣的意義并不大，并不會(huì)限制值的合法范圍，仍能存儲(chǔ)-2^31至2^31-1的值，所影響的是與MySQL的交互工具顯示字符的個(gè)數(shù)。

1.2 實(shí)數(shù)類(lèi)型

實(shí)數(shù)類(lèi)型的對(duì)比如下：

實(shí)數(shù)類(lèi)型	空間大?。˙yte）	取值范圍	計(jì)算精度
FLOAT	4	負(fù)數(shù)：-3.4E+38～-1.17E-38；非負(fù)數(shù)：0、1.17E-38~3.4E+38	近似計(jì)算
DOUBLE	8	負(fù)數(shù)：-1.79E+308～-2.22E-308；非負(fù)數(shù)：0、2.22E-308~1.79E+308	近似計(jì)算
DECIMAL	與精度有關(guān)	同DOUBLE	精確計(jì)算

從上面可以看出，F(xiàn)LOAT和DOUBLE都有固定的空間大小，但同時(shí)由于是使用標(biāo)準(zhǔn)的浮點(diǎn)運(yùn)算，所以只能近似計(jì)算。而DECIMAL則可以實(shí)現(xiàn)精確計(jì)算，與此同時(shí)占用的空間會(huì)相較更大，所耗費(fèi)的計(jì)算開(kāi)銷(xiāo)也更多。

DECIMAL所占空間大小與指定的精度有關(guān)，例如DECIMAL(M,D)：

• M為整個(gè)數(shù)字的最大長(zhǎng)度，取值范圍為[1, 65]，默認(rèn)值為10;

• D為小數(shù)點(diǎn)后的長(zhǎng)度，取值范圍為[0, 30]，且D <= M，默認(rèn)值為0。

MySQL在存儲(chǔ)DECIMAL類(lèi)型時(shí)會(huì)作為二進(jìn)制字符串存儲(chǔ)，每4個(gè)字節(jié)存9個(gè)數(shù)字，當(dāng)不足9位時(shí)，數(shù)字的占用空間如下：

數(shù)字個(gè)數(shù)	占用空間（Byte）
1、2	1
3、4	2
5、6	3
7、8	4

小數(shù)點(diǎn)前后將分別存儲(chǔ)，同時(shí)小數(shù)點(diǎn)也要占1個(gè)字節(jié)。下面舉兩個(gè)計(jì)算的例子：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. DECIMAL(18, 9)：整數(shù)部分長(zhǎng)度為9，占用4個(gè)字節(jié)。小數(shù)部分長(zhǎng)度為9，占用4個(gè)字節(jié)。同時(shí)加上小數(shù)點(diǎn)1個(gè)字節(jié)，則總共占用9個(gè)字節(jié)。

3. DECIMAL(20,  9)：整數(shù)部分長(zhǎng)度為14，占用7(4+3)個(gè)字節(jié)。小數(shù)部分長(zhǎng)度為9，占用4個(gè)字節(jié)。同時(shí)加上小數(shù)點(diǎn)1個(gè)字節(jié)，則總共占用12個(gè)字節(jié)。

可以看出DECIMAL的空間占用還是很大的，因此只有當(dāng)需要對(duì)小數(shù)進(jìn)行精確計(jì)算時(shí)，才需要使用DECIMAL。除此之外，我們還可以使用BIGINT代替DECIMAL，例如需要保證小數(shù)點(diǎn)后5位的計(jì)算，可以將值乘上10的5次方后作為BIGINT存儲(chǔ)，這樣能同時(shí)避免浮點(diǎn)存儲(chǔ)計(jì)算不精確和DECIMAL精確計(jì)算代價(jià)高的問(wèn)題。

1.3  字符串類(lèi)型

最常用的字符串類(lèi)型當(dāng)屬VARCHAR和CHAR。VARCHAR作為可變長(zhǎng)字符串，會(huì)使用1或2個(gè)額外字節(jié)記錄字符串的長(zhǎng)度，當(dāng)最大長(zhǎng)度未超過(guò)255時(shí)，只需1個(gè)字節(jié)記錄長(zhǎng)度，超過(guò)255，則需2個(gè)字節(jié)。VARCHAR的適用場(chǎng)景：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 最大長(zhǎng)度比平均長(zhǎng)度大很多;

3. 列的更新少，避免碎片;

4. 使用復(fù)雜的字符集，如UTF-8，每個(gè)字符能使用不同的字節(jié)存儲(chǔ)。

CHAR則為定長(zhǎng)字符串，根據(jù)定義的字符串長(zhǎng)度分配足夠的空間，適用場(chǎng)景：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 長(zhǎng)度短;

3. 長(zhǎng)度相近，例如MD5;

4. 經(jīng)常更新。

除了VARCHAR和CHAR，針對(duì)存儲(chǔ)大字符串，可以使用BLOB和TEXT類(lèi)型。BLOB和TEXT的區(qū)別在于，BLOB是以二進(jìn)制方式存儲(chǔ)，而TEXT是以字符方式存儲(chǔ)。這也導(dǎo)致，BLOB類(lèi)型的數(shù)據(jù)沒(méi)有字符集的概念，無(wú)法按字符排序，而TEXT類(lèi)型則有字符集的概念，可以按字符排序。兩者的使用場(chǎng)景，也由存儲(chǔ)格式?jīng)Q定了，當(dāng)存儲(chǔ)二進(jìn)制數(shù)據(jù)時(shí)，例如圖片，應(yīng)使用BLOB，而存儲(chǔ)文本時(shí)，例如文章，則應(yīng)使用TEXT類(lèi)型。

1.4 日期和時(shí)間類(lèi)型

MySQL中所能存儲(chǔ)的最小時(shí)間粒度為秒，常用的日期類(lèi)型有DATETIME和TIMESTAMP。

類(lèi)型	存儲(chǔ)內(nèi)容	空間大小（Byte）	時(shí)區(qū)概念
DATETIME	格式為YYYYMMDDHHMMSS的整數(shù)	8	無(wú)
TIMESTAMP	從1970年1月1日零點(diǎn)以來(lái)的秒數(shù)	4	有

TIMESTAMP顯示的值將依賴(lài)于時(shí)區(qū)，意味在不同時(shí)區(qū)查詢(xún)到的值將不一樣。除了以上列出的不同，TIMESTAMP還具有一個(gè)特殊屬性，在插入和更新時(shí)，如果沒(méi)有指定第一個(gè)TIMESTAMP列的值，將會(huì)設(shè)置這個(gè)列的值為當(dāng)前時(shí)間。

我們?cè)陂_(kāi)發(fā)過(guò)程中，應(yīng)盡量使用TIMESTAMP，主要是因?yàn)槠淇臻g大小僅需DATETIME的一半，空間效率更高。

如果我們想存儲(chǔ)的日期和時(shí)間精確到秒之后，怎么辦?由于MySQL并未提供，所以我們可以使用BIGINT存儲(chǔ)微妙級(jí)別的時(shí)間戳，或者使用DOUBLE存儲(chǔ)秒之后的小數(shù)部分。

1.5 選擇標(biāo)識(shí)符

通常來(lái)說(shuō)整數(shù)是標(biāo)識(shí)符的最好選擇，主要是因?yàn)槠浜?jiǎn)單，計(jì)算快，且可使用AUTO_INCREMENT。

2. 范式和反范式

簡(jiǎn)單來(lái)說(shuō)，范式就是一張數(shù)據(jù)表的表結(jié)構(gòu)所符合的某種設(shè)計(jì)標(biāo)準(zhǔn)的級(jí)別。第一范式，屬性不可分割，現(xiàn)在的RDBMS系統(tǒng)建成的表都是符合第一范式的。而第二范式，則是消除非主屬性對(duì)碼(可以理解為主鍵)的部分依賴(lài)。第三范式消除非主屬性對(duì)碼的傳遞依賴(lài)。具體的介紹，可以讀讀知乎上的這個(gè)回答(https://www.zhihu.com/question/24696366/answer/29189700)

嚴(yán)格范式化的數(shù)據(jù)庫(kù)中，每個(gè)事實(shí)數(shù)據(jù)會(huì)出現(xiàn)且只出現(xiàn)一次，不會(huì)出現(xiàn)數(shù)據(jù)冗余，這樣所能帶能帶來(lái)的好處有：

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

2. 更新操作更快;

3. 修改更少的數(shù)據(jù);

4. 表更小，更好地放內(nèi)存中，執(zhí)行操作更快;

5. 更少需要DISTINCT或GROUP BY。

但也由于數(shù)據(jù)分散存在各張表中，查詢(xún)時(shí)需要對(duì)表進(jìn)行關(guān)聯(lián)。而反范式的優(yōu)點(diǎn)則是不用進(jìn)行關(guān)聯(lián)，將數(shù)據(jù)冗余存儲(chǔ)。

在實(shí)際應(yīng)用中，不會(huì)出現(xiàn)完全的范式化或完全的反范式化，時(shí)常需要混用范式和反范式，使用部分范式化的schema，往往是最好的選擇。關(guān)于數(shù)據(jù)庫(kù)設(shè)計(jì)，在網(wǎng)上看到這樣一段話(huà)，大家可以感受下。

數(shù)據(jù)庫(kù)設(shè)計(jì)應(yīng)該分為三個(gè)境界：

第一境界：剛?cè)腴T(mén)數(shù)據(jù)庫(kù)設(shè)計(jì)，范式的重要性還未深刻理解。這時(shí)候出現(xiàn)的反范式設(shè)計(jì)，一般會(huì)出問(wèn)題。

第二境界：隨著遇到問(wèn)題解決問(wèn)題，漸漸了解到范式的真正好處，從而能快速設(shè)計(jì)出低冗余、高效率的數(shù)據(jù)庫(kù)。

第三境界：再經(jīng)過(guò)N年的鍛煉，是一定會(huì)發(fā)覺(jué)范式的局限性的。此時(shí)再去打破范式，設(shè)計(jì)更合理的反范式部分。

范式就像武俠里面的招數(shù)，初學(xué)者妄想不按招數(shù)來(lái)，只能死的很難堪。畢竟招數(shù)都是高手總結(jié)歸納的精華。而隨著武功提高，招數(shù)熟練之后，必然是發(fā)現(xiàn)招數(shù)的局限性，要么忘掉招數(shù)，要么自創(chuàng)招數(shù)。

只要努力，加上多熬幾年，總能達(dá)到第二個(gè)境界，總會(huì)覺(jué)得范式是經(jīng)典。此時(shí)能不過(guò)分依賴(lài)范式，快速突破范式局限性的人，自然是高手。

4. 緩存表和匯總表

除了上述說(shuō)到的反范式，在表中存儲(chǔ)冗余數(shù)據(jù)，我們還可以創(chuàng)建一張完全獨(dú)立的匯總表或緩存表，來(lái)滿(mǎn)足檢索的需要。

緩存表，指的是存儲(chǔ)可以從schema其他表中獲取數(shù)據(jù)的表，也就是邏輯上冗余的數(shù)據(jù)。而匯總表，則指的是存儲(chǔ)使用GROUP  BY等語(yǔ)句聚合數(shù)據(jù)，計(jì)算出的不冗余的數(shù)據(jù)。

緩存表，可用于優(yōu)化搜索和檢索查詢(xún)語(yǔ)句，這里可以使用的技巧有對(duì)緩存表使用不同的存儲(chǔ)引擎，例如主表使用InnoDB，而緩存表則可使用MyISAM，獲得更小的索引占用空間。甚至可以將緩存表放到專(zhuān)門(mén)的搜索系統(tǒng)中，例如Lucene。

匯總表，則是為了避免實(shí)時(shí)計(jì)算統(tǒng)計(jì)值所帶來(lái)的高昂代價(jià)，代價(jià)來(lái)自?xún)煞矫?，一是需要掃描表中的大部分?jǐn)?shù)據(jù)，二是建立特定的索引，會(huì)對(duì)UPDATE操作有影響。例如，查詢(xún)微信過(guò)去24小時(shí)的朋友圈數(shù)量，則可固定每1小時(shí)掃描全表，統(tǒng)計(jì)后寫(xiě)一條記錄到匯總表，當(dāng)查詢(xún)時(shí)，只需查詢(xún)匯總表上最新的24條記錄，而不必每次查詢(xún)時(shí)都去掃描全表進(jìn)行統(tǒng)計(jì)。

在使用緩存表和匯總表時(shí)，必須決定是實(shí)時(shí)維護(hù)數(shù)據(jù)還是定期重建，這取決于我們的需求。定期重建相比實(shí)時(shí)維護(hù)，能節(jié)省更多的資源，表的碎片更少。而在重建時(shí)，我們?nèi)孕璞ＷC數(shù)據(jù)在操作時(shí)可用，需要通過(guò)“影子表”來(lái)實(shí)現(xiàn)。在真實(shí)表后創(chuàng)建一張影子表，當(dāng)填充好數(shù)據(jù)后，通過(guò)原子的重命名操作來(lái)切換影子表和原表。

5. 加快ALTER TABLE操作的速度

當(dāng)MySQL在執(zhí)行ALTER  TABLE操作時(shí)，往往是新建一張表，然后把數(shù)據(jù)從舊表查出并插入到新表中，再刪除舊表，如果表很大，這樣需要花費(fèi)很長(zhǎng)時(shí)間，且會(huì)導(dǎo)致MySQL的服務(wù)中斷。為了避免服務(wù)中斷，通?？梢允褂脙煞N技巧：

在一臺(tái)不提供服務(wù)的機(jī)器上執(zhí)行ALTER TABLE操作，然后再與提供服務(wù)的主庫(kù)進(jìn)行切換;

“影子拷貝”，建立一張與原表無(wú)關(guān)的新表，在數(shù)據(jù)遷移完成后，通過(guò)重命名操作進(jìn)行切換。

但也不是所有的ALTER TABLE操作會(huì)引起表重建，例如在修改字段的默認(rèn)值時(shí)，使用MODIFY COLUMN會(huì)進(jìn)行表重建，而使用ALTER  COLUMN則不會(huì)進(jìn)行表重建，操作速度很快。這是因?yàn)锳LTER  COLUMN在修改默認(rèn)值時(shí)，會(huì)直接修改了存在表的.frm文件(存儲(chǔ)字段的默認(rèn)值)，而并未重建表。

關(guān)于MySQL中怎么優(yōu)化Schema就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

文章題目：MySQL中怎么優(yōu)化Schema
本文來(lái)源：http://chinadenli.net/article36/jeicsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營(yíng)銷(xiāo)推廣、網(wǎng)站設(shè)計(jì)、虛擬主機(jī)、搜索引擎優(yōu)化、關(guān)鍵詞優(yōu)化、品牌網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)