MySQL 全表扫描

淩亂°似流年 2022-12-13 01:55 289阅读 0赞

## Server层 ##

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

## 查询数据 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center]

1.  InnoDB的数据是保存在主键索引上，全表扫描实际上是直接扫描表t的主键索引
2.  获取一行，写到net\_buffer中，默认为16K，控制参数为net\_buffer\_length
3.  重复获取行，直到写满net\_buffer，然后调用网络接口发出去
4.  如果发送成功，就清空net\_buffer，然后继续取下一行并写入net\_buffer
5.  如果发送函数返回EAGAIN或者WSAEWOULDBLOCK，表示本地网络栈socket send buffer写满。**此时，进入等待，直到网络栈重新可写，再继续发送**
6.  一个查询在发送数据的过程中，占用MySQL内部的内存最大为net\_buffer\_length，因此不会达到200G
7.  socket send buffer也不可能达到200G，如果socket send buffer被写满，就会暂停读取数据

-- 16384 Bytes = 16 KB
    mysql> SHOW VARIABLES LIKE '%net_buffer_length%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | net_buffer_length | 16384 |
    +-------------------+-------+

## Sending to client ##

1.  MySQL是边读边发的，如果客户端接收慢，会导致MySQL服务端由于结果发不出去，事务的执行时间变长
2.  下图为MySQL客户端不读取socket receive buffer中的内容的场景
    
    1.  State为Sending to client，表示服务端的网络栈写满了
3.  mysql --quick，会使用mysql\_use\_result方法，该方法会读取一行处理一行
    
    1.  假设每读出一行数据后要处理的逻辑很慢，就会导致客户端要过很久才会去取下一行数据
    2.  这时也会出现State为Sending to client的情况
    3.  对于正常的线上业务，如果单个查询返回的结果不多，推荐使用mysql\_store\_result接口
    4.  适当地调大net\_buffer\_length可能是个更优的解决方案  
        ![在这里插入图片描述][20201009170921287.png_pic_center]

## Sending data ##

### State切换 ###

1.  MySQL的查询语句在进入执行阶段后，首先把State设置为Sending data
2.  然后，发送执行结果的列相关的信息（meta data）给客户端
3.  再继续执行语句的流程，执行完成后，把State设置为空字符串
4.  因此State为Sending data不等同于正在发送数据

### 样例 ###

CREATE TABLE `t` (
        `id` int(11) NOT NULL,
        `c` int(11) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO t VALUES (1,1);

<table> 
 <thead> 
  <tr> 
   <th>session A</th> 
   <th>session B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BEGIN;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SELECT * FROM t WHERE id=1 FOR UPDATE;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>SELECT * FROM t LOCK IN SHARE MODE;(Blocked)</td> 
  </tr> 
 </tbody> 
</table>

## InnoDB层 ##

1.  内存的数据页是在Buffer Pool中管理的
2.  作用：加速更新（WAL机制）+加速查询

### 内存命中率 ###

1.  SHOW ENGINE INNODB STATUS中的Buffer pool hit rate 990 / 1000，表示命中率为99%
2.  Buffer Pool的大小由参数innodb\_buffer\_pool\_size控制，一般设置为物理内存的60%~80%
3.  Buffer Pool一般都会小于磁盘的数据量，InnoDB将采用LRU算法来淘汰数据页

-- 134217728 Bytes = 128 MB
    mysql> SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | innodb_buffer_pool_size | 134217728 |
    +-------------------------+-----------+

### 基本LRU ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 1]

1.  InnoDB采用的LRU算法，是基于链表实现的
2.  State1，链表头部是P1，表示P1是最近刚刚被访问过的数据页
3.  State2，有一个读请求访问P3，P3被移动到链表的最前面
4.  State3，要访问的数据页不在链表中，所以需要在Buffer Pool中新申请一个数据页Px，加到链表头部
    
    1.  但由于Buffer Pool已满，不能再申请新的数据页
    2.  于是会清空链表末尾Pm这个数据页的内存，存入Px的内容，并且放到链表头部

### 冷数据全表扫描 ###

1.  扫描一个200G的表，该表为历史数据表，平时没有什么业务访问它
2.  按照基本LRU算法，就会把当前Buffer Pool里面的数据全部淘汰，存入扫描过程中访问到的数据页
3.  此时，对外提供业务服务的库来说，Buffer Pool的命中率会急剧下降，磁盘压力增加，SQL语句响应变慢
4.  因此InnoDB采用了改进的LRU算法

### 改进LRU ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 2]

1.  在InnoDB的实现上，按照5:3的比例把整个LRU链表分成young区和old区
2.  LRU\_old指向old区的第一个位置，即靠近链表头部的5/8是young区，靠近链表尾部的3/8是old区
3.  State1，要访问数据页P3，由于P3在young区，与基本的LRU算法一样，将其移动到链表头部，变为State2
4.  然后要访问一个不在当前链表的数据页，此时依然要淘汰数据页Pm，但新插入的数据页Px放在LRU\_old
5.  处于old区的数据页，每次被访问的时候都需要做以下判断
    
    1.  如果这个数据页在LRU链表中存在的时间超过了1S，就把它移动到链表头部，否则，位置不变
    2.  存在时间的值由参数innodb\_old\_blocks\_time控制
6.  该策略是为了处理类似全表扫描的操作而定制的
    
    1.  扫描过程中，需要新插入的数据页，都被放到old区
    2.  一个数据页会有多条记录，因此一个数据页会被访问多次。**但由于是顺序扫描，数据页的第一次被访问和最后一次被访问的时间间隔不会超过1S，因此还是会留在old区**
    3.  继续扫描，之前的数据页再也不会被访问到，因此也不会被移到young区，最终很快被淘汰
7.  该策略最大的收益是在扫描大表的过程中，虽然用到了Buffer Pool，但对young区完全没有影响。**保证了Buffer Pool响应正常业务的查询命中率**

-- 1000ms = 1s
    mysql> SHOW VARIABLES LIKE '%innodb_old_blocks_time%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_old_blocks_time | 1000  |
    +------------------------+-------+

### INNODB STATUS ###

mysql> SHOW ENGINE INNODB STATUS\G;
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    -- 137428992 Bytes = 131.0625 MB
    Total large memory allocated 137428992
    Dictionary memory allocated 432277
    -- innodb_buffer_pool_size = 134217728 / 16 / 1024 / 1024 = 8192
    -- 6957 + 1223 = 8180 ≈ Buffer pool size
    Buffer pool size   8191
    Free buffers       6957
    Database pages     1223
    -- 1223 * 3 / 8 = 458.625 ≈ Old database pages
    Old database pages 465
    Modified db pages  0
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    -- made young : old -> young
    -- not young : young -> old
    Pages made young 0, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 1060, created 163, written 666
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 1223, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]

## 参考资料 ##

《MySQL实战45讲》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/d77b9c4f0c6b4ec289050efc6cc0e28b.png
[20201009170921287.png_pic_center]: /images/20221123/40b21534dcb442d8ba3c09449ca8e7a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/2b99711f3f0e4e6aa1dab210915c8937.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/7ce15d2d55604438891746d54f331aa1.png