MySQL慢速连接 - 但并非总是而不是所有表 [英] MySQL Slow join - but not always and not on all tables

问题描述

我们遇到了一个MySQL数据库的性能问题很奇怪我们需要另外一组眼睛来告诉我们我们是否疯了。
我们团队中有2位MySQL认证开发人员，但他们只能说：这是不可能的。

We're experiencing a performance issue with a MySQL database that's so weird we need another set of eyes to tell us whether we're going crazy or not. We've got 2 MySQL Certified Developers in the team, but all they can say is : "this is impossible".

无论如何，情况如下：我们有一个理论上应该合理快速的查询，但实际上是缓慢的。如果我们通过删除1个连接来减少查询，则查询变得非常快。如果我们删除不同的连接，它仍然非常慢，尽管连接表具有几乎相同的结构。更糟糕的是：连接有时很快，有时候不是......它似乎是某种随机问题，虽然它与服务器负载无关，因为我在本地系统上也有。

Anyway, here's the situation : we have a query that in theory should be reasonbly fast, but in reality is slow. If we slim down the query by removing 1 join, the query becomes extremely fast. If we remove a different join, it's still very slow, although the joined table has nearly the same structure. Worse even : the joins are SOMETIMES fast, sometimes not... it seems it's a random problem of some kind, although it has nothing to do with server load, since I have it on my local system too.

表结构如下所示：

Table : article Rows : 57491
Field            Type                 Null   Key     Default     Extra
arti_id          int(10) unsigned     NO     PRI                 auto_increment
prev_id          int(10) unsigned     YES    MUL                 (null)
news_id          int(10) unsigned     NO     MUL                 (null)
cate_id          int(10) unsigned     NO     MUL                 (null)
pdf_id           int(10) unsigned     YES    MUL                 (null)
imag_id          int(10) unsigned     YES    MUL                 (null)
publication_date date                 NO     MUL                 (null)
title            varchar(255)         NO     MUL                 (null)
full_text        text                 YES    (null)              (null)

Table : category Rows : 3
Field            Type                 Null   Key     Default     Extra
cate_id          int(10) unsigned     NO     PRI                 auto_increment
code             varchar(7)           NO     (null)              (null)

Table : language Rows : 4
Field            Type                 Null     Key     Default     Extra
lang_id          int(10) unsigned     NO       PRI                 auto_increment
code             varchar(2)           NO       (null)              (null)

Table : newspaper Rows : 393
Field            Type                 Null     Key     Default     Extra
news_id          int(10) unsigned     NO       PRI                 auto_increment
lang_id          int(10) unsigned     NO       MUL                 (null)
name             varchar(255)         NO       UNI                 (null)

现在出现了奇怪的部分：你可以看到046_newspaper和046_category都有一个主键（幸运）。它们都是通过外键从a046_article引用的。当我们运行以下查询时：

Now comes the weird part : as you can see 046_newspaper and 046_category both have a primary key (luckily). They're both referenced from a046_article by a foreign key. When we run the following query :

SELECT SQL_NO_CACHE
    article.*
FROM
    article
        INNER JOIN
        newspaper AS `n`
        ON
        article.news_id = n.news_id
ORDER BY
    article.publication_date DESC
LIMIT
    50

我们在0.016秒后得到一个结果，这非常快。

We get a result after 0.016 seconds, which is pretty fast.

现在，当我们通过类别的加入替换报纸的加入时：

Now when we replace the join with newspaper by a join with category :

SELECT SQL_NO_CACHE
    article.*
FROM
    article
        INNER JOIN
        category AS `c`
        ON
        article.cate_id = c.cate_id
ORDER BY
    article.publication_date DESC
LIMIT
    50

查询需要1.02秒。

奇怪的是，并非总是这样。有时，由于没有明显的原因，第一个查询也需要很长时间。

The odd thing is that this isn't always the case. Sometimes, for no apparent reason, the first query takes about that long too.

最后我们要做的是：

SELECT SQL_CALC_FOUND_ROWS
    *,
    `n`.`name` AS `news_name`,
    `c`.`cate_id`,
    `c`.`code` AS `cate_name`,
    `l`.`code` AS `lang_name`
FROM
    `article`
        INNER JOIN
        `newspaper` AS `n`
        ON
        article.news_id = n.news_id
            INNER JOIN
            `category` AS `c`
            ON
            article.cate_id = c.cate_id
                INNER JOIN
                `language` AS `l`
                ON
                n.lang_id = l.lang_id
ORDER BY
    `article`.`publication_date` DESC
LIMIT
  50

此时需要12秒。这部分是由于*，我们可以用单个字段替换，但是它仍然需要3秒。

which takes over 12 seconds at this point. This is partly due to the *, which we could replace by individual fields, but then it still takes 3 seconds.

我们尝试了很多东西：
- 添加索引（尽管所有必需的索引已经存在并且添加更多只是一个坏主意）
- 增加排序缓冲区大小和密钥缓冲区
- 看看解释很多...
- 一遍又一遍地阅读MySQL手册
- 阅读很多论坛
然而，没有这样的事情解决了这个问题。

We've tried a number of things : - Adding indexes (although all required indexes were there already and adding more is simply a bad idea) - Increasing the sort buffer size and key buffer - Looking at explain a lot... - Reading the MySQL manual over and over again - Reading a lot of forums However, nothing like this has solved the issue.

如果有人有任何想法，请随时大喊！如果您需要SQL脚本甚至可以访问数据库，那么您可以尝试一下，让我知道......我们的客户对这些慢页面抱怨很多......

If anyone has any ideas, feel free to shout ! If you need the SQL-script or even access to the database, so you can give it a try, let me know... our client is complaining a lot about the slow pages...

谢谢！

推荐答案

我认为您可以尝试使用标量查询。

I think you can try with scalar query .

这篇关于MySQL慢速连接 - 但并非总是而不是所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！