mysql按照时间字段升序查询_MySQL性能优化：按日期时间字段排序

我有一个表，该表包含大约100.000个博客文章，并通过1：n关系链接到具有50个供稿的表。当我用select语句查询两个表时(按发布表的datetime字段排序)，MySQL始终使用文件排序，导致查询时间非常慢(>

1秒)。这是postings表的架构(简化)：

+---------------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| feed_id | int(11) | NO | MUL | NULL | |

| crawl_date | datetime | NO | | NULL | |

| is_active | tinyint(1) | NO | MUL | 0 | |

| link | varchar(255) | NO | MUL | NULL | |

| author | varchar(255) | NO | | NULL | |

| title | varchar(255) | NO | | NULL | |

| excerpt | text | NO | | NULL | |

| long_excerpt | text | NO | | NULL | |

| user_offtopic_count | int(11) | NO | MUL | 0 | |

+---------------------+--------------+------+-----+---------+----------------+

这是feed桌子：

+-------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| type | int(11) | NO | MUL | 0 | |

| title | varchar(255) | NO | | NULL | |

| website | varchar(255) | NO | | NULL | |

| url | varchar(255) | NO | | NULL | |

+-------------+--------------+------+-----+---------+----------------+

这是执行时间超过1秒的查询。请注意，该post_date字段具有索引，但MySQL并未使用它对发布表进行排序：

SELECT

`postings`.`id`,

UNIX_TIMESTAMP(postings.post_date) as post_date,

`postings`.`link`,

`postings`.`title`,

`postings`.`author`,

`postings`.`excerpt`,

`postings`.`long_excerpt`,

`feeds`.`title` AS feed_title,

`feeds`.`website` AS feed_website

FROM

(`postings`)

JOIN

`feeds`

ON

`feeds`.`id` = `postings`.`feed_id`

WHERE

`feeds`.`type` = 1 AND

`postings`.`user_offtopic_count` < 10 AND

`postings`.`is_active` = 1

ORDER BY

`postings`.`post_date` desc

LIMIT

15

该explain extended查询命令的结果表明MySQL正在使用文件排序：

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

| 1 | SIMPLE | postings | ref | feed_id,is_active,user_offtopic_count | is_active | 1 | const | 30996 | Using where; Using filesort |

| 1 | SIMPLE | feeds | eq_ref | PRIMARY,type | PRIMARY | 4 | feedian.postings.feed_id | 1 | Using where |

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

当我删除该order

by部分时，MySQL停止使用文件排序。如果您对如何优化此查询以使MySQL通过使用索引来排序和选择数据有任何想法，请告诉我。我已经尝试了一些方法，例如，根据一些博客文章的建议，在所有按位置/按字段排序的字段上创建了一个组合索引，但是这也不起作用。