mysql语句优化的几种方法_秒杀应用的MySQL数据库优化，就是这么简单

关于闪购

随着双十一活动的持续开展，以及小米饥饿营销模式的兴起，“秒杀”也成为了一个热词。在一些活动中，热销产品会以惊人的速度售罄。比如我最近抢购的一款美图M4手机，在12点开售后1分钟内就被抢购一空。

闪购的实现

对于对数据库感兴趣的朋友来说，他们更关心的是如何高效的实现秒杀应用。淘宝之前在2013数据库大会上分享过他们的秒杀方案，通过修改MySQL数据库源码实现了高效的秒杀应用。不过那次分享太过冠冕堂皇，并没有给出具体的实现过程。另外，我从其他渠道听说这个方案还没有在生产环境中上线，不知道有没有知道内幕的朋友可以具体告诉我一下淘宝的方案是否已经上线了。

当然，优化闪购应用的方法有很多，比如使用CAS函数，但这些方法都无法实现事务特性。对于在Jim Gray事务处理教育下成长起来的一代人而言，Jun认为一切都应该是事务性的。不支持事务只能取得暂时的胜利。整个世界的哲学应该是事务，即要么做所有的事情，要么什么都不做，不要处于中间状态。Jun的人生哲学是，要么不设定目标，要么就一定会实现这个目标。比如Jun决定读博士，就一定会完成这个学业。

淘宝虽然没有给出具体的实现方法，但确实指出了秒杀应用带来的数据库压力问题，即大并发下更新同一行数据的压力。例如并发执行如下SQL语句来模拟秒杀场景：

开始;

进入

计数来自库存 WHERE id=1 AND count>0 FOR ;

库存设置计数 = 计数 -1 WHERE id=1 AND 计数 > 0;

；

做秒杀的时候，最主要的是对库存表进行操作，操作之前可能需要插入一些其他操作，比如日志，然后再更新库存表。下图是并发增加时事务处理的性能：

很明显，随着并发事务数的增加，事务处理的性能变差了，这个和之前淘宝分享的数据基本一致，原因是秒杀更新的是同一个商品，需要锁定同一行记录，所以秒杀操作虽然是并行的，但是在数据库层面是串行的。

随着并发度不断提升，事务不断出现等待锁、唤醒操作，导致性能急剧下降。如果你使用 perf 工具观察，应该可以观察到类似如下的情况：

59.06% [.] 真

16.63% libc-2.13.so [.] 3.09% [.]

2.96％[.]

......

可以发现锁的死锁检测占用了大部分的CPU时间，原因是因为锁等待。

利率

可能有些小伙伴知道可以使用 参数来控制存储引擎层的并发度，确实这个参数可以限制进入引擎层的事务数量，如果对比测试的话，性能确实会有一定的提升：

可以发现，将 设置为 16 确实能提升性能，当并发线程数为 128 时，TPS 从 4300 提升到了 7200，性能提升了 65%。但是到了 256 线程之后，性能还是让人堪忧。

造成上述情况的原因是，虽然在存储引擎层做了“限流”，但是MySQL数据库上层的线程仍然需要等待唤醒。

线程池技术

业界针对秒杀MySQL提供了很多解决方案，但是都非常定制化，需要应用去修改程序。比如通过在SQL语句中写hint来排队，而这种排队机制在我看来在低并发下性能会变差。因此一个常见的解决方案是使用线程池技术。

线程池可以在 MySQL 上层限制同时运行的 MySQL 事务数，从而解决秒杀带来的资源竞争问题。例如通过前面的测试已经知道当并发线程数为 16 个时，秒杀可以有最佳的性能。此时用户将线程池大小设置为 16，这样用户就可以得到期望的性能：

可以发现，即便是4096个并发线程，秒杀也能有近，有了线程池技术，秒杀非常简单，应用端不需要做任何改动。

但是线程池有一个参数cribe，类似于云计算中“超额预订”的概念，即MySQL线程池允许运行额外的线程。这个参数的默认值是3，之前设置为16，所以一共允许16*(1+3)=64个线程同时运行。这个参数的默认值没有问题，但是对于秒杀应用来说，这个参数就没有必要了，因为前面已经讨论过了，秒杀应用是串行的。所以将参数cribe设置为1，可以进一步提升秒杀应用的性能：

可以发现，大并发线程下，性能可以提升10%~30%。

总结

其实秒杀应用的数据库层优化很简单，只需要做好各层级的排队就可以了，比如：

MySQL企业版提供了线程池插件，但是需要另外付费，可以使用开源版MySQL，免费提供了线程池，可以保证大并发下应用的稳定性，特别适合秒杀应用。

B2B2C电子商务、技术与企业管理！