MySQL-小章节-InnoDB存储引擎核心特性-参数补充

最新推荐文章于 2023-10-12 12:53:43 发布

CN-FuWei

最新推荐文章于 2023-10-12 12:53:43 发布

阅读量270

点赞数

分类专栏： # MySQL 文章标签： mysql 数据库 dba

本文链接：https://blog.csdn.net/zfw_666666/article/details/124843041

版权

MySQL 专栏收录该内容

31 篇文章 5 订阅

订阅专栏

12.1 存储引擎相关

12.1.1 查看

show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;

12.1.2 如何指定和修改存储引擎

(1) 通过参数设置默认引擎

(2) 建表的时候进行设置

(3) alter table t1 engine=innodb;

12.2. 表空间

12.2.1 共享表空间

innodb_data_file_path

一般是在初始化数据之前就设置好

例子:innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

12.2.2 独立表空间

show variables like 'innodb_file_per_table';

12.2.3 DWB（Double Write Buffer）

作用

MySQL，最小IO单元page（16KB），OS中最小的IO单元是block（4KB）

DWB由128个页构成，容量只有2M。

为了防止出现以下问题：

mysqld process crash in the middle of a page write

执行过程：

第一步，页数据memcopy到DWB的内存，速度很快；

第二步，DWB的内存fsync刷到DWB的磁盘，属于顺序追加写，速度也很快；

第三步，刷磁盘，随机写，本来就需要进行，不属于额外操作；

另外，128页（每页16K）2M的DWB，会分两次刷入磁盘，每次最多64页，即1M的数据，执行也是非常之快的。

MySQL有很强的数据安全性机制：

在异常崩溃时，如果不出现“页数据损坏”，能够通过redo恢复数据；

在出现“页数据损坏”时，能够通过double write buffer恢复页数据；

double write buffer：

不是一个内存buffer，是一个内存/磁盘两层的结构，是InnoDB里On-Disk架构里很重要的一部分；

是一个通过写两次，保证页完整性的机制；

画外音：传统的buffer，大部分是内存存储；而DWB里的数据，是需要落地的。

12.2.4 ib_buffer_pool

作用：缓冲和缓存，用来做“热”（经常查询或修改）数据页，减少物理IO。

当关闭数据库的时候，缓冲和缓存会失效。

5.7版本中，MySQL正常关闭时，会将内存的热数据存放（流方式）至ib_buffer_pool。下次重启直接读取ib_buffer_pool加载到内存中。

mysql> select @@innodb_buffer_pool_dump_at_shutdown;

mysql> select @@innodb_buffer_pool_load_at_startup;

12.3. 缓冲区池

12.3.1 内存

1. InnoDB BUFFER POOL（IBP）

作用：

用来缓冲、缓存，MySQL的数据页和索引页。MySQL中最大的、最重要的内存区域。

管理：

查询：mysql> select @@innodb_buffer_pool_size;

    默认大小： 128M

生产建议：物理内存的：50-80%。

在线设置： mysql> set global innodb_buffer_pool_size=268435456;

    重新登录mysql生效。

永久设置：

vim /etc/my.cnf

#添加参数

innodb_buffer_pool_size=256M

    重启生效

①共享内存缓冲区：buffer_pool

缓冲区池: select @@innodb_buffer_pool_size;

②会话内存缓冲区

Join_buffer_size

Key_buffer_size

Read_buffer_size

Read_rnd_buffer_size

Sort_buffer_size

2. InnoDB LOG BUFFER (ILB)

作用：用来缓冲 redo log日志信息。

管理：

  查询： mysql> select @@innodb_log_buffer_size;

      默认大小：16M

  生产建议：和innodb_log_file_size有关，1-N倍

      设置方式：

  vim /etc/my.cnf

  innodb_log_buffer_size=33554432

      重启生效：

  [root@db01 data]# /etc/init.d/mysqld restart

12.4. innodb_flush_log_at_trx_commit (双一标准之一)

12.4.1 作用

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

1:在每次事务提交时，会立即刷新redo buffer到磁盘，commit才能成功

0：每次刷新redo buffer到os cache 再fsync()到磁盘，异常宕机时，会有可能丢失1s内的事务

2：每次事务提交，都会立即刷新redo buffer到os cache 再每秒fsync()到磁盘，异常宕机时，会有可能丢失1s内的事务

目前默认的是1

另外，redo buffer还和操作系统缓存机制有关，所以刷新策略可能和innodb_flush_method参数有一定的关系

Redo也有group commit,可以理解为，在每次刷新已提交的redo时，顺便可以将一些未提交的事务redo也一次性刷新的磁盘，

此时，为了区分不同状态的redo，会加一些特殊的状态（是否commit标记）

---------------------------------------------------------

sync_binlog

sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

当sync_binlog =N (N>0) ，MySQL 在每写 N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

12.4.2 查询

select @@innodb_flush_log_at_trx_commit;

12.4.3 参数说明

1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush  到操作系统的文件系统缓存  fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

12.5. Innodb_flush_method=(O_DIRECT, fdatasync)

12.5.1 作用

控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存

12.5.2 查看

show variables like '%innodb_flush%';

12.5.3 参数值说明

O_DIRECT :数据缓冲区写磁盘,不走OS buffer

fsync :日志和数据缓冲区写磁盘,都走OS buffer

O_DSYNC :日志缓冲区写磁盘,不走 OS buffer

12.5.4 使用建议

最高安全模式

innodb_flush_log_at_trx_commit=1

Innodb_flush_method=O_DIRECT

最高性能:

innodb_flush_log_at_trx_commit=0

Innodb_flush_method=fsync

12.6. redo日志有关的参数

innodb_log_buffer_size=16777216

innodb_log_file_size=50331648

innodb_log_files_in_group = 3

13. 扩展(自己扩展，建议是官方文档)

RR模式(对索引进行删除时):

GAP: 间隙锁

next-lock: 下一键锁定

例子:

id（有索引）

1 2 3 4 5 6

GAP：

在对3这个值做变更时，会产生两种锁，一种是本行的行级锁，另一种会在2和4索引键上进行枷锁

next-lock：

对第六行变更时，一种是本行的行级锁，在索引末尾键进行加锁，6以后的值在这时是不能被插入的。

总之：

GAP、next lock都是为了保证RR模式下，不会出现幻读，降低隔离级别或取消索引，这两种锁都不会产生。

IX IS X S是什么?

CN-FuWei

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL-小章节-InnoDB存储引擎核心特性-参数补充

12.1 存储引擎相关12.1.1 查看show engines;show variables like 'default_storage_engine';select @@default_storage_engine;12.1.2 如何指定和修改存储引擎(1) 通过参数设置默认引擎(2) 建表的时候进行设置(3) alter table t1 engine=innodb;12.2. 表空间12.2.1 共享表空间innodb_data_file_path
复制链接

扫一扫