草庐IT

MySQL LIMIT x,y 在两台机器上的性能差异巨大

coder 2023-10-23 原文

我在 InnoDb item 表中有一个查询，其中包含 400k 条记录(仅...)。我需要对表示层的结果进行分页(每页 60 个)，因此我使用 LIMIT，其值取决于要显示的页面。

查询是(110000 偏移量只是一个例子):

SELECT i.id, sale_type, property_type, title, property_name, latitude,
       longitude,street_number, street_name, post_code,picture, url,
       score, dw_id, post_date
FROM item i WHERE picture IS NOT NULL AND picture != ''
AND sale_type = 0
ORDER BY score DESC  LIMIT 110000, 60;

在我的机器上运行这个查询大约需要 1 秒。 在我们的测试服务器上运行此查询需要 45-50 秒。

EXPLAIN 都是一样的:

+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
|  1 | SIMPLE      | i     | index | NULL          | IDX_SCORE | 5       | NULL | 110060 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+

查询 show variables 时唯一的配置差异是:

  • innodb_use_native_aio。它在测试服务器上启用，而不是在我的机器上启用。我尝试禁用它，但没有看到任何重大变化
  • innodb_buffer_pool_size 1G 在测试服务器上，2G 在我的机器上

测试服务器有 2Gb 内存，2 核 CPU:

  • mysqld 始终使用 > 65% 的 RAM，但在运行以上查询时仅增加 1-2%
  • mysqld 在运行上述查询时使用了 14% 的 CPU，空闲时没有

我的本​​地机器有 8Gb，8 核 CPU:

  • mysqld 始终使用 28% 的 RAM，并且在运行上述查询时并没有真正增加(或者在我能看到的这么短的时间内)
  • mysqld 在运行上述查询时使用了 48% 的 CPU，空闲时没有

要在测试服务器上获得相同的性能，我在哪里可以做什么？ RAM 和/或 CPU 是否太低？

更新

我设置了一个新的测试服务器，具有相同的规范，但 8G RAM 和 4 核 CPU，性能刚刚跃升至与我的机器相似的值。原来的服务器似乎没有使用所有的 RAM/CPU，为什么性能这么差？

最佳答案

破坏性能的最可靠方法之一是让 MySQL 扫描一个不适合内存的索引。因此在查询期间，它必须将索引的一部分加载到缓冲池中，然后逐出该部分并加载索引的另一部分。在查询期间像这样在缓冲池中造成搅动会导致大量 I/O 负载，这会使其非常慢。磁盘 I/O 比 RAM 慢大约 100,000 倍。

因此，1GB 缓冲池和 2GB 缓冲池之间存在很大差异，如果您的索引是 1.5GB。

另一个提示:您真的不想使用 LIMIT 110000, 60。这导致 MySQL 从缓冲池中读取 110000 行(必要时可能从磁盘加载它们)只是为了丢弃它们。还有其他方法可以更有效地对结果集进行分页。

参见 Optimized Pagination using MySQL 等文章.

关于MySQL LIMIT x,y 在两台机器上的性能差异巨大，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16312119/

MySQLLIMITcodesection的performance

