阅读之MySQL数据库分表

移动互联网时代，海量的用户数据每天都在产生，基于用户使用数据等这样的分析，都需要依靠数据统计和分析，当数据量小时，数据库方面的优化显得不太重要，一旦数据量越来越大，系统响应会变慢，TPS直线下降，直至服务不可用。

虽说MySQL单表可以存储10亿级的数据，但这个时候性能非常差。既然一张表无法搞定，那么就想办法将数据放到多个地方来解决问题吧，于是，数据库分库分表的方案便产生了，目前比较普遍的方案有三个：分区，分库分表，NoSQL/NewSQL。

在实际的项目中，往往是这三种方案的结合来解决问题，目前绝大部分系统的核心数据都是以RDBMS存储为主，NoSQL/NewSQL存储为辅。

分区

分区方案

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。这个方案也不错，它对用户屏蔽了sharding的细节，即使查询条件没有sharding column，它也能正常工作（只是这时候性能一般）。不过它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等。如何进行分区，在实际应用中是一个非常关键的要素之一。在项目中，以客户信息为例，客户数据量5000万加，项目背景要求保存客户的银行卡绑定关系，客户的证件绑定关系，以及客户绑定的业务信息。此业务背景下，该如何设计数据库呢。项目一期的时候，我们建立了一张客户业务绑定关系表，里面冗余了每一位客户绑定的业务信息。

查询时，对银行卡做索引，业务编号做索引，证件号做索引。随着需求大增多，这张表的索引会达到10个以上。而且客户解约再签约，里面会保存两条数据，只是绑定的状态不同。假设我们有5千万的客户，5个业务类型，每位客户平均2张卡，那么这张表的数据量将会达到惊人的5亿，事实上我们系统用户量还没有过百万时就已经不行了。MySQL数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看）， 一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。这三个文件都非常的庞大，尤其是.myd文件，快5个G了。下面进行第一次分区优化，MySQL支持的分区方式有四种：

 

HASH 分区仅支持int类型列的分区，且是其中的一列。看看我们的库表结构，发现没有哪一列是int类型的，如何做分区呢？可以增加一列，绑定时间列，将此列设置为int类型，然后按照绑定时间进行分区，将每一天绑定的用户分到同一个区里面去。这次优化之后，我们的插入快了许多，但是查询依然很慢，为什么，因为在做查询的时候，我们也只是根据银行卡或者证件号进行查询，并没有根据时间查询，相当于每次查询，MySQL都会将所有的分区表查询一遍。

分库分表

如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：

• 阿里的TDDL，DRDS和cobar，
• 京东金融的sharding-jdbc；
• 间组织的MyCAT；
• 360的Atlas；
• 美团的zebra；
• 其他比如网易、58、京东等公司都有自研的中间件。

归总起来，就两类：client模式和proxy模式。

 

 

proxy模式

 

垂直分库

 

对于每分钟要处理近1000万的流水，每天流水近1亿的量，如何高效的写入和查询，是一项比较大的挑战。还是老办法，分库分表分区，读写分离，只不过这一次，我们先分表，再分库，最后分区。

我们将消息流水按照不同的业务类型进行分表，相同业务的消息流水进入同一张表，分表完成之后，再进行分库。我们将流水相关的数据单独保存到一个库里面去，这些数据，写入要求高，查询和更新到要求低，将它们和那些更新频繁的数据区分开。分库之后，再进行分区。

 

这是基于业务垂直度进行的分库操作，垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库，以达到系统资源的饱和利用率。这样的分库方案结合应用的微服务治理，每个微服务系统使用独立的一个数据库。将不同模块的数据分库存储，模块间不能进行相互关联查询，如果有，要么通过数据冗余解决，要么通过应用代码进行二次加工进行解决。

总结：SQL数据优化的方法有很多：垂直分库、水平分库、建立索引等。在不同的项目中采用合适的优化方案是必要的。