1.介绍
存储引擎MySQL中的“文件系统”
2.种类:
2.1查看MYSQL的自带存储引擎
mysql> show engines;
InnoDB ******
MyISAM
MRG_MYISAM
CSV
BLACKHOLE
PERFORMANCE_SCHEMA
ARCHIVE
MEMORY
FEDERATED
面试题:请你列举MYSQL中支持的存储引擎的种类:
INNODB MYISAM CSV MEMORY
2.2分支产品的引擎种类介绍
Percona、Mariadb
TokuDB、MyRocks、Rocksdb
特点:
1. 压缩比15倍以上
2. 插入数据性能
适应场景:例如Zabbix监控类的平台、归档库、历史数据存储业务
3.InnoDB存储引擎特性
MVCC ： 多版本并发控制
聚簇索引 ： 用来组织存储数据和优化查询，IOT。
支持事务 ： 数据安全保证
支持行级锁 ： 控制并发
外键
多缓冲区支持
自适应Hash索引： AHI
复制中支持高级特性。
备份恢复： 支持热备。
自动故障恢复：CR Crash Recovery
双写机制：DWB Double Write Buffer
面试题: InnoDB 核心特性有哪些？ InnoDB和MyISAM区别有哪些？
InnoDB 支持： 事务、MVCC、聚簇索引、外键、缓冲区、AHI、CR、DWB，MyISAM不支持。
InnoDB 支持： 行级锁，MyISAM支持表级锁。
InnoDB 支持热备（业务正常运行，影响低），MyISAM支持温备份（锁表备份）。
InnoDB 支持CR(自动故障恢复)，宕机自动故障恢复，数据安全和一致性可以得到保证。MyISAM不支持，宕机可能丢失当前修改。
4. 存储引擎的基本操作
-
存储引擎的基本操作
4.1.1 查询支持的存储引擎 mysql> show engines; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)4.1.3 查看、设定 表的存储引擎
查看某张表的存储引擎 mysql> show create table +表名 （2） 查询系统中所有业务表的存储引擎信息 mysql> select table_schema, table_name , engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); ------------------------------------------------------ +--------------+-----------------+--------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +--------------+-----------------+--------+ | school | course | InnoDB | | school | sc | InnoDB | | school | student | InnoDB | | school | teacher | InnoDB | | test | stu | InnoDB | | test | student | InnoDB | | wordpress | stu | InnoDB | | world | city | InnoDB | | world | country | InnoDB | | world | countrylanguage | InnoDB | +--------------+-----------------+--------+ 10 rows in set (0.01 sec) （3）创建表设定存储引擎 mysql> create table xxx (id int) engine=innodb charset=utf8mb4; （4）修改已有表的存储引擎 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.s5. InnoDB 存储引擎的体系结构 ******
5.1 磁盘结构 （on-disk）
5.1.1 表空间结构
介绍： 表空间的概念源于Oracle数据库。最初的目的是为了能够很好的做存储的扩容。
5.1.1 表空间结构
介绍： 表空间的概念源于Oracle数据库。最初的目的是为了能够很好的做存储的扩容。共享（系统）表空间
存储方式
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.11~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_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend |
+-------------------------+
1 row in set (0.01 sec)
###查看可扩容共享表空间的大小 默认可扩容64k
mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
| 64 |
+-------------------------------+
初始化数据库之后进行数据库的扩容:
第一步:
vim /etc/my.cnf
image-20201111203516330.png
查看ibdata的初始化大小
du -h /data/3306/data/ibdata1
错误案例的分析 将ibdata的空间大小改成与当前的ibdata大小不一致
第一步:重启数据库进行查看错误
image-20201111204353823.png
错误分析: 查看数据库的日志
image-20201111205440067.png
-
正确的设置大小
先查看实际大小： [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在初始化数据库的时候进行ibdata的设置:
### 模拟在初始化时设置共享表空间（生产建议） 5.7 中建议：设置共享表空间2-3个，大小建议1G或者4G，最后一个定制为自动扩展。 8.0 中建议：设置1-2个就ok，大小建议1-4G模拟初始化数据库是设置表空间
第一步:停止数据库
/etc/init.d/mysqld stop\
第二步:清理配置文件
[root@db01 data]# rm -rf /data/3306/data/*
root@db01 data]# vim /etc/my.cnf # 修改 innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend第三步:重新初始化
[root@db01 data]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data第四步:重启数据库生效
[root@db01 data]# /etc/init.d/mysqld start独立表空间
介绍
5.6版本开始，针对用户数据，单独的存储管理。存储表的数据行和索引。
8.0 之前： city ---> city.ibd city.frm ibdata1
8.0 之后 city city.ibd通过参数进行查看独立表空间
mysql> select @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ 测试： 共享表空间存储用户数据 mysql> set global innodb_file_per_table=0;###在数据库的默认状态下用户的数据会存入在独立表空间
利用独立表空间进行快速数据迁移
第一步:进行锁表要迁移的表
flush tables test.t100w with read lock ;
第二步:在目标端创建跟第一个表一模一样的表
show create table t100w; ###查看原来表的属性
第三步在目标的数据库进行创建数据库:
create database test;
复制原来表的属性进行创建表
第四步:删除目标表的空间文件
mysql> alter table test.t100w discard tablespace;
第五步:拷贝源端ibd文件到目标端目录，并设置权限
[root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db01 test]# chown -R mysql.mysql /data/*
第六步:导入空间表(在目标端进行导入空间表)
mysql> alter table test.t100w import tablespace;
第七步:解锁源端数据表
mysql> unlock tables;
