通过mysql optimize table优化表性能

最近运营一直反馈后台审核页面很慢，我看了一下是分页慢导致的。这个表有300多万条数据，给后台用，count要5秒太慢了。

select count(*) from ck_album_status  where album_status = 'new';

mysql> explain select count(*) from ck_album_status  where album_status = 'new';
+----+-------------+-----------------+------+------------------------------+--------------+---------+-------+---------+--------------------------+
| id | select_type | table           | type | possible_keys                | key          | key_len | ref   | rows    | Extra                    |
+----+-------------+-----------------+------+------------------------------+--------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | ck_album_status | ref  | idx_alb_stat,idx_alb_stat_id | idx_alb_stat | 98      | const | 1384320 | Using where; Using index |
+----+-------------+-----------------+------+------------------------------+--------------+---------+-------+---------+--------------------------+
1 row in set (0.01 sec)

  KEY `idx_alb_stat` (`album_status`) USING BTREE,

 

删掉这个表的数据到20万以下还是很慢

 但奇怪的是发现data_length还是300多万，通过mysql optimize试试。果然好了：