optimize 删除写入频繁表

收到电话，反映数据库中的表数据删除非常之慢，拿到 sql 语句，测试先将旧的数据清完：

mysql> DELETE  FROM zb_live_user_list WHERE  grade = 0 AND `updatetime`<='2014-06-14 13:20:36' limit 10000;

Query OK, 10000 rows affected (1 min 51.48 sec)

mysql> DELETE  FROM zb_live_user_list WHERE  grade = 0 AND `updatetime`<='2014-06-14 13:20:36' limit 100000;

Query OK, 100000 rows affected (2 min 1.84 sec)

mysql> DELETE  FROM zb_live_user_list WHERE  grade = 0 AND `updatetime`<='2014-06-14 13:20:36' limit 500000;

Query OK, 500000 rows affected (2 min 27.87 sec)

旧数据删完后，看看这个表的数据量：

mysql> select count(*) FROM zb_live_user_list;                                                         

+----------+

| count(*) |

+----------+

|   273320 |

+----------+

1 row in set (5.80 sec)

做一次 optimize 操作：

mysql> OPTIMIZE TABLE zb_live_user_list;

+-----------------------+----------+----------+-------------------------------------------------------------------+

| Table                 | Op       | Msg_type | Msg_text                                                          |

+-----------------------+----------+----------+-------------------------------------------------------------------+

| xiu.zb_live_user_list | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| xiu.zb_live_user_list | optimize | status   | OK                                                                |

+-----------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (40.77 sec)

mysql> DELETE FROM zb_live_user_list WHERE  grade > 0 AND `updatetime`<='2014-06-14 14:20:36' limit 100000;

Query OK, 204 rows affected (0.13 sec)

mysql> DELETE FROM zb_live_user_list WHERE  grade = 0 AND `updatetime`<='2014-06-14 14:15:01' LIMIT 100;

Query OK, 100 rows affected (0.35 sec)

mysql> DELETE FROM zb_live_user_list WHERE  grade = 0 AND `updatetime`<='2014-06-14 14:15:01' LIMIT 1000;

Query OK, 1000 rows affected (0.52 sec)

mysql> DELETE FROM zb_live_user_list WHERE  grade = 0 AND `updatetime`<='2014-06-14 14:15:01' LIMIT 10000;

Query OK, 10000 rows affected (0.72 sec)

再看看查询：

mysql> select count(*) from zb_live_user_list;

+----------+

| count(*) |

+----------+

|   130604 |

+----------+

1 row in set (0.78 sec)

mysql> 

对于写入删除频繁的表，需要定期的做一次 optimize 操作，优化索引。