SYSTEM
内存优化
src/content/notes/system/内存优化.md 内存优化
Change
InnoDB 管理的 Buffer Pool 中有一块内存叫 Change Buffer 用来对增删改操作提供缓存,可以通过参数来动态设置,设置为 50 时表示 Change Buffer 的大小最多占用 Buffer Pool 的 50%
- 唯一索引的更新不能使用 Change Buffer,需要将数据页读入内存,判断没有冲突在写入
- 普通索引可以使用 Change Buffer,直接写入 Buffer 就结束,不用校验唯一性
Change Buffer 并不是数据页,只是对操作的缓存,所以需要将 Change Buffer 中的操作应用到旧数据页,得到新的数据页(脏页)的过程称为 Merge
- 触发时机:访问数据页时会触发 Merge、后台有定时线程进行 Merge、在数据库正常关闭(shutdown)的过程中也会触发
- 工作流程:首先从磁盘读入数据页到内存(因为 Buffer Pool 中不一定存在对应的数据页),从 Change Buffer 中找到对应的操作应用到数据页,得到新的数据页即为脏页,然后写入 redo log,等待刷脏即可
说明:Change Buffer 中的记录,在事务提交时也会写入 redo log,所以是可以保证不丢失的
业务场景:
-
对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 Change Buffer 的使用效果最好,常见的就是账单类、日志类的系统
-
一个业务的更新模式是写入后马上做查询,那么即使满足了条件,将更新先记录在 Change Buffer,但之后由于马上要访问这个数据页,会立即触发 Merge 过程,这样随机访问 IO 的次数不会减少,并且增加了 Change Buffer 的维护代价
补充:Change Buffer 的前身是 Insert Buffer,只能对 Insert 操作优化,后来增加了 Update/Delete 的支持,改为 Change Buffer
Net
Server 层针对优化查询的内存为 Net Buffer,内存的大小是由参数 net_buffer_length定义,默认 16k,实现流程:
- 获取一行数据写入 Net Buffer,重复获取直到 Net Buffer 写满,调用网络接口发出去
- 若发送成功就清空 Net Buffer,然后继续取下一行;若发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,表示本地网络栈
socket send buffer写满了,进入等待,直到网络栈重新可写再继续发送
MySQL 采用的是边读边发的逻辑,因此对于数据量很大的查询来说,不会在 Server 端保存完整的结果集,如果客户端读结果不及时,会堵住 MySQL 的查询过程,但是不会把内存打爆导致 OOM
SHOW PROCESSLIST 获取线程信息后,处于 Sending to client 状态代表服务器端的网络栈写满,等待客户端接收数据
假设有一个业务的逻辑比较复杂,每读一行数据以后要处理很久的逻辑,就会导致客户端要过很久才会去取下一行数据,导致 MySQL 的阻塞,一直处于 Sending to client 的状态
解决方法:如果一个查询的返回结果很是很多,建议使用 mysql_store_result 这个接口,直接把查询结果保存到本地内存
参考文章:https://blog.csdn.net/qq_33589510/article/details/117673449
Read
read_rnd_buffer 是 MySQL 的随机读缓冲区,当按任意顺序读取记录行时将分配一个随机读取缓冲区,进行排序查询时,MySQL 会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,大小是由 read_rnd_buffer_size 参数控制的
Multi-Range Read 优化,将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销,因为大多数的数据都是按照主键递增顺序插入得到,所以按照主键的递增顺序查询的话,对磁盘的读比较接近顺序读,能够提升读性能
二级索引为 a,聚簇索引为 id,优化回表流程:
- 根据索引 a,定位到满足条件的记录,将 id 值放入 read_rnd_buffer 中
- 将 read_rnd_buffer 中的 id 进行递增排序
- 排序后的 id 数组,依次回表到主键 id 索引中查记录,并作为结果返回
说明:如果步骤 1 中 read_rnd_buffer 放满了,就会先执行步骤 2 和 3,然后清空 read_rnd_buffer,之后继续找索引 a 的下个记录
使用 MRR 优化需要设进行设置:
SET optimizer_switch='mrr_cost_based=off'
Key
MyISAM 存储引擎使用 key_buffer 缓存索引块,加速 MyISAM 索引的读写速度。对于 MyISAM 表的数据块没有特别的缓存机制,完全依赖于操作系统的 IO 缓存
-
key_buffer_size:该变量决定 MyISAM 索引块缓存区的大小,直接影响到 MyISAM 表的存取效率
SHOW VARIABLES LIKE 'key_buffer_size'; -- 单位是字节在 MySQL 配置文件中设置该值,建议至少将1/4可用内存分配给 key_buffer_size:
vim /etc/mysql/my.cnf key_buffer_size=1024M -
read_buffer_size:如果需要经常顺序扫描 MyISAM 表,可以通过增大 read_buffer_size 的值来改善性能。但 read_buffer_size 是每个 Session 独占的,如果默认值设置太大,并发环境就会造成内存浪费
-
read_rnd_buffer_size:对于需要做排序的 MyISAM 表的查询,如带有 ORDER BY 子句的语句,适当增加该的值,可以改善此类的 SQL 的性能,但是 read_rnd_buffer_size 是每个 Session 独占的,如果默认值设置太大,就会造成内存浪费