mysql varchar转int_MySQL性能优化：按日期时间字段排序

我有一张大约有100000个博客文章的表格，通过1：n关系链接到一个有50个Feed的表格。当我用一个select语句查询这两个表时，由发布表的日期时间字段排序，MySQL总是使用filesort，导致查询时间非常慢(> 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_websiteFROM     (`postings`)JOIN     `feeds` ON     `feeds`.`id` = `postings`.`feed_id`WHERE     `feeds`.`type` = 1 AND     `postings`.`user_offtopic_count` < 10 AND     `postings`.`is_active` = 1ORDER BY     `postings`.`post_date` descLIMIT     15  

这个查询的explain extended命令的结果显示MySQL正在使用filesort：

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+| 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停止使用filesort。求帮忙，如果你有任何想法如何优化这个查询让MySQL排序和选择使用索引的数据。我已经尝试了一些东西，比如像在一些博客帖子中提到的那样，在所有的字段/字段上创建一个组合索引，但是这也不起作用。

最佳解决思路

在postings (is_active, post_date)上创建一个复合索引(按此顺序)。

它将用于is_active的过滤和post_date的排序。

MySQL应在EXPLAIN EXTENDED中显示REF访问方法。

请注意，通过user_offtopic_count您有一个RANGE过滤条件，这就是为什么您不能使用该字段的索引在过滤和其他字段进行排序。

根据user_offtopic_count的选择性(即多少行满足user_offtopic_count < 10)的不同，在user_offtopic_count上创建索引并对post_dates进行排序可能会更有用。

为此，请在postings (is_active, user_offtopic_count)上创建一个复合索引，并确保使用了通过此索引的RANGE访问方法。

哪个索引将更快取决于您的数据分布。创建两个索引，FORCE他们，看看哪个更快：

CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);CREATE INDEX ix_active_date ON postings (is_active, post_date);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_websiteFROM     `postings` FORCE INDEX (ix_active_offtopic)JOIN     `feeds` ON     `feeds`.`id` = `postings`.`feed_id`WHERE     `feeds`.`type` = 1 AND     `postings`.`user_offtopic_count` < 10 AND     `postings`.`is_active` = 1ORDER BY     `postings`.`post_date` descLIMIT     15/* This should show RANGE access with few rows and keep the FILESORT */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_websiteFROM     `postings` FORCE INDEX (ix_active_date)JOIN     `feeds` ON     `feeds`.`id` = `postings`.`feed_id`WHERE     `feeds`.`type` = 1 AND     `postings`.`user_offtopic_count` < 10 AND     `postings`.`is_active` = 1ORDER BY     `postings`.`post_date` descLIMIT     15/* This should show REF access with lots of rows and no FILESORT */

参考资料

• MySQL performance optimization: order by datetime field