文章/答案/技术大牛

发布

社区首页 >问答首页 >为什么下面的查询不使用‘`always`’索引？

问为什么下面的查询不使用‘`always`’索引？
EN

Database Administration用户

提问于 2012-04-11 14:47:21

回答 2查看 164关注 0票数 2

今天早上，我开始在我管理的一个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并没有什么不同。WHy这个查询不使用movies表上的索引吗？我如何向开发人员解释，他们应该重写这个查询，使其不让服务器陷入困境？

mysql

index

optimization

回答 2

Database Administration用户

回答已采纳

发布于 2012-04-11 15:28:22

可以将其重写为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属性在以下两方面失败：

正如@yper多维数据集所指出的，这是非常低的选择性，因为只有两个可能的值。MySQL将使用这些类型的索引丢弃
查询有OR。这意味着它无论如何都要做一次表格扫描才能匹配这两种条件。

票数 2

Database Administration用户

发布于 2012-04-11 15:38:02

您也可以尝试这样做(通过以下方式避免分组)：

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

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/16352

复制

相似问题

问为什么下面的查询不使用‘`always`’索引？
EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么下面的查询不使用‘`always`’索引？EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么下面的查询不使用‘`always`’索引？
EN