1. 基本概念

1.1 什么是表分區(qū)？

分區(qū)原理：客戶端 --> Id 和分區(qū)鍵進行比較–>找到指定分區(qū)–>和數(shù)據(jù)庫查詢一致

表分區(qū)是指根據(jù)一定規(guī)則，將數(shù)據(jù)庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分區(qū)組成。

簡單來說：就是把一張表數(shù)據(jù)分塊存儲,提升索引的查詢效率

當(dāng)一個表中的數(shù)據(jù)量太大時，會面臨兩個問題，一是對數(shù)據(jù)的操作會變慢，比如select、join、update、delete時，會對全表操作；二是不便于存儲，可能會出現(xiàn)剩余磁盤空間存儲不下這張表的情況。而分區(qū)就可以在一定程度上解決這兩個問題。

mysql數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認(rèn)放在/mysql/data下面（可以通過my.cnf中的datadir來查看），一張表主要對應(yīng)著三個文件，一個是frm存放表結(jié)構(gòu)的，一個是myd存放表數(shù)據(jù)的，一個是myi存表索引的。如果一張表的數(shù)據(jù)量太大，則myd,myi也會很大，查找數(shù)據(jù)很慢，此時可以利用mysql的分區(qū)功能，在物理上將該表對應(yīng)的三個文件，分割成許多個小塊，如此在查找數(shù)據(jù)時，只要知道這條數(shù)據(jù)在哪一塊，然后在那一塊找就可以，不用全部查找。如果表的數(shù)據(jù)太大，可能一個磁盤放不下，這個時候，我們可以把數(shù)據(jù)分配到不同的磁盤里面去。

簡要的說，分區(qū)就是將表物理截斷，但在邏輯上依然是一個整體，開發(fā)人員在數(shù)據(jù)操作時仍然是對這個整體大表進行操作，之后由數(shù)據(jù)庫底層自己去尋找對應(yīng)的分區(qū)進行操作，數(shù)據(jù)庫底層尋找分區(qū)這個過程對開發(fā)人員來說是透明的，這樣在數(shù)據(jù)操作時可以只對特定分區(qū)操作以提高效率，存儲時也可以將不同分區(qū)的物理文件分開存放。

注：當(dāng)過濾條件為分區(qū)的字段時才會自動尋找分區(qū)，否則還是全表掃描

1.2 表分區(qū)與分表的區(qū)別

分表：指的是通過一定規(guī)則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據(jù)時間成多個表。

分表與分區(qū)的區(qū)別在于：分區(qū)從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

1.3 表分區(qū)有什么好處？

• 存儲更多。與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。
• 便于刪除。刪除數(shù)據(jù)時，可以通過刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。
• 查詢優(yōu)化。
   （1）滿足一個給定WHERE語句的數(shù)據(jù)可以只保存在一個或多個分區(qū)內(nèi)，在查找時無需查找其他分區(qū)。因為分區(qū)可以在創(chuàng)建了分區(qū)表后進行修改，所以在第一次配置分區(qū)方案還不曾這么做時，可以重新組織數(shù)據(jù)，來提高常用查詢的效率。
    （2）聚合函數(shù)（例如SUM()、COUNT()）的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區(qū)上同時進行，最終結(jié)果為總計所有分區(qū)得到的結(jié)果。
    （3）通過跨多個磁盤來分散數(shù)據(jù)查詢，獲得更大的查詢吞吐量。

1.4 分區(qū)表的限制因素

• 一個表最多只能又1024個分區(qū)，5.7版本時可以支持8196個分區(qū)。 8196個分區(qū)只8196個文件，linux中cd proc/1064/fd 文件描述符太多會影響io效率；還有ulimit -a 中open files最大可以同時打開1024個文件?？梢孕薷?，但是要和內(nèi)存匹配，1G內(nèi)存最多打開10萬個文件。
• MySQL5.1中，分區(qū)表達(dá)式必須是整數(shù)或者是返回整數(shù)表達(dá)式，mysql5.5中，可以直接使用列進行分區(qū)。
• 如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來。即：分區(qū)字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。
• 分區(qū)表中無法使用外鍵約束。
• MySQL的分區(qū)適用于一個表的所有數(shù)據(jù)和索引，不能只對表數(shù)據(jù)分區(qū)而不對索引分區(qū)，也不能只對索引分區(qū)而不對表分區(qū)，也不能只對表的一部分?jǐn)?shù)據(jù)分區(qū)。
• 必須使用分區(qū)字段才行，不然分區(qū)查詢就會失敗。走所有分區(qū)。

