分庫分表的垂直切分與水平切分看這篇就夠了！
哈嘍大家好呀!我是小三。今天來講分庫分表：)
什么是分庫分表
分庫分表：在一些數(shù)據(jù)庫大的項(xiàng)目中，隨著時(shí)間的推移和業(yè)務(wù)量的增加，數(shù)據(jù)庫里的表中數(shù)據(jù)就會(huì)越來越多，如果單單還使用上面的模式，顯然是不夠用的。這時(shí)候就想到了把一個(gè)庫里的數(shù)據(jù)分散到多個(gè)庫里，并且把存在一個(gè)表里的數(shù)據(jù)分散到多個(gè)表里。
「分庫分表的方式有垂直切分，水平切分」
分庫分表能帶來什么樣的好處
第一、能解決數(shù)據(jù)庫本身的瓶頸，當(dāng)連接數(shù)過多時(shí)，就會(huì)出現(xiàn)‘too many connections’的錯(cuò)誤，這種訪問量 太大或者是數(shù)據(jù)庫設(shè)置的最大連接數(shù)太小的原因。mysql的默認(rèn)的最大連接數(shù)是100，可以進(jìn)行修改，而mysql服務(wù)允許最大的連接數(shù)為16384。數(shù)據(jù)庫分表可以解決單表海量數(shù)據(jù)的查詢性能問題，數(shù)據(jù)庫分庫可以解決單臺(tái)數(shù)據(jù)庫的并發(fā)訪問壓力問題。
第二、解決系統(tǒng)本身的IO、CPU瓶頸。磁盤讀寫IO瓶頸：熱點(diǎn)數(shù)據(jù)量很多的情況下，盡量使用了數(shù)據(jù)庫本身的緩存，但是依舊有大量IO，導(dǎo)致sql執(zhí)行速度慢。網(wǎng)絡(luò)IO瓶頸：請(qǐng)求的數(shù)據(jù)太多，數(shù)據(jù)傳輸量大了，網(wǎng)絡(luò)帶寬就顯得不夠用了，鏈路的響應(yīng)時(shí)間就變長(zhǎng)了。CPU瓶頸，在進(jìn)行基礎(chǔ)的數(shù)據(jù)量大單機(jī)復(fù)制SQL計(jì)算的時(shí)候，SQL語句執(zhí)行占用CPU的使用率就會(huì)變高，不單只這種原因，也有掃描行數(shù)大、鎖沖突、鎖等待等等原因。
可以通過show processlist; 、show full processlist，發(fā)現(xiàn) CPU 使用率比較高的SQL。常見的對(duì)于查詢時(shí)間長(zhǎng)，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能問題SQL，清楚相關(guān)影響問題的情況可以kill掉。也存在執(zhí)行時(shí)間短，但是CPU占用率高的SQL，通過上面命令查詢不到，這個(gè)時(shí)候最好通過執(zhí)行計(jì)劃分析explain進(jìn)行分析
什么時(shí)候考慮使用分庫分表呢?
能不使用分庫分表就盡量不使用分庫分表，并不是所有的表都需要進(jìn)行切分的，主要還是看數(shù)據(jù)的增長(zhǎng)速度。切分后是對(duì)在某一些成都市提高了業(yè)務(wù)的復(fù)雜程度，數(shù)據(jù)庫除了承載數(shù)據(jù)的存儲(chǔ)和查詢以外，能更好的協(xié)助業(yè)務(wù)實(shí)現(xiàn)需求也是重要的工作。
不到萬不得已不要輕易使用分庫分表，避免“過度設(shè)計(jì)”和“過早優(yōu)化”。在進(jìn)行分庫分表之前，不要是因?yàn)橄敕侄郑且热プ隽λ芗暗氖虑椋热缯f：給硬件升級(jí)、給網(wǎng)絡(luò)升級(jí)、讀寫分離等等。數(shù)據(jù)量達(dá)到單表的瓶頸時(shí)在考慮使用分庫分表。
數(shù)據(jù)量過大會(huì)影響業(yè)務(wù)的正常訪問，對(duì)數(shù)據(jù)庫的備份如果是單表很大的話，備份的時(shí)候就需要大量的磁盤IO和網(wǎng)絡(luò)IO。對(duì)一個(gè)很大的表進(jìn)行DDL修改的時(shí)候，Mysql會(huì)鎖住全表，這個(gè)時(shí)間會(huì)挺長(zhǎng)，這個(gè)時(shí)間段里業(yè)務(wù)是不能訪問這個(gè)表的，造成的影響會(huì)很大。因?yàn)閿?shù)據(jù)量大的表會(huì)經(jīng)常訪問與更新，這種情況就會(huì)有可能出現(xiàn)鎖的等待。這時(shí)候?qū)?shù)據(jù)進(jìn)行切分，用空間換時(shí)間，降低訪問壓力。
安全性和可用性，在業(yè)務(wù)的層面進(jìn)行垂直切分的話，將不相關(guān)的數(shù)據(jù)庫來進(jìn)行分隔，因?yàn)榈脑捗總€(gè)業(yè)務(wù)的數(shù)據(jù)量，訪問量都不一樣，不能因?yàn)橐粋€(gè)業(yè)務(wù)就把數(shù)據(jù)庫搞掛掉牽連到其他的業(yè)務(wù)。利用水平切分的話，當(dāng)一個(gè)數(shù)據(jù)庫出現(xiàn)了問題，不會(huì)影響到全部的用戶，因?yàn)槊總€(gè)庫只承擔(dān)了業(yè)務(wù)的一小部分?jǐn)?shù)據(jù)，這樣的整體可用性就可以得到提高。
「Mysql數(shù)據(jù)庫垂直分表講解」
垂直分表也就是把“大表拆成小表”，基于列字段進(jìn)行的。拆分的原則一般是表中的字段較多，將不常用的或者是數(shù)據(jù)較大，長(zhǎng)度較長(zhǎng)的拆分到擴(kuò)展表里，如text類類型字段。把訪問頻次低、字段大的商品描述信息單獨(dú)的放在一張表里，訪問頻次較為多的商品基本信息單獨(dú)的放在一張表里。
垂直拆分的原則：把不常用的字段單獨(dú)的放在一張表里，把大字段拆分出來放在附表中，把業(yè)務(wù)經(jīng)常組合查詢的列放在一張表中。
為什么大字段的IO效率低下呢?
第一點(diǎn)就是由于數(shù)據(jù)量本身的龐大的，需要更長(zhǎng)的讀取時(shí)間;第二點(diǎn)就是跨頁，也是數(shù)據(jù)庫的存儲(chǔ)單位，很多操作包括查找這些都是以頁為單位的，單頁里的數(shù)據(jù)行越多的話數(shù)據(jù)庫的整體性能就越好，并且的話字段大所占用的空間也大，單頁內(nèi)的存儲(chǔ)行數(shù)少，所以IO效率就較為低下;第三就是數(shù)據(jù)庫以行為單位把數(shù)據(jù)加載到內(nèi)存當(dāng)中，這樣的話表里的字段長(zhǎ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 '價(jià)格,分',
- `total` int(10) DEFAULT '0' COMMENT '總庫存',
- `left_num` int(10) DEFAULT '0' COMMENT '剩余',
- `learn_base` text COMMENT '課前須知，學(xué)習(xí)基礎(chǔ)',
- `learn_result` text COMMENT '達(dá)到水平',
- `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 '價(jià)格,分',
- `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 '達(dá)到水平',
- `summary` varchar(1026) DEFAULT NULL COMMENT '概述',
- `detail` text COMMENT '視頻商品詳情',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Mysql數(shù)據(jù)庫垂直分庫講解
垂直分庫是針對(duì)的是一個(gè)系統(tǒng)中不同業(yè)務(wù)進(jìn)行拆分的，數(shù)據(jù)庫的連接資源比較寶貴并且單機(jī)的處理能力也是有限的。在沒拆分之前全部都是落到單一的庫上的，這時(shí)候單庫的處理能力有瓶頸，與之還有的是磁盤的空間、內(nèi)存、tps等限制。拆分之后，避免不同庫競(jìng)爭(zhēng)同一個(gè)物理機(jī)上的CPU、內(nèi)存/網(wǎng)絡(luò)IO、磁盤，所以在高并發(fā)的場(chǎng)景下，垂直分庫一定程度上能夠突破IO、連接數(shù)及單機(jī)硬件資源的瓶頸。垂直分庫可以更好的解決業(yè)務(wù)層面的耦合，業(yè)務(wù)清晰并且方便管理和維護(hù)。一般從單體項(xiàng)目升級(jí)改造成為微服務(wù)項(xiàng)目的話，那就是垂直分庫。
Mysql數(shù)據(jù)庫水平分表
水平分表都是大表拆小表，垂直分表是按表結(jié)構(gòu)進(jìn)行拆分，水平分表是按數(shù)據(jù)結(jié)構(gòu)進(jìn)行拆分。把一個(gè)表的數(shù)據(jù)分到一個(gè)數(shù)據(jù)庫的多張表里，每個(gè)表只有這個(gè)表的部分?jǐn)?shù)據(jù)，其核心就是把一個(gè)大表分割成多個(gè)小表，每一個(gè)的結(jié)構(gòu)是一樣的，數(shù)據(jù)不一樣，全部表的數(shù)據(jù)合起來就是全部的數(shù)據(jù)，針對(duì)數(shù)據(jù)量巨大的單張表(比如訂單表)照某種規(guī)則(RANGE,HASH取模等)，切分到多張表里面去。但是這些表還是在同一個(gè)庫中，所以單數(shù)據(jù)庫操作還是有IO瓶頸，主要是解決單表數(shù)據(jù)量過大的問題。減少鎖表時(shí)間，沒分表前，如果是DDL(create/alter/add等)語句，當(dāng)需要添加一列的時(shí)候mysql會(huì)鎖表，期間所有的讀寫操作只能等待。
Mysql數(shù)據(jù)庫水平分庫講解
把同個(gè)表的數(shù)據(jù)按照一定的規(guī)則分到不同的數(shù)據(jù)庫里，數(shù)據(jù)庫在不同的服務(wù)器上，水平分庫就是把不同的表拆分到不同的數(shù)據(jù)庫里，它是對(duì)數(shù)據(jù)的行拆分，不會(huì)影響表的結(jié)構(gòu)。每個(gè)庫的結(jié)構(gòu)都一樣，但是每個(gè)庫的數(shù)據(jù)都不一樣，沒有交集，庫的并集就是全量數(shù)據(jù)了。但水平分庫的粒度會(huì)比水平分表更大。
分庫分表總結(jié)：
垂直角度(表結(jié)構(gòu)不一樣)
垂直分表: 將一個(gè)表字段拆分成多個(gè)表，每個(gè)表存儲(chǔ)部分字段。好處是避免IO時(shí)鎖表的次數(shù)，分離熱點(diǎn)字段和非熱點(diǎn)字段，避免大字段IO導(dǎo)致性能下降。原則是業(yè)務(wù)經(jīng)常組合查詢的字段一個(gè)表;不常用字段一個(gè)表;text、blob類型字段作為附屬表
垂直分庫：根據(jù)業(yè)務(wù)將表分類放到不同的數(shù)據(jù)庫服務(wù)器上，好處是避免表之間競(jìng)爭(zhēng)同個(gè)物理機(jī)的資源，比如CPU/內(nèi)存/硬盤/網(wǎng)絡(luò)IO，原則是根據(jù)業(yè)務(wù)相關(guān)性進(jìn)行劃分，領(lǐng)域模型，微服務(wù)劃分一般就是垂直分庫。
水平角度(表結(jié)構(gòu)一樣)
水平分庫：把同個(gè)表的數(shù)據(jù)按照一定規(guī)則分到不同的數(shù)據(jù)庫中，數(shù)據(jù)庫在不同的服務(wù)器上。好處:是多個(gè)數(shù)據(jù)庫，降低了系統(tǒng)的IO和CPU壓力。原則是選擇合適的分片鍵和分片策略，和業(yè)務(wù)場(chǎng)景配合;避免數(shù)據(jù)熱點(diǎn)和訪問不均衡、避免二次擴(kuò)容難度大
水平分表：同個(gè)數(shù)據(jù)庫內(nèi)，把一個(gè)表的數(shù)據(jù)按照一定規(guī)則拆分到多個(gè)表中，對(duì)數(shù)據(jù)進(jìn)行拆分，不影響表結(jié)構(gòu)。好處是單個(gè)表的數(shù)據(jù)量少了，業(yè)務(wù)SQL執(zhí)行效率高，降低了系統(tǒng)的IO和CPU壓力。原則是選擇合適的分片鍵和分片策略，和業(yè)務(wù)場(chǎng)景配合;避免數(shù)據(jù)熱點(diǎn)和訪問不均衡、避免二次擴(kuò)容難度大
