十六、存储引擎

功能

存储引擎具有
1、数据读写
2、数据安全和一致性
3、提高性能
4、热备份
5、自动故障恢复
6、高可用方面支持

查看使用的存储引擎

常用的存储引擎类型(面试题)
InnoDB
MyISAM
MEMORY
CSV

对原有的存储引擎进行改进，也就是第三方存储引擎
第三方的存储引擎:
RocksDB
MyRocks
TokuDB
压缩比较高,数据的插入性能高.其他功能和InnoDB没差别

常用数据库使用的存储引擎
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB

InnoDB存储引擎特性
也就是MyISAM所不具备的特性
1、事务
2、行锁
3、MVCC: 多版本并发控制
4、外键
5、ACSR自动故障恢复
6、热备
7、复制(多线程并发,GTID,MTS)

查看存储引擎

查看支持的存储引擎

查看默认使用的存储引擎

创建表t11，查看创表语句会发现自动加载默认存储引擎

建表指定存储引擎

其他查看表的存储引擎方式

mysql> SHOW TABLE STATUS LIKE 'test'G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-02-10 17:10:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

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       | check_score        | NULL   |
| school       | course             | InnoDB |
| school       | score              | InnoDB |
| school       | student            | InnoDB |
| school       | teacher            | InnoDB |
| school       | test               | InnoDB |
| school       | vote_record        | InnoDB |
| school       | vote_record_memory | MEMORY |
+--------------+--------------------+--------+
8 rows in set (0.00 sec)

通过配置文件修改默认存储引擎

$ cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql5.7
datadir=/usr/local/mysql5.7/data
socket=/tmp/mysql.sock
server_id=6
port=3306
default_storage_engine=InnoDB #修改默认存储引擎
[mysql]
socket=/tmp/mysql.sock

修改存储引擎

修改表的存储引擎

mysql> alter table test engine=myisam;
mysql> show create table test;

当我们使用delect删除表数据时，会有很多碎片，时间久了就会影响查询性能，此时可以使用修改存储引擎的方式整理碎片，此方法仅限于使用InnoDB引擎。

#使用此命令进行碎片整理时需要在业务不繁忙的时间做，因为会有短暂的锁表情况，但锁表时间不长。
mysql> alter table test engine=InnoDB;

InnoDB物理存储结构

以MySQL5.7版本为例
InnoDB引擎存储方式由以下5种文件
1、ibdata1文件
也是就共享表空间文件，存储系统数据字典信息(即表的统计信息，类似于information_schema.table存储的表的属性状态数据行数引擎等信息)
undo表空间数据(主要用于事务回滚)

2、ib_logfile0~ib_logfile1文件
是存储引擎日志，存储redo日志文件，也叫重做日志
这两个文件是一样大小

3、ibtmp1文件
存储临时表的文件，例如在做join union操作产生临时数据，用完就自动删除

4、frm文件
存储表的列信息

5、ibd文件
存储表的数据行和索引

总结：做表的备份迁移并不是复制frm、ibd文件就能实现的。

表空间

源于oracle数据库概念
相当于一个逻辑分区，当物理硬盘sda挂载到表空间中供oracle使用，当空间不足时，再挂载一块硬盘sdb到表空间中供其使用。相当于linux的逻辑卷。

共享表空间(ibdata1~N)
在mysql5.5版本中引入，因为Mysql数据库访问硬盘数据要基于文件系统，所以出现共享表空间ibdata1文件理念。
例如格式化sda硬盘，挂载到/data/目录下，myslq会在该目录下创建ibdata1文件，该文件就是共享表空间文件。

共享表空间

即ibdata1文件

mysql各版本之间存储差距
MySQL5.5版本
默认模式为共享表空间
需要将所有数据存储到同一个表空间ibdata1文件中，管理比较混乱
所以在5.6中将默认表空间更改为独立表空间。

MySQL5.6版本
共享表空间ibdata1(存储数据字典信息,undo,临时表ibtmp1)
frm
ibd

MySQL5.7版本
共享表空间ibdata1(存储数据字典信息,undo)
临时表ibtmp1
frm
ibd

MySQL8.0版本
共享表空间ibdata1(存储数据字典信息)
undo表主要用于回滚
临时表ibtmp1
frm
ibd

查看ibdata1配置信息

#ibdata1默认大小12M，自动以64M自增
#ibdata1文件默认在mysql的/data目录下
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

#所以ibdata1大小为12M+64M=76M，不够了再增加64M，以此类推
$ ls -sh
total 185M
   0 3307       96K client1.err            76M ibdata1      4.0K mysql                12K sys
   0 3308      4.0K client1.pid            48M ib_logfile0   12K performance_schema     0 test
   0 3309         0 client1.pid.shutdown   48M ib_logfile1     0 sch                    0 xyz
4.0K auto.cnf  4.0K ib_buffer_pool         12M ibtmp1       4.0K school

ibdata1设置是在初始化安装mysql时配置的

#该命令在mysql初始化时会在硬盘上创建ibdata1跟ibdata2两个大小都为512M的文件，当这两个文件用完时才会自增
$ mysqld --initialize-insecure --user=mysql --basedir=xxx
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend inodb_autoextend_increment=64

独立表空间

从5.6版本开始，默认表空间不再使用共享表空间，而开始使用独立表空间。

独立表空间特点
每个表都是一个独立的ibd文件，用来存储数据行和索引
即一张InnoDB表=frm+idb+ibdata1

查看独立表空间

#1表示使用的独立表空间模式，0表示使用的共享表空间模式
mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

#修改为共享表空间模式
mysql> set global innodb_file_per_table=0;

#当修改为共享表空间模式之后再创建表，此时表有frm文件，其余文件存储在ibdata1中

表空间迁移

前提是两个数据库的版本要一致。

例如将school数据库中的student表迁移到test数据中

#创建t100w数据库
mysql> create database test charset utf8mb4;

#查看school库中student表的建表语句
#复制建表语句在test库中创建student表
mysql> show create table student;

#删除test库中的表空间文件
mysql> use test;
mysql> alter table student discard tablespace;

#复制原表ibd到test库中,并且修改权限
$ cp school/student.ibd test/
$ chown -R mysql:mysql sutdent*

#导入表空间
mysql> alter table student import tablespace;

#表迁移完成
mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| sno   | int(11)             | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)         | NO   | MUL | NULL    |                |
| sage  | tinyint(3) unsigned | NO   |     | NULL    |                |
| ssex  | enum('f','m')       | NO   |     | m       |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

日志文件

MySQL的存储引擎日志有以下2种
1、Redo Log日志
ib_logfile0，ib_logfile1日志文件，也叫重做日志

2、Undo Log日志
存储在共享表空间中，回滚日志

学习来自：郭老师博客，老男孩深标DBA课程 第五章