MySQL 8.0 OCP认证精讲视频、环境和题库之五 事务、缓存 、表空间

 redo log buffer：

   缓存与事务有关的redo log ,用来对mysql进行crash恢复，不可禁用；
   日志缓冲区是存储要写入磁盘上日志文件的数据的内存区域。日志缓冲区大小由innodb_Log_buffer_size变量定义。
   默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，
   而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新、插入或删除许多行的事务，
   那么增加日志缓冲区的大小可以节省磁盘I/O
变量innodb_log_buffer_size:
    指定redo log buffer的大小；
变量innodb_log_group_home_dir:
    指定redo log file的路径
变量innodb_log_files_in_group :
    每组日志组中包含的日志文件个数；

    mysql> show variables like 'innodb_log_group_home_dir';
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | innodb_log_group_home_dir | ./    |
    +---------------------------+-------+
    1 row in set (0.00 sec)    
    mysql> show variables like 'innodb_log_files_in_group';
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | innodb_log_files_in_group | 2     |
    +---------------------------+-------+
    1 row in set (0.00 sec)

    mysql> show variables like 'datadir';
    +---------------+-------------------+
    | Variable_name | Value             |
    +---------------+-------------------+
    | datadir       | /data/mysql/data/ |
    +---------------+-------------------+
    1 row in set (0.00 sec)

    mysql> 
    [root@student ~]# ls  /data/mysql/data/*logfile*
    /data/mysql/data/ib_logfile0  /data/mysql/data/ib_logfile1
    组中共有两个logfile
    [root@student ~]# du -m  /data/mysql/data/*logfile*     #48MB
    48    /data/mysql/data/ib_logfile0
    48    /data/mysql/data/ib_logfile1
    [root@student ~]# 
    mysql> show variables like 'innodb_log_file_size';  #日志大小
    +----------------------+----------+
    | Variable_name        | Value    |
    +----------------------+----------+
    | innodb_log_file_size | 50331648 |
    +----------------------+----------+
    1 row in set (0.01 sec)
    mysql> select 50331648/1024/1024;
    +--------------------+
    | 50331648/1024/1024 |
    +--------------------+
    |        48.00000000 |
    +--------------------+
    1 row in set (0.00 sec)

    mysql> 
  修改日志组和日志文件大小
    [root@student ~]# systemctl stop mysql
    [root@student ~]# cat  /etc/my.cnf
    [mysqld]
    datadir=/data/mysql/data/
    socket=/data/mysql/mysql.sock

    innodb_buffer_pool_chunk_size=64M
    innodb_buffer_pool_size=256M

    innodb_log_file_size=100M
    innodb_log_files_in_group=4
    
   [root@student ~]# systemctl start mysql
    mysql> show variables like '%innodb_log%';
    +------------------------------------+-----------+
    | Variable_name                      | Value     |
    +------------------------------------+-----------+
    | innodb_log_buffer_size             | 16777216  |
    | innodb_log_checksums               | ON        |
    | innodb_log_compressed_pages        | ON        |
    | innodb_log_file_size               | 104857600 |   #100MB 
    | innodb_log_files_in_group          | 4         |   #4个logfile 
    | innodb_log_group_home_dir          | ./        |
    | innodb_log_spin_cpu_abs_lwm        | 80        |
    | innodb_log_spin_cpu_pct_hwm        | 50        |
    | innodb_log_wait_for_flush_spin_hwm | 400       |
    | innodb_log_write_ahead_size        | 8192      |
    +------------------------------------+-----------+
    [root@student ~]# du -m  /data/mysql/data/ib_logfile*
    100    /data/mysql/data/ib_logfile0
    100    /data/mysql/data/ib_logfile1
    100    /data/mysql/data/ib_logfile2
    100    /data/mysql/data/ib_logfile3
    [root@student ~]# 
    
    redo log buffer:缓存与事务有关的redo log,用来对mysqldi进行crash恢复，不可禁用
    变量innodb log buffer size:指定redo log buffer的大小
    变量innodb_log_group_home_dir:指定redo log文件的路径
    变量innodb_log_files_in_group:指定Redo log.文件的数量
    变量innodb_log_file_size:指定每个redo log文件的大小
    实验：redo log的设置：
    1、查看上述变量I
    2、通过set命令指定redo log buffer的大小为32M
    3、在选项文件中指定相关变量，设置redo log buffer的大小为32M,设置redo log.文件为4个，每个文件100M
    重新启动mysqld服务，再此查看上述变量

存储引擎

