`

MySql数据库分库分表

 
阅读更多

为什么要分表和分区?

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

什么是分表?

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,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

分享到:
评论

相关推荐

    MyBatis实现Mysql数据库分库分表操作和总结(推荐)

    MyBatis实现Mysql数据库分库分表操作和总结 MyBatis是一款流行的持久层框架,能够与Mysql数据库进行交互。在大规模的应用系统中,数据库的性能和可扩展性变得非常重要。为了解决这个问题,需要实现数据库的分库分表...

    mysql 数据库分库分表技术,主从复制技术-mysql_learn_demo.zip

    为了解决这一问题,通常会采用分库分表和主从复制等技术。以下是对这些技术的详细解析。 **一、分库分表** 1. **分库分表的概念** 分库分表是将一个大型的数据库拆分为多个小型数据库或表格,以降低单个数据库的...

    mysql高可用分库分表.pdf

    为了提升MySQL数据库的性能和可用性,常采用的策略是数据库分库分表,其中可以分为垂直分库、垂直分表、水平分库、水平分表等方法。 垂直分表是针对数据库表中的列进行拆分,将一张大表拆成若干个结构更小的表,以...

    mysql数据库分库分表实践

    在应对大数据量带来的挑战时,"mysql数据库分库分表实践"成为了许多企业解决数据库性能瓶颈的关键策略。本文将深入探讨这一主题,分析背景、调研的技术特性,并介绍相关的架构设计改造。 一、背景 随着零售行业的...

    数据库分库分表的几种方式.wmv

    mysql数据库分库分表的几种方式,数据库优化的一部分...

    MySQL数据库之分库分表方案_ITPUB博客.mhtml

    MySQL数据库之分库分表方案_ITPUB博客.mhtml MySQL数据库之分库分表方案_ITPUB博客.mhtml MySQL数据库之分库分表方案_ITPUB博客.mhtml

    Python+MySQL分表分库实战

    Python作为一种流行的编程语言,与MySQL数据库结合使用,可以方便地实现分库分表的各种操作和自动化处理。 本实战教程将通过一系列知识点的介绍,讲解如何使用Python配合MySQL来实现分库分表的实战操作。首先,我们...

    MySQL 分库分表的实现原理及演示案例

    数据库分库分表的几种方式主要包括垂直分库、垂直分表、水平分库和水平分表。垂直分库是按照业务的不同进行数据库的分割,每个数据库只包含部分业务的数据;垂直分表是将一个表按照字段划分成多个表,通常按照数据的...

    MySQL分库分表技术

    **MySQL分库分表技术** 随着互联网业务的快速发展,数据量呈现爆炸性增长,单个数据库的性能瓶颈问题日益突出。在这种背景下,MySQL的分库分表技术应运而生,旨在解决高并发、大数据量场景下的性能挑战。本篇将深入...

    基于jdbcDriver的MySQL分库分表实现-OShard.zip

    本项目"基于jdbcDriver的MySQL分库分表实现-OShard.zip"提供了使用Java JDBC驱动来实现MySQL数据库分库分表的示例。下面将详细阐述相关知识点。 1. **MySQL数据库**:MySQL是一款开源、免费的关系型数据库管理系统...

    Mycat数据库分库分表

    《Mycat数据库分库分表详解》 在大数据时代,单个数据库处理海量数据的能力逐渐捉襟见肘,这催生了数据库分库分表的需求。Mycat,作为一个开源的分布式数据库中间件,应运而生,为解决大数据场景下的高性能、高可用...

    阿里巴巴数据库分库分表的实践.docx

    阿里巴巴在应对大数据量挑战时,采用了数据库分库分表的策略,这涉及到一系列的技术实践和解决方案。早期,阿里巴巴团队基于开源项目Cobar开发了分布式数据层平台,旨在解决Oracle数据库扩展性问题。Cobar虽然在一定...

    mycat对mysql数据库进行分库分表demo-mycat-demo-parent.zip

    标题"mycat对mysql数据库进行分库分表demo-mycat-demo-parent.zip"揭示了本示例主要围绕MyCat这一中间件,用于MySQL数据库的分布式处理,特别是分库分表功能。MyCat是一个开源的分布式数据库系统,它能够将一个大型...

    ShardingJDBC分库分表mysql数据库实战小滴新版

    课程首先介绍了分库分表的背景,包括 MySQL 数据库架构演变历史以及业务增长下数据库性能优化思路。接着深入探讨了分库分表的各种方式,如垂直分表、垂直分库、水平分表和水平分库,并对其进行了总结。还讲解了 ...

    php mysql分库分表实例

    "php mysql分库分表实例"这个主题就是针对这样的需求,它涉及到如何利用PHP编程语言来实现MySQL数据库的分库分表操作,以提高系统的性能和可扩展性。 分库分表是一种常见的数据库垂直切分和水平切分策略,用于解决...

    mysql大数据分库和分表 php解决方案

    在这种背景下,单一数据库往往难以承受巨大的数据处理压力,因此,采用分库分表技术成为了提高数据库性能的有效手段之一。本文主要探讨MySQL大数据分库和分表的PHP解决方案,并通过具体的案例分析来加深理解。 ####...

    MySQL数据库优化之分表分库操作实例详解

    Spring Boot等框架提供了对分库分表的支持,如ShardingSphere等中间件可以帮助开发者实现这些策略,简化数据库的扩展和维护工作。 总之,MySQL数据库的分表分库优化是应对高并发、大数据量场景的有效手段。通过对...

    一个MySQL分库分表php类

    一个MySQL分库分表php类。当一个表数据记录过大时就会出现性能瓶颈,而一般对应的解决办法是要么做分区表,要么分表,分区表就不说了,分表又分为垂直分割和水平分割,具体区 别请自行搜索。一般而言,分库分表属于...

    基于Mycat实现Mysql读写分离以及分库分表.doc

    基于Mycat实现Mysql读写分离以及分库分表可以提高数据库的性能和可扩展性。但是,需要正确地配置Mycat的配置文件,并且手动建好拆分后的库,以及手动建好逻辑库里的表。 通过本文档,你可以了解到Mycat读写分离和分...

Global site tag (gtag.js) - Google Analytics