基础章节-01-MySQL数据库服务中级课程
1.00 课程知识章节说明
目前在互联网的实际应用中，各个企业都会比较关注自身网站的数据信息，既要保证数据信息的安全性，同时也要保证数据存储读取效率
并且在特殊的场景下，还要对存储的数据信息进行检索和分析；因此数据库服务业务已经在各行各业应用非常的广泛
对于互联网领域的技术人员，对于数据库服务知识的掌握，也将是在求职时必备的技能，有些时候还会绝对入职的定级和薪资水平。
1.09 数据库服务存储引擎
1.9.1 数据库存储引擎概念
存储引擎就是数据库服务中的文件系统，用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等；
1.9.2 数据库存储引擎种类
在各种版本的数据库服务中，是有多种存储引擎可以应用的，以MySQL数据库服务为例，可以使用命令查看可以应用存储引擎：
mysql> showengines;
引擎类型信息输出：
在实际场景中，99.9%都是使用innodb存储引擎，并且在最新版8.0数据库中，所有mysql数据库中的表对应的引擎也都改为了innodb；
如果在面试环节中，面试官询问你：
列举出mysql中支持的存储引擎种类：InnoDB、MyISAM、CSV、MEMORY；
列举出mysql分支产品的存储引擎种类：在percone、mariadb数据库中，可能还会应用TokuDB MyRocks Rocksdb存储引擎
从特点上可以支持innodb引擎的特性(支持事务)，并且数据压缩比比较高(15倍)，数据插入性能比较强(5~6倍)；
以上存储引擎就比较适合于zabbix监控类的平台，归档数据、历史数据存储业务等，数据量级比较大的情况；
监控服务部署tokuDB存储引擎参考链接：https://www.cnblogs.com/oldboy-heqing /articles/16891210.html
1.9.3 数据库存储引擎特性
在数据库服务领域，大部分场景下都会使用innodb存储引擎，是因为innodb存储引擎具有一定优秀特性：
序号特性解释说明
01数据访问特性支持多版本并发控制特性(MVCC)，支持行级锁控制并发
02数据索引特性支持聚簇索引/辅助索引特性，可以组织存储数据和优化查询(IOT)
03数据事务特性支持事务概念特性，可以实现数据的安全保证
04数据缓冲特性支持多缓冲区功能，自适应hash索引(AHI)
05数据迁移特性支持复制数据中的高级功能特性，支持数据备份恢复的热备
06服务自愈特性支持自动故障恢复(CR-Crash Recovery)
07数据存储特性支持数据双写机制(Double write) 数据存储有关的安全机制

图片

图片

