MySQL-“Order By”/子查询上的实体框架性能问题

Question

我有一个表存储3K条记录,

我对实体框架和ORM是个新手，但我能理解出了问题。

运行此linq查询时：

repo.GetQuery<Article>().Where(foo=>foo.Expires_date>=date
                            && foo.Istoparticle==true
                            && foo.Isfrontpage==false)
                        .OrderByDescending(foo=>foo.Date)
                        .Take(4);

我在mysql端得到以下查询：

SELECT
`Project1`.`Id`, 
`Project1`.`User_id`, 
`Project1`.`Category_id`, 
`Project1`.`Title`, 
`Project1`.`Value`, 
`Project1`.`Keywords`, 
`Project1`.`Description`, 
`Project1`.`Images`, 
`Project1`.`Votes`, 
`Project1`.`Views`, 
`Project1`.`Isvisible`, 
`Project1`.`Isfrontpage`, 
`Project1`.`Istoparticle`, 
`Project1`.`Date`, 
`Project1`.`Expires_date`, 
`Project1`.`Votes_sum`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`User_id`, 
`Extent1`.`Category_id`, 
`Extent1`.`Title`, 
`Extent1`.`Value`, 
`Extent1`.`Keywords`, 
`Extent1`.`Description`, 
`Extent1`.`Images`, 
`Extent1`.`Votes`, 
`Extent1`.`Votes_sum`, 
`Extent1`.`Views`, 
`Extent1`.`Isvisible`, 
`Extent1`.`Isfrontpage`, 
`Extent1`.`Istoparticle`, 
`Extent1`.`Expires_date`, 
`Extent1`.`Date`
FROM `tcms_articles` AS `Extent1`
 WHERE `Extent1`.`Expires_date` >= '2012-06-24 13:41:47.816') AS `Project1`
 ORDER BY 
`Project1`.`Date` DESC LIMIT 4

执行此查询大约需要3.50秒。

说明此查询：

+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+
| id | select_type | table      | type  | possible_keys | key          | key_len | ref  | rows | Extra          |
+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+
|  1 | PRIMARY     |  | ALL   | NULL          | NULL         | NULL    | NULL | 4054 | Using filesort |
|  2 | DERIVED     | Extent1    | range | expires_date  | expires_date | 8       | NULL | 4053 | Using where    |
+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+

当我查询：

SELECT *
    FROM tcms_articles
    WHERE expires_date >= '2012-06-24 13:41:47.816'
    ORDER BY date DESC
    limit 4

我只有0.01秒...

跑着再解释一遍我得到：

+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tcms_articles | index | expires_date  | date | 8       | NULL |   11 | Using where |
+----+-------------+---------------+-------+---------------+------+---------+------+------+-------------+

我不明白为什么会这样。

Entity Framework 4.3 MySQL Connector Net 6.5.4.0

tcms_articles：

CREATE TABLE `tcms_articles` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `User_id` int(10) unsigned DEFAULT NULL,
  `Category_id` int(10) unsigned DEFAULT NULL,
  `Title` varchar(255) DEFAULT NULL,
  `Value` longtext,
  `Keywords` varchar(255) NOT NULL DEFAULT '',
  `Description` varchar(255) NOT NULL DEFAULT '',
  `Images` longtext NOT NULL,
  `Votes` int(10) unsigned NOT NULL DEFAULT '1',
  `Votes_sum` int(10) unsigned NOT NULL DEFAULT '5',
  `Views` int(10) unsigned NOT NULL DEFAULT '0',
  `Isvisible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `Isfrontpage` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Istoparticle` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `Expires_date` datetime NOT NULL DEFAULT '2099-12-31 00:00:00',
  `Date` datetime NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `article_users` (`User_id`) USING BTREE,
  KEY `article_section` (`Category_id`) USING BTREE,
  KEY `Isvisible_index2` (`Isvisible`) USING BTREE,
  KEY `Istoparticle_index2` (`Istoparticle`) USING BTREE,
  KEY `Expires_date_index2` (`Expires_date`) USING BTREE,
  KEY `isfrontpage2` (`Isfrontpage`) USING BTREE,
  KEY `Date_index2` (`Date`) USING BTREE,
  CONSTRAINT `tcms_articles_ibfk_1` FOREIGN KEY (`Category_id`) REFERENCES `tcms_categories` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tcms_articles_ibfk_2` FOREIGN KEY (`User_id`) REFERENCES `tcms_users` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8;

那么为什么Linq会产生这个查询，如何/可以修复这个问题呢？

Answer 1

repo.GetQuery<Article>().Where(foo=>foo.Expires_date>=date  -- Note 1
                        && foo.Istoparticle==true
                        && foo.Isfrontpage==false)
                    .OrderByDescending(foo=>foo.Date)       -- Note 2
                    .Take(4);

在这两个位置都使用foo.expires_date。