草庐IT

MySQL 对两个不同索引的查询速度很快，但组合成一个查询速度很慢。为什么？

coder 2023-10-05 原文

我有一个包含 200 万行的表。我有两个索引(状态，性别)和(生日)。

我发现这个查询花费了 3.6 秒或更长时间很奇怪 问题一

SELECT COUNT(*) FROM ts_user_core
WHERE birthday BETWEEN '1980-01-01' AND '1985-01-01'
    AND status='ok' AND gender='female';

同样的: 问题 2

SELECT COUNT(*) FROM ts_user_core
WHERE status='ok' AND gender='female'
    AND birthday between '1980-01-01' AND '1985-01-01';

虽然此查询耗时 0.140 秒 问题 3

select count(*) from ts_user_core where (birthday between '1990-01-01' and '2000-01-01');

此查询也需要 0.2 秒 问题 4

select count(*) from ts_user_core where status='ok' and gender='female'

我希望第一个查询更快，这种行为怎么可能？我没时间处理这个查询。

结果如下:

我知道我可以添加一个包含 3 列的新索引，但是有没有一种方法可以在不为每个 where 子句添加索引的情况下进行更快的查询？

谢谢你的建议

最佳答案

is there a way to optimize the query without adding an index for every possible where clause?

是的，有点。但这需要了解索引的工作原理。

让我们看看您目前已呈现的所有SELECT。

  1. 要为 SELECT 构建最佳索引，请从 WHERE 子句中的所有 = constant 项开始。将这些列以任意顺序放入索引中。这给了我们 INDEX(status, gender, ...) 或 INDEX(gender, status, ...)，但(还)没有决定它们之间的关系。
  2. 添加一个 范围或所有ORDER BY。在您的前几个 SELECTs 中，那将是 birthday。现在我们有了 INDEX(status, gender, birthday) 或 INDEX(gender, status, birthday)。对于前两个 SELECTs，其中任何一个都是“最佳”。

对于 #4，这些索引工作得很好:select count(*) from ts_user_core where status='ok' and gender='female'。所以它不需要额外的索引。

现在，让我们处理 #3:select count(*) from ts_user_core where (birthday between '1990-01-01' and '2000-01-01');

  • 它不能使用我们目前已有的索引。
  • INDEX(birthday) 本质上是唯一的选择。

现在，假设我们还有 ... WHERE status='foo';(没有 gender)。这将迫使我们选择 INDEX(status, gender, birthday) 而不是它的变体。

结果:2 个好的索引可以处理所有 5 个选择:

INDEX(status, gender, birthday)
INDEX(birthday)

建议:如果您最终得到超过 5 个 INDEXes 或包含超过 5 列的索引，缩短一些索引可能是明智的。这是事情变得非常模糊的地方。如果您想向我展示一打“现实”指标，我将带您完成。

其他评论的注释:

  • 对于时间安排，每个查询运行两次并进行第二次 - 以避免缓存效应。 (您的 3.6 与 0.140 闻起来像是索引缓存。)
  • 对于计时，关闭查询缓存或使用 SQL_NO_CACHE。
  • 优化器很少在单个查询中使用两个索引。
  • 向我们展示EXPLAIN plain；我们可以帮助您阅读。
  • 在多个 INDEX 中进行选择所花费的额外时间通常是值得的。
  • 如果您有INDEX(a,b,c)，则不需要INDEX(a,b)。

关于MySQL 对两个不同索引的查询速度很快，但组合成一个查询速度很慢。为什么？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/29921437/

MySQL查询codebirthdaygenderperformance

