铁匠 铁匠
首页
收藏
java
架构之路
常用算法
  • Java
  • nginx
  • 系统运维
  • 系统安全
  • mysql
  • redis
参考文档
关于
链接
  • 分类
  • 标签
  • 归档

专注、不予评判地关注当下
首页
收藏
java
架构之路
常用算法
  • Java
  • nginx
  • 系统运维
  • 系统安全
  • mysql
  • redis
参考文档
关于
链接
  • 分类
  • 标签
  • 归档
  • mysql

    • MySQL-InnoDB 存储引擎概述
      • 整体架构
      • 内存结构
        • 缓冲池(Buffer Pool)
        • 写(修改)缓冲(Change Buffer)
        • 自适应哈希索引(Adaptive Hash Index)
        • 日志缓冲(Log Buffer)
      • 磁盘结构
    • MySql 索引
    • MySql 事务
    • MySql 日志系统(WAL)
    • MySql order by 的执行过程与优化
    • MySQL 性能分析与常见性能问题总结
    • mysql推荐使用规范
    • MySQL 常用配置整理
    • mysql常用命令整理
    • mycli-强大的MySQL命令行客户端
  • redis

  • 数据库
  • mysql
fengjx
2020-08-07
目录

MySQL-InnoDB 存储引擎概述

# 整体架构

(图片来自MySQL官网:https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html (opens new window))

# 内存结构

# 缓冲池(Buffer Pool)

Buffer Pool用于缓存数据和索引,减少磁盘访问,提高查询速度。

Buffer Pool是一个链表实现的数据页(默认大小16k),每个数据页可以存多行数据。当需要查询数据时,会先从Buffer Pool中查找,如果没有查到数据,会读取磁盘文件进行查找,然后将查询到的数据写入Buffer Pool。

由于服务器的内存是有限的,当Buffer Pool内存不足时,会通过LRU算法(优化算法)淘汰掉最长时间没有使用的数据。

优化的LRU算法:Buffer Pool划分为young区和old区,young占5 / 8,old占 3 / 8。当内存不足时,会淘汰掉链表末尾的数据,然后将新查询到的数据放在old区域的头部。总体流程如下图。

(图中忽略了 Change Buffer merge 的过程,下面会提到)

监控

SHOW ENGINE INNODB STATUS \G;
1
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

各参数含义,可以查看上面的官方文档,其中一个很重要的指标Buffer pool hit rate是内存命中率,需要特别关注下。如果对性能要求比较高,最好能达到 99%。

Buffer pool的大小通过参数innodb_buffer_pool_size来设置(单位字节),通常可以设置为物理内存的60% ~ 80%。

查询innodb_buffer_pool_size大小

SELECT @@innodb_buffer_pool_size/1024/1024/1024 as 'buffer_pool_size_GB';
1

参考:

  • https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html (opens new window)
  • https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html (opens new window)
  • https://time.geekbang.org/column/article/79407 (opens new window)

# 写(修改)缓冲(Change Buffer)

Change Buffer用于缓存DML(update、insert、delete)操作,减少磁盘IO。会占用 Buffer Pool的一部分空间。

当执行一个DML操作时,会先判断Buffer Pool中是否有对应数据,如果有,则直接更新Buffer Pool中的数据。如果没有,则将这个操作缓存到Change Buffer中。在下一次查询该数据的时候,会从磁盘读取数据(因为之前在Buffer Pool也没有数据),然后把数据页写入Buffer Pool,再将Change Buffermerge到对应数据页(磁盘的数据是旧的,merge后得到正确的值)。除此之外,MySQL 后台线程也会定期 merge(这样可以把多次IO合并成一次,提高性能)。

(便于理解,图中忽略了 BufferPool 内存淘汰策略,可以结合上面的图一起理解)

需要注意的是,如果数据包含唯一索引,是无法使用Change Buffer的,因为唯一索引需要从磁盘读取数据才能判断数据是否唯一。

另外对于写入数据后马上就需要查询的数据页不适合使用Change Buffer,因为在下次读取的时候也是需要从磁盘读取数据(Buffer Pool不存在该数据),然后执行Change Buffermerge。

相关参数

  • innodb_change_buffer_max_size

    占用Buffer Pool多少百分比的空间,默认25,即:占Buffer Pool25%的空间

  • innodb_change_buffering

    哪些操作可以使用Change Buffer,可以设置的值:all, none, inserts, deletes, changes, purges

监控

SHOW ENGINE INNODB STATUS\G
1
-- 相关参数含义查看官方文档
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s
1
2
3
4
5
6
7
8
9
10
11

参考:

  • https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html (opens new window)
  • https://time.geekbang.org/column/article/70848 (opens new window)

# 自适应哈希索引(Adaptive Hash Index)

InnoDB 索引使用的是B+Tree,查询复杂度是 O(logN),为了提高查询效率,MySQL 为热点数据构建了一个哈希索引,提高查询效率。

自适应哈希索引只能用于等值查询。

自适应哈希索引更新需要加锁,在5.7版本之前,只有一个锁来控制,5.7版本之后,使用了分区,类似 java 里面的 ConcurrentHashMap 分段锁,减小了锁的粒度,同分区才会有锁竞争。

我们可以通过监控,观察每个分区等待锁的线程数,如果等待线程过多,反而降低了性能,可以调整分区数量或关闭自适应哈希索引。

相关参数

  • innodb_adaptive_hash_index: 是否开启自适应哈希索引,默认开启

    set global innodb_adaptive_hash_index=off/on

  • innodb_adaptive_hash_index_parts: 分区数量,默认8

参考:

  • https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html (opens new window)

# 日志缓冲(Log Buffer)

Log Buffer 是写入磁盘日志数据的内存缓存区,作用是合并多条记录到一次磁盘IO中,减少IO次数,提高性能。

例如:在一个事务中需要修改多个记录,可以把多个记录的写操作合并在一次磁盘IO中。当然也可以把多个事务的多个记录合并到一次IO(有数据丢失风险,可以通过参数配置)

相关配置项

  • innodb_log_buffer_size缓冲区大小,默认16MB,官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size (opens new window)
  • innodb_flush_log_at_trx_commit日志刷盘机制,默认是1,官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
    • 0: 每秒刷盘,在 mysqld 崩溃的情况下,可能丢失1秒数据
    • 1: 在提交事务时刷盘,ACID事务保障,数据不丢失
    • 2: 每次事务提交时写入OS Cache,并且每隔1秒调用 flush 写入磁盘文件,在机器断电的情况下会丢失1秒数据

参考:

  • https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log-buffer.html (opens new window)

# 磁盘结构

  • 表(Tables)
  • 索引(Indexs)
  • 表空间(Tablespaces)
  • 数据字典(InnoDB Data Dictionary)
  • 双写缓冲(Doublewrite Buffer)
  • 重做日志(Redo Log)
  • 撤销日志(Undo Logs)

https://dev.mysql.com/doc/refman/5.7/en/innodb-on-disk-structures.html (opens new window)

#mysql#InnoDB
MySql 索引

MySql 索引→

最近更新
01
策略模式
01-09
02
模板方法
01-06
03
观察者模式
01-06
更多文章>
Theme by Vdoing | Copyright © 2016-2023 铁匠 | 粤ICP备15021633号
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式