需要一些帮助来破译复杂连接的 MYSQL EXPLAIN 输出

Question

我网站的主页有一个复杂的查询，如下所示：

SELECT karmalog.*, image.title as img_title, image.date_uploaded, imagefile.file_name as img_filename, imagefile.width as img_width, imagefile.height as img_height, imagefile.transferred as img_transferred, u1.uname as usr_name1, u2.uname as usr_name2, u1.avat_url as usr_avaturl1, u2.avat_url as usr_avaturl2, class.title as class_title,forum.id as f_id, forum.name as f_name, forum.icon, forumtopic.id as ft_id, forumtopic.subject
FROM karmalog 
LEFT JOIN image on karmalog.event_type = 'image' and karmalog.object_id = image.id 
LEFT JOIN imagefile on karmalog.object_id = imagefile.image_id and imagefile.type = 'smallthumb'
LEFT JOIN class on karmalog.event_type = 'class' and karmalog.object_id = class.num
LEFT JOIN user as u1 on karmalog.user_id = u1.id
LEFT JOIN user as u2 on karmalog.user_sec_id = u2.id
LEFT JOIN forumtopic on karmalog.object_id = forumtopic.id and karmalog.event IN ('FORUM_REPLY','FORUM_CREATE')
LEFT JOIN forum on forumtopic.forum_id = forum.id
WHERE karmalog.event IN ('EDIT_PROFILE','FAV_IMG_ADD','FOLLOW','COM_POST','IMG_UPLOAD','IMG_VOTE','LIST_VOTE','JOIN','CLASS_UP','CLASS_DOWN','LIST_CREATE','FORUM_REPLY','FORUM_CREATE','FORUM_SUBSCRIBE')
  AND karmalog.delete=0
ORDER BY karmalog.date_created DESC, karmalog.id DESC 
LIMIT 0,13 

我不会让您厌烦确切的细节，但会做一个简短的解释： 基本上，这是系统中发生的事件列表，有点像流。事件可以有多种类型，并且根据其类型，它需要连接来自不同表的特定数据。

目前，此查询需要 2 秒才能运行，但随着条目数量的增加，速度会逐渐变慢。因此我正在寻求优化它。这是 MYSQL 解释的输出：

我对 EXPLAIN 的理解太有限，无法理解这一点。我更愿意保持这个查询不变（而不是对其进行非规范化），但使用适当的索引或其他快速获胜来提高其性能。根据此解释输出，您是否看到我可以跟进的内容？

编辑：根据要求，karmalog 表的定义：

CREATE TABLE `karmalog` (
  `id` int(11) NOT NULL auto_increment,
  `guid` char(36) default NULL,
  `user_id` int(11) default NULL,
  `user_sec_id` int(11) 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_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','JOIN') NOT NULL,
  `event_type` enum('follow','tag','image','class','list','forum','user') NOT NULL,
  `active` bit(1) NOT NULL,
  `delete` bit(1) NOT NULL default '\0',
  `object_id` int(11) default NULL,
  `object_cache` varchar(1024) default NULL,
  `karma_delta` int(11) NOT NULL,
  `gold_delta` int(11) NOT NULL,
  `newkarma` int(11) NOT NULL,
  `newgold` int(11) NOT NULL,
  `mail_processed` bit(1) 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`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL,
  CONSTRAINT `user_sec_id` FOREIGN KEY (`user_sec_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

• 首先，您可能缺少 (event_type, object_id) 上的复合索引。第二次阅读时，请忽略这一点。您可能需要这样的索引用于其他查询，但不适用于此查询（因为 ORDER BY ... LIMIT）。

• 其次，您在 date_created 上没有索引，并且您ORDER BY 此列。对此添加索引。考虑到 WHERE 条件，最好的索引可能是 (delete, date_created) 或 (event, date_created) 或（可能最好）：（event，delete，date_created）。

• 第三，尝试这样重写：

先LIMIT，然后JOIN（已更正）：

SELECT karmalog.*, image.title as img_title, image.date_uploaded, imagefile.file_name as img_filename, imagefile.width as img_width, imagefile.height as img_height, imagefile.transferred as img_transferred, u1.uname as usr_name1, u2.uname as usr_name2, u1.avat_url as usr_avaturl1, u2.avat_url as usr_avaturl2, class.title as class_title,forum.id as f_id, forum.name as f_name, forum.icon, forumtopic.id as ft_id, forumtopic.subject
FROM 
    ( SELECT *
      FROM karmalog
      WHERE karmalog.event IN ('EDIT_PROFILE','FAV_IMG_ADD','FOLLOW','COM_POST','IMG_UPLOAD','IMG_VOTE','LIST_VOTE','JOIN','CLASS_UP','CLASS_DOWN','LIST_CREATE','FORUM_REPLY','FORUM_CREATE','FORUM_SUBSCRIBE')
        AND karmalog.delete=0
      ORDER BY karmalog.date_created DESC, karmalog.id DESC 
      LIMIT 0,13  
    ) AS karmalog 
LEFT JOIN image on karmalog.event_type = 'image' and karmalog.object_id = image.id 
LEFT JOIN imagefile on karmalog.object_id = imagefile.image_id and imagefile.type = 'smallthumb'
LEFT JOIN class on karmalog.event_type = 'class' and karmalog.object_id = class.num
LEFT JOIN user as u1 on karmalog.user_id = u1.id
LEFT JOIN user as u2 on karmalog.user_sec_id = u2.id
LEFT JOIN forumtopic on karmalog.object_id = forumtopic.id and karmalog.event IN ('FORUM_REPLY','FORUM_CREATE')
LEFT JOIN forum on forumtopic.forum_id = forum.id
ORDER BY karmalog.date_created DESC, karmalog.id DESC 

Answer 2

解释的重要部分是：可能的键、键和行。

如果没有可能的键，则需要创建索引。
如果没有使用密钥，可能是由于：

• 密钥基数低；
• 函数的使用；

将精力集中在行数最多的表上。即karmalog。

请记住，MySQL 对于每个表的每个选择只能使用一个索引。
连接都是左连接，因此它们不会限制 karmalog 索引中的行数，这对您没有帮助。
查看 where 部分，deleted 的基数较低（只有 2 个值，其中 90% 为 =0）。因此，只有 event+date_created 字段符合索引条件，将索引放在：

ALTER TABLE karmalog ADD INDEX date_event (event, date_created);

Answer 3

尝试在表 karmalog 绝对事件上放置一个索引（也许是删除和 object_id），因为这将使速度更快，并为其提供第一个连接的键。

其次看看这个表，看看你是否可以通过某种连接来做到这一点，以使其将来变得更轻。但这可能意味着对您的数据库进行更改

karmalog.event IN ('EDIT_PROFILE','FAV_IMG_ADD','FOLLOW','COM_POST','IMG_UPLOAD','IMG_VOTE','LIST_VOTE','JOIN','CLASS_UP','CLASS_DOWN','LIST_CREATE','FORUM_REPLY','FORUM_CREATE','FORUM_SUBSCRIBE')