mysql分区表-白红宇

mysql分区表

阅读量：5830 次

发布时间：2019-06-18

本文共 8846 字，大约阅读时间需要 29 分钟。

分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.

最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.

限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,需要注意这类查询的性能.

水平分区（根据列属性按行分）

举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

水平分区的几种模式：

* Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，90年代（1990's）的数据，20世纪（2000's）的数据以及任何在2010年（包括2010年）后的数据。

* Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。

* Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

* List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

* Composite（复合模式） -以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

垂直分区（按列分）

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

水平分区表语法:

　　分区表分为RANGE,LIST,HASH,KEY以及复合模式五种类型,并且分区表的索引是可以局部针对分区表建立的。

CREATE TABLE `xaction_log_time` (

`xaction_idx` int(10) unsigned NOT NULL AUTO_INCREMENT,

`pidx` int(10) unsigned DEFAULT NULL,

`time` datetime NOT NULL,

PRIMARY KEY (`xaction_idx`,`time`),

KEY `pidx` (`pidx`)

) ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(time))

(PARTITION p_2009 VALUES LESS THAN (2009) ,

PARTITION p_2010 VALUES LESS THAN (2010) ,

PARTITION p_2011 VALUES LESS THAN (2011) ,

PARTITION p_2012 VALUES LESS THAN (2012) ,

PARTITION p_2013 VALUES LESS THAN (2013) ,

PARTITION p_2014 VALUES LESS THAN (2014) ,

PARTITION p_2015 VALUES LESS THAN (2015) ,

PARTITION p_catchall VALUES LESS THAN MAXVALUE ) ;

　　这段语句表示将表内数据按照tiem的年份范围进行分区,

　　要注意如果这么做,则tiem必须包含在主键中,且会产生一个问题,就是当年份超过阈值,到了2016年时,需要手动创建这些分区

新增加分区：

mysql> ALTER TABLE xaction_log_time ADD PARTITION (PARTITION p2016 VALUES LESS THAN (2016));

删除分区：

当删除了一个分区，也同时删除了该分区中所有的数据。

mysql> ALTER TABLE xaction_log_time DROP PARTITION p2010;

TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。

分区的合并

下面的SQL，将p2010 - p2012 合并为1个分区p2012-3

mysql> ALTER TABLE xaction_log_time

REORGANIZE PARTITION p2010,p2011,p2012,

INTO (PARTITION p2010-3 VALUES LESS THAN (2012));

拆分分区

mysql> ALTER TABLE xaction_log_time REORGANIZE PARTITION p2010INTO (

PARTITION s2009 VALUES LESS THAN (201001),

PARTITION s2010 VALUES LESS THAN (201006)

);

分区并不能总是改善性能，要进行性能评测。例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据：

mysql> explain partitions select count(*) from xaction_log_time where time>'2009-10-01' and time <'2015-10-02';

+----+-------------+------------------+------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+

+----+-------------+------------------+------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+

+----+-------------+------------------+------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+

1 row in set (0.00 sec)

mysql> explain partitions select count(*) from xaction_log_time where time>'2009-10-01' and time <'2009-10-02';

+----+-------------+------------------+------------+-------+---------------+------+---------+------+---------+--------------------------+

+----+-------------+------------------+------------+-------+---------------+------+---------+------+---------+--------------------------+

+----+-------------+------------------+------------+-------+---------------+------+---------+------+---------+--------------------------+

* 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间

* 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。

HASH模式可以不用再手动添加新的分区：

CREATE TABLE `xaction_log_time` (

`xaction_idx` int(10) unsigned NOT NULL AUTO_INCREMENT,

`pidx` int(10) unsigned DEFAULT NULL,

`time` datetime NOT NULL,

PRIMARY KEY (`xaction_idx`,`time`),

KEY `pidx` (`pidx`)

) ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 PARTITION BY HASH(xaction_idx DIV 1000000);

这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响

list类型：

