logo标签列表

MySQL子查询使查询变得非常缓慢

mysqlsqlquery-optimization
4

在优化以下查询（示例1）时，我一直感到困扰：

    SELECT `service`.* 
    FROM
    (
          SELECT `storeUser`.`storeId`
          FROM `storeUser`
          WHERE `storeUser`.`userId` = 1
      UNION
          SELECT `store`.`storeId`
          FROM `companyUser`
            INNER JOIN `store` ON `companyUser`.`companyId` = `store`.`companyId`
          WHERE `companyUser`.`userId` = 1
      UNION
      SELECT `store`.`storeId`
      FROM `accountUser`
        INNER JOIN `company` ON `company`.`accountId` = `accountUser`.`accountId`
        INNER JOIN `store` ON `company`.`companyId` = `store`.`companyId`
      WHERE `accountUser`.`userId` = 1
    ) AS `storeUser`

        INNER JOIN `service` ON `storeUser`.`storeId` = `service`.`storeId`
    LIMIT 10;

子查询应该返回类似"1"、"2"、"3"、"4"这样的内容。无论如何，它非常缓慢，需要约48秒才能给出响应，尽管该子查询本身在另一个控制台中运行只需约0.0020毫秒即可给出结果。如果将子查询放在IN语句中（示例2），情况也是一样的。
    SELECT `service`.*
    FROM `service`
    WHERE 1
    AND `service`.`storeId` IN (
        SELECT `storeUser`.`storeId` FROM `storeUser` WHERE `storeUser`.`userId` = 1
      UNION
        SELECT `store`.`storeId` FROM `companyUser` 
            INNER JOIN `store` ON `companyUser`.`companyId` = `store`.`companyId`
        WHERE `companyUser`.`userId` = 1
      UNION
        SELECT `store`.`storeId`
        FROM `accountUser`
            INNER JOIN `company` ON `company`.`accountId` = `accountUser`.`accountId`
            INNER JOIN `store` ON `company`.`companyId` = `store`.`companyId`
      WHERE `accountUser`.`userId` = 1
    )
    LIMIT 10;

但是，如果我手动输入查询返回的值，它基本上是立即响应的：
    SELECT
      `service`.*
    FROM
      `service`
    WHERE 1
    AND `service`.`storeId` IN (
      "1", "2", "3", "4", "5"
    )
    LIMIT 10;

需要重要提到的是，我已经检查了连接中的索引，一切似乎都就位了，而且“EXPLAIN [query]”返回的筛选分数基本上都是100分。

编辑：

之前没有提供足够的信息，很抱歉，希望这次可以更有帮助：

MySQL 5.7,
Storage engine: InnoDB

EXPLAINs
1.) StoreUser

id | select_type | table          | partitions | type | possible_keys          |  key     | key_len | ref   | rows  | filtered | Extra
1  | SIMPLE      | storeUser      | NULL       | ref  | PRIMARY, storeUserUser |  PRIMARY | 4       | const | 1     |100.00    | Using index

2.) CompanyUser

id  | select_type | table       | partitions | type | possible_keys                              | key              | key_len | ref                         | rows  | filtered  | Extra
1   | SIMPLE      | companyUser | NULL       | ref  | PRIMARY,companyUserCompany,companyUserUser | companyUserUser  | 4       | const                       | 30    | 100.00    | Using index
1   | SIMPLE      | store       | NULL       | ref  | storeCompany                               | storeCompany     | 4       | Table.companyUser.companyId | 5     | 100.00    | Using index

3.) AccountUser

id  | select_type | table         | partitions | type | possible_keys           | key             | key_len | ref                         | rows  | filtered  | Extra
1   | SIMPLE      | accountUser   | NULL       | ref  | PRIMARY,accountUserUser | accountUserUser | 4       | const                       | 1     | 100.00    | Using index
1   | SIMPLE      | company       | NULL       | ref  | PRIMARY,companyAccount  | companyAccount  | 4       | Table.accountUser.accountId | 305   | 100.00    | Using index
1   | SIMPLE      | store         | NULL       | ref  | storeCompany            | storeCompany    | 4       | Table.company.companyId     | 5     | 100.00    | Using index

4.) Whole query (Example 2)

