是实际的开发中,可能因为误操作。可能会执行一个全表扫描的SQL,如果这个表的数据比较大,比如10G,但是数据库内存8G ,会不会将这个数据库内存打爆。带着这个问题,我们来深入学习下。其实主要就是一个server层、以及存储引擎层的影响。
全表扫描对server层的影响
InnoDB的数据是保存在主键索引上的,所以全表扫描其实就是扫描表的主键索引。那么具体的执行流程是什么样?
取数据和发送数据的流程:
1.获取一行,写到net_buffer中,默认16KB,net_buffer_length 进行设置。
2.重复获取行,直到net_buffer写满,调用网络接口发出去。
3.发送成功,将net_buffer 清空,继续读区数据发送。
4.如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,表示本地网络栈(socket send buffer)写满了,进入等待,直到网络栈重新可以写入。继续发送。
show variables like '%net_buffer_length%';
所以可以看到,mysql占用内存的大小也就 net_buffer_length 那么大,网络发送缓存 也不会达到10G。MySQL采用的是边读边发。但是如果客户端接受比较慢的话,这个事务的执行时间就比较长。
show PROCESSLIST;
展示的 Sending to client 表示。等待客户端接受结果。Sending data:正在执行中。
全表扫描对InnoDB的影响
接着来看看,全表查询对innoDB的影响,
内存的数据页是在buffer pool中管理的,在WAL里 buffer pool 起到了加速更新的作用,同时也起到了加速查询。
比如更新了一个SQL,但是由于WAL机制,数据先是写到buffer pool中,然后磁盘的数据还是旧的,如果这个时候来了一个查询,那么就会直接从buffer pool中直接读取数据返回,可以加速读的作用。
可以通过 show engine innodb status
显示当前的命中率。
InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size
确定的,一般建议设置成可用物理内存的 60%~80%。
show variables like '%innodb_buffer_pool_size%';
但是数据是远远超过物理内存的,所以数据就有一定的淘汰算法,LRU。
使用LRU算法,发现其实在正常业务查询,使用没有问题,但是如果突然查询一个大表的操作,那么就会将buffer pool中的数据全部清空,导入的都是大表的数据,这样的话,其实命中率急剧下降。磁盘压力增加,SQL语句响应变慢。
那么InnoDB是如何解决这个问题的?
其实在LRU的基础上进行了优化,也就是将LRU分成young 区域和old区域,3/5进行划分:young、old区域。大概的思想就是,如果访问的数据在LRU链表中,并且youug 区域,那么就移动到head。否则如果不是的话,新增的数据,先到old区域,超过1s在移动到链表头部,小于1S位置不动。
这样就可以很大程度上,将短暂的数据保存在lru Old区域。保证buffer pool响应正常业务的查询命中率。
小结
结论是mysql采用边算边发的策略,查询很大的数据表,不会将内存打爆,而InnoDB引擎内部,有淘汰策略,并且对LRU进行了改进,对Buffer pool做到可控。