21-4-27_innodb内幕

2.4 Checkpoint技术

Write Ahead Log 策略：

​ 事务提交时，先做redo日志，再修改页，当宕机数据丢失则通过redo日志来完成数据恢复

Cheackpoint解决以下几个问题：

  • 缩短数据库恢复时间
  • 缓冲池内存不够用时将脏页刷新到磁盘
  • redo日志不可用时刷新脏页

数据库宕机时不需要重做所有redo日志，因为checkponit之前的页已经刷新

两种checkponit：

  • Sharp Checkpoint：数据库关闭时将所有脏页刷新回磁盘，默认工作方式，参数innodb_fast_shutdown = 1

    mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.02 sec)

    有时也使用，但可用性受影响

  • Fuzzy Checkpoint：InnoDB内部使用，只刷新一部分脏页回磁盘

    • Master Thread Cheackpoint：每秒或者每十秒刷新一定比例，异步，InnoDB其他操作不会阻塞

    • FLUSH_LRU_LIST Cheackpont：保证LRU列表中有100个空闲页，多余的会被移除，如果有脏页则进行FLUSH_LRU_LIST Cheackpont，新版本已经由Page Cleaner进行，且LRU列表可用页数量默认为1024

      mysql> show variables like 'innodb_lru_scan_depth';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_lru_scan_depth | 1024  |
+-----------------------+-------+
1 row in set (0.03 sec)

    • Async/Sync Flush Checkpoint：redo日志不可用的情况，需要强制将一些页刷新回磁盘，现在同样由Page Cleaner负责

    • Dirty Page too much：顾名思义，脏页数量太多导致强制Cheackpoint，保证缓冲池中有足够的页

      mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 90.000000 |
+----------------------------+-----------+
1 row in set, 1 warning (0.00 sec)

      当脏页数量占据innodb_max_dirty_pages_pct时，强制Checkpoint，刷新一部分

2.5 Master Thread工作方式

2.5.1 InnoDB 1.0.x 版本之前的Master Thread

最高线程优先级别，内部多个循环：主循环（loop）、后台循环（backgroup loop）、刷新循环（flush loop）、暂停循环（suspend loop）

Master Thread会根据数据库运行状态在这些循环内切换

1 Loop

大多数操作发生在Loop里，主要有两大部分操作，每秒钟和每十秒的操作（有延迟，大概频率）

每秒一次的操作：

  • 日志缓冲刷新到磁盘，即使事务未提交（总是）

    所以再大的事务提交需要的时间也是很短的

  • 合并插入缓冲（可能）

    前一秒的IO次数小于5次，可认为压力小，库执行操作

  • 最多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）

    超过innodb_max_dirty_pages_pct参数（默认90%）则需要磁盘同步操作

  • 没有用户活动则切换到background loop

每十秒的操作：

  • 刷新100个脏页（可能）

    IO操作小于200次

  • 合并至多5个插入缓冲（总是）

  • 将日志缓冲刷新

  • 删除无用undo日志

    full purge操作，删除undo日志（undo日志维护update、delete操作等的一致性读MVVC），最多尝试20个undo页

  • 刷新100个或者10脏页

    ≥ 70% --> 100

    ＜ 70% --> 10

2 background loop

数据库空闲或者数据库关闭，切换到此线程

  • 删除无用的undo页（总是）
  • 合并20个插入缓冲（总是）
  • 回到主循环（总是）
  • 不断刷新100个页直到符合条件（可能，跳转到flush loop完成）

若flush loop也没有什么事情可以做，会切换到suspend_loop，将Master Thread挂起

若是使用innodb引擎，但没有使用innodb的表，则Master Thread总是挂起状态

2.5.2 InnoDB 1.2.x 版本之前的Master Thread

mysql> show variables like 'innodb_io_capacity';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_io_capacity | 200   |
+--------------------+-------+
1 row in set, 1 warning (0.84 sec)

磁盘吞吐量，默认200

  • 在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity的5%
  • 从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity

innodb_adaptive_flushing：自适应刷新

通过一个buf_flush_get_desired_flush_rate函数判断最合适数量，通过产生redo日志的速度来决定，所以比例小于innodb_max_dirty_pages_pct也会刷新

innodb_purge_batch_size可控制full purge回收undo页的数量

mysql> show variables like 'innodb_purge_batch_size';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_purge_batch_size | 300   |
+-------------------------+-------+
1 row in set, 1 warning (0.00 sec)

