MySQL选择查询非常慢 [英] MySQL select query is terribly slow
问题描述
我有一张桌子,上面有2 196 998条记录:
I have a table with 2 196 998 records:
CREATE TABLE price (
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
marketId INT,
buy DOUBLE,
sell DOUBLE,
PRIMARY KEY (dt, marketId),
FOREIGN KEY fk_price_market(marketId) REFERENCES market(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;
查询
select max(buy) from price;
花费1.92秒,这是一个合理的时间,如果我在购买"列上创建索引,则需要0.00秒:
takes 1.92 sec that is a reasonable time and it takes 0.00 sec if I create an index on 'buy' column:
CREATE INDEX idx_price_buy ON price (buy);
和查询
select count(*) from price where marketId=309;
花费0.05秒并返回160570.
takes 0.05 sec and returns 160 570.
但是查询
select max(buy) from price where marketId=309;
花费15.49秒(这是非常大的),即使我同时创建了两个示例:
takes 15.49 sec (that is terribly huge) even if I create both idices:
CREATE INDEX idx_price_market ON price (marketId);
CREATE INDEX idx_price_buy ON price (buy);
(我不确定,但是索引idx_price_market
可能已经存在,因为在外键约束中需要marketId
列)
(I am not sure, but probably index idx_price_market
already exists because marketId
column is needed in a foreign key constraint)
1)有没有优化的方法?
1) Is there a way to optimize it?
2)如果否,那么其他数据库呢?他们表现更好吗?
2) If no, what about other databases? Do they perform better?
创建复合索引后
创建索引idx_price_market_buy开价(marketId,购买);
CREATE INDEX idx_price_market_buy ON price (marketId, buy);
查询需要0.00秒.
desc select max(buy) from price where marketId=309;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.01 sec)
推荐答案
select max(buy) from price where marketId=309;
在每列上创建单独的索引可能不允许MySQL优化查询.
Creating individual indexes on each column probably does not allow MySQL to optimize the query.
对于此查询,您需要(marketId, buy)
上的化合物索引.
For this query, you want a compound index on (marketId, buy)
.
create index idx_price_market_buy ON price (marketId, buy);
索引中列的顺序很重要:首先在marketId
上使用查询过滤器(因此您希望此列位于Coumpound索引中的第一位置),然后 计算最大buy
The ordering of columns in the index matters: first the query filters on marketId
(so you want this column in first position in the coumpound index), then it computes the maximum buy
.
这篇关于MySQL选择查询非常慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!