mysql - MySQL select查询运行比较慢

Question

从我以前的帖子中，我发现如果我在选择查询中引用多个列，我需要一个复合索引，所以对于我的表

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;

我创建了复合索引：

CREATE INDEX idx_price_market_buy ON price (marketId, buy, sell, dt);

现在查询

select max(dt) from price where marketId=309 and buy>0.3;

在 0.02 秒内执行得足够快，但是具有相同列组合的类似查询

select max(buy) from price where marketId=309 and dt>'2019-10-29 15:00:00';

需要 0.18 秒，相对较慢。

这些查询的描述看起来有点不同：

mysql> desc select max(dt) from price where marketId=309 and buy>0.3;
+----+-------------+-------+------------+-------+-----------------------------------------------------+----------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys                                       | key                  | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------------------------------------------+----------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | price | NULL       | range | idx_price_market,idx_price_buy,idx_price_market_buy | idx_price_market_buy | 13      | NULL | 50442 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------------------------------------------+----------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select max(buy) from price where marketId=309 and dt>'2019-10-29 15:00:00';
+----+-------------+-------+------------+------+-----------------------------------------------+----------------------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys                                 | key                  | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+-----------------------------------------------+----------------------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | price | NULL       | ref  | PRIMARY,idx_price_market,idx_price_market_buy | idx_price_market_buy | 4       | const | 202176 |    50.00 | Using where; Using index |
+----+-------------+-------+------------+------+-----------------------------------------------+----------------------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

例如， key_len 不同。这是什么意思？

buy主要问题：和dt列之间有什么区别？为什么在查询中切换它们会影响性能？

标签： mysql