mysql> explain SELECT count(*) FROM `fdp`.`reuters_rkd_pm`;
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | reuters_rkd_pm | index | NULL          | PRIMARY | 53      | NULL | 7483719 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)mysql> SELECT count(*) FROM `fdp`.`reuters_rkd_pm`;
+----------+
| count(*) |
+----------+
|  7985412 |
+----------+
1 row in set (11.70 sec)mysql> desc `fdp`.`reuters_rkd_pm`;
+------------------------+---------------------+------+-----+-------------------+-------+
| Field                  | Type                | Null | Key | Default           | Extra |
+------------------------+---------------------+------+-----+-------------------+-------+
| xref                   | int(10) unsigned    | NO   | PRI | NULL              |       |
| prod_dt                | datetime            | NO   |     | NULL              |       |
| event_cd               | varchar(20)         | NO   |     | NULL              |       |
| event_asof             | datetime            | NO   |     | NULL              |       |
| periodseries_startdate | datetime            | NO   | PRI | NULL              |       |
| periodseries_enddate   | datetime            | YES  |     | NULL              |       |
| periodseries_seqno     | tinyint(4)          | NO   | PRI | NULL              |       |
| fyearend               | char(6)             | NO   | PRI | NULL              |       |
| fperiodend             | char(6)             | NO   | PRI | NULL              |       |
| periodtype             | char(1)             | NO   | PRI | NULL              |       |
| periodnum              | tinyint(3) unsigned | NO   | PRI | 0                 |       |
| period_length          | tinyint(3) unsigned | NO   |     | NULL              |       |
| period_length_unit     | char(1)             | NO   |     | NULL              |       |
| period_advance_date    | datetime            | YES  |     | NULL              |       |
| period_expect_date     | datetime            | YES  |     | NULL              |       |
| period_data_status     | char(1)             | YES  |     | NULL              |       |
| period_et_phase    | tinyint(3) unsigned | YES  |     | NULL              |       |
| upd_stmp               | timestamp           | NO   |     | CURRENT_TIMESTAMP |       |
+------------------------+---------------------+------+-----+-------------------+-------+
18 rows in set (0.00 sec)mysql>
表的记录所占据的磁盘空间在mysql administrator的Catalogs里面显示为1.7G,explain了都用了index,但是count(*)竟然需要12秒左右,如何优化?

解决方案 »

  1.   

    1 如果行数准确度要求不高 去information.table表里去拿
    或者2 读取非聚集索引来统计数据
    或者3 自己抓们维护一个有表的行数的表
      

  2.   

    没办法,你的表中这么多数据,COUNT(*)时MYSQL需要在你的索引上把所有节点都数一下才行。
      

  3.   

    没什么好办法。你的问题类似这样一个问题。假设 一个表 T(ID VARCHAR(10)PRIMARY KEY),如果800W记录,让你写个算法统计T表中一共多少记录? 有什么更好的方法吗?除了一条条数过来,显然没有。 information.table中可以得到一个非精确的行数。
      

  4.   

    我也遇到这样的问题。如果只是要COUNT(*),而没有其他条件,你可以把表引擎改成MYISAM,这样就很快又很准。如果必须要用INNODB,那么把这条语句的值程序缓存起来,10分钟去维护一下,不要每次都去数据库里取,当然这样的值是非精确的了。
      

  5.   


    mysql> SELECT count(id) FROM `fdp`.`reuters_rkd_pm`;
    +-----------+
    | count(id) |
    +-----------+
    |   7985412 |
    +-----------+
    1 row in set (5.89 sec)mysql>我自己建立一个自增主键id ,但是还是需要近6秒才能执行完。
      

  6.   

    原来的主键disable掉了?
    能看看你新表的表结构吗?
      

  7.   


    mysql> desc reuters_rkd_pm;
    +------------------------+---------------------+------+-----+---------------------+----------------+
    | Field                  | Type                | Null | Key | Default             | Extra          |
    +------------------------+---------------------+------+-----+---------------------+----------------+
    | xref                   | int(10) unsigned    | NO   |     | NULL                |                |
    | prod_dt                | datetime            | NO   |     | NULL                |                |
    | event_cd               | varchar(20)         | NO   |     | NULL                |                |
    | event_asof             | datetime            | NO   |     | NULL                |                |
    | periodseries_startdate | datetime            | NO   |     | NULL                |                |
    | periodseries_enddate   | datetime            | YES  |     | NULL                |                |
    | periodseries_seqno     | tinyint(4)          | NO   |     | NULL                |                |
    | fyearend               | char(6)             | NO   |     | NULL                |                |
    | fperiodend             | char(6)             | NO   |     | NULL                |                |
    | periodtype             | char(1)             | NO   |     | NULL                |                |
    | periodnum              | tinyint(3) unsigned | NO   |     | 0                   |                |
    | period_length          | tinyint(3) unsigned | NO   |     | NULL                |                |
    | period_length_unit     | char(1)             | NO   |     | NULL                |                |
    | period_advance_date    | datetime            | YES  |     | NULL                |                |
    | period_expect_date     | datetime            | YES  |     | NULL                |                |
    | period_data_status     | char(1)             | YES  |     | NULL                |                |
    | period_et_phase    | tinyint(3) unsigned | YES  |     | NULL                |                |
    | upd_stmp               | timestamp           | NO   |     | 0000-00-00 00:00:00 |                |
    | id                     | int(11)             | NO   | PRI | NULL                | auto_increment |
    +------------------------+---------------------+------+-----+---------------------+----------------+
    19 rows in set (0.00 sec)
      

  8.   

    你这种情况,就显示出复制的优越性了。如配置了复制,就可以在从机上将该表设为MYISAM,然后即可瞬间得出结果。
      

  9.   

    mysql> alter table test.reuters_rkd_pm add id2 int unsigned;
    mysql> create index idx_id2 on test.reuters_rkd_pm(id2);mysql> update test.reuters_rkd_pm set  id2 = id;
    Query OK, 0 rows affected (2 min 24.39 sec)
    Rows matched: 7985412  Changed: 0  Warnings: 0
    mysql> select count(*) from  test.reuters_rkd_pm where id2>0;
    +----------+
    | count(*) |
    +----------+
    |  7985412 |
    +----------+
    1 row in set (2.92 sec)
    mysql>用了辅助id2二级索引字段,但是也需要3秒左右,大家还有别的办法吗,能将这个count(*)语句控制在一秒之内吗?
      

  10.   

    分别试试count(*)
    count(主键)
    count(某一列)
    count(1)
      

  11.   

        顶一下,同样情况,我用myisam引擎的,执行select count(*) from 表名,查数千万的数据总量值也是1秒以内的事。不知道为什么在INNODB里却很慢。