MySQL 5.1 分区技术初探(二)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

MySQL分区技术是用来减轻海量数据带来的负担,解决数据库性能下降问题的一种方式,其他的方式还有建立索引,大表拆小表等等。

  MySQL分区按照分区的参考方式来分有RANGE分区、LIST分区、HASH分区、KEY分区。本文对这几种分区方式进行了详细的介绍,并且给出了简单的示例,文章简洁明了,对于想要初步了解MySQL分区技术的同学来说是很不错的参考材料。


三、案例分析

  这个案例是针对有个员工、部门、部门经理、头衔和销售记录的模拟数据,其ER图如下所示,数据量大概有4百万左右。数据下载URLhttps://launchpad.net/test-db

  

 

11,案例分析

  通过如上可知,对于同样的数据按照分区和不分区的技术分别存储,从而便于如下的查询性能分析和对比。对于salaries表,它采用RANGE分区,定义如下:

  

 

12,案例分析

  1,单表查询

  从销售记录中找到1999年整年的销售记录有多少条,这个很简单,查询语句如下:

  select count(*) from salaries s where s.from_date between"1999-01-01" and "1999-12-31" ;

  那么对于分区前后的查询性能却有很大的差别:

  

 

13,分区前后查询性能对比

  通过如上可知,利用分区之后它只需扫描p16分区,访问的记录明显减少,所以性能自然有较大的提升:



 

14,无采用分区技术和采用分区技术性能对比

  2,单表查询的badcase

  若现在有如下查询:

  select count(*) from salaries s where year(s.from_date)=1999;

  那么它是否能够利用到分区技术呢,答案是否定的。为什么呢,因为分区中的keys.from_date,而不是year(s.from_date)mysql并不能很智能地判断year1999的,那么它就是分为p16分区,这个可以通过如下的查询计划可以证实:

  

 

15,未优化前的单表查询

  也就是其实它访问了所有的分区,所以并没有很好地利用分区功能,将SQL改写如下:

  select count(*) from salaries s where year between '1999-01-01' and'1999-12-31' ;

  则查询计划如下:


 

16,改进后的单表查询

  可知,书写正确的SQL可以完全表现出两种相差特别大的性能。

  3,连接查询

  同样地,对于连接查询,在有没有分区的条件下,将有性能3倍左右的差距。对于更大的数据量,可能会有更大的性能差距。SQL如下:

  select count(*) from salaries s left join employees e ons.emp_no=e.emp_no where s.from_date between '1999-01-01' and '1999-12-31' ;

  

 

17,无采用分区和采用分区的性能对比

  4,删除查询

  为了删除1998年的销售数据,那么在有分区情况下可以不利用delete查询快速地完成垃圾数据的清理。


 

18,删除查询性能对比

  可知,对于有分区的情况下,只需要将某个分区删除掉即可,时间仅为0.05s,相对应原来的2.82s,这个提升是非常高的。当然,利用分区功能删除之后的数据文件信息如下:


 

19,利用分区功能删除后的文件信息

  那么接下来如果接着插入1998年的数据,数据是否丢失了呢?还是会写不进去?答案也都是否定,它会将数据写入p16分区中。有兴趣的读者可以自己收到试试。

 

四、总结和不足

  分区的好处有很多

  1,与单个磁盘或文件系统分区相比,可以存储更多的数据;

  2,对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据;

  3,一些查询可以得到极大的优化,如where语句数据可以只保存在一个或多个分区内;

  4,涉及到例如SUM()COUNT()这样聚合函数的查询,可以很容易地进行并行处理;

  5,通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

  在设计分区过程中,需要考虑的因素有很多,如:

  1,分区的列;

  2,分区使用的函数,特别是非Integer类型的列;

  3,服务器性能;

  4,内存大小。

  根据分区技术,有一些技巧:

  1,若索引的大小> RAM,考虑选用分区,不采用索引;

  2,尽量不采用Primary Key做分区的key

  3,当CPU性能高的时候,考虑使用Archive存储引擎;

  4,对于大量的历史数据,考虑使用Archive+PARTITION

  总之,

  1MySQL分区技术是一种逻辑的水平分表技术;

  2,它只访问需要访问的分区,从而提高性能;

  3,支持range, hash, key, list和复合分区方法;

  4,支持MySQL服务器所支持的任何存储引擎;

  5,除了Key分区方法,Partitionkey 必须是整数(或者能转化成整数)

 

 
















本文转自百度技术51CTO博客,原文链接:http://blog.51cto.com/baidutech/748627,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
监控 关系型数据库 MySQL
10亿数据如何最快速插入MySQL:技术干货分享
【8月更文挑战第2天】在大数据时代,处理并快速插入数十亿条数据到MySQL数据库是许多企业面临的关键挑战。本文将深入分享一系列高效的技术策略和实战经验,帮助读者优化这一过程,确保数据能够快速、准确地进入数据库系统。
272 1
|
2月前
|
JavaScript 安全 Java
java版药品不良反应智能监测系统源码,采用SpringBoot、Vue、MySQL技术开发
基于B/S架构,采用Java、SpringBoot、Vue、MySQL等技术自主研发的ADR智能监测系统,适用于三甲医院,支持二次开发。该系统能自动监测全院患者药物不良反应,通过移动端和PC端实时反馈,提升用药安全。系统涵盖规则管理、监测报告、系统管理三大模块,确保精准、高效地处理ADR事件。
130 1
|
3月前
|
监控 前端开发 Java
【技术开发】接口管理平台要用什么技术栈?推荐:Java+Vue3+Docker+MySQL
该文档介绍了基于Java后端和Vue3前端构建的管理系统的技术栈及功能模块,涵盖管理后台的访问、登录、首页概览、API接口管理、接口权限设置、接口监控、计费管理、账号管理、应用管理、数据库配置、站点配置及管理员个人设置等内容,并提供了访问地址及操作指南。
|
3月前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
242 3
|
3月前
|
SQL 监控 关系型数据库
MySQL如何查看每个分区的数据量
通过本文的介绍,您可以使用MySQL的 `INFORMATION_SCHEMA`查询每个分区的数据量。了解分区数据量对数据库优化和管理具有重要意义,可以帮助您优化查询性能、平衡数据负载和监控数据库健康状况。希望本文对您在MySQL分区管理和性能优化方面有所帮助。
505 1
|
4月前
|
存储 关系型数据库 MySQL
MySQL 如何查看每个分区的数据量
MySQL 如何查看每个分区的数据量
178 3
|
4月前
|
XML 关系型数据库 MySQL
MySQL 导出某些数据的技术详解
MySQL 导出某些数据的技术详解
231 2
|
5月前
|
存储 关系型数据库 MySQL
技术解析:MySQL中取最新一条重复数据的方法
以上提供的两种方法都可以有效地从MySQL数据库中提取每个类别最新的重复数据。选择哪种方法取决于具体的使用场景和MySQL版本。子查询加分组的方法兼容性更好,适用于所有版本的MySQL;而窗口函数方法代码更简洁,执行效率可能更高,但需要MySQL 8.0及以上版本。在实际应用中,应根据数据量大小、查询性能需求以及MySQL版本等因素综合考虑,选择最合适的实现方案。
531 6
|
4月前
|
关系型数据库 MySQL 数据库
MySQL技术深度解析:每次最大插入条数探秘
MySQL技术深度解析:每次最大插入条数探秘
88 0
|
4月前
|
关系型数据库 MySQL 数据库管理
MySQL技术指南:如何更改数据字段的前几位数字
MySQL技术指南:如何更改数据字段的前几位数字
96 0