有关MySQL LIMIT x,y 在两台机器上的性能差异巨大的更多相关文章

  1. ruby - 将差异补丁应用于字符串/文件 - 2

    对于具有离线功能的智能手机应用程序，我正在为Xml文件创建单向文本同步。我希望我的服务器将增量/差异(例如GNU差异补丁)发送到目标设备。这是计划:Time=0Server:hasversion_1ofXmlfile(~800kiB)Client:hasversion_1ofXmlfile(~800kiB)Time=1Server:hasversion_1andversion_2ofXmlfile(each~800kiB)computesdeltaoftheseversions(=patch)(~10kiB)sendspatchtoClient(~10kiBtransferred)Cl

  2. ruby-on-rails - date_field_tag，如何设置默认日期？ [ rails 上的 ruby ] - 2

    我想设置一个默认日期，例如实际日期，我该如何设置？还有如何在组合框中设置默认值顺便问一下，date_field_tag和date_field之间有什么区别？ 最佳答案 试试这个:将默认日期作为第二个参数传递。youcorrectlysetthedefaultvalueofcomboboxasshowninyourquestion. 关于ruby-on-rails-date_field_tag，如何设置默认日期？[rails上的ruby]，我们在StackOverflow上找到一个类似的问

  3. ruby-on-rails - openshift 上的 rails 控制台 - 2

    我将我的Rails应用程序部署到OpenShift，它运行良好，但我无法在生产服务器上运行“Rails控制台”。它给了我这个错误。我该如何解决这个问题？我尝试更新ruby​​gems，但它也给出了权限被拒绝的错误，我也无法做到。railsc错误:Warning:You'reusingRubygems1.8.24withSpring.UpgradetoatleastRubygems2.1.0andrun`gempristine--all`forbetterstartupperformance./opt/rh/ruby193/root/usr/share/rubygems/rubygems

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

  5. ruby - 在 Windows 机器上使用 Ruby 进行开发是否会适得其反？ - 2

    这似乎非常适得其反，因为太多的gem会在window上破裂。我一直在处理很多mysql和ruby​​-mysqlgem问题(gem本身发生段错误，一个名为UnixSocket的类显然在Windows机器上不能正常工作，等等)。我只是在浪费时间吗？我应该转向不同的脚本语言吗？ 最佳答案 我在Windows上使用Ruby的经验很少，但是当我开始使用Ruby时，我是在Windows上，我的总体印象是它不是Windows原生系统。因此，在主要使用Windows多年之后，开始使用Ruby促使我切换回原来的系统Unix，这次是Linux。Rub

  6. ruby-on-rails - Ruby - 如何从 ruby​​ 上的 .pfx 文件中提取公钥、rsa 私钥和 CA key - 2

    我有一个.pfx格式的证书，我需要使用ruby​​提取公共(public)、私有(private)和CA证书。使用shell我可以这样做:#ExtractPublicKey(askforpassword)opensslpkcs12-infile.pfx-outfile_public.pem-clcerts-nokeys#ExtractCertificateAuthorityKey(askforpassword)opensslpkcs12-infile.pfx-outfile_ca.pem-cacerts-nokeys#ExtractPrivateKey(askforpassword)o

  7. ruby - 我的 Ruby IRC 机器人没有连接到 IRC 服务器。我究竟做错了什么？ - 2

    require"socket"server="irc.rizon.net"port="6667"nick="RubyIRCBot"channel="#0x40"s=TCPSocket.open(server,port)s.print("USERTesting",0)s.print("NICK#{nick}",0)s.print("JOIN#{channel}",0)这个IRC机器人没有连接到IRC服务器，我做错了什么？ 最佳答案 失败并显示此消息::irc.shakeababy.net461*USER:Notenoughparame

  8. 带有 attr_accessor 的类上的 Ruby instance_eval - 2

    我了解instance_eval和class_eval之间的基本区别。我在玩弄时发现的是一些涉及attr_accessor的奇怪东西。这是一个例子:A=Class.newA.class_eval{attr_accessor:x}a=A.newa.x="x"a.x=>"x"#...expectedA.instance_eval{attr_accessor:y}A.y="y"=>NoMethodError:undefinedmethod`y='forA:Classa.y="y"=>"y"#WHATTT?这是怎么回事:instance_eval没有访问我们的A类(对象)然后它实际上将它添加到

  9. ruby-on-rails - rails 上的 ruby : radio buttons for collection select - 2

    我有一个集合选择:此方法的单选按钮是什么？谢谢 最佳答案 Rails3中没有这样的助手。在Rails4中，它是collection_radio_buttons. 关于ruby-on-rails-rails上的ruby:radiobuttonsforcollectionselect，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/18525986/

  10. Ruby 的数字方法性能 - 2

    我正在使用Ruby解决一些ProjectEuler问题，特别是这里我要讨论的问题25(Fibonacci数列中包含1000位数字的第一项的索引是多少？)。起初，我使用的是Ruby2.2.3，我将问题编码为:number=3a=1b=2whileb.to_s.length但后来我发现2.4.2版本有一个名为digits的方法，这正是我需要的。我转换为代码:whileb.digits.length当我比较这两种方法时，digits慢得多。时间./025/problem025.rb0.13s用户0.02s系统80%cpu0.190总计./025/problem025.rb2.19s用户0.0

随机推荐