33 查询大量数据,是否会把内存撑爆

如果主机内存只有100g,现在要对一个200g的大表做全部扫描,会不会把数据库主机的内存用光了?

全表扫描对server层的影响

假设,现在要对一个200g的innodb表db1.t33执行全表扫描,当然,要把全部扫描的结果保存在客户端,会用类似这样的命令:

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t33" > $target_file

Innodb的数据保存在主键索引上,所以全表扫描实际上是直接扫描表t33的主键索引,由于这条语句没有其他条件判断,所以查询的每一行都可以直接放在结果集里面,然后返回给客户端。

实际上,服务端并不需要保存一个完整的结果集,取数据和发数据的流程是这样的:

--1 获取一行,写到net_buffer中,这块内存的大小是由参数net_buffer_length,默认16k

--2 重复获取行,直到net_buffer写满,调用网络接口发出去

--3 如果发送成功,就清空net_buffer,然后继续取下一行,并写入net_buffer

--4 如果发送函数返回eagain或者wsaewouldblcok,就表示本地网络栈(socker send
buffer)写满了,进入等待,直到网络栈重新可写,再继续发送

对应流程

从这个流程中,可以看到:

--1 一个查询在发送过程中,占用的mysql内部的内存最大就是net_buffer_length这么大,并不会达到200g

--2 socket send buffer也不能达到200g(默认定义/proc/sys/net/core/wmem_default),如果socket
send buffer被写满,就会暂停读取数据的流程

也就是说,mysql是”边读边发的”,这就意味着,如果客户端接收得慢,就会导致mysql服务器由于结果发不出去,这个事务的执行时间变长。

比如下面这个状态,故意让客户端不去读socket receive buffer中的内容,然后在服务器端show processlist看到结果

如果看到state的值一直是”sending to client”,就表示服务器端的网络栈被写满了。

上一篇提到,如果客户端使用参数-quick,会使用mysql_use_result方法,这个方法是读一行取一行,

对于正常的线上业务来说,如果一个查询的返回结果不会很多的话,都建议使用mysql_store_result这个接口,直接把查询结果保存到本地内存。

当然前提是查询返回结果不多,假如有一个大查询导致客户端内存消耗很高,这种情况就需要改用mysql_use_result接口

另一方面,如果在负责维护的mysql里看到很多个线程都处于”sending to
client”这个状态,就意味着要让业务开发同学优化查询结果,评估这么多的返回结果是否合理。

另外一个状态”sending data”,实际上,一个查询语句的状态变化是这样的:

--mysql查询语句进入执行阶段后,首先把状态设置成”sending data”

--然后,发送执行结果的列相关的信息(meta data)给客户端

--再继续执行语句的流程

--执行完成后,把状态设置为空字符串

也就是说,”sending data”并不一定是指”正在发送数据”,而可能是处于执行器过程中的任意阶段,比如,

SESSION A

SESSION B

begin;

select * from t where id=1 for update;

 

 

select * from t lock in share mode;

(blocked)

 

 

 

可以看到,session B明显是在等锁,状态显示为sending data

也就是说,仅当一个线程处于”等待客户端接收结果”的状态,才会显示”sending to client”,而如果显示成”sending
data”,它的意思是”正在执行”。

现在知道,查询的结果是分段发给客户端的,因此扫描全表,查询返回大量的数据,并不会打爆内存。

全表扫描对innodb的影响

在介绍WAL机制的时候,分析了innodb内存的一个作用,就是保存更新的结果,在配合redo log,就避免了随机写盘。

内存的数据页是在buffer pool中管理的,在WAL里buffer pool起到了加速更新的动作,而实际上,buffer
pool还有一个更重要的作用,就是加速查询。

WAL机制的存在,当事务提交的时候,磁盘上的数据页时旧的,如果这时候马上要读取这个数据页,是不需要读磁盘,直接从内存拿结果。

而buffer pool对查询的加速效果,一个重要的指标:内存命中率

在(system@127.0.0.1:3306) [(none)]> show engine innodb status\G; 中查看结果

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

如果所有需要查询的数据页能够直接从内存中得到,那内存命中率就是100%,但在实际的生成是很难做到的。

Innodb buffer pool的大小由参数innodb_buffer_pool_size控制,一般是实际内存的60%~80%

所以innodb_buffer_pool_size小于磁盘的数量是很常见的,如果buffer_pool满了,而又要从磁盘读入一个数据页,那就会淘汰一个旧的数据页。

Innodb内存管理用的lru法则

 

Innodb管理buffer pool的LRU算法,使用链表来实现的。但是在innodb中,不能直接使用这个lru算法,做了一些改进

在innodb实现上,按照5:3的比例把整个lru链表分成了 young和old两个区,

--1 要访问数据页p3,由于p3在young区域,因此和优化器的lru算法一样,一到链表头部

--2 之后要访问一个新的不存在于当前链表的数据页,这时候依然是淘汰数据页的pm,但是新插入的数据页px,是放在lru_old处

--3 处于old区域的数据页,每次被访问的时候都要做这个判断,

---若这个数据页在lru链表存在的时间超过了1秒,就把它移动到链表头部

---如果这个数据源在lru链表中存在的实际短于1秒,位置保持不变,1秒这个时间有参数innodb_old_blocks_time控制的,

这个策略就是处理类似全表扫描的操作量身定做的。

技术
友情链接
码工具
Toolsou
API参考文档
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信