表分区
表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。
-
- 水平分区
这种形式分区是对表的行进行分区，所有在表中定义的列，在每个数据集中都能找到，所以表的特性依然得以保持。 举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。 -
2、垂直分区
这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。 举个简单例子：一个包含了大 text 和 BLOB 列的表，这些 text 和 BLOB 列又不经常被访问，这时候就要把这些不经常使用的 text 和 BLOB 了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。表分区的优缺点
- 优点
1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据 2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。 3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。 4、避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。- 分区的限制
1、一个表最多只能有1024个分区 2、MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。 3、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。 4、分区表中无法使用外键约束。 5、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。- MySQL支持的分区类型有哪些？
RANGE分区：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区 LIST分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。 HASH分区：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。 KEY分区：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。-
分区使用示例
-
在5.6及以后查看当前配置是否支持分区：
mysql> show plugins; | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | #返回的结果中，有以上字段（如果status列为“ACTIVE”，则表示支持分区） -
按照范围（range）方式的表分区
mysql> create database test01; mysql> use test01; mysql> create table user( id int not null auto_increment, name varchar(30) not null default '', sex int(1) not null default '0', primary key(id) )default charset=utf8 auto_increment=1 partition by range(id)( partition p0 values less than (3), partition p1 values less than (6), partition p2 values less than (9), partition p3 values less than (12), partition p4 values less than maxvalue ); #在上面创建的表中，当id列的值小于3将会插入到p0分区，大于3小于6的记录将会插入到p1分区，以此类推，所有id值大于12的记录都会插入到p4分区-
利用存储过程插入一些数据
mysql> delimiter // #更改默认的阶段符 mysql> create procedure adduser() begin declare n int; declare summary int; set n = 0; while n <= 20 do insert into test01.user(name,sex) values('tom',0); set n = n + 1; end while; end // mysql> delimiter ; mysql> call adduser(); mysql> select * from user; -
到存放数据表文件的目录下查看
[root@db01 ~]# cd /usr/local/mysql/data/test01 [root@db01 test01]# ll 总用量 496 -rw-r----- 1 mysql mysql 61 4月 16 15:41 db.opt -rw-r----- 1 mysql mysql 8614 4月 16 15:41 user.frm -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p0.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p1.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p2.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p3.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p4.ibd #可以看到数据是被分散存到不同的文件中的，本地的文件名都是“user#P#p0...”命名的，其中p0是自定义的分区名 -
从information_schema系统库中的partition表中查看分区信息
select * from information_schema.partitions where table_schema='test01' and table_name='user'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test01 #库名 TABLE_NAME: user #表名 PARTITION_NAME: p0 #分区名 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 #分区位置，1表示第一个分区 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: RANGE #分区方式为range SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: id #以id列进行分区 SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 3 #分区范围是3 TABLE_ROWS: 2 #该分区中有两行数据 ……………… #省略部分内容 -
从分区中查询数据
select * from user partition(p1); +----+------+-----+ | id | name | sex | +----+------+-----+ | 3 | tom | 0 | | 4 | tom | 0 | | 5 | tom | 0 | +----+------+-----+ -
添加分区及合并分区（需要先合并分区再新增分区）
1）添加分区
注意：由于在创建表的时候，指定的最后一个分区range是maxvalue，所以是无法直接增加分区的，如下：
mysql> alter table user add partition (partition p5 values less than (20)); ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition #大意是：MAXVALUE只能在最后一个分区定义中使用但也不可以将最后定义了maxvalue的分区直接删除，因为删除分区的话，分区中的数据也会丢失，所以，如果需要新增分区的正确做法，应该是先合并分区，再新增分区，这样才可以保证数据的完整性，如下：
mysql> alter table user reorganize partition p4 into (partition p03 values less than (15),partition p04 values less than maxvalue ); 将最后一个分区分为两个分区，一个是自己所需要的分区，最后一个分区还是maxvalue（也必须是maxvalue），这样就完成了添加分区 [root@db01 test01]# ll #查看本地表文件 总用量 592 -rw-r----- 1 mysql mysql 61 4月 16 15:41 db.opt -rw-r----- 1 mysql mysql 8614 4月 16 16:16 user.frm -rw-r----- 1 mysql mysql 98304 4月 16 16:16 user#P#p03.ibd -rw-r----- 1 mysql mysql 98304 4月 16 16:16 user#P#p04.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p0.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p1.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p2.ibd -rw-r----- 1 mysql mysql 98304 4月 16 15:51 user#P#p3.ibd mysql> select * from user partition(p03); +----+------+-----+ | id | name | sex | +----+------+-----+ | 12 | tom | 0 | | 13 | tom | 0 | | 14 | tom | 0 | +----+------+-----+ mysql> select * from user partition(p04); +----+------+-----+ | id | name | sex | +----+------+-----+ | 15 | tom | 0 | | 16 | tom | 0 | | 17 | tom | 0 | | 18 | tom | 0 | | 19 | tom | 0 | | 20 | tom | 0 | | 21 | tom | 0 | +----+------+-----+ #查看新增分区中的数据2）合并分区
mysql> alter table user reorganize partition p0,p1,p2,p3 into (partition p02 values less than (12)); #将p0、p1、p2、p3四个分区合并为p02 mysql> select * from user partition(p02); +----+------+-----+ | id | name | sex | +----+------+-----+ | 1 | tom | 0 | | 2 | tom | 0 | | 3 | tom | 0 | | 4 | tom | 0 | | 5 | tom | 0 | | 6 | tom | 0 | | 7 | tom | 0 | | 8 | tom | 0 | | 9 | tom | 0 | | 10 | tom | 0 | | 11 | tom | 0 | +----+------+-----+ #查看合并后的数据 [root@db01 test01]# ll #本地文件 总用量 304 -rw-r----- 1 mysql mysql 61 4月 16 15:41 db.opt -rw-r----- 1 mysql mysql 8614 4月 16 16:20 user.frm -rw-r----- 1 mysql mysql 98304 4月 16 16:20 user#P#p02.ibd -rw-r----- 1 mysql mysql 98304 4月 16 16:16 user#P#p03.ibd -rw-r----- 1 mysql mysql 98304 4月 16 16:16 user#P#p04.ibd4.9 删除分区
mysql> alter table user drop partition p02; #删除分区p02 [root@db01 test01]# ll 总用量 208 -rw-r----- 1 mysql mysql 61 4月 16 15:41 db.opt -rw-r----- 1 mysql mysql 8614 4月 16 16:22 user.frm -rw-r----- 1 mysql mysql 98304 4月 16 16:16 user#P#p03.ibd -rw-r----- 1 mysql mysql 98304 4月 16 16:16 user#P#p04.ibd #分区被删除后，分区中的数据也将被删除，删除分区p02的表中所有数据
-
-
分表
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。
将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法（如用hash的方式，也可以用求余（取模）的方式），让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，减小数据库的负担，缩短查询时间。
- Mysql分表分为垂直切分和水平切分
水平拆分：指数据表行的拆分，把一张的表的数据拆成多张表来存放。 水平拆分原则，通常情况下，我们使用hash、取模等方式来进行表的拆分 比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4 通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3] 然后查询,更新,删除也是通过取模的方法来查询 部分业务逻辑也可以通过地区，年份等字段来进行归档拆分; 进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。 垂直切分：指数据表列的拆分，把一张列比较多的表拆分为多张表 通常我们按以下原则进行垂直拆分: - 把不常用的字段单独放在一张表; 把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中； - 经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用join关键起来即可；