主页 / dba / 问题 / 314235
Accepted
Blackbam
Asked: 2022-07-11 03:49:31 +0800 CST

MySQL：如何优化导致负载非常高的某个 SELECT 语句？

  • 772

有一个包含 25.000.000 个条目的表，我有以下查询，每 2 秒启动一次，这会导致非常高的负载（亚马逊 AWS 中高达 40 AAS）。执行需要 20 秒到 5 分钟，这甚至会导致用户浏览器超时和高丢弃率。

SELECT COUNT ( * ) AS `chk` 
  FROM ( SELECT `item_id` 
           FROM `items` 
          WHERE `item_status` IN (...) 
            AND `item_type` = ? 
            AND `user_id` != ? 
            AND `item_name` IN (...) 
          LIMIT 3 
       ) AS OTHERS
;

索引优化已经完成 - user_id, item_name,item_type并且item_status都被索引（每列一个索引）。

更多信息：

  • 一个用户有 1 - 1.000.000 百万个条目
  • item_name 是varchar128
  • item_type 的基数为 7
  • item_status 的基数也为 7
  • 只需要知道是否有三个或更多匹配项

请注意，在大约 50% 的情况下，MySQL 必须检查完整的表，因为找到的项目少于 3 个。所以这个限制只有在超过 3 个项目的情况下才有帮助。

虽然我对某些结果进行 Redis 缓存，但对于此查询，这是不可能的，因为始终需要准确的结果。随着数据库以每秒约 1 个条目的速度增长，查询性能变得非常快。

虽然过去我可以解决大多数索引问题，但这里有一个严重的问题。想过用一些触发器或视图来解决问题，但我不确定这是否有帮助？在高度活跃的生产数据库中，在不知道是否解决问题的情况下进行此类更改是危险的。

我在这里向专业人士提出的问题是：如何用 MySQL 解决这个问题？

mysql performance

2 个回答

  1. Best Answer

    您可以尝试使用复合索引。

    您的索引可能如下所示：

    INDEX (item_type, item_name, item_status, user_id)

    该索引应该允许 MySQL 有效地查找所有行，而无需从实际表中查找任何行。

    列的顺序在这里很重要：

    • item_type排在第一位，因为您在列上有一个相等过滤器。
    • item_name并排在第二和item_status第三，因为两者都使用IN. 列的顺序可以更改，但因为item_name可能比item_status首先拥有更高的基数可能会更快一些。
    • user_id排在最后，因为它使用了一个不能用索引有效处理的不等式过滤器。

    一般来说，MySQL 每个表只使用 1 个索引¹，因此拥有超过 1 个索引并没有帮助。

    此外，如果索引看起来不会过滤足够多的行（查询计划器根据表和索引统计信息计算出足够多的行），MySQL 也不会使用索引。这样做的原因是，当使用索引时，MySQL 仍然需要查找表中的原始行（除非您有覆盖索引²），这有点昂贵，因此使用索引查找大量行实际上可能比进行全表扫描。

    鉴于您的表中的行数，如果不是全部，您的 4 个现有索引中的大多数可能每个值都有太多行，并且 MySQL 甚至不会考虑此查询。尽管这实际上取决于数据分布和基数。

    您可以检查使用了哪些索引以及它们是否覆盖或不使用EXPLAIN. 例如

    EXPLAIN SELECT COUNT ( * ) AS `chk` FROM ( SELECT `item_id` FROM `items` WHERE `item_status` IN (...) AND `item_type` = ? AND `user_id` != ? AND `item_name` IN (...) LIMIT 3 ) AS OTHERS;

    如果您使用 MySQL 8，您还可以使用它EXPLAIN ANALYZE来实际了解 MySQL 如何读取和过滤计时数据。

    Rick James 写了一篇很棒的文档，介绍如何为 SELECT 构建最佳索引，我建议您阅读该文档。他还写了一篇关于复合（复合）索引的文档，我也可以推荐。

    将来请尝试至少提供表模式和索引（您可以SHOW CREATE TABLE ?用于此）以及运行有问题的查询的输出EXPLAIN以及 MySQL 版本。这减少了我们必须做的猜测，并允许我们给出更具体和更好的答案。

    ¹ 有一个优化，MySQL 可以使用多个索引，称为索引合并优化，但它只在极少数情况下有效，应该避免。

    ² 覆盖索引是包含查询中使用的所有列的索引。由于所有列都已经在索引中，MySQL 不需要从表本身获取其他列。我在这个答案中提出的索引是覆盖索引的一个例子。

    • 9

  2. 在复合索引之上，请尝试确保您要索引的所有列都是另一个表的外键，这应该会使大表上的索引更小，假设您的查找记录比主表少得多。即使他们不这样做，它仍然可以工作：

    例如：

    5000Kb/sec 磁盘扫描速度（100GB 磁盘是您可以在 AWS 上合理委托的最低磁盘） item_name 中的 1,000,000 条记录，item_status 中的 < 100 条记录：

    • item_name 表为 125KB，索引扫描需要 25ms
    • 主表索引约为 400K，索引扫描需要 80 毫秒

    如果您无法更改数据模型，则可以使用临时表强制以这种方式处理查询：

    create temporary table search_item_name(
    id int(11) autoincrement not null,
    primary key(id)
)

insert into search_item_name
select id from main_table where item_name in ()

    然后在您的主查询中，将 search_item_name 与内部连接连接起来。

    • 2

相关问题