MySQL 表分區(qū)詳解MyiSam引擎和InnoDb 區(qū)別(實測)

/ --------------------? ? ? ? ?寫在最前面? ? ? ? ? ? --------------------------------/

如果表分區(qū)使用的是：innodb數(shù)據(jù)庫引擎，要把分區(qū)技術(shù)做成功必須設(shè)置表為獨(dú)立表空間

具體參考：https://www.jb51.net/article/134901.htm，或下面

/ --------------------? ? ? ? ?寫在最前面? --? ? ?end? ? ? ? ? ? --------------------------------/

一、什么是表分區(qū)

通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個小表。mysql5.1開始支持?jǐn)?shù)據(jù)表分區(qū)了。

如：某用戶表的記錄超過了1000萬條，那么就可以根據(jù)入庫日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當(dāng)然也可根據(jù)其他的條件分區(qū)。

二、為什么要對表進(jìn)行分區(qū)

為了改善大型表以及具有各種訪問模式的表的可伸縮性，可管理性和提高數(shù)據(jù)庫效率。

分區(qū)的一些優(yōu)點包括：

1)、與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。

2)、對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。通常和分區(qū)有關(guān)的其他優(yōu)點包括下面列出的這些。MySQL分區(qū)中的這些功能目前還沒有實現(xiàn)，但是在我們的優(yōu)先級列表中，具有高的優(yōu)先級；我們希望在5.1的生產(chǎn)版本中，能包括這些功能。

3)、一些查詢可以得到極大的優(yōu)化，這主要是借助于滿足一個給定WHERE語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，這樣在查找時就不用查找其他剩余的分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進(jìn)行修改，所以在第一次配置分區(qū)方案時還不曾這么做時，可以重新組織數(shù)據(jù)，來提高那些常用查詢的效率。

4)、涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區(qū)上同時進(jìn)行，最終結(jié)果只需通過總計所有分區(qū)得到的結(jié)果。

5)、通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。

三、分區(qū)類型

· RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

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

· HASH分區(qū)：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

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

1.RANGE分區(qū)

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

2.LIST分區(qū)

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

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

3.HASH分區(qū)

基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。

要使用HASH分區(qū)來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數(shù)的表達(dá)式。它可以僅僅是字段類型為MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句，其中num 是一個非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。

4.KSY分區(qū)

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

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

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

注意為了查看方便我這邊對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(c1int?default?null,c2 varchar(30)default?null,c3 datedefault?null) engine=myisam

PARTITIONby?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ū)表，主要用于作對比表字段和結(jié)構(gòu)和分區(qū)表一模一樣。

1

create table no_part_tab(c1int?default?null,c2 varchar(30)default?null,c3 datedefault?null) engine=myisam;

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

1

2

3

4

5

6

7

8

9

10

create PROCEDURE load_part_tab()

begin

declare iint?default?0;

while?i<1000000

do

insertinto?part_tab values(i,'test data part',

adddate('2007-01-01',(rand(i)*36520) mod 3652));

set?i=i+1;

endwhile;

end;

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

1

call load_part_tab();

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

1

2

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

[root@localhost test]# ls

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

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

1

insertinto?no_part_tabselect?*from?part_tab;

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

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

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

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

8.為何會分區(qū)會有如此大的區(qū)別我們通過查詢計劃分析。

8.1)分區(qū)的查詢計劃影響的行數(shù)是：99813

8.2)未分區(qū)查詢計劃影響的行數(shù)是：1000000

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

9.增加未索引字段查詢

1

select?*from?part_tabwhere?c3>date'2007-01-01'?and c3

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

1

select?*from?no_part_tabwhere?c3>date'2007-01-01'?and c3

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

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

四.innodb表分區(qū)

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

1.什么是共享表空間和獨(dú)立表空間

貢獻(xiàn)表空間已經(jīng)獨(dú)立表空間都是針對數(shù)據(jù)的存儲方式而言的。

貢獻(xiàn)表空間：某一個數(shù)據(jù)庫的所有的表數(shù)據(jù)，索引文件全部放在一個文件中，默認(rèn)這個共享表空間的文件路徑在data目錄下。默認(rèn)的文件名為ibdata1初始大小為10M。

獨(dú)占表空間：每個表都將會生成意獨(dú)立文件方式來進(jìn)行存儲，每個表都有一個.frm表描述，還有一個.dbd文件.其中這個文件包括了單獨(dú)一個表數(shù)據(jù)內(nèi)容以及索引內(nèi)容，默認(rèn)情況下它的存儲位置也是在表的位置之中。

2.共享表空間：

優(yōu)點：

可以放表空間分成多文件存放各個磁盤上(表空間文件大小不受表大小的限制，如一個表可以分布在不同的文件上)。

數(shù)據(jù)和文件放一起方便管理。

缺點：

所有的數(shù)據(jù)和索引存放到一個文件中講有一個很大的文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，這樣對于一個表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統(tǒng)計分析，日志系統(tǒng)這類應(yīng)用最不合適用共享表空間。

3.實際操作

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

編輯my.cnf 增加innodb_file_per_table=1

1

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

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

總結(jié)

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

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