MES案例研究1 - 大表并发查询

某工厂是生产数码产品金属外壳的，每天近100万件的产量，随着圣诞节的临近，客户订单大量增加，但是生产却跟不上，经初步分析，发现问题发生在激光雕刻二维码的工位，由于镭雕机从MES取号的时间太长，造成生产的瓶颈。

该厂MES的主要功能是做生产追溯，包括：生产过程记录、关键工位检查、质量问题收集等。

在镭雕工位，客户端程序要从MES中查询得到对应机型的最小序列号，然后传给镭雕机。

查询SQL的核心逻辑为：

SELECT   MIN(serial_number) FROM   t_product_history t WHERE t.type = ‘PN1’ //产品类型 AND t.status = 1;    //产品状态，1表示尚未镭雕

通过ORACLE AWR可以得到此查询SQL在某个时间段的总执行时间。

此外，我们可以查询得到SQL_TEXT的执行次数。

两者相除，就得到此SQL的平均时间：竟然达到2秒！

更糟糕的是，查看客户端代码后，发现没有做并发处理，当现场二十多台镭雕机同时工作时，工人一旦发现响应慢，就会不断点击手动请求按钮，造成多并发，进而造成查询的执行时间更长。

接下来只有认真地分析t_product_history这个表的业务。

这个表是一个产品的生产历史记录表，整个生产过程中大约要经过近100个采集工位，每件产品经过每个工位时会在此表中新增一条记录，这样一天100万件产品就要新增近1亿条记录，表容量相当地大。

这个表也做了分区处理，是按月分区的，但是查询SQL的WHERE条件(产品类型和状态)并没有利用分区字段，所以系统会从索引中扫描记录，而索引没有做分区，而索引的容量也已经非常巨大了，这样的后果是每次查询都要扫描数百亿条记录，自然效率低了。

其实数据库里还有另一个表t_product_status，用于表示产品的状态，数据容量要小得多，但自从工单下发后，状态值就为1了，一直到完工变成状态2，所以不能从此表中得到尚未镭雕的最小序列号。

在不更改这两表功能的前提下，只有另辟蹊径了。

首先我们分析生产的特性：大批量少品种，每个工单对应一种产品，每个工单的量都非常大，一般都在几万至几十万的量，每个工单下发后，在镭雕工位做连续生产，中间不会跳序列号。

因此，我们可以把镭雕要取的最小序列号抽象成符合条件的工单的一个特征，一个中间量的指针值。

首先建一个工单的扩展属性表t_wo_pointer，定义以下属性：工单号，产品类型，状态，数量，最小序列号，当前指针值，当前序列号。

当工单下发时，在原存储过程中增加一个小的逻辑，即在此扩展表中增加一条记录，将状态置为1表示尚未镭雕，并计算得到最小序列号，并置指针值为0。

在镭雕工位，查询的SQL更改为：

SELECT   MIN(Current_sn), //当前序列号   Pointer         //当前指针值 FROM   t_wo_pointer WHERE t.type = ‘PN1’ //产品类型 AND t.status = 1;    //产品状态，1表示尚未镭雕

这样就查询得到了符合条件的最小序列号和指针值。

将序列号输出，然后将指针值加1并更新当前序列号，如果指针值=工单数量则将工单的状态改为2。

然后加上并发事务处理。

经过这番处理后，并没有改动业务逻辑，但是查询的时间从原来的2秒变成只有1.8微秒(要查询的表只有100多条记录)，当即解决了此处瓶颈，提升了8%的产能，帮助工厂顺利完成了圣诞节的订单。

我从此案例学到的教训是：

1.       大表查询要慎重。

2.       尽可能不要在历史记录表中执行生产现场控制的逻辑。

3.       大表做了分区以后，分区字段不一定会被查询语句利用到，而大表的索引不一定做了分区处理。

4.       连续数字、序列号可以考虑用指针表的方式处理，前提是要做好并发事务处理。