2.5.3 InnoDB 1.2.x 版本的Master Thread

对于刷新脏页的操作，从Master Thread移到了单独的Page Cleaner Thread

2.6 InnoDB关键特性

  • 插入缓冲（Insert Buffer）
  • 两次写（Double Write）
  • 自适应哈希索引（Adaptive Hash Index）
  • 异步IO（Async IO）
  • 刷新邻接页（Flush Neihbor Page）

2.6.1 插入缓冲

1 Insert Buffer

主键一般是auto_increment属性，在插入时顺序插入，不需要磁盘的随机读取，速度非常快

对主键是UUID这样的类，则和辅助索引（二级索引）都是随机的，即使主键是自增的，但插入的是指定的值，可能导致非连续的情况

大部分非聚集索引叶子节点的插入不是顺序的，需要离散地访问非聚集索引页（B+树特性）

引入了Insert Buffer：对于非聚集索引的插入或更新操作，不是每一次直接插入索引页，而是判断是否在索引池中，是则直接插入，否则则放入一个Insert Buffer对象，“欺骗”数据库已经插入了，再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时是将多个插入合并到一个操作，提高性能

需要满足

  • 是辅助索引（二级索引）
  • 不是唯一索引（unique）：唯一的话需要去查找来验证是否唯一，离散读取，耗费性能

问题：

​ 在写密集情况下，插入缓存会占用过多的缓冲池内存，默认最大是1 / 2

2 Change Buffer

InnoDB对INSERT、DELETE、UPDATE都进行缓存，为Insert Buffer 、Delete Buffer、Purge Buffer

同理，适用于非唯一的辅助索引

对一条记录的进行update操作可能分为两个过程

  • 将记录标记为已删除
  • 将记录真正删除

Delete Buffer对应第一个过程，Purge Buffer对应第二个过程

innodb_change_buffering参数来开启各种Buffer选项，inserts、purges、changes、all、none

默认为all

mysql> show variables like 'innodb_change_buffering';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set, 1 warning (0.00 sec)

innodb_change_buffer_max_size参数来控制Change Buffer最大内存数量

mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

默认为25，最大有效值为50

3 Insert Buffer内部实现

全局B+树，存放在共享表空间，ibdata1

叶节点和非叶节点组成

非叶节点中的search key：

space（表所在的表空间，4字节，获得哪张表 marker（兼容老版本的InsertBuffer） offset（页所在的偏移量）

9字节

当一个辅助索引要插入到页（space,offset），如果页不在缓冲池内，那么构造一个search key，再查询这棵Insert Buffer的B+树，再插入叶子节点

插入时需要进行构造：

space marker offset metadata secondary index record

前三个如上所述，而metadata占4个字节，内容如下：

名称 字节
IBUF_REC_OFFSET_COUNT 2
IBUF_REC_OFFSET_TYPE 1
IBUF_REC_OFFSET_FALGS 1
  • IBUF_REC_OFFSET_COUNT：记录进入Insert Buffer的顺序

Insert Buffer B+ 树叶子节点的第五列开始就是实际插入记录的各个字段

Insert Buffer Bitmap页来标记每个辅助索引页（space，page_no）的可用空间

​ 可追踪16384个辅助索引页，即256个区（64个页一个区）

Insert Buffer Bitmap页在16384个页的第二页中

每个辅助索引页在Insert Buffer Bitmap中存储的信息（4bit）

每个辅助索引页在Insert Buffer Bitmap中存储的信息

4 Merge Insert Buffer

发生在以下几种情况：

  • 辅助索引页被读取到缓冲池

    执行正常的select操作，需要先检查Insert Buffer Bitmap页，确认其辅助索引页是否有记录存在在Insert Buffer B+树中，有则需要将该页的记录插入到辅助索引页中

  • Insert Buffer Bitmap页追踪到该辅助索引页没有空间

    Insert Buffer Bitmap页追踪到该辅助索引页的可用空间，并至少有 1 / 32页的空间。若插入辅助索引页记录时检测到插入记录后可用空间会小于 1 / 32页时则强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B+树中该页的记录及待插入记录插入到辅助索引页

  • Master Thread

    Master Thread线程每秒或者每十秒执行一个Merge Insert Buffer操作

    辅助索引页根据(space, offset)已经排序，但InnoDB选择随机选择Insert Buffer B+树的一个页，读取页中的space及之后所需要数量的页，该算法已更高的公平性

    进行merge时，要进行merge的表已经被删删除，此时可以直接丢弃Insert/Change Buffer的记录

    2.6.2 两次写

