Mysql性能在600万行表上 [英] Mysql performance on 6 million row table

问题描述

有一天，我怀疑我必须学习hadoop并将所有这些数据传输到非结构化数据库，但我很惊讶地发现性能在如此短的时间内显着降低。

One day I suspect I'll have to learn hadoop and transfer all this data to a non-structured database, but I'm surprised to find the performance degrade so significantly in such a short period of time.

我有一个不到600万行的mysql表。
我在这个表上做了一个非常简单的查询，并且相信我已经有了所有正确的索引。

I have a mysql table with just under 6 million rows. I am doing a very simple query on this table, and believe I have all the correct indexes in place.

查询是

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秒才能运行。

so i am using the correct index as far as I can tell, but this query is taking 11 seconds to run.

数据库是MyISAM，phpMyAdmin说表是1.0GiB。

The database is MyISAM, and phpMyAdmin says the table is 1.0GiB.

这里有什么想法吗？

编辑：
date_idx是日期和静脉列的索引。那些应该是两个单独的索引吗？

Edited: The date_idx is indexes both the date and venid columns. Should those be two seperate indexes?

推荐答案

你想要确定的是查询只使用索引，所以make确保索引涵盖了您选择的所有字段。此外，由于它涉及范围查询，因此需要在索引中首先使用venid，因为它被查询为常量。因此我会像这样创建和索引：

What you want to make sure is that the query will use ONLY the index, so make sure that the index covers all the fields you are selecting. Also, since it is a range query involved, You need to have the venid first in the index, since it is queried as a constant. I would therefore create and index like so:

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

使用此索引，完成查询所需的所有信息都在索引中。这意味着，希望存储引擎能够获取信息而无需在表内部实际查找。但是，MyISAM可能无法执行此操作，因为它不会将数据存储在索引的叶子中，因此您可能无法获得所需的速度增加。如果是这种情况，请尝试创建表的副本，并在副本上使用InnoDB引擎。在那里重复相同的步骤，看看你是否有显着的速度提升。 InnoDB 确实将字段值存储在索引叶子中，并允许覆盖索引。

With this index, all the information that is needed to complete the query is in the index. This means that, hopefully, the storage engine is able to fetch the information without actually seeking inside the table itself. However, MyISAM might not be able to do this, since it doesn't store the data in the leaves of the indexes, so you might not get the speed increase you desire. If that's the case, try to create a copy of the table, and use the InnoDB engine on the copy. Repeat the same steps there and see if you get a significant speed increase. InnoDB does store the field values in the index leaves, and allow covering indexes.

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

Now, hopefully you'll see the following when you explain the query:

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

这篇关于Mysql性能在600万行表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！