MySQL表删除数据，索引文件会不会变小？

vlambda
2021-07-01

MySQL表删除数据，索引文件会不会变小？

“
大家好，我是3y。今天给大家分享下MySQL的一些小知识。

“
以前在公司的时候提交申请修改表结构工单执行DDL（比如增加一个列），DBA都会问下表现在的数据量有多少，会不会影响到业务。

原来这跟DDL的原理有关阿（关键字：Online DDL）。

如果我要新增一个列：那需要新增一张表，然后将主表的数据导到新表中，等完成后再rename...如果数据量大，还需要考虑主从延迟的问题。

这篇文章又让我了解到：原来删除数据，表的空间是不会释放的...

一张千万级的数据表，删除了一半的数据，你觉得B+树索引文件会不会变小？

我们先来做个实验，看看表的大小是如何变化的？？

做个实验，让数据说话

1、首先，在mysql中创建一张用户表，表结构如下：

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `age` int(11) NOT NULL  COMMENT '年龄',
  `address` varchar(128) COMMENT '地址',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2、造数据。用户表中批量插入1000W条数据

@GetMapping("/insert_batch")
public Object insertBatch(@RequestParam("batch") int batch) {

    // 设置批次batch=100000，共插入 1000W 条数据
    for (int j = 1; j <= batch; j++) {
        List<User> userList = new ArrayList<>();
        for (int i = 1; i <= 100; i++) {
            User user = User.builder().userName("Tom哥-" + ((j - 1) * 100 + i)).age(29).address("上海").build();
            userList.add(user);
        }
        userMapper.insertBatch(userList);
    }
    return "success";
}

批量插入，每个批次100条记录，100000个批次，共1000W条数据

MySQL表删除数据，索引文件会不会变小？

3、查看表文件大小

MySQL表删除数据，索引文件会不会变小？

索引文件大小约 595 M，最后修改时间 02:17

说明：

MySQL 8.0 版本以前，表结构是存在以 .frm 为后缀的文件里
独享表空间存储方式使用 .ibd 文件来存放数据和索引，且每个表一个 .ibd 文件

表数据既可以存在共享表空间，也可以是单独文件。通过innodb_file_per_table参数控制。MySQL 5.6.6 版本之后，默认是ON，这样，每个 InnoDB 表数据存储在一个以 .ibd为后缀的文件中。

4、删除约500W条数据

@GetMapping("/delete_batch")
public Object deleteBatch(@RequestParam("batch") int batch) {
    for (int j = 1; j <= batch; j++) {
        List<Long> idList = new ArrayList<>();
        for (int i = 1; i <= 100; i += 2) {
            idList.add((long) ((j - 1) * 100 + i));
        }
        userMapper.deleteUser(idList);
    }
    return "success";
}