部分写失效：当数据库宕机时，可能InnoDB存储引擎正在写入某个页到表，而这个页只写了一部分，如16KB只写了4KB

doublewrite：在应用redo日志重做之前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做。

doublewrite架构

组成

  • 内存中的doublewrite buffer，1MB * 2 = 2MB
  • 磁盘上共享表空间的连续128个页，两个区，128 * 16KB = 2MB

过程：

​ 通过memcpy函数将脏页复制到内存的doublewrite buffer中，之后Buffer再通过两次，每次1MB顺序写入共享表空间的磁盘，再调用fsync函数同步磁盘，避免缓冲写带来的问题，因为doublewrite是连续的，过程是顺序的，开销不大，最后将doublewrite buffer的页写入各个表空间文件

如果操作系统在将页写入时发生了崩溃，在恢复过程中，innodb可以从共享表空间的doublewrite找到该页的一个副本，将其复制到表空间文件，再应用redo日志。

两次写防止的是写入磁盘时脏页还没有完全写入时系统崩溃导致页面损坏，redo日志防止的是脏页在缓存中还没有写入准备系统崩溃导致数据丢失，一个注重的是在写入磁盘过程，一个是还在缓存中。

2.6.3 自适应哈希索引

哈希（hash）查找时间复杂度O(1)，一次查询

B+树查找取决树的高度，一般3~4层，需要3~4次查询

InnoDB会监控对表上各索引页的查询，如果观察到建立哈希索引可以提升速度，则建立哈希索引，此过程称为自适应哈希索引AHI，不为整张表构建哈希索引，根据访问频率和模式来自动地为某些热点页建立哈希索引

AHI要求：

  • 对这个页的连续访问模式是一样，指查询条件是一样的

  • 以该模式访问了100次（应该是指此次查询对所有页访问了100次以上）

  • 页通过该模式访问了N次，N = 页中记录 / 16

读写速度可以提高2倍，辅助索引的连接性能可以提高5倍

哈希索引只能来搜索等值的查询

innodb_adaptive_hash_index来控制是否开启，默认开

mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set, 1 warning (0.00 sec)

2.6.4 异步IO

提高磁盘操作性能，操作系统、innodb使用异步IO（Asynchronous IO, AIO）

Sync IO：每进行一次IO操作，需要等待操作才能继续操作

例如索引扫描：没必要等一个IO请求完成再发出另一个IO，可以在发出一共IO操作后立即发出另外一个IO请求，等所有请求发送后等待所有请求的完成，AIO

AIO优点：

​ IO Merge操作，例如访问（space, page_no）有（8，6），（8，7），（8，8）

同步IO需要进行3次IO操作，而异步IO会判断这三个页是连续的，直接发送一个IO请求，从（8，6）开始，读取48KB（16KB * 3）的页

mysql> show variables like 'innodb_use_native_aio';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_use_native_aio | ON    |+-----------------------+-------+1 row in set, 1 warning (0.00 sec)

需要操作系统提供支持

2.6.5 刷新邻接页

Flush Neighbor Page，刷新邻接页

原理：

​ 当刷新原理时，innodb会检测该页的区中的所有页，如果是脏页，则一起刷新

好处：

​ 通过AIO将多个IO操作合并

innodb_flush_neighbors来控制，建议机械硬盘开启1，固态硬盘关闭0

2.7 启动、关闭与恢复

innodb的启动与关闭指MySQL实例的启动过程中对innodb存储引擎的处理过程

innodb_fast_shutdown

  • 0：数据库关闭时，完成所有的full purge和merge insert Buffer，且将脏页刷新回磁盘，需要的时间长，如果升级InnoDB需要设为0，再关闭数据库
  • 1：默认值，不需要完成full purge和merge insert Buffer，但缓冲池的脏页回刷回磁盘
  • 2：所有操作都不，但会将日志都写日志文件，当数据库启动时进行恢复操作

恢复：当不正常关闭数据库，如kill命令，运行中重启服务器，innodb_fast_shutdown参数设为2时在启动时进行恢复操作

innodb_force_recovery默认0，当需要恢复时恢复，当不能恢复时（数据页发生corruption）时，数据库可能宕机，把错误写入错误日志