有关MySQL 对两个不同索引的查询速度很快，但组合成一个查询速度很慢。为什么？的更多相关文章

  1. ruby - 为什么我可以在 Ruby 中使用 Object#send 访问私有(private)/ protected 方法？ - 2

    类classAprivatedeffooputs:fooendpublicdefbarputs:barendprivatedefzimputs:zimendprotecteddefdibputs:dibendendA的实例a=A.new测试a.foorescueputs:faila.barrescueputs:faila.zimrescueputs:faila.dibrescueputs:faila.gazrescueputs:fail测试输出failbarfailfailfail.发送测试[:foo,:bar,:zim,:dib,:gaz].each{|m|a.send(m)resc

  2. ruby-on-rails - Rails - 子类化模型的设计模式是什么？ - 2

    我有一个模型:classItem项目有一个属性“商店”基于存储的值，我希望Item对象对特定方法具有不同的行为。Rails中是否有针对此的通用设计模式？如果方法中没有大的if-else语句，这是如何干净利落地完成的？ 最佳答案 通常通过Single-TableInheritance. 关于ruby-on-rails-Rails-子类化模型的设计模式是什么？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.co

  3. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  4. ruby - 什么是填充的 Base64 编码字符串以及如何在 ruby​​ 中生成它们？ - 2

    我正在使用的第三方API的文档状态:"[O]urAPIonlyacceptspaddedBase64encodedstrings."什么是“填充的Base64编码字符串”以及如何在Ruby中生成它们。下面的代码是我第一次尝试创建转换为Base64的JSON格式数据。xa=Base64.encode64(a.to_json) 最佳答案 他们说的padding其实就是Base64本身的一部分。它是末尾的“=”和“==”。Base64将3个字节的数据包编码为4个编码字符。所以如果你的输入数据有长度n和n%3=1=>"=="末尾用于填充n%

  5. ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2

    我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i

  6. ruby - 使用 Vim Rails，您可以创建一个新的迁移文件并一次性打开它吗？ - 2

    使用带有Rails插件的vim，您可以创建一个迁移文件，然后一次性打开该文件吗？textmate也可以这样吗？ 最佳答案 你可以使用rails.vim然后做类似的事情::Rgeneratemigratonadd_foo_to_bar插件将打开迁移生成的文件，这正是您想要的。我不能代表textmate。 关于ruby-使用VimRails，您可以创建一个新的迁移文件并一次性打开它吗？，我们在StackOverflow上找到一个类似的问题： https://sta

  7. ruby-on-rails - Rails - 一个 View 中的多个模型 - 2

    我需要从一个View访问多个模型。以前，我的links_controller仅用于提供以不同方式排序的链接资源。现在我想包括一个部分(我假设)显示按分数排序的顶级用户(@users=User.all.sort_by(&:score))我知道我可以将此代码插入每个链接操作并从View访问它，但这似乎不是“ruby方式”，我将需要在不久的将来访问更多模型。这可能会变得很脏，是否有针对这种情况的任何技术？注意事项:我认为我的应用程序正朝着单一格式和动态页面内容的方向发展，本质上是一个典型的网络应用程序。我知道before_filter但考虑到我希望应用程序进入的方向，这似乎很麻烦。最终从任何

  8. ruby-on-rails - 渲染另一个 Controller 的 View - 2

    我想要做的是有2个不同的Controller，client和test_client。客户端Controller已经构建，我想创建一个test_clientController，我可以使用它来玩弄客户端的UI并根据需要进行调整。我主要是想绕过我在客户端中内置的验证及其对加载数据的管理Controller的依赖。所以我希望test_clientController加载示例数据集，然后呈现客户端Controller的索引View，以便我可以调整客户端UI。就是这样。我在test_clients索引方法中试过这个:classTestClientdefindexrender:template=>

  9. ruby - 为什么 4.1%2 使用 Ruby 返回 0.0999999999999996？但是 4.2%2==0.2 - 2

    为什么4.1%2返回0.0999999999999996？但是4.2%2==0.2。 最佳答案 参见此处:WhatEveryProgrammerShouldKnowAboutFloating-PointArithmetic实数是无限的。计算机使用的位数有限(今天是32位、64位)。因此计算机进行的浮点运算不能代表所有的实数。0.1是这些数字之一。请注意，这不是与Ruby相关的问题，而是与所有编程语言相关的问题，因为它来自计算机表示实数的方式。 关于ruby-为什么4.1%2使用Ruby返

  10. ruby-on-rails - 如何在 ruby​​ 中使用两个参数异步运行 exe？ - 2

    exe应该在我打开页面时运行。异步进程需要运行。有什么方法可以在ruby​​中使用两个参数异步运行exe吗？我已经尝试过ruby​​命令-system()、exec()但它正在等待过程完成。我需要用参数启动exe，无需等待进程完成是否有任何ruby​​gems会支持我的问题？ 最佳答案 您可以使用Process.spawn和Process.wait2:pid=Process.spawn'your.exe','--option'#Later...pid,status=Process.wait2pid您的程序将作为解释器的子进程执行。除

随机推荐