MySQL分區(qū)表的正確使用方法

MySQL分區(qū)表概述

我們經(jīng)常遇到一張表里面保存了上億甚至過(guò)十億的記錄，這些表里面保存了大量的歷史記錄。 對(duì)于這些歷史數(shù)據(jù)的清理是一個(gè)非常頭疼事情，由于所有的數(shù)據(jù)都一個(gè)普通的表里。所以只能是啟用一個(gè)或多個(gè)帶where條件的delete語(yǔ)句去刪除（一般where條件是時(shí)間）。 這對(duì)數(shù)據(jù)庫(kù)的造成了很大壓力。即使我們把這些刪除了，但底層的數(shù)據(jù)文件并沒(méi)有變小。面對(duì)這類(lèi)問(wèn)題，最有效的方法就是在使用分區(qū)表。最常見(jiàn)的分區(qū)方法就是按照時(shí)間進(jìn)行分區(qū)。

分區(qū)一個(gè)最大的優(yōu)點(diǎn)就是可以非常高效的進(jìn)行歷史數(shù)據(jù)的清理。

1. 確認(rèn)MySQL服務(wù)器是否支持分區(qū)表

命令：

show plugins;

2. MySQL分區(qū)表的特點(diǎn)

在邏輯上為一個(gè)表，在物理上存儲(chǔ)在多個(gè)文件中

HASH分區(qū)（HASH）

HASH分區(qū)的特點(diǎn)

• 根據(jù)MOD(分區(qū)鍵，分區(qū)數(shù))的值把數(shù)據(jù)行存儲(chǔ)到表的不同分區(qū)中
• 數(shù)據(jù)可以平均的分布在各個(gè)分區(qū)中
• HASH分區(qū)的鍵值必須是一個(gè)INT類(lèi)型的值，或是通過(guò)函數(shù)可以轉(zhuǎn)為INT類(lèi)型

如何建立HASH分區(qū)表

以INT類(lèi)型字段 customer_id為分區(qū)鍵

