大型表上的慢速MySQL选择

Question

我有一张桌子，用来在两周内每15分钟储存35k件物品的价格。大致相当于表中的3500万行。我正在尝试执行最简单的查询：

SELECT buy_price, sell_price, created_at FROM price_archive WHERE item_id = X

该查询的第一个非缓存运行大约需要4-7秒才能返回~1300行(每项)。对于数据库来说，这似乎太慢了，特别是考虑到item_id列上有索引。

该表每15分钟插入35k行，每天都有任务运行，从< (2周- 1)之前删除项(以防止表增长过多)。我怀疑这在很大程度上是对表的碎片，但是这种碎片会导致查询执行得如此糟糕吗？如果是，在created_at上进行分区以删除旧数据是否会使其更好？

1306 rows in set (8.32 sec)

mysql> explain select * from price_archives where item_id = 743;
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys                   | key                             | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | price_archives | ref  | index_price_archives_on_item_id | index_price_archives_on_item_id | 5       | const | 1305 | Using where |
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+

Answer 1

这是您的查询：

SELECT buy_price, sell_price, created_at
FROM price_archive
WHERE item_id = X;

此查询的最佳索引是复合索引：price_archive(item_id, buy_price, sell_price, created_at)。这是一个可以满足查询的“覆盖”索引。不过，这也有其不利之处。此索引可能会减慢您正在表中执行的插入操作。每小时140 k行是大量的数据，但是维护这个索引不应该那么糟糕。

在数据库中，您正面临一个并非罕见的挑战。查询的问题是，返回的大约1300行都位于不同的数据页上。很可能，该表不适合您的计算机上的内存，因此这将导致大约1300次对磁盘上的文件的访问。这就解释了为什么你看到的是几秒钟的滞后时间。

另一种解决方案是确保数据表本身适合内存。虽然第一个未缓存的查询需要一些时间，但是后续的查询应该相当快。

Answer 2

可以向表列添加索引。

有了这个问题的million+记录，时间从50秒到10秒。

更新表的SQL查询：

ALTER TABLE price_archives ADD INDEX (item_id);
ALTER TABLE price_archives ADD INDEX (buy_price);
ALTER TABLE price_archives ADD INDEX (sell_price);
ALTER TABLE price_archives ADD INDEX (created_at);