MySQL 表分區(qū)詳解MyiSam引擎和InnoDb 區(qū)別（實(shí)測(cè)）

一、什么是表分區(qū)
通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個(gè)小表。mysql5.1開(kāi)始支持?jǐn)?shù)據(jù)表分區(qū)了。
如：某用戶表的記錄超過(guò)了1000萬(wàn)條，那么就可以根據(jù)入庫(kù)日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當(dāng)然也可根據(jù)其他的條件分區(qū)。

二、為什么要對(duì)表進(jìn)行分區(qū)
為了改善大型表以及具有各種訪問(wèn)模式的表的可伸縮性，可管理性和提高數(shù)據(jù)庫(kù)效率。
分區(qū)的一些優(yōu)點(diǎn)包括：
????? 1）、與單個(gè)磁盤(pán)或文件系統(tǒng)分區(qū)相比，可以存儲(chǔ)更多的數(shù)據(jù)。
????? 2）、對(duì)于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過(guò)刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過(guò)程又可以通過(guò)為那些新數(shù)據(jù)專(zhuān)門(mén)增加一個(gè)新的分區(qū)，來(lái)很方便地實(shí)現(xiàn)。通常和分區(qū)有關(guān)的其他優(yōu)點(diǎn)包括下面列出的這些。MySQL分區(qū)中的這些功能目前還沒(méi)有實(shí)現(xiàn)，但是在我們的優(yōu)先級(jí)列表中，具有高的優(yōu)先級(jí)；我們希望在5.1的生產(chǎn)版本中，能包括這些功能。
????? 3）、一些查詢(xún)可以得到極大的優(yōu)化，這主要是借助于滿足一個(gè)給定WHERE語(yǔ)句的數(shù)據(jù)可以只保存在一個(gè)或多個(gè)分區(qū)內(nèi)，這樣在查找時(shí)就不用查找其他剩余的分區(qū)。因?yàn)榉謪^(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時(shí)還不曾這么做時(shí)，可以重新組織數(shù)據(jù)，來(lái)提高那些常用查詢(xún)的效率。
????? 4）、涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢(xún)，可以很容易地進(jìn)行并行處理。這種查詢(xún)的一個(gè)簡(jiǎn)單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過(guò)“并行”，這意味著該查詢(xún)可以在每個(gè)分區(qū)上同時(shí)進(jìn)行，最終結(jié)果只需通過(guò)總計(jì)所有分區(qū)得到的結(jié)果。
????? 5）、通過(guò)跨多個(gè)磁盤(pán)來(lái)分散數(shù)據(jù)查詢(xún)，來(lái)獲得更大的查詢(xún)吞吐量。

三、分區(qū)類(lèi)型
?
· RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。?
· LIST分區(qū)：類(lèi)似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇。?
· HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。
· KEY分區(qū)：類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

1.RANGE分區(qū)

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

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ù)列表。

3.HASH分區(qū)??????

?基于用戶定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。
????? 要使用HASH分區(qū)來(lái)分割一個(gè)表，要在CREATE TABLE 語(yǔ)句上添加一個(gè)“PARTITION BY HASH (expr)”子句，其中“expr”是一個(gè)返回一個(gè)整數(shù)的表達(dá)式。它可以?xún)H僅是字段類(lèi)型為MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一個(gè)“PARTITIONS num”子句，其中num 是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。

4.KSY分區(qū)

類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

不同的表分區(qū)對(duì)比如下：

?

下面來(lái)操作MySQL表分實(shí)例操作主要講解 Range分區(qū)技術(shù)

? 注意為了查看方便我這邊對(duì)mysql操作都是用客戶端工具Navicat For Mysql 進(jìn)行連接操作。

?1.創(chuàng)建分區(qū)表，按日期和年份拆分 使用的是?myisam 引擎

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

-- 創(chuàng)建表分區(qū)，按日期的年份拆分 create table part_tab(c1?int?default?null,c2 varchar(30)?default?null,c3 date?default?null) engine=myisam PARTITION?by?range(year(c3))( PARTITION p0 values less than(2007), PARTITION p1 values less than(2008), PARTITION p2 values less than(2009), PARTITION p3 values less than(2010), PARTITION p4 values less than(2011), PARTITION p5 values less than(2012), PARTITION p6 values less than(2013), PARTITION p7 values less than(2014), PARTITION p8 values less than(2015), PARTITION p9 values less than(2016), PARTITION p10 values less than MAXVALUE); -- 最后一行 ，考慮可能 最大值 默認(rèn)分區(qū)

2.創(chuàng)建未分區(qū)表，主要用于作對(duì)比表字段和結(jié)構(gòu)和分區(qū)表一模一樣。

1	create table no_part_tab(c1?int?default?null,c2 varchar(30)?default?null,c3 date?default?null) engine=myisam;

3.創(chuàng)建測(cè)試數(shù)據(jù)這里使用存儲(chǔ)過(guò)程快速創(chuàng)建100萬(wàn)行數(shù)據(jù)測(cè)試數(shù)據(jù)越多越方便查看，這里只測(cè)試100萬(wàn)行根據(jù)個(gè)人需求插入，因只是用日期分區(qū)，其他列不住細(xì)節(jié)深究。只關(guān)注日期列。

