最近访问用户问题的实现

最新推荐文章于 2021-11-29 14:27:01 发布

ccat

最新推荐文章于 2021-11-29 14:27:01 发布

阅读量1.3k

点赞数

分类专栏： SQL Story Postgres Story 文章标签： session 数据库 postgresql haskell insert login

本文链接：https://blog.csdn.net/ccat/article/details/4506139

版权

SQL Story 同时被 2 个专栏收录

16 篇文章 0 订阅

订阅专栏

Postgres Story

8 篇文章 0 订阅

订阅专栏

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

应用平台需要统计最近访问的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的服务端编程技巧难以照搬，再加上团队管理的政治问题，我也只有祝他好运了。