mysql分表

 

分表就是将一张表在物理上分成若干个小表，使查询更快速，开发的时候稍微麻烦点，需要判断使用那张表，虽然有merge方法但是使用的时候还是要找到确定的表进行操作才能真正的体现出分表的优势，再次就是分表需要对数据有个大致的预估。分表用的并不是很多。

为什么要分表和分区？

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

什么是分表？

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

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 ,

`name`  varchar(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 ,

`name`  varchar(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 ,

`name`  varchar(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中的最后一个表。感觉不太好维护，当需要新表时需要创建并添加到主表中。查询时必须选定某张表才会很快，对主表查询不会有效果，插入尽量也要选定某张表这样你可以提前建立很多表速度也会快，如果插入操作指定的是主表你就不能提前建立很多张表了因为插入模式是LAST。更新和删除可以操作主表。

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

 

 

修改合并哪些子表的命令：

ALTER TABLE article_total union =(article_0,article_1,article_2,article_3)