大型表的MySQL查询优化

Question

我有一个需要50秒的查询

SELECT `security_tasks`.`itemid` AS `itemid`
FROM `security_tasks`
INNER JOIN `relations` ON (`relations`.`user_id` = `security_tasks`.`user_id` AND    `relations`.`relation_type_id` = `security_tasks`.`relation_type_id` AND `relations`.`relation_with` = 3001 )  

security_tasks中的记录=841321 relations中的记录=234254

CREATE TABLE `security_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `itemid` int(11) DEFAULT NULL,
  `relation_type_id` int(11) DEFAULT NULL,
  `Task_id` int(2) DEFAULT '0',
  `job_id` int(2) DEFAULT '0',
  `task_type_id` int(2) DEFAULT '0',
  `name` int(2) DEFAULT '0'
  PRIMARY KEY (`id`),
  KEY `itemid` (`itemid`),
  KEY `relation_type_id` (`relation_type_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1822995 DEFAULT CHARSET=utf8;

CREATE TABLE `relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `relation_with` int(11) DEFAULT NULL,
  `relation_type_id` int(11) DEFAULT NULL,
  `manager_level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `relation_with` (`relation_with`),
  KEY `relation_type_id` (`relation_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1082882 DEFAULT CHARSET=utf8;

我能做些什么让它快一点，比如快1秒或2秒

id select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  relations   ref user_id,relation_with,relation_type_id  relation_with   5   const   169 Using where
1   SIMPLE  security_tasks  ref relation_type_id,user_id    user_id 5   transparent.relations.user_id   569 Using where

有什么想法吗？

Answer 1

稍微调整你的复合指数，不要只做两个，而是所有三个部分

ALTER TABLE relations ADD INDEX(user_id、relation_type_id、relation_with)

索引不仅必须位于联接的列上，而且应该基于联接的列以及查询条件所涉及的任何其他有意义的内容（在合理的情况下，需要时间来了解更多的效率）。因此，在建议的情况下，您知道用户和类型上的联接，但也是特定于与...因此将其添加到同一索引中。

SELECT 
      st.itemid
   FROM 
      security_tasks st
         INNER JOIN relations r
            ON st.user_id = r.user_id 
            AND st.relation_type_id = r.relation_type_id
            AND r.relation_with = 3001