我查这么多数据,会不会把数据库内存打爆 有更新!

Published on with 715 views

    一、全表扫描对server的影响

    Innodb的数据是保存在主键索引上,所以全盘扫描实际上是直接扫描表t的主键索引,查到的每一行都可以放到结果集里面,然后返回给客户端。但实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程如下:

    1.获取一行,写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的,默认是16K。

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

    3.如果发送成功,就清空net_buffer,然后读取下一行,并写入net_buffer。

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

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

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

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

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

    如果在show processlist看到的结果中State值一直处于"Sending to client",就表示服务器端的网络栈写满了。

    如果客户端使用-quick参数,会使用mysql_use_result方法,这个方法是读一行处理一行。假设一个业务的逻辑比较复杂,每读一行数据以后要处理的逻辑如果很慢,就会导致客户端要过很久才去读下一行数据,可能就会出现"Sending to client"这种情况。,

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

    如果要进行一个大查询导致占用内存近20G,该如何处理?

    1.可以考虑改用mysql_use_result接口。

    2.优化查询结果并评估是否合理。

    如果想快速减少处于"Sending to client"的线程的话,可以考虑更改net_buffer_length参数为一个很大的值(最大1G)。

    与"Sending to client"很像的一个State是"Sending data","Sending data"并不一定是指“正在发送数据”,而可能是处于执行器过程中的任意阶段。

    二、全表扫描对Innodb的影响

    内存的数据页是在Buffer Pool中管理的,在WAL里Buffer Pool起到了加速更新的作用。而实际上Buffer Pool还有一个重要的作用就是加速查询,它依赖于一个重要指标,即:内存命中率。可以在show engine innodb status结果中,查看一个系统当前的BP命中率。一般情况下,一个稳定服务的线上系统,要保证响应时间符合要求的话,内存命中率要在99%以上。

    innoDB内存管理用的是LRU算法,在innoDB实现上,按照5:3的比例把整个LRU链表分成了young区域和old区域,靠近链表头部的5/8是young区域,靠近链表尾部的3/8是old区域,在young区域访问会将其移动到链表头部,当要访问一个新的不存在于当前链表的数据页,这时候依然是淘汰尾部数据页,但是新插入的数据页是放在LRU_old处,若这个数据页在LRU链表中存在的时间超过了1秒,就把他移动到链表头部,如果这个数据页在LRU链表中存在的时间短于1秒,位置保持不变。

    在扫描大表的过程中虽然也用到了Buffer Pool,但是对young区域完全没有影响,从而保证了Buffer Pool相应正常业务的查询命中率。

    Responses