My Github

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  阅读(280)  评论(0)  编辑  收藏  举报