2. 如何判斷當(dāng)前MySQL是否支持分區(qū)？

mysql> show plugins

即：看名為partition的插件是否為active，active表示支持分區(qū)。

并且同一個數(shù)據(jù)庫，不同表支持分區(qū)可以是不同的存儲引擎，但是表分區(qū)后所有的分區(qū)都必須和表使用相同引擎。

MyISAM和InnoDB都支持分區(qū)。

MySQL 8都無需插件即可支持分區(qū)，且只有InnoDB支持，MyISAM不支持分區(qū)。

MySQL 5.7 的NDB支持分區(qū)有自己的規(guī)則。

MySQL只支持水平分區(qū)，對垂直分區(qū)的支持無計劃。

3.分區(qū)類型詳解

3.1 MySQL支持的分區(qū)類型有哪些？

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

說明：在MySQL5.1版本中，RANGE,LIST,HASH分區(qū)要求分區(qū)鍵必須是INT類型，或者通過表達(dá)式返回INT類型。但KEY分區(qū)的時候，可以使用其他類型的列(BLOB，TEXT類型除外)作為分區(qū)鍵。

mysql官方介紹鏈接：分區(qū)的創(chuàng)建和限制

3.2 RANGE分區(qū)

根據(jù)范圍分區(qū)，范圍應(yīng)該連續(xù)但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關(guān)鍵字。不使用COLUMNS關(guān)鍵字時RANGE括號內(nèi)必須為整數(shù)字段名或返回確定整數(shù)的函數(shù)。

3.2.1 根據(jù)數(shù)值范圍分區(qū)

示例如下：

1.創(chuàng)建表和分區(qū)邏輯，并插入數(shù)據(jù)

-- 建表
drop table if exists employees;
create table employees(
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null default 0,
    store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21)
);
-- 插入數(shù)據(jù)
insert into employees (id,fname,lname,hired,store_id) values(1,'張三','張','2015-05-04',1);
insert into employees (id,fname,lname,hired,store_id) values(2,'李四','李','2016-10-01',5);
insert into employees (id,fname,lname,hired,store_id) values(3,'王五','王','2016-11-14',10);
insert into employees (id,fname,lname,hired,store_id) values(4,'趙六','趙','2017-08-24',15);
insert into employees (id,fname,lname,hired,store_id) values(5,'田七','田','2018-05-20',20);

查詢數(shù)據(jù)如下：

mysql> SELECT * FROM employees;

查看data文件如下：

按照這種分區(qū)方案，在商店1到5工作的雇員相對應(yīng)的所有行被保存在分區(qū)P0中，商店6到10的雇員保存在P1中，依次類推。注意，每個分區(qū)都是按順序進行定義，從最低到最高。這是PARTITION BY RANGE 語法的要求。

但是如果增加了一個編號為第21的商店(7,‘周九’,‘周’,‘2018-07-24’,21)，將會發(fā)生什么呢？

在這種方案下，由于沒有規(guī)則把store_id大于20的商店包含在內(nèi)，服務(wù)器將不知道把該行保存在何處，將會導(dǎo)致錯誤。
執(zhí)行：mysql> insert into employees (id,fname,lname,hired,store_id) values(7,'周九','周','2018-07-24',21);執(zhí)行結(jié)果：
ERROR 1526 (HY000): Table has no partition for value 21

要避免這種錯誤，可以通過在CREATE TABLE語句中使用一個“catchall” VALUES LESS THAN子句，該子句提供給所有大于明確指定的最高值的值：
即partition 自居修改為：

partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21),
    partition p4 values less than MAXVALUE
);

3.2.2 根據(jù)TIMESTAMP范圍分區(qū)

示例如下：

drop table if exists report;
create table report(
  r_id int not null,
  r_status varchar(20) not null,
  r_updated timestamp not null default current_timestamp on update current_timestamp
)
partition by range(unix_timestamp(r_updated))(
  partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
  partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')),
  partition p9 values less than maxvalue
);

3.2.3 根據(jù)DATE、DATETIME范圍分區(qū)

  添加COLUMNS關(guān)鍵字可定義非integer范圍及多列范圍，不過需要注意COLUMNS括號內(nèi)只能是列名，不支持函數(shù)；多列范圍時，多列范圍必須呈遞增趨勢：
示例如下：

create table member(
  firstname varchar(25) not null,
  lastname varchar(25) not null,
  username varchar(16) not null,
  email varchar(35),
  joined date not null
)
partition by range columns(joined)(
  partition p0 values less than ('1960-01-01'),
  partition p1 values less than ('1990-01-01'),
  partition p2 values less than maxvalue
);

3.2.4 根據(jù)多列范圍分區(qū)

示例如下：

drop table if exists rd;
create table rd(
  a int,
  b int
)
partition by range columns(a,b)(
  partition p0 values less than (0,50),
  partition p1 values less than (50,100),
  partition p2 values less than (maxvalue,maxvalue)
)

3.2.5 RANGE分區(qū)適用場合

• 當(dāng)需要刪除一個分區(qū)上的“舊”數(shù)據(jù)時，只刪除分區(qū)即可。
• 想要使用一個包含有日期或時間值，或包含有從一些其他級數(shù)開始增長的值的列。
• 經(jīng)常運行直接依賴于用于分割表的列的查詢。例如，當(dāng)執(zhí)行一個如”select count(*) from staff where year(separated) = 200 group by store_id;”這樣的查詢時，MySQL可以很迅速地確定只有分區(qū)p2需要掃描，這是因為余下的分區(qū)不可能包含有符合該WHERE子句的任何記錄。

3.3 LIST分區(qū)

list就是枚舉的意思，list分區(qū)就是在創(chuàng)建各分區(qū)時具體指定哪些值屬于這些分區(qū)。

根據(jù)具體數(shù)值分區(qū)，每個分區(qū)數(shù)值不重疊，使用PARTITION BY LIST、VALUES IN關(guān)鍵字。

跟Range分區(qū)類似，不使用COLUMNS關(guān)鍵字時List括號內(nèi)必須為整數(shù)字段名或返回確定整數(shù)的函數(shù)。

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

LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實現(xiàn)，其中“expr”是某列值或一個基于某個列值、并返回一個整數(shù)值的表達(dá)式，然后通過“VALUES IN (value_list)”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(shù)列表。
示例：假定有20個音像店，分布在4個有經(jīng)銷權(quán)的地區(qū)，如下表所示

創(chuàng)建表如下：

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by list(store_id)(
  partition pNorth values in (3,5,6,9,17),
  partition pEast values in (1,2,10,11,19,20),
  partition pWest values in (4,12,13,14,18),
  partition pCentral values in (7,8,15,16)
);

這使得在表中增加或刪除指定地區(qū)的雇員記錄變得容易起來。例如，假定西區(qū)的所有音像店都賣給了其他公司。那么與在西區(qū)音像店工作雇員相關(guān)的所有記錄（行）可以使用查詢“ALTER TABLE staff DROP PARTITION pWest；”來進行刪除，它與具有同樣作用的DELETE（刪除）“DELETE FROM staff WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。

如果試圖插入列值（或分區(qū)表達(dá)式的返回值）不在分區(qū)值列表中的一行時，那么“INSERT”查詢將失敗并報錯。

當(dāng)插入多條數(shù)據(jù)出錯時，如果表的引擎支持事務(wù)（Innodb），則不會插入任何數(shù)據(jù)；如果不支持事務(wù)，則出錯前的數(shù)據(jù)會插入，后面的不會執(zhí)行。

