mysql分区实践

数据库中的数据达到一定上限时候，表查询会很慢。我们常使用的策略就是对数据库进行分表（大表分成多张小表）、分区（大表拆分成区）。
下面主要介绍分区的几种方式和具体的实现

mac进入mysql命令模式

1. 查找mysql安装路径:

   find /usr/ -iname "mysql"

2. 设置mysql的别名

   alias mysql='/usr//local/mysql-5.6.35-macos10.12-x86_64/bin/mysql'

3. 进入mysql

   mysql -u root -p 

mysql表分区

分区的好处

• 可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询
• 分区与不使用分区相比，能够更好的维护数据。比如，可以很快的通过删除分区来移除旧数据。我们还可以优化、检查、修复个别分区
• 屏蔽分表的 复杂性
  分区前的准备

1.查看建表语句是否存在分区

show create table m_book;

2.查看是否支持分区

show variables like '%part%';

mysql的分区包括四种分区、list分区、hash分区、key分区以及这四种互相的复合分区
常使用的是range和hash分区，这里主要介绍如下：

range分区

range分区实践

1. 建表语句
   这里是通过id来分区的，有可以根据时间或者其他有意义的字段。

CREATE TABLE `m_books` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(45) NOT NULL,\n  `img_url` varchar(128) DEFAULT NULL,\n  `score` double DEFAULT \'0\',\n  `author` varchar(45) DEFAULT NULL,\n  `publish_company` varchar(45) DEFAULT NULL,\n  `publish_at` varchar(45) DEFAULT NULL,\n 
 `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=453 DEFAULT CHARSET=utf8
 partition by range(id)(
    PARTITION p0 VALUES LESS THAN (100),  
    PARTITION p1 VALUES LESS THAN (200),  
    PARTITION p2 VALUES LESS THAN (300),  
    PARTITION p3 VALUES LESS THAN (400),  
    PARTITION p4 VALUES LESS THAN MAXVALUE
 );

2.插入数据
具体的数据，后面会提供

insert into m_books select * from books

验证分区是否生效

思路是这样的，先查询表中的总数，然后删除一个分区；再次查询表中的总数。看是否有变化

-- 查询现有数据，为452
select count(*) from m_books
count
'452'
-- 删除第二个分区
alter table m_books drop partition p2
-- 再次查询数据，为352
select count(id) from m_books
count
'452'

hash分区

1. 把id作为partition key来进行分区

-- 创建一个hash分区的表
 CREATE TABLE `h_books` 
( `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `img_url` varchar(128) DEFAULT NULL,
  `score` double DEFAULT 0,
  `author` varchar(45) DEFAULT NULL,
  `publish_company` varchar(45) DEFAULT NULL,
  `publish_at` varchar(45) DEFAULT NULL, 
 `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)) 
 ENGINE=InnoDB AUTO_INCREMENT=453 DEFAULT CHARSET=utf8
 partition by hash(id) partitions 30;

1. 查询分区信息

 select partition_name part,partition_expression expr
 from information_schema.partitions where table_name='h_books'

这里遇到一个问题，当设置id为主键时候，如果把create_at作为partition Key来进行分区，此时mysql会不支持这么做。我的操作是这样的
* 重新定义时间作为分区主键

alter table h_books partition by hash(unix_timestamp(create_at)) partitions 20; 

报错信息

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

google一下原因，表的主键和patition的主键需要保持一致，MYSQL的分区字段，必须包含在主键字段内
将table设置为id和create_at的复合主键，于是创建时间的partition分区成功！

 CREATE TABLE `h_books` 
( `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `img_url` varchar(128) DEFAULT NULL,
  `score` double DEFAULT 0,
  `author` varchar(45) DEFAULT NULL,
  `publish_company` varchar(45) DEFAULT NULL,
  `publish_at` varchar(45) DEFAULT NULL, 
 `create_at` date,
 PRIMARY KEY (`id`,`create_at`)) 
 ENGINE=InnoDB AUTO_INCREMENT=453 DEFAULT CHARSET=utf8
 partition by hash(to_days(`create_at`)) partitions 30;

复合分区

MySQL中的四种分区类型能够组合成多种复合分区，如：RANGE-HASH、RANGE-KEY、LIST-HASH、LIST-KEY等。

• 重点是RANGE-HASH复合分区

CREATE TABLE `f_books` 
( `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `img_url` varchar(128) DEFAULT NULL,
  `score` double DEFAULT 0,
  `author` varchar(45) DEFAULT NULL,
  `publish_company` varchar(45) DEFAULT NULL,
  `publish_at` varchar(45) DEFAULT NULL, 
 `create_at` date,
 PRIMARY KEY (`id`,`create_at`)
 ) 
 ENGINE=InnoDB AUTO_INCREMENT=453 DEFAULT CHARSET=utf8
 partition by range(`id`)
 subpartition by hash(to_days(`create_at`)) subpartitions 3
 (
    partition p1 values less than (100),
    partition p2 values less than maxvalue
 );

上面主分区是range，分为2个区；下面是hash分区，分为3个区；总共分为2*3个区。

• 查询分区信息：

 select partition_name part,partition_expression expr,subpartition_name subPart,subpartition_expression as subExpr
 from information_schema.partitions where table_name='f_books'

分区操作的sql介绍

删除分区

 alter table f_books drop partition p2;

增加分区

alter table f_books add partition (partition p3 values less than (200));

参考博文
1. 还有一些其他的sql语法，这里可以参考
http://www.wiquan.com/article/669
2. 复合分区的详细介绍参考：http://www.cnblogs.com/ivictor/p/5026640.html
3. http://blog.51yip.com/mysql/1013.html
4. https://my.oschina.net/u/617773/blog/135963