mysql第五章事务_第五章:MYSQL存储引擎

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.s

5. 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;