# Day08 ENGINE 存储引擎

# 存储引擎介绍

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

mysql> show engines;
InnoDB **
MyISAM
MEMORY

InnoDB 存储引擎核心特性介绍

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

问答：MyISAM和InnoDB的区别？

提示：InnoDB具备哪些特性，而MyISAM不具备

# 查看存储引擎

-- 默认引擎
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
如insert、update、delete数据

## 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加载到内存中

← Day07 EXPLAIN 执行计划 Day09 CR事务与锁 →