分区分表分库

 

 

---

 

 

分区（加快访问速度）

什么时候分区？

一张表的查询速度已经慢到影响使用的时候。

• sql经过优化
• 数据量大（表的大小超过2GB，一般单表撑死1000万条）
• 表中的数据是分段的（表中包含历史数据，新的数据被增加都新的分区中）
• 对数据的操作往往只涉及一部分数据，而不是所有的数据

从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

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);

---

Mysql的主从模式（分库分表前的解决方案）

面对越来越多的请求，我们将数据库的写操作和读操作进行分离， 使用多个从库副本（Slaver Replication）负责读，使用主库（Master）负责写， 从库从主库同步更新数据，保持数据一致。架构上就是数据库主从同步。 从库可以水平扩展，所以更多的读请求不成问题。

但是当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的， 因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且更加复杂。（需要分布式一致性算法维护）

这时就需要用到分库分表（sharding），对写操作进行切分。

数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）

---

垂直切分和水平切分（分库分表）

垂直分表（同一表的冷热数据拆分）

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

​ 将访问频次低的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中。

MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。

• 通常我们按以下原则进行垂直拆分:

（1）把不常用的字段单独放在一张表;
（2）把text，blob等大字段拆分出来放在附表中;
（3）经常组合查询的列放在一张表中;

垂直分库

通过垂直分表性能得到了一定程度的提升，但是还没有达到要求，并且磁盘空间也快不够了，因为数据还是始终限制在一台服务器，库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。

水平分表分库

• RANGE

从0到10000一个表，10001到20000一个表；

• HASH取模

一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。

比如美团的分库分表的方案是32个数据库实例，通过userId进行取模的话就是，将UserId后面4位模32然后丢到32个数据库中，同时又将UserId后面4位除以32再mod32丢到32张表里面，这样就有1024张表，然后线上部署8个主从实例，每个实例4个数据库。

如果频繁用到的查询条件中不带sharding key时，将会导致无法定位数据库，从而需要同时向多个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

• 地理区域

比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

• 时间

按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

---

分库分表后面临的问题

• 事务支持

分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

• 多库结果集合并（group by，order by）

• 跨库join（水平分库能join，垂直分库不行）

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

粗略的解决方法：
（1）全局表：基础数据，所有库都拷贝一份。
（2） 字段冗余：这样有些字段就不用join去查询了。
（3）系统层组装：分别查询出所有，然后组装起来，较复杂。

 

image.png

分布式下怎么保障ID唯一

1. 利用数据库集群并设置相应的步长（Flickr方案）
   优点：高可用、ID较简洁。 缺点：需要单独的数据库集群。

2. Twitter Snowflake
   优点：高性能高可用、易拓展。 缺点：需要独立的集群以及ZK。

3. 带有业务属性的方案： > 时间戳+用户标识码+随机数

有下面几个好处：

（1）方便、成本低。
基本无重复的可能。
（2）自带分库规则，这里的用户标识码即为用户ID的后四位，在查询的场景下，只需要订单号就可以匹配到相应的库表而无需用户ID，只取四位是希望订单号尽可能的短一些，并且评估下来四位已经足够。
（3）可排序，因为时间戳在最前面。

欢迎关注微信公众号：shoshana