mysql数据库分区技术

mysql数据库的分区技术，可以有效地解决大数据量引起的读写效率低的问题。

数据库分区技术是mysql5.1版本以后才有的，该技术实现的目的大致上与数据库的分表技术类同，不过对于php开发人员来说不用修改所读取的表名，大大减少了由于分为多个水平表引起的维护代价。

一般来说一个数据库超过了1000万条时，各种查询开销都会很多，如果使用索引，索引占用内存与CPU也是很大的，这时候就需要考虑分表或者分区了，分表之后引起了代码维护量过多，这里建议分区，分区其实也会生成多个物理上的文件，但是逻辑上还是同一个表。

我这里的日志分析表，每天基本上在1万条，一年下来一个表基本上就几百万条记录，可能到时候就会占用过多的内存，而且查询效率也不高。

由于日志每天都会有记录，不存在写少读多的情况，所以这里创建表时就用innodb引擎，基本上可以保证不会因为锁表相互影响。

而myisam引擎在读表时，会导致另一个进程无法写入该表，这就明显不适合这一个工作场景了。

将每一个月的记录保存到一个分区里，也可以将一个季度的数据放到一个分区中，这里将每月的记录保存到了一张表中。分区的依据是写日志的时间，按照range进行分区。

基本上每一个表都要有一个主键，这里不能以id作为主键，但是可以是自增长的类型，如果以id为主键，则分区依据中一定要包含id字段才行，

这里只是以range（oprtime）作为分区的依据，所以不用将id设为主键，但是这条日志信息需要看详情时，需要根据id来查找（这样效率高一些），就需要将id作为一个索引了。

create table webservicelog(`id` int(11) unsigned not null auto_increment,
`fromto` tinyint(1) not null default '0',
`biztype` tinyint(2) not null default '0',
`bizcode` varchar(32) not null default '',
`result` tinyint(1) unsigned not null default '0',
`errmsg` varchar(256) not null default '',
`desc` varchar(100) not null default '',
`oprtime` datetime,
key `id` (`id`),
key `biz`(`biztype`,`bizcode`),
KEY `operatetime` (`oprtime`))ENGINE=InnoDB AUTO_INCREMENT=1  DEFAULT CHARSET=utf8 
partition by range(to_days(`oprtime`))
(partition p201610 values less than (to_days('2016-11-01')),
partition p201611 values less than (to_days('2016-12-01')),
partition p201612 values less than (to_days('2017-01-01')),
partition p201701 values less than maxvalue);


insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20161018081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20161118081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20161218081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20170118081011');
insert into webservicelog(fromto,biztype,bizcode,result,oprtime) values(1,1,'areyouok',1,'20170218081011');

如何查看分区文件呢？

可以看mysql的data文件夹下面的数据库文件，就可以查看当前分区情况。

查看一下mysql进程的datadir目录，如下图所示：

然后进入到datadir目录

cd /var/lib/mysql
cd test
可以看到webservicelog表的相关文件，如下图所示：

其中的以 .ibd结尾的那几个文件就是分区文件了。

也可以通过INFORMATION_SCHEMA.PARTITIONS表来查看分区的详细信息，
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等重要信息

查看分区是否生效

explain partitions select * from webservicelog where oprtime< date '2016-10-21';
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | webservicelog | p201610    | range | operatetime   | operatetime | 6       | NULL |    1 | Using where |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)

###删除并重新创建分区
alter table webservicelog drop partition p201701;
alter table webservicelog add partition(partition p201701 values less than (to_days('2017-02-01')));
alter table webservicelog add partition(partition p201702 values less than (to_days('2017-03-01')));

分区类型的区别：

hash类型只能针对int类型，但是key类型可以针对int类型字段也可以针对字符型字段。