【MySQL】分库-分区-分表的区别

1.分区、分表、分库的简单介绍

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

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

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

3.分区介绍

--什么时候考虑使用分区？

一张表的查询速度已经慢到影响使用的时候。
sql经过优化，但是查询还是缓慢
数据量大,查询缓慢
表中的数据是分段的
对数据的操作往往只涉及一部分数据，而不是所有的数据
分区解决的问题：主要可以提升查询效率，分区的实现方式（简单）
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);

4.分表使用场景

4.分表使用场景

1)、什么时候考虑分表？
一张表的查询速度已经慢到影响使用的时候。
sql经过优化
数据量大
当频繁插入或者联合查询时，速度变慢
2)、分表解决的问题
分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了
查询一次的时间短了
数据分布在不同的文件，磁盘I/O性能提高
读写锁影响的数据量变小
插入数据库需要重新建立索引的数据减少
分表的实现方式（复杂），需要业务系统配合迁移升级，工作量较大

3)、分区和分表的区别与联系
分区和分表的目的都是减少数据库的负担，提高表的增删改查效率。
分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。
当访问量大，且表数据比较大时，两种方式可以互相配合使用。
当访问量不大，但表数据比较多时，可以只进行分区。
常见分区分表的规则策略（类似）

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

5、分库使用场景

1)、什么时候考虑使用分库？
单台DB的存储空间不够
随着查询量的增加单台数据库服务器已经没办法支撑
2)、分库解决的问题
其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。

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

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

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

思路：垂直分库-->水平分库-->读写分离

6、拆分之后面临新的问题

--问题
事务的支持，分库分表，就变成了分布式事务
join时跨库，跨表的问题
分库分表，读写分离使用了分布式，分布式为了保证强一致性，必然带来延迟，
导致性能降低，系统的复杂度变高。

--常用的解决方案：
对于不同的方式之间没有严格的界限，特点不同，侧重点不同。需要根据实际情况，
结合每种方式的特点来进行处理。
选用第三方的数据库中间件（Atlas，Mycat，TDDL，DRDS），同时业务系统需要配合数据存储的升级。

7、数据存储的演进

1、单库单表
单库单表是最常见的数据库设计，例如，有一张用户(user)表放在数据库db中，所有的用户都可以在db库
中的user表中查到。

2、单库多表
随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候
对user表的查询会渐渐的变慢，从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是，
当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。
可以通过某种方式将user进行水平的切分，产生两个表结构完全一样的user_0000,user_0001等表，
user_0000 + user_0001 + …的数据刚好是一份完整的数据。

3、多库多表
随着数据量增加也许单台DB的存储空间不够，随着查询量的增加单台数据库服务器已经没办法支撑。
这个时候可以再对数据库进行水平拆分。

8、总结
总的来说，优先考虑分区。当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。