草庐IT

MySQL (id >= N AND col2 IS NULL) 查询对于大 N 意外地慢

coder 2023-10-03 原文

我们使用的是 MySQL 5.5.42。

我们有一个表publications，其中包含大约 1.5 亿行(在 SSD 上大约 140 GB)。

该表有很多列，其中有两列特别重要:

  • id是表的主键，类型是bigint
  • cluster_id 是 bigint
    • 类型的可为空的列

两列都有自己的(单独的)索引。

我们对表单进行查询

SELECT * FROM publications
WHERE id >= 14032924480302800156 AND cluster_id IS NULL
ORDER BY id
LIMIT 0, 200;

Here is the problem: The larger the id value (14032924480302800156 in the example above), the slower the request.

换句话说，对于低 id 值的请求速度很快(< 0.1="" 秒)，但是="">id 值越高，请求越慢(最多几分钟)。

如果我们在 WHERE 子句中使用另一个(索引)列，一切都很好。例如

SELECT * FROM publications
WHERE inserted_at >= '2014-06-20 19:30:25' AND cluster_id IS NULL
ORDER BY inserted_at
LIMIT 0, 200;

其中 inserted_at 是 timestamp 类型。

编辑:

使用 id >= 14032924480302800156 时 EXPLAIN 的输出:

id | select_type | table        | type | possible_keys      | key        | key_len | ref   | rows     | Extra
---+-------------+--------------+------+--------------------+------------+---------+-------+----------+------------
1  | SIMPLE      | publications | ref  | PRIMARY,cluster_id | cluster_id | 9       | const | 71647796 | Using where

使用 inserted_at >= '2014-06-20 19:30:25' 时 EXPLAIN 的输出:

id | select_type | table        | type | possible_keys          | key        | key_len | ref   | rows     | Extra
---+-------------+--------------+------+------------------------+------------+---------+-------+----------+------------
1  | SIMPLE      | publications | ref  | inserted_at,cluster_id | cluster_id | 9       | const | 71647796 | Using where

最佳答案

关于 MySQL 以错误的顺序使用索引涉及一些猜测。 PRIMARY索引的处理方式似乎与其他索引完全不同。

在一个带有主键条件索引的查询中PRIMARY和 cluster_id可以使用。由于某种原因，MySQL 忽略了 PRIMARY index 并查看 cluster_id 上的索引首先，你有一个条件:它应该是 NULL .这给我们留下了一个巨大的潜在无序(NULL 无处不在!)要被 id 过滤的行集。 .

然而，对于下一个查询，它是不同的:PRIMARY根本不能使用索引，因此 MySQL 以更好的方式计算使用什么，显然是在 inserted_at 上使用索引首先没有任何提示。

在第一个查询中它实际上应该做的是取 PRIMARY首先索引 ( tell it to do so )。我不是 MySQL 用户，我所有的猜测都是基于我自己对内部数据结构的理解。不知道能不能在cluster_id上加索引在结果之上，但创建一个复合索引并比较使用和不使用它的性能可能会提供是否使用它的线索。

关于MySQL (id >= N AND col2 IS NULL) 查询对于大 N 意外地慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31450503/

外地MySQLcodecluster_idclusterperformance

有关MySQL (id >= N AND col2 IS NULL) 查询对于大 N 意外地慢的更多相关文章

  1. 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.

  2. ruby-on-rails - 如何从 format.xml 中删除 <hash></hash> - 2

    我有一个对象has_many应呈现为xml的子对象。这不是问题。我的问题是我创建了一个Hash包含此数据，就像解析器需要它一样。但是rails自动将整个文件包含在.........我需要摆脱type="array"和我该如何处理？我没有在文档中找到任何内容。 最佳答案 我遇到了同样的问题；这是我的XML:我在用这个:entries.to_xml将散列数据转换为XML，但这会将条目的数据包装到中所以我修改了:entries.to_xml(root:"Contacts")但这仍然将转换后的XML包装在“联系人”中，将我的XML代码修改为

  3. ruby-on-rails - rspec should have_select ('cars' , :options => ['volvo' , 'saab' ] 不工作 - 2

    关闭。这个问题需要detailsorclarity.它目前不接受答案。想改进这个问题吗？通过editingthispost添加细节并澄清问题.关闭8年前。Improvethisquestion在首页我有:汽车:VolvoSaabMercedesAudistatic_pages_spec.rb中的测试代码:it"shouldhavetherightselect"dovisithome_pathit{shouldhave_select('cars',:options=>['volvo','saab','mercedes','audi'])}end响应是rspec./spec/request

  4. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  5. ruby-on-rails - Nokogiri:使用 XPath 搜索 <div> - 2

    我使用Nokogiri(Rubygem)css搜索寻找某些在我的html里面。看起来Nokogiri的css搜索不喜欢正则表达式。我想切换到Nokogiri的xpath搜索，因为这似乎支持搜索字符串中的正则表达式。如何在xpath搜索中实现下面提到的(伪)css搜索？require'rubygems'require'nokogiri'value=Nokogiri::HTML.parse(ABBlaCD3"HTML_END#my_blockisgivenmy_bl="1"#my_eqcorrespondstothisregexmy_eq="\/[0-9]+\/"#FIXMEThefoll

  6. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  7. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  8. ruby-on-rails - 找不到 gem railties (>= 0.a) (Gem::GemNotFoundException) - 2

    我已经看到了一些其他的问题，尝试了他们的建议，但没有一个对我有用。我已经使用Rails大约一年了，刚刚开始一个新的Rails项目，突然遇到了问题。我卸载并尝试重新安装所有Ruby和Rails。Ruby很好，但Rails不行。当我输入railss时，我得到了can'tfindgemrailties。我当前的Ruby版本是ruby2.2.2p95(2015-04-13修订版50295)[x86_64-darwin15]，尽管我一直在尝试通过rbenv设置ruby​​2.3.0。如果我尝试rails-v查看我正在运行的版本，我会得到同样的错误。我使用的是MacOSXElCapitan版本10

  9. ruby - Rails -- :id attribute? 所需的数据库索引 - 2

    因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration

  10. ruby-on-rails - 连接字符串时如何在 <%=%> block 内输出 html_safe？ - 2

    考虑一下:现在这些情况:#output:http://domain.com/?foo=1&bar=2#output:http://domain.com/?foo=1&bar=2#output:http://domain.com/?foo=1&bar=2#output:http://domain.com/?foo=1&bar=2我需要用其他字符串输出URL。我如何保证＆符号不会被转义？由于我无法控制的原因，我无法发送&。求助!把我的头发拉到这里:\编辑:为了澄清，我实际上有一个像这样的数组:@images=[{:id=>"fooid",:url=>"http://

随机推荐