代码之家 › 专栏 › 技术社区 › Dennis G.

MySQL性能优化:按日期时间字段排序

select performance mysql

Dennis G. · 技术社区 · 16 年前

我有一个大约有10万篇博客帖子的表,通过1:n关系链接到一个有50个提要的表。当我使用select语句(按postings表的datetime字段排序)查询这两个表时,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_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正在使用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使用索引对数据进行排序和选择。我已经尝试过一些事情,比如在所有where/orderby字段上创建一个组合索引,正如一些博客帖子所建议的那样,但这也不起作用。

3 回复 | 直到 16 年前

Quassnoi 16 年前

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

它将同时用于在上进行过滤 is_active 以及订购 post_date .

MySQL REF 中此索引的访问方法 EXPLAIN EXTENDED .

请注意,您有一个 RANGE 过滤条件超过 user_offtopic_count ,这就是为什么在筛选和按其他字段排序时不能在此字段上使用索引的原因。

用户\u主题\u计数 (即,满足以下条件的行数: user_offtopic_count < 10 用户\u主题\u计数 然后让我们来整理一下发帖日期。

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

哪个索引更快取决于您的数据分布。创建两个索引, 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_website
FROM 
    `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` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    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_website
FROM 
    `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` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show REF access with lots of rows and no FILESORT */

ÊÉÄ±u 16 年前

MySQL有两种文件排序算法:一种是对磁盘上的记录进行排序的旧文件排序算法,另一种是在内存中工作的新文件排序算法。

或

MySQL选择使用is_active列,表面上是因为它认为该列在继续使用其他连接和where条件之前最有选择性地删除行。我建议的第一件事是尝试使用post_date、feed_id和where条件中的列创建复合索引,例如(is_active、user_offtopic_count、post_date、feed_id)。

Chris Henry 15 年前

另外,重要的是要记住,如果您排序的列应用了一个函数,那么MySQL不会使用索引。