大型表上MySQL SELECT查询速度缓慢

Question

大型表上MySQL SELECT查询速度缓慢

6

我有一个表来存储大约35k个项目每15分钟2周内的价格变化情况。大致上，这就相当于在表中有大约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 |
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+

- Supremacy

我建议您运行OPTIMIZE命令，然后运行上面的SELECT查询以查看是否运行更快。如果是，则说明碎片整理导致它变慢。另外，您当前使用的索引可能不是针对此特定查询最优化的。尝试创建一个带有item_id、buyprice、sellprice和created_at的索引。 - Hyder B.

我不打算运行优化并锁定表以进行新的插入操作，而是将表克隆，然后尝试同样的查询。 - Supremacy

2个回答

2

你可以给表的列添加索引。

我曾经遇到过一个类似的问题，当数据量达到百万级别时，执行时间从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);

- Mathlight

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

这是您的查询：

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

这个查询的最佳索引是复合索引：price_archive(item_id, buy_price, sell_price, created_at)。这是一个“覆盖”索引，可以满足查询。然而，它也有一个缺点。这个索引可能会减慢您在表中进行的插入操作。每小时14万行数据是很多的，但维护这个索引应该不会那么糟糕。

您面临着数据库中常见的挑战。您的查询问题在于返回的1300行左右位于不同的数据页上。很可能，表在您的计算机上无法完全存入内存，因此需要访问磁盘文件约1300次，这就解释了为什么会出现几秒钟的延迟时间。

另一个解决方案是确保数据表本身适合存储在内存中。尽管第一个未缓存的查询需要一些时间，但随后的查询应该非常快速。