Mysql如何按照范圍區(qū)間創(chuàng)建分區(qū)表

更新時(shí)間：2024年08月01日 14:42:27 作者：威贊

在Mysql的范圍分區(qū)表定義中,分區(qū)范圍需要連續(xù)并且不會(huì)有覆蓋,定義范圍分區(qū)表時(shí),使用VALUES LESS THAN操作符,這篇文章主要介紹了Mysql如何按照范圍區(qū)間創(chuàng)建分區(qū)表,需要的朋友可以參考下

定義

每一個(gè)分區(qū)僅包含在指定范圍內(nèi)的數(shù)據(jù)列。這樣的分區(qū)方式就是范圍分區(qū)。在Mysql的范圍分區(qū)表定義中，分區(qū)范圍需要連續(xù)并且不會(huì)有覆蓋。定義范圍分區(qū)表時(shí)，使用VALUES LESS THAN操作符。在PARTITION BY RANGE語(yǔ)法中，建立分區(qū)表指定分區(qū)時(shí)，每一個(gè)分區(qū)都是按順序定義。使用時(shí)類似C語(yǔ)言和java中的if...elseif...表達(dá)式。

應(yīng)用

本文的幾個(gè)舉例，都假設(shè)用戶為一家具有20個(gè)門店的音響公司創(chuàng)建員工記錄表。這20家門店的編號(hào)store_id是1到20.

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,
    store_id INT NOT NULL
);

使用store_id列進(jìn)行分區(qū)

使用store_id列，將員工表建立四個(gè)分區(qū)

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,
    store_id INT NOT NULL
) 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)
);

在這個(gè)新建的分區(qū)表中，門店1-5的員工數(shù)據(jù)，保存在第一個(gè)分區(qū)p0中；門店6-10的員工數(shù)據(jù)，保存在第二個(gè)分區(qū)p1中....

按照上面的分區(qū)表定義，Mysql很容易將數(shù)據(jù)(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13) 插入第三個(gè)分區(qū)p2當(dāng)中。但這家公司開(kāi)了一家新的門店，第21個(gè)門店時(shí)，會(huì)出現(xiàn)什么樣的問(wèn)題。在這個(gè)分區(qū)表的定義中，并未指定門店編碼超過(guò)20的處理方式，因此插入第21個(gè)門店的員工數(shù)據(jù)時(shí)，Mysql會(huì)報(bào)錯(cuò)。Mysql給出來(lái)一個(gè)數(shù)值MAXVALUE來(lái)解決這個(gè)問(wèn)題。MAXVALUE是Mysql中最大的一個(gè)數(shù)值，所有數(shù)字都會(huì)比MAXVALUE小。則構(gòu)建該分區(qū)表的語(yǔ)句變成下面的形式

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,
    store_id INT NOT NULL
) 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 MAXVALUE
);

現(xiàn)在，所有門店編號(hào)大于等于16的數(shù)據(jù)，都會(huì)插入到第四個(gè)分區(qū)p3當(dāng)中。而當(dāng)后續(xù)門店增加時(shí)，數(shù)據(jù)庫(kù)管理員和開(kāi)發(fā)人員，可以為表建立新的分區(qū)。如為編號(hào)21-25門店添加新的分區(qū) ，為26-30的門店添加另一個(gè)分區(qū) 。

使用員工編號(hào)分區(qū)

使用員工編號(hào)分區(qū)的方式，是一種更加時(shí)髦的分區(qū)方法。在上面的例子中，數(shù)據(jù)庫(kù)管理員和開(kāi)發(fā)人員可以使用字段job_code進(jìn)行分區(qū)。如，兩位編碼用于保存店長(zhǎng)的信息。三位編碼分配給辦公室和后勤人員。而為銷售經(jīng)理分配四位編碼。則可以按照下面的方法來(lái)創(chuàng)建分區(qū)表

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,
    store_id INT NOT NULL
) partition by range (job_code) (
    partition p0 values less than (100),
    partition p1 values less than (1000),
    partition p2 values less than (10000)
);

使用日期時(shí)間字段建立分區(qū)

除了使用門店編號(hào)，和員工代碼分區(qū)外，還可以使用時(shí)間列來(lái)建立分區(qū)表。假設(shè)現(xiàn)在需要使用員工的離職年份建立分區(qū)。即使用YEAR(seperated)表達(dá)式返回的值來(lái)確定分區(qū)范圍。則使用下面的語(yǔ)句建立分區(qū)表

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,
    store_id INT NOT NULL
) partition by range (YEAR(separated)) (
    partition p0 values less than (1991),
    partition p1 values less than (1996),
    partition p2 values less than (2001),
    partition p4 values less than MAXVALUE
);

在上面的分區(qū)表中，1991年前離職的員工在分區(qū)p0中，而在1991-1995年離職的員工，在分區(qū)p1中...

當(dāng)然，也可以基于TIMESTAMP字段，按照時(shí)間范圍來(lái)建立分區(qū)表，使用UNIX_TIMESTAMP()方法，建立分區(qū)表如下。

CREATE TABLE quarterly_report_status (
  report_id INT NOT NULL,
  report_status VARCHAR(20) NOT NULL,
  report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
  PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
  PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
  PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
  PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
  PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
  PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
  PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
  PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
  PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
  PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

使用時(shí)間段分區(qū)使用下面的場(chǎng)景

當(dāng)開(kāi)發(fā)人員或數(shù)據(jù)庫(kù)管理員想要?jiǎng)h除舊數(shù)據(jù)的時(shí)候，如前面的employee表，用戶可以簡(jiǎn)單的使用ALTER TABLE employees DROP PARTITION p0,來(lái)刪除1991年前離職員工的所有數(shù)據(jù)。當(dāng)有很多數(shù)據(jù)需要?jiǎng)h除時(shí)，使用這種刪除方法比使用DELETE方法刪除數(shù)據(jù)效率會(huì)高很多。
用戶想要使用時(shí)間或其他序列的字段來(lái)操作數(shù)據(jù)表
用戶經(jīng)常使用表分區(qū)字段來(lái)進(jìn)行查詢。如當(dāng)執(zhí)行下面這條語(yǔ)句.Mysql能夠快速的使用where查詢條件找到第三個(gè)分區(qū)p2, 而不需要掃描其他分區(qū)的數(shù)據(jù)。

EXPLAIN SELECT COUNT(*) FROM employees WHERE seperated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id

范圍分區(qū)列

與范圍分區(qū)相似的用法就是分區(qū)列。使用RANGE COLUMNS方法構(gòu)建分區(qū)表，允許用戶使用多個(gè)列來(lái)建立分區(qū)表。這些列可以共同用于查詢時(shí)對(duì)掃描數(shù)據(jù)的修剪和過(guò)濾。

使用范圍分區(qū)列來(lái)創(chuàng)建分區(qū)表。

CREATE TABLE members (
    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 ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

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

您可能感興趣的文章: