目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量，从而提高查询速度，一般分表的方式有两种：水平拆分和垂直拆分，两者各有利弊，适用于不同的情况。

准备工作

数据拆分前其实是要首先做准备工作的，然后才是开始数据拆分，我先讲拆分前需要做的事情：

  1. 采用分布式缓存redis、memcached等降低对数据库的读操作。
  2. 如果缓存使用过后，数据库访问量还是非常大，可以考虑数据库读、写分离原则。
  3. 当我们使用读写分离、缓存后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。

数据库拆分原则：就是指通过某种特定的条件，按照某个维度，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面以达到分散单库（主机）负载的效果。

拆分模式

当我们使用读写分离、缓存后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。          数据库拆分简单来说，就是指通过某种特定的条件，按照某个维度，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面以达到分散单库（主机）负载的效果。 

垂直分表

垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”（字段）进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中，如下图所示：

垂直分表 垂直分表

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

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

小结

在字段很多的情况下，拆分开确实更便于开发和维护（笔者曾见过某个遗留系统中，一个大表中包含 100 多列的）。某种意义上也能避免“跨页”的问题（MySQL、MSSQL 底层都是通过“数据页”来存储的，“跨页”问题可能会造成额外的性能开销，这里不展开，感兴趣的朋友可以自行查阅相关资料进行研究）。

垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用jion关键起来即可;如果是在发展过程中拆分，则需要改写以前的查询语句，会额外带来一定的成本和风险，建议谨慎。

垂直分库

垂直分库在“微服务”盛行的今天已经非常普及了。基本的思路就是按照业务模块来划分出不同的数据库，而不是像早期一样将所有的数据表都放到同一个数据库中。如下图：

垂直分库

优点：

  1. 拆分后业务清晰，拆分规则明确。
  2. 系统之间整合或扩展容易。
  3. 数据维护简单。

缺点：

  1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
  2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
  3. 事务处理复杂。

小结

系统层面的“服务化”拆分操作，能够解决业务系统层面的耦合和性能瓶颈，有利于系统的扩展维护。而数据库层面的拆分，道理也是相通的。与服务的“治理”和“降级”机制类似，我们也能对不同业务类型的数据进行“分级”管理、维护、监控、扩展等。

众所周知，数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于 Web 和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破 IO、连接数及单机硬件资源的瓶颈，是大型分布式系统中优化数据库架构的重要手段。

然后，很多人并没有从根本上搞清楚为什么要拆分，也没有掌握拆分的原则和技巧，只是一味的模仿大厂的做法。导致拆分后遇到很多问题（例如：跨库 join，分布式事务等）。

水平分表

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分表也称为横向分表，比较容易理解，就是将表中不同的数据行按照一定规律分布到不同的数据库表中（这些表保存在同一个数据库中），这样来降低单表数据量，优化查询性能。最常见的方式就是通过主键或者时间等字段进行 Hash 和取模后拆分。如下图所示：

水平分表

小结

水平分表，能够降低单表的数据量，一定程度上可以缓解查询性能瓶颈。但本质上这些表还保存在同一个库中，所以库级别还是会有 IO 瓶颈。所以，一般不建议采用这种做法。

注意,进行水平拆分后的表,字段的列和类型和原表应该是相同的,但是要记得去掉auto_increment自增长

水平分库分表

水平分库分表与上面讲到的水平分表的思想相同，唯一不同的就是将这些拆分出来的表保存在不同的数据中。这也是很多大型互联网公司所选择的做法。如下图：

水平分库分表

某种意义上来讲，有些系统中使用的“冷热数据分离”（将一些使用较少的历史数据迁移到其他的数据库中。而在业务功能上，通常默认只提供热点数据的查询），也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破 IO、连接数、硬件资源的瓶颈。当然，投入的硬件成本也会更高。同时，这也会带来一些复杂的技术问题和挑战（例如：跨分片的复杂查询，跨分片事务等）          优点：

  1. 不存在单库大数据，高并发的性能瓶颈。
  2. 对应用透明，应用端改造较少。     
  3. 按照合理拆分规则拆分，join操作基本避免跨库。
  4. 提高了系统的稳定性跟负载能力。