某些情况不需要进行完整的恢复操作，如用户自行操作

innodb_force_recovery可设置的值

  • 1：忽略检查到的corrupt页

  • 2：阻止Master Thread的运行，当Master Thread要进行full pureg时会宕机

  • 3：不进行事务的回滚操作

  • 4：不进行插入缓冲的合并操作

  • 5：不查看undo日志，innodb会将未提交事务视为提交

  • 6：不进行前滚的操作

回滚：

未提交，撤销已写入到数据库的脏块

前滚：

未提交，将在内存里的脏页写入磁盘

参数 > 0后只允许对表select、create、drop 操作，insert、update、delete不允许

第三章 文件

3.1 参数文件

寻找文件位置：

mysql --helpgrep my.cnf

没有参数文件可以装载，通过编译MySQL时指定的默认值和源码中参数的默认值

MySQL实例在默认的数据库目录下找不到mysql架构会启动失败：

mysql架构记录了访问该实例的权限

参数文件是以文本方式存储的

3.1.1 什么是参数

数据库参数可以看成键值对（key/value）

show variables\G查看所有参数

3.1.2 参数类型

  • 动态参数
  • 静态参数

动态参数可以在MySQL实例运行中修改，反之不行

SET 命令可以对动态参数修改

SET语法

global指整个实例的生命周期都会生效，不会对参数文件产生修改

session指当前会话

3.2 日志文件

常见日志文件：

  • 错误文件error log
  • 二进制文件binlog
  • 慢查询文件slow query log
  • 查询日志log

3.2.1 错误日志

对MySQL的启动、运行、关闭进行了记录

定位错误日志：

mysql> show variables like 'log_error';+---------------+------------------------------------------------------------+| Variable_name | Value                                                      |+---------------+------------------------------------------------------------+| log_error     | D:\mysql-8.0.19-winx64\mysql-8.0.19-winx64\data\Zephxu.err |+---------------+------------------------------------------------------------+1 row in set, 1 warning (0.00 sec)

3.2.2 慢查询日志

在MySQL启动时设置一个阈值，将运行时间超过该值的的所有SQL语句都记录到慢查询日志文件中，long_query_time，默认为10，代表10秒

mysql> show variables like 'long_query_time';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set, 1 warning (0.00 sec)

默认MySQL数据库不启动慢查询日志

slow_query_log控制打开

mysql> show variables like 'slow_query_log'\G*************************** 1. row ***************************Variable_name: slow_query_log        Value: OFF1 row in set, 1 warning (0.00 sec)mysql> set global slow_query_log='ON';Query OK, 0 rows affected (0.37 sec)mysql> show variables like 'slow_query_log'\G*************************** 1. row ***************************Variable_name: slow_query_log        Value: ON1 row in set, 1 warning (0.00 sec)mysql> show variables like 'slow_query_log_file';+---------------------+-----------------------------------------------------------------+| Variable_name       | Value                                                           |+---------------------+-----------------------------------------------------------------+| slow_query_log_file | D:\mysql-8.0.19-winx64\mysql-8.0.19-winx64\data\Zephxu-slow.log |+---------------------+-----------------------------------------------------------------+1 row in set, 1 warning (0.00 sec)

大于long_query_time阈值才会记录（等于不算）

log_queries_not_using_indexes开启后，没有使用索引的语句会被记录

mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF   |+-------------------------------+-------+1 row in set, 1 warning (0.00 sec)mysql> set global log_queries_not_using_indexes='ON';Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | ON    |+-------------------------------+-------+1 row in set, 1 warning (0.00 sec)

log_throttle_queries_not_using_indexes表示每分钟允许记录到slow log且没有使用索引的sql语句的次数，默认0，没有限制，生产环境会加大负担

mysql> show variables like 'log_throttle_queries_not_using_indexes';+----------------------------------------+-------+| Variable_name                          | Value |+----------------------------------------+-------+| log_throttle_queries_not_using_indexes | 0     |+----------------------------------------+-------+1 row in set, 1 warning (0.00 sec)

mysql.slow_log表会记录慢查询的语句

对slow_log一些操作：

