mysql - sql - 为什么要扫描的行数较少的查询(根据解释)实际上比行数多的查询运行得慢得多?
问题描述
TL;DR
查询同一个表时,为什么下面的第一个查询(扫描 20 行)比第二个查询(扫描 35k+ 行)花费的时间要长得多?
第一个查询:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Groups range <lots of keys> group_name_ip_address_idx 317 NULL 20 Using index condition; Using where
第二个查询:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Groups ref_or_null <lots of keys> email_address_idx 768 const 35415 Using index condition; Using where
我在解释中使用“行”作为查询性能的直接指标(这可能是错误的?),但 20 行查询仍然比 35k 查询花费更长的时间让我感到困惑。不是sql专家,有人可以教我什么可能导致这种情况吗?
更长的版本:
我有一个表“Groups”,其中有一个字段“group_name”和 20 个关于客户信息的字段(“field_1”、“field_2”、...、“field_20”)。
当我看到客户的信息时,我使用此表来确定它与哪个组匹配。举一个虚拟的例子,如果表有一个记录,其中组名是“美国男性”并且除了“公民身份”是“美国”和“性别”是“男性”之外,20 个字段都是空的,那么这意味着每当我看到具有公民身份“美国”和性别“男性”的客户,他匹配组“美国男性”。
我正在使用这个查询(Query1),它需要 3~5ms 来实现这个目标:
select * from Groups
where group_name = "US male"
and (field_1 = "something1" or field_1 is null)
and (field_2 = "something2" or field_2 is null)
and ... and (field_20 = "something20" or field_20 is null)
那些“东西”代表我想知道他/她匹配哪些组的当前客户的信息。因此,如果此查询返回任何内容,则表示匹配;否则不匹配。
解释上述查询的输出:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Groups ref_or_null <lots of keys> group_name_email_address_idx 962 const,const 2 Using index condition; Using where
请注意,一个客户可以匹配多个组,因此对于 N 个组名,我需要 N 个类似上面的查询。现在随着 N 越来越大,我想使用一个查询而不是 N 个小查询来做同样的事情,这就是我卡住的地方。
我首先尝试删除group_name = "XXXX"
条件where
- 选择所有匹配的组,而不是一一检查(Query2)。
select * from Groups
where (field_1 = "something1" or field_1 is null)
and (field_2 = "something2" or field_2 is null)
and ... and (field_20 = "something20" or field_20 is null)
解释输出:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Groups ref_or_null <lots of keys> email_address_idx 768 const 35415 Using index condition; Using where
这很慢(约 70 毫秒),因为它无法使用任何需要组名的索引,这是最有效的索引,因为 group_name 的基数最低。(扫描所需的行数为 35k,而第一个查询为 2)。所以这并没有很好地解决。
然后为了让查询使用我group_name in (<all group names>)
在where
(Query3)中添加的组名索引:
select * from Groups
where group_name in ("group1", "group2", ..., "groupN")
and (field_1 = "something1" or field_1 is null)
and (field_2 = "something2" or field_2 is null)
and ... and (field_20 = "something20" or field_20 is null)
解释输出:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Groups range <lots of keys> group_name_ip_address_idx 317 NULL 20 Using index condition; Using where
我看到它需要扫描的行数是 20,这比 35415 好得多,所以我期待它运行得很快。但是,当我尝试运行它时,它实际上比破坏我的服务的 Query2(大约 20 倍)要多得多。
毕竟,我现在很困惑,为什么扫描 20 行的查询比扫描 35k 行的查询花费更长的时间?我读错了解释输出吗?
解决方案
推荐阅读
- ios - 如何使用滚动视图单击列表部分内的项目以导航到详细信息页面 SwiftUI
- ios - 使用多个 AVPlayer 时如何检测哪个视频结束了?
- python - Python3 类方法输入;清洁溶液
- ms-access - 在 Ms Access 表单中从 RichTextBox 中的超链接文本打开另一个表单,而不是使用按钮单击
- julia - 在 Julia 中创建自定义类型
- delphi - 如何在 Indy SSL 中设置 ConnectTimeout/ReadTimeout
- ruby-on-rails - 如何在 Rails 方法中调用方法
- c++ - 从句子中删除给定字符的 C++ 程序不起作用
- javascript - 使用 React 调整 Div 大小
- excel - 如何动态填充自定义功能区编辑框