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

2023-05-16

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;

结果如下图：

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

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

接口签名实现拦截的两种方式

1 采用spring的aop思想进行拦截需要自定义注解 xff0c 然后定义切面 xff08 五大类 xff09 然后在定义 xff0c 可以获取所有的参数 2 拦截器的实现方式自定义拦截器 xff0c 然后对拦截器进行配置即可配置
Java程序员利器，lombok神搭档：delombok插件

Lombok是一款非常实用Java工具 xff0c 它可以帮助开发人员减少样板代码 xff0c 使开发人员专注业务逻辑 xff0c 在Java界几乎无人不知但也有一些明显的缺点 xff0c 例如 xff1a 对插件强依赖 xff0c 在团
C++bind函数

1 基本概念 bind函数定义在头文件 functional 中可以将 bind 函数看作一个通用的函数适配器 xff0c 它接受一个可调用对象 xff0c 生成一个新的可调用对象来适应原对象的参数列表 C 43 43 Primer
C++值的分类 —— 摘自维基百科

在C 43 43 11 xff0c 对于值的分类 xff0c 要考虑标识 xff08 identity xff09 与可移动性 xff08 movability xff09 xff0c 二者的组合产生了五种分类 xff1a 基础值类型左值
pytorch 深度学习入门代码（一）线性回归代码实现

34 34 34 一维线性回归代码实现 34 34 34 import torch from torch autograd import Variable import matplotlib pyplot as plt import tor
pytorch 深度学习入门代码（三）Logistic 回归代码实现

span class hljs string 34 34 34 Logistic 回归的代码实现 34 34 34 span span class hljs keyword import span matplotlib pyplot spa
pytorch 深度学习入门代码（四）多层全连接神经网络实现 MNIST 手写数字分类

net py span class hljs keyword import span torch nn span class hljs keyword as span nn span class hljs class span class
CentOs云服务器部署项目全流程

目录序工具准备putty安装及使用pscp安装及使用环境安装及配置serverjre 或jdk 安装及配置mysql安装及配置Tomcat 安装及配置项目部署上传war包至服务器tomcat无法启动常见问题去除端口号和目录名访问项目项
快速上手MybatisPlus

首先附上mybatis plus官方文档本篇参考官方文档记录spring mvc项目接入mybatis plus的全流程及一些问题的解决方案 xff0c 建议优先参考官方文档开始之前 xff0c 假设数据库已建好并已能正常访问依赖配置
FTP工具类一

public class FTPClientUtils public static String FTPCONFIG 61 34 config ftpConfig properties 34 private static String LO
在 SourceTree 中使用 git rebase （变基）

原始状态假如我们要在 master 分支上进行开发 xff0c 在远端的 master 分支上右键 xff0c 检出一个自己的开发分支 dev 1 做一些开发 xff0c 提交到本地 xff0c 不要推送 xff08 push xff0
云服务器搭建部署全流程

本篇记录在centos7 3上部署web项目的全流程及一些问题的解决方案工具准备 putty安装及使用 PuTTY可用来在windows上连接linux服务器 xff0c 可去PuTTY官网下载安装如果不想每次登录都输入密码 xff0c
三种获取字节码对象的方式及区别

方式一 xff1a 对象 getClass 方法是根对象Object的方法是其他类继承Object的getClass方法方式二 xff1a 类名 class xff0c 你可以理解为字节码本身就是静态的 xff0c 类加载的时字节码就
关于接口与Object 类的关系

看到这个标题 xff0c 你或许就会想好自己的那份答案但事实上这个确实没有答案 xff0c 至少没官方证明它们之间的基友关系看法一 xff1a 因为老师说 xff0c 你可以把接口看作是特殊的类 xff0c 所以不假思索的就认为接口也
单词博弈Java实现(借鉴“miss若尘”博客中写的解题思路)

单词博弈Java实现 xff0c 已经通过庞果网的用例测试代码如下 import java util HashMap public class WordGameFinalTest public static int who String
mysql安装时的粗心错误：last error unable to update security settings. access denied for user 'root' @ 'localh

来自梦想家haima 39 s blog gt http blog dreamwe cn 这个报错出现在mysql最后当你看到mysql的最后一步需要设置密码可能你开心得很 Mysql就快安装好了赶快输入三行密码都是root 结果报下
@SuppressWarnings

简介 xff1a java lang SuppressWarnings是J2SE 5 0中标准的Annotation之一可以标注在类字段方法参数构造方法 xff0c 以及局部变量上作用 xff1a 告诉编译器忽略指定的警告 xf
欢迎使用CSDN-markdown编辑器

欢迎使用Markdown编辑器写博客本Markdown编辑器使用StackEdit修改而来 xff0c 用它写博客 xff0c 将会带来全新的体验哦 xff1a Markdown和扩展Markdown简洁的语法代码块高亮图片链接和图片上传
Linux之强大的awk

来自梦想家 Haima s blog gt http blog dreamwe cn awk简介 awk是Linux中的一个命令 xff0c 用来做文本处理与分析 xff0c 功能简单强悍 xff0c 同时它也是一门编程语言 awk处理文
手机抓包charles使用

使用的是charles window 之前使过fiddler但是感觉并没有charles好用以及一目了然链接 https pan baidu com s 1NMNXa8M4niLObQKIsCNL3A 提取码 2wsa 安装包可以通过连接

随机推荐

