MySQL在拥有600万行数据表的性能表现

Question

有一天我怀疑我需要学习hadoop并将所有这些数据转移到非结构化数据库中，但令我惊讶的是，在如此短的时间内性能下降了这么多。

我有一个mysql表，只有不到600万行。 我正在对此表进行非常简单的查询，并且认为我已经放置了所有正确的索引。

查询语句如下：

SELECT date, time FROM events WHERE venid ='47975' AND date>='2009-07-11' ORDER BY date

解释结果如下：

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  updateshows     range   date_idx    date_idx    7   NULL    648997  Using where

据我所知，我正在使用正确的索引，但是这个查询需要运行11秒钟。

数据库是MyISAM，并且phpMyAdmin显示该表大小为1.0GiB。

这里有什么思路吗？

编辑：date_idx索引同时包含date和venid列。 这些应该是两个单独的索引吗？

- pedalpete

---

你的解释查询说它必须扫描648997行（可能它没有充分有效地使用索引。如果是我，我会单独为列建立索引）。实际上有多少行被返回？ - nos

Answer 1

你需要确保查询仅使用索引，因此请确保索引覆盖了你选择的所有字段。此外，由于涉及范围查询，你需要将venid放在索引的最前面，因为它作为常量进行查询。因此，我会创建以下索引：

ALTER TABLE events ADD INDEX indexNameHere (venid, date, time);

这个索引包含了完成查询所需的所有信息，这意味着存储引擎可以在不需要查找表本身的情况下获取所需信息。但是，MyISAM可能无法做到这一点，因为它不将数据存储在索引叶子中，所以您可能无法获得所需的速度提升。如果是这种情况，请尝试创建表的副本，并在副本上使用InnoDB引擎，重复相同的步骤并查看是否能够获得显著的速度提升。InnoDB会将字段值存储在索引叶子中，并允许使用覆盖索引。

现在，当您解释查询时，希望您能看到以下内容:

mysql> EXPLAIN SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date;

id  select_type table  type  possible_keys        key       [..]  Extra
1   SIMPLE   events range date_idx, indexNameHere indexNameHere   Using index, Using where

- PatrikAkerstrand

---

覆盖索引是必不可少的。通过谨慎地创建索引和查询，600万行数据并不算什么。 - Michael Haren

太棒了！谢谢。我之前没有意识到需要用索引覆盖选择的字段，我以为只有 WHERE 字段需要创建索引。 - pedalpete

如果您记得的话，使用索引的新查询的执行时间是多少？ - Justin

抱歉回复晚了 @JustinKrause（和其他人），你的评论是在几年后发表的原始问题之后。修复索引后，查询时间似乎只有不到0.4秒。它的速度之快令人惊讶，而且当时它并不在专用服务器上。那是一个中等大小的托管机箱，当时并不是很大。我记不清是linode还是我不久后转换为linode。 - pedalpete

嘿，我现在有50万行数据，到年底将达到600万行。使用内连接对结果进行求和，平均用时2.345秒。我已经按照上述方式添加了索引，但没有改变。该怎么办？ - codefreaK

Answer 2

尝试添加一个跨越venid和date的键（或者反过来，或者两者都...）

- Greg

---

当你说“添加一个键”时，你是指索引吗？我编辑了我的条目，说明date_idx在日期和venid字段上都存在。 - pedalpete

谢谢Michael，我没有意识到SELECT字段也应该建立索引。干杯。 - pedalpete

在索引中也包含SELECT字段会使系统更加刚性。任何新的投影都必须添加到索引中。这是正确的操作方式吗？ - Franklin

Answer 3

我想，一个600万行的表应该能够通过普通技术进行优化。

我假设你有一台专用的数据库服务器，并且它有合理的内存大小（最少8G）。

你需要确保已经调整了mysql以有效地使用你的内存。如果你正在运行32位操作系统，请不要这样做。如果你正在使用MyISAM，请调整你的键缓冲区以使用相当比例但不要过多的内存。

无论如何，你都需要在生产级硬件上进行重复的性能测试。

- MarkR

---

感谢@MarkR，非常抱歉回复晚了。这是我建立的第二个网站，所以对于专用数据库服务器或类似的东西一无所知。我在同一台机器上运行了几年的所有进程，没有任何问题，MySQL扩展到800万行以上的表现让我惊讶。当数据达到那个点时，我会将旧数据存档。 - pedalpete

Answer 4

尝试在venid列上添加一个索引。

- Lucas Jones

---

我刚刚编辑了我的条目，date_idx 在日期和 venid 字段上都有。抱歉我最初没有写清楚。 - pedalpete