mysql分区表
分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.
限制单表分区数不要超过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>> 删除分区：
当删除了一个分区，也同时删除了该分区中所有的数据。
mysql>> TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。
分区的合并
下面的SQL，将p2010 - p2012 合并为1个分区p2012-3
mysql>> REORGANIZE PARTITION p2010,p2011,p2012,
INTO (PARTITION p2010-3 VALUES LESS THAN (2012));
拆分分区
mysql>> PARTITION s2009 VALUES LESS THAN (201001),
PARTITION s2010 VALUES LESS THAN (201006)
);
分区并不能总是改善性能，要进行性能评测。例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据：
mysql> explain partitionsselect count(*) from xaction_log_time where time>'2009-10-01' and time +----+-------------+------------------+------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
|1 | SIMPLE | xaction_log_time | p_2010,p_2011,p_2012,p_2013,p_2014,p_2015,p_catchall | index | NULL | pidx | 5 | NULL | 21582698 | Using where; Using index |
+----+-------------+------------------+------------------------------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)
mysql> explain partitionsselect count(*) from xaction_log_time where time>'2009-10-01' and time +----+-------------+------------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
|1 | SIMPLE | xaction_log_time | p_2010 | index | NULL | pidx | 5 | NULL | 1473670 | Using where; Using index |
+----+-------------+------------------+------------+-------+---------------+------+---------+------+---------+--------------------------+
* 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间
* 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
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 RANGECOLUMNS(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)
页:
[1]