MySQL分表、分区方法

项目刚开始需求分析后的架构一般都会考虑到数据库设计的未来承载量和承载方式，即使是小项目开发到一定程度也需要重新升级或者设计数据库的，今天就来理解一下分区、分表的概念。

一、分区的概念

1. 相对于单个文件系统或是硬盘，分区可以存储更多的数据；
2. 数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可；
3. 精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率；
4. 可跨多个分区磁盘查询，来提高查询的吞吐量；
5. 在涉及聚合函数查询时，可以很容易进行数据的合并；

二、分区类别 （row 行分 ，column 列分）

1、水平分区

这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）

2、垂直分区

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。

三、分区、分表、分库的详细理解

      1、什么是分区、分表、分库

• 分区
  就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的
• 分表
  就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。
• 分库
  一旦分表，一个库中的表会越来越多

将整个数据库比作图书馆，一张表就是一本书。当要在一本书中查找某项内容时，如果不分章节，查找的效率将会下降。而同理，在数据库中就是分区。

     2、常用的单机数据库的瓶颈

    问题描述

1. 单个表数据量越大，读写锁，插入操作重新建立索引效率越低。
2. 单个库数据量太大（一个数据库数据量到1T-2T就是极限）
3. 单个数据库服务器压力过大
4. 读写速度遇到瓶颈（并发量几百）

     3、分区

    什么时候考虑使用分区？

• 一张表的查询速度已经慢到影响使用的时候。
  sql经过优化
  数据量大
  表中的数据是分段的
  对数据的操作往往只涉及一部分数据，而不是所有的数据

    分区解决的问题

• 主要可以提升查询效率

    分区的实现方式（简单）

• mysql5 开始支持分区功能

一、例如：按时间一个字段来做分区

CREATE TABLE sales (
    `id` INT AUTO_INCREMENT,
    `amount` DOUBLE NOT NULL,
    `order_day` DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
) ENGINE=Innodb 
PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE);

二、按多个字段的值来分区

CREATE TABLE `sales` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `amount` DOUBLE NOT NULL, order_day DATETIME NOT NULL,
  `order_day` DATETIME NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '状态 0:未完成,1:已完成',
  PRIMARY KEY (`id`,`type`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(YEAR(order_day), status)
(PARTITION p0 VALUES LESS THAN (2010,1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2010,2) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2011,1) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2011,2) ENGINE = InnoDB);

     4、分表

    什么时候考虑分表？

• 一张表的查询速度已经慢到影响使用的时候。
  sql经过优化
  数据量大
  当频繁插入或者联合查询时，速度变慢

    分表解决的问题

    分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

• 查询一次的时间短了
  数据分布在不同的文件，磁盘I/O性能提高
  读写锁影响的数据量变小
  插入数据库需要重新建立索引的数据减少

    分表的实现方式（复杂）

     需要业务系统配合迁移升级，工作量较大

    分区和分表的区别与联系

• 分区和分表的目的都是减少数据库的负担，提高表的增删改查效率。
  分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。
  当访问量大，且表数据比较大时，两种方式可以互相配合使用。
  当访问量不大，但表数据比较多时，可以只进行分区。

     常见分区分表的规则策略（类似）

• Range（范围）
  Hash（哈希）
  按照时间拆分
  Hash之后按照分表个数取模
  在认证库中保存数据库配置，就是建立一个DB，这个DB单独保存user_id到DB的映射关系

例如：利用 merge 存储引擎来分表(表结构需一致且子表存储引擎必须为MyISAM)，主表使用UNION联合其他分表

CREATE TABLE `log_00` (
  `id` INT(11) NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `desc` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `log_01` (
  `id` INT(11) NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `desc` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#INSERT_METHOD：NO(禁止插入)|FIRST(插入UNION列出的第一个表)|LAST(插入UNION列出的最后一个表)
CREATE TABLE `log` (
  `id` INT(11) NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `desc` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM UNION=(log_00, log_01) INSERT_METHOD=LAST DEFAULT CHARSET=utf8;

#插入数据,使用 redis 生成全局ID
INSERT INTO `log_00`(`id`, `user_id`, `desc`) VALUES(1, 1, 'A');
INSERT INTO `log_00`(`id`, `user_id`, `desc`) VALUES(2, 2, 'B');
INSERT INTO `log_01`(`id`, `user_id`, `desc`) VALUES(3, 3, 'C');
INSERT INTO `log_01`(`id`, `user_id`, `desc`) VALUES(4, 4, 'D');
INSERT INTO `log`(`id`, `user_id`, `desc`) VALUES(5, 5, 'E');

#查询
SELECT * FROM log;

来自Mr Ming的示例：https://sevming.github.io/Mysql/mysql-partitions.html#more

     5、分库

    什么时候考虑使用分库？

• 单台DB的存储空间不够
  随着查询量的增加单台数据库服务器已经没办法支撑

    分库解决的问题

• 其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。

   垂直拆分

• 将系统中不存在关联关系或者需要join的表可以放在不同的数据库不同的服务器中。
  按照业务垂直划分。比如：可以按照业务分为资金、会员、订单三个数据库。
  需要解决的问题：跨数据库的事务、jion查询等问题。

    水平拆分

• 例如，大部分的站点。数据都是和用户有关，那么可以根据用户，将数据按照用户水平拆分。
  按照规则划分，一般水平分库是在垂直分库之后的。比如每天处理的订单数量是海量的，可以按照一定的规则水平划分。需要解决的问题：数据路由、组装。

    读写分离

• 对于时效性不高的数据，可以通过读写分离缓解数据库压力。需要解决的问题：在业务上区分哪些业务上是允许一定时间延迟的，以及数据同步问题。

    思路

• 垂直分库–>水平分库–>读写分离