CREATE TABLE category (

cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY LIST (cid) (

PARTITION p0 VALUES IN (0,4,8,12)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES IN (1,5,9,13)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2 VALUES IN (2,6,10,14)

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3 VALUES IN (3,7,11,15)

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4个区，数据文件和索引文件单独存放。

KEY 类型

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY KEY (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx',

PARTITION p2

DATA DIRECTORY = '/data4/data'

INDEX DIRECTORY = '/data5/idx',

PARTITION p3

DATA DIRECTORY = '/data6/data'

INDEX DIRECTORY = '/data7/idx'

);

分成4个区，数据文件和索引文件单独存放。

复合类型

CREATE TABLE employees (

emp_no int(11) NOT NULL,

birth_date date NOT NULL,

first_name varchar(14) NOT NULL,

last_name varchar(16) NOT NULL,

gender char(1) DEFAULT NULL,

hire_date date NOT NULL

) ENGINE=MyISAM

PARTITION BY RANGE COLUMNS(gender,hire_date)

(PARTITION p01 VALUES LESS THAN ('F','1990-01-01') ,

PARTITION p02 VALUES LESS THAN ('F','2000-01-01') ,

PARTITION p03 VALUES LESS THAN ('F',MAXVALUE) ,

PARTITION p04 VALUES LESS THAN ('M','1990-01-01') ,

PARTITION p05 VALUES LESS THAN ('M','2000-01-01') ,

PARTITION p06 VALUES LESS THAN ('M',MAXVALUE) ,

PARTITION p07 VALUES LESS THAN (MAXVALUE,MAXVALUE) );

explain partitions select count(*) from employees where gender='F' and hire_date < '1990-01-01'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

partitions: p01

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 300024

Extra: Using where

explain partitions select count(*) from employees where gender='F'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

partitions: p01,p02,p03,p04

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 300024

Extra: Using where

explain partitions select count(*) from employees where hire_date < '1990-01-01'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

partitions: p01,p02,p03,p04,p05,p06,p07

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 300024

Extra: Using where

复合分区如果不用分区定义的第一部分，使用分区定义的第二部分，那么将会发生全表扫描。

子分区

子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如：

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx'

);

对RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT '',

email VARCHAR(30) NOT NULL DEFAULT ''

)

PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = '/data0/data'

INDEX DIRECTORY = '/data1/idx',

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = '/data2/data'

INDEX DIRECTORY = '/data3/idx'

);

对 RANGE 分区再次进行子分区划分，子分区采用 KEY 类型。

对于大数据而言

　　对于大数据(如10TB)而言,索引起到的作用相对小,因为索引的空间与维护成本很高,另外如果不是索引覆盖查询,将导致回表,造成大量磁盘IO.那么对于这种情况的解决策略是:

　　1.全量扫描数据,不要任何索引

　　通过分区表表达式将数据定位在少量的分区表,然后正常访问这些分区表的数据

　　2.分离热点,索引数据

　　将热点数据分离出来在一个小的分区,并对分区建立索引,对热点数据的查询提高效率.

分区表的问题

　　1.NULL值使分区过滤无效

　　假设按照RANGE YEAR(time)分区,那么如果这个表达式计算出来的时NULL值,记录就会被存放到第一个分区.所以在查询时加入查询条件有可能出现NULL值,那么就会去检查第一个分区.

解决的方法可以是将第一个分区建立为NULL分区 PARTITION p_nulls VALUES LESS THAN (0),或者在MySQL5.5以后,直接使用COLUMN建立分区 PARTITION BY RANGE COLUMNS(time)

　　2. 选择分区的成本

　　每插入一行数据都需要按照表达式筛选插入的分区地址

　　3. 分区列和索引列不匹配

　　如果索引列和分区列不匹配,且查询中没有包含过滤分区的条件,会导致无法进行分区过滤,那么将会导致查询所有分区.

　　4. 打开并锁住所有底层表

　　分区表的的查询策略是在分区过滤之前,打开并锁住所有底层表,这会造成额外的开销,解决问题的方法是尽量使用批量操作,例如LOAD DATA INFILE,或者一次删除多行数据.

过滤分区表的要点

　　过滤分区表的WHERE条件必须是切分分区表的列,而不能带有函数,例如只能是time,而不能是YEAR(time)

转载于:https://blog.51cto.com/9548010/1752457

你可能感兴趣的文章

抓住云机遇编排工作搞定复杂IT工作流

查看>>

MYSQL的longtext字段能放多少数据？

查看>>

MTK 平台上如何给 camera 添加一种 preview size

直播源码开发视频直播平台，不得不了解的流程

查看>>

Ubuntu上的pycrypto给出了编译器错误

查看>>

聊聊flink的RestClientConfiguration

查看>>

在CentOS上搭建git仓库服务器以及mac端进行克隆和提交到远程git仓库

【BATJ面试必会】JAVA面试到底需要掌握什么？【上】

查看>>

CollabNet_Subversion小结