如果在面试环节中，面试官询问你： InnoDB核心特性有哪些，以及与MyISAM存储引擎之间的区别： InnoBD支持：事务、mvcc、聚簇索引、外键、缓冲区、AHI、DW；MyISAM均不支持 InnoDB支持：行级锁，MyISAM只支持表级锁； InnoDB支持：数据热备，可以保证业务正常运行，对业务影响低，MyISAM只支持温备份，需要锁表备份； InnoDB支持：支持CR自动故障恢复，宕机自动恢复，数据安全和一致性可以得到保证；MyISAM不支持，宕机可能丢失当前数据； 企业实际场景案例分析说明： 案例说明01：某期学员负责运维+MySQL相关工作； 平台环境：zabbix 3.2 + centos 7.3 + mariadb 5.5（InnoDB引擎），利用监控平台，监控了2000多个节点服务； 问题现象：每隔一段时间zabbix服务就会运行操作很卡，每隔3-4个月，都要重新部署zabbix，存储空间经常爆满（ibdata1 400500G）；ibdate1 ibdata1-01 异常分析： zabbix版本过低，建议将zabbix程序进行升级更新； zabbix使用的数据版本过低，建议将数据库版本进行升级，因为新版本数据库的原生态环境就比旧版本好些； 在数据库5.5版本中，在没有做数据存储调配时，数据库所有数据都会保存到ibdata1文件中； 在ibdata1文件中的数据空间，不会因为数据库中的数据删除，产生数据回缩的效果，即空间不释放； 优化建议： 数据库版本升级到percona 5.7+（mariadb 10.x+），zabbix软件升级更高版本； 数据库服务存储引擎改为tokudb； 监控数据最好按月份进行数据切割（二次开发zabbix程序，数据保留机制功能重写，并且数据库分表存储） 将数据库服务的binlog功能关闭（双1）； 数据库服务相关内存优化参数调整； 优化思路： zabbix程序原生态支持TokuDB，经过压力测试，5.7要比5.5数据库版本性能高出 23倍； 使用TokuDB作为数据库存储引擎，insert数据比innodb要快的多，数据压缩比也要比Innodb高； 监控数据按月份进行切割（分区），为了能够truncate每个分区表，以便立即释放存储空间； 将数据库服务binlog关闭，是为了减少无关日志的记录，避免磁盘IO的消耗，以及节省磁盘空间的使用； 参数优化调整，主要是对安全性参数或内存相关参数调整，提高数据库服务运行性能； 企业案例资料参考： https://mariadb.com/kb/en/ installing-tokudb https://docs.percona.com/percona-server/5.7/tokudb/tokudb_intro.html https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html 案例说明02：企业客户实际数据库需求 平台环境：centos 5.8+mysql 5.0版本，MyISAM存储引擎+网站架构LNMP，数据量50G左右 问题现象：业务并发压力大的时候，整体网站访问非常卡，还会出现数据库服务宕机情况，造成部分数据丢失 问题分析： MyISAM存储引擎具有表级锁，在高并发访问时，会频繁出现锁等待情况； MyISAM存储引擎不支持事务机制，在断电或宕机时，会有可能丢失数据信息； 优化建议： 数据库服务版本进行升级，从5.0升级到更高的版本；ok 数据库服务升级后，迁移所有表数据到新环境（表空间迁移），调整存储引擎为InnoDB； 数据库服务开启双1安全参数； 数据库服务进行重构主从架构

1.9.4 数据库存储引擎应用

01 引擎基本操作说明： 数据库存储引擎信息查看：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 查看数据库可用存储引擎
mysql> show engines;
# 查看数据库默认存储引擎
mysql> select @@default_storage_engine;
+----------------------------------+
| @@default_storage_engine |
+----------------------------------+
| InnoDB                                    |
+----------------------------------+
1 row in set (0.00 sec)

数据库存储引擎配置修改：

1
2
3
4
5

# 永久修改存储引擎配置
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
-- 重启数据库服务生效

数据表存储引擎信息查看：（具体表的存储引擎）

1
2
3
4
5

# 查看建表语句获取存储引擎信息
mysql > show create table city;
# 查看information_schema数据库获取存储引擎信息
mysql > select table_schema,table_name,engine from information_schema.tables where table_schema not
in('sys','mysql','information_schema','performance_schema')

数据表存储引擎配置修改：（具体表的存储引擎）

1
2
3
4
5

# 创建表时设置存储引擎
mysql > create table xxx (id int) engine=innodb charset=utf8mb4;
# 修改表示设置存储引擎
mysql > alter table world.xxx engine=myisam;
mysql > alter table world.xxx engine=innodb;

1.9.5 数据库存储引擎结构

数据库服务存储引擎结构的介绍，可以依据官方图示参考说明： https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

图片

结合官方存储引擎结构图，可以看出存储引擎结构分为两个部分： 01 On-Disk Structures（磁盘结构部分） 在磁盘存储结构中，会使用表空间模式进行数据信息的管理，经常提到的段 区 页概念也是属于表空间的逻辑结构； 表空间的概念源于oracle数据库，最初的目的是为了能够更好的做存储的扩容；因此数据库的表空间技术类似磁盘管理的LVM技术；

图片

在数据库服务中所使用的表空间也被划分出几种种类： 类型一：共享(系统)表空间 属于数据库服务5.5版本时默认的表空间应用，具体数据存储数据方式为：ibdata1~ibdataN ibdata共享表空间在各个版本之间的作用区别：

