MySQL删除数据的方式

以MySQL 5.7为例，数据库删除数据的方式一共有以下三种：

1. delete
2. truncate
3. drop

以上三种方式都可以删除数据，但是使用场景是不同的。
对于整个表进行删除的执行速度来说：
drop > truncate >> delete

MySQL删除数据的方式-delete

delete是属于数据库的DML操作语言，一般是根据条件逐行进行删除。
使用delete删除数据时，数据库只能删除数据不能删除表的结构，并且会触发数据库的事务机制。
delete执行时，会先将所删除数据缓存到rollback segment中，事务commit之后生效;
在InnoDB中，使用delete其实并不会真正的把数据删除，是一种逻辑删，数据库底层实际上只是给删除的数据做了一个已删除的标记，因此，删除数据后的表占空间大小和删除前是一样的.

MySQL删除数据的方式-drop

drop属于数据库DDL定义语言，同 truncate ，执行后立即生效，无法找回。
drop table table_name立刻释放磁盘空间 ,drop 语句将删除表的结构、被依赖的约束(constraint)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

删除数据的方式-TianMu 引擎的支持情况

目前StoneDB的TianMu引擎是支持 truncate 语句 和 drop 语句的，但是delete语句目前还不支持。

TianMu引擎需要不需要delete？

TianMu引擎设计的初衷

TianMu是一个列式存储引擎，列式存储的出现主要是为了方便快捷查询和高效存储大量同类型的数据而设计的,主要使用场景就是OLAP场景。下面是OLAP场景的部分关键特征：
①绝大多数是读请求
②数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。
③已添加到数据库的数据不能修改。
④对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
⑤列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
⑥处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
⑦事务不是必须的
⑧对数据一致性要求低

目前行业现状

OLAP场景下 对于数据的delete的操作可以说没有或者频率很小，同时列式存储对比行式存储来说并不擅长数据的增删改，如果是为了极致的查询性能，完全可以舍弃 DML 操作。但是为了功能的完整性，提升产品的竞争力，目前我们也放开了 insert 和 单表的 update 的功能，delete功能暂时还不支持,未来我们将对改功能进行支持。

主流列式数据库的delete方案

本节主要讲以下三种列式存储数据库的delete方案：
•openGauss
•ClickHouse
•PolarDB

openGauss 列存储引擎的方案

底层存储结构

openGauss底层存储结构与stonedb类似 ，存储基本单位是CU（Compression Unit，压缩单元），即表中一列的一部分数据组成的压缩数据块。行存引擎中是以行作为单位来管理，而当使用列存储时，整个表整体被按照不同列划分为若干个CU。

CU文件本身结构，则如下图所示。

每个CU对应一个CU Desc的记录，在CU desc里记录了整个CU的事务时间戳信息、CU的大小、存储位置、magic校验码、min/max等信息。

每张列存表还配有一张Delta表，Delta表自身为行存储表。当有少量的数据插入到一张列存表时，数据会被暂时放入Delta表，等到到达阈值或满足一定条件或操作时再行整合为CU文件。Delta表可以避免单点数据操作带来的很重的CU操作与开销。

删除策略

• 列存储的CU中数据的删除，实际上是标记删除。删除操作，相当于是更新了CUDesc表中CU对应CUDesc记录的delete bitmap（删除位图）结构，标记列中某行对应数据已被删除，而CU文件数据不会被更改。这样可以避免删除操作带来的IO放大以及解压、压缩的高额CPU开销。这样的设计，也可以使得对于同一个CU的select（查询）和delete（删除）互不阻塞，提升并发能力。列存储CU中数据更新，则是遵循append-only（仅允许追加）原则的，即CU文件仅会向后进行延展扩充，亦或是启用新的CU文件，而不是在对应行在CU中的位置就地更新。

失效空间的清理

• 由于CU以及CUDesc的元数据管理模式，原有系统中的Vacuum机制实际上并不会非常有效的清除CU中已经失效的存储空间，因为Lazy Vacuum（清理数据时，只是标识无用行的状态可以录入新数据，不会影响对表数据的操作）仅能在CUDesc级别进行操作，在多数场景下无法对CU文件本身进行清理。列存储内部如果要对列存数据表进行清理，需要执行Vacuum Full（除了清理无用行，还会合并数据块，整个过程会锁定表）操作。

ClickHouse delete 方案

特点：缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据。
数据有序存储
ClickHouse支持在建表时，指定将数据按照某些列进行sort by。排序后，保证了相同sort key的数据在磁盘上连续存储，且有序摆放。在进行等值、范围查询时，where条件命中的数据都紧密存储在一个或若干个连续的Block中，而不是分散的存储在任意多个Block， 大幅减少需要IO的block数量。另外，连续IO也能够充分利用操作系统page cache的预取能力，减少page fault。

