空间问题
在Mysql日常使用中,会出现这样的一个问题,我将数据表的内存删除了一半,但是数据表占用的空间并未发生变化。
数据删除流程
假设,我们要删掉R4这个记录,InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时,可能会复用这个位置。但是,磁盘文件的大小并不会缩小。
现在,你已经知道了InnoDB的数据是按页存储的,那么如果我们删掉了一个数据页上的所有记
录,会怎么样?
答案是,整个数据页就可以被复用了。
数据页的复用跟记录的复用是不同的。
记录的复用,只限于符合范围条件的数据。比如上面的这个例子,R4这条记录被删除后,如果
插入一个ID是400的行,可以直接复用这个空间。但如果插入的是一个ID是800的行,就不能复
用这个位置了。
而当整个页从B+树里面摘掉以后,可以复用到任何位置。以图1为例,如果将数据页page A上的所有记录删除以后,page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候,page A是可以被复用的。
不止是删除数据会造成空洞,插入数据也会
如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。
解决空间问题的方式:重建表
你可以新建一个与表A结构相同的表B,然后按照主键ID递增的顺序,把数据一行一行地从表A
里读出来再插入到表B中。
由于表B是新建的表,所以表A主键索引上的空洞,在表B中就都不存在了。显然地,表B的主键索引更紧凑,数据页的利用率也更高。如果我们把表B作为临时表,数据从表A导入表B的操作完成后,用表B替换A,从效果上看,就起到了收缩表A空间的作用。
MySQL 5.6版本开始引入的Online DDL,对这个操作流程做了优化。
我给你简单描述一下引入了Online DDL之后,重建表的流程:
- 建立一个临时文件,扫描表A主键的所有数据页;
- 用数据页中表A的记录生成B+树,存储到临时文件中;
- 生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中,对应的是图中state2的状态;
- 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件,对应的就是图中state3的状态;
- 用临时文件替换表A的数据文件。
DDL之前是要拿MDL写锁的,这样还能叫Online DDL吗?
流程中,alter语句在启动的时候需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢?为了实现Online,MDL读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做DDL。
count(*)
在不同的MySQL引擎中,count(*)有不同的实现方式。
- MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
- 而InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出
来,然后累积计数。
为什么InnoDB不跟MyISAM一样,也把数字存起来呢?
因为即使是在同一个时刻的多个查询,由于多版本并发控制(MVCC)的原因,InnoDB表“应该返回多少行”也是不确定的。
count()比较
对于count(主键id)来说:Innodb会遍历整张表,取出所有id,server层拿到所有id,进行累加
对于count(1)来说:Innodb会遍历整张表,不取值,server会返回的每行,放一个数字“1”进去,累加。
对于count(字段)来说:Innodb遍历整张表之后,一行行读取该字段的值,判断不为null,进行累加。
**count(*)**是mysql专门做了优化的,不会去每一行取值
运行效率:
count(字段)<count(id)<count(1)约等于count(*)
在高频率数据更新的情况下,我们如何更快的计算出count(*)
-
用缓存系统保存计数
Redis服务来保存这个表的总行数。这个表每被插入一行Redis计数就加1,每被删除一行Redis计数就减1。这种方式下,读和更新操作都很快。
但你再想一下这种方式存在什么问题吗?没错,缓存系统可能会丢失更新。
将计数保存在缓存系统中的方式,还不只是丢失更新的问题。即使Redis正常工作,这个值还是逻辑上不精确的。
你可以设想一下有这么一个页面,要显示操作记录的总数,同时还要显示最近操作的100条记录。那么,这个页面的逻辑就需要先到Redis里面取出计数,再到数据表里面取数据记录。
我们是这么定义不精确的:- 一种是,查到的100行结果里面有最新插入记录,而Redis的计数里还没加1;
- 另一种是,查到的100行结果里没有最新插入的记录,而Redis的计数里已经加了1。
这两种情况,都是逻辑不一致的。
-
在数据库保存计数
新建一个表用来存储count(*)的计数,及每次插入、删除的操作,与redis不同的是,我们可以利用数据库的事务特性:
T3时刻,由于A事务并未提交,因此对于C表的修改对于B事务来说是不可见的,因此保持了两者数据的一致性。