草庐IT

mysql - 一篇MySQL Explain解读及提升查询响应时间的建议

coder 2023-10-09 原文

我正在运行一个向下聚合查询并希望得到一些关于如何获得更好的查询响应时间的反馈。

查询(运行，但平均 400 秒以上):

select data_date,sum(closeprice) from moving_avgs
where
    symbol in (select distinct symbol from moving_avgs
                where
                ma200_close     >= 5.00 and
                ma200_volume    >= 400000 and
                data_date   = (select min(data_date) from moving_avgs
                                where year(data_date) = 2007) 
                )
group by data_date;

我的 EXPLAIN 查询读取(格式化为在此环境中读取):

id:         1
select_type:    PRIMARY
table:      moving_avgs
type:       ALL
possible_keys:  NULL
key:        NULL
key_len:        NULL
ref:        NULL
rows:       6250033
Extra:      Using where; Using temporary; Using filesort

id:         2
select_type:    DEPENDENT SUBQUERY
table:      moving_avgs
type:       unique_subquery
possible_keys:  PRIMARY,symbol,data_date,ma200_close,ma200_volume
key:        PRIMARY
key_len:        29
ref:        func,const
rows:       1
Extra:      Using where

id:         3
select_type:    SUBQUERY
table:      moving_avgs
type:       index
possible_keys:  NULL
key:        data_date
key_len:        3
ref:        NULL
rows:       6250033
Extra:      Using where; Using index

我的 my.ini [mysqld] 和 [myisamchk] 部分读取(在 4GB 双处理器 AMD 笔记本电脑上运行):

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 20M
table_open_cache = 256
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_cache_size= 132M
basedir=c:/wamp/bin/mysql/mysql5.5.24
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.5.24/data
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

谢谢!

最佳答案

你能列出 SHOW CREATE TABLE 的结果吗？

您也可以试试这个变体，看看需要多长时间:

SELECT  
    data_date,  
    sum(closeprice)  
FROM moving_avgs  
INNER JOIN  
(  
    SELECT distinct symbol  
    FROM moving_avgs  
    WHERE    
        ma200_close     >= 5.00 and    
        ma200_volume    >= 400000 and  
        data_date   =  
        (  
            SELECT min(data_date)  
            FROM moving_avgs  
            WHERE year(data_date) = 2007  
        )   
) symbols ON symbols.symbol = moving_avgs.symbol  
GROUP BY data_date;

我怀疑缓慢的三个来源(组合或单独)。前两个背后的原因非常简单:

(1) 你的表上的索引可能没有设计的那么好。我在您的 EXPLAIN 信息中没有看到良好的索引使用情况。

(2) 在 WHERE 中设计子查询的方式可能会强制引擎不使用您在 'symbol' 上的索引——否则索引可能会损失性能。 EXPLAIN 输出使这种损失看起来就是这种情况。

(3) 在不讨论索引滚动的情况下陈述 (2) 的另一种方式是，引擎可能基于错误地推断与外部查询的关系而低效地处理主子查询(WHERE 中的子查询)(即，它认为存在一种关系——您的查询是一个相关的子查询——并且它对该关系做出了错误的选择。

[注意:您的 WHERE 的编写方式，子查询不是相关查询，它可以有效地执行，并且 IN 可以相对有效地解析(尽管可能没有索引的好处)；但是，引擎可能无法很好地解释这种情况——您确实遇到了某种复杂的嵌套子查询情况，这可能会使引擎出错]。

在任何情况下，将子查询移动到连接都可以解决这种情况，因为它消除了引擎试图低效地将子查询与查询的其余部分相关联的任何可能性。当子查询是连接的来源时，引擎必须先解析它，然后才能考虑查询主体的其余部分。这消除了引擎可能做出的关于子查询和其余查询之间关系的任何错误推论。

关于mysql - 一篇MySQL Explain解读及提升查询响应时间的建议，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13999144/

Explainmysqldata_datemoving_avgsdatasqldatabase-performance

有关mysql - 一篇MySQL Explain解读及提升查询响应时间的建议的更多相关文章

  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 - 在 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

  3. ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2

    我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby​​是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。 最佳答案 我不太明白你的问题。我假设您想检查

  4. ruby-on-rails - 每次我尝试部署时，我都会得到 - (gcloud.preview.app.deploy) 错误响应 : [4] DEADLINE_EXCEEDED - 2

    我是Google云的新手，我正在尝试对其进行首次部署。我的第一个部署是RubyonRails项目。我基本上是在关注thisguideinthegoogleclouddocumentation.唯一的区别是我使用的是我自己的项目，而不是他们提供的“helloworld”项目。这是我的app.yaml文件runtime:customvm:trueentrypoint:bundleexecrackup-p8080-Eproductionconfig.ruresources:cpu:0.5memory_gb:1.3disk_size_gb:10当我转到我的项目目录并运行gcloudprevie

  5. ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2

    这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build

  6. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

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

  8. HBase Region 简介和建议数量&大小 - 2

    Region是HBase数据管理的基本单位,region有一点像关系型数据的分区。region中存储这用户的真实数据，而为了管理这些数据，HBase使用了RegionSever来管理region。Region的结构hbaseregion的大小设置默认情况下，每个Table起初只有一个Region，随着数据的不断写入，Region会自动进行拆分。刚拆分时，两个子Region都位于当前的RegionServer，但处于负载均衡的考虑，HMaster有可能会将某个Region转移给其他的RegionServer。RegionSplit时机：当1个region中的某个Store下所有StoreFile

  9. 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中提取小时

  10. ruby-on-rails - 在 Ruby on Rails 中发送响应之前如何等待多个异步操作完成？ - 2

    在我做的一些网络开发中，我有多个操作开始，比如对外部API的GET请求，我希望它们同时开始，因为一个不依赖另一个的结果。我希望事情能够在后台运行。我找到了concurrent-rubylibrary这似乎运作良好。通过将其混合到您创建的类中，该类的方法具有在后台线程上运行的异步版本。这导致我编写如下代码，其中FirstAsyncWorker和SecondAsyncWorker是我编写的类，我在其中混合了Concurrent::Async模块，并编写了一个名为“work”的方法来发送HTTP请求:defindexop1_result=FirstAsyncWorker.new.async.

随机推荐