存储引擎基础
常见数据库引擎
数据库存储引擎是数据库管理系统的核心组件，负责数据的存储、检索、更新和管理，决定了数据库的性能、事务支持和存储结构（数据信息有序的存储和调取）
-- 查看数据库可应用的存储引擎 mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
MySQL核心存储引擎有哪些⭐⭐⭐⭐⭐
InnoDB： 默认的、也是最核心的存储引擎。它支持ACID事务、行级锁、外键约束，提供了良好的并发性能和崩溃恢复能力，适用于绝大多数需要事务支持的应用场景 MyISAM： MySQL 5.5版本之前的默认引擎。特点是不支持事务和行级锁（只有表锁），但访问速度快，支持全文索引。适合只读或读多写少、不需要事务的场景 Memory： 将所有数据存储在RAM中，速度极快。但重启后数据会丢失，适用于临时表、缓存或会话存储等临时性数据 CSV： 它的表实际上是带有逗号分隔值的文本文件。允许我们直接编辑CSV文件来操作数据，非常适合作为数据交换的格式，但缺乏索引等高级功能 Archive： 顾名思义，专为高速插入和压缩而设计，支持压缩表，非常适合存储大量的归档数据。但它只支持INSERT和SELECT操作，不支持删除和更新 # MySQL分支产品的存储引擎？ Percona Server 和 MariaDB 这两个最流行的MySQL分支，都集成了TokuDB和MyRocks： TokuDB：使用Fractal Tree索引结构，数据压缩比极高（通常可达10倍以上）、大量数据插入性能非常好、支持在线DDL操作。
但它在复杂查询方面的性能有时不如InnoDB MyRocks：由Facebook开发，旨在替代InnoDB。它的核心优势是更高的压缩比和更低的写放大(通过顺序写入代替随机写入)。
这意味着它能节省大量存储空间，并在写入密集型负载（尤其是SSD硬盘上）表现优异。 以上存储引擎就比较适合于zabbix监控类的平台，归档数据、历史数据存储业务等，数据量级比较大的情况 监控服务部署tokuDB存储引擎参考链接：https://www.cnblogs.com/oldboy-heqing/articles/16891210.html
MySQL分支产品的存储引擎⭐⭐⭐⭐⭐
Percona Server 和 MariaDB 这两个最流行的MySQL分支，都集成了TokuDB和MyRocks：
TokuDB：使用Fractal Tree索引结构，数据压缩比极高（通常可达10倍以上）、大量数据插入性能非常好、支持在线DDL操作。
但它在复杂查询方面的性能有时不如InnoDB
MyRocks：由Facebook开发，旨在替代InnoDB。它的核心优势是更高的压缩比和更低的写放大(通过顺序写入代替随机写入)。
这意味着它能节省大量存储空间，并在写入密集型负载（尤其是SSD硬盘上）表现优异。
以上存储引擎就比较适合于zabbix监控类的平台，归档数据、历史数据存储业务等，数据量级比较大的情况
监控服务部署tokuDB存储引擎参考链接：https://www.cnblogs.com/oldboy-heqing/articles/16891210.html
数据库存储引擎特征（高性能、高并发、高可靠性）⭐⭐⭐⭐⭐
| 特性 | 解释说明 |
|---|---|
| 数据访问特性（并发控制能力） | 支持多版本并发控制特性(MVCC)，支持行级锁控制并发 |
| 数据索引特性（高效查询能力） | 支持聚簇索引/辅助索引特性，可以组织存储数据和优化查询(IOT：组织索引表/索引组织表) |
| 数据事务特性（数据可靠性基石） | 支持事务概念特性，可以实现数据的安全保证 |
| 数据缓冲特性（性能加速能力） | 支持多缓冲区功能，自适应hash索引(AHI) |
| 数据迁移特性（数据流通与高可用能力） | 支持复制数据中的高级功能特性，支持数据备份恢复的热备 |
| 服务自愈特性（崩溃恢复能力） | 支持自动故障恢复(CR-Crash Recovery) |
| 数据存储特性（数据安全写入能力） | 支持数据双写机制(Double write) 数据存储有关的安全机制 |
- MVCC (多版本并发控制) ：通过为行数据创建多个快照版本，使得读写操作互不阻塞，读不阻塞写，写不阻塞读，从而极大的提升了系统的并发吞吐量
- 行级锁：精确地对需要修改的数据行进行加锁，最大程度地减少了锁冲突
- 事务：一组不可分割的SQL操作集合，必须完全满足 ACID（原子性、一致性、隔离性、持久性）属性
- 缓冲池（Buffer Pool）：主内存中的一块区域，用于缓存数据和索引页（内存访问速度比磁盘快几个数量级，缓冲池通过减少磁盘I/O来极大提升性能）
- 自适应哈希索引 (AHI) ：会自动为频繁查询的索引页在内存中建立哈希索引，进一步提升查询速度
- 高级复制：通过半同步、组复制等技术，在基础数据同步之上，提供更强的一致性保证、自动故障切换和高可用集群能力的数据库核心功能
- Crash Recovery 机制：数据库重启时，会利用重做日志 (Redo Log) 重放(checkpoint)之后已提交的事务，并利用回滚日志 (Undo Log) 回滚未提交的事务。
- Checkpoint（检查点）：为了防止Redo Log无限增长，数据库会定期地将内存中已修改的脏页刷新到磁盘的数据文件中，并在这个时间点做一个标记。恢复时只需要从最后一个Checkpoint之后开始重放Redo Log即可，大大提高了恢复效率。
- 双写机制 (Doublewrite Buffer)：在将数据页写入磁盘数据文件之前，InnoDB会先将它们写入磁盘上的一个特定区域（双写缓冲区），然后再写入正式位置。防止因电源等故障导致部分页写入，从而引起数据损坏。
InnoDB 与 MyISAM 核心区别总结⭐⭐⭐⭐⭐
| 特性 | InnoDB | MyISAM | 意味着什么 |
|---|---|---|---|
| 事务 (Transaction) | 支持 | 不支持 | InnoDB 能保证数据的原子性和一致性，适合金融、订单等核心业务。 |
| 行级锁 (Row-level Lock) | 支持 | 只有表级锁 | InnoDB 在高并发写操作时性能远高于 MyISAM，不会锁住整个表。 |
| 外键 (Foreign Keys) | 支持 | 不支持 | InnoDB 能在数据库层面保证数据的参照完整性。 |
| MVCC (多版本并发控制) | 支持 | 不支持 | InnoDB 实现了非阻塞读，大大提升了并发性能。 |
| 聚簇索引 (Clustered Index) | 支持 | 非聚簇索引 | InnoDB 的表数据文件本身就是按主键组织索引的，主键查询极快。 |
| Crash Recovery (崩溃恢复) | 支持 | 不支持 | InnoDB 有 Redo Log，宕机后能自动恢复数据，保证数据安全。 |
| 双写缓冲区 (DoubleWrite) | 支持 | 不支持 | 防止因部分页写入导致的数据损坏， another layer of data safety. |
| 自适应哈希索引 (AHI) | 支持 | 不支持 | 自动优化频繁查询，提升查询速度。 |
| 数据缓冲 (Buffer Pool) | 支持 | 只缓存索引 | InnoDB 同时缓存数据和索引，极大减少磁盘 I/O，提升性能。 |
| 备份与恢复 | 支持热备 | 支持温备(需锁表) |
企业实际场景案例分析⭐⭐⭐
案例一：zabbix运行卡顿且存储空间爆满
平台环境：zabbix3.2 + centos7.3 + mariadb5.5(innoDB引擎)，利用监控平台，监控了2000多个节点服务
问题现象：每隔一段时间zabbix服务运行操作时很卡，每隔3-4个月，都要重新部署zabbix，存储空间经常爆满(ibdata1 400~500G)
异常分析：
- zabbix版本过低，建议将zabbix程序进行升级更新
- zabbix使用的数据库版本过低，建议将数据库版本进行升级（新版本的数据库原生态环境比旧版本好）
- 在对于mariadb5.5版本的数据库，在没有做数据存储调配时，数据库所有数据都会保存到ibdata1文件中
- 在ibdata1文件中的数据空间，不会因为数据库中的数据删除，产生数据回缩效果(即空间不释放)
优化建议：
- zabbix程序原生态支持TokuDB，经过压力测试，5.7要比5.5数据库版本性能高出 2~3倍；
- 使用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
案例二：企业客户实际数据库需求
平台环境：centos 5.8+mysql 5.0版本，MyISAM存储引擎+网站架构LNMP，数据量50G左右
问题现象：业务并发压力大的时候，整体网站访问非常卡，还会出现数据库服务宕机情况，造成部分数据丢失
问题分析：
- MyISAM存储引擎具有表级锁，在高并发访问时，会频繁出现锁等待情况；
- MyISAM存储引擎不支持事务机制，在断电或宕机时，会有可能丢失数据信息；
优化建议：
- 数据库服务版本进行升级，从5.0升级到更高的版本；ok
- 数据库服务升级后，迁移所有表数据到新环境（表空间迁移），调整存储引擎为InnoDB；
- 数据库服务开启双1安全参数；
- 数据库服务进行重构主从架构
数据库存储引擎应用⭐⭐⭐
# 数据库存储引擎信息查看 # 查看数据库可用存储引擎 mysql> show engines; # 查看数据库默认存储引擎 mysql> select @@default_storage_engine; # 数据库存储引擎配置修改 # 临时修改 set global default_storage_engine=InnoDB;（退出当前会话生效） # 永久生效 [root@db01 ~]# vim /etc/my.cnf [mysqld] default_storage_engine=InnoDB -- 重启数据库服务生效 # 数据表存储引擎查看 # 查看某张表的存储引擎 mysql> show create table world.city; # 查看所有库所有表的存储引擎 mysql> select table_schema,table_name,engine from information_schema.tables; 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 表名(字段名 类型) engine=innodb charset=utf8mb4; # 修改表示设置存储引擎 mysql > alter table 表名 engine=myisam; mysql > alter table 表名 engine=innodb;
数据库存储引擎结构⭐⭐⭐⭐⭐
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
On-Disk Structures（磁盘结构部分）⭐⭐⭐⭐⭐
表空间的概念源于oracle数据库，最初的目的是为了能够更好的做存储的扩容；因此数据库的表空间技术类似磁盘管理的LVM技术；
共享(系统)表空间⭐⭐⭐⭐⭐
MySQL各版本系统表空间存储内容
属于数据库服务5.5版本时默认的表空间应用，具体数据存储方式为：ibdata1~ibdataN
| 数据库版本 | 存储数据 | 解释说明 |
|---|---|---|
| MySQL 5.5版本 | 系统相关数据 |
|
| 用户相关数据 |
数据表中数据清理后(delete\drop)，ibdata1也不会释放磁盘空间 |
|
| MySQL 5.6版本 | 系统相关数据 |
|
| 用户相关数据 | 共享表空间只存储系统数据，用户相关数据被独立存储(独立表空间) | |
| MySQL 5.7版本 | 系统相关数据 |
|
| 用户相关数据 | 共享表空间只存储系统数据，用户相关数据被独立存储(独立表空间) | |
| MySQL 8.0.11 | 系统相关数据 |
|
| 用户相关数据 | 共享表空间只存储系统数据，用户相关数据被独立存储(独立表空间) | |
| MySQL 8.0.20 | 系统相关数据 |
|
扩容共享表空间
- 查看扩展前共享表空间信息
-- 共享表空间文件 mysql> select @@innodb_data_file_path; -- ibdata1:12M:autoextend ibdata1：文件名 12M：文件的初始大小 autoextend：表示当空间不足时自动扩展 -- 自动扩展步长 mysql> select @@innodb_autoextend_increment; -- 64 当需要自动扩展时，共享表空间文件（即配置了 autoextend 的那个文件）每次会增加 64MB 的磁盘空间
- 共享表空间扩容操作方法
# 停止mysql服务 [root@db01 ~]# systemctl stop mysqld # 检查现有ibdata1文件的精确大小 [root@db01 ~]# ls -lh /data/3306/data/ibdata1 12M# 修改配置文件 根据计算出的精确大小来配置，确保ibdata1:12M与实际文件大小完全一致 [root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend -- ibdata1:固定大小12M -- ibdata2:初始大小10M，当表空间不足时自动扩展 -- 只有最后一个文件可以设置为autoextend # 启动mysql服务 [root@db01 ~]# systemctl start mysqld
共享表空间初始化设置
| 版本信息 | 数据库初始化设置共享表空间建议 |
|---|---|
| MySQL 5.7 | 设置共享表空间2~3个，大小建议512M或1G，最后一个定制为自动扩展 |
| MySQL 8.0 | 设置共享表空间1个即可，大小建议512M或1G |
# 模拟初始化清理数据 [root@db01 ~]# /etc/init.d/mysqld stop [root@db01 ~]# rm -rf /data/3306/data/* # 模拟初始化配置文件 [root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextend -- ibdata1:固定大小100M -- ibdata2:初始大小100M，当表空间不足时自动扩展 -- 只有最后一个文件可以设置为autoextend # 模拟初始化操作命令 [root@db01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data # 模拟初始化重启服务 [root@db01 ~]# /etc/init.d/mysqld start
独立表空间⭐⭐⭐⭐⭐
MySQL 8.0 前后表组成文件对比与元数据锁（MDL）优化
.ibd核心数据文件（包括表的数据行和所有索引） .frm表结构定义文件（存储了表的元数据或结构定义） 从mysql8.0开始，.frm文件被彻底移除，表结构信息被整合并存储在系统数据字典 # 在数据库服务8.0版本前 用户表包含三个部分组成（表.ibd 表.frm ibdata1-全局数据字典信息） 修改表结构会更新frm与ibdata文件并获取元数据锁(元数据锁锁表) 为确保数据一致性，需待事务完成后才释放锁(frm和ibdata文件更新完成)，故需避开业务高峰期操作 # 在数据库服务8.0版本后 用户表包含两个部分组成（表.ibd mysql.ibd） 修改表数据结构信息(元数据修改)，只会修改mysql.ibd文件信息,元数据锁持有时间变短，降低了对业务的影响
独立表空间管理
- 表空间配置参数信息查看
mysql > select @@innodb_file_per_table; -- 1 -- 1：每个表就是一个独立文件，进行数据信息的独立存储，不建议进行修改 -- 0：所有数据统一存储在共享表空间 [root@db01 ~]# ibd2sdi /data/3306/data/world/city.ibd -- 可以看到文件中存储的元数据信息(数据字典信息)，并且数据库8.0之后不再有表对应的frm文件信息了 -- 在数据库5.7环境中，每个表数据信息会存储生成两个表 frm ibd -- frm文件中存储数据表的数据字典信息(元数据信息) -- ibd文件中存储数据行信息和索引信息
- 表空间配置参数信息修改
mysql > set global innodb_file_per_table=0 -- 设置为0表示利用共享表空间存储用户数据 1表示利用独立表空间存储用户数据
表空间企业应用案例⭐⭐⭐⭐⭐
案例01：利用独立表空间进行快速数据迁移
源端 3306/world/city.ibd --> 目标端 3307/world/city.ibd
说明：在需要某个表中的数据信息时，可以将数据表的独立表空间数据信息做迁移，在另一个数据库中进行恢复
- 步骤一：锁定源端city表
mysql > lock tables world.city write; -- 给city表加写数据锁 mysql > show create table world.city; -- 获取创建表结构数据信息 CREATE TABLE `city` ( `ID` int NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), KEY `idx1` (`CountryCode`,`Population`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- 步骤二：目标端创建world库和city空表
mysql > create database world; mysql > create table world.city; CREATE TABLE `city` ( `ID` int NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), KEY `idx1` (`CountryCode`,`Population`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- 步骤三：单独删除源端空表的表空间文件
mysql> alter table world.city discard tablespace; -- 删除 city 表的用户表空间文件（.ibd 文件）
-- 保留其在 MySQL 数据字典（Data Dictionary）中的表结构定义（元数据）
- 步骤四：拷贝源端ibd文件到目标端目录，并设置权限
[root@db01 ~]# cp /data/3306/data/world/city.ibd /data/3307/data/world/ [root@db01 ~]# chown -R mysql.mysql /data/*
- 步骤五：导入表空间
-- 在目标端加载并识别迁移过来的数据文件信息 mysql> alter table world.city import tablespace; -- 查看数据表中是否迁移数据 mysql> select count(*) from world.city;
- 步骤六：解锁源端数据表
mysql> unlock tables;
案例02：利用表空间迁移功能实现数据损坏恢复
说明：操作系统突然断电了，启动完成后/目录变为只读，通过fsck修复文件系统后再次重新启动，mysql启动不了 结果：confulence库还在、jira库丢失（备份没有 日志也没开） 服务：jira(bug追踪)、confluence(内部知识库)、mysql5.6.33(innodb引擎 使用独立表空间)、LNMT架构 硬件：联想服务器（8核 16G内存 500G存储空间 没有raid），centos 6.8系统 对话描述： yuanxiaojiang：有备份吗？ customer：连二进制日志都没有，没有备份，没有主从 yuanxiaojiang：jira数据库数据没什么办法了，只能进行硬盘数据恢复了 customer：jira数据库数据先不用关注，数据磁盘已经拉到中关村处理了 confulence库还想使用，但将生成中的库目录，导入到其他主机上(var/lib/mysql)，无法直接访问数据库中数据？ /data/3306/data/confulence yuanxiaojiang：可以尝试下独立表空间迁移 create table xx alter table coufulence.t1 discard tablespace; alter table coufulence.t1 import tablespace; 问题：confulence库中总共有107张表 困惑：如何创建107张和原来一模一样的表 解决：customer环境中有去年的历史库，让customer利用mysqldump命令备份confulence历史库 mysqldump -uroot -ppassw0rd -B confulence --no-date > test.sql 只获取所有表结构信息 没有备份如何解决 mysql工具包中，拥有mysqlfrm工具也可以读取frm文件获取表结构
- 步骤一：备份历史数据库的所有表结构信息，并进行恢复
[root@db01 ~]# mysqldump -uroot -p密码 -B confulence --no-date > test.sql mysqldump：MySQL官方提供的用于逻辑备份的核心命令行工具。它通过连接数据库，将数据库的结构和数据生成一系列SQL语句，并导出到一个文件 -B：不加则mysqldump会认为confulence是当前所用数据库中的表，加mysqldump会认为confulence是数据库 --no-data：只导出数据库表结构，而不导出表中的数据 重定向到当前目录下的 test.sql 文件中 mysql > create database confulence mysql > source test.sql
- 步骤二：删除空表的额独立表空间
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张表无法成功，主外键关系问题 set foreign_key_checks=0 -- 跳过外键检查，从而把有问题的20-30张表的独立表空间也删除了
- 步骤三：拷贝生成中confulence库下的所有表的ibd文件到准备好的环境中并加载识别
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数据文件，导致数据库服务无法启动
背景：数据库使用了独立表空间（innodb_file_per_table=ON），ibdata1文件丢失，但.frm和.ibd文件存在
说明：如何恢复world表中数据，假设库中有100张表，而且表结构无法通过show create table获得；
思路：先获取表结构信息，然后重新建表，删除空表的独立表空间，导入表的数据文件，加载识别表数据信息
- 步骤一：创建一个新的mysql5.7实例
# 创建mysql5.7实例，在新实例中必须确保配置一致 innodb_file_per_table=ON innodb_force_recovery=0 # 正常模式 # 复制文件时需要保持正确的所有权和权限 [root@db01 ~]# mkdir /tmp/frm_output/ [root@db01 ~]# chown -R mysql:mysql /new_mysql_data_dir/world/
- 步骤二：通过mysqlfrm工具读取frm文件获得表结构
# 将原 world 数据库目录下的所有 .frm 文件复制到新实例对应的数据目录下 /data/3357/data/world/*.frm---->/new_mysql_data_dir/world/ # 编写一个shell脚本 for frm_file in /data/3357/data/world/*.frm do table_name=$(basename "$frm_file" .frm) # 提取表名 mysqlfrm ${frm_file} --diagnostic > "/tmp/frm_output/${table_name}.sql" done mysqlfrm：从.frm二进制文件中反编译出表的create table语句 /data/3306/data/test/city.frm 指定要分析的.frm文件绝对路径 --diagnostic 是一种“强力”恢复模式，用于在极端情况下尽最大可能提取信息 mysqlfrm一次只能处理一个.frm文件，可用通过for循环来实现批量操作
- 步骤三：新的库中创建表
# 创建world数据库 CREATE DATABASE world;
# 在编写一个shell脚本实现批量创建表 for sql_file in /tmp/frm_output/*.sql do (echo "USE world;"; cat "$sql_file") | mysql -uroot -p done
- 步骤四：将新库中所有独立表空间进行删除
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
- 步骤五：拷贝源端ibd文件到目标端目录，并设置权限
[root@db01 ~]# cp /data/3357/data/world/*.ibd /new_mysql_data_dir/world/ [root@db01 ~]# chown -R mysql.mysql /new_mysql_data_dir/world/*.ibd
- 拷贝生成中world库下的所有表的ibd文件到准备好的环境中并加载识别
-- 生成import语句 select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema='world' into outfile '/tmp/import.sql'; -- 执行import source /tmp/import.sql;
undo表空间（撤销表空间）⭐⭐⭐⭐⭐
- undo表空间主要用来完成撤销工作（回滚操作）
- 在数据库5.7版本中，默认存储在共享表空间中（ibdata）
- 在数据库8.0版本后，默认就是独立存储了(undo_001、undo_002)
- 在实际生产环境中，建议在5.7版本之后，都将undo表空间进行独立文件存储
- undo表空间的数量只能在初始化MySQL实例时配置，并且在实例生命周期内是固定的
- 对于数据库8.0版本，在进行undo表空间配置信息调整的时候，可以进行在线调整；
undo表空间常见配置参数
-- 确认是否打开独立undo模式，并设置undo表空间文件个数（推荐3-5个） mysql> select @@innodb_undo_tablespaces; -- 整个undo表空间允许的最大空间值（默认为1G） mysql> select @@innodb_max_undo_log_size; -- 开启undo自动回收机制（undo purge） mysql> select @@innodb_undo_log_truncate; -- 触发自动回收的条件（单位是检测次数） Purge pass(Purge线程)每完成N次工作，就会区检查是否有undo表空间可以被阶段并释放空间个操作系统（事务提交或回滚会触发） mysql> select @@innodb_purge_rseg_truncate_frequency;
undo表空间配置参数修改调整
由于mysql5.7的undo表空间未独立，以此为实验进行独立操作 # 关闭数据库服务程序，清理数据库服务数据目录 [root@db01 ~]# systemctl stop mysqld57.service [root@db01 ~]# rm -rf /data/3357/data/* # 修改配置文件 [root@db01 ~]# vim /etc/my57.cnf -- 添加以下参数 [mysqld] innodb_undo_tablespaces=3 innodb_max_undo_log_size=128M innodb_undo_log_truncate=ON innodb_purge_rseg_truncate_frequency=32 # 重新初始化数据库服务程序 [root@db01 ~]# /usr/local/mysql57/bin/mysqld --defaults-file=/etc/my57.cnf --initialize-insecure --basedir=/usr/local/mysql57 --datadir=/data/3357/data --user=mysql # 重新启动数据库服务程序 [root@db01 ~]# systemctl start mysqld57.service
undo表空间文件指定目录存储
# 将数据库服务进行关闭 [root@db01 ~]# systemctl stop mysqld57# 编写数据库服务配置文件 [root@db01 ~]# vim /etc/my57.cnf [mysqld] innodb_undo_directory=/data/3357/undologs # 创建存储undo表空间文件目录 [root@db01 ~]# mkdir -p /data/3357/undologs [root@db01 ~]# chown -R mysql.mysql /data/* [root@db01 ~]# cp -a /data/3357/data/undo* /data/3357/undologs/# 将数据库服务进行启动 [root@db01 ~]# mysql -S /tmp/mysql.sock mysql > select @@innodb_undo_directory; /data/3357/undologs
数据库8.0独立表空间扩展
数据库8.0 undo表空间与数据库5.7undo表空间区别资料：
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
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. As of MySQL 8.0.14, additional undo tablespaces can be created at runtime using CREATE UNDO TABLESPACE syntax. 由于长时间运行的事务可能导致撤销日志变得庞大，创建额外的撤销表空间有助于防止单个撤销表空间过大 从MySQL 8.0.14版本开始，可以使用 CREATE UNDO TABLESPACE 语法在运行时创建额外的撤销表空间
# 创建新的独立的undo表空间文件 CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu'; -- 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 | ./file_name.ibu | +---------------------------+-----------------+ # 删除已有的独立的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，子查询等情况都会使用临时表；
- 临时表可以存储在内存和磁盘上；
- 临时表空间中的数据，在不需要时(会话或事务结束)就会被自动清空或回收；
扩容临时表空间
- 扩容前临时表空间信息查看
-- 查看InnoDB默认临时表空间的数据文件路径、初始大小、自动扩展策略 mysql> select @@innodb_temp_data_file_path; -- ibtmp1:12M:autoextend -- 自动扩展每次扩展多少M mysql> select @@innodb_autoextend_increment; -- 64
- 临时表空间的扩容操作方法
# 编写数据库配置文件信息 vim /etc/my.cnf [mysqld] innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:120M:autoextend:max:500M # 查看配置信息是否生效 mysql> select @@innodb_temp_data_file_path; ibtmp1:12M;ibtmp2:120M:autoextend:max:500M
初始化设置临时表空间
- 数据库初始化时设置临时表空间容量建议
| 版本信息 | 建议说明 |
|---|---|
| MySQL 5.7 | 设置共享表空间2~3个，大小建议512M或1G，最后一个定制为自动扩展 |
| MySQL 8.0 | 设置共享表空间1个即可，大小建议512M或1G |
- 临时表空间的初始设置方法
# 模拟初始化清理数据 [root@db01 ~]# /etc/init.d/mysqld stop [root@db01 ~]# rm -rf /data/3306/data/* # 模拟初始化配置文件 [root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:120M:autoextend:max:500M # 模拟初始化操作命令 [root@db01 ~]# mysqld --initialize-insecure --user=mysql \
--basedir=/usr/local/mysql --datadir=/data/3306/data # 模拟初始化重启服务 [root@db01 ~]# /etc/init.d/mysqld start
redo事务日志（事务重做日志）⭐⭐⭐⭐⭐
redo log属于事务重做日志文件，主要用于记录内存数据页的变化（记录在内存中对数据页的操作信息），都会以日志文件方式记录；
数据库通过WAL(write ahead log)机制优先将redo日志以顺序IO方式持久化，避免了数据页随机写入的开销，从而显著提升了事务提交效率和系统并发性能
# redo事务日志配置信息查看 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组（写入数据过程轮询写入） # redo事务日志配置操作方法 # 编写数据库配置文件信息 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)文件
双写缓冲区通过先将数据页副本写入专用区域，再写入实际位置，确保了数据库崩溃时能用副本来恢复部分写入的损坏页，从而保障数据页写入的原子性和一致性
坏页是因写入中断导致数据页部分更新、校验失败的损坏状态（在数据库服务存储时，数据页写了一半）
数据库Innodb可以从Doublewrite Buffer文件中找到一个好的数据页副本，主要是避免数据信息出现损坏；
MySQL数据库最小IO存储单元是page(16kB)，OS系统中最小的IO存储单元是block(4kB)，OS也可以称为存储子系统；
会出现一个问题：数据库系统与操作系统的存储关系问题，在数据库中写入一个数据页时，在文件系统层面可能只是写入了2个block；
在数据库8.0.19之前，默认在ibdataN文件中进行存储，在数据库8.0.20以后，可以进行独立文件存储；
[root@db01 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
In-Memory Structures（内存结构部分）⭐⭐⭐⭐⭐
InnoDB Buffer Pool（IBP）
缓冲池是数据库主内存中的一个区域，用于缓存从数据文件中读取的表和索引数据页
- Buffer Pool配置参数信息查看：
mysql> select @@innodb_buffer_pool_size; +---------------------------+ | @@innodb_buffer_pool_size | +---------------------------+ | 134217728 | +---------------------------+ -- buffer pool默认内存空间大小为128M，生产建议大小可以设置为物理内存总量的50%~80%
- Buffer Pool配置参数修改方法：
# 配置信息临时调整 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配置参数信息查看：
mysql> select @@innodb_log_buffer_size; +--------------------------+ | @@innodb_log_buffer_size | +--------------------------+ | 16777216 | +--------------------------+ -- log_buffer默认内存空间大小为16M，生产建议大小可以设置为innodb_log_file_size文件大小的 1-N倍（后续说明）
- Log Buffer配置参数修改方法：
# 配置信息临时调整 mysql > set global innodb_log_buffer_size=33554432; -- 配置调整后，重新登录mysql数据库生效 # 配置信息永久调整 [root@xiaoQ-01 ~]# vim /etc/my.cnf [mysqld] innodb_log_buffer_size=32M -- 配置调整后，重新启动mysql数据库生效
浙公网安备 33010602011771号