3亿数据 mysql 分表分库 mysql分库分表

当数据量较大时（一般 千万条 记录级别以上），MySQL的性能就会开始下降，这时我们就需要 分库、分表，来优化数据，提高数据库的执行效率。

1 分库

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

• 减少增量数据写入时的锁对查询的影响。
• 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短。

但是它无法解决单表数据量太大的问题。

2 分表

两种分割方式。

垂直拆分

垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

水平拆分（数据分片）

单表的容量不超过500W，否则建议水平拆分。

是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

水平分割的几种方法：

• 使用MD5哈希，做法是对唯一键（例如用户的UID）进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
• 根据时间划分，放入不同的表，比如：article_201601，article_201602。
• 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
• 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二个表user_0001中，随用户增加，直接添加用户表就行了。

3 分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：

• 用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。
• 跨库联合查询的问题，可能需要两次查询。
• 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并。
• 额外的数据管理负担，如：访问数据表的导航定位。
• 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

可以使用成熟的中间件：ShardingSphere（客户端，jar包集成）、Mycat（中间件，需要单独部署）。

4 分库分表后id主键如何处理

需要一个全局唯一的id来支持。

使用 snowflake（雪花算法）。

snowflake 算法是 twitter 开源的分布式 id ⽣成算法，采⽤ Scala 语⾔实现，是把⼀个 64 位的 long 型的 id划分成多段：1 个 bit 是不⽤的，⽤其中的 41 bit 作为毫秒数，⽤ 10 bit 作为⼯作机器 id，12 bit 作为序列号。一毫秒内能够生成4096个id。