表結構優化方式詳解，玩轉MySQL資料庫

前言

從今天開始本系列文章就帶各位小夥伴學習資料庫技術。資料庫技術是Java開發中必不可少的一部分知識内容。也是非常重要的技術。本系列教程由淺入深, 全面講解資料庫體系。 非常适合零基礎的小夥伴來學習。

全文大約【2083】 字，不說廢話，隻講可以讓你學到技術、明白原理的純幹貨！本文帶有豐富案例及配圖視訊，讓你更好的了解和運用文中的技術概念，并可以給你帶來具有足夠啟迪的思考...

一. 資料庫結構優化

資料庫的庫表結構優化包含很多内容，例如：字段類型優化、垂直分表、水準分表、表分區、分庫等内容。 下面就帶各位小夥伴一起分析下具體内容。

1. 字段類型優化

字段類型優化的目的是将字段類型設定得更合理一些，我們總結的規則如下：

• 盡量将表字段定義為NOT NULL限制，這時由于在MySQL中含有空值的列很難進行查詢優化，NULL值會使索引以及索引的統計資訊變得很複雜。
• 對于隻包含特定類型的字段，可以使用enum、set 等資料類型。
• 數值型字段的比較比字元串的比較效率高得多，字段類型盡量使用最小、最簡單的資料類型。例如IP位址可以使用int類型。
• 盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數類型而非INT，如果非負則加上UNSIGNED。但對整數類型指定寬度，比如INT(11)，沒有任何用，因為指定的類型辨別範圍已經确定。
• VARCHAR的長度隻配置設定真正需要的空間。
• 盡量使用TIMESTAMP而非DATETIME，但TIMESTAMP隻能表示1970 - 2038年，比DATETIME表示的範圍小得多，而且TIMESTAMP的值因時區不同而不同。
• 單表不要有太多字段，建議在20以内。
• 合理的加入備援字段可以提高查詢速度。

2. 垂直拆分表（垂直切割）

垂直拆分按照字段進行拆分，其實就是把組成一行的多個列分開放到不同的表中，這些表具有不同的結構，拆分後的表具有更少的列。例如使用者表中的一些字段可能經常通路，可以把這些字段放進一張表裡。另外一些不經常使用的資訊就可以放進另外一張表裡。

插入的時候使用事務，也可以保證兩表的資料一緻。缺點也很明顯，由于拆分出來的兩張表存在一對一的關系，需要使用備援字段，而且需要join操作。但是我們可以在使用的時候可以分别取兩次，這樣的來說既可以避免join操作，又可以提高效率。

2.1 拆分時機:

單表字段過多, 并且不需要一次性查詢傳回展示到一個頁面上。

2.2 拆分方法:

一個頁面需要展示的資料字段, 盡量放在一張表中. 如果拆分成多個表, 那麼這多個表的關系是1:1:1:1......的關系。

2.3 優點 :

拆分後查詢單表性能得到提升, 速度快。

2.4 缺點:

如果拆分後, 需要查詢跨多張表的資料就會用到join連接配接查詢, 查詢速度會比沒拆分前慢。

3. 水準拆分表（水準切割）

水準拆分按照行進行拆分，常見的就是分庫分表。

以使用者表為例，可以取使用者ID，然後對ID取10的餘數，将使用者均勻的配置設定進這 0-9這10個表中。查找的時候也按照這種規則，又快又友善。

有些表業務關聯比較強，那麼可以使用按時間劃分的。例如每天的資料量很大，需要每天建立一張表。這種業務類型就是需要高速插入，但是對于查詢的效率不太關心。表越大，插入資料所需要索引維護的時間也就越長。

3.1 拆分時機:

MySQL單表大于500萬條資料, 就需要考慮水準分表, 因為單表資料量越大查詢越慢, 查詢效率成指數級下降。

3.2 拆分方法:

根據業務拆分:

根據時間範圍拆分, 例如: 訂單資料, 按照訂單建立時間, 一個月一張表。

按照區間分為拆分, 例如: 按照表自增id, 1-300萬條一張表。

按照業務分類拆分, 例如: 使用者表按照男女拆分。

hash分表:

通過一個原始目标的ID或者名稱通過一定的hash算法計算出資料存儲表的表名，然後通路相應的表。普通hash算法和一緻性hash算法均可。

一般企業利用工具來做水準分表：

服務端分庫分表: MyCat, corbar等。

用戶端分庫分表: shardingJDBC, ShardingSphere等。

3.3 優點:

提高了查詢效率, 主要是在大量資料情況, 提高了查詢的命中率。

3.4 缺點:

提高了複雜度. 在資料量不大的情況, 水準分表展現不出好處。

4. 表分區

4.1 目的

表分區給大家簡明扼要的說就是大量資料操作, 會有磁盤IO跟不上的情況, 是以表分區, 就是将儲存在硬碟上的資料檔案放到一塊或者多塊硬碟上, 以提高資料庫的IO性能。

分區适用于例如日志記錄，查詢少。一般用于背景的資料報表分析。對于這些資料彙總需求，需要很多日志表去做資料聚合，我們能夠容忍1s到2s的延遲，隻要資料準确能夠滿足需求就可以。

MySQL主要支援4種模式的分區：Range分區、List預定義清單分區，Hash 分區，Key鍵值分區。

4.2 錄入使用key鍵值分區

舉個栗子：

二. 結語

最後在這裡對本文核心要點進行總結:

1. 各位小夥伴需要熟練記住垂直分表和水準分表的方案以及優缺點，在合适的時機進行靈活使用。
2. 垂直分表一定要慎用，用不好會造成表結構變得複雜化不利于查詢。

以上就是我們本篇的全部内容了，更多技術類問題，歡迎和我們一起溝通交流~

存儲引擎的概念及作用，玩轉MySQL

SQL優化之EXPLAIN執行計劃

玩轉MySQL資料庫之SQL優化之慢查詢

MySQL優化思路及方向

更多技術類幹貨/IT程式員資訊，關注@千鋒教育