本文围绕一个问题展开: 假如主机内存只有 100G,现在要对一个 200G 的大表做全表扫描,会不会把数据库主机的内存用光了?
假如有如下全表扫描:
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_fileInnoDB 的数据是保存在主键索引上的,所以全表扫描实际上是直接扫描表 t 的主键索引。 这条查询语句由于没有其他的判断条件,所以查到的每一行都可以直接放到结果集里面,然后返回给客户端。
取数据和发数据的流程:
流程如下:

这个流程中:
net_buffer_length 的最大值是 1G,这个值比 socket send buffer大(一般是几M)
也就是说,MySQL是“边读边发”的,如果客户端接收得慢,或者socket_receive_buffer ,堵住了,会导致客户端的结果发不出去,这个事务的执行时间就会变长。
如下是客户端不去读 socket receive buffer 中的内容,然后在服务端 show processlist 看到的结果:

如果你看到 State 的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。
对于正常的线上业务来说,如果一个查询的返回结果不会很多的话,我都建议你使用 mysql_store_result 这个接口,直接把查询结果保存到本地内存。
MySQL 里看到很多个线程都处于“Sending to client”这个状态,可以这样处理:
与“Sending to client”长相很类似的一个状态是“Sending data”。
一个查询语句的状态变化:
也就是说,“Sending data”并不一定是指“正在发送数据”,而可能是处于执行器过程中的任意阶段。比如,一个锁等待的场景,就能看到 Sending data 状态。
比如:


我在mysql5.5版本看到的状态是这样的:

state的值是statistics,不知道是不是一样的。
总结: 就是说,仅当一个线程处于“等待客户端接收结果”的状态,才会显示"Sending to client";而如果显示成“Sending data”,它的意思只是“正在执行”。
InnoDB 内存的一个作用,是保存更新的结果,再配合 redo log,就避免了随机写盘。
内存的数据页是在 Buffer Pool (BP) 中管理的,在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上,Buffer Pool 还有一个更重要的作用,就是加速查询。
如果此时有一个查询,可以直接在内存里拿结果,不用读磁盘,所以buffer Pool 还有加速查询的作用,加速查询的一个重要指标就是:内存命中率。
show engine innodb statusInnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的,一般建议设置成可用物理内存的 60%~80%。
如果一个 Buffer Pool 满了,而又要从磁盘读入一个数据页,那肯定是要淘汰一个旧数据页的,有如下两种算法:
这个算法的核心就是淘汰最久未使用的数据。

InnoDB 管理 Buffer Pool 的 LRU 算法,是用链表来实现的。
缺点: 假设按照这个算法,我们要扫描一个 200G 的表,而这个表是一个历史数据表,平时没有业务访问它。那么,按照这个算法扫描的话,就会把当前的 Buffer Pool 里的数据全部淘汰掉,存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。
此时Buffer Pool 的内存命中率急剧下降,磁盘压力增加,SQL 语句响应变慢。

在 InnoDB 实现上,按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置,是整个链表的 5/8 处。也就是说,靠近链表头部的 5/8 是 young 区域,靠近链表尾部的 3/8 是 old 区域。
所以整个流程简单为:不在链表数据页,先放在old处,如果在old处呆的超过1秒了,移到young这里的头部,如果数据一直在young处,移到young的前面。
针对全表扫描的200G数据,
小结: 由于 MySQL 采用的是边算边发的逻辑,因此对于数据量很大的查询结果来说,不会在 server 端保存完整的结果集。所以,如果客户端读结果不及时,会堵住 MySQL 的查询过程,但是不会把内存打爆。
疑问: