数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应用来说分区完全是透明的。
分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,需要注意这类查询的性能。
2.1 分区表优点
可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。
方便维护,通过删除分区来删除老的数据。
分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。
可以优化、检查、修复个别分区。
根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了。
进行大数据搜索时可以进行并行处理。
跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
2.2 分区表缺点
每张表最大分区数为1024。
所有的主键或者唯一索引必须被包含在分区表达式中。
不能使用任何外键约束。
MYSQL的分区主要有两种形式:水平分区和垂直分区。
3.1 水平分区(根据列属性按行分)
这种形式的分区是对根据表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。
所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份,日期等。
例如:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。
3.1.1 水平分区几种模式:
Range(范围) – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980's)的数据,90年代(1990's)的数据以及任何在2000年(包括2000年)后的数据。
List(预定义列表) – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。
Hash(哈希) – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。
Key(键值) – Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
Composite(复合模式) - 其实是以上模式的组合使用而已。例如:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash或KEY(Linear Hash)分区。
3.2 垂直分区(按列分)
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应所有行。
例如:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。
4.1 查看分区表
查看是否支持分区
mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
mysql> show plugins;
+--------------------------+----------+--------------------+---------+-------------+
| Name | Status | Type | Library | License |
+--------------------------+----------+--------------------+---------+-------------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| CSV | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | PROPRIETARY |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| partition | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
+--------------------------+----------+--------------------+---------+-------------+
4.2 Range分区
创建range分区表
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
`sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女',
PRIMARY KEY (`id`)
) ENGINE=Innodb DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (3)
DATA DIRECTORY = '/data/data0'
INDEX DIRECTORY = '/data/index0',
PARTITION p1 VALUES LESS THAN (6)
DATA DIRECTORY = '/data/data1'
INDEX DIRECTORY = '/data/index1',
PARTITION p2 VALUES LESS THAN (9)
DATA DIRECTORY = '/data/data2'
INDEX DIRECTORY = '/data/index2',
PARTITION p3 VALUES LESS THAN (12)
DATA DIRECTORY = '/data/data3'
INDEX DIRECTORY = '/data/index3',
PARTITION p4 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/data/data4'
INDEX DIRECTORY = '/data/index4'
);
在这里,将用户表分成5个分区,以每3条记录为界限(示例),每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的物理磁盘分区可能也都是完全独立的,可以多大提高了磁盘IO吞吐量。