mysql - 内连接不会使用索引

Question

为什么这个查询(以及许多类似的变体)不使用“标签”表上的 ASIN 索引？即使 A 只包含几行，它也坚持进行全表扫描。由于生产环境中的“标签”表包含近一百万个条目，因此它非常糟糕地扼杀了查询。

SELECT C.tag, count(C.tag) AS total
FROM
(
    SELECT B.*
    FROM
    (
        SELECT ASIN FROM requests WHERE user_id=9
    ) A
    INNER JOIN tags B USING(ASIN)
) C
GROUP BY C.tag ORDER BY total DESC 

EXPLAIN 显示未使用索引(在测试数据库上运行，因此“标签”中的行数很少，但仍然是全表扫描):

|  1 | PRIMARY     | <derived2>         | system | NULL          | NULL        | NULL    | NULL |    0 | const row not found            |
|  2 | DERIVED     | <derived3>         | ALL    | NULL          | NULL        | NULL    | NULL |   28 |                                |
|  2 | DERIVED     | B                  | ALL    | NULL          | NULL        | NULL    | NULL | 2593 | Using where; Using join buffer |
|  3 | DERIVED     | borrowing_requests | ref    | idx_user_id   | idx_user_id | 5       |      |   27 | Using where        

索引:

| book_tags |          1 | asin           |            1 | ASIN        | A         |         432 |     NULL | NULL   |      | BTREE      |         |
| book_tags |          1 | idx_tag        |            1 | tag         | A         |        1296 |     NULL | NULL   |      | BTREE      |         |
| book_tags |          1 | idx_updated_on |            1 | updated_on  | A         |         518 |     NULL | NULL   |      | BTREE

查询是从具有相同问题的 INNER JOIN 重写的:

SELECT tag, count(tag) AS total 
FROM tags 
INNER JOIN requests ON requests.ASIN=tags.ASIN 
WHERE user_id=9 
GROUP BY tag 
ORDER BY total DESC

解释:

|  1 | SIMPLE      | tags          | ALL  | NULL                 | NULL     | NULL    | NULL | 2593 | Using temporary; Using filesort |
|  1 | SIMPLE      | requests | ref  | idx_ASIN,idx_user_id | idx_ASIN | 33      | func |    3 | Using where 

我明白这是我所缺少的一个真正的基本点，但是大约 4 个小时的工作让我一无所获。欢迎任何建议。

编辑:

由于一些回复，我可以看到使用子查询的第一个查询不会使用索引，但它被使用是因为它的运行速度是仅使用 INNER JOIN 的底部查询的两倍。

例如，请求中有 70,000 行(均带有索引 ASIN)，标签中有 700,000 行，标签中有 95,000 个不同的 ASIN，每个标签记录少于 10 个。

如果用户有 10 个请求，我只希望列出和计算这 10 个 ASIN 的标签。在我看来，这应该使用 tags.idx_ASIN 并且应该从标签表中最多查找 100 行(10 个 ASIN，每行最多有 10 个标签)。

我错过了一些东西......我只是看不到什么。

编辑:

请求创建表:

CREATE TABLE IF NOT EXISTS `requests` (
  `bid` int(40) NOT NULL AUTO_INCREMENT,
  `user_id` int(20) DEFAULT NULL,
  `ASIN` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` enum('active','inactive','pending','deleted','completed') COLLATE     utf8_unicode_ci NOT NULL,
  `added_on` datetime NOT NULL,
  `status_changed_on` datetime NOT NULL,
  `last_emailed` datetime DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`bid`),
  KEY `idx_ASIN` (`ASIN`),
  KEY `idx_status` (`status`),
  KEY `idx_added_on` (`added_on`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status_changed_on` (`status_changed_on`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=149380 ;

标签创建表

CREATE TABLE IF NOT EXISTS `tags` (
  `ASIN` varchar(10) NOT NULL,
  `tag` varchar(50) NOT NULL,
  `updated_on` datetime NOT NULL,
  KEY `idx_tag` (`tag`),
  KEY `idx_updated_on` (`updated_on`),
  KEY `idx_asin` (`ASIN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

标签上没有主键。我通常没有没有主键的表，但没有看到这个需要。这可能是个问题吗？

啊哈!不同的字符集和排序规则。我会更正并重试!

后来:

明白了。查询从 10 秒下降到 0.006 秒。感谢大家让我以不同的方式看待这个问题。

Answer 1

MySQL 不索引子查询。如果您希望索引提高查询的性能，请重写它们以不使用子查询。