昨天把一个数据表的字段从普通索引,修改成为了唯一索引。准备早早下班的时候,突然发现数据库的内存命中率从98%下降到了48%,导致大量的任务处于阻塞状态,整个系统也阻塞了。
其实这个就是普通索引和唯一索引使用不当引起的原因。下面听我分析。
先来介绍一个知识点change buffer
数据库运行更新语句的时候,在更新一个数据页时,如果此数据页没有在内存中,为了不需要从磁盘中读取整个数据页的数据,不影响数据一致性的条件下,InnoDB引擎会将更新操作直接缓存在 change buffer上面。下次查询需要访问这个数据页数据的话,会将数据页读入内存然后结合change buffer 更新数据,从而保证数据正确性。
虽然名字叫作 change buffer,但是实际上也是可以持久化的数据。change buffer 在内存中有拷贝,也会被写入到磁盘上。
当数据库需要更新一条记录时,这个记录要更新的目标页不在内存中。这时,InnoDB 的处理流程如下:
- 唯一索引,需要将数据页读入内存,判断到没有冲突,插入这个值,执行语句
- 普通索引,则是将更新记录在 change buffer,执行语句
数据从磁盘读入内存涉及随机 IO 的访问,这个是数据库里面成本最高的操作之一。因为change buffer 减少了随机磁盘访问,所以对更新性能的提升是会很明显的。
所以我通常情况下推荐大家使用普通索引替代唯一索引。但是这个需要业务程序来保证数据插入都是唯一的。否则若业务程序无法保证数据唯一的话,那只能强行使用唯一索引了。切记。
最近我会一直总结mysql的相关知识。欢迎关注 点赞。