在MySQL 5中，SELECT COUNT(1)FROM table_name非常慢 [英] In MySQL 5, SELECT COUNT(1) FROM table_name is very slow

问题描述

我有一个MySQL 5.0数据库，其中包含一些表，这些表包含超过5000万行.但是我怎么知道呢?当然，通过运行"SELECT COUNT(1)FROM foo".在一个包含5880万行的表上进行的查询完成了 10分钟！

I have a MySQL 5.0 database with a few tables containing over 50M rows. But how do I know this? By running "SELECT COUNT(1) FROM foo", of course. This query on one table containing 58.8M rows took 10 minutes to complete!

mysql> SELECT COUNT(1) FROM large_table;
+----------+
| count(1) |
+----------+
| 58778494 | 
+----------+
1 row in set (10 min 23.88 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM large_table;
+----+-------------+-------------------+-------+---------------+----------------------------------------+---------+------+-----------+-------------+
| id | select_type | table             | type  | possible_keys | key                                    | key_len | ref  | rows      | Extra       |
+----+-------------+-------------------+-------+---------------+----------------------------------------+---------+------+-----------+-------------+
|  1 | SIMPLE      | large_table       | index | NULL          | fk_large_table_other_table_id          | 5       | NULL | 167567567 | Using index | 
+----+-------------+-------------------+-------+---------------+----------------------------------------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

mysql> DESC large_table;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| created_on        | datetime            | YES  |     | NULL    |                | 
| updated_on        | datetime            | YES  |     | NULL    |                | 
| other_table_id    | int(11)             | YES  | MUL | NULL    |                | 
| parent_id         | bigint(20) unsigned | YES  | MUL | NULL    |                | 
| name              | varchar(255)        | YES  |     | NULL    |                | 
| property_type     | varchar(64)         | YES  |     | NULL    |                | 
+-------------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

所有有问题的表都是InnoDB.

All of the tables in question are InnoDB.

有什么想法为什么这么慢，以及我如何加快速度?

Any ideas why this is so slow, and how I can speed it up?

推荐答案

如果您需要立即获得结果，而又不在乎它是58.8M还是51.7M，则可以通过调用来找到大约的行数

If you need to have the result instantly and you don't care if it's 58.8M or 51.7M, you can find out the approximate number of rows by calling

show table status like 'large_table';

请参阅列rows
有关结果的更多信息，请参见 http://dev.mysql.com/doc/refman/5.1/zh-CN/show-table-status.html

See the column rows
For more information about the result take a look at the manual at http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

这篇关于在MySQL 5中，SELECT COUNT(1)FROM table_name非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！