数据库版本存储数据解释说明
全局数据字典信息(表基本结构信息、状态系统参数、属性)、undo回滚日志(记录撤销操作);
MySQL 5.5版本系统相关数据
Double write buffer信息、临时表信息、changer buffer
业务表数据行、表的索引数据均统一存储在ibdata1中，实现集中管理
用户相关数据
数据表中数据清理后，ibdata1也不会释放磁盘空间
全局数据字典信息(表基本结构信息、状态系统参数、属性)、undo回滚日志(记录撤销操作);
MySQL 5.6版本系统相关数据
Double write buffer信息、临时表信息、changer buffer
用户相关数据共享表空间只存储系统数据，用户相关数据被独立管理了(独立表空间管理)
全局数据字典信息 undo回滚日志 Double write buffer信息、changer buffer
MySQL 5.7版本系统相关数据
临时表信息被独立出来了，undo也可以设定为独立
用户相关数据共享表空间只存储系统数据，用户相关数据被独立管理了(独立表空间管理)
Double write buffer信息、changer buffer
MySQL 8.0.11系统相关数据
undo回滚日志信息被独立出来了，数据字典信息也不再集中存储管理了
用户相关数据共享表空间只存储系统数据，用户相关数据被独立管理了(独立表空间管理)
changer buffer
MySQL 8.0.20系统相关数据
Double write buffer信息被独立出来了

共享表空间管理： 扩容共享表空间操作： 扩容前共享表空间信息查看：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> select @@innodb_data_file_path;
+-------------------------------------+
| @@innodb_data_file_path     |
+-------------------------------------+
| ibdata1:12M:autoextend         |
+-------------------------------------+
1 row in set (0.00 sec)
-- 可以在初始安装好数据库服务后，进行修改配置为两个ibdate文件，每个共享表空间文件占用2G，总共占用4个G空间
mysql> select @@innodb_autoextend_increment;
+---------------------------------------------+
| @@innodb_autoextend_increment |
+---------------------------------------------+
|                                                         64 |
+---------------------------------------------+
1 row in set (0.00 sec)
-- 查看参数信息说明：ibdata1文件，默认初始大小12M，不够用会自动扩展，默认每次扩展64M

共享表空间的扩容操作方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# 编写数据库配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend
-- 需要注意的是ibdata1文件大小必须和实际数据库要存储的数据相匹配，否则会出现如下报错信息
[ERROR] [MY-012264] [InnoDB] The innodb_system data file './ ibdata1' is of a different size 768 pages (rounded down to MB) than the 4864 pages specified in the
.cnf file!
-- 表示ibdate1指定大小超过了原有ibdata1实际的大小尺寸
# 查看配置信息是否生效
mysql> select @@innodb_data_file_path;
+---------------------------------------------------------------------+
| @@innodb_data_file_path                                                  |
+---------------------------------------------------------------------+
| ibdata1:12M;/data02/ ibdata2:100M;/data03/ ibdata3:100M:autoextend      |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

数据库初始化时设置共享表空间容量建议：

序号版本信息建议说明
01MySQL 5.7设置共享表空间2~3个，大小建议512M或1G，最后一个定制为自动扩展
02MySQL 8.0设置共享表空间1个即可，大小建议512M或1G

共享表空间的初始设置方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 模拟初始化清理数据
[root@xiaoQ-01 ~]# /etc/init.d/mysqld stop
[root@xiaoQ-01 ~]# rm -rf /data/3306/data/*
# 模拟初始化配置文件
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend
# 模拟初始化操作命令
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 模拟初始化重启服务
[root@xiaoQ-01 ~]#  /etc/init.d/mysqld start

