首頁技術(shù)文章正文

MySQL數(shù)據(jù)庫訪問性能優(yōu)化之?dāng)?shù)據(jù)表優(yōu)化

更新時間:2018-08-17 來源:黑馬程序員技術(shù)社區(qū) 瀏覽量:

  很多人都將 數(shù)據(jù)庫設(shè)計范式 作為數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計“圣經(jīng)”,認(rèn)為只要按照這個范式需求設(shè)計,就能讓設(shè)計出來的表結(jié)構(gòu)足夠優(yōu)化,既能保證性能優(yōu)異同時還能滿足擴(kuò)展性要求。但是被奉為“圣經(jīng)”的數(shù)據(jù)庫設(shè)計3范式早就已經(jīng)不完全適用了。這里我整理了一些比較常見的數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計方面的優(yōu)化技巧,希望對大家有用。 由于MySQL數(shù)據(jù)庫是基于行(Row)存儲的數(shù)據(jù)庫,而數(shù)據(jù)庫操作 IO 的時候是以 page(block)的方式,也就是說,如果我們每條記錄所占用的空間量減小,就會使每個page中可存放的數(shù)據(jù)行數(shù)增大,那么每次 IO 可訪問的行數(shù)也就增多了。反過來說,處理相同行數(shù)的數(shù)據(jù),需要訪問的 page 就會減少,也就是 IO 操作次數(shù)降低,直接提升性能。此外,由于我們的內(nèi)存是有限的,增加每個page中存放的數(shù)據(jù)行數(shù),就等于增加每個內(nèi)存塊的緩存數(shù)據(jù)量,同時還會提升內(nèi)存換中數(shù)據(jù)命中的幾率,也就是緩存命中率。

  數(shù)據(jù)類型選擇

  數(shù)字類型:盡量不使用DOUBLE,不僅僅只是存儲長度的問題,同時還會存在精確性的問題。同樣,固定精度的小數(shù),也不建議使用DECIMAL,建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲,可以大大節(jié)省存儲空間,且不會帶來任何附加維護(hù)成本。對于整數(shù)的存儲,在數(shù)據(jù)量較大的情況下,建議區(qū)分開 TINYINT / INT / BIGINT 的選擇,因為三者所占用的存儲空間也有很大的差別,能確定不會使用負(fù)數(shù)的字段,建議添加unsigned定義。當(dāng)然,如果數(shù)據(jù)量較小的數(shù)據(jù)庫,也可以不用嚴(yán)格區(qū)分三個整數(shù)類型。

  字符類型:盡量不使用 TEXT 數(shù)據(jù)類型,其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理。定長字段,建議使用 CHAR 類型,不定長字段盡量使用 VARCHAR,且僅僅設(shè)定適當(dāng)?shù)淖畲箝L度,而不是非常隨意的給一個很大的最大長度限定,因為不同的長度范圍,MySQL也會有不一樣的存儲處理。

  時間類型:盡量使用TIMESTAMP類型,因為其存儲空間只需要 DATETIME 類型的一半。對于只需要精確到某一天的數(shù)據(jù)類型,建議使用DATE類型,因為他的存儲空間只需要3個字節(jié),比TIMESTAMP還少。不建議通過INT類型類存儲一個unix timestamp 的值,因為這太不直觀,會給維護(hù)帶來不必要的麻煩,同時還不會帶來任何好處。

  ENUM & SET:對于狀態(tài)字段,可以嘗試使用 ENUM 來存放,因為可以極大的降低存儲空間,而且即使需要增加新的類型,只要增加于末尾,修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢?可以嘗試使用SET類型,即使存在多種屬性,同樣可以游刃有余,同時還可以節(jié)省不小的存儲空間。

  LOB類型:強(qiáng)烈反對在數(shù)據(jù)庫中存放 LOB 類型數(shù)據(jù),雖然數(shù)據(jù)庫提供了這樣的功能,但這不是他所擅長的,我們更應(yīng)該讓合適的工具做他擅長的事情,才能將其發(fā)揮到極致。在數(shù)據(jù)庫中存儲 LOB 數(shù)據(jù)就像讓一個多年前在學(xué)校學(xué)過一點Java的營銷專業(yè)人員來寫 Java 代碼一樣。

  字符編碼

  字符集直接決定了數(shù)據(jù)在MySQL中的存儲編碼方式,由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會有較大的差異,所以通過使用合適的字符集,可以幫助我們盡可能減少數(shù)據(jù)量,進(jìn)而減少IO操作次數(shù)。

  純拉丁字符能表示的內(nèi)容,沒必要選擇 latin1 之外的其他字符編碼,因為這會節(jié)省大量的存儲空間

  如果我們可以確定不需要存放多種語言,就沒必要非得使用UTF8或者其他UNICODE字符類型,這回造成大量的存儲空間浪費

  MySQL的數(shù)據(jù)類型可以精確到字段,所以當(dāng)我們需要大型數(shù)據(jù)庫中存放多字節(jié)數(shù)據(jù)的時候,可以通過對不同表不同字段使用不同的數(shù)據(jù)類型來較大程度減小數(shù)據(jù)存儲量,進(jìn)而降低 IO 操作次數(shù)并提高緩存命中率

  適當(dāng)拆分

  有些時候,我們可能會希望將一個完整的對象對應(yīng)于一張數(shù)據(jù)庫表,這對于應(yīng)用程序開發(fā)來說是很有好的,但是有些時候可能會在性能上帶來較大的問題。當(dāng)我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時候,如果我們大部分訪問這張表的時候都不需要這個字段,我們就該義無反顧的將其拆分到另外的獨立表中,以減少常用數(shù)據(jù)所占用的存儲空間。這樣做的一個明顯好處就是每個數(shù)據(jù)塊中可以存儲的數(shù)據(jù)條數(shù)可以大大增加,既減少物理 IO 次數(shù),也能大大提高內(nèi)存中的緩存命中率。

  上面幾點的優(yōu)化都是為了減少每條記錄的存儲空間大小,讓每個數(shù)據(jù)庫中能夠存儲更多的記錄條數(shù),以達(dá)到減少 IO 操作次數(shù),提高緩存命中率。下面這個優(yōu)化建議可能很多開發(fā)人員都會覺得不太理解,因為這是典型的反范式設(shè)計,而且也和上面的幾點優(yōu)化建議的目標(biāo)相違背。

  適度冗余

  為什么我們要冗余?這不是增加了每條數(shù)據(jù)的大小,減少了每個數(shù)據(jù)塊可存放記錄條數(shù)嗎?確實,這樣做是會增大每條記錄的大小,降低每條記錄中可存放數(shù)據(jù)的條數(shù),但是在有些場景下我們?nèi)匀贿€是不得不這樣做:

  被頻繁引用且只能通過 Join 2張(或者更多)大表的方式才能得到的獨立小字段

  這樣的場景由于每次Join僅僅只是為了取得某個小字段的值,Join到的記錄又大,會造成大量不必要的 IO,完全可以通過空間換取時間的方式來優(yōu)化。不過,冗余的同時需要確保數(shù)據(jù)的一致性不會遭到破壞,確保更新的同時冗余字段也被更新

  盡量使用 NOT NULL

  NULL 類型比較特殊,SQL 難優(yōu)化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異,會進(jìn)入索引中,但如果是一個組合索引,那么這個NULL 類型的字段會極大影響整個索引的效率。此外,NULL 在索引中的處理也是特殊的,也會占用額外的存放空間。

  很多人覺得 NULL 會節(jié)省一些空間,所以盡量讓NULL來達(dá)到節(jié)省IO的目的,但是大部分時候這會適得其反,雖然空間上可能確實有一定節(jié)省,倒是帶來了很多其他的優(yōu)化問題,不但沒有將IO量省下來,反而加大了SQL的IO量。所以盡量確保 DEFAULT 值不是 NULL,也是一個很好的表結(jié)構(gòu)設(shè)計優(yōu)化習(xí)慣。




作者:黑馬程序員人工智能+Python培訓(xùn)學(xué)院  
首發(fā):http://m.3rdspacecomics.com/special/pythonzly/index.shtml

分享到:
在線咨詢 我要報名
和我們在線交談!