前言

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

什么需要分區(qū)表

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

分區(qū)的策略

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

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

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

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

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

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

3: 對一個獨立分區(qū)進行優(yōu)化、檢查、修復(fù)等操作

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

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

分區(qū)表的限制

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

在MySQL5.5中，某些場景可以直接使用列來進行分區(qū)

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

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

-- 創(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ū)，或者用一個更大的值
);

-- 創(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ū)，或者用一個更大的值
);

分區(qū)類型

RANGE分區(qū)

基于屬于一個給定連續(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ū)是基于列值匹配一個離散值集合中的某個值來進行選擇。

LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實現(xiàn)，其中“expr”是某列值或一個基于某個列值、并返回一個整數(shù)值的表達式，然后通過“VALUES IN (value_list)”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(shù)列表。 注釋：在MySQL 5.1中，當使用LIST分區(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
);

HASH分區(qū)

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

要使用HASH分區(qū)來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數(shù)的表達式。它可以僅僅是字段類型為MySQL整型的一列的名字。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句，其中num是一個非負的整數(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ū)別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。線性哈希分區(qū)和常規(guī)哈希分區(qū)在語法上的唯一區(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ū)只支持計算一列或多列，且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ū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。

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

最新評論