更新语句执行流程
update执行流程
update table set age = age + 1 where id = 2;
- 执行器先引擎取出id=2这行数据,若这行数据所在的数据页已经在内存中,直接返回给执行器,否则需要从磁盘中读取内存,然后返回
- 执行器得到这行数据,进行字段的更新操作,得到新的数据行调用执行器引擎接口,写入这行数据
- 执行引擎先更新内存中的数据,同时数据行所在数据页的更改记录到redo log中,此时redo log 处于prepare状态,通知执行器执行完成,随时可以提交事务
- 执行器生成对应的binlog,并把binlog写入磁盘
- 执行器调用引擎的提交事务接口,把redo log的状态修改,commit状态 更新完成
redo log 重做日志 引擎日志
InnoDB先把记录写入到redo log中,并更新内存,系统空闲时,引擎将记录更新到磁盘
数据块发生重启,之前提交的记录不会丢失,成为crash-safe
binlog 归档日志 server层日志
binlog日志为逻辑记录,记录执行了些什么原始操作,而redolog记录的是数据页改变了什么
binlog可以追加写入,而redolog物理日志,空间会用完,所以需要清楚清除数据,同步到磁盘才能写入
索引的更新
change buffer
当需要跟新一个数据页时,如果数据页已经存在于内存中,那么可以直接更新,如果没有在内存中,在不影响一致性的前提下,InnoDB会把更新操作缓冲到change buffer中,暂时先不必读取更新数据页,当下次有需求读取这个数据页的时候,先把数据页读入内存中,然后在数据页上执行merage change buffer的操作,同样保证数据逻辑的准确性
changebuffer在内存中有拷贝,也会被写入磁盘,除了提取数据页时发生merage,系统后台进程也会定期merage,例如数据库支持关闭时
change buffer使用的是buffer pool 里的内存,可以通过innodb_change_buffer_max_size设置百分比
如果更新记录操作可先记录在chanage buffer中,减少磁盘的IO,那么更新语句的执行速度也会得到明显的提升
针对某某表的数据,如果写多读少的业务,页面在写完之后立马被访问的概率比较小,那个change buffer就会起到比较好的效果
唯一索引字段的更新
首先需要读取数据页,用来判定更新或者插入是否存在唯一冲突,那么语句在更新的过程中就不会使用change buffer 因为内存中已经有了对应的数据页
普通索引字段的更新
更新是需要判定内存中是否存在对用的数据页,如果有则直接更新,如果没有则走change buffer的逻辑,这样就提高了语句执行的效率
在选择普通索引和唯一索引时,这种索引在查询性能上相差不大,但在维护性能上,普通索引理论上会更占优势,当是在数据更新之后里面进行查询,这种情况下两个索引的情况差不多
删除数据奥秘
表的数据存储方式
innodb_file_per_table
off: 表的数据使用共享空间,更数据字典放在一起
on: 每个innodb表数据都存放在一个以*.idb为后缀的文件中
数据’空洞’
- 当执行delete语句时,Inndb做标记删除;如果后续插入数据时,定位到这个数据页上有可能继续复用这个位置
- 同理,如果一个数据页上的数据都被删除了, 那么这个数据页也是可以被复用的
- Inndb的标记删除,是不会主动回收表空间的,这些没有被复用的空间就造成了“空洞”
- 插入数据的时候,如果是随机插入,就有可能形成数据页分裂,就会造成数据页的末尾形成空洞
- 更新索引上的值可以理解为删除一个旧值, 插入一个新值。 同样会造成空洞
- 总结: 一个数据表经过大量的增删改后,都是有可能造成空洞的, 通过重建表可进行回收