MySQL 5.1 分区技术初探(二)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

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,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
NoSQL 算法 关系型数据库
redis与mysql的数据一致性问题( 网络分区)
redis与mysql的数据一致性问题( 网络分区)
14 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL中的WAL技术
MySQL中的WAL技术
|
1月前
|
人工智能 关系型数据库 MySQL
一键实现穿衣自由|揭秘淘宝AI试衣间硬核技术:AnalyticDB MySQL向量在线召回
在AI试衣间功能的背后,阿里云瑶池旗下的云原生数据仓库AnalyticDB MySQL提供了高维向量低延时的在线向量召回检索服务,下面将进行介绍。
|
1月前
|
存储 SQL 关系型数据库
二十、MySQL技术体系之InnoDB存储引擎的索引
二十、MySQL技术体系之InnoDB存储引擎的索引
46 0
|
2月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版并行查询技术探索与实践
PolarDB MySQL版并行查询技术探索与实践 PolarDB MySQL版在企业级查询加速特性上进行了深度技术探索,其中并行查询作为其重要组成部分,已经在线稳定运行多年,持续演进。本文将详细介绍并行查询的背景、挑战、方案、特性以及实践。
88 2
|
2月前
|
SQL 分布式计算 数据库
【大数据技术Spark】Spark SQL操作Dataframe、读写MySQL、Hive数据库实战(附源码)
【大数据技术Spark】Spark SQL操作Dataframe、读写MySQL、Hive数据库实战(附源码)
52 0
|
2月前
|
SQL 算法 关系型数据库
mysql集群分区
mysql集群分区
14 0
|
2月前
|
人工智能 监控 物联网
基于物联网、大数据、云计算、人工智能等技术的智慧工地源码(Java+Spring Cloud +UniApp +MySql)
基于物联网、大数据、云计算、人工智能等技术的智慧工地源码(Java+Spring Cloud +UniApp +MySql)
398 0
|
2月前
|
存储 分布式计算 关系型数据库
云原生数据仓库AnalyticDB MySQL湖仓版架构升级,持续释放技术红利!
云原生数据仓库AnalyticDB MySQL湖仓版架降价23%!持续提供高性价比的产品服务
|
2月前
|
存储 SQL 关系型数据库
MySQL5.7到8.0的升级迁移!高效的MySQL跨版本迁移技术解读
NineData是一种高效、稳定且易于使用的迁移工具,可帮助用户将MySQL 5.7迁移到MySQL 8.0。由于MySQL 5.7已经结束生命周期并不再获得技术支持,因此迁移至MySQL 8.0是必要的。NineData提供了数据复制功能,包括数据迁移和实时同步,还支持双向复制。与传统迁移方法相比,NineData具有简单易用、数据一致、强劲性能和高可靠性的优势。它提供了完善的观测和干预能力,保障迁移的成功。此外,NineData还提供了对比功能,确保数据的一致性。通过简单的配置过程,用户可以实现自动化的数据迁移。NineData还提供了完善的观测和干预能力,帮助用户追踪迁移进展并诊断和修复
267 3