数据切分——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分区表的建立及性能分析的相关文章

ADRC学习|TD微分跟踪器(原理解析和Matlab实现)

系列文章目录 TD微分跟踪器原理解析和Matlab实现状态扩张观测器目录系列文章目录前言微分跟踪器作用原理线性TD非线性TDTD的一个定理最速离散控制函数 Matlab实现参考文献前言学习韩老师的ADRC xff0c 把学习过程
ROS踩坑|warning：clock skew detected. Your build may be incomplete

报错展示 warning xff1a clock skew detected Your build may be incomplete 问题分析 ROS编译时遇到这个问题95 是因为系统时钟错误了 xff0c 比如说你之前的编译的时间时20
ros学习|功能包相关|查找、安装、卸载删除某个功能包

在运行别人的demo时 xff0c 是不是会出现找不到功能包的情况所以查找安装和卸载某个功能包是蛮重要的查找查询当前安装完成的所有包文件 rospack list 查询ros的所有功能包 span class token funct
Vector数组类型在ROS开发中的用法小结

目录前言数组类型在不同消息类型中的定义在ROS消息中的定义在C 43 43 中定义数组 vector数组的一些常用操作基本操作求数组的最值遍历数组排序数组查找数组注意前言 ROS系统的一个显著优势就是分布式和灵活性 xff0c 用户可以
Java 核心技术卷一随笔

Java 易混 xff0c 重难点汇总 1 基础篇整型浮点 final 一般用于指示常量 xff0c 即变量只能被赋值一次一旦被赋值 xff0c 就不能更改了 xff08 即没有set方法 xff09 命名规则一般使用全大写及下划线
Linux 面试最常问的十个问题

如果你要去面试一个Linux系统运维工程师的职位 xff0c 下面这十个最常见的问题一定要会 xff0c 否则你的面试可能就危险了这些都是比较基本的问题 xff0c 大家要理解 xff0c 不能光死记硬背 1 如何查看系统内核的版本这里
处理iOS开发中的各种警告

iOS开发中我们会遇到各种警告包括第三方不再支持更新导致的警告苹果一些过时方法的警告其中尤其是ASIHttpRequest的不支持更新导致有很多的警告所以我在这里做了罗列 Semantic Warnings WarningMessa
c#利用虚拟串口工具进行串口通信数据的发送和接收

原文 xff1a 我的个人博客串口通信串口通信 xff08 Serial Communications xff09 的概念非常简单 xff0c 串口按位 xff08 bit xff09 发送和接收字节尽管比按字节 xff08 byte
stm32 移植 FreeRTOS

1 建立裸机工程在移植 freeRTOS 之前需要创建一个 stm32f103 的 keil5 裸机工程所谓裸机工程就是包含 stm32f103 单片机的启动文件 xff0c 外设驱动固件库的文件集合 xff0c 也可以说是支持裸机开发
ESP32(Espressif-IDE)添加外部(.c .h)文件（error：undefined reference to）

0x00 xff1a 前言笔记文件用习惯了keil和iar之类的开发软件 xff0c 一下子转换到ESP32是特别的不习惯 xff0c 这里使用的开发环境是乐鑫官方推出的Espressif IDE xff0c 也是刚学折腾ESP32 x
无人机地面站源码搭建环境（qt版）记录

最近基于项目需要 xff0c 借鉴无名创新的QT版开源地面站 xff0c 搭建一款测试平台原本以为比较简单的但是在搭建过程中还是遇到了很多看不见的坑 xff0c 在这里分享给大家 xff0c 避免别人在开发类似项目时像我这样走弯路基本
如何才能有论文创新点？idear

1 看别人是怎么解决问题的 xff0c 别人写的每一篇论文都是在解决问题 2 看自己领域综述性论文 xff0c 对某一问题都是一步步发展出来的 xff0c 某一方法也是一步一步更新完善的 xff0c 这些解决的都是问题 3 看一篇论文的引言
前端图片裁剪cropper.js

背景 xff1a 我们在项目中常常都会有更换登陆人头像的需求 xff0c 这个很简单直接上传一张图片展示就可以了 xff0c 但是如果上传的图片需要剪裁成正方形的话就需要我们来做一些特殊的处理 xff1b 这里我们会用到一个图片剪裁插件 x
新产品开发之C流程（C-flow）

关于新产品开发的C流程 C flow xff0c 是世界上大公司采用的标准开发流程 xff0c 十分基本也十分重要 xff0c 但是网上关于C流程介绍相关的资料很少 xff0c 所以花点时间整理一下相关的资料下面以软件BSP开发为例 xf
Arduino的Stepper库函数及其控制步进电机（ULN2003）调速

问题来源最近自学Arduino xff0c 在使用步进电机时开始没能使步进电机转起来 xff0c 转起来后感觉没法调速 xff0c 遂完成此篇笔记供自己后续查阅以及方便遇到相同问题的诸君寻找灵感对于如何使步进电机转动就不详述 xff0c
一、电脑端实现单片机与ESP8266的通信

1 准备工具 xff08 硬件软件 xff09 硬件 xff1a 51单片机开发板 ESP8266无线模块 xff08 ESP8266 01 xff09 TTL USB串口杜邦线数据线 xff1b 软件 xff1a keil uv4单
错误 LNK2038 检测到“_ITERATOR_DEBUG_LEVEL”的不匹配项: 值“2”不匹配值“0”(main.obj 中)

前言 vs2019报错如下图 xff1a 错误 LNK2038 检测到 ITERATOR DEBUG LEVEL 的不匹配项值 2 不匹配值 0 main obj 中错误原因 1 产生这个问题的原因是当前工程是Debug版本 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

随机推荐

我的2013，梦在路上

我的2013 xff0c 在路上今年最后一次给姐姐打电话 xff0c 她在那里像我炫耀自己和爸爸妈妈一起跨年 xff0c 说1314的意义 xff0c 而我还在北京苦逼着回想2013年对于我来说 xff0c 或许是不错的一年这一年我进
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处理文
ucos源码分析（一）

时间 2018 01 27 本人目前是大三学生电子信息工程专业 xff0c 在大学前俩年的时间 xff0c 一直在使用和学习单片机 xff0c 不过也仅仅是从 xff15 xff11 到 xff13 xff12 xff0c 马上要面临就业
随笔——Python & C++ 混用——使用cmake生成c++动态库

Python amp C 43 43 混用 xff0c 使用cmake 生成c 43 43 动态库 xff0c 可供python调用说明 xff1a test cpp为源文件 add h为头文件源文件使用第三方库opencv CMake
机会都是留给有准备的人，你在准备什么？| 每天成就更大成功

最近养成一个习惯 xff0c 就是每晚抄书 xff0c 大概200字左右 xff0c 在抄书的过程中反思了一个问题 xff1a 因为字写的不漂亮 xff0c 于是就在抄书的时候就有意的去练字 xff0c 这样抄书的精力就被分散了一部分出去
《SQL Server 2005开发技术大全》分享一本书

数据库是一个非常重要的领域 xff0c 不管是什么样的系统 xff0c 都必须要与数据库打交道 xff0c 因此作为一个程序员来讲 xff0c 数据库的基本知识技能是必须要掌握的我接触C 已经有两年半的时间了吧 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 让变
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
k8s 官方文档

k8s 官方文档 https kubernetes io zh docs home
数据切分——Mysql分区表的建立及性能分析

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

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

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

随机推荐

热门标签

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