MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

13 | 为什么表数据删掉一半,表文件大小不变?

林晓斌 2018-12-12
经常会有同学来问我,我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?
那么今天,我就和你聊聊数据库表的空间回收,看看如何解决这个问题。
这里,我们还是针对 MySQL 中应用最广泛的 InnoDB 引擎展开讨论。一个 InnoDB 表包含两部分,即:表结构定义和数据。在 MySQL 8.0 版本以前,表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。
接下来,我会先和你说明为什么简单地删除表数据达不到表空间回收的效果,然后再和你介绍正确回收空间的方法。

参数 innodb_file_per_table

表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的:
这个参数设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
这个参数设置为 ON 表示的是,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
从 MySQL 5.6.6 版本开始,它的默认值就是 ON 了。
我建议你不论使用 MySQL 的哪个版本,都将这个值设置为 ON。因为,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(122)

  • undifined 置顶
    老师,有几个问题:
    1.Truncate 会释放表空间吗
    2.重建表的时候如果没有数据更新,有没有可能产生页分裂和空洞
    3.页分裂是发生在索引还是数据上
    4.应用 row log 的过程会不会再次产生页分裂和空洞
    5.不影响增删改,就是 Online;相对 Server层没有新建临时表,就是 inplace,这里怎么判断是不是相对 Server 层没有新建临时表
    辛苦老师解答一下,谢谢老师

    作者回复: 1. Truncate 可以理解为drop+create
    2. Online 可以认为没有
    3. 数据也是索引哦
    4. 可能会
    5. 好问题,我放到明天答疑部分

    2018-12-12
    38
  • 陈飞 置顶
    老师,请问下分布式ID(雪花算法生成的ID)生成的索引会比自增长的ID性能低吗?

    雪花算法生成的ID是越来越大的,但不是逐渐递增,长度用的的bitint,麻烦解答下,非常感谢。

    作者回复: 好问题。


    性能一样的,没有一定要“连续”,只要是递增

    2018-12-13
    25
  • 发条橙子 。 置顶
    老师 , 我的确实是 5.7 版本。我今天看了些关于 online ddl的文章 ,再结合表锁那章评论的内容,有几个点还想确认一下 ,希望老师能解答一下 。

    1. 是不是 5.6 之后 所有的 alter 操作(增删字段、增删索引等)都是支持 online ddl

    2. 如果 alter 都是 online ddl 那么是不是如果 alter操作 获取到mdl写锁 时, 后面的 查询需要mdl读锁会暂时阻塞, 但是mdl会马上降为读锁 ,后面的操作会继续进行不会堵塞 。等再升到写锁 ,后面操作又会暂时阻塞。

    3. 当 alter 降到mdl 读锁时 , 这时候可以新增数据么 , mdl表级读锁 不会影响到 insert 或者 update的行锁么

    4. 如果将 alter 操作显式的放到事务里 ,事务不提交 , 另一个事务查询的时候会查询到alter 操作后的表结构 , 比如新增了一个字段。这个是什么原因 ,是否打破了 mvcc 的定义呢

    作者回复: 1. 不是哦,我文章里说的加全文索引就不online

    2. 对,这两个暂时,都是时间很短的

    3. 是,DML语句加的是MDL读锁,读读不冲突

    4. 好问题 , 不过alter table 语句会默认提交前面的事务,然后自己独立执行😄

    2018-12-13
    1
    13
  • 置顶
    麻烦咨询个问题,“在图 3 中,我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表,是在 server 层创建的。”
    在server层创建的表也是将A表数据copy到了临时表,为什么在空间不够用时就没有问题,而inplace在InnoDB执行则会再占用一份存储?

    作者回复: 额,

    Copy的时候肯定更要的…

    这里特别指出来,是因为大多数人很容易理解copy需要临时空间,但是误以为inplace不需要


    Anyway,好问题 😄

    2018-12-13
    5
  • 飞翔
    我想到的其中一种可能:
    本来就很紧凑,没能整出多少剩余空间。
    重新收缩的过程中,页会按90%满的比例来重新整理页数据(10%留给UPDATE使用),
    未整理之前页已经占用90%以上,收缩之后,文件就反而变大了。

    作者回复: 一个漂亮的答案

    2018-12-12
    81
  • Mr.Panda
    很喜欢作者的MySQL,绝对干货哈哈。
    这里针对空洞提下问题:
    1.删除有空洞,是因为标记了已删除可复用的节点位置,不会释放。
    2.随机插入有空洞,是因为数据页分裂造成。
    3.但更新有空洞,有点费解,我个人理解是更新是结合删除和插入的一个合并操作。删除后产生的空洞,在插入时不是应该就马上被复用了吗,毕竟主键是一致的。所以为什么更新会产生空洞呢??

    作者回复: 3. 可以这么想下,如果1,2,3,4,5
    然后update把2 改成6, 如果原地修改,这个索引就不是“有序”的了

    2019-01-29
    2
    23
  • 帆帆帆帆帆帆帆帆
    @undifined怎么判断是不是相对 Server 层没有新建临时表。一个最直观的判断方法是看命令执行后影响的行数,没有新建临时表的话新建的行数是0。

    作者回复: 👍🏿,下一篇答疑直接贴你答案😄

    2018-12-12
    19
  • godtrue
    1:为啥删除了表的一半数8据,表文文件大小没变化?

    因为delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。也可以认为是一种逻辑删除,所以物理空间没有实际释放,只是标记为可复用,表文件的大小当然是不变的啦!

    2:表的数据信息存在哪里?

    表数据信息可能较小也可能巨大无比,她可以存储在共享表空间里,也可以单独存储在一个以.ibd为后缀的文件里,由参数innodb_file_per_table来控制,老师建议总是作为一个单独的文件来存储,这样非常容易管理,并且在不需要的时候,使用drop table命令也能直接把对应的文件删除,如果存储在共享空间之中即使表删除了空间也不会释放。

    3:表的结构信息存在哪里?

    首先,表结构定义占有的存储空间比较小,在MySQL8.0之前,表结构的定义信息存在以.frm为后缀的文件里,在MySQL8.0之后,则允许把表结构的定义信息存在系统数据表之中。

    系统数据表,主要用于存储MySQL的系统数据,比如:数据字典、undo log(默认)等文件

    4:如何才能删除表数据后,表文件大小就变小?

    重建表,消除表因为进行大量的增删改操作而产生的空洞,使用如下命令:

    1:alter table t engine=InnoDB

    2:optimize table t( 等于 recreate+analyze)。

    3:truntace table t (等于drop+create)

    5:空洞是啥?咋产生的?

    空洞就是那些被标记可复用但是还没被使用的存储空间。

    使用delete命令删除数据会产生空洞,标记为可复用

    插入新的数据可能引起页分裂,也可能产生空洞

    修改操作,有时是一种先删后插的动作也可能产生空洞
    2019-07-01
    10
  • 某、人
    通过第10期的课后思考题学习到如果delete的数据还会被用于MVCC,那么该数据页(二级索引和聚簇索引)上的记录不会被物理删除,是被标记删除。只有当该事务不会用于mvcc了,才可以被purge线程把之前标记删除的数据真正删除掉.但是即便数据物理删除了,磁盘空间也不会返回给操作系统.可以通过show table status like 't';观察data_free来预估该表的碎片。如果过大,可以用alter table t engine=innodb来清除
    我有几个问题请教下老师:
    1.inplace相对于其他在线改表软件,多了MDL X锁.既然都是通过临时表/文件来做,为什么一开始要上MDL X锁?
    2.gh-ost使用binlog来做同步,假设从position 1开始,先lock S前面1000条数据做cp,这时有事务对后面1000条数据做了修改或者插入。等cp后面这个1000条时,会把修改好的数据cp到临时表.最后又应用binlog,那么这相当于做了两次操作,请问这部分数据是怎么处理的?
    3.online会把过程中对表的操作记录在一个(row log)中,那么中途这些DML事务,是怎么判定的commit?我做测试,中途这些事务都是成功的。但是有在做online DDL快完了,commit那个阶段,DDL报唯一键冲突,这又是什么原因造成的啊?我没有模拟出来这个例子

    作者回复: 1. 确保没有启发请求在用这个表

    2. Binlog设置为row格式,幂等的

    3. 这个我觉得其实是bug,就没提。你在DDL期间,往原表插入一个已经存在相同主键的一行试试

    2018-12-13
    1
    9
  • null
    临时表插入数据时,不允许表 A 有增删改操作,否则会造成数据丢失。所以表数据 copy 的方式不是 online 的。
    而 inplace 的方式,在构建临时文件时,允许表 A 有增删改操作,期间新的增删改操作会记录到另外的日志文件,表 A 数据页的所有数据复制完成后,再应用日志文件(自己理解:应用日志文件时,不允许对表 A 增删改操作,即非 online 的)。整体操作是 online 的。
    切换表名或临时文件时,需要获取 MDL 写锁。
    inplace 表示在 innodb 引擎完成所有操作,对 server 层是透明的。inplace 操作不一定是 online 的,如 MySQL 8.0 的添加全文索引和空间索引。而 online 的一定是 inplace 的。

    作者回复: 👍

    2019-02-10
    8
  • wang chen wen
    optimize table t 等于 recreate+analyze
    老师请教个问题recreate出来应该是几乎全新的,analyze的必要性?

    作者回复: 好问题,这个得是比较极端的情况下才有必要,所以我比较喜欢直接用alter

    2018-12-12
    8
  • 阿杜
    我以前负责的一个系统就出现过这种情况,突然有个表的sql执行很慢,后来觉得是此表增删的数据很多,碎片很多,就执行了optimize table,立马就好了。看来就是老师文中说的,删除和增加都是空洞造成。

    作者回复: 👍,找到正主了😆

    2019-03-01
    6
  • 库淘淘
    alter table A engine=InnoDB 中由写锁降为读锁。有个疑问 为何不直接就加个MDL读锁 ,这样DDL 也执行不了,应用redo 替换文件后释放读锁即可

    作者回复: 如果有别的线程正在读这个表的数据,得等下

    2018-12-13
    1
    6
  • 毛毛
    可能性有两个。
    1. 生成临时表过程中,row log中新增的数据较多,导致表的行数变多。
    2. 生成临时表过程中,row log中有部分插入和删除操作会产生“空洞”。
    个人感觉,第一种情况,突然多了高于1%的数据,比较少见;但第二种情况,在某些业务中,经常出现。
    2018-12-12
    5
  • 尘封
    老师,请教一个问题,和本文无关,既然mysql支持了打包数据排序模式,能够更紧凑的分配内存进行排序,那定义表结构的时候,varchar(10)存储hello和varchar(100)存储hello的优势在哪里呢?谢谢

    作者回复: 你这个问题放在第16篇问就刚刚好了。

    以前不支持紧凑排序的时候有,现在没啥了差别了,小于256都差不多

    2018-12-12
    4
  • Boh
    老师,有个问题请教下,对于主键自增的表,每次delete后都会产生空洞,这个空洞由于主键递增的关系是不会被复用的,累计一定时间后,会发现空洞占用空间非常大,需要执行alter table t engine = InnoDB
    收缩下表,对于这种情况的表,有没有什么好的方案让它自动收缩而非等到存储空间告警了再处理
    2019-07-18
    2
  • GLADIATOR
    老师,online DDL有一个问题:因为row log的存在,解决了 DDL过程中的增量dml数据,最后应用日志后,再把temp文件替换表A的文件。但是如果替换的时候,必须等到没有增量数据了吗?如果这个过程中一直有dml,是不是就一直完成不了这个替换操作?
    2019-03-19
    2
  • 还一棵树
    每一篇+评率 都认真看完,受益不少。 老师啥时间有空了 讲解下double write,之前的文档里提到过,但是没搞懂。 现在要是有人问我 什么是mysql的 double write 及有啥作用 感觉还回答不上来,求老师指点

    作者回复: 后面有一章会讲到的,赞认真哦

    2018-12-13
    2
  • 天王
    delete删除表数据,只是打上一个可复用标记,如果是数据页上一部分数据打上标记,如果按照自增主键insert数据,那表空间的数据不会复用,如果是整个数据页的所有数据打上标记,那么可以复用。2个3相临的数据页,如果空洞太多,合并成一个页,另外一个可以标记复用。重建表可以减少空洞,文件大小可以减小,本质上是通过,创建临时文件,将数据在临时文件上,重建一份,重建过程,按照顺序插入,极大减少了黑洞,数据都拷贝到临时文件以后,会有删除原来文件,切换到新文件。文件会减小了。


    作者回复: 👍🏿

    2018-12-12
    2
  • insist
    老师,咨询一个问题,我生产服务器有一个表有千万条数据,如果需要加一个普通索引,非全文索引,会导致对这个表的增删该查都阻塞吗?(mysql版本5.7)
    2019-09-13
    1
收起评论
99+
返回
顶部