php innodb存储引擎实现分区存储数据

根据公司现有的系统，一个月将近有100W条数据。将来需求日满足日10W，月300W的数据要求。现在明显单个表已经不满足要求。通常的解决方法有分库分表，但是近期需改动代码。后来决定还是用mysql自带的分区功能来实现。

分区可采用水平分区和垂直分区，这里我使用了hash方式进行分区。根据id规则使数据分布到几个分区表内。

CREATE TABLE `sp_order_list_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `pid` int(11) DEFAULT NULL ,
  `order_id` varchar(19) DEFAULT NULL,
  `goods_id` int(11) DEFAULT NULL,
  `pay_status` tinyint(3) DEFAULT,
  `nper_id` int(11) DEFAULT NULL,
  `exec_data` text,
  `index_start` int(11) DEFAULT NULL,
  `index_end` int(255) DEFAULT NULL,
  `dealed` enum('false','true') DEFAULT 'false',
  `num` int(11) DEFAULT NULL,
  `goods_name` varchar(255) DEFAULT NULL,
  `bus_type` enum('recharge','buy') DEFAULT 'buy',
  `username` varchar(255) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id` (`order_id`,`id`) USING BTREE,
  KEY `nper_id` (`nper_id`),
  KEY `index` (`index_start`,`index_end`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表' PARTITION BY HASH(id) PARTITIONS 10 ;

在执行的时候，如果primary和unique中没有分区的字段就会报错。如下：

 A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION

MySQL主键的限制，每一个分区表中的公式中的列，必须在primary/unique key中

在MYSQL的官方文档里是这么说明的

18.5.1. Partitioning Keys, Primary Keys, and Unique Keys
This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.  

In other words, every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:  

执行成功后，在data目录下的数据库中可以看到：

接下里将之前的表内的数据导入到新的表内。

insert into sp_order_list_parent_new select * from sp_order_list_parent; 

之后再将老得多表备份删除或者修改名字，将新的表改成原来的名字即可。

分区表的删除，添加等操作可网上搜索。

分区索引测试：

没有用到索引：

<span style="font-size:12px;">SELECT * FROM `sp_order_list` WHERE `pid` = 1000</span>

显示行 0 - 0 ( 1 总计, 查询花费 0.9460 秒) [pid: 1000 - 1000]

使用EXPLAIN PARTITIONS 查看：

<pre name="code" class="sql">EXPLAIN PARTITIONS  SELECT * FROM `sp_order_list` WHERE <span style="font-family: "Hiragino Sans GB W3", "Hiragino Sans GB", Arial, Helvetica, simsun, u5b8bu4f53;">`pid` = 1000</span>

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sp_order_list	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9	ALL	NULL	NULL	NULL	NULL	498275	10.00	Using where

可以看到全表查询了，如果我们用到分区字段进行查询：

SELECT * FROM `sp_order_list` WHERE id = 1000

显示行 0 - 0 ( 1 总计, 查询花费 0.0004 秒) [pid: 1000 - 1000]

使用EXPLAIN PARTITIONS 查看：

EXPLAIN PARTITIONS  SELECT * FROM `sp_order_list` WHERE id = 1000

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sp_order_list	p0	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL

这里可以看到只扫描了p0，再使用过程中用分区字段进行查询会快很多。