全文大約【2083】字，不說廢話，只講可以讓你學(xué)到技術(shù)、明白原理的純干貨!本文帶有豐富案例及配圖視頻，讓你更好的理解和運(yùn)用文中的技術(shù)概念，并可以給你帶來具有足夠啟迪的思考......

　　一. 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

　　數(shù)據(jù)庫的庫表結(jié)構(gòu)優(yōu)化包含很多內(nèi)容，例如：字段類型優(yōu)化、垂直分表、水平分表、表分區(qū)、分庫等內(nèi)容。下面小編就帶各位小伙伴一起分析下具體內(nèi)容。

　　1.字段類型優(yōu)化

　　字段類型優(yōu)化的目的是將字段類型設(shè)置的更合理一些，小編總結(jié)的規(guī)則如下：

　　● 盡量將表字段定義為NOT NULL約束，這時由于在MySQL中含有空值的列很難進(jìn)行查詢優(yōu)化，NULL值會使索引以及索引的統(tǒng)計信息變得很復(fù)雜。

　　● 對于只包含特定類型的字段，可以使用enum、set 等數(shù)據(jù)類型。

　　● 數(shù)值型字段的比較比字符串的比較效率高得多，字段類型盡量使用最小、最簡單的數(shù)據(jù)類型。例如IP地址可以使用int類型。

　　● 盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類型而非INT，如果非負(fù)則加上UNSIGNED。但對整數(shù)類型指定寬度，比如INT(11)，沒有任何用，因?yàn)橹付ǖ念愋蜆?biāo)識范圍已經(jīng)確定。

　　● VARCHAR的長度只分配真正需要的空間。

　　● 盡量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多，而且TIMESTAMP的值因時區(qū)不同而不同。

　　● 單表不要有太多字段，建議在20以內(nèi)。

　　● 合理的加入冗余字段可以提高查詢速度。

　　2.垂直拆分表(垂直切割)

　　垂直拆分按照字段進(jìn)行拆分，其實(shí)就是把組成一行的多個列分開放到不同的表中，這些表具有不同的結(jié)構(gòu)，拆分后的表具有更少的列。例如用戶表中的一些字段可能經(jīng)常訪問，可以把這些字段放進(jìn)一張表里。另外一些不經(jīng)常使用的信息就可以放進(jìn)另外一張表里。

　　插入的時候使用事務(wù)，也可以保證兩表的數(shù)據(jù)一致。缺點(diǎn)也很明顯，由于拆分出來的兩張表存在一對一的關(guān)系，需要使用冗余字段，而且需要join操作。但是我們可以在使用的時候可以分別取兩次，這樣的來說既可以避免join操作，又可以提高效率。

　　2.1 拆分時機(jī):

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

　　2.2 拆分方法:

　　一個頁面需要展示的數(shù)據(jù)字段, 盡量放在一張表中. 如果拆分成多個表, 那么這多個表的關(guān)系是1:1:1:1......的關(guān)系。

　　2.3 優(yōu)點(diǎn) :

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

　　2.4 缺點(diǎn):

　　如果拆分后, 需要查詢跨多張表的數(shù)據(jù)就會用到j(luò)oin連接查詢, 查詢速度會比沒拆分前慢。

　　3.水平拆分表(水平切割)

　　水平拆分按照行進(jìn)行拆分，常見的就是分庫分表。以用戶表為例，可以取用戶ID，然后對ID取10的余數(shù)，將用戶均勻的分配進(jìn)這 0-9這10個表中。查找的時候也按照這種規(guī)則，又快又方便。

　　有些表業(yè)務(wù)關(guān)聯(lián)比較強(qiáng)，那么可以使用按時間劃分的。例如每天的數(shù)據(jù)量很大，需要每天新建一張表。這種業(yè)務(wù)類型就是需要高速插入，但是對于查詢的效率不太關(guān)心。表越大，插入數(shù)據(jù)所需要索引維護(hù)的時間也就越長。

　　3.1 拆分時機(jī):

　　MySQL單表大于500萬條數(shù)據(jù), 就需要考慮水平分表, 因?yàn)閱伪頂?shù)據(jù)量越大查詢越慢, 查詢效率成指數(shù)級下降。

　　3.2 拆分方法:

　　根據(jù)業(yè)務(wù)拆分:

　　根據(jù)時間范圍拆分, 例如: 訂單數(shù)據(jù), 按照訂單創(chuàng)建時間, 一個月一張表。

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

　　按照業(yè)務(wù)分類拆分, 例如: 用戶表按照男女拆分。

　　hash分表:

　　通過一個原始目標(biāo)的ID或者名稱通過一定的hash算法計算出數(shù)據(jù)存儲表的表名，然后訪問相應(yīng)的表。普通hash算法和一致性hash算法均可。

　　一般企業(yè)利用工具來做水平分表：

　　服務(wù)端分庫分表: MyCat, corbar等。

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

　　3.3 優(yōu)點(diǎn):

　　提高了查詢效率, 主要是在大量數(shù)據(jù)情況, 提高了查詢的命中率。

　　3.4 缺點(diǎn):

　　提高了復(fù)雜度. 在數(shù)據(jù)量不大的情況, 水平分表體現(xiàn)不出好處。

　　4.表分區(qū)

　　4.1 目的:

　　表分區(qū)小編給大家簡明扼要的說就是大量數(shù)據(jù)操作, 會有磁盤IO跟不上的情況, 所以表分區(qū), 就是將保存在硬盤上的數(shù)據(jù)文件放到一塊或者多快硬盤上, 以提高數(shù)據(jù)庫的IO性能。

　　分區(qū)適用于例如日志記錄，查詢少。一般用于后臺的數(shù)據(jù)報表分析。對于這些數(shù)據(jù)匯總需求，需要很多日志表去做數(shù)據(jù)聚合，我們能夠容忍1s到2s的延遲，只要數(shù)據(jù)準(zhǔn)確能夠滿足需求就可以。

　　MySQL主要支持4種模式的分區(qū)：Range分區(qū)、List預(yù)定義列表分區(qū)，Hash 分區(qū)，Key鍵值分區(qū)。

　　4.2 錄入使用key鍵值分區(qū)

　　小編舉個栗子：

CREATE TABLE `test2` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(100) DEFAULT NULL COMMENT '名稱',
  `state` int(1) DEFAULT NULL COMMENT '狀態(tài)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (id)
PARTITIONS 10;