mysql 查询增强,解释MySQL解释和建议以增强查询响应时间

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

查询(运行,但平均为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中设计子查询的方式可能会强制引擎不使用“符号”上的索引 – 索引可能会失去性能. EXPLAIN输出使得看起来像这种损失就是这种情况.

(3)说明(2)而不谈论索引卷的另一种方式是引擎可能与主子查询(WHERE中的子查询)的工作效率低下,这是基于错误地推断出与外部查询的关系(即,它认为存在一种关系 – 您的查询是一个相关的子查询 – 并且它对该关系做出了错误的选择).

[注意：编写WHERE的方式,子查询不是相关查询,可以有效执行,并且可以相对有效地解析IN(尽管可能没有索引的好处);但是,引擎可能会很难解释这种情况 – 你确实有一个有点复杂的嵌套子查询情况,可能会让引擎弄错了].

在任何情况下,将子查询移动到连接都可以修复这种情况,因为它消除了引擎尝试将子查询与查询的其余部分无效关联的任何可能性.当子查询是连接的源时,引擎必须先解析它,然后才能考虑查询的其余部分.这消除了关于子查询与引擎可能正在进行的查询的其余部分之间的关​​系的任何不良推论.

标签：mysql,sql,database-performance

来源： https://codeday.me/bug/20190613/1230742.html