缺点： 1. 拆分规则难以抽象。

  1. 分片事务一致性难以解决。
  2. 数据多次扩展难度跟维护量极大。
  3. 跨库join性能较差。

需要注意的问题

两种方式共同缺点

  1. 引入分布式事务的问题。
  2. 跨节点Join 的问题。
  3. 跨节点合并排序分页问题。

针对数据源管理，目前主要有两种思路：

  • A. 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个 数据库，在模块内完成数据的整合。

    优点：相对简单，无性能损耗。

    缺点：不够通用，数据库连接的处理复杂，对业务不够透明，处理复杂。

  • B. 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明；   

    优点：通用，对应用透明，改造少。   

    缺点：实现难度大，有二次转发性能损失。

拆分原则

  1. 尽量不拆分，架构是进化而来，不是一蹴而就。(SOA)
  2. 最大可能的找到最合适的切分维度。
  3. 由于数据库中间件对数据Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取  尽量少使用多表Join -尽量通过数据冗余，分组避免数据垮库多表join。
  4. 尽量避免分布式事务。
  5. 单表拆分到数据1000万以内。

分库分表的难点

垂直分库带来的问题和解决思路：

跨库 join 的问题

在拆分之前，系统中很多列表和详情页所需的数据是可以通过 sql join 来完成的。而拆分后，数据库可能是分布式在不同实例和不同的主机上，join 将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库 join 的。那该怎么办呢？首先要考虑下垂直分库的设计问题，如果可以调整，那就优先调整。如果无法调整的情况，下面笔者将结合以往的实际经验，总结几种常见的解决思路，并分析其适用场景。

跨库 Join 的几种解决思路

全局表

所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库 join 查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。

字段冗余

这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免 join 查询。

举个电商业务中很简单的场景：

“订单表”中保存“卖家 Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。

字段冗余能带来便利，是一种“空间换时间”的体现。但其适用场景也比较有限，比较适合依赖字段较少的情况。最复杂的还是数据一致性问题，这点很难保证，可以借助数据库中的触发器或者在业务代码层面去保证。当然，也需要结合实际业务场景来看一致性的要求。就像上面例子，如果卖家修改了 Name 之后，是否需要在订单信息中同步更新呢？

数据同步

定时 A 库中的 tab_a 表和 B 库中 tbl_b 有关联，可以定时将指定的表做同步。当然，同步本来会对数据库带来一定的影响，需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。笔者曾经在项目中是通过 ETL 工具来实施的。

系统层组装

在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。说起来很容易，但实践起来可真没有这么简单，尤其是数据库设计上存在问题但又无法轻易调整的时候。

具体情况通常会比较复杂。下面笔者结合以往实际经验，并通过伪代码方式来描述。

简单的列表查询的情况

改进前

伪代码很容易理解，先获取“我的提问列表”数据，然后再根据列表中的 UserId 去循环调用依赖的用户服务获取到用户的 RealName，拼装结果并返回。

有经验的读者一眼就能看出上诉伪代码存在效率问题。循环调用服务，可能会有循环 RPC，循环查询数据库…不推荐使用。再看看改进后的：

改进后

这种实现方式，看起来要优雅一点，其实就是把循环调用改成一次调用。当然，用户服务的数据库查询中很可能是 In 查询，效率方面比上一种方式更高。（坊间流传 In 查询会全表扫描，存在性能问题，传闻不可全信。其实查询优化器都是基本成本估算的，经过测试，在 In 语句中条件字段有索引的时候，条件较少的情况是会走索引的。这里不细展开说明，感兴趣的朋友请自行测试）。

