主页 / dba / 问题 / 316004
Accepted
Blackbam
Asked: 2022-08-25 06:41:41 +0800 CST

MySQL：提高大型表的性能，以正确使用复合索引动态构建查询

  • 772

假设我有一个包含约 3000 万个条目和 40 列的 MySQL 表，我有一个高度活跃的查询（5 个查询/秒），它非常慢（平均约 20 秒）并且扫描的行数很高（平均 50.000行）。随着表的增长，性能越来越差。我想通过添加正确的复合甚至覆盖索引来解决问题。

教义查询由动态查询构建器构建，涉及以下属性（任何查询中仅使用 userId，所有其他列有时仅用于过滤）：

  • 总是：user_idint 有=[> 1 m 用户，但单个用户可能有 > 200K 条目]
  • 有时：带有[7 种可能性]status的 varchar(20)IN()
  • 有时：expiration_timestamp带有<[可以是任何时间戳]的日期时间
  • 有时：typevarchar(20)( 有IN()[7 种可能性]
  • 罕见：namevarchar(255) 带有LIKE[带有尾随通配符，很少重复]
  • 非常罕见：带有[前导通配符和尾随通配符] 的tagsvarchar(2000)LIKE
  • 经常：orderBy id int DESC[id为主键，orderBy是必须的]

未经测试（将需要具有维护窗口的生产部署，包括短停机时间）我会提出以下解决方案：

CREATE INDEX listing ON items(user_id,status,type,name,expiration_timestamp,id);

这是我的推理：首先，user_id总是与相等比较一起使用，所以这应该是第一个。status并且type有一个IN子句，因此它们应该是第二个。第三个是name，因为即使LIKE使用尾随通配符，它​​也是高度选择性的。索引expiration_timestamp将有助于显着减少结果的数量。id由于 MySQL 使用索引进行排序，因此将 放在复合索引的末尾是有意义的。没有理由将标签放入索引中，因为带有前导通配符的 LIKE 上的索引是无用的。

这是正确的方法还是你会建议在这里改进一些东西？

还有一个我不确定的事实：如果查询没有类型或状态，MySQL 是否足够“智能”以使用我的复合索引？对 MySQL 索引还是很陌生，感谢您的帮助！

mysql performance

3 个回答

  1. Best Answer

    这样的索引有几个问题。

    您描述的所有条件user_id = ?都被视为范围条件。范围条件是在每种情况下匹配多个值的任何条件。所以<, IN(), LIKE, 都是范围条件。

    这是第一个问题：在复合索引中，只会使用范围条件中涉及的一列。

    示例：假设您在假设表中的 (a,b,c) 上有一个索引。

    SELECT ... WHERE a = 1 AND b < 10 AND c IN (1,2,3)

    这将仅使用索引的 (a,b) 列。在范围条件中使用第一列之后，需要逐行评估索引后续列的条件。

    实际上，有一种缓解方法，即index condition pushdown。这会自动发生。但这不如真正的索引查找好。

    第二个问题是索引中使用的列必须是连续的。如果您尝试“跳过”一列，它不能使用索引中的列。

    例子：

    SELECT ... WHERE a = 1 AND c IN (1,2,3)

    我说过，除了用于相等的列之外，您还可以拥有一列，这个示例查询似乎满足了这一要求。但是，如果索引在列 (a,b,c) 上，但此查询中没有 b 上的条件，则 c 列上的条件也不能使用索引。

    第三个问题是 ORDER BY 优化也被查询中的任何范围条件所破坏。也就是说，一旦查询执行了范围条件，排序顺序就不会隐含在索引顺序中。

    所以底线是，给定您的动态查询，在给定的运行中可能包含或不包含不同条件的混合，您无法创建满足所有情况的单个复合索引。

    您可以做的是创建几个复合索引：

    (user_id, status)
(user_id, expiration_timestamp)
(user_id, type) 
(user_id, name)

    然后让优化器根据包含的动态条件选择与给定查询最相关的查询。

    但无论如何，ORDER BY id都需要文件排序。

    • 3
    1. 所有“ =”列在前—— user_id。
    2. 然后是部分或全部IN列——如果 SELECT has only one item in theIN [How often does that happen?], the Optimizer that into an=`. 此外，可能存在优化器可以跳过索引的情况。
    3. 然后任何范围测试： BETWEEN, '<',LIKE没有前导通配符等--expiration_timestamp

    由于查询是动态创建的，因此您应该在（在限制范围内）添加额外的列。但是，让任何索引与另一个索引的前几列完全匹配也是不明智的。所以，摆脱INDEX(user_id).

    为了处理单值 IN 案例，我将添加 Bill 的建议：

    (user_id, status, expiration_timestamp)
(user_id, expiration_timestamp)
(user_id, type, expiration_timestamp) 
(user_id, name)

    至于ENUM(1 字节) for statusand type; 这是值得考虑的，因为它会节省大约 GB 的磁盘空间（在数据和具有该列的每个索引之间）。它不会明显改变任何索引的性能。

    使用合适的值打开慢日志long_query_time。稍后检查慢日志以查看哪些列组合导致查询最慢。然后小心地添加更多的复合索引。

    您可能会遇到的异常情况。使用ORDER BY id DESC，一些查询将能够避免“文件排序”。

    在PRIMARY KEY每个辅助键的末尾默默地添加了 。所以，如果你有一个只包含=测试的 WHERE 子句，id最后的 将避免文件排序。

    • 2

  3. 我想补充一点，拥有一些通用分析工具非常有帮助。我正在使用 JetProfiler，但还有其他替代品，例如 Percona Toolkit。

    在某些情况下，我能够找到一些特定的查询集，这些查询令人惊讶地是真正的罪魁祸首，而不是我预期的问题。

    • 1

相关问题