业务场景：几千万数据量的工单表如何快速优化

系统简介

• 系统名：邮件客服系统

• 介绍：一个 SaaS（通过网络提供软件服务）系统，但是大客户只有两三家，最主要的客户是一家大型媒体集团。

• 主要功能：

  • 对接客户的邮件服务器，自动收取发到几个特定客服邮箱的邮件，每收到一封客服邮件，就自动生成一个工单。之后系统就会根据一些规则将工单分派给不同的客服专员处理。

  • 系统支持多租户，每个租户使用自己的数据库（MySQL）。

• 数据量：这家媒体集团客户两年多产生了近2000万的工单，工单的操作记录近1亿。

• 问题描述：

  • 平时客服在工单页面操作时，打开或者刷新工单列表需要10秒钟左右。

  • 该客户当时做了一个业务上的变更，增加了几个客服邮箱，然后把原来不进入邮件客服系统的一些客户邮件的接收人改为这几个新增加的客服邮箱，并接入这个系统。发生这个业务变更以后，工单数量急剧增长，工单列表打开的速度越来越慢。

• 问题分析：

  客户进行业务变更后的数据特征如下：

  • 工单表已经达到3000万条数据。

  • 工单表的处理记录表达到1.5亿条数据。

  • 工单表每日以10万的数据量在增长。

• 问题解决时间：1 周

• 已使用过的优化手段：

  在客户提出需求之前，项目组已经通过优化表结构、业务代码、索引、SQL语句等办法来提高系统响应速度，系统最终支撑起了3000万数据的表查询。

• 优化方案目标：

  因为给的时间太少了，所以也不太可能去做一些大的架构变动，项目组的预期是先用改动最小的临时性方案让客服可以正常工作。

关键表结构与查询

工单表ticket

工单表ticket中的关键字段如下表：

工单表最主要的几个查询语句如下：

• 客服查询无处理人的工单：“Where assignedUserID = ?”

• 客服获取分派给自己的工单：“Where status in（…）and assignedUserID = ?”。

• 客服组长查看自己组的工单：“Where assignedUserGroupID = ?”。

• 客服查询特定客户的工单：“Where consumerEmail = ?”

业务分析

系统主要业务流程如下：

1. 系统从邮件服务器同步到邮件以后，创建一个工单，createdTime就是工单创建的时间。

2. 客服先去查询无处理人的工单，然后把工单分派给自己。

3. 客服处理工单，每处理一次，系统自动增加一条处理记录。

4. 客服处理完工单以后，将工单状态改为“关闭”。

关键特征：

• 关闭后的工单，客服查询的概率就很低。

• 对于那些关闭超过一个月的工单，基本上一年都打开不了几次。

解决思路

基本的思路是增加一个状态：归档。

首先将关闭超过一个月以上的工单自动转为“归档”状态，然后将数据库分为两个区，所有“归档”状态的工单存放在一个区，所有非“归档”状态的工单存放在另外一个区，最后在所有的查询语句中加一个条件，就是状态不等于“归档”。

简单估算一下：客服频繁操作的工单基本上都是1个月内的工单，按照后期一天10万来算，也就是300万的数据，这样数据库的非归档区基本就没什么压力了。

大致方案如下：

• 新建一个数据库，然后将1个月前已经完结的工单数据都移动到这个新的数据库。这个数据库就叫冷库，因为里面基本是冷数据（当然，叫作归档数据库也可以），之后极少被访问。

• 当前的数据库保留正常处理的较新的工单数据，这是热库。

这样处理后，因为客服查询的基本是近期常用的数据，大概只有300万条，性能就基本没问题了。即使因为查询频繁，或者几个客服同时查询，也不会再像之前那样出现数据库占满CPU、整个系统几乎宕机的情况了。