與Range分區(qū)相同，添加COLUMNS關(guān)鍵字可支持非整數(shù)和多列。

3.4 HASH分區(qū)

HASH分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，Hash括號內(nèi)只能是整數(shù)列或返回確定整數(shù)的函數(shù)，實際上就是使用返回的整數(shù)對分區(qū)數(shù)取模。

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

示例：

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by hash(year(hired))
partitions 4;

Hash分區(qū)也存在與傳統(tǒng)Hash分表一樣的問題，可擴展性差。MySQL也提供了一個類似于一致Hash的分區(qū)方法－線性Hash分區(qū)，只需要在定義分區(qū)時添加LINEAR關(guān)鍵字。

線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by linear hash(year(hired))
partitions 4;

3.5 KEY分區(qū)

Key分區(qū)與Hash分區(qū)很相似，只是Hash函數(shù)不同，定義時把Hash關(guān)鍵字替換成Key即可，同樣Key分區(qū)也有對應(yīng)與線性Hash的線性Key分區(qū)方法。
示例如下：

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by key(store_id)
partitions 4;

在KEY分區(qū)中使用關(guān)鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。

另外，當(dāng)表存在主鍵或唯一索引時可省略Key括號內(nèi)的列名，Mysql將按照主鍵－唯一索引的順序選擇，當(dāng)找不到唯一索引時報錯。

3.6 組合分區(qū)

子分區(qū),也稱為復(fù)合分區(qū),是對已分區(qū)表中的每個分區(qū)進行進一步的劃分。

在MySQL 5.7中，可以對由RANGE或LIST分區(qū)的表進行第一層分區(qū)，第二層可以使用HASH分區(qū)或KEY分區(qū)。這也稱為復(fù)合分區(qū)。

使用SUBPARTITION子句顯式地定義子分區(qū)，為各個子分區(qū)指定選項。注意事項如下：

• 每個分區(qū)必須有相同數(shù)量的子分區(qū)。
• 如果在分區(qū)表的任何分區(qū)上使用SUBPARTITION顯式定義任何子分區(qū)，則必須定義所有子分區(qū)。
• 每個SUBPARTITION子句必須(至少)包含子分區(qū)的名稱。否則，您可以為子分區(qū)設(shè)置任何想要的選項，或者允許它為該選項設(shè)置默認(rèn)設(shè)置。
• 子分區(qū)名稱在整個表中必須是唯一的。語法示例：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

• 子分區(qū)可以與特別大的MyISAM表一起使用，將數(shù)據(jù)和索引分布到多個磁盤上。假設(shè)有6個磁盤掛載為/disk0、/disk1、/disk2等等。語法示例:

CREATE TABLE ts (id INT, purchased DATE)
    ENGINE = MYISAM
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0
                DATA DIRECTORY = '/disk0/data'
                INDEX DIRECTORY = '/disk0/idx',
            SUBPARTITION s1
                DATA DIRECTORY = '/disk1/data'
                INDEX DIRECTORY = '/disk1/idx'
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2
                DATA DIRECTORY = '/disk2/data'
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s3
                DATA DIRECTORY = '/disk3/data'
                INDEX DIRECTORY = '/disk3/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s5
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
        )
    );

示例：

CREATE TABLE `product-Partiton-flex` (
    `Id` BIGINT(8) NOT NULL,
    `ProductName` CHAR(245) NOT NULL DEFAULT '1',
    `ProductId` CHAR(255) NOT NULL DEFAULT '1',
    `ProductDescription` CHAR(255) NOT NULL DEFAULT '1',
    PRIMARY KEY (`Id`,`ProductName`),
    INDEX `ProductId` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (Id) PARTITIONS 3
SUBPARTITION BY KEY(ProductName)
  SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (12980),
    PARTITION p1 VALUES LESS THAN (25960),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

4. 帶分區(qū)的表操作語句

詳情鏈接：mysql分區(qū)表的增刪改查操作介紹及實例

到此這篇關(guān)于mysql創(chuàng)建表分區(qū)的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)mysql創(chuàng)建表分區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論