哈嘍大家好呀!我是小三。今天來講分庫分表：)

什么是分庫分表

分庫分表：在一些數(shù)據(jù)庫大的項目中，隨著時間的推移和業(yè)務(wù)量的增加，數(shù)據(jù)庫里的表中數(shù)據(jù)就會越來越多，如果單單還使用上面的模式，顯然是不夠用的。這時候就想到了把一個庫里的數(shù)據(jù)分散到多個庫里，并且把存在一個表里的數(shù)據(jù)分散到多個表里。

「分庫分表的方式有垂直切分，水平切分」

分庫分表能帶來什么樣的好處

第一、能解決數(shù)據(jù)庫本身的瓶頸，當(dāng)連接數(shù)過多時，就會出現(xiàn)‘too many connections’的錯誤，這種訪問量 太大或者是數(shù)據(jù)庫設(shè)置的最大連接數(shù)太小的原因。mysql的默認的最大連接數(shù)是100，可以進行修改，而mysql服務(wù)允許最大的連接數(shù)為16384。數(shù)據(jù)庫分表可以解決單表海量數(shù)據(jù)的查詢性能問題，數(shù)據(jù)庫分庫可以解決單臺數(shù)據(jù)庫的并發(fā)訪問壓力問題。

第二、解決系統(tǒng)本身的IO、CPU瓶頸。磁盤讀寫IO瓶頸：熱點數(shù)據(jù)量很多的情況下，盡量使用了數(shù)據(jù)庫本身的緩存，但是依舊有大量IO，導(dǎo)致sql執(zhí)行速度慢。網(wǎng)絡(luò)IO瓶頸：請求的數(shù)據(jù)太多，數(shù)據(jù)傳輸量大了，網(wǎng)絡(luò)帶寬就顯得不夠用了，鏈路的響應(yīng)時間就變長了。CPU瓶頸，在進行基礎(chǔ)的數(shù)據(jù)量大單機復(fù)制SQL計算的時候，SQL語句執(zhí)行占用CPU的使用率就會變高，不單只這種原因，也有掃描行數(shù)大、鎖沖突、鎖等待等等原因。

可以通過show processlist; 、show full processlist，發(fā)現(xiàn) CPU 使用率比較高的SQL。常見的對于查詢時間長，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能問題SQL，清楚相關(guān)影響問題的情況可以kill掉。也存在執(zhí)行時間短，但是CPU占用率高的SQL，通過上面命令查詢不到，這個時候最好通過執(zhí)行計劃分析explain進行分析

什么時候考慮使用分庫分表呢?

能不使用分庫分表就盡量不使用分庫分表，并不是所有的表都需要進行切分的，主要還是看數(shù)據(jù)的增長速度。切分后是對在某一些成都市提高了業(yè)務(wù)的復(fù)雜程度，數(shù)據(jù)庫除了承載數(shù)據(jù)的存儲和查詢以外，能更好的協(xié)助業(yè)務(wù)實現(xiàn)需求也是重要的工作。

不到萬不得已不要輕易使用分庫分表，避免“過度設(shè)計”和“過早優(yōu)化”。在進行分庫分表之前，不要是因為想分而分，更是要先去做力所能及的事情，比如說：給硬件升級、給網(wǎng)絡(luò)升級、讀寫分離等等。數(shù)據(jù)量達到單表的瓶頸時在考慮使用分庫分表。

數(shù)據(jù)量過大會影響業(yè)務(wù)的正常訪問，對數(shù)據(jù)庫的備份如果是單表很大的話，備份的時候就需要大量的磁盤IO和網(wǎng)絡(luò)IO。對一個很大的表進行DDL修改的時候，Mysql會鎖住全表，這個時間會挺長，這個時間段里業(yè)務(wù)是不能訪問這個表的，造成的影響會很大。因為數(shù)據(jù)量大的表會經(jīng)常訪問與更新，這種情況就會有可能出現(xiàn)鎖的等待。這時候?qū)?shù)據(jù)進行切分，用空間換時間，降低訪問壓力。

