-- db1.t有200GB mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
查询数据
- InnoDB的数据是保存在主键索引上,全表扫描实际上是直接扫描表t的主键索引
- 获取一行,写到
net_buffer
中,默认为 16K ,控制参数为net_buffer_length
- 重复获取行,直到 写满
net_buffer
,然后调用网络接口发出去 - 如果发送成功,就 清空
net_buffer
,然后继续取下一行并写入net_buffer
- 如果发送函数返回
EAGAIN
或者WSAEWOULDBLOCK
,表示本地网络栈socket send buffer
写满- 此时,进入等待,直到网络栈重新可写,再继续发送
- 一个查询在发送数据的过程中,占用 MySQL 内部的内存最大为
net_buffer_length
,因此不会达到200G -
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
- MySQL是 边读边发 的,如果 客户端接收慢 ,会导致MySQL服务端由于 结果发不出去 , 事务的执行时间变长
- 下图为MySQL客户端不读取
socket receive buffer
中的内容的场景- State为
Sending to client
,表示服务端的网络栈写满了
- State为
-
mysql --quick
,会使用mysql_use_result
方法,该方法会 读取一行处理一行Sending to client mysql_store_result net_buffer_length
Sending data
State切换
- MySQL的 查询语句 在进入 执行阶段 后,首先把State设置为
Sending data
- 然后,发送执行结果的 列相关的信息 ( meta data )给客户端
- 再继续执行语句的流程,执行完成后,把State设置为 空字符串
- 因此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);
session A | session B |
---|---|
BEGIN; | |
SELECT * FROM t WHERE id=1 FOR UPDATE; | |
SELECT * FROM t LOCK IN SHARE MODE; (Blocked) |
mysql> SHOW PROCESSLIST; +----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 713722 | Waiting on empty queue | NULL | | 37 | root | localhost | test | Sleep | 35 | | NULL | | 38 | root | localhost | test | Query | 15 | Sending data | SELECT * FROM t LOCK IN SHARE MODE | | 39 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+--------+------------------------+------------------------------------+
InnoDB层
- 内存的数据页是在
Buffer Pool
中管理的 - 作用: 加速更新 (WAL机制)+ 加速查询
内存命中率
-
SHOW ENGINE INNODB STATUS
中的Buffer pool hit rate 990 / 1000
,表示命中率为99% -
Buffer Pool
的大小由参数innodb_buffer_pool_size
控制,一般设置为物理内存的60%~80%
-
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
- InnoDB采用的LRU算法,是基于 链表 实现的
- State1,链表头部是P1,表示P1是最近 刚刚被访问过 的数据页
- State2,有一个读请求访问P3,P3被移动到链表的最前面
- State3,要访问的数据页不在链表中,所以需要在
Buffer Pool
中新申请一个数据页Px,加到链表头部Buffer Pool
冷数据全表扫描
- 扫描一个200G的表,该表为历史数据表,平时没有什么业务访问它
- 按照基本LRU算法,就会把当前Buffer Pool里面的数据 全部淘汰 ,存入扫描过程中访问到的数据页
- 此时,对外提供业务服务的库来说, Buffer Pool的命中率会急剧下降 , 磁盘压力增加 , SQL语句响应变慢
- 因此InnoDB采用了改进的LRU算法
改进LRU
- 在InnoDB的实现上,按照
5:3
的比例把整个LRU链表分成young
区和old
区 -
LRU_old
指向old区的第一个位置,即靠近链表头部的5/8
是young
区,靠近链表尾部的3/8
是old
区 - State1,要访问数据页P3,由于P3在young区,与基本的LRU算法一样,将其移动到链表头部,变为State2
- 然后要访问一个不在当前链表的数据页,此时依然要淘汰数据页Pm,但新插入的数据页Px放在
LRU_old
- 处于old区的数据页,每次被访问的时候都需要做以下判断
- 如果这个数据页在LRU链表中 存在的时间 超过了1S,就把它移动到链表头部,否则,位置不变
- 存在时间的值由参数
innodb_old_blocks_time
控制
- 该策略是为了处理类似 全表扫描 的操作而定制的
- 扫描过程中,需要 新插入的数据页 ,都被放到
old
区 - 一个数据页会有多条记录 ,因此 一个数据页会被访问多次
- 但由于是 顺序扫描
- 数据页的 第一次被访问 和 最后一次被访问 的时间间隔不会超过1S,因此还是会留在
old
区
- 继续扫描,之前的数据页再也不会被访问到,因此也不会被移到
young
区, 最终很快被淘汰
- 扫描过程中,需要 新插入的数据页 ,都被放到
- 该策略最大的收益是在扫描大表的过程中,虽然 用到了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讲》
转载请注明出处:http://zhongmingmao.me/2019/03/08/mysql-full-table-scan/
访问原文「MySQL -- 全表扫描」获取最佳阅读体验并参与讨论
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
分布式服务架构:原理、设计与实战
李艳鹏、杨彪 / 电子工业出版社 / 2017-8 / 89.00
《分布式服务架构:原理、设计与实战》全面介绍了分布式服务架构的原理与设计,并结合作者在实施微服务架构过程中的实践经验,总结了保障线上服务健康、可靠的最佳方案,是一本架构级、实战型的重量级著作。 《分布式服务架构:原理、设计与实战》以分布式服务架构的设计与实现为主线,由浅入深地介绍了分布式服务架构的方方面面,主要包括理论和实践两部分。理论上,首先介绍了服务架构的背景,以及从服务化架构到微服务架......一起来看看 《分布式服务架构:原理、设计与实战》 这本书的介绍吧!
UNIX 时间戳转换
UNIX 时间戳转换
HEX HSV 转换工具
HEX HSV 互换工具