MySql之分區(qū)分表

MySql之分區(qū)分表

分表的概念

分表：將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表，每個(gè)表都對(duì)應(yīng)三個(gè)文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些表可以分布在同一塊磁盤(pán)上，也可以在不同的機(jī)器上。app讀寫(xiě)的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的表名，然后去操作它。

常用的算法：hash或求余（取模）等方式

分表的好處：減小數(shù)據(jù)庫(kù)的負(fù)擔(dān)，縮短查詢(xún)時(shí)間

分表的類(lèi)型：①垂直切分：是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表

            ②水平拆分是指數(shù)據(jù)表行的拆分，把一張的表的數(shù)據(jù)拆成多張表來(lái)存放

分表的方式：①mysql集群

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

            ③利用merge存儲(chǔ)引擎來(lái)實(shí)現(xiàn)分表

分表的案例：

1.創(chuàng)建庫(kù)、表、數(shù)據(jù)

使用：select  * from member；  查詢(xún)表

2.將member分為兩個(gè)表tb_member1,tb_member2。

tb_member1

tb_member2

創(chuàng)建主表

使用降序查看b_member表的結(jié)構(gòu)

3.將數(shù)據(jù)分到兩個(gè)表中

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

查看主表：mysql> select * from tb_member;  此時(shí) 總表只是一個(gè)外殼，存取數(shù)

據(jù)發(fā)生在一個(gè)一個(gè)的子表里面。

mysql> select * from tb_member where id=3; 查詢(xún)基于id的條件語(yǔ)句更加快捷

4.查看文件表：每個(gè)子表都有自已獨(dú)立的相關(guān)表文件，而主表只是一個(gè)殼，并沒(méi)有完整的相關(guān)表文件

分區(qū)的概念

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

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

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

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

                mysql> SHOW VARIABLES LIKE '%partition%'; 

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

                mysql> show plugins;

分區(qū)案例

1.創(chuàng)建庫(kù)、表、范圍分區(qū)表

插入15條數(shù)據(jù)

查看存放數(shù)據(jù)庫(kù)表文件

計(jì)算基于id值的有多少行

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

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

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

新增分區(qū)

分區(qū)合并

再次查看存放數(shù)據(jù)庫(kù)表文件

查看合并后的分區(qū)

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

1.創(chuàng)建數(shù)據(jù)庫(kù)和一個(gè)未分區(qū)的表

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

3.插入10萬(wàn)條測(cè)試語(yǔ)句

4.在表中插入數(shù)據(jù)

查詢(xún)表明分區(qū)表比未分區(qū)表的執(zhí)行時(shí)間少很多。

在通過(guò)explain語(yǔ)句來(lái)分析執(zhí)行情況

explain語(yǔ)句顯示了SQL查詢(xún)要處理的記錄數(shù)目可以看出分區(qū)表比未分區(qū)表的明顯掃描的記錄要少很多。

創(chuàng)建索引后與不不創(chuàng)建索引差異不大 1不創(chuàng)建索引重啟mysql服務(wù)在次查詢(xún)即可

分區(qū)表的類(lèi)型

1.RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來(lái)進(jìn)行定義。

案例：

創(chuàng)建maxvalue分區(qū)，所有不在指定范圍內(nèi)的記錄都會(huì)被存儲(chǔ)到maxvalue所在的分區(qū)中。

2.LIST分區(qū)：類(lèi)似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。

LIST分區(qū)通過(guò)使用“PARTITION BY LIST(expr)”來(lái)實(shí)現(xiàn)，其中“expr” 是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式，然后通過(guò)“VALUES IN (value_list)”的方式來(lái)定義每個(gè)分區(qū)，其中“value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(shù)列表。

案例：

查詢(xún)語(yǔ)句

3.HASH分區(qū) ：這種模式允許DBA通過(guò)對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過(guò)這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。

hash分區(qū)的目的是將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致一致。在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中；而在HASH分區(qū)中，MYSQL自動(dòng)完成這些工作，用戶(hù)所要定一個(gè)列值或者表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。

hash的分區(qū)函數(shù)頁(yè)需要返回一個(gè)整數(shù)值。partitions子句中的值是一個(gè)非負(fù)整數(shù)，不加的partitions子句的話(huà)，默認(rèn)為分區(qū)數(shù)為1。

該記錄會(huì)被放入分區(qū)p2中。因?yàn)椴迦?/span>2010-04-01進(jìn)入表t_hash,那么

MOD(YEAR('2010-04-01'),4)=2

4.key分區(qū)：key分區(qū)和hash分區(qū)相似，不同在于hash分區(qū)是用戶(hù)自定義函數(shù)進(jìn)行分區(qū)，key分區(qū)使用mysql數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)，NDB cluster使用MD5函數(shù)來(lái)分區(qū)，對(duì)于其他存儲(chǔ)引擎mysql使用內(nèi)部的hash函數(shù)。

上面的RANGE、LIST、HASH、KEY四種分區(qū)中，分區(qū)的條件必須是×××，如果不是×××需要通過(guò)函數(shù)將其轉(zhuǎn)換為×××。

5.columns分區(qū)：mysql-5.5開(kāi)始支持COLUMNS分區(qū)，可視為RANGE和LIST分區(qū)的進(jìn)化，COLUMNS分區(qū)可以直接使用非×××數(shù)據(jù)進(jìn)行分區(qū)。COLUMNS分區(qū)支持以下數(shù)據(jù)類(lèi)型：
　　所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL則不支持。
　　日期類(lèi)型，如DATE和DATETIME。其余日期類(lèi)型不支持。
　　字符串類(lèi)型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類(lèi)型不支持。
　　COLUMNS可以使用多個(gè)列進(jìn)行分區(qū)。