文档章节

mysql 按月分区

xuewenyu
 xuewenyu
发布于 2015/05/20 14:31
字数 1426
阅读 1008
收藏 0
点赞 0
评论 0

一、概述

当 MySQL的总记录数超过了100万后,会出现性能的大幅度下降吗?答案是肯定的,但是,性能下降>的比率不一而同,要看系统的架构、应用程序、还有>包括索引、服务器硬件等多种因素而定。当有网友问我这个问题的时候,我最常见的回答>就是:分表,可以根据id区间或者时间先后顺序等多种规则来分表。分表很容易,然而由此所带来的应用程序甚至是架构方面的改动工作却不>容小觑,还包括将来的扩展性等。

在以前,一种解决方案就是使用 MERGE
类型,这是一个非常方便的做饭。架构和程序基本上不用做改动,不过,它的缺点是显见的:

  • 只能在相同结构的 MyISAM 表上使用
  • 无法享受到 MyISAM 的全部功能,例如无法在 MERGE 类型上执行 FULLTEXT 搜索
  • 它需要使用更多的文件描述符
  • 读取索引更慢

这个时候,MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了:

  • 与单个磁盘或文件系统分区相比,可以存储更多的数据
  • 很容易就能删除不用或者过时的数据
  • 一些查询可以得到极大的优化
  • 涉及到 SUM()/COUNT() 等聚合函数时,可以并行进行
  • IO吞吐量更大

分区允许可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。

分区应该注意的事项:

1、 做分区时,要么不定义主键,要么把分区字段加入到主键中。

2、 分区字段不能为NULL,要不然怎么确定分区范围呢,所以尽量NOT NULL


二、分区的类型

  • RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。
  • LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
  • HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
  • KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。

可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区,例如:

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_partition_engine | YES   |
+-----------------------+-------+
1 row in set (0.00 sec)
 
1 。range分区 
 create table t_range( 
  id int(11), 
  money int(11) unsigned not null, 
  date datetime 
  )partition by range(year(date))( 
  partition p2007 values less than (2008), 
  partition p2008 values less than (2009), 
  partition p2009 values less than (2010) 
  partition p2010 values less than maxvalue 
  );

2.list分区

create table t_list( 
  a int(11), 
  b int(11) 
  )(partition by list (b) 
  partition p0 values in (1,3,5,7,9), 
  partition p1 values in (2,4,6,8,0) 
  );

对于innodb和myisam引擎,一条语句插入多条记录的时候,如果中间有值不能插入,innodb会全部回滚,myisam在错误值之前的数据可以插入到表中。
对于innodb和myisam引擎,一条语句插入多条记录的时候,如果中间有值不能插入,innodb会全部回滚,myisam在错误值之前的数据可以插入到表中。
3.hash分区

hash分区的目的是将数据均匀的分布到预先定义的各个分区中,保证各分区的数据量大致一致。

