博主QQ：819594300

10年積累的成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先網(wǎng)站策劃后付款的網(wǎng)站建設(shè)流程，更有通道免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

博客地址：http://zpf666.blog.51cto.com/

有什么疑問的朋友可以聯(lián)系博主，博主會(huì)幫你們解答，謝謝支持！一、分表

為什么要分表？

我們的數(shù)據(jù)庫數(shù)據(jù)越來越大，隨之而來的是單個(gè)表中數(shù)據(jù)太多。以至于查詢書讀變慢，而且由于表的鎖機(jī)制導(dǎo)致應(yīng)用操作也搜到嚴(yán)重影響，出現(xiàn)了數(shù)據(jù)庫性能瓶頸。

什么是分表？

分表是將一個(gè)達(dá)標(biāo)按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，.MYD數(shù)據(jù)文件、.MYI索引文件、.frm表結(jié)構(gòu)文件。這些表可以分布在同一塊磁盤上，也可以在不同主機(jī)的不同的磁盤上。

App讀寫的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的表名，然后去操作它。

將單個(gè)數(shù)據(jù)庫表進(jìn)行拆分，拆分成多個(gè)數(shù)據(jù)表，然后用戶訪問的時(shí)候，根據(jù)一定的算法（如用hash的方式，也可以用求余（取模）的方式），讓用戶訪問不同的表，這樣數(shù)據(jù)分散到多個(gè)數(shù)據(jù)表中，減少了單個(gè)數(shù)據(jù)表的訪問壓力。提升了數(shù)據(jù)庫訪問性能。分表的目的就在于此，減小數(shù)據(jù)庫的負(fù)擔(dān)，縮短查詢時(shí)間。

MySQL分表分為垂直切分和水平切分

垂直切分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。我們常常把常用的幾個(gè)列單獨(dú)放在一個(gè)表，不常用的單獨(dú)放在另外一個(gè)表。

水平拆分是指數(shù)據(jù)表行的拆分，把一張表的數(shù)據(jù)拆分成多張表來存放。通常情況下，我們使用hash算法和取模等方式來進(jìn)行表的拆分。比如：比如一張有400W的用戶表users，為提高其查詢效率我們把其分成4張表users1，users2，users3，users4，通過用ID取模的方法把數(shù)據(jù)分散到四張表內(nèi)Id%4= [0,1,2,3]，然后查詢,更新,刪除也是通過取模的方法來查詢。

分表的幾種方式？

1）mysql集群

它并不是分表，但是起到了和分表相同的作用。集群可分擔(dān)數(shù)據(jù)庫的操作次數(shù)，將任務(wù)分擔(dān)到多臺(tái)數(shù)據(jù)庫上。集群可以讀寫分離，減少讀寫壓力，從而提升數(shù)據(jù)庫性能。

2）預(yù)先估計(jì)會(huì)出現(xiàn)大量數(shù)據(jù)并且訪問頻繁的表，將其分為若干個(gè)表。

根據(jù)一定的算法（如用hash的方式，也可以用求余（取模）的方式）讓用戶訪問不同的表。

例如論壇里面發(fā)表帖子的表，時(shí)間長了這張表肯定很大，幾十萬，幾百萬都有可能。聊天室里面信息表，幾十個(gè)人在一起一聊一個(gè)晚上，時(shí)間長了，這張表的數(shù)據(jù)肯定很大。像這樣的情況很多。所以這種能預(yù)估出來的大數(shù)據(jù)量表，我們就事先分出個(gè)N個(gè)表，這個(gè)N是多少，根據(jù)實(shí)際情況而定。以聊天信息表為例：我們事先建100個(gè)這樣的表，message_00,message_01,message_02..........message_98,message_99.然后根據(jù)用戶的ID來判斷這個(gè)用戶的聊天信息放到哪張表里面，可以用hash的方式來獲得，也可以用求余的方式來獲得，方法很多?；蛘呖梢栽O(shè)計(jì)每張表容納的數(shù)據(jù)量是N條，那么如何判斷某張表的數(shù)據(jù)是否容量已滿呢？可以在程序段對(duì)于要新增數(shù)據(jù)的表，在插入前先做統(tǒng)計(jì)表記錄數(shù)量的操作，當(dāng)

3）利用merge存儲(chǔ)引擎來實(shí)現(xiàn)分表

如果要把已有的大數(shù)據(jù)量表分開是比較痛苦的，最痛苦的事就是改代碼，因?yàn)槌绦蚶锩娴膕ql語句已經(jīng)寫好了，用merge存儲(chǔ)引擎來實(shí)現(xiàn)分表，這種方法比較適合。

