时间戳

首页 > 解决方案 > PHP; 随着 WHERE 条件更新，对大型数据集的 MySQL JOIN 查询变慢

mysql - PHP; 随着 WHERE 条件更新，对大型数据集的 MySQL JOIN 查询变慢

问题描述

所以这可能有点傻，但我使用的替代方案更糟。我正在尝试使用我的数据库中的数据和一个名为Box/Spout的 PHP 工具编写一个 excel 表。问题是 Box/Spout 一次读取一行，并且不通过索引检索它们（例如 rows[10], rows[42], rows[156] ）

我需要按照行出现的顺序从数据库中检索数据。我有一个包含客户列表的数据库，通过它进来，Import我必须将它们写入 excel 电子表格。他们有电话号码、电子邮件和地址。很抱歉造成混乱......：/所以我编译了这个相当复杂的查询：

SELECT
      `Import`.`UniqueID`,
      `Import`.`RowNum`,
      `People`.`PeopleID`,
      `People`.`First`,
      `People`.`Last`,
      GROUP_CONCAT(
        DISTINCT CONCAT_WS(',', `PhonesTable`.`Phone`, `PhonesTable`.`Type`)
          ORDER BY `PhonesTable`.`PhoneID` DESC
          SEPARATOR ';'
      ) AS `Phones`,
      GROUP_CONCAT(
        DISTINCT CONCAT_WS(',', `EmailsTable`.`Email`)
          ORDER BY `EmailsTable`.`EmailID` DESC
          SEPARATOR ';'
      ) AS `Emails`,
      `Properties`.`Address1`,
      `Properties`.`city`,
      `Properties`.`state`,
      `Properties`.`PostalCode5`,
      ...(17 more `People` Columns)...,
      FROM `T_Import` AS `Import`
      LEFT JOIN `T_CustomerStorageJoin` AS `CustomerJoin` 
        ON `Import`.`UniqueID` = `CustomerJoin`.`ImportID`
      LEFT JOIN `T_People` AS `People` 
        ON `CustomerJoin`.`PersID`=`People`.`PeopleID`
      LEFT JOIN `T_JoinPeopleIDPhoneID` AS `PeIDPhID` 
        ON `People`.`PeopleID` = `PeIDPhID`.`PeopleID`
      LEFT JOIN `T_Phone` AS `PhonesTable` 
        ON `PeIDPhID`.`PhoneID`=`PhonesTable`.`PhoneID`
      LEFT JOIN `T_JoinPeopleIDEmailID` AS `PeIDEmID` 
        ON `People`.`PeopleID` = `PeIDEmID`.`PeopleID`
      LEFT JOIN `T_Email` AS `EmailsTable` 
        ON `PeIDEmID`.`EmailID`=`EmailsTable`.`EmailID`
      LEFT JOIN `T_JoinPeopleIDPropertyID` AS `PeIDPrID` 
        ON `People`.`PeopleID` = `PeIDPrID`.`PeopleID` 
        AND `PeIDPrID`.`PropertyCP`='CurrentImported'
      LEFT JOIN `T_Property` AS `Properties` 
        ON `PeIDPrID`.`PropertyID`=`Properties`.`PropertyID`
      WHERE `Import`.`CustomerCollectionID`=$ccID
        AND `RowNum` >= $rnOffset 
        AND `RowNum` < $rnLimit 
      GROUP BY `RowNum`;

所以我对每个ON段和WHERE段都有索引。当RowNumber值约为 0->2500 时，查询运行良好并在几秒钟内执行。但似乎查询执行时间成倍地增加了较大的 RowNumber。

我在EXPLAIN这里有一个：在pastebin（https://pastebin.com/PksYB4n2）

id  select_type table         partitions  type    possible_keys                     key                   key_len   ref                                          rows    filtered    Extra
1   SIMPLE      Import        NULL        ref     CustomerCollectionID,RowNumIndex  CustomerCollectionID  4         const                                        48108   8.74        Using index condition; Using where; Using filesort;
1   SIMPLE      CustomerJoin  NULL        ref     ImportID                          ImportID              4         MyDatabase.Import.UniqueID                       1   100         NULL
1   SIMPLE      People        NULL        eq_ref  PRIMARY,PeopleID                  PRIMARY               4         MyDatabase.CustomerJoin.PersID                   1   100         NULL
1   SIMPLE      PeIDPhID      NULL        ref     PeopleID                          PeopleID              5         MyDatabase.People.PeopleID                       8   100         NULL
1   SIMPLE      PhonesTable   NULL        eq_ref  PRIMARY,PhoneID,PhoneID_2         PRIMARY               4         MyDatabase.PeIDPhID.PhoneID                      1   100         NULL
1   SIMPLE      PeIDEmID      NULL        ref     PeopleID                          PeopleID              5         MyDatabase.People.PeopleID                       5   100         NULL
1   SIMPLE      EmailsTable   NULL        eq_ref  PRIMARY,EmailID,DupeDeleteSelect  PRIMARY               4         MyDatabase.PeIDEmID.EmailID                      1   100         NULL
1   SIMPLE      PeIDPrID      NULL        ref     PeopleMSCP,PeopleID,PropertyCP    PeopleMSCP            5         MyDatabase.People.PeopleID                       4   100         Using where
1   SIMPLE      Properties    NULL        eq_ref  PRIMARY,PropertyID                PRIMARY               4         MyDatabase.PeIDPrID.PropertyID                   1   100         NULL

