MySQL索引的放缓查询(MySQL index slowing down query)

MySQL Server version: 5.0.95
Tables All: InnoDB

我有一个MySQL数据库查询的问题。基本上，我发现，如果我索引特定VARCHAR（50）字段tag.name，我的查询需要较长的时间（X10）比不是索引字段。我试图加快这个查询了，但是我的努力似乎适得其反。

罪魁祸首行和场似乎是：

WHERE `t`.`name` IN ('news','home')

我注意到，如果我查询tag直接表没有加入使用相同的条件，以及与索引的名称字段中，我没有问题。它实际上可以作为预期的要快。

示例查询**

      SELECT `a`.*, `u`.`pen_name`
        FROM `tag_link` `tl`
  INNER JOIN `tag` `t`
          ON `t`.`tag_id` = `tl`.`tag_id`
  INNER JOIN `article` `a` 
          ON `a`.`article_id` = `tl`.`link_id`
  INNER JOIN `user` `u`
          ON `a`.`user_id` = `u`.`user_id`   
       WHERE `t`.`name` IN ('news','home')
         AND `tl`.`type` = 'article'
         AND `a`.`featured` = 'featured'
    GROUP BY `article_id`
       LIMIT 0 , 5

与指数EXPLAIN **

| id | select_type | table | type   | possible_keys            | key     | key_len | ref               | rows | Extra                                                     |
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t     | range  | PRIMARY,name             | name    | 152     | NULL              |    4 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | tl    | ref    | tag_id,link_id,link_id_2 | tag_id  | 4       | portal.t.tag_id   |   10 | Using where                                               | 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,fk_article_user1 | PRIMARY | 4       | portal.tl.link_id |    1 | Using where                                               | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY | 4       | portal.a.user_id  |    1 |                                                           | 
+----+-------------+-------+--------+--------------------------+---------+---------+-------------------+------+-----------------------------------------------------------+

EXPLAIN没有指数**

+----+-------------+-------+--------+--------------------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys            | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | a     | index  | PRIMARY,fk_article_user1 | PRIMARY | 4       | NULL                | 8742 | Using where | 
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY | 4       | portal.a.user_id    |    1 |             | 
|  1 | SIMPLE      | tl    | ref    | tag_id,link_id,link_id_2 | link_id | 4       | portal.a.article_id |    3 | Using where | 
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                  | PRIMARY | 4       | portal.tl.tag_id    |    1 | Using where | 
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------+------+-------------+

CREATE TABLE

CREATE TABLE `tag` (
  `tag_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `type` enum('layout','image') NOT NULL,
  `create_dttm` datetime default NULL,
  PRIMARY KEY  (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43077 DEFAULT CHARSET=utf8

指数法

SHOW INDEX FROM tag_link;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tag_link |          0 | PRIMARY  |            1 | tag_link_id | A         |       42023 |     NULL | NULL   |      | BTREE      |         |
| tag_link |          1 | tag_id   |            1 | tag_id      | A         |       10505 |     NULL | NULL   |      | BTREE      |         |
| tag_link |          1 | link_id  |            1 | link_id     | A         |       14007 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

SHOW INDEX FROM article;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| article |          0 | PRIMARY          |            1 | article_id  | A         |        5723 |     NULL | NULL   |      | BTREE      |         |
| article |          1 | fk_article_user1 |            1 | user_id     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| article |          1 | create_dttm      |            1 | create_dttm | A         |        5723 |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

最终的解决方案似乎MySQL的只是排序的数据不正确。最后，它竟然快了标签表格看作为一个子查询返回的ID。

Answer 1:

有多大你的表？我在第一次注意到说明你有一个“使用临时;使用文件排序”，这是不好的。您的查询可能被倾销到光盘这使得它比在内存中的查询方法要慢。也尽量避免使用“SELECT *”，而是查询所需的最低领域。

Answer 2:

看来，article_id的是文章表的主键。

既然你通过article_id的分组，MySQL需要通过该列，以返回记录，以执行GROUP BY。

你可以看到，如果没有索引，它会扫描所有记录中的文章表，但他们至少为了通过article_id的，所以最迟排序是必需的。极限优化可以应用在这里，因为它已经在命令，它可以只是停止它得到五个行之后。

在与tag.name指数，而不是扫描整个表的文章，查询，它利用了指数，但对标签表，并从那里开始。不幸的是，这样做的时候，记录以后必须通过article.article_id为了完成GROUP BY子句排序。极限优化不能应用，因为它必须返回整个结果集，然后订购，为了拿到第5行。

在这种情况下，MySQL只是猜测错误。

如果没有LIMIT子句，我猜，使用索引是更快，这也许是什么的MySQL是猜测。