关闭

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

标签: innodb存储分区 php之innod
68人阅读 评论(0) 收藏 举报


根据公司现有的系统,一个月将近有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,再使用过程中用分区字段进行查询会快很多。




0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:13820次
    • 积分:206
    • 等级:
    • 排名:千里之外
    • 原创:7篇
    • 转载:0篇
    • 译文:0篇
    • 评论:2条
    文章存档