注意：merge存儲(chǔ)引擎來實(shí)現(xiàn)分表有局限性，只針對(duì)myisam存儲(chǔ)引擎表。

Merge分表，分為一個(gè)主表和若干個(gè)子表，主表就是一個(gè)殼子，在邏輯上是包含子表的，但是主表不存放任何的數(shù)據(jù)，真正的數(shù)據(jù)存放在子表中。

我們可以通過主表插入和查詢數(shù)據(jù)，如果清楚分表規(guī)律，也可以直接操作子表。

下面我們來實(shí)現(xiàn)一個(gè)簡單的利用merge存儲(chǔ)引擎來實(shí)現(xiàn)分表的演示

1）創(chuàng)建一個(gè)完整表存儲(chǔ)著所有的成員信息

2）加入實(shí)驗(yàn)數(shù)據(jù)（我們加入32行實(shí)驗(yàn)數(shù)據(jù)）

3）下面我們進(jìn)行分表，這里我們把member分成兩個(gè)子表tb_member1,tb_member2

4）創(chuàng)建主表tb_member

注：INSERT_METHOD,此參數(shù)

INSERT_METHOD= NO表示該表不能做任何寫入操作只作為查詢使用，即只讀狀態(tài)；

INSERT_METHOD= LAST表示插入到最后的一張表里面；

INSERT_METHOD= first表示插入到第一張表里面。

Insert_method是指以后我們插入新的數(shù)據(jù)，則要遵守該配置項(xiàng)后面的參數(shù)執(zhí)行。這也正是merge分表的缺陷，對(duì)于新插入的數(shù)據(jù)有局限性，而innodb分表則沒有這個(gè)局限性。

5）接下來，我們把原表中的數(shù)據(jù)分到兩個(gè)子表中去

6）查看兩個(gè)子表的數(shù)據(jù)

7）查看主表的數(shù)據(jù)

總結(jié)：主表只是一個(gè)外殼，存取數(shù)據(jù)發(fā)生在一個(gè)一個(gè)的子表里面，每個(gè)子表都有自已獨(dú)立的相關(guān)表文件，而主表只是一個(gè)殼，并沒有完整的相關(guān)表文件。我們看到的主表的數(shù)據(jù)，都是給用戶的一個(gè)假象，這也說明，分表對(duì)于客戶來說是透明的。主表和子表的關(guān)系就很類似與我們學(xué)過的lvs的調(diào)度器和所有的節(jié)點(diǎn)服務(wù)器。

8）刪除原表，修改主表表名（因?yàn)榇藭r(shí)原表已經(jīng)沒有用處了，把主表改成原表表名，這樣做的好處是不用修改APP了，這是merge的一大優(yōu)勢(shì)）

說明：主表也包含有.frm文件，但是他有個(gè)特殊的文件是.mrg文件，這個(gè)文件不存放任何表的真實(shí)數(shù)據(jù)，而是存放的是所有額子表表名和插入方式。

本實(shí)驗(yàn)總結(jié)：merge分表的順序是：

①先創(chuàng)建所需的所有子表→②再創(chuàng)建主表→③把原表的數(shù)據(jù)導(dǎo)入到新建的不同子表中→④刪除原表，把主表改名字為原表表名（這樣做的好處不用修改APP代碼，這是merge的優(yōu)點(diǎn)）

其實(shí)innodb表也可以分表，步驟如下：

①先創(chuàng)建所需的所有子表→②把原表的數(shù)據(jù)導(dǎo)入到新建的不同子表中→③修改APP代碼，把客戶的請(qǐng)求對(duì)應(yīng)到各個(gè)子表。

兩種不同點(diǎn)是，innodb不需要?jiǎng)?chuàng)建主表，但是需要修改APP代碼，比較麻煩，而merge不需要修改app，但是merge需要建主表，而innodb不需要。

二、分區(qū)

什么是分區(qū)？

分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放，分區(qū)后，表還是一張表，但數(shù)據(jù)散列到多個(gè)位置了。app讀寫的時(shí)候操作的還是表名字，db自動(dòng)去組織分區(qū)的數(shù)據(jù)。

分區(qū)主要有兩種形式：

水平分區(qū)（Horizontal Partitioning） 這種形式分區(qū)是對(duì)表的行進(jìn)行分區(qū)，所有在表中定義的列在每個(gè)數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。

舉個(gè)簡單例子：一個(gè)包含十年發(fā)票記錄的表可以被分區(qū)為十個(gè)不同的分區(qū)，每個(gè)分區(qū)包含的是其中一年的記錄。

