Postgres Story——最近访问用户问题

前几天有个朋友提了一个问题:

应用平台需要统计最近访问的20个用户的信息。

我第一个想到的是用memcache之类的专用的缓冲，以用户名为键，以最后访问时间为值，如 果用户访问比较均匀，限定一个合适的 超时值，查询的时候遍历过滤就好了，虽然不是很 精确，但是实现起来够简单。

答：不可以，领导要求只在数据库端解决。

环境准备

提问的朋友使用的是 MySQL ，这里我用一个 PostgreSQL 8.4(Enterprise DB Postgres Plus Stand Server 8.4) 建立实验环境。操作系统是 Windows 7 RC。

客户端是 Emacs sql-postgres，\timing on。

使用 explain analyze 分析性能消耗。

尝试

那么，最简单的应该是在日志表上查询：

select username, max(logintime)
from log
group by username
order by 2 desc
limit 20;

这里假定的是会话表(log)中有名为 username 的用户标识列，名为 logintime 的时间戳字 段。

但是显然这个查询的性能并不好，它要遍历整个日志表。

首先，我们给日志表加一个聚集索引，使它按插入时间倒排（显然这对插入效率不利）。

create index idx_logintime on login_session(logintime desc);

alter table login_session cluster on idx_logintime;

然后，再进行查询，可见效率有微弱提升。在我本机的 Postgres 8.4 上，通过 explain analyze 可见有不到百分之二的效率上升。我甚至怀疑这仅仅是源自一些随机事件的影响。

换一个角度分析，只要 select max ... group by ... 的模式不改变，就很难有根本的性 能提升，最好的办法仍然是缓存。

改进

有个简单的办法，可以在PG数据库中制造一个缓存表：

• 首先，直接建立一个 user->timstamp 键值对表：

  create table sessions(username text, seqs serial, logintime timestamp, primary key(username));

• 然后创建一个触发器函数

create or replace function on_log() returns trigger as $$
begin
  if exists(select * from sessions where username=NEW.username) then
    update sessions set seqs = nextval('sessions_seqs_seq') where username=NEW.username;
  else
    insert into sessions(username, logintime) select NEW.username, NEW.logintime;
  end if;
  if (select count(*) from sessions)> 20 then
    delete from sessions where seqs < (select min(seqs) from (select seqs from
sessions order by seqs desc limit 20) as t);
  end if;
  return NEW;
end;
$$ language plpgsql;

create trigger onlog after insert on log for each row execute procedure on_log();

OK，这样一来，经过反复测试，在我的笔记本上，插入速度基本没有降低（约 2%~ 5%）， 而查询“最新的20个用户”这一操作，速度提升了一百七十余倍。几乎可以视作是一个数据库 端的队列缓存了。这个数值是基于 log 表中有五万条数据，在实际应用中日志表十万以上 （按每日切分导出）比比皆是，性能差距会更为显著。

分析

“最新的 20 个用户”，这一问题，与“最近5分钟内的用户”此类问题的区别在于，它不能通 过获取当前时间，简单回溯得到有效数据区间。如何避免遍历整个日志表是一个关键问题———— 现代的在线服务系统每日访问日志量动辄数十万，几百上千万的也不罕见，第一种解法明显 不能满足。相比之下，第二种方案不需要干涉日志表的存储排序，不需要建立多余的索引。 通常我们讲触发器速度比较慢，但具体到这里，只是却是一个比较快速高效的实现方案。

如果用户量不大，在几千以内，为用户表建立一个最后登录时间字段，绑定 null last 索 引（可以加上FASTUPDATE=ON），然后利用基本的order by limit就可以得到比较慢意的效 果了。但是对于大型SNS等应用，数十上百万用户也是有可能的（甚至大型企业内部应用， 数万乃至上十万用户的系统也不在少数）。此时，第二种解决方案就理想的多。虽然看起来 在session表上反复进行计算和写入，但是由于session表的数据量非常小，永远只有几十 条，所以计算速度很快。如果对 log 表进行恰当的分区，同时将sessions表存储到另一个 区域（甚至直接缓存到内存），那么对于海量数据，也会有一个稳定的，良好的性能表现。

更进一步的解决方法，则应该是在数据库或应用层服务器环境，建立一个内存中的队列，来 记录这个数据。只要注意并发写入的问题，就可以得到很好的性能。这方面Haskell的STM机制、我之前用在MSG.Summoner.Trac的旋转锁机制、都可以比较漂亮的解决这一问题。

优化

sessions 表的读写都非常频繁，通常有比较多的 update，，但是数据量基本恒定。应该积极的执行 vacuum。

可以为日志表添加一个 uuid 字段，以便在数据量上升时按 hash 分表。这样可以获得更好 的写入性能。

可以将 session 的主键索引设为 FASTUPDATE=ON 。

触发器中设定

SET LOCAL synchronous_commit TO OFF;

打开 WAL 异步事务提交，可以进一步提高并发写入速度。

更进一步的，可以写一个常驻的守护进程，用应用语言建立一个队列，将这一需求建立为独 立的服务。这个服务可以通过 pl 嵌入语言与数据库联接，也可以直接联接到应用层，有一 些应用层架构依赖于并发的多 fastcgi 实例，此时要注意并发访问冲突和数据同步的问题。

总结

之所以出现这样一个困扰开发人员的问题，根本在于项目领导不提供，也不认同使用灵活、 开放的思维方式解决此问题。服务项目的架构是一个系统工程，类似这样的问题，完全可以 用更开阔的眼光去寻找出路。建立一个简单的缓存队列实例，用perl或python，只需要数十 行。结合Postgres的plperl或plpython等嵌入脚本，可以非常简单的达到目的。甚至不用担 心使用多实例 fastcgi 时，多个应用服务进程争用缓存队列I/O的问题。

即使从方案二出发，通过利用服务器环境的资源，也可以做出更多优化。如把“删除第20之 后的旧数据”这部分脚本，移到 crontab 中，与vacuum 结合执行。会得到更好的效率。

找我请教的这位朋友，使用的不是Postgres，而是MySQL，相对来说很多PG的服务端编程技 巧难以照搬，再加上团队管理的政治问题，我也只有祝他好运了。