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秒左右,如何优化?
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| 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秒左右,如何优化?
或者2 读取非聚集索引来统计数据
或者3 自己抓们维护一个有表的行数的表
mysql> SELECT count(id) FROM `fdp`.`reuters_rkd_pm`;
+-----------+
| count(id) |
+-----------+
| 7985412 |
+-----------+
1 row in set (5.89 sec)mysql>我自己建立一个自增主键id ,但是还是需要近6秒才能执行完。
能看看你新表的表结构吗?
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)
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(*)语句控制在一秒之内吗?
count(主键)
count(某一列)
count(1)