第一章 什么是MySQL存储引擎？
MySQL存储引擎类似linux“文件系统”
MySQL自带的存储引擎种类
mysql> show engines; MRG_MYISAM CSV MyISAM BLACKHOLE PERFORMANCE_SCHEMA InnoDB ARCHIVE MEMORY FEDERATED
面试题
请你列举MySQL中支持的存储引擎种类？
InnoDB、MyISAM、CSV、MEMORY 等
分支产品的引擎种类介绍
TokuDB --> percona、mariadb MyRocks、Rocksdb
特点： 1. 压缩比15倍以上 2. 插入数据性能 适应场景： 例如Zabbix监控类的平台、归档库、历史数据存储业务
InnoDB存储引擎特性
MVCC ：多版本并发控制 聚簇索引 ：用来组织存储数据和优化查询 支持事务 ：数据安全保证 支持行级锁 ：控制并发 外键 多缓冲区支持 自适应Hash索引：AHI 复制中支持高级特性。 备份恢复：支持热备。 自动故障恢复：CR Crash Recovery 双写机制：Double Write
InnoDB 核心特性有哪些？InnoDB和MyISAM区别有哪些？
InnoDB 支持：事务、MVCC、聚簇索引、外键、缓冲区、AHI、CR、DW，MyISAM不支持。 InnoDB 支持：行级锁，MyISAM支持表级锁。 InnoDB 支持热备（业务正常运行，影响低），MyISAM支持温备份（锁表备份）。 InnoDB 支持CR(自动故障恢复)，宕机自动故障恢复，数据安全和一致性可以得到保证。MyISAM不支持，宕机可能丢失当前修改。
第二章 存储引擎的基本操作
查询支持的存储引擎
mysql> show engines;
查询、设置默认存储引擎
mysql> select @@ default_storage_engine;
修改配置文件 （设置引擎默认为innodb）
vim /etc/my.cnf default_storage_engine=InnoDB
查看、设定 表的存储引擎
查看某张表的存储引擎
mysql> show create table xta;
查询系统中所有业务表的存储引擎信息
mysql> select table_schema, table_name , engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
创建表设定存储引擎
mysql> create table xxx (id int) engine=innodb charset=utf8mb4;
修改已有表的存储引擎
mysql> alter table xxx engine=myisam; mysql> alter table world.xxx engine=innodb;
将所有的非InnoDB引擎的表查询出来，批量修改为InnoDB
mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb'; mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb' into outfile '/tmp/a.sql'; mysql> source /tmp/a.sql
第三章 InnoDB 存储引擎的体系结构
磁盘结构
表空间结构
表空间的概念源于Oracle数据库。最初的目的是为了能够很好的做存储的扩容。
共享（系统）表空间
存储方式ibdata1~ibdataN, 5.5版本默认的表空间类型
ibdata1共享表空间在各个版本的变化
5.5版本： 系统相关：（全局）数据字典信息（表基本结构信息、状态、系统参数、属性..）、UNDO回滚信息(记录撤销操作)、Double Write信息、临时表信息、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
共享表空间管理
扩容共享表空间
mysql> select @@innodb_data_file_path; mysql> select @@innodb_autoextend_increment; ibdata1文件，默认初始大小12M，不够用会自动扩展，默认每次扩展64M
设置方式
先查看实际大小： [root@db01 data]# ls -lh ibdata1 -rw-r----- 1 mysql mysql 76M May 6 17:11 ibdata1 配置文件设定为和实际大小一致： innodb_data_file_path=ibdata1:76M;ibdata2:100M;ibdata3:100M:autoextend
模拟在初始化时设置共享表空间（生产建议）
5.7 中建议：设置共享表空间2-3个，大小建议512M或者1G，最后一个定制为自动扩展。 8.0 中建议：设置1个就ok，大小建议512M或者1G
$ /etc/init.d/mysqld stop $ rm -rf /data/3306/data/* $ vim /etc/my.cnf # 修改 innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend # 重新初始化 $ mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data # 重启数据库生效 $ /etc/init.d/mysqld start
注：数据库配置文件改动则需重启生效
独立表空间
5.6版本中，针对用户数据，单独的存储管理。存储表的数据行和索引。
通过参数控制： mysql> select @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ 测试：共享表空间存储用户数据 mysql> set global innodb_file_per_table=0;
利用独立表空间进行快速数据迁移
源端:3306/test/t100w -----> 目标端：3307/test/t100w
1. 锁定源端t100w表 mysql> lock tables test.t100w read; mysql> show create table test.t100w; CREATE TABLE `t100w` ( `id` int(11) DEFAULT NULL, `num` int(11) DEFAULT NULL, `k1` char(2) DEFAULT NULL, `k2` char(4) DEFAULT NULL, `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 2. 目标端创建test库和t100w空表 mysql> create database test charset=utf8mb4; CREATE TABLE `t100w` ( `id` int(11) DEFAULT NULL, `num` int(11) DEFAULT NULL, `k1` char(2) DEFAULT NULL, `k2` char(4) DEFAULT NULL, `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 3. 单独删除空的表空间文件（保留t100w的frm,ibdata1中关于t100w的系统数据） mysql> alter table test.t100w discard tablespace; 4. 拷贝源端ibd文件到目标端目录，并设置权限 [root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/ [root@db01 test]# chown -R mysql.mysql /data/* 5. 导入表空间 mysql> alter table test.t100w import tablespace; mysql> select count(*) from test.t100w; +----------+ | count(*) | +----------+ | 1000000 | 6. 解锁源端数据表 mysql> unlock tables;
undo表空间
1. 作用：用来作撤销工作。 2. 存储位置：5.7版本，默认存储在共享表空间中（ibdataN）。8.0版本以后默认就是独立的（undo_001-undo_002）。 3. 生产建议：5.7版本后，将undo手工进行独立。
undo 表空间管理
如何查看undo的配置参数SELECT @@innodb_undo_tablespaces; ---->3-5个 #打开独立undo模式，并设置undo的个数。 SELECT @@innodb_max_undo_log_size; #undo日志的大小，默认1G。SELECT @@innodb_undo_log_truncate; #开启undo自动回收的机制（undo_purge）。SELECT @@innodb_purge_rseg_truncate_frequency; #触发自动回收的条件，单位是检测次数。
配置undo表空间
$ /etc/init.d/mysqld stop $ rm -rf /data/3306/data/* $ 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 $ mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data $ /etc/init.d/mysqld start $ ll /data/3306/data/undo00* -rw-r----- 1 mysql mysql 10485760 May 7 15:39 /data/3306/data/undo001 -rw-r----- 1 mysql mysql 10485760 May 7 15:39 /data/3306/data/undo002 -rw-r----- 1 mysql mysql 10485760 May 7 15:39 /data/3306/data/undo003
临时表空间
作用：存储临时表。 管理： 建议数据初始化之前设定好，一般2-3个，大小512M-1G。 innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M
InnoDB 事务日志
redo log 重做日志
作用: 记录数据页的变化。实现“前进”的功能。WAL（write ahead log），MySQL保证redo优先于数据写入磁盘。
存储位置：数据路径下，进行轮序覆盖记录日志
查询redo log文件配置 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.cnf # 添加参数： innodb_log_file_size=100M innodb_log_files_in_group=3 $ /etc/init.d/mysqld restart $ ll /data/3306/data/ib_logfile* -rw-r----- 1 mysql mysql 104857600 May 7 16:17 /data/3306/data/ib_logfile0 -rw-r----- 1 mysql mysql 104857600 May 7 16:17 /data/3306/data/ib_logfile1 -rw-r----- 1 mysql mysql 104857600 May 7 16:17 /data/3306/data/ib_logfile2 其他结构 ib_buffer_pool 作用： 缓冲和缓存，用来做“热”（经常查询或修改）数据页，减少物理IO。 当关闭数据库的时候，缓冲和缓存会失效。 5.7版本中，MySQL正常关闭时，会将内存的热数据存放（流方式）至ib_buffer_pool。下次重启直接读取ib_buffer_pool加载到内存中。 # Double Write Buffer（DWB）（5.7 默认在ibdataN中，8.0.20以后可以独立了。） 作用： MySQL，最小IO单元page（16KB），OS中最小的IO单元是block（4KB） 为了防止出现以下问题： mysqld process crash in the middle of a page write
其他结构
ib_buffer_pool
作用：
缓冲和缓存，用来做“热”（经常查询或修改）数据页，减少物理IO。
当关闭数据库的时候，缓冲和缓存会失效。
管理： 查询： 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 重启生效
内存结构
InnoDB BUFFER POOL（IBP）
作用：
用来缓冲、缓存，MySQL的数据页和索引页。MySQL中最大的、最重要的内存区域。
管理 ： 查询： 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
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