delete 策略

ClickHouse是个分析型数据库。
OLAP场景下，数据一般是不变的，因此ClickHouse对update、delete的支持是比较弱的，实际上并不支持标准的update、delete操作。
ClickHouse通过alter方式实现更新、删除，它把update、delete操作叫做mutation(突变)。
标准SQL的更新、删除操作是同步的，即客户端要等服务端返回执行结果（通常是int值）；
而ClickHouse的update、delete是通过异步方式实现的，当执行update语句时，服务端立即返回，但是实际上此时数据还没变，而是排队等着。
Mutation具体过程
首先，使用where条件找到需要修改的分区；然后，重建每个分区，用新的分区替换旧的，分区一旦被替换，就不可回退；对于单独一个分区，是原子性的；但对于整个mutation，如果涉及多个分区，则不是原子性的。

PolarDB In-Memory Column Index

特点：PolarDB将列存实现为InnoDB的二级索引
在PolarDB中所有Primary Index和Secondary Index都实现为一个B+Tree。列索引在定义上是一个Index，但其实是一个虚拟的索引，用于捕获对该索引覆盖列的增删改操作。

如上图所示，在PolarDB中所有Primary Index和Secondary Index都实现为一个B+Tree。而列索引在定义上是一个Index，但其实是一个虚拟的索引，用于捕获对该索引覆盖列的增删改操作。
对于上面的表其主表(Primary Index）包含（C1,C2,C3,C4,C5) 5列数据, Seconary Index索引包含(C2,C1) 两列数据, 在普通二级索引中，C2与C1编码成一行保存在B+tree中。而其中的列存索引包含(C2,C3,C4)三列数据. 在实际物理存储时，会对三列进行拆分独立存储，每一列都会按写入顺序转成列存格式。
列存实现为二级索引的另一个好处是执行器的工程实现非常简单，在MySQL中已经存在覆盖索引的概念，即一个查询所需要的列都在一个二级索引中存储，则可以直接利用这个二级索引中的数据满足查询需求，使用二级索引相对于使用Primary Index可以极大减少读取的数据量进而提升查询性能。当一个查询所需要的列都被列索引覆盖时，借助列存的加速作用，可以数十倍甚至数百倍的提升查询性能。

实现为InnoDB二级索引方案的优点：

1.查询执行器的工程实现非常简单
2.可以复用InnoDB的事务处理框架
3.可以复用InnoDB的数据编码格式
4.DDL语句操作非常灵活
5.可以复用InnoDB的Redo事务日志模块
6.二级索引与主表有一样的生命周期，方便管理

列存数据组织

对ColumnIndex中每一列，其存储都使用了无序且追加写的格式，结合标记删除及后台异步compaction实现空间回收。其具体实现上有如下几个关键点：
列索引中记录按RowGroup进行组织，每个RowGroup中不同的列会各自打包形成DataPack。
每个RowGroup都采用追加写，分属每个列的DataPack也是采用追加写模式。对于一个列索引，只有个Active RowGroup负责接受新的写入。当该RowGroup写满之后即冻结，其包含的所有Datapack会转为压缩格保存到磁盘上，同时记录每个数据块的统计信息便于过滤。
列存RowGroup中每新写入一行都会分配一个RowID用作定位，属于一行的所有列都可以用该RowID计算定位，同时系统维护PK到RowID的映射索引，以支持后续的删除和修改操作。
删除操作只需要设置一个删除标记位。
更新操作采用标记删除的方式来支持，对于更新操作，首先根据RowID计算出其原始位置并设置删除标记，然后在ActiveRowGroup中写入新的数据版本。
当一个RowGroup中的无效记录超过一定阈值，则会触发后台异步compaction操作，其作用一方面是回收空间，另一方面可以让有效数据存储更加紧凑，提升分析型查询单的效率。

delete 策略

删除操作只需要设置一个删除标记位。
更新操作采用标记删除的方式来支持，对于更新操作，首先根据RowID计算出其原始位置并设置删除标记，然后在ActiveRowGroup中写入新的数据版本。
当一个RowGroup中的无效记录超过一定阈值，则会触发后台异步compaction操作，其作用一方面是回收空间，另一方面可以让有效数据存储更加紧凑，提升分析型查询的效率。

各列式存储的delete方案汇总

TianMu引擎 delete 功能规划

GitHub issue 链接：
https://github.com/stoneatom/stonedb/issues/343

TianMu引擎 delete 功能实现流程图

来源：https://www.cnblogs.com/yangwilly/p/16591997.html
本站部分图文来源于网络，如有侵权请联系删除。