安卓Tinker热更新接入踩坑（minSdkVersion 21）

哎哟 xff0c 这个坑啊我项目采用的是ARouter 43 Tinker 我接入的是tinkerpathchttp www tinkerpatch com Docs intro按照文档对接 xff0c 我采用的是reflectAppli
Android 解压和重新打包system.img

开始我们的工作前 xff0c 请记住 xff0c Linux一定要学会用file命令分析文件类型 xff0c 这样才好入手 xff0c 否则错了都不知道怎么回事 xff01 xff01 xff01 1 解压system img 先用file
Jetpack系列学习笔记整理一之LifeCycles

学习最好的途径就是官网和github Demo xff0c 先放链接 xff0c 想看自行跳转 xff1a 官网 xff1a https developer android com topic libraries architecture
获取安卓位置信息

别忘了添加权限 xff1a lt uses permission android name 61 34 android permission INTERNET 34 gt lt uses permission android name 61
安卓与html混合开发之原生与js相互调用

原生和html的优缺点就不多说了 xff0c 有些特定条件下用html页面可以很方便 xff0c 也很容易更新和维护 xff0c 那么这就涉及到html与安卓原生的交互和通信接下来我要分享的是html调用原生的弹窗和位置信息 xff0c
应用保活--杀死进程也能收到推送消息

我选取的是极光推送 xff0c 当把进程杀死时候就接受不到推送过来的消息这是因为我使用的是小米手机 xff0c 小米和华为手机属于那种深度定制安卓系统 xff0c 需要用户的操作才能够实现应用保活的目的小米 MIUI 自启动管理 x
安卓原生与vue前段相互调用

之前写过一个博客是安卓原生与JS交互的博客 xff1a http blog csdn net jhl122 article details 53406623 那是正常情况下的交互 xff0c 但是如果前段人员使用vue开发就会产生一个问题
CMake 编译时报错 ninja: error: ......missing and no known rule to make it

Build command failed Error while executing process F Android sdk cmake 3 6 4111459 bin cmake exe with arguments build E
数据类型和Json格式

1 前几天 xff0c 我才知道有一种简化的数据交换格式 xff0c 叫做yaml 我翻了一遍它的文档 xff0c 看懂的地方不多 xff0c 但是有一句话令我茅塞顿开它说 xff0c 从结构上看 xff0c 所有的数据最终都可以分成三种
golang语言rsa加解密及签名验签

golang语言rsa加解密及签名验签 96 rsa 96 算法概述 96 Rsa 96 结构体封装封装的优点使用案例 rsa算法概述 rsa是一种非对称的可逆的加密算法 xff0c 对加密数据长度有限制 xff0c 同时rsa也提供了数
异常与错误处理

异常与错误处理 PHP的异常与错误是分开的 xff0c 当程序出现异常时会throw一个 Exception 或子类对象 xff0c 但是当出现错误时会触发一个错误 1 异常处理 1 1 通过try catch主动处理异常 span cl
设计模式

1 什么是模式设计模式是对某些典型易变问题的特定解决方案 xff0c 这些问题和解决方案经过分类总结 xff0c 并且为了方便交流给每个解决方案都起了特定的名字模式是为了解决变化的问题 xff0c 将变化的问题进行封装 xff0c 让变
最新完美解决Python第三方库安装出现Microsoft Visual C++ 14.0 is required的问题

安装库出现报错 xff1a Microsoft Visual 14 0 or greater is required 怎么办 xff1f 使用Python下载第三方库 xff0c pip也更新了 xff0c 镜像也使用了 xff0c 网络也
pdo-mysql

pdo mysql PHP连接数据库推荐使用PDO xff0c PDO扩展为PHP访问数据库定义了一个轻量级接口我们可以通过实现PDO接口的每个数据库驱动来访问数据库服务访问mysql数据库服务 xff0c 我们使用PDO MYSQL驱
db封装

db封装以下Connection类封装支持以下几个特性 1 参数绑定防止sql注入2 读写分离3 多主多从 xff0c 多节点负载均衡4 故障自动摘除及自动恢复代码实现 span class token delimiter import
数据结构

1 数据结构提到算法不能不提数据结构 xff0c 数据结构就是数据元素按照一种或多种关系的集合 xff0c 按照逻辑结构划分 xff0c 可以分为 xff1a 1 1 集合集合是由一堆无序的相关联的 xff0c 且不重复的数据元素组成
数据切分——Atlas介绍

Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目它在MySQL官方推出的MySQL Proxy 0 8 2版本的基础上 xff0c 修改了大量bug xff0c 添加了很多功能
Nginx负载均衡：分布式/热备Web Server的搭建

Nginx是一款轻量级的Web 服务器反向代理服务器及电子邮件 xff08 IMAP POP3 xff09 代理服务器 xff0c 并在一个BSD like 协议下发行由俄罗斯的程序设计师Igor Sysoev所开发 xff0c 供俄
数据切分——Atlas读写分离Mysql集群的搭建

关于数据切分的原理可以参见博客 xff1a http blog csdn net jhq0113 article details 44226789 关于Atlas的介绍可以参见博客 xff1a http blog csdn net jhq0
数据切分——Mysql分区表的建立及性能分析

Mysql的安装方法可以参考 xff1a http blog csdn net jhq0113 article details 43812895 Mysql分区表的介绍可以参考 xff1a http blog csdn net jhq011

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

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

随机推荐

热门标签

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