小结 简单字段组装的情况下，我们只需要先获取“主表”数据，然后再根据关联关系，调用其他模块的组件或服务来获取依赖的其他字段（如例中依赖的用户信息），最后将数据进行组装。

通常，我们都会通过缓存来避免频繁 RPC 通信和数据库查询的开销。

列表查询带条件过滤的情况

在上述例子中，都是简单的字段组装，而不存在条件过滤。看拆分前的 SQL：

拆分前

这种连接查询并且还带条件过滤的情况，想在代码层面组装数据其实是非常复杂的（尤其是左表和右表都带条件过滤的情况会更复杂），不能像之前例子中那样简单的进行组装了。试想一下，如果像上面那样简单的进行组装，造成的结果就是返回的数据不完整，不准确。

有如下几种解决思路：

  1. 查出所有的问答数据，然后调用用户服务进行拼装数据，再根据过滤字段 state 字段进行过滤，最后进行排序和分页并返回。

    这种方式能够保证数据的准确性和完整性，但是性能影响非常大，不建议使用。

  2. 查询出 state 字段符合 / 不符合的 UserId，在查询问答数据的时候使用 in/not in 进行过滤，排序，分页等。过滤出有效的问答数据后，再调用用户服务获取数据进行组装。

    这种方式明显更优雅点。笔者之前在某个项目的特殊场景中就是采用过这种方式实现。

跨库事务（分布式事务）的问题

按业务拆分数据库之后，不可避免的就是“分布式事务”的问题。以往在代码中通过 spring 注解简单配置就能实现事务的，现在则需要花很大的成本去保证一致性。这里不展开介绍，

感兴趣的读者可以自行参考《分布式事务一致性解决方案》，链接地址：

http://www.infoq.com/cn/articles/solution-of-distributed-system-transaction-consistency

垂直分库总结和实践建议

本篇中主要描述了几种常见的拆分方式，并着重介绍了垂直分库带来的一些问题和解决思路。读者朋友可能还有些问题和疑惑。

  1. 我们目前的数据库是否需要进行垂直分库？

    根据系统架构和公司实际情况来，如果你们的系统还是个简单的单体应用，并且没有什么访问量和数据量，那就别着急折腾“垂直分库”了，否则没有任何收益，也很难有好结果。

    切记，“过度设计”和“过早优化”是很多架构师和技术人员常犯的毛病。

  2. 垂直拆分有没有原则或者技巧？

    没有什么黄金法则和标准答案。一般是参考系统的业务模块拆分来进行数据库的拆分。比如“用户服务”，对应的可能就是“用户数据库”。但是也不一定严格一一对应。有些情况下，数据库拆分的粒度可能会比系统拆分的粒度更粗。笔者也确实见过有些系统中的某些表原本应该放 A 库中的，却放在了 B 库中。有些库和表原本是可以合并的，却单独保存着。还有些表，看起来放在 A 库中也 OK，放在 B 库中也合理。

如何设计和权衡，这个就看实际情况和架构师 / 开发人员的水平了。

  1. 上面举例的都太简单了，我们的后台报表系统中 join 的表都有 n 个了，

分库后该怎么查？

有很多朋友跟我提过类似的问题。其实互联网的业务系统中，本来就应该尽量避免 join 的，如果有多个 join 的，要么是设计不合理，要么是技术选型有误。请自行科普下 OLAP 和 OLTP，报表类的系统在传统 BI 时代都是通过 OLAP 数据仓库去实现的（现在则更多是借助离线分析、流式计算等手段实现），而不该向上面描述的那样直接在业务库中执行大量 join 和统计。

由于篇幅关系，下篇中我们再继续细聊“水平分库分表”相关的话题。

案例分析

场景一

建立一个历史his系统，将公司的一些历史个人游戏数据保存到这个his系统中，主要是写入，还有部分查询，读写比约为1:4；由于是所有数据的历史存取，所以并发要求比较高； 

