文档章节

mysql分表和表分区详解

zyt_1978
 zyt_1978
发布于 2016/07/06 19:34
字数 1751
阅读 3
收藏 0
点赞 0
评论 0

为什么要分表和分区?

日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。

什么是分表?

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。

什么是分区?

分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。

mysql分表和分区有什么联系呢?
1.都能提高mysql的性高,在高并发状态下都有一个良好的表现。
2.分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。
3.分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系。
4.表分区相对于分表,操作方便,不需要创建子表。

分表的几种方式:

1、mysql集群

它并不是分表,但起到了和分表相同的作用。集群可分担数据库的操作次数,将任务分担到多台数据库上。集群可以读写分离,减少读写压力。从而提升数据库性能。

2、自定义规则分表

大表可以按照业务的规则来分解为多个子表。通常为以下几种类型,也可自己定义规则。

?

1

2

3

4

5

Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。

Composite(复合模式) –以上模式的组合使用 

分表规则与分区规则一样,在分区模块详细介绍。

下面以Range简单介绍下如何分表(按照年份表)。

假设表结构有4个字段:自增id,姓名,存款金额,存款日期

把存款日期作为规则分表,分别创建几个表

2011年:account_2011

2012年:account_2012

……

2015年:account_2015

app在读写的时候根据日期来查找对应的表名,需要手动来判定。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

var getTableName = function() {

    var data = {

        name: 'tom',

        money: 2800.00,

        date: '201410013059'

    };

    var tablename = 'account_';

    var year = parseInt(data.date.substring(0, 4));

    if (year < 2012) {

        tablename += 2011; // account_2011

    } else if (year < 2013) {

        tablename += 2012; // account_2012

    } else if (year < 2014) {

        tablename += 2013; // account_2013

    } else if (year < 2015) {

        tablename += 2014; // account_2014

    } else {

        tablename += 2015; // account_2015

    }

    return tablename;

}

3、利用merge存储引擎来实现分表

merge分表,分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。

我们可以通过主表插入和查询数据,如果清楚分表规律,也可以直接操作子表。

子表2011年

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE TABLE `account_2011` (

`id`  int(11) NOT NULL AUTO_INCREMENT ,

`namevarchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

`money`  float NOT NULL ,

`tradeDate`  datetime NOT NULL

PRIMARY KEY (`id`)

)

ENGINE=MyISAM

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

AUTO_INCREMENT=2

CHECKSUM=0

ROW_FORMAT=DYNAMIC

DELAY_KEY_WRITE=0

;

子表2012年

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE TABLE `account_2012` (

`id`  int(11) NOT NULL AUTO_INCREMENT ,

`namevarchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

`money`  float NOT NULL ,

`tradeDate`  datetime NOT NULL

PRIMARY KEY (`id`)

)

ENGINE=MyISAM

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

AUTO_INCREMENT=2

CHECKSUM=0

ROW_FORMAT=DYNAMIC

DELAY_KEY_WRITE=0

;

主表,所有年

?

1

2

3

4

5

6

7

8

9

10

11

12

13

