c# - Mysql - "order by"/Subquery 上的 Entity Framework 性能问题

Question

我有一个存储 3k 条记录的表，

我是 Entity Framework 和 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

It takes about 3.50 sec to exequte this query.

Explain of this query :

+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+| 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    |+----+-------------+------------+-------+---------------+--------------+---------+------+------+----------------+

When i query :

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.3MySQL 连接器网 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。