MySQL分区与传统的分库分表

传统的分库分表

传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。

分库

分库的原因

首先，在单台数据库服务器性能足够的情况下，分库对于数据库性能是没有影响的。在数据库存储上，database只起到一个namespace的作用。database中的表文件存储在一个以database名命名的文件夹中。比如下面的employees数据库：

mysql> show tables in employees;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+

在操作系统中看是这样的：

# haitian at haitian-coder.local in /usr/local/var/mysql/employees on git:master ● [21:19:47]
→ ls  
db.opt           dept_emp.frm     dept_manager.ibd salaries.frm     titles.ibd
departments.frm  dept_emp.ibd     employees.frm    salaries.ibd
departments.ibd  dept_manager.frm employees.ibd    titles.frm

database不是文件，只起到namespace的作用，所以MySQL对database大小当然也是没有限制的，而且对里面的表数量也没有限制。

C.10.2 Limits on Number of Databases and Tables

MySQL has no limit on the number of databases. The underlying file
system may have a limit on the number of directories.

MySQL has no limit on the number of tables. The underlying file system
may have a limit on the number of files that represent tables.
Individual storage engines may impose engine-specific constraints.
InnoDB permits up to 4 billion tables.

所以，为什么要分库呢？

答案是为了解决单台服务器的性能问题，当单台数据库服务器无法支撑当前的数据量时，就需要根据业务逻辑紧密程度把表分成几撮，分别放在不同的数据库服务器中以降低单台服务器的负载。

分库一般考虑的是垂直切分，除非在垂直切分后，数据量仍然多到单台服务器无法负载，才继续水平切分。

比如一个论坛系统的数据库因当前服务器性能无法满足需要进行分库。先垂直切分，按业务逻辑把用户相关数据表比如用户信息、积分、用户间私信等放入user数据库；论坛相关数据表比如板块，帖子，回复等放入forum数据库，两个数据库放在不同服务器上。

拆分后表往往不可能完全无关联，比如帖子中的发帖人、回复人这些信息都在user数据库中。未拆分前可能一次联表查询就能获取当前帖子的回复、发帖人、回复人等所有信息，拆分后因为跨数据库无法联表查询，只能多次查询获得最终数据。

所以总结起来，分库的目的是降低单台服务器负载，切分原则是根据业务紧密程度拆分，缺点是跨数据库无法联表查询。

分表

分表的原因

当数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。

垂直分表

原因：

1.根据MySQL索引实现原理及相关优化策略的内容我们知道Innodb主索引叶子节点存储着当前行的所有信息，所以减少字段可使内存加载更多行数据，有利于查询。

2.受限于操作系统中的文件大小限制。

切分原则：
把不常用或业务逻辑不紧密或存储内容比较多的字段分到新的表中可使表存储更多数据。。

水平分表

原因：

1.随着数据量的增大，table行数巨大，查询的效率越来越低。

2.同样受限于操作系统中的文件大小限制，数据量不能无限增加，当到达一定容量时，需要水平切分以降低单表（文件）的大小。

切分原则： 增量区间或散列或其他业务逻辑。

使用哪种切分方法要根据实际业务逻辑判断。

比如对表的访问多是近期产生的新数据，历史数据访问较少，可以考虑根据时间增量把数据按照一定时间段（比如每年）切分。

如果对表的访问较均匀，没有明显的热点区域，则可以考虑用范围（比如每500w一个表）或普通Hash或一致性Hash来切分。

全局主键问题：

原本依赖数据库生成主键（比如自增）的表在拆分后需要自己实现主键的生成，因为一般拆分规则是建立在主键上的，所以在插入新数据时需要确定主键后才能找到存储的表。

实际应用中也已经有了比较成熟的方案。比如对于自增列做主键的表，flickr的全局主键生成方案很好的解决了性能和单点问题，具体实现原理可以参考这个帖子。除此之外，还有类似于uuid的全局主键生成方案，比如达达参考的Instagram的ID生成器。

一致性Hash：

使用一致性Hash切分比普通的Hash切分可扩展性更强，可以实现拆分表的添加和删除。一致性Hash的具体原理可以参考这个帖子，如果拆分后的表存储在不同服务器节点上，可以跟帖子一样对节点名或ip取Hash；如果拆分后的表存在一个服务器中则可对拆分后的表名取Hash。

MySQL的分区表

上面介绍的传统的分库分表都是在应用层实现，拆分后都要对原有系统进行很大的调整以适应新拆分后的库或表，比如实现一个SQL中间件、原本的联表查询改成两次查询、实现一个全局主键生成器等等。

而下面介绍的MySQL分区表是在数据库层面，MySQL自己实现的分表功能，在很大程度上简化了分表的难度。

介绍

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表实现。

也就是说，对于原表分区后，对于应用层来说可以不做变化，我们无需改变原有的SQL语句，相当于MySQL帮我们实现了传统分表后的SQL中间件，当然，MySQL的分区表的实现要复杂很多。

另外，在创建分区时可以指定分区的索引文件和数据文件的存储位置，所以可以把数据表的数据分布在不同的物理设备上，从而高效地利用多个硬件设备。

一些限制：

1.在5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。

2.分区表中无法使用外键约束。

3.主表的所有唯一索引列（包括主键）都必须包含分区字段。MySQL官方文档中写的是：

All columns used in the partitioning expression for a partitioned
table must be part of every unique key that the table may have.

这句话不是很好理解，需要通过例子才能明白，MySQL官方文档也为此限制特意做了举例和解释。

分区表的类型

RANGE分区

根据范围分区，范围应该连续但是不重叠，使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!