列出 mysql所支持的存储引擎
mysql>show engines;

默认存储引擎为innoDB,不可禁用

mysql> show engines;

只有InnoDB  和DBcluster支持事务acid

mysql> use testdb
mysql> create table t1(id integer);
mysql> start transaction;
mysql> insert into t1 values(1);
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into t1 values('xyz');
ERROR 1366 (HY000): Incorrect integer value: 'xyz' for column 'id' at row 1
mysql> rollback;
mysql> select * from t1;
Empty set (0.00 sec)

#体现要么全部成功，要么全部失败

LAMP：Linux Apache mysql PHP

加锁的级别：

    InnoDB:行级锁
    MyISAM:表级锁
数据字典：系统表
表空间：一种逻辑结构，用于组织表和索引，位于表和数据文件之间

实验1：存储引擎

1、创建数据库testdb,进入数据库
    create databases testdb;
    use testdb;
2、 创建表t1,存储引擎为myisam
    create table t1(id integer) engine=myisam
3、在表上执行事务，观察事务的rollback
    start transaction;
    insert into t1 values(100);
    insert into t1 values('xyz');#观赛错误信息；
    rollback ;
    mysql> rollback ; #回滚之后数据还在，因为mysql 不支持ACID
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> select * from t1;
    +------+
    | id   |
    +------+
    |  100 |
    +------+
    1 row in set (0.00 sec)

4、创建表t2 存储引擎为innodb,在表上执行事务，观察事务的rollback
    create table t2(id integer) engine=innodb;
    start transaction;
    insert into  t2 values(100);
    insert into  t2 values('xyz');#观赛错误信息；
    rollback;                     #rollback 之后，刚插入的数据全部回滚
    
    mysql> select * from t2;
    Empty set (0.00 sec)

把t1表的存储引擎改为innnodb;
    alter table t1 engine innodb;
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into t1 values(22);
    Query OK, 1 row affected (0.00 sec)

    mysql>  insert into  t2 values('xyz');
    ERROR 1366 (HY000): Incorrect integer value: 'xyz' for column 'id' at row 1
    mysql> rollback;
    Query OK, 0 rows affected (0.02 sec)

    mysql> select * from t1;
    +------+
    | id   |
    +------+
    |  100 |
    +------+
    1 row in set (0.00 sec)

    mysql> 
 

表空间

表空间的类型

1.     System Tablespace
2.     Undo Tablespaces
3.     General Tablespaces
4.     File-Per-Table Tablespaces
5.     Temporary Tablespace

   System Tablespace

 在MySQL 8.0中，InnoDB将元数据存储在MySQL数据字典中.在以前的MySQL版本中，系统表空间也包含双写缓冲区存储（Doublewrite Buffer）区域。从MySQL 8.0.20开始，此存储区域位于单独的双写文件中.

系统表空间可以有一个或多个数据文件。默认情况下，在数据目录中创建一个名为ibdata1的系统表空间数据文件。

与系统表空间有关的变量：

        innodb_data_file_path：系统表空间数据文件的名称、初始大小和数量，是否自动扩展；

         innodb_data_home_dir   ：系统表空间数据文件的路径，如果为空，则是数据文件路径 

        innodb_autoextend_increment：每次扩展系统表空间时的自动扩展量

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

[root@student ~]# du -m  /data/mysql/data/ibdata1;
12    /data/mysql/data/ibdata1

mysql> show variables like 'innodb_autoextend_increment';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.01 sec)

扩展系统表空间

需要在配置文件中更改

扩容：

[mysqld]
datadir=/data/mysql/data/
socket=/data/mysql/mysql.sock

innodb_buffer_pool_chunk_size=64M
innodb_buffer_pool_size=256M

innodb_log_file_size=100M
innodb_log_files_in_group=4

#innodb_data_file_path=ibdata1:10M:autoextend
innodb_data_home_dir=
innodb_data_file_path=/data/mysql/data/ibdata1:12M;/data/mysql/data/ibdata2:480M:autoextend:max:960M
不能通过更改现有系统表空间数据文件的大小属性来增加其大小。例如，在启动服务器时，将innodb_data_file_path设置从ibdata1:12M:autoextend更改为ibdata1:120M:autoextend会生以下 类似错误：

