草庐IT

MySQL:为什么按 ID 排序比按其他列排序慢得多？

coder 2023-06-11 原文

我正在使用 MySQL 5.5.14 版从一个包含 500 万行的表中运行以下查询 QUERY 1:

SELECT P.ID, P.Type, P.Name, P.cty
     , X(P.latlng) as 'lat', Y(P.latlng) as 'lng'
     , P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN
     , P.lv, P.bd, P.bt, P.nb
     , P.ak * E.usD as 'usP' 
FROM PIG P 
  INNER JOIN EEL E 
    ON E.cur = P.cur 
WHERE act='1' 
  AND flA >= '1615' 
  AND ldA >= '0' 
  AND yr >= (YEAR(NOW()) - 100) 
  AND lv >= '0' 
  AND bd >= '3' 
  AND bt >= '2' 
  AND nb <= '5' 
  AND cDate >= NOW() 
  AND MBRContains(LineString( Point(39.9097, -2.1973)
                            , Point(65.5130, 41.7480)
                            ), latlng) 
  AND Type = 'g' 
  AND tn = 'l' 
  AND St + Tm - YEAR(NOW()) >= '30' 
HAVING usP BETWEEN 300/2 AND 300 
ORDER BY ak
LIMIT 100;

使用索引(类型、tn、act、flA)，我能够在800 毫秒 内获得结果。在QUERY 2中，我将ORDER BY子句改为lv，我也能在类似的时间里得到结果。在 QUERY 3 中，我将 ORDER BY 子句更改为 ID，查询时间显着减慢到整整 20 秒平均 10 次试验。

运行 EXPLAIN SELECT 语句产生完全相同的查询执行计划:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: P
         type: range
possible_keys: Index
          key: Index
      key_len: 6
          ref: NULL
         rows: 132478
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: BS.P.cur
         rows: 1
        Extra:

我的问题是:为什么 QUERY 3 中按 ID 排序的速度比其他排序慢？

部分表定义如下:

CREATE TABLE `PIG` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lv` smallint(3) unsigned NOT NULL DEFAULT '0',
  `ak` int(10) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`),
  KEY `id_ca` (`cty`,`ak`),
  KEY `Index` (`Type`, `tn`, `act`, `flA`),
) ENGINE=MyISAM AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1

CREATE TABLE `EEL` (
  `cur` char(3) NOT NULL,
  `usD` decimal(11,10) NOT NULL,
  PRIMARY KEY (`cur`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

更新:在对各种 ORDER BY 选项进行广泛测试后，我确认恰好是主键的 ID 列是唯一导致查询时间缓慢的列。

最佳答案

来自 http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html 处的 MySQL 文档

在某些情况下，MySQL 不能使用索引来解析ORDER BY，尽管它仍然使用索引来查找与WHERE 子句匹配的行。这些案例包括:

. . .

用于获取行的键与 ORDER BY 中使用的键不同:

`SELECT * FROM t1 WHERE key2=constant ORDER BY key1;`

这可能无济于事，但是如果您将 AND ID > 0 添加到 WHERE 子句中会怎样？会不会导致MySQL使用主键排序？我想值得一试。

(用 ak 排序是高效的似乎很奇怪，因为 ak 甚至没有索引，但这可能是因为 ak 的值较少？)

关于MySQL:为什么按 ID 排序比按其他列排序慢得多？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7580685/

MySQL按codestrong39performancequery-optimization

有关MySQL:为什么按 ID 排序比按其他列排序慢得多？的更多相关文章

  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 - 其他文件中的 Rake 任务 - 2

    我试图在一个项目中使用rake，如果我把所有东西都放到Rakefile中，它会很大并且很难读取/找到东西，所以我试着将每个命名空间放在lib/rake中它自己的文件中，我添加了这个到我的rake文件的顶部:Dir['#{File.dirname(__FILE__)}/lib/rake/*.rake'].map{|f|requiref}它加载文件没问题，但没有任务。我现在只有一个.rake文件作为测试，名为“servers.rake”，它看起来像这样:namespace:serverdotask:testdoputs"test"endend所以当我运行rakeserver:testid时

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

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

  4. ruby - 如何将脚本文件的末尾读取为数据文件(Perl 或任何其他语言) - 2

    我正在寻找执行以下操作的正确语法(在Perl、Shell或Ruby中):#variabletoaccessthedatalinesappendedasafileEND_OF_SCRIPT_MARKERrawdatastartshereanditcontinues. 最佳答案 Perl用__DATA__做这个:#!/usr/bin/perlusestrict;usewarnings;while(){print;}__DATA__Texttoprintgoeshere 关于ruby-如何将脚

  5. 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%

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

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

  8. ruby - ruby 中的 TOPLEVEL_BINDING 是什么？ - 2

    它不等于主线程的binding，这个toplevel作用域是什么？此作用域与主线程中的binding有何不同？>ruby-e'putsTOPLEVEL_BINDING===binding'false 最佳答案 事实是，TOPLEVEL_BINDING始终引用Binding的预定义全局实例，而Kernel#binding创建的新实例>Binding每次封装当前执行上下文。在顶层，它们都包含相同的绑定(bind)，但它们不是同一个对象，您无法使用==或===测试它们的绑定(bind)相等性。putsTOPLEVEL_BINDINGput

  9. ruby - Infinity 和 NaN 的类型是什么？ - 2

    我可以得到Infinity和NaNn=9.0/0#=>Infinityn.class#=>Floatm=0/0.0#=>NaNm.class#=>Float但是当我想直接访问Infinity或NaN时:Infinity#=>uninitializedconstantInfinity(NameError)NaN#=>uninitializedconstantNaN(NameError)什么是Infinity和NaN？它们是对象、关键字还是其他东西？ 最佳答案 您看到打印为Infinity和NaN的只是Float类的两个特殊实例的字符串

  10. ruby-on-rails - 如果 Object::try 被发送到一个 nil 对象，为什么它会起作用？ - 2

    如果您尝试在Ruby中的nil对象上调用方法，则会出现NoMethodError异常并显示消息:"undefinedmethod‘...’fornil:NilClass"然而，有一个tryRails中的方法，如果它被发送到一个nil对象，它只返回nil:require'rubygems'require'active_support/all'nil.try(:nonexisting_method)#noNoMethodErrorexceptionanymore那么try如何在内部工作以防止该异常？ 最佳答案 像Ruby中的所有其他对象

随机推荐