前言

當(dāng)我們單表數(shù)據(jù)量比較大的時(shí)候，單表查詢的IO較大。這個(gè)時(shí)候，我們是不是可以將表的數(shù)據(jù)分成多個(gè)文件，按照某個(gè)條件進(jìn)行單文件的查詢，這樣避免了大量的IO操作。通過(guò)分而治之的思想，減少IO操作，提升查詢效率。本文主要是講述MySQL中分區(qū)表，看MySQL是如何實(shí)現(xiàn)分區(qū)表的。

什么需要分區(qū)表

? MySQL從5.1版本開(kāi)始支持分區(qū)功能，分區(qū)是將一個(gè)表的數(shù)據(jù)按照某種方式分別存儲(chǔ)，比如按照時(shí)間上的月份，分成多個(gè)較小的，更容易管理的部分，但是邏輯上仍是一個(gè)表。還沒(méi)出現(xiàn)分區(qū)表的時(shí)候，所有的數(shù)據(jù)都是存放在一個(gè)文件里面的，如果數(shù)據(jù)量太大，查詢數(shù)據(jù)時(shí)總是避免不了需要大量io操作；使用分區(qū)表后，每個(gè)分區(qū)存放不同的數(shù)據(jù)。這樣不但可以減少IO。還可以加快數(shù)據(jù)的訪問(wèn)；為了保證MySQL的性能，我們都建議MySQL單表不要太大，建議是：?jiǎn)伪硇∮?GB，記錄數(shù)小于1千萬(wàn)，十庫(kù)百表。如果但行記錄數(shù)非常小，那么記錄數(shù)可以再偏大些，反之，可能記錄數(shù)到百萬(wàn)級(jí)別就開(kāi)始變慢了。那么，業(yè)務(wù)量在增長(zhǎng)，數(shù)據(jù)到瓶頸了怎么辦呢，除了使用分布式數(shù)據(jù)庫(kù)，我們也可以自行分庫(kù)分表，或者利用MySQL的分區(qū)功能實(shí)現(xiàn)。分區(qū)表的出現(xiàn)是為了分而治之的概念，分區(qū)表的用處非常大，只是現(xiàn)在還有很多人都不知道；將一個(gè)表設(shè)置為分區(qū)表后，會(huì)在數(shù)據(jù)文件.i的文件名加上#號(hào)，代表這是一個(gè)分區(qū)表；

分區(qū)的策略

對(duì)于大數(shù)據(jù)表，有兩種策略進(jìn)行分區(qū)：

1: 不使用索引：創(chuàng)建數(shù)據(jù)表時(shí)不增加索引，而是使用分區(qū)定位到所需要的數(shù)據(jù)行。只要你使用 WHERE 條件將查詢切分到很小的分區(qū)范圍，就已經(jīng)足夠了。這個(gè)時(shí)候需要通過(guò)數(shù)學(xué)方法計(jì)算查詢的響應(yīng)時(shí)間是否能夠接受。當(dāng)然，這里的假設(shè)是不會(huì)將數(shù)據(jù)放到內(nèi)存中，而是全部數(shù)據(jù)都從磁盤(pán)讀取。因此數(shù)據(jù)很快就會(huì)被其他查詢覆蓋，使用緩存沒(méi)什么意義。這種情況一般用于大量數(shù)據(jù)表的基數(shù)是常規(guī)的。需要注意的是，需要限制分區(qū)數(shù)在幾百。

2: 使用索引，并且隔離熱區(qū)數(shù)據(jù)：如果除了熱區(qū)數(shù)據(jù)外，大部分?jǐn)?shù)據(jù)是不使用的，則可以將熱區(qū)數(shù)據(jù)單獨(dú)的分區(qū)，這個(gè)分區(qū)算上索引都能夠加載到內(nèi)存中。這個(gè)時(shí)候可以通過(guò)索引來(lái)優(yōu)化性能，就像操作普通的數(shù)據(jù)表一樣。

分區(qū)表應(yīng)用場(chǎng)景

1: 表非常大以至于無(wú)法全部放在內(nèi)存中，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)，其他都是歷史數(shù)據(jù)

2: 分區(qū)表的數(shù)據(jù)更容易維護(hù)，，能批量刪除大量數(shù)據(jù)

3: 對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作

4: 分區(qū)表的數(shù)據(jù)可以分布在不同的設(shè)備上，從未高效的利用多個(gè)硬件設(shè)備

5: 可以備份和恢復(fù)獨(dú)立的分區(qū)

分區(qū)表的限制

一個(gè)表最多能有1024個(gè)分區(qū)，在5.7版本及以上可以有8196個(gè)分區(qū)。在早期MySQL中，分區(qū)表達(dá)式必須是整數(shù)或者整返回整數(shù)的表達(dá)式，

在MySQL5.5中，某些場(chǎng)景可以直接使用列來(lái)進(jìn)行分區(qū)

• 分區(qū)表無(wú)法使用外檢約束
• 最好不要去修改分區(qū)列

如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)；就像這樣：

-- 創(chuàng)建分區(qū)必須包含所有主鍵
create table user_11(
  id bigint(20) not null ,
  name varchar(20) ,
  age int(3) not null ,
	PRIMARY KEY (`id`,`age`)
)

-- 創(chuàng)建分區(qū)
partition by range columns(id,age)(
  partition p00 values less than(6,30), -- 小于6的值在P0分區(qū)
  partition p11 values less than(11,40), -- 小于11的值在p1分區(qū)
  partition p22 values less than(16,50), -- 小于16的值在p2分區(qū)
  partition p33 values less than (9999,9999) -- 大于9999的值在p3分區(qū)，或者用一個(gè)更大的值
);

-- 創(chuàng)建分區(qū)必須包含所有唯一鍵
create table user_22(
  id bigint(20)  not null,
  name varchar(20) ,
  age int(3) not null ,
	unique key only_one_1(age,id )
)
-- 創(chuàng)建分區(qū)
partition by range columns(id,age)(
  partition p000 values less than(6,30), -- 小于6的值在P0分區(qū)
  partition p111 values less than(11,40), -- 小于11的值在p1分區(qū)
  partition p222 values less than(16,50), -- 小于16的值在p2分區(qū)
  partition p333 values less than (9999,9999) -- 大于9999的值在p3分區(qū)，或者用一個(gè)更大的值
);

分區(qū)類型

RANGE分區(qū)

基于屬于一個(gè)給定連續(xù)區(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 (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)
);

LIST分區(qū)

類似于按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ù)列表。 注釋：在MySQL 5.1中，當(dāng)使用LIST分區(qū)時(shí)，有可能只能匹配整數(shù)列表。

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

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á)式。它可以僅僅是字段類型為MySQL整型的一列的名字。此外，你很可能需要在后面再添加一個(gè)“PARTITIONS num”子句，其中num是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。

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,
  store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

LINER HASH

MySQL還支持線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個(gè)線性的2的冪（powers-of-two）運(yùn)算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語(yǔ)法上的唯一區(qū)別在于，在“PARTITION BY”子句中添加“LINEAR”關(guān)鍵字。

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,
  store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

KEY分區(qū)

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

CREATE TABLE tk (
  col1 INT NOT NULL,
  col2 CHAR(5),
  col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

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

到此這篇關(guān)于深入理解MySQL分區(qū)表的使用的文章就介紹到這了,更多相關(guān)MySQL 分區(qū)表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論