有一个问题,假设我的内存只有20G,我要对一个200G的表进行全表扫描,会不会把主机内存用光了?
这样的一个全表流程,后台内存是怎么样的呢
假设我们,执行一个全表扫描的语句,并保存起来结果集,那么是怎么样的呢
假设一条指令
mysql -h$host -P$port -u$user -p$pwd -e “select * from db1.t” > $target_file
查询一个表,并保存在目标文件
那么这个表的结果集存在了哪里呢
其实,在MySQL中,并不需要完整的保存一个结果集,
在读取过程中
1,首先拿到一行数据,加入net_buffer中,这个内存大小由net_buffer_length
2.重复获取,知道net_buffer写满了
3.发送数据出去,然后清空net_buffer,继续读取写入
4,如果发送函数没有发送出去,EAGAIN或者WSAEWOULDBLOCK,表示网络栈写满了进入等待
可以简化为如下的流程
在这个流程中,可以看出
1.一个查询的过程,一个查询在发送过程中,占用的MySQ内存就是net_buffer那么大
2.socket send buffer,不能达到200G,如果socker send buffer写满了,会暂停读数据的流程
也就是说,MySQL是边读边发的,不可能让结果发不出去
看上面的状态 state
一致处于Sending to client,表示服务器的网络栈已经写满了
如果使用了-quick参数的话,并且处理很慢,客户端很久才回去下一行的话,就会出现如上的场景
所以一般不加-quick参数的原因,并且使用mysql_store_result接口
如果查询的结果很大的话,还会使用mysql_use_result接口了
当然,将net_buffer_length参数设置大一点
当然对于server层,其不会一点点的发出去,而是等待net_buffer_length积累满了再发出去
比起sending to client
在show processlist中还有一种更为常见的status,Sending data
这个sending data的状态表示
一个MySQL查询语句进入了执行阶段后,先将状态设置为了Sending data
然后在发送执行结果的列相关信息给客户端
继续执行语句的流程
执行完成,把状态设为空字符串
那么,这个Sending data,并不是指正在发送数据,而是可能处于执行器的任何阶段,可能是
一个锁等待的场景,也能看到Sending data的状态
对于这样全表扫描的语句,对于server端没有什么特大的影响
主要是对于InnoDB执行引擎层的
之前说WAL机制的时候,说了InnoDB自有的内存中,会有Buffer Pool,Buffer Pool中加速了刷库,同时也加速了查询这个功能
因为内存数据页中数据是最新的,直接读取内存页就可以了,不用去刷库了,但也存在这无法命中的情况
而这个命中是依赖着一个指令 内存命中率
使用show engine innodb status 可以看到Buffer pool hit rate 显示的就是当前的命中率
最好的命中率自然是100%,但是没法真正的做到
Innodb_buffer_pool_size是有上限的,如果需要从磁盘中读入一个数据页,必然要淘汰一个数据页
为了维护命中率,,InnoDB采用了最少使用(LRU)算法,这个算法的核心就是淘汰最久未使用的数据
上面就是基本模型
通过链表来实现的,在上图,链表头部是P1,P1是最新访问过的数据页
然后有一个请求P3,被移到了最前面
然后有一个Px被请求了,于是Pm被淘汰了,Px被移到了最头部
如果简单的按照这个算法,那么如果访问一个200G的表,那么这个表是一个历史表,一般不会访问到
那么,直接傻傻的淘汰的话,读取完成后,BufferPool的内存命中率急剧下降,磁盘压力增加,
InnoDB为了这种情况,不能使用LRU算法,于是进行了改进
按照5:3的比例,将链表分为了young区域和old区域,图中LRU_old指向的就是old区域的第一个位置,
整个执行流程就变成了访问数据页P3
P3在young区域,因此让其移到LRU头部
之后访问一个不存在当前链表的数据页,这时候就淘汰位于old区域的Pm,插入一个Px,放在LRU_OLD区域
如果一个数据页在Old区域,那么被访问的时候做一下判断
如果这个数据页在LRU链表中存在超过了1秒,就移动到头部
如果短于1秒,则位置不变,这个1秒可以设置,通过参数innodb_old_blocks_time控制
再继续扫描后续的数据,这个数据页不会被访问到的话,就很快会淘汰了
本章介绍了MySql的查询流程,因为MySQL采用了边算边发的逻辑,因此不会在server端保存完整的结果集
对于InnoDB引擎内部,采用了改进后的LRU算法,因此对Buffer Pool不会造成太大影响
而且如果客户端的压力过大,导致不能接收到数据,可能导致出现一种长事务一样的影响,也就是如果没有发出去,一直积压在net buffer中
当然,读的事务需要回滚,导致undolog不能回收,会导致回滚端空间膨胀了
在与此同时,其他的语句对这个数据进行更新,发现锁一直锁住了,导致其他的语句被锁住了
发生了socket_timeout超时后业务接口重复发起请求,导致IO资源被占用
导致了异常出现,DBAkill进程也因为回滚时间过长,无法快速恢复
最后因为回滚等其他原因,导致buffer pool block nested-loop多次扫描,从影响了查询命中率
如果对一个数据量非常大的冷数据表进行join查询的话,
会因为扫描这个这个表多次,执行超过了1秒,导致进入了young区域
然后如果很大,可能还会导致业务正常的访问数据页,不能进入young区域,因为这个冷表太大了,会占用old区域的空间,导致其他数据页在old区域就被淘汰了
影响了Buffer Pool的正常运作,这种影响是长时间的,持续的,需要靠后续查询慢慢的恢复回来