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

原创 2016年08月31日 10:35:45


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




[MySQL] MySQL数据移植迁移 + 备份【InnoDB存储引擎】

存储引擎: Myslq目前比较常用的两个数据库存储引擎分别是MyISAM与InnoDB,MyISAM与InnoDB的主要的不同点在于性能和事务控制上。这里简单的介绍一下两者间的区别: MyISAM是...

mysql存储引擎InnoDB插入数据的过程详解

当插入,更新,删除等sql语句运行后,mysql为何总能高效,快速的执行返回,而且不管是断电,mysql进程崩溃,还是一些其它非正常因素,mysql总能保持数据完整, 本文将带着这些问题探秘mysql...

数据存储引擎 InnoDB, MyISAM

http://www.oschina.net/p/innodb http://baike.baidu.com/view/1238935.htm 二者mysql都可以使用! ...

《MySQL技术内幕 InnoDB存储引擎》一书中用于查看数据页内容的python小工具

工具包中用到的三个文件内容如下: include.py #encoding=utf-8 INNODB_PAGE_SIZE=1024*16 # InnoDB Page 16K # Start of...

|- mysql -| MySQL中数据存储引擎MyISAM和InnoDB

今天撸数据库代码时,刚开始使用那会并没有注意到自己使用数据库时的存储类型(我的MySQL默认是使用类型是MyISAM ),所以当我在设置外键的级联更新的时候,突然发现数据并不能进行级联更新操作,刚开始...
  • QAC_Boy
  • QAC_Boy
  • 2017年07月07日 17:49
  • 121

mysql数据据存储引擎InnoDB和MyISAM的优势及区别

MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与...
  • Hsuxu
  • Hsuxu
  • 2012年09月06日 20:33
  • 696

MySQL更改数据存储引擎,禁用innodb

INNODB是MySQL被ORACLE收购后开发的,支持事务和行级锁等高级功能,但是并不是所有人都需要INNODB的,对大部分人来说,以前的MYISAM引擎就够了,一般会选择将默认引擎改为MYISAM...

MySQL技术内幕:InnoDB存储引擎.pdf

  • 2017年12月02日 16:26
  • 47.78MB
  • 下载

mysql数据库-MyISAM与InnoDB两个存储引擎的索引实现方式

1.MyISAM索引实现:主键索引:MyISAM数据存储文件和索引文件是分开的。MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:php innodb存储引擎实现分区存储数据
举报原因:
原因补充:

(最多只允许输入30个字)