mysql> show create table mysql.slow_log\G*************************** 1. row ***************************       Table: slow_logCreate Table: CREATE TABLE `slow_log` (  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),  `user_host` mediumtext NOT NULL,  `query_time` time(6) NOT NULL,  `lock_time` time(6) NOT NULL,  `rows_sent` int NOT NULL,  `rows_examined` int NOT NULL,  `db` varchar(512) NOT NULL,  `last_insert_id` int NOT NULL,  `insert_id` int NOT NULL,  `server_id` int unsigned NOT NULL,  `sql_text` mediumblob NOT NULL,  `thread_id` bigint unsigned NOT NULL) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'1 row in set (0.00 sec)mysql> show variables like 'log_output'\G*************************** 1. row ***************************Variable_name: log_output        Value: FILE1 row in set, 1 warning (0.00 sec)mysql> set global log_output='TABLE';Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'log_output'\G*************************** 1. row ***************************Variable_name: log_output        Value: TABLE1 row in set, 1 warning (0.00 sec)mysql> select sleep(10) \G*************************** 1. row ***************************sleep(10): 01 row in set (10.10 sec)mysql> select * from mysql.slow_log\G*************************** 1. row ***************************    start_time: 2021-04-27 21:04:16.201645     user_host: root[root] @ localhost [::1]    query_time: 00:00:10.100127     lock_time: 00:00:00.000000     rows_sent: 1 rows_examined: 1            db:last_insert_id: 0     insert_id: 0     server_id: 1      sql_text: 0x73656C65637420736C65657028313029     thread_id: 10*************************** 2. row ***************************    start_time: 2021-04-27 21:04:48.045583     user_host: root[root] @ localhost [::1]    query_time: 00:00:00.000224     lock_time: 00:00:00.000104     rows_sent: 1 rows_examined: 1            db:last_insert_id: 0     insert_id: 0     server_id: 1      sql_text: 0x73656C656374202A2066726F6D206D7973716C2E736C6F775F6C6F67     thread_id: 102 rows in set (0.00 sec)

3.2.3 查询日志

记录了所有对数据库请求的信息，无论是否有正确执行

3.2.4 二进制日志

binary log记录了对MySQL数据库执行更改的所有操作，不包含select和show操作

作用：

  • 恢复：某些数据的恢复需要二进制日志
  • 复制：原理与恢复相似，通过复制、执行二进制日志使远程计算机的MySQL数据库（slave或standby）与一台MySQL数据库（master或primary）同步
  • 审计：判断是否有对数据库进行注入攻击

默认没有启动，手动指定参数启动，对性能一点点影响

  • max_binlog_size

单个文件最大值，超过则产生新文件，后缀名+1，记录到.index文件，默认为1G

  • binlog_cache_size

当事务未提交时二进制文件被记录到一个缓存，提交后写入二进制文件，缓存大小由此参数决定，默认32KB

基于会话的，每开启一个会话则分配一个binlog_cache_size大小缓存

  • sync_binlog

    二进制文件同缓存中的脏页，系统崩溃则写入失败，给恢复和复制带来麻烦，参数如下

    • 1：同步
    • 0：默认

  • binlog-do-db

  • binlog-ignore-db

需要写入和不需要，默认空，同步所有

  • log-slave-update

slave角色不会将master的二进制文件写入自己的文件去，如果需要则需要设置此参数

如果要设置 master -> slave -> slave 架构则需要

  • binlog_format

    • statement：日志的逻辑SQL语句

    • row：记录表的行更改，若为row可将Innodb事务隔离级别设为read committed来获得并发性

    • mixed

      在这格式下使用statement

      一些情况使用row，表引擎ndb,uuid()等不确定函数，insert delay不确定语句，用户定义函数，临时表使用row

binlog_format对引擎的限制

binlog_format对引擎的限制

动态参数，可以在运行更改

row有更大可靠性，但二进制文件大小增加快

3.3 套接字文件

套接字文件由参数socket控制，一般在/tmp目录，mysqk.sock

3.4 pid文件

MySQL实例启动，会将自己进程ID写入pid文件，由参数pid_file控制，默认数据库目录下

mysql> show variables like 'pid_file';+---------------+------------------------------------------------------------+| Variable_name | Value                                                      |+---------------+------------------------------------------------------------+| pid_file      | D:\mysql-8.0.19-winx64\mysql-8.0.19-winx64\data\Zephxu.pid |+---------------+------------------------------------------------------------+1 row in set, 1 warning (0.00 sec)

3.5 表结构定义文件

无论什么引擎，都有一个frm文件，定义了表结构，还用来存放视图，文本文件

posted @ 2021-04-27 22:08  zephxu  阅读(89)  评论(0)    收藏  举报