CREATE TABLE `customer_login_log` (
 `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶(hù)ID',
 `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用戶(hù)登錄時(shí)間',
 `login_ip` int(10) unsigned NOT NULL COMMENT '登錄IP',
 `login_type` tinyint(4) NOT NULL COMMENT '登錄類(lèi)型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶(hù)登錄日志表'

PARTITION BY HASH(customer_id) PARTITIONS 4;

以非INT類(lèi)型字段 login_time 為分區(qū)鍵(需要先轉(zhuǎn)換成INT類(lèi)型)

CREATE TABLE `customer_login_log` (
 `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶(hù)ID',
 `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用戶(hù)登錄時(shí)間',
 `login_ip` int(10) unsigned NOT NULL COMMENT '登錄IP',
 `login_type` tinyint(4) NOT NULL COMMENT '登錄類(lèi)型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶(hù)登錄日志表'

PARTITION BY HASH(UNIX_TIMESTAMP(login_time)) PARTITIONS 4;

customer_login_log 表如果不分區(qū)，在物理磁盤(pán)上文件為

customer_login_log.frm # 存儲(chǔ)表原數(shù)據(jù)信息
customer_login_log.ibd # Innodb數(shù)據(jù)文件

如果按上面的建HASH分區(qū)表，則有五個(gè)文件

customer_login_log.frm 
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd

演示

使用起來(lái)和不分區(qū)是一樣的，看起來(lái)只有一個(gè)數(shù)據(jù)庫(kù)，其實(shí)有多個(gè)分區(qū)文件，比如我們要插入一條數(shù)據(jù)，不需要指定分區(qū)，MySQL會(huì)自動(dòng)幫我們處理

查詢(xún)

范圍分區(qū)（RANGE）

RANGE分區(qū)特點(diǎn)

• 根據(jù)分區(qū)鍵值的范圍把數(shù)據(jù)行存儲(chǔ)到表的不同分區(qū)中
• 多個(gè)分區(qū)的范圍要連續(xù)，但是不能重疊
• 默認(rèn)情況下使用VALUES LESS THAN屬性，即每個(gè)分區(qū)不包括指定的那個(gè)值

如何建立RANGE分區(qū)

如果沒(méi)有定義p3分區(qū)，當(dāng)插入的customer_id大于29999時(shí)會(huì)報(bào)錯(cuò)，定義了則超過(guò)的數(shù)據(jù)都存入p3中

RANGE分區(qū)的適用場(chǎng)景

• 分區(qū)鍵為日期或是時(shí)間類(lèi)型 （可以使得各個(gè)分區(qū)表的數(shù)據(jù)比較均衡，如果按上面的例子中以整型id為分區(qū)鍵，假如活躍用戶(hù)集中在10000-19999之間，則p1中的數(shù)據(jù)量就會(huì)比其他分區(qū)的數(shù)據(jù)量大很多，這就失去了分區(qū)的意義；而且按時(shí)間類(lèi)型分區(qū)，如果要按時(shí)間順序進(jìn)行數(shù)據(jù)的歸檔，則只需要對(duì)某一個(gè)分區(qū)進(jìn)行歸檔就可以了）
• 所有查詢(xún)中都包括分區(qū)鍵（避免跨分區(qū)查詢(xún)）
• 定期按分區(qū)范圍清理歷史數(shù)據(jù)

LIST分區(qū)

LIST分區(qū)的特點(diǎn)

• 按分區(qū)鍵取值的列表進(jìn)行分區(qū)
• 同范圍分區(qū)一樣，各分區(qū)的列表值不能重復(fù)
• 每一行數(shù)據(jù)必須能找到對(duì)應(yīng)的分區(qū)列表，否則數(shù)據(jù)插入失敗

如何建立LIST分區(qū)

如果插入一條login_type為10的數(shù)據(jù)行，則會(huì)報(bào)錯(cuò)

3. 如何為登錄日志表（customer_login_log）分區(qū)

業(yè)務(wù)場(chǎng)景

• 用戶(hù)每次登錄都會(huì)記錄customer_login_log日志
• 用戶(hù)登錄日志保存一年，1年后可以刪除或者歸檔

登錄日志表的分區(qū)類(lèi)型及分區(qū)鍵

• 使用RANGE分區(qū)
• 以login_time為分區(qū)鍵

分區(qū)后的用戶(hù)登錄日志表

按年份分區(qū)存儲(chǔ)，所以用YEAR函數(shù)進(jìn)行了轉(zhuǎn)化

CREATE TABLE `customer_login_log` (
 `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶(hù)ID',
 `login_time` DATETIME NOT NULL COMMENT '用戶(hù)登錄時(shí)間',
 `login_ip` int(10) unsigned NOT NULL COMMENT '登錄IP',
 `login_type` tinyint(4) NOT NULL COMMENT '登錄類(lèi)型:0未成功 1成功'
) ENGINE=InnoDB 
PARTITION BY RANGE (YEAR(login_time))(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019)
) 

插入并查詢(xún)數(shù)據(jù)

查詢(xún)指定表中的分區(qū)數(shù)據(jù)情況

SELECT table_name,partition_name,partition_description,table_rows FROM
information_schema.`PARTITIONS` WHERE table_name = 'customer_login_log';

再插入2條18年的日志，會(huì)存入p2表中

之前說(shuō)過(guò)建立分區(qū)表時(shí)，最好建立一個(gè)MAXVALUE的分區(qū)，這里之所以沒(méi)有建立，是為了數(shù)據(jù)維護(hù)的方便，如果我們建立了MAXVALUE分區(qū)，很容易忽視一個(gè)問(wèn)題，當(dāng)我們2019年有的數(shù)據(jù)插入時(shí)，會(huì)自動(dòng)存入那個(gè)MAXVALUE分區(qū)中，之后在做數(shù)據(jù)維護(hù)時(shí)會(huì)不方便，所以沒(méi)有建立MAXVALUE分區(qū)

而是通過(guò)計(jì)劃任務(wù)的方式，在每年年底的時(shí)候增加這個(gè)分區(qū)，比如我們現(xiàn)在在2018年年底，我們需要在日志表中為2019年建立日志分區(qū)，否則2019年的日志都會(huì)插入失敗

我們可以通過(guò)下面語(yǔ)句

增加分區(qū)

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))

增加分區(qū)，并插入數(shù)據(jù)

刪除分區(qū)

假如我們現(xiàn)在要?jiǎng)h除2016年到2017年間一年的數(shù)據(jù)，因?yàn)槲覀円呀?jīng)做了分區(qū)，所以只需要通過(guò)一條語(yǔ)句，刪除p0分區(qū)即可

ALTER TABLE customer_login_log DROP PARTITION p0;

可以發(fā)現(xiàn)p0分區(qū)已被刪除，且2016年的日志全部被清除了

歸檔分區(qū)歷史數(shù)據(jù)

我們可能有另一種需求對(duì)數(shù)據(jù)進(jìn)行歸檔

Mysql版本>=5.7，歸檔分區(qū)歷史數(shù)據(jù)非常方便，提供了一個(gè)交換分區(qū)的方法

分區(qū)數(shù)據(jù)歸檔遷移條件：

• MySQL>=5.7
• 結(jié)構(gòu)相同
• 歸檔到的數(shù)據(jù)表一定要是非分區(qū)表
• 非臨時(shí)表；不能有外鍵約束
• 歸檔引擎要是：archive

建表并交換分區(qū)

CREATE TABLE `arch_customer_login_log` (
 `customer_id` INT unsigned NOT NULL COMMENT '登錄用戶(hù)ID',
 `login_time` DATETIME NOT NULL COMMENT '用戶(hù)登錄時(shí)間',
 `login_ip` INT unsigned NOT NULL COMMENT '登錄IP',
 `login_type` TINYINT NOT NULL COMMENT '登錄類(lèi)型:0未成功 1成功'
) ENGINE=InnoDB ;

ALTER TABLE customer_login_log 
 exchange PARTITION p1 WITH TABLE arch_customer_login_log;

可以發(fā)現(xiàn)，原customer_login_log表中的2017年的數(shù)據(jù)（p1分區(qū)中的數(shù)據(jù)）已轉(zhuǎn)移到了arch_customer_login_log表中，但是p1分區(qū)未刪除，只是數(shù)據(jù)轉(zhuǎn)移了,所以我們還需要執(zhí)行DROP命令刪除分區(qū)，以免有數(shù)據(jù)插入其中

將歸檔數(shù)據(jù)的存儲(chǔ)引擎改為歸檔引擎

最后我們將歸檔數(shù)據(jù)的存儲(chǔ)引擎改為歸檔引擎，命令為

ALTER TABLE customer_login_log ENGINE=ARCHIVE;

使用歸檔引擎的好處是：它比Innodb所占用的空間更少，但是歸檔引擎只能進(jìn)行查詢(xún)操作，不能進(jìn)行寫(xiě)操作

4. 使用分區(qū)表的主要事項(xiàng)

• 結(jié)合業(yè)務(wù)場(chǎng)景選擇分區(qū)鍵，避免跨分區(qū)查詢(xún)
• 對(duì)分區(qū)表進(jìn)行查詢(xún)最好在WHERE從句中包含分區(qū)鍵
• 具有主鍵或唯一索引的表，主鍵或唯一索引必須是分區(qū)鍵的一部分（這也是為什么我們上面分區(qū)時(shí)去掉了主鍵登錄日志id(login_id)的原因，不然就無(wú)法按照上面的按年份進(jìn)行分區(qū)，所以分區(qū)表其實(shí)更適合在MyISAM引擎中）

關(guān)于MyISAM和Innodb的索引區(qū)別

1.關(guān)于自動(dòng)增長(zhǎng)

myisam引擎的自動(dòng)增長(zhǎng)列必須是索引，如果是組合索引，自動(dòng)增長(zhǎng)可以不是第一列，他可以根據(jù)前面幾列進(jìn)行排序后遞增。

innodb引擎的自動(dòng)增長(zhǎng)咧必須是索引，如果是組合索引也必須是組合索引的第一列。

2.關(guān)于主鍵

myisam允許沒(méi)有任何索引和主鍵的表存在，

myisam的索引都是保存行的地址。

innodb引擎如果沒(méi)有設(shè)定主鍵或者非空唯一索引，就會(huì)自動(dòng)生成一個(gè)6字節(jié)的主鍵(用戶(hù)不可見(jiàn))

innodb的數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

3.關(guān)于count()函數(shù)

myisam保存有表的總行數(shù)，如果select count(*) from table;會(huì)直接取出出該值

innodb沒(méi)有保存表的總行數(shù)，如果使用select count(*) from table；就會(huì)遍歷整個(gè)表，消耗相當(dāng)大，但是在加了wehre 條件后，myisam和innodb處理的方式都一樣。

4.全文索引

myisam支持 FULLTEXT類(lèi)型的全文索引

innodb不支持FULLTEXT類(lèi)型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一個(gè)開(kāi)源軟件，提供多種語(yǔ)言的API接口，可以?xún)?yōu)化mysql的各種查詢(xún)）

5.delete from table

使用這條命令時(shí)，innodb不會(huì)從新建立表，而是一條一條的刪除數(shù)據(jù)，在innodb上如果要清空保存有大量數(shù)據(jù)的表，最 好不要使用這個(gè)命令。(推薦使用truncate table，不過(guò)需要用戶(hù)有drop此表的權(quán)限)

6.索引保存位置

myisam的索引以表名+.MYI文件分別保存。

innodb的索引和數(shù)據(jù)一起保存在表空間里。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。