2020-11-19 为什么说MySQL中不建议使用Delete删除数据

	当我们的系统随着业务量的增长，存储在MySQL中的数据会日益增多，其实有一些表对于业务来说，没必要去做一些长久的存储，当我们去对表做数据清理的时候，一般都会想到insert+delete的方式。
	**MySQL采用的是InnoDB存储空间分布，而InnoDB存储结构主要包括：逻辑存储结构和物理存储结构。**
	从逻辑上来说：逻辑上是由表空间tablespace---->段segment或者inode---->区Extent------->数据页Page构成，innodb逻辑管理单元是segment，空间分配的最小单位是extent，每个 segment都会从表空间FREE_PAGE中分配32个page，当这32个page不够用时，会按照以下原则进行扩展：如果当前小于一个extent，则扩展到一个extent；当表空间小于32MB时，每次扩展一个extent；表空间大于32MB时，每次扩展4个extent。
	从物理上来说：物理上主要由系统用户数据文件，日志文件组成，数据文件主要存储MySQL字典数据和用户数据，日志文件记录的是date page的变更记录，用于MySQL  Crash时的恢复。
	**Innodb表空间**
	InnoDB存储包括三类表空间：系统表空间，用户表空间，Undo表空间
				1. 系统表空间：主要存储MySQL内部的数据字典，如information_schema下的数据
				2. 用户表空间：当开启innodb_file_per_table=1时，数据表会从系统表空间独立出来存储在table_name.idb命令的数据文件中，			结构信息存储在table_name.frm文件中
				3. Undo表空间：存储Undo信息，如快照一致读和flashback都是利用undo信息
	从MySQL8.0开始允许用户自定义表空间，这样做的好处是可以做到冷热分离，分别用HDD和SSD来存储，既能实现数据的高效访问，又能节约成本，比如可以添加两块500G的存储盘，经过创建卷组vg，划分逻辑卷lv，创建数据目录并mount相应的lv，假设划分目录分别是

/hot_date和code_date。
这样就可以将核心业务表存储在高性能SSD盘上，一些日志、流水表存储在HDD盘上。如：

#创建热数据表空间
create tablespace tbs_data_hot add datafile '/hot_data/tbs_data_hot01.dbf' max_size 20G;
#创建核心业务表存储在热数据表空间
create table booking(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_hot;
#创建冷数据表空间
create tablespace tbs_data_cold add datafile '/hot_data/tbs_data_cold01.dbf' max_size 20G;
#创建日志，流水，备份类的表存储在冷数据表空间
create table payment_log(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_cold;
#可以移动表到另一个表空间
alter table payment_log tablespace tbs_data_hot;

	从空间上来讲，MySQL内部不会真正删除空间，而是做标记删除，即将delflag：N修改为delflag:Y，删除之后会被purge进入删除链表，如果下一次insert更大的纪录，delete之后的空间不会被重用，如果插入的记录小于等于delete的记录会被重用。

Delete优化建议：
1、控制业务账号权限
对于一个大的系统来说，需要根据业务的特点去拆分子系统，每一个子系统可以看作一个service，例如一个网站，上面上会有很多服务，，比如用户、搜索、位置等。每个服务对应一个数据库，为该数据库创建单独账号，同时只授予DML权限且没有delete权限，同时禁止跨库访问。

#创建用户数据库并授权
create database mt_user charset utf8mb4;
grant USAGE, SELECT, INSERT, UPDATE ON mt_user.*  to 'w_user'@'%' identified by 't$W*g@gaHTGi123456';
flush privileges;

2、delete改为标记删除
在MySQL数据库建模规范中有4个公共字段，基本上每个表必须有的，同时在create_time列要创建索引。有两方面好处：
1、一些查询业务场景都会有一个默认的时间段，比如7天或者一个月，都是通过create_time去过滤，走索引扫描更快。
2、一些核心的业务表需要以T+1的方式抽取数据仓库中，比如每天晚上抽取前一天数据，都是通过create_time过滤的。
总结：
通过InnoDB存储空间分布，delete对性能的影响可以看到，delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；
同时在碎片回收的时候，会耗用大量的CPU，磁盘空间，影响表上的DML操作。
在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。
DML：DML(data manipulation language)：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
DDL：DDL(data definition language)：
DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL：DCL(Data Control Language)：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括(grant,deny,revoke等)语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

DML和DDL的区别：
1、DML操作是可以手动控制事务的开启、提交和回滚的。
2、DDL操作是隐性提交的，不能rollback！