类型二：独立表空间 在数据库服务5.6版本中，针对用户数据，可以进行单独的存储管理，存储表的数据行和索引等相关信息； 独立表空间在各个版本之间的作用区别： 在数据库服务8.0版本前 用户表包含三个部分组成（表.ibd 表.frm ibdata1-全局数据字典信息存储）； 所以在8.0之前，如果想修改表数据结构信息(元数据修改)，都会修改frm和ibdata文件信息，每次更新都会锁表(元数据锁)； 因为要保证数据一致性，并且两个表均更新完，才能释放解锁，因此在8.0前修改元数据信息，要避开业务繁忙时间段； 在数据库服务8.0版本后 用户表数据进行统一存储（表.ibd）; 如果想修改表数据结构信息(元数据修改)，只会修改ibd文件信息； 此时不需要对两个表文件均更新，只要更新一个文件即可，因此对文件锁的代价降低了，降低了对业务的影响； 独立表空间管理： 表空间配置参数信息查看

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql > select @@innodb_file_per_table;
+---------------------------------+
| @@innodb_file_per_table |
+---------------------------------+
|                                            1 |
+---------------------------------+
1 row in set (0.00 sec)
-- 表示每个表就是一个独立文件，进行数据信息的独立存储，不建议进行修改，如果改为0就是所有数据统一存储在共享表空间
[root@xiaoQ-01 ~]#  ibd2sdi city.ibd
-- 可以看到文件中存储的元数据信息(数据字典信息)，并且数据库8.0之后不再有表对应的frm文件信息了
-- 在数据库5.7环境中，每个表数据信息会存储生成两个表 frm ibd
-- frm文件中存储数据表的数据字典信息(元数据信息)
-- ibd文件中存储数据行信息和索引信息

表空间配置参数信息修改

1
2

mysql > set global innodb_file_per_table=0
-- 设置为0表示利用共享表空间存储用户数据 1表示利用独立表空间存储用户数据

表空间企业应用案例： 案例01：利用独立表空间进行快速数据迁移（源端 3306/test/t100w –> 目标端 3307/test/t100w） 说明：可以在需要某个表中的数据信息时，可以将数据表的独立表空间数据信息做迁移，在另一个数据库中进行恢复即可 操作步骤一：锁定源端t100w表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

