数据切分——Mysql分区表的建立及性能分析

Mysql的安装方法可以参考：

http://blog.csdn.net/jhq0113/article/details/43812895

Mysql分区表的介绍可以参考：

http://blog.csdn.net/jhq0113/article/details/44592865

1.检查你的Mysql是否支持分区

mysql> SHOW VARIABLES LIKE '%partition%';

若结果如下，表示你的Mysql支持表分区:

+-----------------------+-------+

       | Variable_name         | Value |

       +-----------------------+-------+

       | have_partition_engine | YES   |

       +-----------------------+-------+

       1 row in set (0.00 sec)

RANGE分区表创建方式：

DROP TABLE IF EXISTS `my_orders`;
CREATE TABLE `my_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `pid` int(10) unsigned NOT NULL COMMENT '产品ID',
  `price` decimal(15,2) NOT NULL COMMENT '单价',
  `num` int(11) NOT NULL COMMENT '购买数量',
  `uid` int(10) unsigned NOT NULL COMMENT '客户ID',
  `atime` datetime NOT NULL COMMENT '下单时间',
  `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',
  `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*********分区信息**************/
PARTITION BY RANGE (YEAR(atime))
(
   PARTITION p0 VALUES LESS THAN (2016),
   PARTITION p1 VALUES LESS THAN (2017),
   PARTITION p2 VALUES LESS THAN MAXVALUE
);

以上是一个简单的订单表，分区字段是atime,根据RANGE分区，这样当你向该表中插入数据的时候，Mysql会根据YEAR(atime)的值进行分区存储。

检查分区是否创建成功，执行查询语句：

EXPLAIN PARTITIONS SELECT * FROM `my_orders`

若成功，结果如下：

性能分析：

1）.创建同样表结构，但没有进行分区的表

DROP TABLE IF EXISTS `my_order`;
CREATE TABLE `my_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `pid` int(10) unsigned NOT NULL COMMENT '产品ID',
  `price` decimal(15,2) NOT NULL COMMENT '单价',
  `num` int(11) NOT NULL COMMENT '购买数量',
  `uid` int(10) unsigned NOT NULL COMMENT '客户ID',
  `atime` datetime NOT NULL COMMENT '下单时间',
  `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',
  `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',
  PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2）.向两张表中插入相同的数据

/**************************向分区表插入数据****************************/
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');

/**************************向未分区表插入数据****************************/
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');

3）.主从复制，大约20万条左右（主从复制的数据和真实环境有差距，但是能体现出表分区查询的性能优劣）

/**********************************主从复制大量数据******************************/
INSERT INTO `my_orders`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_orders`;
INSERT INTO `my_order`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_order`;

4）.查询测试

/***************************查询性能分析**************************************/
SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
/****用时0.084s****/

SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
/****用时0.284s****/

通过以上查询可以明显看出进行表分区的查询性能更好，查询所花费的时间更短。

分析查询过程：

EXPLAIN PARTITIONS SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

EXPLAIN PARTITIONS SELECT * FROM `my_order` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();

通过以上结果可以看出，my_orders表查询直接经过p0分区，只扫描了49386行，而my_order表没有进行分区，扫描了196983行，这也是性能得到提升的关键所在。

当然，表的分区并不是分的越多越好，当表的分区太多时找分区又是一个性能的瓶颈了，建议在200个分区以内。

LIST分区表创建方式：