分析： 历史数据 写多都少 越近日期查询越频繁？ 什么业务数据？用户游戏数据 有没有大规模分析查询？ 数据量多大？ 保留多久？ 机器资源有多少？

方案1：按照日期每月一个分片 带来的问题：1.数据热点问题（压力不均匀） 方案2：按照用户取模，  –by Jerome 就这个比较合适了 带来的问题：后续扩容困难 方案3：按用户ID范围分片（1-1000万=分片1，xxx） 带来的问题：用户活跃度无法掌握，可能存在热点问题

场景二

建立一个商城订单系统，保存用户订单信息。

分析： 电商系统 一号店或京东类？淘宝或天猫？ 实时性要求高 存在瞬时压力 基本不存在大规模分析 数据规模？ 机器资源有多少？ 维度？商品？用户？商户？

方案1：按照用户取模， 带来的问题：后续扩容困难

方案2：按用户ID范围分片（1-1000万=分片1，xxx） 带来的问题：用户活跃度无法掌握，可能存在热点问题

方案3：按省份地区或者商户取模 数据分配不一定均匀

场景3

上海公积金，养老金，社保系统

分析： 社保系统 实时性要求不高 不存在瞬时压力 大规模分析？ 数据规模大 数据重要不可丢失 偏于查询？

方案1：按照用户取模， 带来的问题：后续扩容困难

方案2：按用户ID范围分片（1-1000万=分片1，xxx） 带来的问题：用户活跃度无法掌握，可能存在热点问题

方案3：按省份区县地区枚举 数据分配不一定均匀

数据库优化

思考

如何设计或优化千万级别的大表？此外无其他信息，个人觉得这个话题有点范，就只好简单说下该如何做，对于一个存储设计，必须考虑业务特点，收集的信息如下：

  1. 数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节；

  2. 数据项：是否有大字段，那些字段的值是否经常被更新；

  3. 数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等；

  4. 数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中；

  5. SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？

  6. 预计大表及相关联的SQL，每天总的执行量在何数量级？

  7. 表中的数据：更新为主的业务 还是 查询为主的业务 ？

  8. 打算采用什么数据库物理服务器，以及数据库服务器架构？

  9. 并发如何？

  10. 存储引擎选择InnoDB还是MyISAM？

大致明白以上10个问题，至于如何设计此类的大表，应该什么都清楚了！

至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈

另外对优化索引结构去解决性能问题的话，建议优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是， 索引已经创建的非常好，若是读为主，可以考虑打开query_cache，以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

案例

###　案列一

我现在的公司有三张表，是5亿的数据，每天张表每天的增量是100w

每张表大概在10个columns左右

下面是我做的测试和对比

  1. 首先看engine,在大数据量情况下，在没有做分区的情况下 mysiam比innodb在只读的情况下，效率要高13％左右

  2. 在做了partition之后，你可以去读一下mysql的官方文档，其实对于partition，专门是对myisam做的优化，对于innodb，所有的数据是存在ibdata里面的，所以即使你可以看到schema变了，其实没有本质的变化

    在分区出于同一个physical disk下面的情况下，提升大概只有1％

    在分区在不同的physical disk下，我分到了三个不同的disks下，提升大概在3％，其实所谓的吞吐量，由很多因素决定的，比如你的explain parition时候可以看到，record在那一个分区，如果每个分区都有，其实本质上没有解决读的问题，这样只会提升写的效率。

    另外一个问题在于，分区，你怎么分，如果一张表，有三个column都是经常被用于做查询条件的，其实是一件很悲惨的事情，因为你没有办法对所有的sql做针对性的分区，如果你只是如mysql官方文档上说的，只对时间做一个分区，而且你也只用时间查询的话，恭喜你

  3. 表主要用来读还是写，其实这个问题是不充分的，应该这样问，你在写入的时候，同时并发的查询多么？我的问题还比较简单，因为mongodb的shredding支持不能，在crush之后，还是回到mysql，所以在通常情况下，9am－9pm，写入的情况很多，这个时候我会做一个view，view是基于最近被插入或者经常被查询的，通过做view来分离读取，就是说写是在table上的，读在进行逻辑判断前是在view上操作的

  4. 做一些archive table，比如先对这些大表做很多已有的统计分析，然后通过已有的分析＋增量来解决

  5. 如果你用mysiam，还有一个问题你要注意，如果你的.configure的时候，加了一个max index length参数的时候，当你的record数大于制定长度的时候，这个index会被disable

    案列二

