如何批量删数据和调整系统表空间？

业务需要删除大量数据，如果直接 delete 会造成如下问题：

• 会产生大事务，造成主从延迟，影响数据库高可用切换。
• 系统表空间会不断膨胀。
• 锁定的记录多,更容易可能导致锁等待。
  

问1：如何优雅的删除大量数据？

• 如果表不需要就直接 drop
• 如果只保留表结构用 truncate
• 如果只保留部分数据可以使用 pt-archive 进行分批删除
• 特别注意，如果表太大的话，直接drop会truncate可能会造成大量IO导致数据库出现短暂响应延迟，可以通过硬链接的方式对表删除处理
  

问2：系统表空间不断膨胀怎么处理
1.如果是已存在的数据库 无法在线收缩，那就通过mysqldump的方式建立新的从库，然后主从切换
2.新实例如何处理

• 可以设置独立表空间，关键参数
  

1. innodb_file_per_table

复制代码

独立表空间也会产生碎片，但是可以通过 OPTIMIZE TABLE 或 ALTER TABLE xxxx ENGINE=INNODB 进行碎片回收，5.7之后该操作属online ddl，具体可以自行测试

• 设置独立UNDO空间，然后设置自动回收。
  

5.6 版本就支持独立UNDO空间，但是不支持在线回收，关键参数

1. innodb_undo_directory
   
2. innodb_undo_tablespaces
   
3. innodb_undo_logs

复制代码

5.7 版本增加了在线回收的功能，关键参数

1. innodb_undo_log_truncate
   
2. innodb_max_undo_log_size
   
3. innodb_purge_rseg_truncate_frequency

复制代码

8.0 版本中undo log的管理更加灵活，主要如下改进

• 可以动态创建或删除UNDO表空间
• 可以动态增加或减少UNDO表空间的数量
• 无论是否要进行InnoDB恢复，也可以在启动前更改相关设置
  

问3：ibtmp文件不断增大，怎么处理
5.7 版本可以设置限制ibtmp大小，但是需要重启实例；同时超过设定的最大值会导致SQL执行失败，关键参数

1. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

复制代码

8.0 版本临时表空间有区分全局和session级；垃圾SQL生成的临时表空间随着SQL的结束也会跟着自动释放。