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

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

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

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

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

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

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

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

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

　　● VARCHAR的長(zhǎng)度只分配真正需要的空間。

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

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

　　● 合理的加入冗余字段可以提高查詢(xún)速度。

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

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

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

　　2.1 拆分時(shí)機(jī):

　　單表字段過(guò)多, 并且不需要一次性查詢(xún)返回展示到一個(gè)頁(yè)面上。

　　2.2 拆分方法:

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

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

　　拆分后查詢(xún)單表性能得到提升, 速度快。

　　2.4 缺點(diǎn):

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

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

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

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

　　3.1 拆分時(shí)機(jī):

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

　　3.2 拆分方法:

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

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

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

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

　　hash分表:

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

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

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

　　客戶(hù)端分庫(kù)分表: shardingJDBC, ShardingSphere等。

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

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

　　3.4 缺點(diǎn):

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

　　4.表分區(qū)

　　4.1 目的:

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

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

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

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

　　小編舉個(gè)栗子：

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