任何偏离业务场景的优化都是耍流氓，如果是订单表，主要通过订单id来查询订单信息，则可以对这样的表 进行垂直分库，每个库表容量500万条，按订单号维度 给拆分到多个库，而在查询的时候，使用订单号查询，通过某个业务规则，直接定位到要查询的目标库。或者通过用户ID 、日期维度 进行分库，但是千万要注意，查询时携带 分库的条件。 如果是CRM系统 ，不直接使用订单号直接查询，而是一个范围查询，返回一个列表集合，而你还继续执着于分库分表就能解决你的性能问题，这样你要对各个库的查询结果集进行union，数据库的性能非但不能提高反而会适得其反！

解决方案

首先，任何优化，都需要你了解你的业务，了解你的数据。

QPS要到多少？- 带宽及存储够的情况下，单机几千QPS妥妥的。

读写比例如何？- 读多写少和写多读少，优化方法是有很大差别的。设置于只读场景，果断压缩。

数据是否快速增长？- 基本就是QPS的要求。

数据及服务的SLA要到多少？- 数据需不需要强一致？HA做到什么程度？

诸如此类。

不同的场景有不同的侧重，解决方案是不同的。而对于一些典型的场景可能会有成熟的解决方案。

题主已注明“千万级”，因此以下假设题主为最常见的场景：大量数据，QPS要求高，读多写少，数据快速增长，SLA要求高。

其次，说优化的方法。

