Mysql +大表=慢查询？

Question

我对Mysql上的一个大表有一些性能问题:这个表有3800万行，大小是3 3GB。我想通过测试两列来选择:我尝试了许多索引(每列一个索引，两列一个索引)，但查询速度仍然很慢:像下面这样，超过4秒才能获取1644行：

SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` WHERE (`twstats_twwordstrend`.`word_id` = 1001 AND `twstats_twwordstrend`.`created` > '2011-11-07 14:01:34' );
...
...
...
1644 rows in set (4.66 sec)

EXPLAIN SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` WHERE (`twstats_twwordstrend`.`word_id` = 1001 AND `twstats_twwordstrend`.`created` > '2011-11-07 14:01:34' );
+----+-------------+----------------------+-------+-----------------------------------------------------+-----------------------+---------+------+------+-------------+
| id | select_type | table                | type  | possible_keys                                       | key                   | key_len | ref  | rows | Extra       |
+----+-------------+----------------------+-------+-----------------------------------------------------+-----------------------+---------+------+------+-------------+
|  1 | SIMPLE      | twstats_twwordstrend | range | twstats_twwordstrend_4b95d890,word_id_created_index | word_id_created_index | 12      | NULL | 1643 | Using where |
+----+-------------+----------------------+-------+-----------------------------------------------------+-----------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> describe twstats_twwordstrend;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| created | datetime | NO   |     | NULL    |                |
| freq    | double   | NO   |     | NULL    |                |
| word_id | int(11)  | NO   | MUL | NULL    |                |
+---------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show index from twstats_twwordstrend;
+----------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| twstats_twwordstrend |          0 | PRIMARY                       |            1 | id          | A         |    38676897 |     NULL | NULL   |      | BTREE      |         |               |
| twstats_twwordstrend |          1 | twstats_twwordstrend_4b95d890 |            1 | word_id     | A         |      655540 |     NULL | NULL   |      | BTREE      |         |               |
| twstats_twwordstrend |          1 | word_id_created_index         |            1 | word_id     | A         |      257845 |     NULL | NULL   |      | BTREE      |         |               |
| twstats_twwordstrend |          1 | word_id_created_index         |            2 | created     | A         |    38676897 |     NULL | NULL   |      | BTREE      |         |               |
+----------------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.03 sec)

我还发现，在表中只读取很远的一行非常慢：

mysql> SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` limit 10000000,1;
+----------+---------------------+--------------------+---------+
| id       | created             | freq               | word_id |
+----------+---------------------+--------------------+---------+
| 10000001 | 2011-09-09 15:59:18 | 0.0013398539559188 |   41295 |
+----------+---------------------+--------------------+---------+
1 row in set (1.73 sec)

..。并且在表格开始时不会变慢：

mysql> SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` limit 1,1;
+----+---------------------+---------------------+---------+
| id | created             | freq                | word_id |
+----+---------------------+---------------------+---------+
|  2 | 2011-06-16 10:59:06 | 0.00237777777777778 |       2 |
+----+---------------------+---------------------+---------+
1 row in set (0.00 sec)

该表使用Innodb引擎。如何加快大表的查询速度？

Answer 1

您可以做的主要事情是添加索引。

任何时候在where子句中使用列时，都要确保它有索引。在您创建的列中没有。

包含created列的多索引在本质上不是created上的索引，因为created不是多索引中的第一个索引。

在使用多索引时，几乎总是应该将基数较高的列放在第一位。因此，将索引设置为：(created, word_id)，(word_id)会给您带来很大的提升。

Answer 2

使用LIMIT 10000000,1的查询总是非常慢，因为它需要提取1000多万行(除了最后一行之外，它会忽略所有行)。如果您的应用程序经常需要这样的查询，请考虑重新设计。

表没有“开始”和“结束”；它们本身并不是有序的。

在我看来，您需要一个关于(word_id，created)的索引。

您绝对应该在具有生产级硬件的非生产服务器上对此进行性能测试。

顺便说一句，3 3Gb的数据库现在并不太大，除了最小的服务器外，它可以放在所有服务器的内存中(您运行的是64位操作系统，对吧，并且已经适当地调整了innodb_buffer_pool？或者是你的系统管理员做的？)。