MySQL核心知识学习之路（6）
作为一个后端工程师，想必没有人没用过数据库，跟我一起复习一下MySQL吧，本文是我学习《MySQL实战45讲》的总结笔记的第六篇，总结了MySQL的InnoDB引擎相关的实践使用问题，包括MySQL为什么有时候会不稳定的“抖”一下、为何表数据删除了一半但表文件大小没变 和 为何 count(*)会很慢。
作为一个后端工程师，想必没有人没用过数据库，跟我一起复习一下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讲》
