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 innodb存储引擎做成表分区

详细描述总结请看MySQL分区(一) 例子:该例子为本人个人学习总结分享->详细说明-->有问题欢迎前来交流...
  • leyangjun
  • leyangjun
  • 2014年07月28日 17:14
  • 997

MySQL存储结构MyISAM和InnoDB

MySQL两种表存储结构MyISAM和InnoDB的性能比较测试MyISAM 表。MyISAM 存储格式自版本 3.23 以来是 MySQL 中的缺省类型,它有下列特点:■ 如果操作系统自身允许更大的...
  • xzknet
  • xzknet
  • 2006年06月06日 23:07
  • 2583

Mysql技术内幕InnoDB存储引擎——InnoDB存储引擎

特此申明: 前段时间找工作所以看了《Mysql技术内幕InnoDB存储引擎》,整理的时候除了参考网上已有的笔记贴,加上自己整合的,可能和别人有雷同之处。不过无所谓啦,写出来自己看看,需要的朋友参...
  • yingminxing
  • yingminxing
  • 2012年12月07日 10:43
  • 5448

高性能Mysql-存储引擎(InnoDB)

MySQL将每个数据库(也可以称为schema)保存为数据目录下的一个子目录,创建表时,MySQL会在数据库子目录下创建一个和表同名的 .frm文件保存表的定义。(也因此MySQL数据库和表的定义对大...
  • Return_True_hang
  • Return_True_hang
  • 2017年03月05日 14:57
  • 350

InnoDB存储引擎B+树索引介绍

一、InnoDB索引概述: InnoDB存储引擎支持B+树索引、哈希索引、全文索引和空间索引,后两种很少用到,本文主要介绍B+树索引。 B+树是从最早的平衡二叉树(AVL)演变而来,但是B+树不是...
  • wuxing26jiayou
  • wuxing26jiayou
  • 2017年08月06日 16:27
  • 430

Mysql技术内幕InnoDB存储引擎——事务&备份&性能调优

事务       transaction是数据库区别于文件系统的重要特性之一,innodb引擎完全符合事务的ACID特性。       Atomicity 原子性       Consisten...
  • yingminxing
  • yingminxing
  • 2012年12月11日 13:26
  • 3720

MySQL技术内幕 InnoDB存储引擎(一)

第一章-MySQL体系结构和存储引擎 1定义数据库和实例 “数据库”(database)和“实例”(instance)是两个容易被混淆的概念。 其中,数据库是指:物理操作系统文件或其他形式文件...
  • u014071426
  • u014071426
  • 2016年12月06日 23:02
  • 336

《MySQL技术内幕——InnoDB存储引擎》读书笔记(一)——MySQL体系结构和存储引擎

一、定义数据库和数据库实例 数据库 (database): 物理操作系统文件或其他形式文件类型的集合 实例(instance) :  MySQL数据库由后台线程以及一个共享内存区组成,共享内存可以...
  • u011468424
  • u011468424
  • 2016年09月10日 19:31
  • 467

MYSQL内核:INNODB存储引擎 卷1pdf

下载地址:网盘下载 内容简介  · · · · · ·《MySQL内核:InnoDB存储引擎 卷1》由资深MySQL专家,机工畅销图书作者亲自执笔,在以往出版的两本InnoDB介绍性图书的基础之上,更...
  • cf406061841
  • cf406061841
  • 2017年09月01日 18:27
  • 340

MySql-InnoDB存储引擎的锁和事务

InnoDB存储引擎的关键特性:插入缓冲(性能提升)、两次写(可靠)、自适应哈希索引(查找效率)、异步IO(AIO,提高磁盘操作性能)、刷新邻接页(合并IO)。 InnoDB采用一致性的非锁定读和行级...
  • wuqinghai2012
  • wuqinghai2012
  • 2016年06月28日 21:56
  • 544
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:php innodb存储引擎实现分区存储数据
举报原因:
原因补充:

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