create table t_hash( 
  a int(11), 
  b datetime 
  )partition by hash (YEAR(b) 
  partitions 4;

  hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数,不加的partitions子句的话,默认为分区数为1。

4.key分区

key分区和hash分区相似,不同在于hash分区是用户自定义函数进行分区,key分区使用mysql数据库提供的函数进行分区,NDB cluster使用MD5函数来分区,对于其他存储引擎mysql使用内部的hash函数,这些函数基于password()一样的算法。

create table t_key( 
  a int(11), 
  b datetime) 
  partition by key (b) 
  partitions 4;
5。columns分区

上面的RANGE、LIST、HASH、KEY四种分区中,分区的条件必须是整形,如果不是整形需要通过函数将其转换为整形。

  mysql-5.5开始支持COLUMNS分区,可视为RANGE和LIST分区的进化,COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型:

  所有整形,如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。

  日期类型,如DATE和DATETIME。其余日期类型不支持。

  字符串类型,如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。

  COLUMNS可以使用多个列进行分区。


新增分区

mysql> ALTER TABLE sale_data

    ->   ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

删除分区

--当删除了一个分区,也同时删除了该分区中所有的数据。

mysql> ALTER TABLE sale_data DROP PARTITION p201010;

Query OK, 0 rows affected (0.22 sec)

Records: 0  Duplicates: 0  Warnings: 0

分区的合并

下面的SQL,将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

mysql> ALTER TABLE sale_data

    ->   REORGANIZE PARTITION p201001,p201002,p201003,

    ->                        p201004,p201005,p201006,

    ->                        p201007,p201008,p201009 INTO

    -> (

    ->   PARTITION p2010Q1 VALUES LESS THAN (201004),

    ->   PARTITION p2010Q2 VALUES LESS THAN (201007),

    ->   PARTITION p2010Q3 VALUES LESS THAN (201010)

    -> );

Query OK, 0 rows affected (1.14 sec)

Records: 0  Duplicates: 0  Warnings: 0

本文转载自:

共有 人打赏支持
xuewenyu
粉丝 0
博文 29
码字总数 10347
作品 0
高级程序员
mysql 能不能自动按日期分区(3个月如果手写要写90个partition)

现在有一张表需要按日期进行分区,因为每天大概千万级的数据,索引想按天进行分区。目前数据库中有3个月的数据,就是90天左右吧。看了下MySQL分区的SQL: CREATE TABLE ( int(11) NOT NULL ...

王二铁 ⋅ 2012/05/29 ⋅ 2

关于mysql分区的一些问题拿出来大家讨论下

最近在做一个数据库设计,有关道路上车辆的一个大型数据库,数据库里面有每辆车的一些属性信息,我之前做了一些批量插入表的测试,大概5万条数据(一条数据大概50列,大小1M多的样子)需要1...

w4920 ⋅ 2015/11/27 ⋅ 2

九、MySQL的分区 - 系统的撸一遍MySQL

MySQL支持数据分区,可以在对用户无感知的情况下,将对表数据的物理文件进行分区。 MySQL的分区支持Memory、MyISAM、InnoDB等存储引擎。 注意:MySQL中如果存在主键,或者唯一索引,那么分区...

logbird ⋅ 2016/11/02 ⋅ 0

Oracle数据库分区--实例

分区表通过对分区列进行判断,把满足不同条件的分区列对应的记录保存在不同的分区中。 一、何为分区表 什么情况下会使用分区表? 表中已有大量数据,或预计到表中将会保存大量的数据 可以按照...

mazongfei ⋅ 06/17 ⋅ 0

请教大家数据库分表解决方案

各位OSChina的兄弟姐妹,大家好: 现在小弟在开发中遇到这样个需求,请大家给点建议; 环境:Jdk1.6 +MySql5.0 框架:ofbiz 背景:现在MySql数据库中有一张日志表,这张表会频繁的进行读写操...

忠平 ⋅ 2012/03/09 ⋅ 12

【Hive】Hive基本操作及示例

1、数据库操作 (1)创建数据库 (2)查看数据库 (3)使用数据库 (4)查看数据库字段格式 (5)删除数据库 2、表操作 (1)创建表 (2)加载数据到表 LOCAL:从本地文件加载数据到hive表;...

gongxifacai_believe ⋅ 04/29 ⋅ 0

zabbix数据库优化脚本

分享一个zabbix数据库的优化脚本,适合2.0版本。 对history,hostoryuint按日分区,trends,trendsuint按月分区; 关闭Houserkeeper: vim zabbix_server.conf DisableHousekeeper=1 对history...

avlwx ⋅ 2014/05/07 ⋅ 0

oracle 时间范围分区 怎么插入9999年12月31日的数据

目前我根据时间字段 按月自动分区 插入时间值为9999年12月31日的数据时报ORA-01841: (full) year must be between -4713 and +9999, and not be 0 错误 。 猜测原因是 插入这个数据时,less ...

开源中国技术顾问 ⋅ 2017/01/05 ⋅ 0

昨天利用oracle分区和增量统计帮同事优化一个系统的经验分享。

公司某登记系统平均每天插入数据15w,目前有数据3000多w。该系统分为前端数据采集系统和后端查询统计系统,两个系统硬件隔离,部署了两个数据库。数据库采用第三方软件进行数据同步。 前端 ...

小偷家的保安 ⋅ 2016/01/13 ⋅ 1

DB-Engines 2017 年度数据库:PostgreSQL 实至名归

DB-Engines 网站宣布 PostgreSQL 为 2017 年度数据库管理系统。 DB-Engines 表示,PostgreSQL 在 2017 年的数据库排名中,比其他监测到的 341 个数据库管理系统都更受欢迎。因此,决定宣布 ...

局长 ⋅ 01/10 ⋅ 8

没有更多内容

加载失败,请刷新页面

加载更多

下一页

zblog2.3版本的asp系统是否可以超越卢松松博客的流量[图]

最近访问zblog官网,发现zlbog-asp2.3版本已经进入测试阶段了,虽然正式版还没有发布,想必也不久了。那么作为aps纵横江湖十多年的今天,blog2.2版本应该已经成熟了,为什么还要发布这个2.3...

原创小博客 ⋅ 52分钟前 ⋅ 0

聊聊spring cloud的HystrixCircuitBreakerConfiguration

序 本文主要研究一下spring cloud的HystrixCircuitBreakerConfiguration HystrixCircuitBreakerConfiguration spring-cloud-netflix-core-2.0.0.RELEASE-sources.jar!/org/springframework/......

go4it ⋅ 今天 ⋅ 0

二分查找

二分查找,也称折半查找、二分搜索,是一种在有序数组中查找某一特定元素的搜索算法。搜素过程从数组的中间元素开始,如果中间元素正好是要查找的元素,则搜素过程结束;如果某一特定元素大于...

人觉非常君 ⋅ 今天 ⋅ 0

VS中使用X64汇编

需要注意的是,在X86项目中,可以使用__asm{}来嵌入汇编代码,但是在X64项目中,再也不能使用__asm{}来编写嵌入式汇编程序了,必须使用专门的.asm汇编文件来编写相应的汇编代码,然后在其它地...

simpower ⋅ 今天 ⋅ 0

ThreadPoolExecutor

ThreadPoolExecutor public ThreadPoolExecutor(int corePoolSize, int maximumPoolSize, long keepAliveTime, ......

4rnold ⋅ 昨天 ⋅ 0

Java正无穷大、负无穷大以及NaN

问题来源:用Java代码写了一个计算公式,包含除法和对数和取反,在页面上出现了-infinity,不知道这是什么问题,网上找答案才明白意思是负的无穷大。 思考:为什么会出现这种情况呢?这是哪里...

young_chen ⋅ 昨天 ⋅ 0

前台对中文编码,后台解码

前台:encodeURI(sbzt) 后台:String param = URLDecoder.decode(sbzt,"UTF-8");

west_coast ⋅ 昨天 ⋅ 0

实验楼—MySQL基础课程-挑战3实验报告

按照文档要求创建数据库 sudo sercice mysql startwget http://labfile.oss.aliyuncs.com/courses/9/createdb2.sqlvim /home/shiyanlou/createdb2.sql#查看下数据库代码 代码创建了grade......

zhangjin7 ⋅ 昨天 ⋅ 0

一起读书《深入浅出nodejs》-node模块机制

node 模块机制 前言 说到node,就不免得提到JavaScript。JavaScript自诞生以来,经历了工具类库、组件库、前端框架、前端应用的变迁。通过无数开发人员的努力,JavaScript不断被类聚和抽象,...

小草先森 ⋅ 昨天 ⋅ 0

Java桌球小游戏

其实算不上一个游戏,就是两张图片,不停的重画,改变ball图片的位置。一个左右直线碰撞的,一个有角度碰撞的。 左右直线碰撞 package com.bjsxt.test;import javax.swing.*;import j...

森林之下 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部