[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
###只有最后一个文件才可以自动扩展

验证：

mysql> show variables like '%innodb_data%';
+-----------------------+--------------------------------------------------------------------------------+
| Variable_name         | Value                                                                          |
+-----------------------+--------------------------------------------------------------------------------+
| innodb_data_file_path | /data/mysql/data/ibdata1:12M;/data/mysql/data/ibdata2:480M:autoextend:max:960M |
| innodb_data_home_dir  |                                                                                |
+-----------------------+--------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> 
 

缩容

不支持减小现有系统表空间的大小。实现较小系统表空间的唯一选择是将数据从备份恢复到使用所需系统表空间大小配置创建的新MySQL实例。

为系统表空间使用原始磁盘分区

MySQL :: MySQL 8.0 Reference Manual :: 15.6.3.1 The System Tablespace

Temporary Tablespaces

 

session temporary tablespaces 

 InnoDB：
会话临时表空间存储用户创建的临时表和优化器创建的内部临时表。从MySQL 8.0.16开始，用于磁盘内部临时表的存储引擎是InnoDB。（以前，存储引擎由internal_tmp_disk_storage_engine的值决定。）

会话临时表空间文件创建时大小为五页，文件扩展名为.ibt。

[root@student ~]# du -m  /data/mysql/data/ibtmp1
12    /data/mysql/data/ibtmp1
innodb_tmp_tablespaces_dir：变量定义了创建会话临时表空间的位置。默认位置是数据目录中的#innodb_temp目录。

INNODB_SESSION_TEMP_TABLESPACES：表提供有关会话临时表空间的元数据。

Information Schema INNODB_TEMP_TABLE_INFO：表提供了关于用户创建的临时表的元数据，这些临时表在INNODB实例中处于活动状态。

global temporary tablespace.

全局临时表空间（ibtmp1）存储对用户创建的临时表所做更改的回滚段。

innodb_temp_data_file_path 

innodb_temp_data_file_path 变量定义全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有为innodb_temp_data_file_path指定值，则默认行为是在innodb_data_home_dir目录中创建一个名为ibtmp1的自动扩展数据文件。初始文件大小略大于12MB。

脚本的执行
mysql>source 脚本文件
mysql -uroot -p密码   <脚本文件
事务自动提交

 Undo tablespaces

********************************************************************************
**    Undo tablespaces
********************************************************************************

Undo tablespaces包含撤消日志，这些日志是记录的集合，包含有关如何撤消事务对聚集索引记录的最新更改的信息
create.drop ,delete ,update ,insert ; 存储undo数据，由rollback段组成，功能：允许用户回滚事务

mysql5.7不单独提供Undo tablespaces,从MySQL8.0开始提供

默认Undo tablespaces
    show variables like 'innodb_undo_directory%';
    show variables like 'innodb_undo_tablespaces';
    show variables like ‘%innodb_max_undo_log_size’；
    show variables like ‘innodb_undo_log_truncate‘；
    
    MySQL实例初始化时会创建两个默认的undo表空间。默认的撤消表空间是在初始化时创建的，以便为回滚段提供一个位置，回滚段必须存在才能接受SQL语句。至少需要两个撤消表空间来支持撤消表空间的自动截断。
    show variables like '%datadir%
    +---------------+-------------------+
    | Variable_name | Value             |
    +---------------+-------------------+
    | datadir       | /data/mysql/data/ |
    +---------------+-------------------+
    [root@student ~]# du -m /data/mysql/data/undo*
    10    /data/mysql/data/undo_001
    10    /data/mysql/data/undo_002
    [root@student ~]# 
    mysql> show variables like 'innodb_undo_tablespaces';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | innodb_undo_tablespaces | 2     |  ##默认值
    +-------------------------+-------+
    1 row in set (0.00 sec)
    

Undo tablespaces大小
    在MySQL 8.0.23之前，undo表空间的初始大小取决于innodb_page_size值。对于默认的16KB页面大小，初始撤消表空间文件大小为10MiB
    innodb_page_size     undo tablespace大小
          4k                 7MiB 
          8K                 8M
          32KB               20MiB
          64KB               40MiB
    从MySQL 8.0.23开始，undo表空间的初始大小通常为16MiB。      
    在MySQL 8.0.23之前，undo表空间一次扩展四个区段。从MySQL 8.0.23开始，undo表空间至少扩展了16MB。如果AUTOEXTEND_SIZE选项是为撤消表空间定义的，则它将由AUTOEXTEND _SIZE设置和由上述逻辑确定的扩展大小中的较大者进行扩展     。
    show variables like '%AUTOEXTEND _SIZE%';
    练习：
        改innodb_undo_tablespaces=4 ，在选项文件中设置；
    [root@student ~]# cat /etc/my.cnf|grep innodb_undo_tablespaces=4
    innodb_undo_tablespaces=4
    
    SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
    WHERE FILE_TYPE LIKE 'UNDO LOG';

        
    
添加Undo tablespaces
    MySQL 8.0.14
    在MySQL 8.0.14之前，通过配置innodb_undo_tablespace启动变量可以创建额外的undo表空间。
    
    从MySQL8.0.14开始，innodb_undo_tablespaces 初始化时指定的数量，如要创建更多的undo表空间
    从MySQL8.0.14开始 innodb_undo_tablespaces已不再可用。
    
    CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';
    语法
    https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html
    show variables like '%innodb_directories%';
    撤消表空间文件名必须具有扩展名。不允许定义撤消表空间文件时指定相对路径。
    innodb_directories 
    innodb_data_home_dir
    innodb_undo_directory 
    datadir 
    撤消表空间文件名不包含路径,则由innodb_undo_directory指定。
    innodb_undo_directory未指定，则由datadir指定。
    
    要查看撤消表空间名称和路径
    SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES   WHERE FILE_TYPE LIKE 'UNDO LOG'; 
    一个 MySQL 实例最多支持 127 个撤消表空间，包括 MySQL 实例创建的两个默认撤消表空间 已初始化。
    

删除Undo tablespaces
    从MySQL 8.0.14开始，可以在运行时创建或删除Undo tablespaces。
    1、Undo tablespaces的活动状态：
        ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
    2、删除Undo tablespaces：
        DROP UNDO TABLESPACE tablespace_name;
    3、查询，以确认。
        SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES   WHERE NAME LIKE 'tablespace_name';
    
移动Undo tablespaces
    在服务离线时，由CREATE UNDO TABLESPACE创建时的UNDO TABLESPACE可以移动任何已知的目录：
    innodb_directories 
    innodb_data_home_dir
    innodb_undo_directory
    datadir 
    
配置 Rollback Segments的数量
     innodb_rollback_segments为每个undo tablespace和global temporary tablespace指定回滚段的数量
     innodb_rollback_segments 默认是 128个，具体可参考 “Undo Logs”.
      
截断Undo tablespaces
    方法1：自动化。在没有手动干预的情况下对撤消表空间自动执行停用、截断和重新激活。
        SET GLOBAL innodb_undo_log_truncate=ON;
        show variables like 'innodb_undo_log_truncate';
        如果启用了innodb_undo_log_struncate变量，则会截断超过innodb_max_undo_log_size变量定义的大小限制的撤消表空间。
        SELECT @@innodb_max_undo_log_size;
        
    当启用innodb_undo_log_truncate时
        1、超过innodb_max_undo_log_size设置的默认和用户定义的撤消表空间被标记为截断。选择要截断的撤消表空间是以循环方式执行的，
        以避免每次都截断同一个撤消表空间。
        2、被标记为截断的撤消表空间中的回滚段处于非活动状态，回滚段处于非活动状态，并执行完成当前正在使用回滚段的现有事务。
        3、purge system 释放回滚段空间。
        4、truncate操作将运行并将undo表面积截断为其初始大小。
           关联参数和表空间
             innodb_undo_directory
             SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
        5、 Rollback segments重新激活，以便重新分配给新事务。
    
    优化自动Undo tablespaces
        清除线程负责清空和截断撤消表空间。默认情况下，清除线程每调用128次清除，就会查找要截断的撤消表空间。由innodb_urge_rseg_truncate_frequency指定。
        SELECT@@innodb_urge_rseg_truncate_frequency；
        mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;   #加快清理频率。
        
    方法2：手动。
        手动截断撤消表空间至少需要三个活动撤消表空间。始终需要两个活动的撤消表空间来支持启用自动截断的可能性。
        要手动启动对撤消表空间的截断：
         1、ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
         2、其它步骤同自动截断。
         3、确认，查询 
            SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES  WHERE NAME LIKE 'tablespace_name';
         4、ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;
         
    
    两种方法可以结合使用

    截断Undo tablespaces对性能的影响
    可能会导致性能略有下降。性能受影响的程度取决于多个因素：
    1、撤消表空间数
    2、撤消日志数
    3、撤消表空间大小
    3、I/O子系统的速度
    4、现有的长期事务
    5、系统负载
    6、免除潜在性能影响的最简单方法是增加撤消表空间的数量。 
    
    
    
Undo tablespaces状态 
  监控Undo tablespaces的状态
    SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%truncate%';
    SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
 

来源地址：https://blog.csdn.net/qq_25439957/article/details/133888909