主要从三个维度说：Why, How, When。

  1. sql vs nosql

    有些跑题，但也是很重要的一方面。

    Why: nosql天生分布，而且大多针对某种类型的数据、某种使用场景做过优化。

    比如大批量的监控数据，用mysql存费时费力，可以选择mongo，甚至时间序列数据库，存取会有量级提升。

    How: 找对应解决方案。

    When: 有足够诱惑 - 针对使用场景，有成熟解决方案，效率获得大量提升。

  2. 优化shema、sql语句+索引

    Why: 再好的MySQL架构也扛不住一个频繁的垃圾查询。不合理的schema设计也会导致数据存取慢。索引的作用不必多说，但如innodb下，错的索引带来的可能不只是查询变慢而已。

    How: 设计阶段就需要预计QPS及数据规模，参考业务场景对数据的要求，合理设计表结构（参考mysql在线DDL问题），甚至违反设计范式做到适当冗余。生产环境分析慢日志，优化语句。索引的设计需要知道索引是怎么用的，比如innodb的加锁机制。

    When: 这个不仅仅是第一个要考虑的，而应该是需要持续去优化的。特别是要参考业务。但实际环境中如果是这个的问题，那一般比较幸运了，因为一般已经优化过很多了。实际中遇到的一般是更深的问题。

  3. 缓存

    缓存没有那么简单。

    缓存对于应用不是完全透明的，除非你用Django这种成熟框架，而且缓存粒度很大，但实际。。。像python，最少也得加几个装饰器。

    如何保证缓存里面的数据是始终正确的？写数据前失效缓存还是写数据后？

    缓存挂了或者过冷，流量压到后端mysql了怎么办？

    缓存也不是万能的。写多读少，命中率会很低。

    How: memcache用做缓存，redis用于需要持久化的场景。（redis能不能完全取代memcache？呵呵。。）

    还可以使用mysql自带的query cache，对应用基本完全透明。但会受限于本机。而且只缓存查询结果，mc和redis可以缓存一些加工后的数据。

    而且数据量大、QPS大的情况下，也需要考虑分片及HA的问题。如果有一个数据过热，把一个节点压垮了怎么办？

    When: 基本上大多数读多写少的场景都能用，写多的情况下可能需要考虑考虑。

  4. 复制及读写分离（做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护）

    Why: 这个其实是大多数场景下都必须的。因为复制可以实现备份、高可用、负载均衡。就算嫌麻烦不做负载均衡，那备份下总是要的吧？既然已经备份了，何不加个LVS+HAProxy做下HA？顺便稍微修改下应用，读写分离也就成了。

    How: 节点少的情况下，主备。前面加Keepalived+HAProxy等组件，失效自动切换。读写分离可能需要修改下应用。

    节点多的情况下，一是考虑多级备份，减轻主的压力。其次可以引入第三方组件，接管主节点的备份工作。

    主主不是很推荐。一是需要考虑数据冲突的情况，比如错开id，同时操作数据后冲突解决。其次如果强一致会导致延迟增加，如果有节点挂了，需要等到超时才返回。

    When: 主备几乎大多数场景。甚至不论数据大小。高可用对应用透明，为啥不用?主主麻烦，建议先用切分。

  5. 切分

    包括垂直切分和水平切分，实现方式上又包括分库、分表。

    虽然有些难度，但还是推荐常用的。

    Why: 垂直切分保证业务的独立性，防止不同业务争抢资源，毕竟业务是有优先级的。

    水平切分主要用于突破单机瓶颈。除了主主外，只有切分能真正做到将负载分配下去。

    切分后也可对不同片数据进行不同优化。如按时间切分，超过一定时间数据不允许修改，就可以引入压缩了，数据传输及读取减少很多。

    How: 根据业务垂直切分。业务内部分库、分表。一般都需要修改应用。除分表外，其余实现不是很复杂。有第三方组件可用，但通用高效又灵活的方式，还是自己写client。

    When: 垂直切分一般都要做，只不过业务粒度大小而已。

    分库有是经常用的，就算当前压力小，也尽量分出几个逻辑库出来。等规模上去了，很方便就迁移扩展。

    水平拆分有一定难度，但如果将来一定会到这个规模，又可能用到，建议越早做越好。因为对应用的改动较大，而且迁移成本高。

综上，数据库设计要面向现代化，面向世界，面向未来。。。

在一般运维的角度来看，我们什么情况下需要考虑分库分表？

首先说明，这里所说的分库分表是指把数据库数据的物理拆分到多个实例或者多台机器上去，而不是类似分区表的原地切分。

原则零：能不分就不分。

是的，MySQL 是关系数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而分，去做其他力所能及的事情吧，例如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等等。所有分库分表的前提是，这些你已经尽力了。

  • 原则一：数据量太大，正常的运维影响正常业务访问。

这里说的运维，例如：

（1）对数据库的备份。如果单表或者单个实例太大，在做备份的时候需要大量的磁盘IO或者网络IO资源。例如1T的数据，网络传输占用50MB的时候，需要20000秒才能传输完毕，在此整个过程中的维护风险都是高于平时的。我们在Qunar的做法是给所有的数据库机器添加第二块网卡，用来做备份，或者SST，Group Communication等等各种内部的数据传输。1T的数据的备份，也会占用大量的磁盘IO，如果是SSD还好，当然这里忽略某些厂商的产品在集中IO的时候会出一些BUG的问题。如果是普通的物理磁盘，则在不限流的情况下去执行xtrabackup，该实例基本不可用。

