innodb表select count(*) from t 速度极慢

YourEyes

终于明白了，谢谢各位

yueliangdao0608

如果你有个自增的ID ，那直接SHOW TABLE STATUS LIKE ‘YOUR TABLE NAME’
看看里面的AUTO_INCREMENT 值-1

yueliangdao0608

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.52 sec)

mysql> select count(*) from t where id > 0;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.60 sec)

mysql> explain select count(*) from t where id > 0;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL | 524479 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from t;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | t     | index | NULL          | PRIMARY | 4       | NULL | 1048959 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)


如果你的查询总数非常频繁的话，也可以考虑建一个触发器。。

http://blog.chinaunix.net/u/29134/showart_477425.html

yanghaiquan

路过 ， 回复错了