mysql筆記01 Schema與資料類型最佳化

標籤：

Schema與資料類型最佳化

1. 選擇最佳化的資料類型

    1). 更小的通常更好：更小的資料類型通常更快，因為他們佔用更少的磁碟、記憶體和CPU緩衝，並且處理需要的CPU周期也更少。

    2). 簡單就好：簡單的資料類型的操作通常需要更少的CPU周期。例如：整型比字串操作的代價更低，因為字元集和校對規則(定序)是字串比較比整型比較更複雜。這裡有兩個例子：

                       一個是應該使用MySQL內建的類型而不是字串來儲存日期和時間，另外一個是應該使用整型儲存IP地址。

    3). 避免使用null：通常情況下最好指定列為not null，除非真的需要儲存null。因為null列使得索引、索引統計和值比較都更複雜。可為null的列會使用更多的儲存空間，在MySQL中也需要特殊處理。

2. MySQL相容性支援很多別名，例如INTEGER、BOOL等。他們都是別名，這些別名可能令人不解，但不會影響效能。如果建表時採用資料烈性的別名，然後用show create table檢查，會發現MySQL

    報告的是基本類型，而不是別名。

3. 整數類型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別使用8，16,24,32,64位儲存空間。它們的儲存值的範圍從-2的n-1次冪，到2的n-1次冪減一。

    1). 整數類型有可選的額UNSIGNED屬性，表示不允許負值，這大致可以使正數的上線提高一倍。

    2). 有符號和無符號類型使用相同的儲存空間，並且有相同的效能，因此可以更具實際情況選擇合適的類型。

    3). MySQL可以為正數類型指定寬度，例如INT(11)，但大多數應用這是沒有意義的。對於儲存和計算來說，INT(1)和INT(20)是相同的。

4. 實數類型：實數是帶有小數部分的數字。然而，它們不只是為了儲存小數部分；也可以使用DECIMAL儲存比BIGINT還大的整數。

    1). FLOAT和DOUBLE類型支援使用標準的浮點運算進行近視計算。DECIMAL類型用於儲存精確的小數。

    2). 浮點類型在儲存同樣類型的範圍的值時，通常比DECIMAL使用更少的空間。FLOAT使用4個位元組儲存.DOUBLE佔用8個位元組，相比FLOAT有更高的精度和更大的範圍。

    3). 因為需要額外的空間和計算開銷，所以應該盡量只在對小數進行精確計算時才使用DECIMAL。在資料量比較大的時候，可以考慮使用BIGINT代替DECIMAL，將對應的值擴大N倍。

5. 字串類型：

    1). VARCHAR:它比定長類型更節省空間的，因為它僅使用必要的空間。VARCHAR節省了空間，所以對效能也有協助。但是由於行是邊長的，在UPDATE時可能是行變得比原來更長，這就導致需要做額外的工作。

         下面的情況使用VARCHAR是合適的：字串最大長度比平均長度大很多；列的更新少，所以片段不死問題；使用了像UTF-8這樣複雜的字元集，每個字元使用不同的位元組數。

         在5.0或更高的版本中，MySQL在儲存和檢索時會保留末尾空格。InnoDB則更靈活，它可以把長的VARCHAR儲存為BLOB。

    2). CHAR: 定長，當儲存CHAR值時，MySQL會刪除所有的末尾空格。定長的CHAR類型不容易產生片段，對於非常短的列，CHAR比VARCHAR在儲存空間上也更有效率，VACHAR還有一個記錄長度的額外位元組。

    3). 記住字串的長度定義不是位元組數，是字元數。多位元組字元集會需要更多的空間儲存單個字元。

    4). 與CHAR和VARCHAR類似的類型還有BINARY和VARBINARY，它們儲存的是二進位字串。二進位字串中儲存的是位元組碼和不是字元。

         二進位比較的優勢並不僅僅體現在大小寫敏感上。MySQL比較BINARY字串是，每次按一個位元組，並且根據該位元組的數值進行比較。因此，二進位比字元比較簡單的多，所以也就更快。

    5). BLOB和TEXT類型：BLOB和TEXT都是為了儲存很大的資料而設計的字串資料型別，分別採用二進位和字元方式儲存。當BLOB和TEXT值太大時，InnoDB會使用專門的"外部"儲存地區來進行儲存。

6. 日期和時間類型：MySQL能儲存的最小時間粒紋為秒。

    1). DATETIME : 這個類型能儲存大範圍的值，從1001年到9999年，精度為秒。它把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數中。預設顯示格式為"2008-02-16 22:37:08"

    2). TIMESTAMP：儲存從1970年1月1日午夜依賴的秒數，它和UNIX時間戳記相同。只能表示從1970年到2038年。TIMESTAMP因為空白間佔用小，所以效率更高。

    3). 可以使用BIGINT類型儲存微秒層級的時間戳記。