id    | select_type         | table       | partitions  | type    | possible_keys                               | key             | key_len | ref                         | rows    | filtered  | Extra
1     | PRIMARY             | service     | NULL        | ALL     | NULL                                        | NULL            | NULL    | NULL                        | 2836046 | 100.00    | Using where
2     | DEPENDENT SUBQUERY  | storeUser   | NULL        | eq_ref  | PRIMARY,storeUserStore,storeUserUser        | PRIMARY         | 8       | const,func                  | 1       | 100.00    | Using index
3     | DEPENDENT UNION     | store       | NULL        | eq_ref  | PRIMARY,storeCompany                        | PRIMARY         | 4       | func                        | 1       | 100.00    | NULL
3     | DEPENDENT UNION     | companyUser | NULL        | eq_ref  | PRIMARY,companyUserCompany,companyUserUser  | PRIMARY         | 8       | const,Table.store.companyId | 1       | 100.00    | Using index
4     | DEPENDENT UNION     | companyUser | NULL        | ref     | PRIMARY,accountUserUser                     | accountUserUser | 4       | const                       | 1       | 100.00    | Using index
4     | DEPENDENT UNION     | store       | NULL        | eq_ref  | PRIMARY,storeCompany                        | PRIMARY         | 4       | func                        | 1       | 100.00    | NULL
4     | DEPENDENT UNION     | company     | NULL        | eq_ref  | PRIMARY,companyAccount                      | PRIMARY         | 4       | Table.store.companyId       | 1       | 100.00    | Using where
NULL  | UNION RESULT        | <union2,3,4>| NULL        | ALL     | NULL                                        | NULL            | NULL    | NULL                        | NULL    | NULL      | Using temporary
5
请提供所有相关信息，例如这些表的模式以及来自 EXPLAIN 的输出。 - RiggsFolly
1
将 service 集成到 UNION 的每个子查询中，分别优化每个子查询，然后 UNION，对它们的输出进行 ORDER BY 和 LIMIT。 - Akina
为了帮助您解决 [tag:query-optimization] 的问题，我们需要更多的信息。请阅读此内容，然后[编辑]您的问题。 - O. Jones
需要强调的是，我已经审查了连接中的索引，一切似乎都在正确的位置上。请向我们展示这些索引，以便我们可以进行验证。 - Andy Lester
1
您需要展示给我们表和索引的定义，以及每个表的行数。也许您的表定义得不好。也许索引没有正确生成。也许您认为自己已经在该列上建立了索引，但实际上并没有。如果没有看到表和索引定义，我们就无法判断。我们需要行数，因为它可能会影响查询计划。如果您知道如何执行“EXPLAIN”或获取执行计划，请将结果也放在问题中。如果您没有索引，请访问http://use-the-index-luke.com。 - Andy Lester
3个回答
4

你没有展示索引或者解释输出，因此这都是猜测。

显然是你第二个例子中的子查询没有被优化。那个子查询是一个有三个分支的UNION。你应该如何解决性能问题呢？分别分析和优化UNION的每个分支。

除非你的数据库服务器太小或者配置不当，否则你肯定需要一些更好的索引。这种情况非常罕见，所以我们要着手处理索引问题。

第一个分支是

 SELECT storeUser.storeId
   FROM storeUser
  WHERE storeUser.userId = 1

这个复合索引 覆盖了该查询。尝试添加它。如果你有一个只包含userId的单独索引，请在添加此索引时删除它。

ALTER TABLE storeUser ADD INDEX userId_storeId (userId, storeId);

第二个分支是

 SELECT store.storeId
   FROM companyUser
  INNER JOIN store  ON companyUser.companyId = store.companyId
  WHERE companyUser.userId = 1

使用JOIN操作的子查询，如果没有EXPLAIN输出来优化，则会有些棘手，因此这只是猜测。我猜这些索引会有所帮助。（假设您使用InnoDB并且store上的PK是storeId。）

ALTER TABLE companyUser ADD INDEX userId_companyId (userId, companyId);
ALTER TABLE store ADD INDEX companyId (companyId);

类似的分析适用于UNION的第三个分支。

此外，添加此索引。您的EXPLAIN指向它缺失，因此需要对该大表进行完全扫描。

ALTER TABLE service ADD INDEX storeId (storeId);

再次强调，如果您向我们展示带有索引的表定义，那么帮助您将变得更加容易。例如，SHOW CREATE TABLE service; 将为我们显示您的 service 表所需的内容。调试此类性能问题时的专业提示是始终要仔细检查索引。问我怎么知道的，当您有几个小时的空闲时间时再问我吧。
专业提示：要使查询易于阅读，请过分关注格式设置。您自己、一年后的您以及尚未出生的同事都需要阅读和推理。在我看来，这意味着跳过那些愚蠢的反引号。
我已经编辑了我的问题，希望您能发现额外的信息有用，非常感谢您抽出时间来回答。我已经检查过，在联合表（companyUser、accountUser、storeUser）中确实有复合键。 - Juan Carlos
1
即使使用了 EXPLAIN，也不能确定哪个表会被“首先”查看。这对最佳索引有影响。如果存在一个仅涉及其中一个表的 WHERE，那么很可能会首先使用该表。 - Rick James
1
@JuanCarlos - companyUser似乎有3个索引；通常只需要2个就足够了；也许您有一个不必要的auto_increment？ - Rick James
我在这里进行了一些编辑。 - O. Jones
1
也许您需要重新考虑模式。似乎您需要一个“用户”表，而不是或者除了三个不同类型的“用户”表。
同时，这些组合索引很可能会在任何形式下提高性能。
storeUser:  INDEX(storeId,  userId)
storeUser:  INDEX(userId,  storeId)
service:  INDEX(storeId)
store:  INDEX(companyId,  storeId)
companyUser:  INDEX(userId, companyId)
company:  INDEX(accountId,  companyId)
accountUser:  INDEX(userId, accounted)

添加复合索引时，需删除与其具有相同前导列的索引。也就是说，如果你同时拥有INDEX(a)和INDEX(a,b)，则应该删除前者。

特别地，storeUser 看起来像是一个多对多映射表。如果是这样，请查看多对多映射以获得更多讨论。

一般来说，IN(SELECT ...) 不会被很好地优化，但你可能会在你的查询中发现例外情况。

0

很抱歉没有提供有关模式的更多详细信息，但我不被允许在这里分享它，无论如何，问题出在其他地方： 服务表正在接收大量请求，有些操作甚至将其锁定，每当我们访问该表时都会变慢，我们已经修复了其他进程，现在它运行得非常好。非常感谢您的时间和精力，谢谢。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接