常见面试题-MySQL软删除以及索引结构

为什么 mysql 删了行记录，反而磁盘空间没有减少？

答：

在 mysql 中，当使用 delete 删除数据时，mysql 会将删除的数据标记为已删除，但是并不去磁盘上真正进行删除，而是在需要使用这片存储空间时，再将其从磁盘上清理掉，这是 MySQL 使用延迟清理的方式。

延迟清理的优点：

• 如果 mysql 立即删除数据，会导致磁盘上产生大量的碎片，使用延迟清理可以减少磁盘碎片，提高磁盘的读写效率
• 如果删除数据时立即清理磁盘上的数据，会消耗大量的性能。（如果一个大表存在索引，只删除其中一行，整个索引结构就会发生变化）

延迟清理的缺点：

• 这些被标记为删除的数据，就是数据空洞，不仅浪费空间，还影响查询效率。

  mysql 是以数据页为单位来存储和读取数据，如果一个表有大量的数据空洞，那么 mysql 读取一个数据页，可能被标记删除的数据就占据了大量的空间，导致需要读取很多个数据页，影响查询效率

如何回收未使用空间：

optimize table 表名

索引的结构？

答：

索引是存储在引擎层而不是服务层，所以不同存储引擎的索引的工作方式也不同，我们只需要重点关注 InnoDB 存储引擎和 InnoDB 存储引擎中的索引实现，以下如果没有特殊说明，则都为 InnoDB 引擎。

mysql 支持两种索引结构： B-tree 和 HASH

• B-tree 索引

B-tree 索引结构使用 B+ 树来进行实现，结构如下图（粉色区域存放索引数据，白色区域存放下一级磁盘文件地址）：

B-tree 索引（B+ 树实现）的一些特点：

• B+ 树叶子节点之间按索引数据的大小顺序建立了双向链表指针，适合按照范围查找
• 使用 B+ 树非叶子节点只存储索引，在 B 树中，每个节点的索引和数据都在一起，因此使用 B+ 树时，通过一次磁盘 IO 拿到相同大小的存储页，B+ 树可以比 B 树拿到的索引更多，因此减少了磁盘 IO 的次数。
• B+ 树查询性能更稳定，因为数据只保存在叶子节点，每次查询数据，磁盘 IO 的次数是稳定的