如何在MySQL中优化这个令人困惑的慢查询？

I have a table of blog posts, each with a foreign key back to it's author. There are < 15,000 entries in this table. This query scans over 19,000 rows (per EXPLAIN), requires a filesort (that might be regular MySQL behavior), and takes over 400ms to return 5 rows. possibly because of the complicated WHERE used to check if the item is actually published.

我有一个博客文章表,每个都有一个外键回到它的作者。此表中有<15,000个条目。此查询扫描超过19,000行(每个EXPLAIN),需要一个filesort(可能是常规的MySQL行为),并需要超过400毫秒才能返回5行。可能是因为用于检查项目是否实际发布的复杂WHERE。

Dearest Stack Overflow, how can I wrangle this query under control?

最亲爱的Stack Overflow,如何控制这个查询?

Note: while this criteria might be up for simplification, all of the conditions are required.

注意:虽然此标准可能需要简化,但所有条件都是必需的。

SELECT      `blog_post.id`, 
            `blog_post.title`,
            `blog_post.author_id`,
            `blog_post.has_been_fact_checked`,
            `blog_post.published_date`,
            `blog_post.ordering`,
            `auth_user.username`,
            `auth_user.email`
FROM        `blog_post` 
INNER JOIN  `auth_user` 
ON          (`blog_post`.`author_id` = `auth_user`.`id`) 
WHERE       (`blog_post`.`is_approved` = True  AND 
             `blog_post`.`has_been_fact_checked` = True  AND 
             `blog_post`.`published_date` IS NOT NULL AND 
             `blog_post`.`published_date` <= '2010-10-25 22:40:05' ) 
ORDER BY    `blog_post`.`published_date` DESC, 
            `blog_post`.`ordering` ASC, 
            `blog_post`.`id` DESC 
LIMIT 5

Aside from the PKs, I have the following indexes on the table:

除了PK,我在表上有以下索引:

idx_published_blog_post -> blog_post(is_approved, has_been_fact_checked, published_date)
idx_pub_date -> blog_post(published_date)

The output from EXPLAIN looks like this:

EXPLAIN的输出如下所示:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_post
         type: ref
possible_keys: blog_post_author_id,idx_published_blog_post,idx_pub_date
          key: idx_published_blog_post
      key_len: 4
          ref: const,const
         rows: 19856
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: auth_user
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: blog.blog_post.author_id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

Side-note: 2010-10-25 22:40:05 is just a date generated by the code that executes this query.

附注:2010-10-25 22:40:05只是执行此查询的代码生成的日期。

Thanks so much for any & all help!

非常感谢任何和所有帮助!

3 个解决方案

#1

MySQL does not support ASC/DESC clauses in indexes.

MySQL不支持索引中的ASC / DESC子句。

You would need to create a separate column called reverse_ordering and set its value to -ordering (provided that ordering is an numeric value)

您需要创建一个名为reverse_ordering的单独列,并将其值设置为-ordering(假设排序是一个数值)

Then you could create the following index:

然后,您可以创建以下索引:

CREATE INDEX ix_blogpost_a_c_p_ro_id ON blog_post (is_approved, has_been_fact_checked, published_date, reverse_ordering, id)

and rewrite your query:

并重写您的查询:

SELECT      `blog_post.id`, 
            `blog_post.title`,
            `blog_post.author_id`,
            `blog_post.has_been_fact_checked`,
            `blog_post.published_date`,
            `blog_post.ordering`,
            `auth_user.username`,
            `auth_user.email`
FROM        `blog_post` 
INNER JOIN  `auth_user` 
ON          `blog_post`.`author_id` = `auth_user`.`id`
WHERE       `blog_post`.`is_approved` = 1 AND 
            `blog_post`.`has_been_fact_checked` = 1 AND 
            `blog_post`.`published_date` <= '2010-10-25 22:40:05'
ORDER BY    `blog_post`.`published_date` DESC, 
            `blog_post`.`reverse_ordering` DESC, 
            `blog_post`.`id` DESC 
LIMIT 5

You may get rid of IS NULL check, since the inequality condition implies it.

你可以摆脱IS NULL检查,因为不等式条件意味着它。

Update:

You also might want to read this article:

您可能还想阅读这篇文章:

Mixed ASC/DESC sorting in MySQL

在MySQL中混合ASC / DESC排序

#2

Make a view of "blog_post" with all conditions you have applied in query (where clause) and do the joining of "auth_user" direct with this view.

使用您在query(where子句)中应用的所有条件来查看“blog_post”,并使用此视图直接连接“auth_user”。

feel free ask if not clear. :)

如果不清楚,请随意询问。 :)

#3

To me it looks like the filesort may be killing the speed. If you can get the ORDER BY fields into the index that is being used you may get a speed increase. Try changing:

对我来说,看起来文件可能会扼杀速度。如果您可以将ORDER BY字段放入正在使用的索引中,则可能会提高速度。尝试改变:

idx_published_blog_post -> blog_post(is_approved, has_been_fact_checked, published_date)

idx_published_blog_post - > blog_post(is_approved,has_been_fact_checked,published_date)

idx_published_blog_post -> blog_post(is_approved, has_been_fact_checked, published_date DESC, ordering ASC, id DESC)

idx_published_blog_post - > blog_post(is_approved,has_been_fact_checked,published_date DESC,订购ASC,id DESC)

A couple of after thoughts: under what circumstances do you have a null published_date, searching in a date range may be quicker? Also published_date seems to be a date time field, so when sorting do you really have so may posts each second that you require the other sorting fields?

几个想法:在什么情况下你有一个null published_date,在日期范围内搜索可能会更快? Published_date似乎也是一个日期时间字段,所以在排序时你真的有这样的帖子,你可能会每秒发布一次你需要其他排序字段吗?

#1