（2）对数据表的修改。如果某个表过大，对此表做DDL的时候，MySQL会锁住全表，这个时间可能很长，在这段时间业务不能访问此表，影响甚大。解决的办法有类似腾讯游戏DBA自己改造的可以在线秒改表，不过他们目前也只是能添加字段而已，对别的DDL还是无效；或者使用pt-online-schema-change，当然在使用过程中，它需要建立触发器和影子表，同时也需要很长很长的时间，在此操作过程中的所有时间，都可以看做是风险时间。把数据表切分，总量减小，有助于改善这种风险。

（3）整个表热点，数据访问和更新频繁，经常有锁等待，你又没有能力去修改源码，降低锁的粒度，那么只会把其中的数据物理拆开，用空间换时间，变相降低访问压力。

  • 原则二：表设计不合理，需要对某些字段垂直拆分

这里举一个例子，如果你有一个用户表，在最初设计的时候可能是这样：

1
2
3
4
5
6

table :users
id bigint 用户的ID
name varchar 用户的名字
last_login_time datetime 最近登录时间
personal_info text 私人信息
xxxxx 其他信息字段。

一般的users表会有很多字段，我就不列举了。如上所示，在一个简单的应用中，这种设计是很常见的。但是：

设想情况一：你的业务中彩了，用户数从100w飙升到10个亿。你为了统计活跃用户，在每个人登录的时候都会记录一下他的最近登录时间。并且的用户活跃得很，不断的去更新这个login_time，搞的你的这个表不断的被update，压力非常大。那么，在这个时候，只要考虑对它进行拆分，站在业务的角度，最好的办法是先把last_login_time拆分出去，我们叫它 user_time。这样做，业务的代码只有在用到这个字段的时候修改一下就行了。如果你不这么做，直接把users表水平切分了，那么，所有访问users表的地方，都要修改。或许你会说，我有proxy，能够动态merge数据。到目前为止我还从没看到谁家的proxy不影响性能的。

设想情况二：personal_info这个字段本来没啥用，你就是让用户注册的时候填一些个人爱好而已，基本不查询。一开始的时候有它没它无所谓。但是到后来发现两个问题，一，这个字段占用了大量的空间，因为是text嘛，有很多人喜欢长篇大论地介绍自己。更糟糕的是二，不知道哪天哪个产品经理心血来潮，说允许个人信息公开吧，以方便让大家更好的相互了解。那么在所有人猎奇窥私心理的影响下，对此字段的访问大幅度增加。数据库压力瞬间抗不住了，这个时候，只好考虑对这个表的垂直拆分了。

  • 原则三：某些数据表出现了无穷增长

例子很好举，各种的评论，消息，日志记录。这个增长不是跟人口成比例的，而是不可控的，例如微博的feed的广播，我发一条消息，会扩散给很多很多人。虽然主体可能只存一份，但不排除一些索引或者路由有这种存储需求。这个时候，增加存储，提升机器配置已经苍白无力了，水平切分是最佳实践。拆分的标准很多，按用户的，按时间的，按用途的，不在一一举例。

  • 原则四：安全性和可用性的考虑

这个很容易理解，鸡蛋不要放在一个篮子里，我不希望我的数据库出问题，但我希望在出问题的时候不要影响到100%的用户，这个影响的比例越少越好，那么，水平切分可以解决这个问题，把用户，库存，订单等等本来同统一的资源切分掉，每个小的数据库实例承担一小部分业务，这样整体的可用性就会提升。这对Qunar这样的业务还是比较合适的，人与人之间，某些库存与库存之间，关联不太大，可以做一些这样的切分。

  • 原则五：业务耦合性考虑

这个跟上面有点类似，主要是站在业务的层面上，我们的火车票业务和烤羊腿业务是完全无关的业务，虽然每个业务的数据量可能不太大，放在一个MySQL实例中完全没问题，但是很可能烤羊腿业务的DBA 或者开发人员水平很差，动不动给你出一些幺蛾子，直接把数据库搞挂。这个时候，火车票业务的人员虽然技术很优秀，工作也很努力，照样被老板打屁股。解决的办法很简单:惹不起，躲得起。

