mysql - 大数据量执行速度更快吗[MySQL]

Question

有什么方法可以优化下一个查询:

EXPLAIN EXTENDED SELECT keyword_id, ck.keyword, COUNT( article_id ) AS cnt
FROM career_article_keyword
LEFT JOIN career_keywords ck
USING ( keyword_id ) 
WHERE keyword_id
IN (

SELECT keyword_id
FROM career_article_keyword
LEFT JOIN career_keywords ck
USING ( keyword_id ) 
WHERE article_id
IN (

SELECT article_id
FROM career_article_keyword
WHERE keyword_id =9
)
AND keyword_id <>9
)
GROUP BY keyword_id
ORDER BY cnt DESC

如果我有特定的keyword_id (CURRENT_KID)，我需要找到曾经属于任何文章的所有关键字以及CURRENT_KID，并根据这些关键字的使用量对结果进行排序

表定义为:

mysql> show create table career_article_keyword;
+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                  | Create Table                                                                                                                                                                                                                                                                                                                                               |
+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| career_article_keyword | CREATE TABLE `career_article_keyword` (
  `article_id` int(11) unsigned NOT NULL,
  `keyword_id` int(11) NOT NULL,
  UNIQUE KEY `article_id` (`article_id`,`keyword_id`),
  CONSTRAINT `career_article_keyword_ibfk_1` FOREIGN KEY (`article_id`) REFERENCES `career` (`menu_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table career_keywords;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                         |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| career_keywords | CREATE TABLE `career_keywords` (
  `keyword_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `keyword` varchar(250) NOT NULL,
  PRIMARY KEY (`keyword_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

“解释”的输出吓到我了

http://o7.no/J6ThIs

在大数据上，这个查询可以杀死一切:)我可以以某种方式让它更快吗？

谢谢。

Answer 1

查看您的 EXPLAIN 输出，我担心您对子查询的使用导致索引使用不理想。我感觉(没有任何理由 - 在这一点上我很可能是错误的)使用JOIN重写可能会导致更优化的查询。

为此，我们需要了解您的查询的目的是什么。如果您的问题清楚地表达了它，那将会有所帮助，但在有点令人头疼之后，我决定您的查询正在尝试获取出现在包含某个给定关键字的任何文章中的所有其他关键字的列表，以及计数出现这些关键字的所有文章的数量。

现在让我们分阶段重建查询:

1. 获取“任何包含某个给定关键字的文章”(不用担心重复):

   SELECT ca2.article_id
   FROM
          career_article_keyword AS ca2
   WHERE
         ca2.keyword_id = 9;
   

2. 获取“[上述]中出现的所有其他关键字”

   SELECT ca1.keyword_id
   FROM
          career_article_keyword AS ca1
     JOIN career_article_keyword AS ca2 ON (ca2.article_id = ca1.article_id)
   WHERE
         ca1.keyword_id <> 9
     AND ca2.keyword_id =  9
   GROUP BY ca1.keyword_id;
   

3. 获取“[上述]，以及出现这些关键字的所有文章的计数”

   SELECT ca1.keyword_id, COUNT(DISTINCT ca0.article_id) AS cnt
   FROM
          career_article_keyword AS ca0
     JOIN career_article_keyword AS ca1 USING (keyword_id)
     JOIN career_article_keyword AS ca2 ON (ca2.article_id = ca1.article_id)
   WHERE
         ca1.keyword_id <> 9
     AND ca2.keyword_id =  9
   GROUP BY ca1.keyword_id
   ORDER BY cnt DESC;
   

4. 最后，我们希望将 career_keyword 表中的匹配关键字本身添加到输出中:

   SELECT ck.keyword_id, ck.keyword, COUNT(DISTINCT ca0.article_id) AS cnt
   FROM
          career_keywords        AS ck 
     JOIN career_article_keyword AS ca0 USING (keyword_id)
     JOIN career_article_keyword AS ca1 USING (keyword_id)
     JOIN career_article_keyword AS ca2 ON (ca2.article_id = ca1.article_id)
   WHERE
         ca1.keyword_id <> 9
     AND ca2.keyword_id =  9
   GROUP BY ck.keyword_id -- equal to ca1.keyword_id due to join conditions
   ORDER BY cnt DESC;
   

有一点很明显，您的原始查询引用了 career_keywords 两次，而这个重写的查询仅引用了该表一次；仅此一点就可以解释性能差异 - 尝试删除对它的第二个引用(即它出现在第一个子查询中的位置)，因为它在那里完全多余。

回顾此查询，我们可以看到正在以下列上执行联接:

• career_keywords.keyword_id 在 ck JOIN ca0

  该表定义了PRIMARY KEY (`keyword_id`)，因此有一个很好的索引可用于此连接。

• career_article_keyword.article_id 在 ca1 JOIN ca2

  此表定义了 UNIQUE KEY `article_id` (`article_id`,`keyword_id`) 并且，由于 article_id 是此索引中最左边的列，因此有一个很好的可用于此连接的索引。

• career_article_keyword.keyword_id 在 ck JOIN ca0 和 ca0 JOIN ca1

  没有可用于此连接的索引:此表中定义的唯一索引还有另一列，article_id 位于 keyword_id 的左侧 - 因此 MySQL 不能在索引中查找 keyword_id 条目，而无需先知道 article_id。我建议您创建一个新索引，将 keyword_id 作为最左边的列。

  (同样可以通过查看原始查询直接确定是否需要此索引，其中两个最外面的查询在该列上执行联接。)