垂直分區(qū)（Vertical Partitioning） 這種分區(qū)方式一般來說是通過對(duì)表的垂直劃分來減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。

舉個(gè)簡單例子：一個(gè)包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問，這時(shí)候就要把這些不經(jīng)常使用的text和BLOB了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性的同時(shí)還能提高訪問速度。

分區(qū)技術(shù)支持

①在5.6之前，使用這個(gè)參數(shù)查看當(dāng)將配置是否支持分區(qū)

mysql>show variables like '%partition%';

+-----------------------+---------------+

|Variable_name          | Value |

+-----------------------+---------------+

|have_partition_engine | YES   |

+-----------------------+------------------+

說明：如果是yes表示你當(dāng)前的配置支持分區(qū)

②在5.6及以采用后，則采用如下方式進(jìn)行查看

說明：在顯示結(jié)果中，可以看到partition是ACTIVE的，表示支持分區(qū)。

下面我們先演示一個(gè)按照范圍(range)方式的表分區(qū)

1)創(chuàng)建range分區(qū)表

2)插入些數(shù)據(jù)（說明一下：處于分解點(diǎn)上額數(shù)據(jù)會(huì)被分配到下一個(gè)分區(qū)中，比如數(shù)據(jù)3不會(huì)放p0表，而是放p1分區(qū)）

3)到存放數(shù)據(jù)庫表文件的地方看一下

4)從information_schema系統(tǒng)庫中的partitions表中查看分區(qū)信息

5)從某個(gè)分區(qū)中查詢數(shù)據(jù)

6)新增分區(qū)

mysql>alter table庫名.表名 add partition (partition 新增的分區(qū)名 values less than (n));

注意：n為條件是為具體的數(shù)字或者是maxvalues

7）刪除分區(qū)

注意：當(dāng)刪除了一個(gè)分區(qū)，也同時(shí)刪除了該分區(qū)中所有的數(shù)據(jù)。

8)分區(qū)的合并

未分區(qū)表和分區(qū)表性能測(cè)試

1)創(chuàng)建一個(gè)未分區(qū)的表

2)創(chuàng)建分區(qū)表,按日期的年份拆分

3)通過存儲(chǔ)過程插入100萬條測(cè)試數(shù)據(jù)

創(chuàng)建存儲(chǔ)過程：

注意：RAND()函數(shù)在0和1之間產(chǎn)生一個(gè)隨機(jī)數(shù)，如果一個(gè)整數(shù)參數(shù)N被指定，它被用作種子值。每個(gè)種子產(chǎn)生的隨機(jī)數(shù)序列是不同的。

執(zhí)行存儲(chǔ)過程load_part_tab向bdqn.tab1表插入數(shù)據(jù):

退出去mysql的交互式模式，然后在進(jìn)入交互式模式，執(zhí)行下面的命令

4）向test2.tab2表中插入數(shù)據(jù)

5)測(cè)試SQL性能

總結(jié)：結(jié)果表明分區(qū)表比未分區(qū)表的執(zhí)行時(shí)間少很多。

6）通過explain語句來分析執(zhí)行情況

總結(jié)：explain語句顯示了SQL查詢要處理的記錄數(shù)目可以看出分區(qū)表比未分區(qū)表的明顯掃描的記錄要少很多。

7)創(chuàng)建索引后情況測(cè)試

總結(jié)：創(chuàng)建索引后分區(qū)表比未分區(qū)表相差不大（但是數(shù)據(jù)量越大差別會(huì)明顯些）

Mysql分區(qū)的類型

1、range分區(qū)（范圍分區(qū)）

作用：把一個(gè)連續(xù)的列值中的多行分配給分區(qū)，列區(qū)間連續(xù)并且不重疊。

例子：

總結(jié)：p0到p3分區(qū)都是按順序進(jìn)行定義，從最低到最高，不允許從最高到最低，處于分界點(diǎn)的值自動(dòng)放入下一個(gè)分區(qū)，比如store_id是16的自動(dòng)放入p3分區(qū)，但是如果插入store_id是21的數(shù)據(jù)，就會(huì)報(bào)錯(cuò)，因?yàn)闆]有包含21的分區(qū)，為了避免這種錯(cuò)誤，我們一般都把range分區(qū)的最后一個(gè)分區(qū)設(shè)置為maxvalue分區(qū)，把前面所有分區(qū)都不包括的該值分到maxvalue分區(qū)，避免報(bào)錯(cuò)。

