订单表优化方案
1 背景
随着用户不断下单，DB订单表和订单附属表的单表记录数过大，影响到前端和管理系统拉取订单列表的性能。单表最大多少行合适与具体业务有关，难以下定论，但一般推荐不要超过1千万行，之后单表的性能下降会比较明显。
本文档整理了数据库大表优化的一些常用思路的原理，最后针对订单表提出优化方案。
2 常用思路
- 单表分区
- 大表分表
- 业务分库
- 读写分离和集群
- 热点缓存
- 用ES代替DB
2.1 单表分区
什么是分区？
就是将一张表的单个大文件，按一定逻辑拆分成多个物理的区块文件。对于应用程序来说，还是一张整表；但底层实际上是由多个物理区块组成。目前主流的DB如Oracle、MySql等都有成熟的方案支持分区
MySql支持哪些分区类型？
- range分区：根据key的范围来分区，比如日志表，可以按天或按月分区
- list分区：根据key的枚举值分区，比如以订单状态为key，待付款、待发货、待收货等分别建立一个分区
- hash分区：给定分区数，DB根据key的hash值将记录分散到各个分区，比如以用户ID为key，将订单表打散到各个分区
- key分区：类似hash分区
- 复合分区：Oracle支持丰富的复合分区方案，而MySql相对就简单些，只有range和list分区支持子分区，而且子分区必须是hash或Key分区。
MySql的分区限制？
- MySql（其他DB也是类似的）为保证唯一索引的效率，要求分区字段必须包含在每一个唯一索引中。比如某个订单表以自增ID为主键，订单ID为唯一索引，用户ID为普通索引，如果要以用户ID为分区字段建立分区，则必须将主键和唯一索引都修改为组合索引，加上用户ID。这里就得注意一个问题了，对于组合索引，where条件如果只包含组合索引的个别字段时，必须是从前往后，否则查询语句不会走索引。比如有一个组合索引是订单ID+用户ID，如果以用户ID为查询条件，无法使用这个索引；而用订单ID查询，可以使用该索引，这是索引的存储数据结构决定的。
- 5.6.7以前版本单表最大分区数1024，5.6.7及之后版本最大8192。注意分区数太小优化不明显，分区数太大则会增加IO系统额外消耗反而降低性能。1024以上的分区数，恐怕只适合日志类的冷热分离明显的表，这类表往往只查询最近的几个分区；上千张表并发读写的话，io会让人怀疑人生。
MySql如何对现有表分区或修改分区参数？
- MySql支持ALTER TABLE动态创建或修改表分区，这种方式对线上业务有压力。
- 替代方案是创建临时表，将主表数据导入临时表，再将临时表切换为主表。由于线上数据是实时变化的，这种方式需要处理数据最终一致性的问题。
分区的优势？
- 对应用程序完全透明、且不明显增加DB负担。
- 以分区字段作为查询条件，DB会先确定目标可能的分区，再在分区内完成查询，可以极大的减小查询的数据量。
- 按天或按月分区的日志表，需要删除旧日志时只需要删除对应的分区即可，简单高效。
- 分区文件可以存储在不同的磁盘，如count(1)类操作支持并发
接下来我们来具体思考针对订单表，如何优化：
示例一：以用户ID做HASH分区
之前runner指出用户对订单表的查询，都基于用户ID的，每个用户只能拉取自己的订单信息。所以对于订单表，可以以用户ID作为分区字段将订单表分区打散；这就要求原有的主键(自增ID)和唯一索引(订单ID)都修改为组合索引，分别是自增ID+用户ID、订单ID+用户ID。保留原有的用户ID的普通索引。比如我们创建4个分区。
- 于是拉取某个用户的所有订单的查询语句，只会定位到单个分区，语句需要处理的数据量是原来的1/4。
- 使用订单ID拉取订单记录的查询，需要加上用户ID作为条件，这样查询语句可以先定位到单个分区，再走订单ID+用户ID的组合唯一索引，数据量也优化到原来的1/4。
- 覆盖多个分区的查询或者是扫表操作，由于原来的大文件被拆分成多个小文件，硬盘IO上会更友好，理论上有优化。
示例二：组合分区，range自增ID，hash用户ID
这个方案可以进一步打散数据，比如range出4个分区，每个range分区再做4个hash子分区。
- 通过用户ID拉取订单列表的情况，会在4个子分区处理，每个子分区的数据量我们粗略认为都是1/16，则一次查询涉及的数据量还是 1/4(4 * 1/16)
- 通过订单ID拉取订单记录的查询，则可以优化成先找出range分区，再找hash分区，最终只需要在其中一个子分区内查找数据，数据量变成原来的 1/16
采用此方案，主键和唯一索引需要扩展到3个字段。
示例三：组合分区，list订单ID，hash用户ID
电商的订单展示，有个特点，分待付款、待发货、待收货及全部订单等，很多时候用户是单独拉取待发货、待收货的订单页面的，这时我们可以将订单表按状态做一次list分区，再以用户ID hash出子分区。
- 通过用户ID拉取订单列表的情况，与前两个方案一致。
- 通过订单ID拉取订单记录的情况，只会检索符合状态的订单数据集，考虑到大部分订单是已完成状态，而拉取待发货、待收货订单的操作可能涉及的数据集将更小
同方案二，采用此方案也需要将主键和唯一索引扩展到3个字段。
2.2 大表分表
分表的一个应用场景是替代分区，预先创建多个表名不同但表结构一致的表，并给每个表编号，应用程序在写或读之前先用ID取模等方式得到表编号，从而实现单表分区。
本章我们侧重于用分表来扩展分区的功能，我们来看某金融交易平台的实际案例，将订单表划分成多个小表来分散不同的业务请求：
- 临时订单表：存储尚未出票的订单，主要是下单和出票系统对这个表进行并发读写
- 订单概要表：用于订单列表展示的概要
- 订单详情表：展示订单详情时从这个表拉
- 订单概要历史表、订单详情历史表：根据业务特点，超过一定时期的订单很少会请求到，所以可以挪到历史表中，从而保证主表的记录数不会太大
针对电商的订单表，我们也可以有类似的思路，比如用户拉取订单数据时往往分待付款、待发货、待收货、全部订单等，除了全部订单列表外，前端是按不同阶段来拉取和展示订单的，所以我们可以将不同阶段的订单移到不同的表中，降低单表的记录数来提高查询效率。
再比如历史表，前端拉取1个月前甚至1年前的已完成订单的机会是不多的，可以将一定时间以前的记录移到历史表去。
2.3 业务分库
随着业务量进一步增长，单个DB实例已经无法支撑大量的用户请求时，可以考虑根据业务分库，甚至是对单个业务进行细粒度的分库，将不同的请求分散到不同的库去处理，硬件成本换性能。目前我们平台后台与前端业务部门的后台系统DB是分离的，这就是个分库的案例。
有了前面的分区、分表，分库的思路应该很好理解，就目前而言，我暂时没有看到我们有进一步分库的需求，线上业务都在公有云，一般的性能增长需求可以先通过快速的单库扩容实现。如果后续需要，可以先考虑将商品中心、用户中心等强业务相关的子系统分离；业务量继续增长时，还可以考虑更细粒度的，比如就订单表而言，也可以将各个子表分离到不同的DB去。当然，这些操作对我们的应用开发提出更高的适配要求，业界也有成熟的如mycat等中间件方案。
2.4 读写分离和集群
读写分离和DB集群是互联网架构常用的方案，我们也已经实现了一部分，这种方案主要是为了解决读远大于写的情况，可以是一主一从、一主多从、多主多从等，本质上是将数据拷贝多份，由多个DB实例同时负载前端应用，以硬件成本换性能。该方案在提升读的性能、HA等方面效果明显，但并未真正解决订单表单表过大的问题，这里就不展开说了。
2.5 热点缓存
一个高性价比的缓存设计，适合更新少、读取多的数据，比如商品，大量的用户请求会拉取商品信息，真正下单的会少很多，缓存商品信息可以拦截下大量的重复的请求。至于订单信息，每个用户只能拉取自己的订单，每个订单被访问到的次数是很少的，所以为订单创建缓存的性价比就显得很低。这里也不展开讨论了。
2.6 用ES代替DB
参考Fylos推荐的这篇博文：http://www.sohu.com/a/327627159_315839
京东到家的订单系统主要依赖ES集群来承担订单查询的压力，目前支撑10亿文档数和5亿的日均查询量。
3 订单表优化方案
3.1 业务分析
这是订单业务涉及的主要表的关系图，图中我整理了select/update涉及的where的主要条件字段(索引)，其中Ux表示唯一索引，Ix表示普通索引：
- 除order_info和order_sku表外，其他表都只需要一个字段索引，如order_product_attr表的所有查询均通过order_id字段筛选。
- order_sku表，只有在管理系统以product_id/sku_id为筛选条件时，才会用到I2/I3两个索引，其他所有查询均通过order_id筛选。
- order_info表查询涉及的筛选字段很多，上图我没列全。其中从前端传入的用户请求，都是以用户为维度的，带user_id。
目前我们的订单表记录数不到500万，远没到需要分库、建集群才能支撑性能的地步；而订单的查询频率并不高，性能主要受单表数据量太大限制，所以建缓存的意义也不大；至于用ES来代替DB查询，引入了新的数据节点和数据同步的需要，在分区和分表能解决问题的情况下，使用ES属于过度优化，没这必要。
所以接下来我们直接讨论怎么分区、分表。此外，分区对业务代码是无感知的，分表需要修改业务逻辑，所以推荐优先选择分区。
| 序号 | 表名 | 记录数 |
|---|---|---|
| 1 | order_info | 4507745 |
| 2 | order_sku | 4885235 |
| 3 | order_product_attr | 25450856 |
| 4 | order_sku_epay | 5772024 |
| 5 | order_product_ext_info | 2927387 |
| 6 | order_product_set_info | 14677 |
| 7 | order_package_info | 1139441 |
| 8 | package_sku_info | 213238 |
从上表来看，前4张表的记录数比较大，也是大部分请求集中的表，需要优化。
3.2 分区方案(order_sku/order_product_attr/order_sku_epay)
分区思路：以order_id为分区键；以当前数据量为基础，每个分区记录数50万以内。
| 表名 | 分区数 | 每个分区的平均记录数 |
|---|---|---|
| order_sku | 16 | 30.5万 |
| order_product_attr | 64 | 39.8万 |
| order_sku_epay | 16 | 36.1万 |
这个分区设计下，即使在我们的业务增长10倍之后，每个分区的记录数在400万左右，也能很好的支撑。当然，后面我们可能得考虑一些分表、分库的设计了。
存在的问题：管理系统单独以product_id/sku_id筛选时，无法锁定分区。考虑到这个请求不是很多，可以走普通索引。
3.2 历史表+分区方案(order_info)
order_info表的业务特点：
- 用户请求：请求参数都是带user_id的，可以按user_id分区，支持查历史数据
- 定时任务，有支付超时回滚、自动确认收货、支付中定时器(确认支付状态)、发货轮询、每日结算、发货失败自动退费、订单发货超时自动标志、热销榜等。这些定时任务基本是针对未完成订单或是近期已完成订单的，但筛选条件有订单状态、支付时间等，不适合分区。可以创建历史表，将非热点数据移除。
- 管理系统拉取订单，一些涉及全表数据的操作，除了堆硬件分库外，没有很好的解决方案。
结合上述业务特点，可以将order_info拆分为主表order_info和历史表order_info_his，同时order_info_his表可以按user_id分区，保证用户端拉取的速度。
主表order_info：
- 保留最近3个月的订单
- 数据量不大，暂时不需要分区。以后业务增长了，这个表可以考虑分区
- 热点数据 - 未结束的订单都在这个表，适合用户拉取未完成订单、后台结算统计等热点行为
历史表order_info_his：
- 主要是用户拉取历史订单和管理系统拉取订单列表，这里以用户为主，以user_id建立分区
代码逻辑变更：
- 用户拉待付款、待收货页面，只查主表
- 用户拉待发货页面，由于发货超时时间是手动配置的，部分测试商品没配置发货超时时间，不排除正式商品也有没配置的，所以这个页面的数据需要主表和历史表都拉取
- 其他用户拉取订单列表的页面：拉取两张表
- 定时器任务：需要分析下主表是否包含了全部数据
- 管理系统：订单管理页面涉及的数据量很大，全量拉取的耗时比较长，所以可以特殊处理，分两次拉，第1次拉取并展示主表的数据，但分页区不显示订单总数，第2次再拉取历史表数据进行汇总
