我正在运行一个过滤下来的聚合查询,并希望得到一些关于如何获得更好的查询响应时间的反馈.
查询(运行,但平均为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