CREATE TABLE `account_all` (

`id`  int(11) NOT NULL AUTO_INCREMENT ,

`namevarchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

`money`  float NOT NULL ,

`tradeDate`  datetime NOT NULL

PRIMARY KEY (`id`)

)

ENGINE=MRG_MYISAM

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

UNION=(`account_2011`,`account_2012`)

INSERT_METHOD=LAST

ROW_FORMAT=DYNAMIC

;

创建主表的时候有个INSERT_METHOD,指明插入方式,取值可以是:0 不允许插入;FIRST 插入到UNION中的第一个表; LAST 插入到UNION中的最后一个表。

通过主表查询的时候,相当于将所有子表合在一起查询。这样并不能体现分表的优势,建议还是查询子表。

分区的几种方式

Range:

?

1

2

3

4

5

6

7

8

9

10

create table 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

);

List:

?

1

2

3

4

5

6

7

create table 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)

 );

Hash:

?

1

2

3

4

5

create table hash(

  a int(11),

  b datetime

  )partition by hash (YEAR(b)

  partitions 4;

Key:

?

1

2

3

4

5

create table t_key(

  a int(11),

  b datetime)

  partition by key (b)

  partitions 4;

分区管理

新增分区

?

1

2

ALTER TABLE sale_data

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

删除分区
--当删除了一个分区,也同时删除了该分区中所有的数据。
ALTER TABLE sale_data DROP PARTITION p201010;

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

?

1

2

3

4

5

6

7

8

9

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)

);

本文转载自:http://www.2cto.com/database/201503/380348.html

共有 人打赏支持
zyt_1978
粉丝 8
博文 166
码字总数 21058
作品 0
程序员

暂无文章

大数据教程(2.11):keeperalived+nginx高可用集群搭建教程

上一章节博主为大家介绍了目前大型互联网项目的系统架构体系,相信大家应该注意到其中很重要的一块知识nginx技术,在本节博主将为大家分享nginx的相关技术以及配置过程。 一、nginx相关概念 ...

em_aaron
23分钟前
0
0
Apache Directory Studio连接Weblogic内置LDAP

OBIEE默认使用Weblogic内置LDAP管理用户及组。 要整理已存在的用户及组,此前办法是导出安全数据,文本编辑器打开认证文件,使用正则表达式获取用户及组的信息。 后来想到直接用Apache Dire...

wffger
30分钟前
2
0
HFS

FS,它是一种上传文件的软件。 专为个人用户所设计的 HTTP 档案系统 - Http File Server,如果您觉得架设 FTP Server 太麻烦,那么这个软件可以提供您更方便的档案传输系统,下载后无须安装,...

garkey
35分钟前
1
0
Java IO类库之BufferedInputStream

一、BufferedInputStream介绍 /** * A <code>BufferedInputStream</code> adds * functionality to another input stream-namely, * the ability to buffer the input and to * sup......

老韭菜
37分钟前
0
0
STM 32 窗口看门狗

http://bbs.elecfans.com/jishu_805708_1_1.html https://blog.csdn.net/a1985831055/article/details/77404131...

whoisliang
昨天
0
0
Dubbo解析(六)-服务调用

当dubbo消费方和提供方都发布和引用完成后,第四步就是消费方调用提供方。 还是以dubbo的DemoService举例 -- 提供方<dubbo:application name="demo-provider"/><dubbo:registry address="z...

青离
昨天
1
0
iptables规则备份和恢复、firewalld的9个zone以及操作和service的操作

保存以及备份iptalbes规则 设定了的防火墙规则要进行保存,否则系统重启后这些规则就没有了,使用命令 ”service iptables save ” 会把设定好的防火墙规则保存到文件/etc/sysconfig/iptabl...

黄昏残影
昨天
0
0
k8s image

k8s.gcr.io/kube-apiserver-amd64:v1.11.0k8s.gcr.io/kube-controller-manager-amd64:v1.11.0k8s.gcr.io/kube-scheduler-amd64:v1.11.0k8s.gcr.io/kube-proxy-amd64:v1.11.0k8s.gcr.......

分秒
昨天
0
0
数据结构--排序

这篇博客包含了数据结构中多种的排序算法: (1)简单选择:第一趟在A[0]~A[n-1]之间找到最小的,与A[0]进行交换,之后在A[1]~A[n-1]之间进行。。。第i趟在A[i-1]~A[n-1]之间找到最小的,最后...

wangxuwei
昨天
1
0
一名3年工作经验的java程序员应该具备的职业技能

一名3年工作经验的Java程序员应该具备的技能,这可能是Java程序员们比较关心的内容。我这里要说明一下,以下列举的内容不是都要会的东西—-但是如果你掌握得越多,最终能得到的评价、拿到的薪...

老道士
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部