安全性和可用性，在業(yè)務(wù)的層面進行垂直切分的話，將不相關(guān)的數(shù)據(jù)庫來進行分隔，因為的話每個業(yè)務(wù)的數(shù)據(jù)量，訪問量都不一樣，不能因為一個業(yè)務(wù)就把數(shù)據(jù)庫搞掛掉牽連到其他的業(yè)務(wù)。利用水平切分的話，當(dāng)一個數(shù)據(jù)庫出現(xiàn)了問題，不會影響到全部的用戶，因為每個庫只承擔(dān)了業(yè)務(wù)的一小部分?jǐn)?shù)據(jù)，這樣的整體可用性就可以得到提高。

「Mysql數(shù)據(jù)庫垂直分表講解」

垂直分表也就是把“大表拆成小表”，基于列字段進行的。拆分的原則一般是表中的字段較多，將不常用的或者是數(shù)據(jù)較大，長度較長的拆分到擴展表里，如text類類型字段。把訪問頻次低、字段大的商品描述信息單獨的放在一張表里，訪問頻次較為多的商品基本信息單獨的放在一張表里。

垂直拆分的原則：把不常用的字段單獨的放在一張表里，把大字段拆分出來放在附表中，把業(yè)務(wù)經(jīng)常組合查詢的列放在一張表中。

為什么大字段的IO效率低下呢?

第一點就是由于數(shù)據(jù)量本身的龐大的，需要更長的讀取時間;第二點就是跨頁，也是數(shù)據(jù)庫的存儲單位，很多操作包括查找這些都是以頁為單位的，單頁里的數(shù)據(jù)行越多的話數(shù)據(jù)庫的整體性能就越好，并且的話字段大所占用的空間也大，單頁內(nèi)的存儲行數(shù)少，所以IO效率就較為低下;第三就是數(shù)據(jù)庫以行為單位把數(shù)據(jù)加載到內(nèi)存當(dāng)中，這樣的話表里的字段長度較為短并訪問的頻率高，內(nèi)存就能加載到了更多的數(shù)據(jù)，命中率就更高了，減少了磁盤的IO，提升了數(shù)據(jù)庫的性能。

