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

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

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

定義

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

應用

本文的幾個舉例，都假設用戶為一家具有20個門店的音響公司創(chuàng)建員工記錄表。這20家門店的編號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列進行分區(qū)

使用store_id列，將員工表建立四個分區(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)
);

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

按照上面的分區(qū)表定義，Mysql很容易將數據(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13) 插入第三個分區(qū)p2當中。但這家公司開了一家新的門店，第21個門店時，會出現什么樣的問題。在這個分區(qū)表的定義中，并未指定門店編碼超過20的處理方式，因此插入第21個門店的員工數據時，Mysql會報錯。Mysql給出來一個數值MAXVALUE來解決這個問題。MAXVALUE是Mysql中最大的一個數值，所有數字都會比MAXVALUE小。則構建該分區(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 MAXVALUE
);

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

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

使用員工編號分區(qū)的方式，是一種更加時髦的分區(qū)方法。在上面的例子中，數據庫管理員和開發(fā)人員可以使用字段job_code進行分區(qū)。如，兩位編碼用于保存店長的信息。三位編碼分配給辦公室和后勤人員。而為銷售經理分配四位編碼。則可以按照下面的方法來創(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)
);

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

除了使用門店編號，和員工代碼分區(qū)外，還可以使用時間列來建立分區(qū)表。假設現在需要使用員工的離職年份建立分區(qū)。即使用YEAR(seperated)表達式返回的值來確定分區(qū)范圍。則使用下面的語句建立分區(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中...

當然，也可以基于TIMESTAMP字段，按照時間范圍來建立分區(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)
);

使用時間段分區(qū)使用下面的場景

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

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

范圍分區(qū)列

與范圍分區(qū)相似的用法就是分區(qū)列。使用RANGE COLUMNS方法構建分區(qū)表，允許用戶使用多個列來建立分區(qū)表。這些列可以共同用于查詢時對掃描數據的修剪和過濾。

使用范圍分區(qū)列來創(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
);

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

您可能感興趣的文章: