MySQL 大数据操作注意事项
http://netkiller.github.io/journal/mysql.parallel.html
关于 delete
delete from mytable 必死无疑,你需要分批删除,尽量缩小每个批次删除的记录数,delete 是可以并行执行的,你可以同时运行多个删除操作
手工分批删除
通过 where 条件和 limit 限制条数,缩小删除结果集,分多笔执行,手工删除。
mysql> show processlist; +--------+-----------------+---------------------+-----------+---------+-------+-----------------------------+--------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-----------------+---------------------+-----------+---------+-------+-----------------------------+--------------------------------------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 52 | Waiting for next activation | NULL | | 115986 | dba | localhost | example | Query | 0 | NULL | show processlist | | 117446 | dba | localhost | example | Query | 20 | updating | delete from mytable where OPEN_TIME like '2011.11.28%' | | 117525 | dba | localhost | example | Query | 2 | updating | delete from mytable where OPEN_TIME like '2011.12.02%' | | 117526 | dba | localhost | example | Query | 49 | updating | delete from mytable where OPEN_TIME like '2011.12.12%' | | 117527 | dba | localhost | example | Query | 6 | updating | delete from mytable where OPEN_TIME like '2011.12.21%' | | 117528 | dba | localhost | example | Query | 64 | updating | delete from mytable where OPEN_TIME like '2011.12.30%' | | 117546 | dba | localhost | example | Query | 33 | updating | delete from mytable where OPEN_TIME like '2011.11.10%' | +--------+-----------------+---------------------+-----------+---------+-------+-----------------------------