7. 位元運算：BIT , SET 

8. 選擇標識符(identifier，主鍵) 

    1). 當選擇識別欄位的類型時，不僅僅需要考慮儲存類型，還需要考慮MySQL這種類型怎麼執行計算和比較

    2). 一旦選擇了一種類型，要確保在所有關聯表中都使用同樣的類型。會用不同資料類型可能導致效能問題，即使沒有效能影響，在比較操作時隱式類型轉換也可能導致很難發現錯誤。

    3). 在可以滿足值的範圍要求，並且預留未來增長空間的前提下，應該選擇最小的資料類型。

    4). 整數類型通常是識別欄位最好的選擇，因為它們很快並且可以使用AUTO_INCREMENT

    5). 如果可能，應該避免使用字串類型作為識別欄位，因為它們很消耗空間，並且通常比數字類型慢。

    6). 對於完全"隨機"的字串也需要多加註意。例如：MD5(),SHAI()或者UUID()產生的字串。這些函數產生的新值也任意分布在很大空間內，這會導致INSERT和一些SELECT語句很緩慢

    7).如果儲存UUID值，則應該移除"-"符號，或者更好的做法是，用UNHEX()函數轉換UUID值為16位元組的數字，並儲存在一個BINARY(16)的列中。檢索時再轉換回來。

9. 當心自動產生的schema(表)

10. 人們通常使用VARCHAR(15)來儲存IP地址。然而，它們實際是32位不帶正負號的整數，不是字串。用小數點將欄位分割成四段是為了閱讀方便。所以應該用不帶正負號的整數儲存IP地址。MySQL提供INET_ATON()

      和INET_NTOA()函數在這兩種表示方法之間轉換。

11. MySQL schema 設計中的陷阱：

      1). 太多的列

      2). 太多的關聯

      3). 全能的枚舉

      4). 變相的枚舉

      5). 非此發明的NULL：不要因為不適用NULL值，而走極端，根據實際情況也可以使用NULL

12. 範式的優點和缺點

      優點：

      1). 範式化的更新操作通常比反範式化要快

      2). 修改更少的資料

      3). 範式化的表通常表更小，可以更好地放在記憶體中，所以執行操作會更快

      缺點：通常需要關聯查詢，不僅代價昂貴，也可能使一些索引策略無效

13. 反範式的優點和缺點：

     優點：很好的避免關聯，更有效使用索引策略

     缺點：範式的優點，就是反範式的缺點

14. 緩衝表和匯總表：

     1). 緩衝表：儲存那些可以比較容易的從schema其他表擷取(但每次擷取速度緩慢)資料的表

     2). 匯總表：儲存的是使用GROUP BY語句彙總資料的表。即時計算統計值是很昂貴的操作。

     4). 在使用緩衝表和匯總表時，必須決定是即時維護資料還是定期重建。哪個更好依賴於應用程式，但是定期重建並不只是節省資源，可以保持表不會有很多片段，以及完全順序組織的索引。

15. 影子表：指的是在一張真實表"背後" 建立的表。當完成了建表操作後，可以通過一個原子的重新命名操作切換影子表和原表。原表盡量保留備份，防止新表出問題。

16. 物化視圖：物化視圖實際上是預先計算並且儲存在磁碟上的表，可以通過各種各樣的策略重新整理和更新。MySQL並不原聲支援物化視圖，可以使用開源工具Flexviews。

                   對比傳統的維護匯總表和緩衝表的方法，Flexviews通過提起對源表的更改，可以增量地重新計算物化視圖的內容。

 17. 計數器表：建立一張獨立的表格儲存體計數器通常是一個好主意。使用計數器表的一些技巧

       1). 為了防止互斥鎖影響效率，可以添加多條記錄，隨機更新一條記錄。統計總數時，將所有資料相加。

       2). 對於需要根據時間更新計數器，也可以用上述方法，不過多加一步操作。每天定時將前一天的總數合計起來，插入到計數器表，刪除那些零散的統計記錄。

18. 加快ALTER TABLE操作的速度：MySQL的ALTER TABLE操作的效能對大表來說是一個問題。MySQL執行大部分修改表結構的方法是用新的結構建立一個空表，從舊錶中查出所有資料插入新表，然後刪除舊錶。

      可以通過ALTER COLUMN操作來改變列的預設值。這個語句會直接修改.frm檔案而不涉及表資料。所以，這個操作非常快。

  

mysql筆記01 Schema與資料類型最佳化