一、如何删除较大的表
两个删除策略:
1、慢的方法是写脚本定时任务带where的delete语句 慢慢删除 范围定小一点 锁的时间很短 客户端无感;
2、快的方法就是找个业务空的时候 新建表 程序往新表中插入 然后把老表中需要的记录让新表中插入;
举个例子说明:
情景:数据库中有一张表t_aa , 表中数据包含了2年的数据,将近3000万条记录,目前需要清除一年的历史数据。
有人说很简单,直接delete+where条件就可以了,比如 delete from t_aa where CRE_TIME <= '2018-01-16 00:00:00';
那我们来测算下假设需要删除1000万条数据所需的用时:
不出问题的情况:备份t_aa表(起码30分钟) + 删除1000万条记录(100分钟,初步测试1分钟删除10万条)=约2小时
出现问题需还原的情况:备份t_aa表(起码30分钟) + 删除1000万条记录(100分钟,初步测试1分钟删除10万条)+ source还原3000万的记录(4小时,初步测试2小时还原1500万条记录) = 6小时
时间其实还是蛮长的吧,下面说下解决亲测的解决方法:
1、优化my.cnf文件参数:
innodb_flush_log_at_trx_commit = 0;
interactive_timeout = 120;
wait_timeout = 120;
max_allowed_packet = 128M
innodb_buffer_pool_size = 2G
2、新建一张表,表结构与t_aa相同:
CREATE TABLE t_aa_copy
SELECT * FROM t_aa WHERE 1=2; (这种创建方式,表结构相同,但是主键,索引都没了)
最好 show create student\G; 手动创建表,例如:
mysql> show create table student\G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec) ERROR:
No query specified
经过验证,索引的使用对查询和插入速度有以下影响:
- 查询时,使用带有索引的列可以明显提高查询速度;
- 插入时,如果插入的列有索引,插入速度会减慢。
在以后创建表格时,尽量保证将主要的数据都插入之后,再添加索引,避免在添加索引之后进行数据插入,以保证效率最高。
3、插入近一年的数据,放入新表 t_aa_copy
INSERT INTO t_aa_copy
SELECT * FROM t_aa
WHERE CRE_TIME >= '2018-01-16 00:00:00';
4、重命名原始表,并给新表命名为原始表的原始表名
RENAME TABLE t_aa TO t_aa_old;
RENAME TABLE t_aa_copy TO t_aa;
不出问题的情况: 修改参数重启服务(5分钟)+ 新建t_aa_copy表(2分钟) + 向新表插入数据(10分钟)+ 重命名(2分钟)=约20分钟
出现问题需还原的情况: 修改参数重启服务(5分钟)+ 新建t_aa_copy表(2分钟) + 向新表插入数据(10分钟,经过测试3分钟1000万条数据)+ 重命名(2分钟)+ 重命名(2分钟)=约25分钟
-----------------------------------------------
二、可能会出现的问题:
1、报错:ERROR 1206 (HY000): The total number of locks exceeds the lock table size