主頁(yè) > 知識(shí)庫(kù) > 詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化

詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化

熱門(mén)標(biāo)簽:廣東人工電話機(jī)器人 芒果電話機(jī)器人自動(dòng)化 南通自動(dòng)外呼系統(tǒng)軟件 石家莊電商外呼系統(tǒng) 申請(qǐng)外呼電話線路 百度地圖圖標(biāo)標(biāo)注中心 信陽(yáng)穩(wěn)定外呼系統(tǒng)運(yùn)營(yíng)商 湖南人工外呼系統(tǒng)多少錢(qián) 日照旅游地圖標(biāo)注

最近在學(xué)習(xí)MySQL優(yōu)化方面的知識(shí)。本文就數(shù)據(jù)類型和schema方面的優(yōu)化進(jìn)行介紹。

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

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

  • 更小的通常更好

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

  • 簡(jiǎn)單就好

當(dāng)兩種數(shù)據(jù)類型都能勝任一個(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ù)類型時(shí),需遵循的步驟:首先確定合適的大類型,例如數(shù)據(jù)、字符串、時(shí)間等;然后再選擇具體的類型。下面將討論大類型下的一些具體類型,首先是數(shù)字,有兩種類型:整數(shù)和實(shí)數(shù)。

1.1 整數(shù)類型

整數(shù)類型和所占用的空間如下:

整數(shù)類型 空間大?。╞it)
TINYINT 8
SMALLINT 16
MEDIUMINT 24
INT 32
BIGINT 64

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

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

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

1.2 實(shí)數(shù)類型

實(shí)數(shù)類型的對(duì)比如下:

實(shí)數(shù)類型 空間大?。˙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)銷也更多。

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類型時(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ì)算的例子:

  • 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é)。
  • 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 字符串類型

最常用的字符串類型當(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)景 :

  • 最大長(zhǎng)度比平均長(zhǎng)度大很多;
  • 列的更新少,避免碎片;
  • 使用復(fù)雜的字符集,如UTF-8,每個(gè)字符能使用不同的字節(jié)存儲(chǔ)。

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

  • 長(zhǎng)度短;
  • 長(zhǎng)度相近,例如MD5;
  • 經(jīng)常更新。

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

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

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

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

TIMESTAMP顯示的值將依賴于時(shí)區(qū),意味在不同時(shí)區(qū)查詢到的值將不一樣。除了以上列出的不同,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ì)碼(可以理解為主鍵)的部分依賴。第三范式消除非主屬性對(duì)碼的傳遞依賴。

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

  • 更新操作更快;
  • 修改更少的數(shù)據(jù);
  • 表更小,更好地放內(nèi)存中,執(zhí)行操作更快;
  • 更少需要DISTINCT或GROUP BY。

但也由于數(shù)據(jù)分散存在各張表中,查詢時(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)上看到這樣一段話,大家可以感受下。

數(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ò)分依賴范式,快速突破范式局限性的人,自然是高手。

3. 緩存表和匯總表

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

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

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

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

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

4. 加快ALTER TABLE操作的速度

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

在一臺(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)值),而并未重建表。

參考

《高性能MySQL》

MySQL DECIMAL 數(shù)據(jù)類型

以上就是詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化的詳細(xì)內(nèi)容,更多關(guān)于MySQL 數(shù)據(jù)類型和schema優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • mysql decimal數(shù)據(jù)類型轉(zhuǎn)換的實(shí)現(xiàn)
  • mysql 數(shù)據(jù)類型轉(zhuǎn)換的實(shí)現(xiàn)
  • MySQL數(shù)據(jù)類型DECIMAL用法詳解
  • mysql中decimal數(shù)據(jù)類型小數(shù)位填充問(wèn)題詳解
  • MySQL數(shù)據(jù)類型全解析
  • 基于PostgreSQL和mysql數(shù)據(jù)類型對(duì)比兼容
  • MyBatis JdbcType 與Oracle、MySql數(shù)據(jù)類型對(duì)應(yīng)關(guān)系說(shuō)明
  • MySQL 數(shù)據(jù)類型選擇原則

標(biāo)簽:天津 公主嶺 沈陽(yáng) 呼和浩特 合肥 阿里 牡丹江 惠州

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化》,本文關(guān)鍵詞  詳解,MySQL,中的,數(shù)據(jù),類型,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章