我有一张桌子,上面有2 196 998条记录:
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秒:
CREATE INDEX idx_price_buy ON price (buy);
和查询
select count(*) from price where marketId=309;
花费0.05秒并返回160570。
但是查询
select max(buy) from price where marketId=309;
花费15.49秒(这是非常大的),即使我同时创建了两个示例:
CREATE INDEX idx_price_market ON price (marketId);
CREATE INDEX idx_price_buy ON price (buy);
(我不确定,但是索引idx_price_market
可能已经存在,因为在外键约束中需要marketId
列)
1)有没有优化的方法?
2)如果否,那么其他数据库呢?他们表现更好吗?
EDIT1:
创建复合索引后
创建索引idx_price_market_buy开价(marketId,购买);
查询需要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)