mysql - 优化查询——它正在使用文件排序。 (包括解释、查询和显示创建表)

Question

我想知道为什么我的查询没有使用索引“created_2”，它涵盖了查询中使用的所有字段。它似乎使用了文件排序。选择索引的规则是什么？

查询:

SELECT * FROM (`stories`) WHERE `image_full_url` != '' AND `order` != 0 ORDER BY `created` DESC, `order` DESC LIMIT 5

创建表:

| stories | CREATE TABLE `stories` (
  `id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `news_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `author` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `author_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_caption` text COLLATE utf8_unicode_ci,
  `image_credit` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_full_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `body` text COLLATE utf8_unicode_ci,
  `summary` text COLLATE utf8_unicode_ci,
  `external_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `news_type` (`news_type`),
  KEY `created` (`created`),
  KEY `news_type_2` (`news_type`,`created`),
  KEY `created_2` (`created`,`image_full_url`,`order`),
  KEY `image_full_url` (`image_full_url`,`order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

解释:

mysql> explain SELECT * FROM (`stories`) WHERE `image_full_url` != '' AND `order` != 0 ORDER BY `created` DESC, `order` DESC LIMIT 5;
+----+-------------+---------+-------+----------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys  | key            | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+----------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | stories | range | image_full_url | image_full_url | 768     | NULL |   25 | Using where; Using filesort |
+----+-------------+---------+-------+----------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

Answer 1

完整的规则集是here , 并说明如果 order by 引用了索引的非连续部分，则不能使用该索引。如果您将索引从 (created,image_full_url,order) 更改为 (created,order,image_full_url), 这可能会让它被使用。