1。删除不是真正的删除。熟悉MySQLInnoDB存储引擎的同学应该都知道，当我们执行delete的时候，实际上并没有删除数据，只是开启了数据对应的deleteMark。第一次执行查询时，如果发现数据存在但启用了deleteMark，还是会返回empty。因为这个细节，经常出现“我明明删除了数据，为什么空间还没有释放”的现象。15M7618:46user_info.ibd#删除前15M10416:47user_info.ibd#删除后2.为什么不直接删除，而是标记一下我们知道InnoDB存储引擎是支持MVCC的，也就是多版本控制，得益于MVCC，MySQL在事务中查询数据时不需要加锁，可以提供很好的并发性，同时提供可重复读这个非常重要的特性。那么它是如何到达那里的呢？答案就是undolog，可以简单理解为，每次更新数据，先将更新前的数据写入undolog，这样当需要回滚的时候，只需要按照undolog日志查找历史数据。undolog和原始数据通过指针链接，即每条数据都有一个指向undolog的回滚指针。如果InnoDB在删除数据的时候真的把数据从磁盘上抹掉了，那么这个时候：其他事务是无法通过undolog找到原来的数据的。可重复读取功能将被破坏。3.直接标出来不是很浪费空间吗？MySQL中有一个清除线程。它的任务之一是使用deleteMark检查数据。如果带有deleteMark的数据没有被其他事务引用，那么就会被标记为可重用，因为叶子节点数据是有序的，这样下次插入相同位置的数据时，可以直接重用这个磁盘空间。当整个页面可以重复使用时，不会退回，保留可重复使用的页面。下次需要新的页面时，可以直接使用，减少频繁的页面申请。4、基于页面的存储方式我们知道MySQL的数据是存储在磁盘上的，磁盘的速度大家一定知道，尤其是发生随机IO的时候。这里简单解释一下IO是什么。以机械盘为例，我们最终的数据落在磁盘的各个扇区上。当一个扇区已满时，我们必须更换下一个扇区。这时，通过盘片的转动找到目标扇区，这就是物理运动。如果要写入的下一个扇区紧挨着当前扇区，这称为顺序IO。如果要写入的扇区和当前扇区之间有多个扇区，这称为随机IO。显然随机IO需要更长的时间来旋转。所以在查询一条数据的时候，减少IO是非常关键的，尤其是随机IO。为了减少磁盘IO，MySQL采用了B+树的索引结构来组织数据。B+树的特点就是矮胖。一般来说，树的高度代表IO的数量。树越短，树的高度越低，对应的IO次数就越少，另外要知道的是，数据最终都在叶子节点上，所以在B+树上查找的时候，一定要检索到最后一层叶子节点，这是稳定性的体现。1.行和页这里我们需要知道的是，我们最终通过B+树检索到的并不是我们的目标行数据，而是目标行数据所在的页。这个页面有很多数据，都是用索引号相邻的。当找到目标页面时，会将目标页面加载到内存中，然后通过二分法找到目标数据。你可能会问，查找的开销不仅仅是磁盘IO，还有二分查找的开销。这里不可否认，但是我们一般忽略这部分开销，因为CPU在内存中检索的速度很快，一页只有16k，数据不多。2.IO的数量不一定等于树的高度。我们说过树的高度等于IO的个数。这实际上不是很准确。我们知道树的根节点肯定在内存中，所以对于一个高度为3的数据，只需要2次IO，其实也可以理解。毕竟根节点只占用一页空间，一页也只有16K，放在内存里绰绰有余。但有时树的第二层也可以放入内存中。假设现在主键是bigint，我们知道bigint占用8个字节。对于索引来说，除了类型本身占用的空间外，还有一个指针，占用6个字节。对于根节点来说，大概可以存储16K/(8+6)B=1170个数据，每个数据可以指向一页（也就是它的下一层），所以整棵树的第二层大约占1170*16K=18M空间，这也不是一个很大的数字。对于机器的内存来说，几乎是沧海一粟，所以第二层往往也在内存中，所以在B+树上检索数据消耗的IO应该低于理论值。由上可知，检索一条数据的速度主要受树高的影响。这与数据表的大小无关。现实中，当数据表达量达到百万级别时，可能有人会考虑拆分表，我个人觉得这有点低估了B+树的能力。还是以bigint类型的主键索引为例，假设一行数据占用1K（理论上足够），那么一页可以存储16K/1K=16条数据。对于一棵高度为3的B+树，它可以存储1170*1170*16=21902400条数据，接近2000万条。如果你的数据行占用的空间少，就可以存储更多的数据，所以简单的根据数据行数来判断是否需要分表就不是那么合理了。5.可重用空间没有使用怎么办？前面我们提到，删除的数据并不会真正删除，而是会打上一个deleteMark，然后重新使用。但如果一直没有被重复利用，那么这个空间就只是白白浪费了。更糟糕的是，如果大量删除的数据空间没有被重新使用，会导致页面空间产生大量碎片。为了解决这种情况，MySQL中有一个叫做pagemerge的功能。这是什么意思？简单理解就是页面A现在有很多空间可以复用，它的邻居页面B也有很多空间可以复用。这时候就可以将A页面和B页面进行合并，如果合并可以省掉一个页面出来，那么下次就可以使用多出的页面，从而达到页面使用最大化的效果。合并的关键是要求当前页的上一页或后一页也有大量的碎片空间。这里“量大”的原因是关键。合并动作可以简单理解为移动其他页面的数据。如果两个页面pageA、pageB和pageB都只有少量的可复用空间，那么合并后，即使pageA可以填满，另一个页面Page还有碎片空间，而且碎片更大。这时，数据移动的开销可能大于存储的开销，得不偿失。而且会出现一个严重的问题，pageB可能会和pageC合并，那么pageC的碎片会更大……这样的话，就好像是一个无底洞，导致很多page移动数据。因此，合理的合并条件至关重要。InnoDB中合并时受参数MERGE_THRESHOLD影响，其默认值为50%。50%的意图很明显。两个50%可以保存一个页面。让我们看一个例子。pageA中50%的数据已被删除，而它的邻居pageB只使用了不到50%的数据。此时pageB中的数据会被移动到pageA中，所以整个pageB是一个空页。它可以被其他数据使用。这里需要知道的是，除了删除会触发页面合并，更新也可能会触发页面合并。6.有合并和拆分。合并页面是提高页面利用率的一种方式，但是有时候我们不得不拆分页面。我们知道叶子节点的页面是用一个双向链表串联起来的，页面之间链接的数据是有序的。以上图为例。当我们要插入5条数据时，我们应该尝试将它们放在pageA中，但是pageA目前没有足够的空间来存储一条数据，所以我们试图找到pageA的相邻页pageB，但是不幸的是在这个time的问题是pageB没有足够的空间来存储一条数据。由于数据连续性的要求，数据5必须在数据4和数据6之间，所以只能新建一个页面。创建新页面后，它会尝试把pageA中的一部分数据转移到新的页面中，页面之间的关系会重新组织，即在pageA和pageB之间隔出一个新的页面pageC。页面拆分会降低页面的利用率。分页的原因有很多，比如离散插入，导致数据不连续。将记录更新为更大的记录，导致空间不足。另外要知道的是，无论是页面合并还是页面拆分，都是一个比较耗时的操作。除了移动数据的开销之外，InnoDB还会锁定索引树。7.手动重建表页面的合并和拆分主要发生在插入、删除或更新时，并且在满足一定条件时发生。如果不满足这些条件，则无法清理碎片。这时候经常会出现针对“我的??表数据不多，怎么还占那么多空间”的现象，有人说重建索引。这是对的。重建索引可以让数据更紧凑，页面利用率更高。但是如何重建索引呢？第一次可能会想到先dropindex，再addindex。这似乎不是那么准确。如果要重建的索引是一个普通的索引，用这个方法是可以的。需要注意的是，如果你的业务TPS很大，建议在业务的非高峰期执行，因为MySQL虽然支持在线ddl，但是重建索引的过程还是很快的。消耗cpu和io资源。如果要重建主键索引，那么问题来了，首先，如果你的主键索引设置为自增长，是不支持drop的。其次，如果你的主键没有设置为自增长，直接drop也不是我们想象的那样。我们知道，普通索引除了记录本身的索引字段外，还会记录主键的值。如果drop是直接删除索引，那么通过普通索引是找不到对应行记录的，所以InnoDB需要主键索引。这时候InnoDB会尝试在表中寻找一个唯一索引作为主键。如果没有唯一索引，会自动创建一个默认的主键索引rowid，当新的主键索引建立时，需要修改相关的普通索引来存储新的主键，但是如果在这个修改方式，开销会很大，尤其是普通索引比较多的时候，所以InnoDB干脆选择重建表。下一次添加索引操作，主键索引也会发生变化，所以表也会重建，最后可以发现主键索引上的drop和add其实是做同样的事情。总结一下，一般当你的表有很多页碎片的时候，推荐使用：altertablexxengine=InnoDB这个命令可以重建我们的表，但是前提是我们的表是独占表空间。基于MySQL的在线ddl，该过程不影响正常读写。它的过程是这样的：扫描原表主键索引的所有记录，生成新的b+树记录，存入临时文件。在生成临时文件的过程中，新的变化在临时文件生成后，将行日志中的变化应用到新的临时文件中，然后用当前文件替换临时文件。这里需要注意的是，重建表的过程涉及复制数据。确保磁盘有足够的空间，至少是当前磁盘空间的两倍，如果磁盘空间不足，重建将不会成功。8、重建表不一定缩小空间。在重建表的过程中，需要知道一件事：InnoDB不会用数据填充重建的页面，而是预留“1/16”的空间。这个意图很明显。如果不保留，选择填满整个页面。这时，要更新一个需要更多空间的旧数据，就需要一个新的页面。写入新页后，往往会造成碎片，所以最好提前预留一点空间。有用。但是由于这种保留操作，在某些情况下，重建的表空间反而会变大。如果你的桌子本身很紧凑，它会因为预留1/16而变大。第一次重建表后，由于有新的插入，使用了部分保留空间（这里需要注意的是，保留空间并没有用完，还有一部分），但是没有使用新的页，所以整体空间没有变化。这时候如果重新建表，会保留1/16，导致申请新的page，空间会变大。
