Mysql全表扫描时对内存的使用

全表扫描时数据流向：

data--->net_buffer--->socket_send_buffer--->socket_receive_buffer--->client

其中：
1.net_buffer的大小由参数net_buffer_length决定，每个session都有自己的net_buffer
net_buffer_length：
Dynamic Yes、DEFAULT VALUE 16384、Scope Global, Session、Minimum Value 1024、Maximum Value 1048576
Each client thread is associated with a connection buffer(socket_send_buffer) and result buffer(net_buffer). Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.
This variable should not normally be changed, but if you have very little memory, you can set it to the expected length of statements sent by clients. 
If statements exceed this length, the connection buffer is automatically enlarged. The maximum value to which net_buffer_length can be set is 1MB.

max_allowed_packet：
Dynamic Yes、DEFAULT VALUE 4194304、Scope Global, Session、Minimum Value 1024、Maximum Value 1073741824
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB.
The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.
You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

2.服务端会将data逐行往net_buffer里写，直到net_buffer写满，然后调用网络接口发出到socket_send_buffer(默认定义 /proc/sys/net/core/wmem_default)
3.如果发送成功，就清空 net_buffer，然后继续取下一行，并写入net_buffer
4.如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示socket_send_buffer写满了，那么写入就进入等待，直到socket_send_buffer重新可写，再继续发送

所以说Mysql在执行全表扫描时，是边读边发的，查询结果集不会一股脑的都塞到内存里，所以大家也不用担心什么 ”服务器就100G内存，我要对200G的表做全表扫表，服务器会不会崩啊“