# Day08 ENGINE 存储引擎

# 存储引擎介绍

相当于Linux的文件系统和"磁盘",负责IO的控制(磁盘资源、内存资源、对象资源)

mysql> show engines;
InnoDB **
MyISAM
MEMORY
  • InnoDB 存储引擎核心特性介绍
MVCC      :多版本并发控制
聚簇索引    : 用来组织存储数据和优化查询
支持事务    : 数据最终一致提供保证
支持行级锁  : 并发控制
外键         : 多表之间的数据一致一致性
多缓冲区支持
自适应Hash索引: AHI
复制中支持高级特性
备份恢复: 支持热备
自动故障恢复:CR Crash Recovery
双写机制 : DWB Double Write Buffer

问答:MyISAM和InnoDB的区别?

# TODO

# 查看存储引擎

-- 默认引擎
select @@default_storage_engine;

-- 查看库表引擎
show create table world.city;
select table_schema, table_name, engine from information_schema.tables where table_schema='world';

-- 创建与修改
create table t1 engine=innodb;
alter table t1 engine=innodb;   -- 另外也是碎片整理的语句

-- 批量替换引擎
-- concat 导入 sql 语句
select concat("alter table ", table_schema, ".", table_name, " engine='innodb'") 
from information_schema.tables where table_schema='world'
into outfile '/tmp/alter.sql';   -- 新增配置 secure-file-priv=/tmp

-- 数据库所有库表的元信息
mysql> desc information_schema.tables;
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
| Field           | Type                                                               | Null | Key | Default | Extra |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(64)                                                        | NO   |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)                                                        | NO   |     | NULL    |       |
| TABLE_NAME      | varchar(64)                                                        | NO   |     | NULL    |       |
| TABLE_TYPE      | enum('BASE TABLE','VIEW','SYSTEM VIEW')                            | NO   |     | NULL    |       |
| ENGINE          | varchar(64)                                                        | YES  |     | NULL    |       |
| VERSION         | int                                                                | YES  |     | NULL    |       |
| ROW_FORMAT      | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint unsigned                                                    | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint unsigned                                                    | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint unsigned                                                    | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint unsigned                                                    | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint unsigned                                                    | YES  |     | NULL    |       |
| DATA_FREE       | bigint unsigned                                                    | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint unsigned                                                    | YES  |     | NULL    |       |
| CREATE_TIME     | timestamp                                                          | NO   |     | NULL    |       |
| UPDATE_TIME     | datetime                                                           | YES  |     | NULL    |       |
| CHECK_TIME      | datetime                                                           | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(64)                                                        | YES  |     | NULL    |       |
| CHECKSUM        | bigint                                                             | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(256)                                                       | YES  |     | NULL    |       |
| TABLE_COMMENT   | text                                                               | YES  |     | NULL    |       |
+-----------------+--------------------------------------------------------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

-- 查看碎片情况
mysql> select table_name, data_free from information_schema.tables where table_name = 'city';
+------------+-----------+
| TABLE_NAME | DATA_FREE |
+------------+-----------+
| city       |       100 |
+------------+-----------+
-- 减少碎片量
alter table world.city engine=innodb;

# 线程实践排查

# 前台线程(连接层)
show processlist;
show full processlist;
select * from information_schema.processlist;
- Sleep状态,如连接池连接过来的,连接数打满会抛异常

# 后台线程(Server\Engine)
select * from performance_schema.threads;

说明:如何查询到连接线程和SQL线程的关系

方法一:
select * from information_schema.processlist;  --> iD=9
select * from performance_schema.threads where processlist_id=9\G;

mysql> select * from performance_schema.threads where processlist_id=9\G;
*************************** 1. row ***************************
          THREAD_ID: 49
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 9
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: information_schema
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: executing
   PROCESSLIST_INFO: select * from performance_schema.threads where processlist_id=9
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 1590           -- 实际操作系统线程ID
     RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

方法二:
 ps aux | grep 'mysqld'     --> 进程号 1439
 top -Hp 1439               --> 找到占用 CPU MEM 过多的线程号 1444


 select * from performance_schema.threads where thread_os_id=1444;
 *************************** 1. row ***************************
          THREAD_ID: 3     --> MySQL中线程thread_id=3
               NAME: thread/innodb/io_ibuf_thread
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: NULL
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 1444
     RESOURCE_GROUP: SYS_default
1 row in set (0.00 sec)

NAME: thread/innodb/io_ibuf_thread  --> 如何是SQL处理,找到对应的SQL语句处理  -> thread_id=3


select * from performance_schema.events_statements_current where thread_id=3\G;
select * from performance_schema.events_statements_history where thread_id=3\G;

*************************** 1. row ***************************
              THREAD_ID: 3
               EVENT_ID: 125
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:96
            TIMER_START: 76882498707961000
              TIMER_END: 76882498889288000

             TIMER_WAIT: 181327000 ***
              LOCK_TIME: 124000000 ***
               SQL_TEXT: select * from performance_schema.events_statements_current limit 1
                 DIGEST: 80224372d4fc6e5e379fc664fe4b5f374bb4c94aef7e876c38f7d6f27aa4691d
            DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_current` LIMIT ?
         CURRENT_SCHEMA: performance_schema
            .......
           STATEMENT_ID: 241
1 row in set (0.00 sec)

分析小结:
show processlist;   --> processlist_id
select * from performance_schema.threads where processlist_id=<processlist_id>;
或者
ps aux | grep 'mysqld'     --> 进程号 1439
top -Hp 1439               --> 找到占用 CPU MEM 过多的线程号 1444
select * from performance_schema.threads where thread_os_id=1444;
确定 thread_id,从而进一步分析

select * from performance_schema.events_statements_current where thread_id;
select * from performance_schema.events_statements_history where thread_id;

# Thread 工作线程

  • Master Thread
a. 控制刷新脏页到磁盘(CKPT)
b. 控制日志缓冲刷新到磁盘(log buffer ---> redo)
c. undo回收页
d. 合并插入缓冲(change buffer)
e. 控制IO刷新数量

# 参数 innodb_io_capacity 表示每秒刷新脏页的数量,默认200 机械磁盘
# 参数 innodb_max_dirty_pages_pct 设置触发刷新脏页百分比,结合定时刷新机制
# 另外MySQL倾向于换成脏页数据在内存中
mysql> show variables like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
- SSD 固定磁盘,可以设置
innodb_io_capacity 1000
innodb_io_capacity_max 20000


mysql> show variables like '%innodb_max_dirty_pages_pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
+--------------------------------+-----------+
  • IO Thread
-- 负责这些IO请求的回调处理
show engine innodb status\G;
  • Purge Thread
事务在提交之前,通过undolog(回滚日志)记录事务开始之前的状态,当事务被提交后,undolog便不再需要
因此需要Purge Thread线程来回收已经使用并分配的undo页
  • Page Cleaner Thread
脏页刷新,减轻master工作,提高性能

# InnoDB Architecture

# InnoDB 内存结构

  • In-Memory InnoDB Structures
# InnoDB Buffer Pool
作用:
    用来缓冲、缓冲MySQL的数据页(data page)和索引页、UNDO
    MySQL中最大的、最重要的内存区域
管理:
    查询
    > select @@innodb_buffer_pool_size;
    > select @@innodb_buffer_pool_instances;  --> 隔离实例,起到并发效果,4G内存的话,1个就行
    默认大小:128M
    生产建议:物理内存 50%-75%
    在线设置:> set global innodb_buffer_pool_size = 4831838208;
    永久设置:vim /etc/my.cnf  innodb_buffer_pool_size=256M
    查看是否有等待使用Buffer Pool的情形,从而判断是否需要增大缓冲池大小
          show global status like '%innodb%wait%';

Buffer不够用的场景有哪些?
    1. 设置太小
    2. 大事务
    3. CKPT刷新脏页不及时
    4. IO比较慢
    5. 查询语句优化的不好

Buffer Pool 具体分为
    1. Data Buffer
    2. Change Buffer 25%
    3. Adaptive Hash Index

## 1. Data Buffer
用来缓冲、缓冲MySQL的数据页(data page)

## 2. Change Buffer TODOinsertupdatedelete数据

## 3. Adaptive Hash Index
内存中"热"数据索引,索引的索引


# Log Buffer 
作用:
    存储数据页的变化,落到磁盘的话,对应 Redo Log(ib_logfile0 ib_logfile1)
管理:
    查询:
    > select @@innodb_log_buffer_size;
    默认大小:16M
    生产建议:和innodb_log_file_file_有关,1-2倍, 并发高的话,可以大些,1-2G
    配置方式:vim /etc/my.cnf  innodb_log_buffer_size=33554432
    查看状态:show global status like '%Innodb_log_waits%';24C 96G 三个 redo log 文件循环使用
innodb_log_file_size=2G
innodb_log_file_in_group=3
innodb_log_buffer_size=1G

# InnoDB 磁盘结构

# System Tablespace(系统表空间)

  • ibdata1

## 存储方式 
ibdata1~ibdataN, 5.5版本默认的表空间类型

## ibdata1共享表空间在各个版本的变化
5.5版本:
    系统相关:(全局)数据字典信息(表基本结构信息、状态、系统参数、属性..)、UNDO回滚信息(记 录撤销操作)Double Write buffer信息、临时表信息、change buffer 
    用户数据: 表数据行、表的索引数据

5.6版本:
共享表空间只存储于系统数据,把用户数据独立了
    系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、临时表信息、change buffer

5.7版本:在5.6基础上,把临时表独立出来,UNDO也可以设定为独立 
    系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、change buffer

8.0.19版本:在5.7的基础上将UNDO回滚信息默认独立,数据字典不再集中存储了 
    系统相关:Double Write信息、change buffer

8.0.20版本:在之前版本基础上,独立 Double Write信息 
    系统相关:change buffer

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html 
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

## 扩容共享表空间
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+

mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
参数用途:ibdata1文件,默认初始大小12M,不够用会自动扩展,默认每次扩展64M

vim /etc/my.cnf
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend
-- 注意初始的 ibdata1:12M 要和实际的idbata1 实际大小一致

5.7 中建议:设置共享表空间2-3个,大小建议512M或者1G,最后一个定制为自动扩展 
8.0 中建议:设置1-2个就ok,大小建议512M或者1G
生产中先配置大小,再初始化数据库

# File-Per-Table Tablespaces

  • 如 city.ibd等文件
# 表空间迁移,只能同版本,不同实例之间迁移,如5.7
create table t1 like source_t1;
cp source_t1.ibd;
chown -R mysql.mysql source_t1.ibd

alter table t1 discard tablespace;
alter table t1 import tablespace;

适用案例:
案例1:同学由于不可抗力因素,导致只剩下test库下的ibd 和 frm文件了(5.6版本)。没有备份。
案例2:同学将ibdata1(5.7版本)误rm掉了。导致只剩下test库下的ibd 和 frm文件了。备份坏的

在8.0没有frm文件,建表语句怎么查呢? 
[root@db world]# ibd2sdi city.ibd

# Undo Tablespaces

作用:
    撤销日志,回滚日志
存储位置: 
    5.7版本,默认存储在共享表空间中(ibdataN)
    8.0版本以后默认就是独立的 (undo_001-undo_002)
添加参数:
    vim /etc/my.cnf 
    innodb_undo_tablespaces=3
    innodb_max_undo_log_size=128M
    innodb_undo_log_truncate=ON
    innodb_purge_rseg_truncate_frequency=32

# Temporary Tablespaces

作用:
    存储临时表
        - 大的结果集,内存装不下
        - 排序分组结果临时表
        - uinon临时表
    所在位置 ibtmp1
管理:
    innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
生产:
    建议数据初始化之前设定好,一般2-3个,大小512M-1G

# General Tablespaces

  • 自定义共享文件,库表都存在这个文件,类似Oracle
  • 适合高端的存储设备

# 区与页

区extent(簇)
    一个区默认64个连续数据页。默认值是1M空间
页 page
    默认16KB
  • 页的通用结构
  • 行格式 TODO
建议:
    5.7+ 版本Dynamic。 建议,大字段不要存储到MySQL 核心业务表中
    或者非得用,建议将大列做hash值运算,然后单独存储一列,每次查询按照hash值列进行查询
结论:
    Compact:768字节 
    Dynamic:3072字节 
    建立索引时:列值长度不能超过以上字节数

# Doublewrite Buffer Files(双写缓冲区)

作用:
    MySQL,最小IO单元page(16KB),OS中最小的IO单元是block(4KB)
    为了防止出现以下问题: 
        mysqld process crash in the middle of a page write 
    
    先写DWB每次1M,2次写完,数据页再刷到磁盘 
 
补充: 
    DWB 对于写入性能确实有影响
    可关闭,用UPS + 高可用架构

# Redo Log

内存             磁盘
Log Buffer      Redo Log
                ib_logfile0、ib_logfile1

- What?
* DML(增删改,另外包含8.0+ DDL)操作导致的数据页面变化,均需要记录Redo日志
* 大部分为物理日志,即数据页的变化。逻辑日志说的是SQL的修改

- When?
* 在数据页修改完成之后,在脏页刷出磁盘之前,写入Redo日志
* 日志先行,日志一定比数据页先写会磁盘(WAL)
    ** 顺序IO,更快
    ** 日志量级小,更快
    ** 只要日志落到磁盘,就认为成功
* 聚簇索引/二级索引/Undo页面修改,均需要记录Redo日志


1. 作用:记录数据页的变化,实现"前进"的功能。WAL(write ahead log),日志先行,MySQL保证Redo优先于数据写入磁盘
2. 存储位置:数据路径下,进行轮序覆盖日志
    ib_logfile0 48M
    ib_logfile1 48M
3. 管理:
    mysql> show variables like '%innodb_log_file%';
    +---------------------------+----------+
    | Variable_name             | Value    |
    +---------------------------+----------+
    | innodb_log_file_size      | 50331648 |
    | innodb_log_files_in_group | 2        |
    +---------------------------+----------+

    生产建议:
        大小:512M-4G
        组数:2-4组
        vim /etc/my.conf
        innodb_log_file_size=100M
        innodb_log_files_in_group=3

# Undo 日志

- What?
    * DML(增删改,另外包含8.0+ DDL)操作导致的数据页面变化,均需要将记录前的镜像写入Undo日志
    * 逻辑日志
- When?
    * 实际数据页修改前
注意:Undo页面的修改,同样需要记录Redo日志

数据页修改涉及到Undo、Redo流程
1. 数据页的Undo日志处理流程
    * Undo数据页完成修改
    * Undo对应的Redo日志完成记录
    * Undo对应的Redo日志完成落盘
    * Undo数据页落盘
2. 内存中数据页处理流程
    * 内存中数据页完成修改
    * 内存中数据页对应的Redo日志完成记录
    * 内存中数据页对应的Redo日志完成落盘
    * 内存中数据页完成落盘

# 其他结构 ib_buffer_pool

5.7版本中,MySQL正常关闭时,会将内存的热数据存放(流方式)至ib_buffer_pool,下次重启直接读取 ib_buffer_pool加载到内存中
上次更新: 10/31/2022, 5:19:53 PM