/*****************创建分区表*********************/
CREATE TABLE `products` (
`id`  bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '表主键' ,
`name`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称' ,
`metrial`  tinyint UNSIGNED NOT NULL COMMENT '材质' ,
`weight`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '重量' ,
`vol`  double UNSIGNED NOT NULL DEFAULT 0 COMMENT '容积' ,
`c_id`  tinyint UNSIGNED NOT NULL COMMENT '供货公司ID' ,
PRIMARY KEY (`id`,`c_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

/*********分区信息**************/
PARTITION BY LIST(c_id)
(
    PARTITION pA VALUES IN (1,3,11,13),
    PARTITION pB VALUES IN (2,4,12,14),
    PARTITION pC VALUES IN (5,7,15,17),
    PARTITION pD VALUES IN (6,8,16,18),
    PARTITION pE VALUES IN (9,10,19,20)
);

可以看出，LIST分区和RANGE分区很类似，这里就不做性能分析了，和RANGE很类似。

HASH分区表的创建方式：

/*****************分区表*****************/
CREATE TABLE `msgs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',
  `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',
  `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',
  `msg` varchar(225) NOT NULL COMMENT '消息内容',
  `atime` int(10) unsigned NOT NULL COMMENT '发送时间',
  `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*********分区信息**************/
PARTITION BY HASH(sub_id)
PARTITIONS 10;

以上语句代表，msgs表按照sub_id进行HASH分区，一共分了十个区。

Key分区和HASH分区很类似，不再介绍，若想了解可以参考Mysql官方文档进行详细了解。

子分区的创建方式:

CREATE TABLE `msgss` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `sender` int(10) unsigned NOT NULL COMMENT '发送者ID',
  `reciver` int(10) unsigned NOT NULL COMMENT '接收者ID',
  `msg_type` tinyint(3) unsigned NOT NULL COMMENT '消息类型',
  `msg` varchar(225) NOT NULL COMMENT '消息内容',
  `atime` int(10) unsigned NOT NULL COMMENT '发送时间',
  `sub_id` tinyint(3) unsigned NOT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`,`atime`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*********分区信息**************/
PARTITION BY RANGE (atime) SUBPARTITION BY HASH (sub_id)
(
		PARTITION t0 VALUES LESS THAN(1451577600)
		(
			SUBPARTITION s0,
			SUBPARTITION s1,
			SUBPARTITION s2,
			SUBPARTITION s3,
			SUBPARTITION s4,
			SUBPARTITION s5
		),
		PARTITION t1 VALUES LESS THAN(1483200000)
		(
			SUBPARTITION s6,
			SUBPARTITION s7,
			SUBPARTITION s8,
			SUBPARTITION s9,
			SUBPARTITION s10,
			SUBPARTITION s11
		),
		PARTITION t2 VALUES LESS THAN MAXVALUE
		(
			SUBPARTITION s12,
			SUBPARTITION s13,
			SUBPARTITION s14,
			SUBPARTITION s15,
			SUBPARTITION s16,
			SUBPARTITION s17
		)
);

检查子分区是否创建成功：

EXPLAIN PARTITIONS SELECT * FROM msgss;

结果如下图：

时间： 2024-08-19 18:45:20

数据切分——Mysql分区表的建立及性能分析的相关文章

数据切分——Mysql分区表的管理与维护

关于Mysql分区表的介绍可以参考: http://blog.csdn.net/jhq0113/article/details/44592865 关于Mysql分区表的创建可以参考: http://blog.csdn.net/jhq0113/article/details/44593511 前面已经提过,Mysql支持4种表的分区,即RANGE与LIST.HASH与KEY,其中RANGE和LIST类似,按一种区间进行分区,HA

Mysql Join语法解析与性能分析

原文:Mysql Join语法解析与性能分析一．Join语法概述 join 用于多表中字段之间的联系,语法如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表:table2:右表. JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录. LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录

深入分析MySQL索引结构原理、性能分析与优化详解

第一部分:基础知识: 索引官方介绍索引是帮助MySQL高效获取数据的数据结构.笔者理解索引相当于一本书的目录,通过目录就知道要的资料在哪里,不用一页一页查阅找出需要的资料.关键字index --------------------- 唯一索引强调唯一,就是索引值必须唯一,关键字unique index 创建索引: 1.create unique index 索引名 on 表名(列名); 2.alter table 表名 add unique index 索引名 (列名); 删除索引: 1.

.NET Visual Studio 代码性能分析工具_实用技巧

下面通过图文并茂的方式给大家介绍下,具体内容如下: 软件开发中的性能优化对程序员来说是一个非常重要的问题.一个小问题可能成为一个大的系统的瓶颈.但是对于程序员来说,通过自身去优化代码是十分困难的.幸运的是,有一些非常棒的工具可以帮助程序员进行代码分析和性能测试,从而大大简化程序员进行代码性能优化的过程.MSDN杂志2011年7月份曾发布主题为".NET代码分析工具和技术"的那一期,让广大程序员收获颇丰.四年过去之后,这些工具又进一步做出了很多改进,同时也出现了更多的选择.本文对当前主流

数据切分——Atlas读写分离Mysql集群的搭建

关于数据切分的原理可以参见博客: http://blog.csdn.net/jhq0113/article/details/44226789 关于Atlas的介绍可以参见博客: http://blog.csdn.net/jhq0113/article/details/44239823 Atlas源代码用C语言编写,它对于Web Server相当于是DB,相对于DB相当于是Client,

php导入大量数据到mysql性能优化技巧_php技巧

本文实例讲述了php导入大量数据到mysql性能优化技巧.分享给大家供大家参考.具体分析如下: 在mysql中我们结合php把一些文件导入到mysql中,这里就来分享一下我对15000条记录进行导入时分析与优化,需要的朋友可以参考一下. 之前有几篇文章,说了最近tiandi在帮朋友做一个小项目,用于统计电话号码的,每次按需求从数据库里随机生成打包的电话号码,然后不停地让人打这些电话号码推销产品(小小鄙视一下这样的行为).但是朋友要求帮忙,咱也不能不帮啊,是吧.程序两个星期前已经做好,测试完毕交工

mysql 随机查询数据与性能分析

比如有一个需求,通过sql语句,返回-5至5的随机整数.如果这一个放在PHP中,则非常简单直接用 <?php print rand(-5,5); ?> 在mysql中,rand函数只能有一个参数. //摘自手册 RAND() RAND(N) 返回一个随机浮点值 v ,范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0).若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列. 有两个方法可以达成以上效果. 1.新建一个表,里面存着 -5 至 5 之间的数.再利用ord

可扩展性设计之数据切分

前言通过MySQLReplication功能所实现的扩展总是会受到数据库大小的限制,一旦数据库过于庞大,尤其是当写入过于频繁,很难由一台主机支撑的时候,我们还是会面临到扩展瓶颈.这时候,我们就必须许找其他技术手段来解决这个瓶颈,那就是我们这一章所要介绍恶的数据切分技术. 何谓数据切分可能很多读者朋友在网上或者杂志上面都已经多次见到关于数据切分的相关文章了,只不过在有些文章中称之为数据的Sharding.其实不管是称之为数据的Sharding还是数据的切分,其概念都是一样的.简单来说,就是指通

MYSQL教程：建立加密连接

加密连接可提高数据的安全性,但会降低性能.要进行加密连接,必须满足以下要求: user权限表里要有相关的SSL数据列.如果安装的MySQL服务器是4.0.0版的,user权限表已包含相关的SSL数据列,否则,我们也可用mysql_fix_privilege_tables脚本升级权限表. 服务器和客户程序都已经编译有OpenSSL支持.首先要安装openssl,在编译时MySQL服务器时加--with-vio和--with-openssl选项加上openssl支持.可用以下语句查询服务器是否支持S