1 2 3 4 5 6 7 8 9 10

create PROCEDURE load_part_tab() ????begin ????declare i?int?default?0; ??while?i<1000000 ??do ???insert?into?part_tab values(i,'test data part', ???adddate('2007-01-01',(rand(i)*36520) mod 3652)); ??set?i=i+1; ??end?while; ??end;

4.執(zhí)行存儲(chǔ)過(guò)程插入數(shù)據(jù)

1	call load_part_tab();

5.進(jìn)入查看我們剛才的分區(qū)表，數(shù)據(jù)庫(kù)安裝都是常規(guī)的路徑，測(cè)試數(shù)據(jù)名稱(chēng)為test

1 2	[root@localhost /]# cd usr/local/mysql/data/test [root@localhost test]# ls

顯示如上信息說(shuō)明已經(jīng)創(chuàng)建完成。

?

6.復(fù)制數(shù)據(jù)到未分區(qū)表no_part_tab

1	insert?into?no_part_tab?select?*from?part_tab;

7.測(cè)試SQL 的性能分別查詢(xún)1年的數(shù)據(jù)。

?7.1）分區(qū)表的性能

?

7.2）未分區(qū)表的性能

?

?查詢(xún)時(shí)間非常明顯的區(qū)別，注意數(shù)據(jù)量越大越明顯。

8.為何會(huì)分區(qū)會(huì)有如此大的區(qū)別我們通過(guò)查詢(xún)計(jì)劃分析。

?8.1）分區(qū)的查詢(xún)計(jì)劃影響的行數(shù)是：99813

8.2）未分區(qū)查詢(xún)計(jì)劃影響的行數(shù)是：1000000

很明顯查詢(xún)條件的數(shù)據(jù)是在p1分區(qū) 所以查詢(xún)的只會(huì)查詢(xún)這分區(qū)，查詢(xún)效果就顯而易見(jiàn)。

9.增加未索引字段查詢(xún)

1	select?*?from?part_tab?where?c3>date'2007-01-01'?and c3<date'2017-12-31'?and c2='mysql';

?查看執(zhí)行時(shí)間2.55s　　

1	select?*?from?no_part_tab?where?c3>date'2007-01-01'?and c3<date'2017-12-31'?and c2='mysql';

?查看執(zhí)行時(shí)間11.16s　

對(duì)比時(shí)間差距還是很大的具體查詢(xún)時(shí)間根據(jù)個(gè)人計(jì)算機(jī)性能已經(jīng)數(shù)據(jù)大小進(jìn)行測(cè)試，數(shù)據(jù)量越大越明顯。

四.innodb表分區(qū)

在innodb數(shù)據(jù)庫(kù)引擎中要把分區(qū)技術(shù)做成功必須設(shè)置表為獨(dú)立表空間特別注意這點(diǎn)。?

1.什么是共享表空間和獨(dú)立表空間
貢獻(xiàn)表空間已經(jīng)獨(dú)立表空間都是針對(duì)數(shù)據(jù)的存儲(chǔ)方式而言的。
貢獻(xiàn)表空間：某一個(gè)數(shù)據(jù)庫(kù)的所有的表數(shù)據(jù)，索引文件全部放在一個(gè)文件中，默認(rèn)這個(gè)共享表空間的文件路徑在data目錄下。默認(rèn)的文件名為ibdata1初始大小為10M。
獨(dú)占表空間：每個(gè)表都將會(huì)生成意獨(dú)立文件方式來(lái)進(jìn)行存儲(chǔ)，每個(gè)表都有一個(gè).frm表描述，還有一個(gè).dbd文件.其中這個(gè)文件包括了單獨(dú)一個(gè)表數(shù)據(jù)內(nèi)容以及索引內(nèi)容，默認(rèn)情況下它的存儲(chǔ)位置也是在表的位置之中。

2.共享表空間：
優(yōu)點(diǎn)：
可以放表空間分成多文件存放各個(gè)磁盤(pán)上（表空間文件大小不受表大小的限制，如一個(gè)表可以分布在不同的文件上）。
數(shù)據(jù)和文件放一起方便管理。
缺點(diǎn)：
所有的數(shù)據(jù)和索引存放到一個(gè)文件中講有一個(gè)很大的文件，雖然可以把一個(gè)大文件分成多個(gè)小文件，但是多個(gè)表及索引在表空間中混合存儲(chǔ)，這樣對(duì)于一個(gè)表做了大量刪除操作后表空間中將會(huì)有大量的空隙，特別是對(duì)于統(tǒng)計(jì)分析，日志系統(tǒng)這類(lèi)應(yīng)用最不合適用共享表空間。

3.實(shí)際操作

開(kāi)啟innodb數(shù)據(jù)庫(kù)引擎獨(dú)立表空間。

編輯my.cnf 增加innodb_file_per_table=1

1	[root@localhost test]# vi /etc/my.cnf

　　

只有設(shè)置了innodb 引擎的獨(dú)立表空間才能做成功表分區(qū)，特別注意。【win下無(wú)需設(shè)置】

?

總結(jié)

以上是生活随笔為你收集整理的MySQL 表分区详解MyiSam引擎和InnoDb 区别（实测）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。