主页 / dba / 问题 / 16352
Accepted
Josh
Asked: 2012-04-12 06:47:21 +0800 CST

为什么以下查询不使用“always”索引？

  • 772

今天早上，我开始在我管理的一台 MySQL 服务器上收到负载警告。SHOW FULL PROCESSLIST透露罪魁祸首是以下（写得不好的）查询：

SELECT * FROM movies, showtimes
WHERE site='5' AND
    (showtimes.movie = movies.id OR always='true') AND
    ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')\
GROUP BY movies.id ORDER BY listorder

我不确定这是谁写的。看起来它在我 5 多年前设计的数据库中，自从我上次接触它以来，不知名的人一直在修补它。有问题的表是：

CREATE TABLE `movies` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `rating` varchar(5) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `special` varchar(255) NOT NULL default '',
  `url` varchar(64) NOT NULL default '',
  `poster` varchar(255) NOT NULL default '',
  `runtime` varchar(20) NOT NULL default '',
  `listorder` tinyint(4) NOT NULL default '0',
  `comsoon` enum('false','true') NOT NULL default 'false',
  `always` enum('false','true') NOT NULL default 'false',
  `site` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `rating` (`rating`),
  KEY `site` (`site`),
  KEY `comsoon` (`comsoon`),
  KEY `always` (`always`),
  KEY `listorder` (`listorder`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `showtimes` (
  `movie` int(11) NOT NULL default '0',
  `showdate` date NOT NULL default '0000-00-00',
  `showtime` time NOT NULL default '00:00:00',
  PRIMARY KEY  (`movie`,`showdate`,`showtime`),
  KEY `showdate` (`showdate`),
  KEY `showtime` (`showtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

当我第一次意识到这种情况时，movies.always 甚至没有索引，也没有movies.listorder。我确定这是问题所在，但是，在这两列上添加索引并没有改变EXPLAIN：

mysql> EXPLAIN SELECT * FROM movies, showtimes WHERE site='5' AND (showtimes.movie = movies.id OR always='true') AND ((showdate>='2012-4-11' AND showdate<'2012-04-18') OR always='true')  GROUP BY movies.id ORDER BY listorder;
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys       | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | showtimes | index | PRIMARY,showdate    | PRIMARY | 10      | NULL | 93411 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | movies    | ALL   | PRIMARY,site,always | NULL    | NULL    | NULL |    25 | Using where                                  | 
+----+-------------+-----------+-------+---------------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

我尝试按如下方式重写查询：

SELECT * FROM movies, showtimes
WHERE (site='5' AND always='true') OR 
  (site='5' AND showtimes.movie = movies.id AND showdate>='2012-4-11' AND showdate<'2012-04-18')
GROUP BY movies.id ORDER BY listorder

然而，EXPLAIN没有什么不同。为什么这个查询不使用movies表上的索引？我如何向开发人员解释他们应该重写此查询以使其不会使服务器崩溃？

mysql index

2 个回答

  1. Best Answer

    可以将其重写为UNION

    SELECT * FROM (
 (
  SELECT movies.* FROM movies
   WHERE site='5' AND always='true'
 ) UNION
 (
  SELECT movies.* FROM movies
  INNER JOIN showtimes ON showtimes.movie=movies.id
  WHERE site='5' AND showdate BETWEEN '2012-4-11' AND '2012-04-18'
 )
) AS movie_shows
GROUP BY id
ORDER BY listorder

    我没有测试过以上内容，但很好奇结果EXPLAIN

    您的always属性以两种方式失败：

    1. 正如@ypercube 指出的那样，选择性非常低，因为只有两个可能的值。MySQL 将放弃使用这些类型的索引

    2. 查询有OR. 这意味着无论如何它都必须进行表扫描才能匹配这两个条件。

    • 2

  2. 你也可以试试这个（避免分组）：

    SELECT movies.* 
FROM movies 
WHERE site = 5
  AND always = 'true'
   OR site = 5
  AND always = 'false'
  AND EXISTS
      ( SELECT *
        FROM showtimes 
        WHERE movies.id = showtimes.movie
          AND showdate >= '2012-04-11' 
          AND showdate < '2012-04-18'
      ) 
ORDER BY listorder
    • 0

相关问题