【MySQL調優】Schema與數據類型優化

schema優化就是指邏輯設計

選擇合適的數據類型：

1. 更小的通常更好

2. 簡單就好

3. 盡量避免NULL（null字段將會多占用1個字節來存儲是否為null）

基本數據類型

整數類型：

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

分別占用

8，16，24，32，64位的存儲空間

還可以區分是否只能為正數

實數類型：

浮點類型：float和double，分別占用4個字節和8個字節

應該盡量只在對小數進行精確計算的時候才能使用DECIMAL，當然有可能的時候還是建議使用倍數然后存儲在正整數。

字符串類型：

VARCHAR與CHAR

VARCHAR會有一位或者兩位 255上下，是變長的

CHAR是定長的

會有自動截斷的場景

BLOB和TEXT：

不建議在數據表中直接使用，分別屬于BLOB和TEXT兩個大家族

BLOB以二進制形式存儲，而TEXT有自己的字符集和排序規則

針對他們的排序，可以指定使用多長的前置字符串 max_sort_length

ENUM枚舉：

ENUM有自己的內部文件排序規則，將不會按照單純的字符串順序進行排序

在多表關聯的時候，enum實際上自己實現了整數類型向字符串（或者其他枚舉適用類型）的映射

我們都知道，在Mysql服務中，針對整數的比較處理速度要高于字符串，所以在實際的設計當中也可以盡可能使用整數作為主鍵或者關聯鍵，提升數據庫查詢速度

同時，ENUM關聯ENUM的速度也是比VARCHAR關聯VARCHAR快的

日期和時間：

Mysql最多支持到秒級數據

TIMESTAMP與DATETIME

datetime范圍更廣，默認1001到9999年，與時區無關，采用8個字節存儲

timestamp保存了從1970年1月1日以來的時間，最多只能到2038年的某天，所以在使用過程中應當注意邊界條件，timestamp可以自動實現時區的轉換，在多時區場景下訪問timestamp和datetime將會得到很不一樣的結果，所以在應用過程中盡量選擇一種單一的時間類型，同時，timestamp某人的類型是not null

timestamp具有更高的時間效率，只占用4個字節。

位數據類型：

BIT:

最大長度為64位，通過BIT(N)來指定N位的位存儲

MySQL把BIT當成字符串來處理而非數字，但是在數字上下文場景中又會將其轉換為數字，所以在使用的時候需要務必小心，考慮清楚上下文條件，在看能不能得到正確的結果

在整數列上進行按位操作可以節約大量的空間，比如訪問控制ACL，但是帶來的后果是數據庫設計的可讀性變差，需要大量的文檔來指導用戶某個位0或1的真正含義

標識符的選擇

所謂標識列，英文是identifiler column，在MySQL中多為主鍵或關聯鍵，外鍵。

選擇合適的標識符，需要考慮MySQL服務器對其的處理情況，同時也要考慮其在不同的存儲引擎中的存儲情況。

整數類型最好，因為可以設置成AUTO_INCREMENT，保證了有序性和磁盤放置的緊密型

ENUM和SET不推薦使用，因為擴展會帶來很多麻煩的問題，他們更適合存儲諸如性別，產品類型，邏輯狀態等固定不變的信息

字符串類型應當盡可能避免，因為它會占用大量的空間，尤其是針對InnoDB這種聚簇索引類型

使用MD5()、SHA1()或者UUID()可以獲得標識符不錯的隨機性，但是會導致插入速度很慢，以及批量的查詢也可能會變得很慢，因為他們在物理邏輯空間上并不是有序排放的

針對IPV4地址，推薦使用MySQL自建函數INET_ATON()和INET_NTOA()來將其保存為無符號整數類型。

Schema設計中應當避開的陷阱

太多的列

太多的關聯關系

全能的枚舉

變相的枚舉

Not Invent Here的NULL（就是不要害怕得完全不使用null，有時候null的場景會比一個魔鬼數字好很多）

范式與反范式

老生常談的問題，范式使得數據更加精簡，占用空間更小，維護起來更容易，但是查詢時可能會存在很多的關聯關系

反范式場景下通過提供數據冗余，犧牲了部分數據維護的有效程度來使得查詢速度更快

二者各有優缺點，這里我的建議是，融匯變通的使用范式與反范式，在存儲空間和查詢效率，數據維護效率之間進行權衡

緩存表與匯總表

緩存表中的數據可能是從某個表中查出來的部分數據，使用緩存表可以加快數據處理能力，同時不至于鎖死主表，高并發場景下可以犧牲部分時效性來換取更高的性能

而匯總表中的數據大多是group by或者sum之后的結果，這個數據在數據庫中是不存在的，使用匯總表可以預先完成一部分數據計算邏輯，減輕查詢時服務器的負擔

這些表與普通的OLTP操作表存在本質上的區別，應用場景也不盡相同

物理化視圖：

物理化視圖指的是具備CDC功能，分析mysql日志，直接記錄數據變更情況并生成相同結果的一個中間狀態。

Flexviews可以很好的為我們提供類似的功能，具體的思路是：

寫出一個SELECT語句從已經存在的數據表中獲取目標數據，使用Flexviews生成SQL語句向Flexviews的API調用，觀察結果。

計數器表：

在統計場景下計數器表很好用，可以加快統計效率，但是需要注意高并發寫入下鎖的等待問題

可以通過異步處理解決，當然更推崇使用更多的行，然后每次隨機選擇一行去進行技術增長，最后統計所有行的信息。

MySQL 數據結構

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。