mysql - 为这个 MySQL 查询寻找最佳索引

Question

在 MySQL 慢查询日志中，我有以下查询:

SELECT * FROM `news_items`
WHERE `ctime` > 1465013901 AND `feed_id` IN (1, 2, 9) AND
`moderated` = '1' AND `visibility` = '1'
ORDER BY `views` DESC
LIMIT 5;

这是 EXPLAIN 的结果:

+----+-------------+------------+-------+---------------------------------------------------------------------------------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys                                                                         | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------------------------------------------------------------------------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | news_items | index | feed_id,ctime,ctime_2,feed_id_2,moderated,visibility,feed_id_3,cday_complex,feed_id_4 | views | 4       | NULL |    5 | Using where |
+----+-------------+------------+-------+---------------------------------------------------------------------------------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

当我手动运行此查询时，大约需要 0.00 秒，但出于某种原因，它有时会出现在 MySQL 的慢速日志中，需要 1-5 秒。我相信当服务器处于高负载时会发生这种情况。

表结构如下:

CREATE TABLE IF NOT EXISTS `news_items` (
  `item_id` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  `source_id` int(10) NOT NULL,
  `feed_id` int(10) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 NOT NULL,
  `announce` varchar(255) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8 NOT NULL,
  `hyperlink` varchar(255) CHARACTER SET utf8 NOT NULL,
  `ctime` varchar(11) CHARACTER SET utf8 NOT NULL,
  `cday` tinyint(2) NOT NULL,
  `img` varchar(100) CHARACTER SET utf8 NOT NULL,
  `video` text CHARACTER SET utf8 NOT NULL,
  `gallery` text CHARACTER SET utf8 NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `visibility` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `pin` tinyint(1) NOT NULL,
  `pin_dttm` varchar(10) CHARACTER SET utf8 NOT NULL,
  `moderated` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

名为“views”的索引仅包含 1 个字段 -- views。我还有许多其他索引，包括(例如):

feed_id + views + visibility + moderated
moderated + visibility + feed_id + ctime
moderated + visibility + feed_id + views + ctime

我按上述顺序使用字段，因为这是 MySQL 开始使用它们的唯一原因。但是，我从来没有在 EXPLAIN 中得到“Using where; using index”。

关于如何使 EXPLAIN 向我展示“使用索引”的任何想法？

Answer 1

如果您已将存储引擎更改为 InnoDB 并创建正确的复合索引，您可以试试这个。第一个查询只获取前 5 行的 item_id。在完成完整的 SELECT 之后完成限制。因此，最好在没有任何大数据的情况下执行此操作，然后仅从 5 个问题中获取孔行

SELECT idata.* FROM (
  SELECT item_id FROM `news_items`
  WHERE `ctime` > 1465013901 AND `feed_id` IN (1, 2, 9) AND
  `moderated` = '1' AND `visibility` = '1'
  ORDER BY `views` DESC
  LIMIT 5 ) as i_ids
LEFT JOIN news_items AS idata ON idata.item_id = i_ids.item_id
ORDER BY `views` DESC;