mysql > lock tables oldboy.t100w write;
-- 给t100w表加写数据锁
mysql > show create table oldboy.t100w;
CREATE TABLE `t100w` (
`id` int DEFAULT NULL,
`num` int DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `idx` (`k1`,`k2`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
-- 获取创建表结构数据信息

操作步骤二：目标端创建oldboy库和t100w空表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> create database oldboy;
-- 创建新的数据库
mysql > CREATE TABLE `t100w` (
`id` int DEFAULT NULL,
`num` int DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `idx` (`k1`,`k2`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 创建新的数据表

操作步骤三：单独删除空的表空间文件

1
2

mysql> alter table oldboy.t100w discard tablespace;
-- 删除t100w表的ibd数据文件信息，但是保留t100w的frm，ibdata1中关于t100w的系统数据

操作步骤四：拷贝源端ibd文件到目标端目录，并设置权限

1
2

[root@xiaoQ-01 ~]# cp /data/3306/data/oldboy/t100w.ibd /data/3307/data/oldboy/
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*

操作步骤五：导入表空间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> alter table oldboy.t100w import tablespace;
-- 在目标端加载识别迁移过来的数据文件信息
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.04 sec)
-- 查看数据表中是否有迁移古来的数据信息

操作步骤六：解锁源端数据表

1

mysql> unlock tables;

案例02：利用表空间迁移功能实现数据损坏恢复 说明：操作系统突然断电了，启动完成后 / 变为只读了，fsck修复文件系统，系统再次重新启动后，mysql启动不了了 结果：造成confulence库在、jira库不见了（备份没有 日志也没开） 服务：jira(bug追踪)、confluence(内部知识库) 、mysql 5.6.33(innodb引擎 使用独立表空间) – LNMT架构 硬件：联想服务器（8核 16G内存 500G存储空间 没有raid），centos 6.8系统

对话描述： 学生：这种情况怎么恢复？ 老师：有备份吗？ 学生：连二进制日志都没有，没有备份，没有主从 老师：jira数据库数据没什么办法了，只能进行硬盘数据恢复了 学生：jira数据库数据先不用关注，数据磁盘已经拉到中关村处理了 主要是confulence库还想使用，但将生成中的库目录，导入到其他主机上(var/lib/mysql)，无法直接访问数据库中数据？ /data/3306/data/confulence 老师有没有工具能直接读取数据库目录中的ibd文件内容 老师：我查查，最后发现没有 我们可以尝试下独立表空间迁移 create table xx alter table coufulence.t1 discard tablespace; alter table coufulence.t1 import tablespace; 虚拟环境测试可行 问题：confulence库中总共有107张表 困惑：如何创建107张和原来一模一样的表 解决：学生环境中有2016年的历史库，让学生利用mysqldump命令备份confulence历史库 mysqldump -uroot -ppassw0rd -B confulence –no-date > test.sql 只获取所有表结构信息 如果是自研数据库，没有备份怎么办 mysql工具包中，拥有mysqlfrm工具也可以读取frm文件获取表结构

操作步骤一：备份历史数据库的所有表结构信息，并进行恢复

1
2
3

[root@xiaoQ-01 ~]# mysqldump -uroot -ppassw0rd -B confulence --no-date > test.sql
mysql > create database confulence
mysql > source test.sql

操作步骤二：删除空表的独立表空间

1
2
3

select concat("alter table ",table_schema,".",table_name," discard tablespace;") from information_schema.tables where table_schema='confulence'; into outfile
'/tmp/discard.sql';
source /tmp/discard.sql

实际执行过程发现，有20-30张表无法成功，主外键关系问题，如果一个表一个表分析表结构，处理外键关系很痛苦

1
2

set foreign_key_checks=0
-- 跳过外键检查，从而把有问题的20-30张表的独立表空间也删除了

操作步骤三：拷贝生成中confulence库下的所有表的ibd文件到准备好的环境中并加载识别

1
2
3

select concat("alter table ",table_schema,".",table_name," import tablespace;") from information_schema.tables where table_schema='confulence' into outfile
'/tmp/ import.sql';
source /tmp/ import.sql

操作步骤四：进行数据信息验证 表都可以访问了，数据挽回了出现问题时刻的状态 案例03：mysql 5.7中误删除了ibdata1数据文件，导致数据库服务无法启动；（作业） 说明：如何恢复t100w表中数据，并且假设库中有100张表，而且表结构无法通过show create table获得； 提示：有可能是自研数据库，并且没有数据备份 思路：先获取表结构信息，然后重新建表，删除空表的独立表空间，导入表的数据文件，加载识别表数据信息 操作步骤一：mysql工具包中含有mysqlfrm工具，可以读取frm文件获得表结构；

1

[root@xiaoQ-01 ~]# ./mysqlfrm /data/3306/data/test/t100w.frm --diagnostic

操作步骤二：将新库中所有独立表空间进行删除

1
2
3

select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from informatin_schema.tables where table_schema='confluence' into outfile
'/tmp/discard.sql';
source /tmp/discard.sql

类型三：undo表空间 利用undo表空间主要用来完成撤销工作（回滚操作）； 在数据库5.7版本中，默认存储在共享表空间中（ibdata）；在数据库8.0版本后，默认就是独立存储了(undo_001-undo_002)； 在实际生产环境中，建议在5.7版本之后，都将undo表空间进行独立文件存储； undo表空间管理： 表空间配置参数信息查看

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

mysql> select @@innodb_undo_tablespaces;
+-------------------------------------------+
| @@innodb_undo_tablespaces       |
+-------------------------------------------+
|                                                         2 |
+-------------------------------------------+
1 row in set (0.00 sec)
-- 确认是否打开独立undo模式，并设置undo表空间文件个数（3-5个）
mysql> select @@innodb_max_undo_log_size;
+-------------------------------------------+
| @@innodb_max_undo_log_size     |
+-------------------------------------------+
|                                       1073741824 |
+-------------------------------------------+
1 row in set (0.00 sec)
-- 表示undo日志信息的大小，默认1G
mysql> select @@innodb_undo_log_truncate;
+-------------------------------------------+
| @@innodb_undo_log_truncate      |
+-------------------------------------------+
|                                                          1 |
+-------------------------------------------+
1 row in set (0.00 sec)
-- 表示开启undo自动回收的机制（undo purge）
mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------------------------+
| @@innodb_purge_rseg_truncate_frequency   |
+----------------------------------------------------------+
|                                                                         128 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
-- 触发自动回收的条件，单位是检测次数

官方参数使用说明（important）： The number of undo tablespaces can only be configured when initializing a MySQL instence and is fixed for the life of the instance； undo表空间的数量只能在初始化MySQL实例时配置，并且在实例生命周期内是固定的 表空间配置参数修改调整 修改数据库5.7版本服务的undo表空间，实现undo表空间的独立存储； 查看获取数据库5.7版本的默认undo配置情况：

1
2
3
4
5
6
7
8

mysql> select @@innodb_undo_tablespaces;
+--------------------------------------+
| @@innodb_undo_tablespaces |
+--------------------------------------+
|                                                  0 |
+--------------------------------------+
1 row in set (0.00 sec)
-- 在数据库5.7版本中，undo表空间默认并未实现独立存储；

关闭数据库服务程序，对undo表空间进行独立存储配置：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# 关闭数据库服务程序，清理数据库服务数据目录
[root@xiaoQ-01 ~]# systemctl stop mysqld3357
[root@xiaoQ-01 ~]# systemctl is-active mysqld3357
unknown
-- 关闭数据库服务程序
[root@xiaoQ-01 ~]# rm -rf /data/3357/data/*
-- 删除清空数据库数据目录
# 编写修改数据库服务配置文件
[root@xiaoQ-01 ~]# vim /data/3357/my.cnf
[mysqld]
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
-- 在数据库服务端添加以上参数信息

重新初始化数据库服务程序：

1
2
3
4
5
6

[root@xiaoQ-01 ~]#  /usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf --initialize-insecure  --basedir=/usr/local/mysql57 --datadir=/data/3357/data
--user=mysql
[root@xiaoQ-01 ~]# ll /data/3357/data/undo*
-rw-r----- 1 mysql mysql 10485760 11月 15 10:19 /data/3357/data/undo001
-rw-r----- 1 mysql mysql 10485760 11月 15 10:19 /data/3357/data/undo002
-rw-r----- 1 mysql mysql 10485760 11月 15 10:19 /data/3357/data/undo003

重新启动数据库服务程序：

1
2
3

[root@xiaoQ-01 ~]# systemctl start mysqld3357
[root@xiaoQ-01 ~]# systemctl is-active mysqld3357
active

实现undo表空间文件指定目录存储

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 将数据库服务进行关闭
[root@xiaoQ-01 ~]# systemctl stop mysqld3357
# 编写数据库服务配置文件
[root@xiaoQ-01 ~]# vim /data/3357/my.cnf
[mysqld]
innodb_undo_directory=/data/3357/undologs
# 创建存储undo表空间文件目录
[root@xiaoQ-01 ~]# mkdir -p /data/3357/undologs
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
[root@xiaoQ-01 ~]# cp -a /data/3357/data/undo* /data/3357/undologs/
# 将数据库服务进行启动
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3357.sock
mysql > select @@innodb_undo_directory;
+-------------------------------------+
| @@innodb_undo_directory    |
+-------------------------------------+
| /data/3357/undologs               |
+-------------------------------------+
1 row in set (0.00 sec)

说明：对于数据库8.0版本，在进行undo表空间配置信息调整的时候，可以进行在线调整； 数据库8.0 undo表空间与数据库5.7undo表空间区别资料： https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html =============================================================================================== 数据库8.0独立表空间设置扩展：Adding Undo Tablespaces Because undo logs can become large during long-running transactions, creating additional undo tablespaces can help prevent individual undo tablespaces from becoming too large. A s of MySQL 8.0.14, additional undo tablespaces can be created at runtime using CREATE UNDO TABLESPACE syntax.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

# 创建新的独立的undo表空间文件
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';
# 查看已经创建的独立的undo表空间文件
mysql> select tablespace_name,file_name from information_schema.files where file_type like 'undo log';
+---------------------------+----------------+
| TABLESPACE_NAME    | FILE_NAME    |
+---------------------------+----------------+
| innodb_undo_001      | ./undo_001   |
| innodb_undo_002      | ./undo_002   |
| tablespace_name       | ./xiaoQ1.ibu |
+---------------------------+----------------+
3 rows in set (0.00 sec)
# 删除已有的独立的undo表空间文件
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
-- 将指定的undo表空间信息设置为失效
DROP UNDO TABLESPACE tablespace_name;
-- 删除指定的undo表空间信息
# 查看已有的独立的undo表空间状态
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME LIKE 'tablespace_name';

=============================================================================================== 类型四：temp表空间 临时表空间主要用于存储临时表信息，主要是在使用group by，order by，having，unique all，子查询等情况都会使用临时表； 临时表可以存储在内存和磁盘上； 临时表空间管理： 扩容临时表空间操作： 扩容前临时表空间信息查看：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

mysql> select @@innodb_temp_data_file_path;
+--------------------------------------------+
| @@innodb_temp_data_file_path     |
+--------------------------------------------+
| ibtmp1:12M:autoextend                    |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_autoextend_increment;
+---------------------------------------------+
| @@innodb_autoextend_increment |
+---------------------------------------------+
|                                                         64 |
+---------------------------------------------+
1 row in set (0.00 sec)
-- 查看参数信息说明：ibtmp1文件，默认初始大小12M，不够用会自动扩展，默认每次扩展64M

临时表空间的扩容操作方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 编写数据库配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:120M:autoextend:max:500M
-- 需要注意的是ibdata1文件大小必须和实际数据库要存储的数据相匹配，否则会出现如下报错信息
# 查看配置信息是否生效
mysql> select @@innodb_temp_data_file_path;
+---------------------------------------------------------------------+
| @@innodb_temp_data_file_path                                       |
+---------------------------------------------------------------------+
| ibtmp1:12M;ibtmp2:120M:autoextend:max:500M             |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

说明：建议数据初始化之前设定好临时表空间，建议23个，大小512M1G； 数据库初始化时设置临时表空间容量建议：

序号版本信息建议说明
01MySQL 5.7设置共享表空间2~3个，大小建议512M或1G，最后一个定制为自动扩展
02MySQL 8.0设置共享表空间1个即可，大小建议512M或1G

临时表空间的初始设置方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 模拟初始化清理数据
[root@xiaoQ-01 ~]# /etc/init.d/mysqld stop
[root@xiaoQ-01 ~]# rm -rf /data/3306/data/*
# 模拟初始化配置文件
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:120M:autoextend:max:500M
# 模拟初始化操作命令
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 模拟初始化重启服务
[root@xiaoQ-01 ~]#  /etc/init.d/mysqld start

类型五：redo事务日志 redo log属于事务重做日志文件，主要用于记录内存数据页的变化（记录在内存中对数据页的操作信息），都会以日志文件方式记录； 可以实现"前进"（WAL-write ahead log）的功能，数据库会保证redo操作日志优先于数据写入磁盘，加快了事务提交效率(提高并发)； 在进行日志信息记录时，采用的是顺序IO，而数据存储时采用的异步IO（随机IO）； redo事务日志的存储路径为：默认存储在数据库服务的数据目录下，默认大小为48M

1
2
3
4
5

[root@xiaoQ-01 data]# pwd
/data/3306/data
[root@xiaoQ-01 data]# ll ib_log*
-rw-r----- 1 mysql mysql 50331648 11月 15 11:34 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 11月 15 11:27 ib_logfile1

redo事务日志管理： 进行redo事务日志操作： redo事务日志配置信息查看：

1
2
3
4
5
6
7
8
9

mysql> show variables like '%innodb_log_file%';
+-----------------------------------+---------------+
| Variable_name                       | Value          |
+-----------------------------------+---------------+
| innodb_log_file_size             | 50331648   |
| innodb_log_files_in_group  | 2                  |
+-----------------------------------+---------------+
2 rows in set (0.00 sec)
-- 在实际生产环境中，建议大小为512M~4G，应用组数为2~4组（写入数据过程轮询写入）

redo事务日志配置操作方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 编写数据库配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_log_file_size=100M
innodb_log_files_in_group=3
# 确认配置信息是否已经生效
[root@xiaoQ-01 data]# /etc/init.d/mysqld restart
[root@xiaoQ-01 data]# ll /data/3306/data/ ib_log*
-rw-r----- 1 mysql mysql 104857600 11月 15 15:12 /data/3306/data/ ib_logfile0
-rw-r----- 1 mysql mysql 104857600 11月 15 15:12 /data/3306/data/ ib_logfile1
-rw-r----- 1 mysql mysql 104857600 11月 15 15:12 /data/3306/data/ ib_logfile2

类型六：ib_buffer_pool预热文件 ib_buffer_pool预热文件可以用于缓冲和缓存，可以存储"热"数据页(经常查询或修改的数据页)，从而减少物理IO消耗； 从数据库5.7版本开始，数据库正常关闭后，内存中存储的数据页缓冲或缓存信息均会失效，重新启动后还会消耗IO获取相应数据页信息； 为了可以尽量减少磁盘IO的消耗，可以将内存中的热数据页信息存储在ib_buffer_pool文件中； 数据库服务再次启动后，会直接读取ib_buffer_pool文件中信息，并将读取的信息加载到内存中，最终减少随机IO数量；

说明：存在ib_buffer_pool预热文件后，有可能在数据库服务关闭时比较耗费一些时间，但实际环境数据库服务关闭情况较少； 类型七：Doublewrite Buffer(DWB)文件 DWB文件主要作用是：mysql process crash in the middle of a page write（在数据库服务存储时，数据页写了一半）； 数据库Innodb可以找到一个好的数据页副本从Doublewrite Buffer文件中，主要是避免数据信息出现损坏； MySQL数据库最小IO存储单元是page(16kB)，OS系统中最小的IO存储单元是block(4kB)，OS也可以称为存储子系统； 会出现一个问题：数据库系统与操作系统的存储关系问题，在数据库中写入一个数据页时，在文件系统层面可能只是写入了2个block；

图片

图片

问题解决思路： 在数据库8.0.19之前，默认在ibdataN文件中进行存储，在数据库8.0.20以后，可以进行独立文件存储；

1
2
3

[root@xiaoQ-01 data]# ll *ib_16384*
-rw-r----- 1 mysql mysql   196608 11月 15 15:14 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql 8585216 11月 15 11:27 #ib_16384_1.dblwr

02 In-Memor y Structures（内存结构部分） 在内存结构中也是包含很多的组成部分，主要的组成部分有： 组成部分一：InnoDB Buffer Pool（IBP） Buffer Pool内存存储区域主要用来缓冲或缓存数据库服务的数据页和索引页，是MySQL中最大的、最重要的内存区域； Buffer Pool内存空间管理： Buffer Pool配置参数信息查看：

1
2
3
4
5
6
7
8

mysql> select @@innodb_buffer_pool_size;
+---------------------------------------+
| @@innodb_buffer_pool_size    |
+---------------------------------------+
|                                    134217728 |
+---------------------------------------+
1 row in set (0.00 sec)
-- buffer pool默认内存空间大小为128M，生产建议大小可以设置为物理内存总量的50%~80%

Buffer Pool配置参数修改方法：

1
2
3
4
5
6
7
8

# 配置信息临时调整
mysql > set global innodb_buffer_pool_size=268435456;
-- 配置调整后，重新登录mysql数据库生效
# 配置信息永久调整
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=256M
-- 配置调整后，重新启动mysql数据库生效

组成部分二：InnoDB Log Buffer（ILB） Log Buffer内存存储区域主要用来缓冲 redo log日志信息； Log Buffer内存空间管理： Log Buffer配置参数信息查看：

1
2
3
4
5
6
7
8

mysql> select @@innodb_log_buffer_size;
+--------------------------------------+
| @@innodb_log_buffer_size     |
+--------------------------------------+
|                                    16777216 |
+--------------------------------------+
1 row in set (0.00 sec)
-- log_buffer默认内存空间大小为16M，生产建议大小可以设置为innodb_log_file_size文件大小的 1-N倍（后续说明）

Log Buffer配置参数修改方法：

1
2
3
4
5
6
7
8

# 配置信息临时调整
mysql > set global innodb_log_buffer_size=33554432;
-- 配置调整后，重新登录mysql数据库生效
# 配置信息永久调整
[root@xiaoQ-01 ~]# vim /etc/my.cnf
[mysqld]
innodb_log_buffer_size=32M
-- 配置调整后，重新启动mysql数据库生效

存储引擎体系结构总览：（事务工作流程前讲解）

图片