MySQL +大表=慢查询? [英] Mysql + big tables = slow queries?
问题描述
我在Mysql上的一张大桌子有一些性能问题: 该表有3800万行，其大小为3GB. 我想通过测试2列进行选择: 我尝试了很多索引(每列一个索引，两列一个索引)，但查询速度仍然很慢:如下所示，需要4秒钟以上才能获取1644行:
I have some performance issues with a big table on Mysql : The table has got 38 million rows, its size is 3GB. I want to select by testing 2 columns : I tried many indexing (one index for each columns and one index with the 2 columns) but I still have slow query : like below, more than 4 secs to fetch 1644 rows :
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)
我还发现仅读取表中很远的一行非常慢:
I also discovered that fetching only one row far away in the table is very slow :
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)
...并且在表的开头并不慢:
... and not slow at the beginning of the table:
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引擎.如何加快对大表的查询?
The table uses Innodb engine. How can I speed-up queries for big tables ?
推荐答案
您可以做的主要事情就是添加索引.
The main thing you can do is to add indexes.
每次在where子句中使用列时，请确保其具有索引.您创建的列中没有一个.
Any time that you use a column in a where clause, make sure it has an index. There isn't one on your created column.
从本质上讲，包含创建列的多重索引不是创建时的索引，因为在多重索引中创建的内容并非首位.
The multi-index including the created column in essence is NOT an index on created since created isn't first in the multi-index.
使用多索引时，几乎应该始终将基数较高的列放在第一位.因此，将索引设为:(created, word_id)，(word_id)会大大提高您的效率.
When using multi-indexes, you should almost always put the column with higher cardinality first. So, having the indexes be: (created, word_id), (word_id) would give you a significant boost.
这篇关于MySQL +大表=慢查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
