作为一个后端工程师,想必没有人没用过数据库,跟我一起复习一下MySQL吧,本文是我学习《MySQL实战45讲》的总结笔记的第六篇,总结了MySQL的InnoDB引擎相关的实践使用问题。
上一篇:MySQL核心知识学习之路(5)
抖即不稳定,一个SQL语句平时速度挺快的,偶尔却会慢一下,称之为“抖”
因为InnoDB引擎在后台刷“脏页”占用了IO资源。
所谓“脏页”,就是当内存数据页跟磁盘数据页内容不一致的时候就是“脏页”。
由于MySQL使用了WAL机制(Write-ahead logging 预写式日志),会将数据库的随机写转化为顺序写,提高读写性能,但副作用就是“脏页”
所谓“干净页”,就是当内存数据写入磁盘后,内存和磁盘上的数据页的内容一致了。
会造成系统不稳定,性能突然下降,对业务端不友好。
(1)设置合理参数配置,如 innodb_io_capacity 设置成磁盘的IOPS值。
平时多关注脏页比例(innodb_max_dirty_pages_pct,默认值75%),不要超过75%
因为delete命令指示将记录的位置 或 数据页 标记为了“可复用”,并没有收回表空间
这些被标记为“可复用”但还未被使用的空间被称之为“空洞”。
使用delete命令删除数据就会产生空洞,被标记为“可复用”。而使用insert命令插入数据可能引起页分裂,也可能产生空洞。
表数据既可以存放在共享表空间里,也可以放在单独的文件(.ibd)中。
由参数 innodb_file_per_table 控制:
重建表,消除因为进行大量的更新操作而产生的空洞。下图是使用alter table t engine=InnoDB重建表的示意图,MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
如何正确重建表:
MySQL 5.6开始引入了Online DDL,支持在重建表过程中对表做更新操作。
其实现原理如下:
适用场景:可以考虑在业务低峰期使用。
在MySQL 5.5及之前版本,这个命令会阻塞DML。
select count(*) from t
MyISAM引擎将表的总行数存在了磁盘上,因此效率很高
InnoDB引擎则每次都需要将数据一行一行地从引擎中读出来进行累积计数,因此存在性能问题。
那么,问题来了:为什么InnoDB不跟MyISAM一样设计?
InnoDB的事务隔离级别默认是可重复读,而MyISAM不支持事务
那么,第二个问题:能用show table status中的TABLE_ROWS代替吗?
不能,因为它是通过采样估算的,存在40%~50%的误差。
方式1:用缓存系统如Redis来保存计数,存在丢失更新、逻辑上不精确的问题,因此不建议使用。
方式2:用数据库表来保存计数,不会丢失更新和不精确,建议使用。
首先,弄清楚的count()的含义。
count():一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加。最后返回累计值。
其次,直接给出结论。
按照效率排序:count(字段)<count(主键 id)<count(1)≈count(*)。
建议尽量使用 count(*),因为MySQL对count(*)专门进行了优化。
本文总结了MySQL的InnoDB引擎相关的实践使用问题,包括MySQL为什么有时候会不稳定的“抖”一下、为何表数据删除了一半但表文件大小没变 和 为何 count(*)会很慢。