mysql - 在 DATETIME 列上使用 <= 与使用 < 时的 Big SQL SELECT 性能差异

Question

给出下表:

desc exchange_rates;
+------------------+----------------+------+-----+---------+----------------+
| id               | int(11)        | NO   | PRI | NULL    | auto_increment | 
| time             | datetime       | NO   | MUL | NULL    |                | 
| base_currency    | varchar(3)     | NO   | MUL | NULL    |                | 
| counter_currency | varchar(3)     | NO   | MUL | NULL    |                | 
| rate             | decimal(32,16) | NO   |     | NULL    |                | 
+------------------+----------------+------+-----+---------+----------------+

我在 time 上添加了索引, base_currency和 counter_currency ，以及关于 (time, base_currency, counter_currency) 的复合索引，但是当我执行 SELECT 时，我看到了很大的性能差异。使用 <=反对使用< .

第一个SELECT是:

ExchangeRate Load (95.5ms)   
SELECT * FROM `exchange_rates` WHERE (time <= '2009-12-30 14:42:02' and base_currency = 'GBP' and counter_currency = 'USD') LIMIT 1

如您所见，这需要 95 毫秒。

如果我更改查询以便使用 < 比较时间而不是 <=我看到了这个:

ExchangeRate Load (0.8ms)   
SELECT * FROM `exchange_rates` WHERE (time < '2009-12-30 14:42:02' and base_currency = 'GBP' and counter_currency = 'USD') LIMIT 1

现在只需不到 1 毫秒，这对我来说很合适。对这种行为有合理的解释吗？

EXPLAIN 的输出提供了进一步的细节，但我不是 100% 确定如何解释这个:

-- Output from the first, slow, select
SIMPLE      | 5,5     | exchange_rates | 1  | index_exchange_rates_on_time,index_exchange_rates_on_base_currency,index_exchange_rates_on_counter_currency,time_and_currency | index_merge | Using intersect(index_exchange_rates_on_counter_currency,index_exchange_rates_on_base_currency); Using where | 813  |     | index_exchange_rates_on_counter_currency,index_exchange_rates_on_base_currency

-- Output from the second, fast, select
SIMPLE      | 5       | exchange_rates | 1  | index_exchange_rates_on_time,index_exchange_rates_on_base_currency,index_exchange_rates_on_counter_currency,time_and_currency | ref  | Using where | 4988 | const | index_exchange_rates_on_counter_currency

(注意:我通过 ActiveRecord(在 Rails 应用程序中)生成这些查询，但这些最终是正在执行的查询)

Answer 1

在第一种情况下，MySQL 尝试合并来自所有索引的结果。它从两个索引中获取所有记录并将它们连接到行指针的值(MyISAM 中的表偏移量，InnoDB 中的PRIMARY KEY)。

在第二种情况下，它只使用一个索引，考虑到 LIMIT 1，这是最好的决定。

您需要在 (base_currency, counter_currency, time) 上创建一个复合索引(按此顺序)以使此查询尽快运行。

引擎将使用索引对前导列 (base_currency, counter_currency) 进行过滤，并对尾随列 (time) 进行排序。

您似乎还想在查询中添加类似 ORDER BY time DESC 的内容以获取最新汇率。

一般来说，任何没有ORDER BY的LIMIT都应该响铃。