如果創(chuàng)表的時(shí)候沒有創(chuàng)建maxvalue分區(qū)，則用如下命令添加即可：

2、list分區(qū)（列表分區(qū)）

作用：基于列值匹配一個(gè)離散值集合中某個(gè)值來進(jìn)行選擇

例子：

總結(jié)：list分區(qū)依據(jù)地區(qū)把數(shù)據(jù)很容易的按地區(qū)劃分開，比如公司打算把西部的店面全部出售，則就只需要把pwest刪除即可，非常方便。注意的地方是如果視圖插入的store_id列值不屬于任何一個(gè)分區(qū)，mysql會(huì)報(bào)錯(cuò)，插入失敗。list分區(qū)沒有range分區(qū)類型的maxvalue(最大值)分區(qū)，要匹配的列值必須是創(chuàng)建表時(shí)幾個(gè)分區(qū)已經(jīng)有的值。

使用下面的語句刪除pwest分區(qū)，它與具有同樣作用的DELETE （刪除）查詢

“deletefrom employees2 where store_id in (7,8,15,16)；”比起來，要有效得多。

用另一種刪除方法刪除pnorch分區(qū)

3、hash分區(qū)

作用：對(duì)表的一個(gè)或多個(gè)列的hash key進(jìn)行計(jì)算，最后通過這個(gè)hash 碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。

例子：

總結(jié)：hash分區(qū)不需要指定分區(qū)的集合，mysql會(huì)自動(dòng)完成分區(qū)工作，用戶只需要定一個(gè)列值或表達(dá)式，以及分區(qū)的數(shù)量，默認(rèn)分區(qū)的數(shù)量是1（即不劃分分區(qū)）。++

上圖中的那個(gè)例子說明：b列的數(shù)值取年份÷分區(qū)數(shù)量，然后去余數(shù)。比如分區(qū)的數(shù)量是4，則余數(shù)則只能有0、1、2、3，自動(dòng)把余數(shù)為0的放p0分區(qū)，余數(shù)為1的放p1分區(qū)等等。其中mysql會(huì)自動(dòng)創(chuàng)建p0、p1、p2、p3分區(qū)，名字就叫p0、p1、p2、p3。

查一個(gè)表中某個(gè)指定分區(qū)中的所有數(shù)據(jù)：

從information_schema庫中的partitions表里面查詢bdqn.employess3表的每個(gè)分區(qū)的具體情況：

4、key分區(qū)

作用：與hash分區(qū)很相似，不同的是hash分區(qū)是用戶自定義函數(shù)進(jìn)行分區(qū)。而key使用mysql數(shù)據(jù)庫提供的函數(shù)進(jìn)行分區(qū)。NDB cluster使用md5函數(shù)來分區(qū)，對(duì)于其他存儲(chǔ)引擎mysql使用內(nèi)部的hash函數(shù)。

例子：

總結(jié)：range、list、hash、key四種分區(qū)中，分區(qū)的條件必須是整數(shù)，如果不是整數(shù)需要通過函數(shù)將其轉(zhuǎn)換成整數(shù)。

5、columns分區(qū)

說明：mysql5.5版本開始支持columns分區(qū)，可視為range和list分區(qū)的進(jìn)化，columns分區(qū)可以直接使用非整數(shù)數(shù)據(jù)進(jìn)行分區(qū)。

Columns分區(qū)支持以下數(shù)據(jù)類型：

• 所有×××，如INT SMALLINTTINYINT BIGINT。FLOAT和DECIMAL則不支持。

• 日期類型，如DATE和DATETIME。其余日期類型不支持。

• 字符串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支持。

• COLUMNS可以使用多個(gè)列進(jìn)行分區(qū)。

分區(qū)時(shí)，將不同分區(qū)放到不同存儲(chǔ)位置：

①建表前，提前創(chuàng)建好存儲(chǔ)目錄，并授權(quán)給mysql:

②創(chuàng)建表

注：使用mysql默認(rèn)的存儲(chǔ)引擎inodb時(shí)候，只需要指定data directory 就可以，因?yàn)閕nodb的數(shù)據(jù)和索引在一個(gè)文件中。但是創(chuàng)建表格時(shí)指定engine=myisam時(shí)，修改分區(qū)的存儲(chǔ)位置，需要同時(shí)指定datadirectory 和 index directory。

總結(jié)：把一個(gè)表的所有數(shù)據(jù)通過分區(qū)劃分到不同目錄（目錄在不同磁盤上），可以提高I/O性能，提高磁讀寫能力，讓幾個(gè)磁盤都能同時(shí)工作，提高mysql的性能。