20条规则摘要如下：

规则1：一般情况可以选择MyISAM存储引擎，如果需要事务支持必须使用InnoDB存储引擎。

规则2：命名规则。

规则3：数据库字段类型定义

经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段，如用TIMESTAMP(4个字节，最小值1970-01-01 00:00:00)代替Datetime（8个字节，最小值1001-01-01 00:00:00）,通过整型替代浮点型和字符型

变长字段使用varchar，不要使用char

对于二进制多媒体数据，流水队列数据(如日志)，超大文本数据不要放在数据库字段中

规则4：业务逻辑执行过程必须读到的表中必须要有初始的值。避免业务读出为负或无穷大的值导致程序失败

规则5：并不需要一定遵守范式理论，适度的冗余，让Query尽量减少Join

规则6：访问频率较低的大字段拆分出数据表。有些大字段占用空间多，访问频率较其他字段明显要少很多，这种情况进行拆分，频繁的查询中就不需要读取大字段，造成IO资源的浪费。

规则7： 水平分表，这个我还是建议 三思，搞不好非但不能提升性能反而多了很多的join和磁盘IO，开发起来也麻烦，有很多的业务就是要求一次查询大部分的字段 看你业务场景了。大表可以考虑水平拆分。大表影响查询效率，根据业务特性有很多拆分方式，像根据时间递增的数据，可以根据时间来分。以id划分的数据，可根据id%数据库个数的方式来拆分。

规则8：业务需要的相关索引是根据实际的设计所构造sql语句的where条件来确定的，业务不需要的不要建索引，不允许在联合索引（或主键）中存在多于的字段。特别是该字段根本不会在条件语句中出现。

规则9：唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引，不能唯一确定一条记录，为了提高查询效率建普通索引。

规则10：业务使用的表，有些记录数很少，甚至只有一条记录，为了约束的需要，也要建立索引或者设置主键。

规则11：对于取值不能重复，经常作为查询条件的字段，应该建唯一索引(主键默认唯一索引)，并且将查询条件中该字段的条件置于第一个位置。没有必要再建立与该字段有关的联合索引。

规则12：对于经常查询的字段，其值不唯一，也应该考虑建立普通索引，查询语句中该字段条件置于第一个位置，对联合索引处理的方法同样。

规则13：业务通过不唯一索引访问数据时，需要考虑通过该索引值返回的记录稠密度，原则上可能的稠密度最大不能高于0.2，如果稠密度太大，则不合适建立索引了。

规则14：需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

注意：索引的顺势不正确也可能导致严重的后果。

规则15：表中的多个字段查询作为查询条件，不含有其他索引，并且字段联合值不重复，可以在这多个字段上建唯一的联合索引，假设索引字段为 (a1,a2,…an),则查询条件(a1 op val1,a2 op val2,…am op valm)m<=n,可以用到索引，查询条件中字段的位置与索引中的字段位置是一致的。

规则16：联合索引的建立原则(以下均假设在数据库表的字段a,b,c上建立联合索引(a,b,c))。

规则17：重要业务访问数据表时。但不能通过索引访问数据时，应该确保顺序访问的记录数目是有限的，原则上不得多于10。

规则18：合理构造Query语句，慢SQL监控，检查是否有大量的的子查询和关联查询 嵌套查询等，尽量避免使用这些查询，使用连接（JOIN）来代替子查询(Sub-Queries)，使用联合(UNION)来代替手动创建的临时表。

规则19：应用系统的优化。

规则20：可以结合redis，memcache等缓存服务，把这些复杂的sql进行拆分，充分利用二级缓存，减少数据库IO操作。对数据库连接池，mybatis，hiberante二级缓存充分利用上。尽量使用顺序IO代替随机IO。合理使用索引，尽量避免全表扫描。

参考文献