我有一个网站,那里有一个活动feed,类似于社交网站,如Facebook有一个。它是一个"最新的第一个"列表,描述了用户所采取的行动。在生产环境中,该表中大约有200k个条目。
由于无论如何都会被问到这个问题,我将首先分享完整的表结构:
CREATE TABLE `karmalog` (
`id` int(11) NOT NULL auto_increment,
`guid` char(36) default NULL,
`user_id` int(11) default NULL,
`user_name` varchar(45) default NULL,
`user_avat_url` varchar(255) default NULL,
`user_sec_id` int(11) default NULL,
`user_sec_name` varchar(45) default NULL,
`user_sec_avat_url` varchar(255) default NULL,
`event` enum('EDIT_PROFILE','EDIT_AVATAR','EDIT_EMAIL','EDIT_PASSWORD','FAV_IMG_ADD','FAV_IMG_ADDED','FAV_IMG_REMOVE','FAV_IMG_REMOVED','FOLLOW','FOLLOWED','UNFOLLOW','UNFOLLOWED','COM_POSTED','COM_POST','COM_VOTE','COM_VOTED','IMG_VOTED','IMG_UPLOAD','LIST_CREATE','LIST_DELETE','LIST_ADMINDELETE','LIST_VOTE','LIST_VOTED','IMG_UPD','IMG_RESTORE','IMG_UPD_LIC','IMG_UPD_MOD','IMG_GEO','IMG_UPD_MODERATED','IMG_VOTE','IMG_VOTED','TAG_FAV_ADD','CLASS_DOWN','CLASS_UP','IMG_DELETE','IMG_ADMINDELETE','IMG_ADMINDELETEFAV','SET_PASSWORD','IMG_RESTORED','IMG_VIEW','FORUM_CREATE','FORUM_DELETE','FORUM_ADMINDELETE','FORUM_REPLY','FORUM_DELETEREPLY','FORUM_ADMINDELETEREPLY','FORUM_SUBSCRIBE','FORUM_UNSUBSCRIBE','TAG_INFO_EDITED','IMG_ADDSPECIE','IMG_REMOVESPECIE','SPECIE_ADDVIDEO','SPECIE_REMOVEVIDEO','EARN_MEDAL','JOIN') NOT NULL,
`event_type` enum('follow','tag','image','class','list','forum','specie','medal','user') NOT NULL,
`active` bit(1) NOT NULL,
`delete` bit(1) NOT NULL default ' ',
`object_id` int(11) default NULL,
`object_cache` text,
`object_sec_id` int(11) default NULL,
`object_sec_cache` text,
`karma_delta` int(11) NOT NULL,
`gold_delta` int(11) NOT NULL,
`newkarma` int(11) NOT NULL,
`newgold` int(11) NOT NULL,
`migrated` int(11) NOT NULL default '0',
`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `user_sec_id` (`user_sec_id`),
KEY `image_id` (`object_id`),
KEY `date_event` (`date_created`,`event`),
KEY `event` (`event`),
KEY `date_created` (`date_created`),
CONSTRAINT `karmalog_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL,
CONSTRAINT `karmalog_ibfk_2` FOREIGN KEY (`user_sec_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在优化这个表之前,我的查询有5个连接,我遇到了很慢的查询时间。我已经反规范化了所有的数据,这样就没有一个连接了。因此,表和查询是平面的。
在表设计中可以看到,有一个"event"字段,它是一个枚举,包含几十个可能的值。在整个站点中,我显示了基于特定事件类型的活动提要。通常,该查询看起来像这样:
SELECT * FROM karmalog as k
WHERE k.event IN ($events) AND k.delete=0
ORDER BY k.date_created DESC, k.id DESC
LIMIT 0,30
这个查询的作用是查找与$events中传递的任何事件匹配的最近30个条目,这些事件可以是多个。
由于删除连接并在大多数字段上有索引,我期望这执行得很好,但它没有。在200k个条目中,它仍然需要超过3秒,我不明白为什么。
关于解决方案,我知道我可以存档旧的条目或按事件类型对表进行分区,但这将对代码产生相当大的影响,我首先想了解为什么上面的方法如此缓慢。
作为一个临时的解决方案,我现在这样做:
SELECT * FROM
(SELECT * FROM karmalog ORDER BY date_created DESC, id DESC LIMIT 0,1000) as karma
WHERE karma.event IN ($events) AND karma.delete=0
LIMIT $page,$pagesize
这样做的目的是限制基集只搜索最近的1000个条目,希望并猜测为我传入的过滤器找到30个条目。但它不是很健壮。它将不能用于更罕见的事件,并且会带来分页问题。
因此,我想首先找出为什么我的初始查询慢的根本原因,这出乎我的意料。
Edit:我被要求共享执行计划。下面是测试查询:
EXPLAIN SELECT * FROM karmalog
WHERE event IN ('FAV_IMG_ADD','FOLLOW','COM_POST','IMG_VOTE','LIST_VOTE','JOIN','CLASS_UP','LIST_CREATE','FORUM_REPLY','FORUM_CREATE','FORUM_SUBSCRIBE','IMG_GEO','IMG_ADDSPECIE','SPECIE_ADDVIDEO','EARN_MEDAL') AND karmalog.delete=0
ORDER BY date_created DESC, id DESC
LIMIT 0,36
执行计划:id = 1
select_type = SIMPLE
table = karmalog
type = range
possible_keys = event
key = event
key_len = 1
red = NULL
rows = 80519
Extra = Using where; Using filesort
我不知道如何理解上面的内容,但我知道sort子句似乎真的杀死了这个查询。使用此排序,需要4.3秒,而不需要0.03秒。
SELECT *
有时会大大降低有序查询的速度,所以让我们从以下方式重构查询开始:
SELECT k.*
FROM karmalog AS k
JOIN (
SELECT id
FROM karmalog
WHERE event IN ($events)
AND delete=0
ORDER BY date_created DESC, id DESC
LIMIT 0,30
) AS m ON k.id = m.id
ORDER BY k.date_created DESC, k.id DESC
这将完成您的ORDER BY ... LIMIT
操作,而不必在排序阶段移动整个表。最后,它将从原始表中查找适当的30行,并再次对这些行进行排序。这可能会节省大量的I/O和内存中的数据转换。
第二,如果id
列值在插入记录时按升序分配,那么在ORDER BY
操作中使用date_created
是多余的。但是MySQL不知道这些,所以省略它可能会有所帮助。如果您在插入时始终使用当前日期,并且从不更新日期,则会出现此情况。
第三,您可以为选择(内部)查询使用复合覆盖索引。这是一个包含您需要的所有字段的索引。当您使用覆盖索引时,整个查询都可以从索引中得到满足,而不需要返回到原始表。
试试这个复合覆盖索引:(delete, event, id)
。如果您决定无法在您的订单中摆脱date_created
的使用,请尝试这样做:(delete, event, date_created, id)
在两个相关问题上添加复合索引。在您的表中,您可以通过指定例如
来实现。KEY `date_created` (`date_created`, `event`)
这个键仍然可以用来满足普通的date_created
范围搜索。但除此之外,event
数据也包括在内,因此DBS将能够仅通过查看索引来检测相关行。
如果您愿意,也可以尝试其他顺序:首先是事件,然后是日期。如果有许多事件类型,但您的过滤器只包含很少的事件类型,那么这可能允许进行一些优化。另一方面,我不确定系统是否能够在这种情况下使用LIMIT
条款,所以我不确定其他订单是否会有任何帮助。
编辑:我完全错过了你的date_event
索引已经有这个信息。但根据你的执行计划,这条线是没用的。看起来优化器出错了。您可以尝试删除event
索引,也可以删除date
索引,然后看看会发生什么。