分区分表心得与整理

分区分表定义（what）

实体表的拆分和存储空间的拆分，减少单位个体空间的大小

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件

数据库分区是一种物理数据库设计技术，是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器

表或索引可以共享相同的逻辑属性，但是可以有不同的物理属性

分区的优缺点:

     分区表优点

    可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
    方便维护，通过删除分区来删除老的数据。
    分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。

    可以优化、检查、修复个别分区。

    根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了。
    进行大数据搜索时可以进行并行处理。
    跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

    分区表缺点

    每张表最大分区数为1024。
    所有的主键或者唯一索引必须被包含在分区表达式中。
    不能使用任何外键约束

为什么拆分：（why）  合久必分 分久必合

业务需求扩展（解耦操作）

访问量并发增大（IO操作）

数据量过大

减少索引和数据模块的体积

 拆分目的：

为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。

HOW

1.垂直拆分

解决问题：表与表之间的io竞争，适用于业务拆分，降低耦合度

不解决问题：单表中数据量增长出现的压力

用户表：

uid uname password nick info edu login_time create_time

订单系统（联系人 商品  地址  发票 支付 其他）

2.水平拆分（表的拆分和内部的拆分）

单表大小：1000w以下

Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。(百合消息表)

List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。   

Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表（筑龙用户表，积分表，答题表）。  

Key（键值） – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。//mysql的函数

Composite（复合模式） - 其实是以上模式的组合使用而已。例如：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash或KEY（Linear Hash）分区。

Range 和 List 的缺点是容易造成热点数据集中 优点：可部分迁移

Hash Key 相对来说是均匀的   缺点：数据迁移的时候麻烦;不能按照机器性能分摊数据 

部分命令分区展示：

多表切分同样适用于

两种方法各有优缺点（分区：对未建立索引优势明显，单一入口，无法分担入口IO操作，操作便捷，易拓展）

3.利用merge存储引擎来实现分表（筑龙百科实现）

merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的 基于mysql的引擎（MYISM/MRG_MYISAM）实现。

CREATETABLE`account_2012` (

`id`  int(11) NOTNULLAUTO_INCREMENT ,

`name`  varchar(50) CHARACTERSETutf8 COLLATEutf8_general_ci NULLDEFAULTNULL,

`money`  floatNOTNULL,

`tradeDate`  datetime NOTNULL

PRIMARYKEY(`id`)

)

ENGINE=MyISAM

DEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ci

AUTO_INCREMENT=2

CHECKSUM=0

ROW_FORMAT=DYNAMIC

DELAY_KEY_WRITE=0

;

CREATETABLE`account_all` (

`id`  int(11) NOTNULLAUTO_INCREMENT ,

`name`  varchar(50) CHARACTERSETutf8 COLLATEutf8_general_ci NULLDEFAULTNULL,

`money`  floatNOTNULL,

`tradeDate`  datetime NOTNULL

PRIMARYKEY(`id`)

)

ENGINE=MRG_MYISAM

DEFAULTCHARACTERSET=utf8 COLLATE=utf8_general_ci

UNION=(`account_2011`,`account_2012`)

INSERT_METHOD=LAST

ROW_FORMAT=DYNAMIC

;

热点数据（作为参考 非真正意义的分区）

将热点数据放到临时表  只保留热点的  如用户登陆 只保留最近30天 减少索引 增加访问速度