MYSQL - 使用 where 和 filesort

Question

你好，这个查询正在生成这个解释，考虑到我为两列都设置了索引，这很奇怪

'1', 'SIMPLE', 'vtr_video_transactions', 'ALL', 'user_standard,user_date', NULL, NULL, NULL, '5', 'Using where; Using filesort'

CREATE TABLE `vtr_video_transactions` (
  `vtr_id` int(11) NOT NULL AUTO_INCREMENT,
  `vtr_transaction_id` int(11) unsigned DEFAULT NULL,
  `vtr_user_id` int(11) unsigned DEFAULT NULL,
  `vtr_standards_id` int(11) unsigned DEFAULT NULL,
  `vtr_video_date` datetime DEFAULT NULL,
  PRIMARY KEY (`vtr_id`),
  KEY `user_standard` (`vtr_user_id`,`vtr_standards_id`),
  KEY `user_date` (`vtr_user_id`,`vtr_video_date`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

索引见 user_date。我在 MYSQL Workbench 上将其设置为 DESC。但是我得到了解释的文件排序。不知道为什么。干杯

表格数据

LOCK TABLES `vtr_video_transactions` WRITE;
/*!40000 ALTER TABLE `vtr_video_transactions` DISABLE KEYS */;
INSERT INTO `vtr_video_transactions` VALUES (1,1,1,2,'2015-09-05 17:18:59'),(2,2,1,3,'2015-08-27 19:04:12'),(3,2,1,4,'2015-08-27 18:55:53'),(4,10,1,119,'2015-08-27 19:04:12'),(5,11,1,10,'2015-08-27 19:04:12');

Answer 1

参见手册页 here

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.