草庐IT

mysql - 具有较大 LIMIT 范围的 ORDER BY 查询非常慢

coder 2023-10-18 原文

MySQL 5.6，64 位，RHEL 5.8
使用 ORDER BY 和 LIMIT 'row_count'(或 LIMIT 0,'row_count')对大表进行查询。如果“row_count”大于结果集的实际计数，将会非常非常慢。

情况 1:下面的查询非常快(没有“LIMIT”):

mysql> SELECT * FROM syslog WHERE 
        (ReportedTime BETWEEN '2013-11-04' AND '2013-11-05') AND
        Priority<3 AND Facility=1 ORDER BY id DESC;
+---
| ...
6 rows in set (0.01 sec)

情况 2:下面的查询也很快('LIMIT 5'):

mysql> SELECT * FROM syslog WHERE 
        (ReportedTime BETWEEN '2013-11-04' AND '2013-11-05') AND
        Priority<3 AND Facility=1 ORDER BY id DESC LIMIT 5;
+---
| ...
5 rows in set (0.42 sec)

情况 3:下面的查询非常非常慢('LIMIT 7'，可以使用任何大于 6 的 'row_count' 值):

mysql> SELECT * FROM syslog WHERE 
        (ReportedTime BETWEEN '2013-11-04' AND '2013-11-05') AND
        Priority<3 AND Facility=1 ORDER BY id DESC LIMIT 7;
+---
| ...
6 rows in set (28 min 7.24 sec)