//拆分前 
CREATE TABLE `product` ( 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
  `title` varchar(524) DEFAULT NULL COMMENT '視頻標(biāo)題', 
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面圖', 
  `price` int(11) DEFAULT NULL COMMENT '價格,分', 
  `total` int(10) DEFAULT '0' COMMENT '總庫存', 
  `left_num` int(10) DEFAULT '0' COMMENT '剩余', 
   
  `learn_base` text COMMENT '課前須知，學(xué)習(xí)基礎(chǔ)', 
  `learn_result` text COMMENT '達到水平', 
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',   
  `detail` text COMMENT '視頻商品詳情', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
 
 
//拆分后 
CREATE TABLE `product` ( 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
  `title` varchar(524) DEFAULT NULL COMMENT '視頻標(biāo)題', 
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面圖', 
  `price` int(11) DEFAULT NULL COMMENT '價格,分', 
  `total` int(10) DEFAULT '0' COMMENT '總庫存', 
  `left_num` int(10) DEFAULT '0' COMMENT '剩余', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
 
CREATE TABLE `product_detail` ( 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
  `product_id` int(11) DEFAULT NULL COMMENT '產(chǎn)品主鍵', 
  `learn_base` text COMMENT '課前須知，學(xué)習(xí)基礎(chǔ)', 
  `learn_result` text COMMENT '達到水平', 
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',   
  `detail` text COMMENT '視頻商品詳情', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

Mysql數(shù)據(jù)庫垂直分庫講解

垂直分庫是針對的是一個系統(tǒng)中不同業(yè)務(wù)進行拆分的，數(shù)據(jù)庫的連接資源比較寶貴并且單機的處理能力也是有限的。在沒拆分之前全部都是落到單一的庫上的，這時候單庫的處理能力有瓶頸，與之還有的是磁盤的空間、內(nèi)存、tps等限制。拆分之后，避免不同庫競爭同一個物理機上的CPU、內(nèi)存/網(wǎng)絡(luò)IO、磁盤，所以在高并發(fā)的場景下，垂直分庫一定程度上能夠突破IO、連接數(shù)及單機硬件資源的瓶頸。垂直分庫可以更好的解決業(yè)務(wù)層面的耦合，業(yè)務(wù)清晰并且方便管理和維護。一般從單體項目升級改造成為微服務(wù)項目的話，那就是垂直分庫。

Mysql數(shù)據(jù)庫水平分表

水平分表都是大表拆小表，垂直分表是按表結(jié)構(gòu)進行拆分，水平分表是按數(shù)據(jù)結(jié)構(gòu)進行拆分。把一個表的數(shù)據(jù)分到一個數(shù)據(jù)庫的多張表里，每個表只有這個表的部分?jǐn)?shù)據(jù)，其核心就是把一個大表分割成多個小表，每一個的結(jié)構(gòu)是一樣的，數(shù)據(jù)不一樣，全部表的數(shù)據(jù)合起來就是全部的數(shù)據(jù)，針對數(shù)據(jù)量巨大的單張表(比如訂單表)照某種規(guī)則(RANGE,HASH取模等)，切分到多張表里面去。但是這些表還是在同一個庫中，所以單數(shù)據(jù)庫操作還是有IO瓶頸，主要是解決單表數(shù)據(jù)量過大的問題。減少鎖表時間，沒分表前，如果是DDL(create/alter/add等)語句，當(dāng)需要添加一列的時候mysql會鎖表，期間所有的讀寫操作只能等待。

Mysql數(shù)據(jù)庫水平分庫講解

把同個表的數(shù)據(jù)按照一定的規(guī)則分到不同的數(shù)據(jù)庫里，數(shù)據(jù)庫在不同的服務(wù)器上，水平分庫就是把不同的表拆分到不同的數(shù)據(jù)庫里，它是對數(shù)據(jù)的行拆分，不會影響表的結(jié)構(gòu)。每個庫的結(jié)構(gòu)都一樣，但是每個庫的數(shù)據(jù)都不一樣，沒有交集，庫的并集就是全量數(shù)據(jù)了。但水平分庫的粒度會比水平分表更大。

分庫分表總結(jié)：

垂直角度(表結(jié)構(gòu)不一樣)

垂直分表: 將一個表字段拆分成多個表，每個表存儲部分字段。好處是避免IO時鎖表的次數(shù)，分離熱點字段和非熱點字段，避免大字段IO導(dǎo)致性能下降。原則是業(yè)務(wù)經(jīng)常組合查詢的字段一個表;不常用字段一個表;text、blob類型字段作為附屬表

垂直分庫：根據(jù)業(yè)務(wù)將表分類放到不同的數(shù)據(jù)庫服務(wù)器上，好處是避免表之間競爭同個物理機的資源，比如CPU/內(nèi)存/硬盤/網(wǎng)絡(luò)IO，原則是根據(jù)業(yè)務(wù)相關(guān)性進行劃分，領(lǐng)域模型，微服務(wù)劃分一般就是垂直分庫。

水平角度(表結(jié)構(gòu)一樣)

水平分庫：把同個表的數(shù)據(jù)按照一定規(guī)則分到不同的數(shù)據(jù)庫中，數(shù)據(jù)庫在不同的服務(wù)器上。好處:是多個數(shù)據(jù)庫，降低了系統(tǒng)的IO和CPU壓力。原則是選擇合適的分片鍵和分片策略，和業(yè)務(wù)場景配合;避免數(shù)據(jù)熱點和訪問不均衡、避免二次擴容難度大

水平分表：同個數(shù)據(jù)庫內(nèi)，把一個表的數(shù)據(jù)按照一定規(guī)則拆分到多個表中，對數(shù)據(jù)進行拆分，不影響表結(jié)構(gòu)。好處是單個表的數(shù)據(jù)量少了，業(yè)務(wù)SQL執(zhí)行效率高，降低了系統(tǒng)的IO和CPU壓力。原則是選擇合適的分片鍵和分片策略，和業(yè)務(wù)場景配合;避免數(shù)據(jù)熱點和訪問不均衡、避免二次擴容難度大