MySQL 优化专题——计数器表优化
关注小厨师，烹饪美味的技术餐
前言
许多论坛或者电商系统都具备点赞数、评论数、浏览数等“计数”功能，好的设计可以实现不低并发量且精确的计数器需求。本文将抛开中间件等额外的设计，从 MySQL 出发简单聊聊如何设计计数器表。
糟糕的设计
下面以论坛系统为例子介绍其中一种计数器表设计，下面给出这个最简单的设计，我们有这样一个表 post，承担着主要的帖子读写业务，其中一个高频写入功能为帖子的点赞计数功能。
| post_id | post_content | ··· 其他列 ··· | likes |
|---|---|---|---|
| 1 | ## content ## | ··· | 3 |
| 2 | ## content ## | ··· | 5 |
| 3 | ## content ## | ··· | 2 |
| ··· | ··· | ··· | ··· |
这个设计对于并发量很低的系统来说，可能没有什么问题，但是当并发量增加时，点赞的并发性能将会成为系统的瓶颈。
点赞是用户无意识的行为，在社交论坛上，点赞的触发次数远大于其他功能，对点赞计数的优化是非常重要的。
首先我们必须明确，我们增加某个点赞计数时使用的 SQL 语句，
update post set likes = likes + 1 where post_id = ?
我们知道，一个事务在更新某一行时，如果 where 子句使用的是索引，那么该事务需要首先获取该行的互斥锁。多个并发事务想要增加同一个帖子的点赞量时，这种设计将会使得这些事务只能串行执行。如图所示，
如果同一时间出现热门帖子时，多个用户同时点赞将会导致大量的数据库连接处于等待状态。为了优化这种事务串行的问题，我们介绍一种低成本且有效的方案——计数器表。
计数器表优化
现在我们希望将帖子的点赞计数移到另外的表 post_counter，并且做一些冗余设计。
| post_id | slot | likes |
|---|---|---|
| 1 | 0 | 3 |
| 1 | 1 | 5 |
| 1 | 2 | 3 |
| 1 | 3 | 4 |
| 1 | 4 | 2 |
| 2 | 0 | 3 |
| 2 | 1 | 5 |
| 2 | 2 | 2 |
| 2 | 3 | 6 |
| 2 | 4 | 3 |
| ··· | ··· |
DDL语句如下，
create table post_counter
(
post_id int not null,
slot tinyint unsigned not null,
likes int default 0 not null
);
对于每个 post_id，我们预先创建 5 行数据，同一个 post_id 的不同行用 slot（槽）标识。如果我们要进行增加点赞数，其 SQL 语句如下所示，
update post_counter set likes = likes + 1 where slot = rand() * 5 and post_id = ?;
相应的减少点赞数如下所示，
update post_counter set likes = likes - 1 where slot = rand() * 5 and post_id = ?;
读者很容易发现，并发事务对同一个帖子的点赞数更新操作将会被随机分散到 5 行数据中，如图所示，
这样能够很好地解决前面所提到的并发事务串行操作的问题。如果我们要获取一个帖子的点赞数，我们只需要做一个聚合查询即可，
select sum(likes) from post_counter where post_id = ?;
每个帖子都预先创建 5 行会不会导致表的行数过多？
我们知道有个帖子可能发出来很长一段时间都没有人点赞，或者点赞数稀少，对于这种情况，预留 5 行会显得比较多余。于是，我们可以选择 update 的时候动态创建，这时候我们可以使用 on duplicate key 子句来解决这个需求。因此新的增加点赞语句如下所示，
insert into post_counter (post_id, likes, slot) values(?, 1, rand() * 5) on duplicate key update likes = likes + 1;
相应的减少点赞数的语句如下，
insert into post_counter (post_id, likes, slot) values(?, -1, rand() * 5) on duplicate key update likes = likes - 1;
总结
本文介绍了计数器表的设计方法，其适用于各种高频的计数需求。本文为了简单演示，使用的随机数窗口为 5，实际生产中，读者可以通过逐步增加窗口值大小，并通过压力测试来查看锁等待时间，以找到合适当前业务的窗口大小。
至于有关 Redis 的高并发点赞计数我们以后再聊。不过作者认为，任何优化都应该优先考虑数据库层面的优化，在数据库层面优化没有达标的情况下，通过引入中间件缓存不过是堆积成本的暴力方法罢了。