区别只是个别(无限制)、“LIMIT 5”和“LIMIT 7”。
为什么情况 3 这么慢？
案例3的一些调查:

  1. 运行命令'SHOW PROCESS'，查询的状态保存在'Sending data'
    • 检查了服务器内存，它仍然足够可用。
    • 在运行查询之前将 SESSION 缓冲区“read_buffer_size”、“read_rnd_buffer_size”、“sort_buffer_size”扩展到非常大的数量(到 16MB)，但没有帮助。
    • 同样只查询“id”列(SELECT id FROM syslog ....)，但结果相同。
  2. 在查询运行期间，在另一个 mysql 连接中提出相同的查询但 row_count<5(例如“limit>
  3. 使用不同的条件，例如，扩展时间范围BETWEEN '2013-10-03' to '2013-11-05' 以获得结果行数149 .使用 LIMIT 140，速度很快。使用 LIMIT 150，速度非常非常慢。好奇怪。

目前在实践中，在我们的网站中，程序首先获取实际结果行数(SELECT COUNT(*) FROM ...，No ORDER BY，No LIMIT)，然后执行查询的 LIMIT 'row_count' 值不超过刚才得到的实际行数。丑。

案例 3 的解释:

-+-----..-+----..+-------+-----..+--------+---------+-----+-----+------------+
 | sele.. | table| type  | poss..| key    | key_len | ref | rows| Extra      |
-+-----..-+----..+-------+-----..+--------+---------+-----+-----+------------+
 | SIMPLE | syslo| index | ...   | PRIMARY| 8       | NULL| 132 | Using where|
-+-----..-+----..+-------+-----..+--------+---------+-----+-----+------------+
1 row in set (0.00 sec)

表定义:

CREATE TABLE syslog (
    id          BIGINT NOT NULL AUTO_INCREMENT,
    ReceivedAt  TIMESTAMP NOT NULL DEFAULT 0,
    ReportedTime TIMESTAMP NOT NULL DEFAULT 0,
    Priority    SMALLINT,
    Facility    SMALLINT,
    FromHost    VARCHAR(60),
    Message     TEXT,
    InfoUnitID  INT NOT NULL DEFAULT 0,
    SysLogTag   VARCHAR(60) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    KEY idx_ReportedTime_Priority_id (ReportedTime,Priority,id),
    KEY idx_Facility (Facility),
    KEY idx_SysLogTag (SysLogTag(16)),
    KEY idx_FromHost (FromHost(16))
);

最佳答案

Mysql 以围绕 ORDER BY DESC + LIMIT 子句的行为而闻名。

参见:http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

请尝试:

SELECT * 
FROM syslog FORCE INDEX (Facility)
WHERE 
    ReportedTime BETWEEN '2013-11-04' AND '2013-11-05' 
AND Priority<3 
AND Facility=1 
ORDER BY id DESC 
LIMIT 7;

您需要强制使用在第一次查询中使用的索引。 (从他们的解释计划中获取，KEY 列)

关于mysql - 具有较大 LIMIT 范围的 ORDER BY 查询非常慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19811628/

mysqlLIMITcodeReportedTimesqlperformanceoptimization

有关mysql - 具有较大 LIMIT 范围的 ORDER BY 查询非常慢的更多相关文章

  1. ruby - 具有身份验证的私有(private) Ruby Gem 服务器 - 2

    我想安装一个带有一些身份验证的私有(private)Rubygem服务器。我希望能够使用公共(public)Ubuntu服务器托管内部gem。我读到了http://docs.rubygems.org/read/chapter/18.但是那个没有身份验证-如我所见。然后我读到了https://github.com/cwninja/geminabox.但是当我使用基本身份验证(他们在他们的Wiki中有)时，它会提示从我的服务器获取源。所以。如何制作带有身份验证的私有(private)Rubygem服务器？这是不可能的吗？谢谢。编辑:Geminabox问题。我尝试“捆绑”以安装新的gem..

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

  3. ruby - 触发器 ruby​​ 中 3 点范围运算符和 2 点范围运算符的区别 - 2

    请帮助我理解范围运算符...和..之间的区别，作为Ruby中使用的“触发器”。这是PragmaticProgrammersguidetoRuby中的一个示例:a=(11..20).collect{|i|(i%4==0)..(i%3==0)?i:nil}返回:[nil,12,nil,nil,nil,16,17,18,nil,20]还有:a=(11..20).collect{|i|(i%4==0)...(i%3==0)?i:nil}返回:[nil,12,13,14,15,16,17,18,nil,20] 最佳答案 触发器(又名f/f)是

  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 - 相关表上的范围为 "WHERE ... LIKE" - 2

    我正在尝试从Postgresql表(table1)中获取数据，该表由另一个相关表(property)的字段(table2)过滤。在纯SQL中，我会这样编写查询:SELECT*FROMtable1JOINtable2USING(table2_id)WHEREtable2.propertyLIKE'query%'这工作正常:scope:my_scope,->(query){includes(:table2).where("table2.property":query)}但我真正需要的是使用LIKE运算符进行过滤，而不是严格相等。然而，这是行不通的:scope:my_scope,->(que

  6. ruby - 当使用::指定模块时，为什么 Ruby 不在更高范围内查找类？ - 2

    我刚刚被困在这个问题上一段时间了。以这个基地为例:moduleTopclassTestendmoduleFooendend稍后，我可以通过这样做在Foo中定义扩展Test的类:moduleTopmoduleFooclassSomeTest但是，如果我尝试通过使用::指定模块来最小化缩进:moduleTop::FooclassFailure这失败了:NameError:uninitializedconstantTop::Foo::Test这是一个错误，还是仅仅是Ruby解析变量名的方式的逻辑结果？ 最佳答案 Isthisabug,or

  7. Ruby 从大范围中获取第 n 个项目 - 2

    假设我有这个范围:("aaaaa".."zzzzz")如何在不事先/每次生成整个项目的情况下从范围中获取第N个项目？ 最佳答案 一种快速简便的方法:("aaaaa".."zzzzz").first(42).last#==>"aaabp"如果出于某种原因你不得不一遍又一遍地这样做，或者如果你需要避免为前N个元素构建中间数组，你可以这样写:moduleEnumerabledefskip(n)returnto_enum:skip,nunlessblock_given?each_with_indexdo|item,index|yieldit

  8. ruby-on-rails - Rails 3.1 中具有相同形式的多个模型？ - 2

    我正在使用Rails3.1并在一个论坛上工作。我有一个名为Topic的模型，每个模型都有许多Post。当用户创建新主题时，他们也应该创建第一个Post。但是，我不确定如何以相同的形式执行此操作。这是我的代码:classTopic:destroyaccepts_nested_attributes_for:postsvalidates_presence_of:titleendclassPost...但这似乎不起作用。有什么想法吗？谢谢! 最佳答案 @Pablo的回答似乎有你需要的一切。但更具体地说...首先改变你View中的这一行对此#

  9. 使用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

  10. ruby-on-rails - 事件记录 : Select max of limit - 2

    我正在尝试将以下SQL查询转换为ActiveRecord，它正在融化我的大脑。deletefromtablewhereid有什么想法吗？我想做的是限制表中的行数。所以，我想删除少于最近10个条目的所有内容。编辑:通过结合以下几个答案找到了解决方案。Temperature.where('id这给我留下了最新的10个条目。 最佳答案 从您的SQL来看，您似乎想要从表中删除前10条记录。我相信到目前为止的大多数答案都会如此。这里有两个额外的选择:基于MurifoX的版本:Table.where(:id=>Table.order(:id).

随机推荐