MySQL核心知识学习之路(6)

作为一个后端工程师,想必没有人没用过数据库,跟我一起复习一下MySQL吧,本文是我学习《MySQL实战45讲》的总结笔记的第六篇,总结了MySQL的InnoDB引擎相关的实践使用问题。

上一篇:MySQL核心知识学习之路(5)

1 MySQL为什么有时会"抖"一下?

啥是抖?

抖即不稳定,一个SQL语句平时速度挺快的,偶尔却会慢一下,称之为“抖”

为何会抖?

因为InnoDB引擎在后台刷“脏页”占用了IO资源。

所谓“脏页”,就是当内存数据页跟磁盘数据页内容不一致的时候就是“脏页”。

由于MySQL使用了WAL机制(Write-ahead logging 预写式日志),会将数据库的随机写转化为顺序写,提高读写性能,但副作用就是“脏页”

所谓“干净页”,就是当内存数据写入磁盘后,内存和磁盘上的数据页的内容一致了。

抖的影响?

会造成系统不稳定,性能突然下降,对业务端不友好。

如何防抖?

(1)设置合理参数配置,如 innodb_io_capacity 设置成磁盘的IOPS值。

平时多关注脏页比例(innodb_max_dirty_pages_pct,默认值75%),不要超过75%

2 为何表数据删掉一半后表文件大小不变?

表文件不变的原因?

因为delete命令指示将记录的位置 或 数据页 标记为了“可复用”,并没有收回表空间

这些被标记为“可复用”但还未被使用的空间被称之为“空洞”。

 

图片来源:林晓斌《MySQL实战45讲》

使用delete命令删除数据就会产生空洞,被标记为“可复用”。而使用insert命令插入数据可能引起页分裂,也可能产生空洞。

 

图片来源:林晓斌《MySQL实战45讲》

表数据如何存放?

表数据既可以存放在共享表空间里,也可以放在单独的文件(.ibd)中。

由参数 innodb_file_per_table 控制:

  • = OFF,存放在系统共享表空间

  • = ON,存放在单独的.ibd文件中

  • MySQL 5.6.6 开始,默认值就是ON

如何让删除数据后的表文件变小?

重建表,消除因为进行大量的更新操作而产生的空洞。下图是使用alter table t engine=InnoDB重建表的示意图,MySQL 会自动完成转存数据、交换表名、删除旧表的操作。 

图片来源:林晓斌《MySQL实战45讲》

如何正确重建表:

  • alter table t engine=InnoDB

  • optimize table t (等同于 recreate + analyze)

  • truncate table t (等同于 drop + create)

Online DDL

MySQL 5.6开始引入了Online DDL,支持在重建表过程中对表做更新操作。

 

图片来源:林晓斌《MySQL实战45讲》

其实现原理如下:

  • 在生成临时文件过程中,通过row log记录更新操作,然后再应用到临时文件。

  • 最后用临时文件整体替换表的数据文件。

  • alter语句在启动时获取MDL写锁,但在真正拷贝数据之前退化为MDL读锁,而MDL读锁不会阻塞更新操作。

适用场景:可以考虑在业务低峰期使用

在MySQL 5.5及之前版本,这个命令会阻塞DML。

3 count(*)为何这么慢?

不同引擎对于count(*)的实现方式

select count(*) from t

MyISAM引擎将表的总行数存在了磁盘上,因此效率很高

InnoDB引擎则每次都需要将数据一行一行地从引擎中读出来进行累积计数,因此存在性能问题

那么,问题来了:为什么InnoDB不跟MyISAM一样设计?

InnoDB的事务隔离级别默认是可重复读,而MyISAM不支持事务

那么,第二个问题:能用show table status中的TABLE_ROWS代替吗?

不能,因为它是通过采样估算的,存在40%~50%的误差。

自己计数的实现方法

方式1:用缓存系统如Redis来保存计数,存在丢失更新、逻辑上不精确的问题,因此不建议使用。

方式2:用数据库表来保存计数,不会丢失更新和不精确,建议使用

不同count()用法对比

首先,弄清楚的count()的含义。

count():一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加。最后返回累计值。

其次,直接给出结论。

按照效率排序:count(字段)<count(主键 id)<count(1)≈count(*)。

建议尽量使用 count(*),因为MySQL对count(*)专门进行了优化

4 小结

本文总结了MySQL的InnoDB引擎相关的实践使用问题,包括MySQL为什么有时候会不稳定的“抖”一下、为何表数据删除了一半但表文件大小没变 和 为何 count(*)会很慢。

参考资料

林晓斌(丁奇),《MySQL实战45讲》

👇扫码订阅《MySQL实战45讲》

 

posted @ 2021-03-10 10:23  EdisonZhou  阅读(119)  评论(0编辑  收藏  举报