如果格式非常糟糕，我深表歉意。我不确定好的格式是什么样的，所以我可能不小心把它弄乱了，而且标签搞砸了。

我想知道的是如何加快查询时间。数据库非常大，例如数百万行。而且它们并不总是这样，因为我们的桌子在不断变化，但是我希望能够在它们发生变化时处理它。

例如，我尝试使用 LIMIT 2000, 1000，但我知道它的效率低于使用索引列。所以我切换到 RowNumber。我觉得这是一个很好的决定，但似乎 MySQL 仍在循环偏移变量之前的每一行，这违背了我的索引的目的......我想？我不确定。我还基本上将这个特定查询拆分为大约 10 个单个查询，并针对 excel 文件的每一行逐个运行它们。这需要很长时间……太长了。这很快，但是，显然我遇到了问题。

任何帮助将不胜感激，并提前感谢您。我再次为我缺乏帖子组织而感到抱歉。

标签： mysqlperformance

解决方案

  • 索引中列的顺序很重要。子句的顺序WHERE无关紧要（通常）。
  • INDEX(a), INDEX(b)与“复合”不同INDEX(a,b)。我特意在它们看起来有用的地方制作了复合索引。
  • INDEX(a,b)和INDEX(b,a)不可互换，除非两者a和b都用 进行了测试=。（加上一些例外。）
  • “覆盖”索引是一个表的所有列都在一个索引中找到的索引。这有时会提供额外的性能提升。我推荐的一些索引是“覆盖”。这意味着只需要访问索引BTree，而不需要访问数据BTree；这是它加快速度的地方。
  • 在EXPLAIN SELECT ...“覆盖”索引中，由“使用索引”表示（与“使用索引条件”不同）。（您的解释目前没有显示覆盖索引。）
  • 索引“不应该”有超过 5 列。（这不是一个硬性规定。） T5的索引有 f5 列要覆盖；为 .建立一个覆盖索引是不切实际的T2。

当 时JOINing，表格的顺序无关紧要；优化器可以随意打乱它们。但是，这些“规则”适用：

  • ALEFT JOIN 可能会强制对表格进行排序。（我认为在这种情况下确实如此。）（我根据我认为优化器想要的内容对列进行了排序；可能会有一些灵活性。）
  • 该WHERE子句通常确定要“从哪个表开始”。（你T1只测试，所以很明显它将从 T1 开始。
  • 要引用的“下一个表”（通过 NLJ - 嵌套循环连接）由多种因素决定。（在您的情况下，这很明显 - 即ON列。）

更多关于索引：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql

修改后的查询

1. Import:  (CustomerCollectionID,  -- '=' comes first
             RowNum,                -- 'range'
             UniqueID)              -- 'covering'
    Import shows up in WHERE, so is first in Explain; Also due to LEFTs
Properties:  (PropertyID)   -- is that the PK?
PeIDPrID:  (PropertyCP, PeopleID, PropertyID)
3. People:  (PeopleID)
    I assume that is the `PRIMARY KEY`?  (Too many for "covering")
    (Since `People` leads to 3 other table; I won't number the rest.)
EmailsTable:  (EmailID, Email)
PeIDEmID:  (PeopleID,    -- JOIN from People
            EmailID)     -- covering
PhonesTable:  (PhoneID, Type, Phone)
PeIDPhID:  (PeopleID, PhoneID)
2. CustomerJoin:  (ImportID,   -- coming from `Import` (see ON...)
                   PersID)     -- covering

添加这些之后，我希望大多数行EXPLAIN都说Using index.

缺少至少一个综合索引Import是导致您的绩效投诉的主要问题。

不好的 GROUP BY

当存在GROUP BY不包括不直接依赖于 group by 列的所有非聚合列时，您将获得额外的随机值。我从EXPLAIN（“行”）中看到几个表可能有多行。你真的应该考虑一下这个查询产生的垃圾。

奇怪的是，电话和电子邮件被输入GROUP_CONCAT()，从而避免了上述问题，但“行”只有 1。

（阅读一下ONLY_FULL_GROUP_BY；它可能会更好地解释这个问题。）

推荐阅读