MySQL的存储引擎

存储引擎简介：

1、文件系统：

1.1 操作系统组织和存取数据的一种机制。
1.2 文件系统是一种软件。

2、文件系统类型：ext2 3 4 ，xfs 数据

2.1 不管使用什么文件系统，数据内容不会变化
2.2 不同的是，存储空间、大小、速度。

3、MySQL引擎：

3.1 可以理解为，MySQL的“文件系统”，只不过功能更加强大。

4、MySQL引擎功能：

4.1 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能
总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。

存储引擎的类型

MySQL 提供以下存储引擎:
01）InnoDB
02）MyISAM
03）MEMORY HASH索引
04）ARCHIVE
05）FEDERATED
06）EXAMPLE
07）BLACKHOLE
08）MERGE
09）NDBCLUSTER
10）CSV

查看表的存储引擎

root@localhost [world] >show create table world.city;
| city  | CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |
ENGINE=InnoDB  //存储引擎类型
#查看存储引擎InnoDB类型的字段
root@localhost [world] >select table_schema,table_name,engine from information_schema.tables where engine='innodb' limit 10;
+---------------+-------------------+--------+
| table_schema  | table_name        | engine |
+---------------+-------------------+--------+
| linux50       | course            | InnoDB |
| linux50       | score             | InnoDB |
| linux50       | student           | InnoDB |
| linux50       | teacher           | InnoDB |
| mtlbbdb_19900 | t_achievement     | InnoDB |
| mtlbbdb_19900 | t_auctionprice    | InnoDB |
| mtlbbdb_19900 | t_backup_tmp_guid | InnoDB |
| mtlbbdb_19900 | t_belle           | InnoDB |
| mtlbbdb_19900 | t_char            | InnoDB |
| mtlbbdb_19900 | t_char_ext1       | InnoDB |
+---------------+-------------------+--------+
10 rows in set (0.01 sec)
#查看存储引擎myisam类型的字段
root@localhost [world] >select table_schema,table_name,engine from information_schema.tables where engine='myisam' limit 10;
+--------------------+-----------------+--------+
| table_schema       | table_name      | engine |
+--------------------+-----------------+--------+
| information_schema | COLUMNS         | MyISAM |
| information_schema | EVENTS          | MyISAM |
| information_schema | OPTIMIZER_TRACE | MyISAM |
| information_schema | PARAMETERS      | MyISAM |
| information_schema | PARTITIONS      | MyISAM |
| information_schema | PLUGINS         | MyISAM |
| information_schema | PROCESSLIST     | MyISAM |
| information_schema | ROUTINES        | MyISAM |
| information_schema | TRIGGERS        | MyISAM |
| information_schema | VIEWS           | MyISAM |
+--------------------+-----------------+--------+
10 rows in set (0.00 sec)

innodb和myisam的区别

事务支持：

InnoDB：支持事务，是一个支持ACID（原子性、一致性、隔离性、持久性）特性的存储引擎。这意味着它能够确保数据库操作要么全部执行成功，要么全部失败回滚，保持数据库的一致性和可靠性。
MyISAM：不支持事务，它是一个非事务性的存储引擎。这意味着它不具备事务的特性，对于并发性要求较高或需要保障数据一致性的应用场景可能不太适合。

并发性：

InnoDB：支持行级锁定，多个事务可以同时读取和写入不同行的数据，因此在高并发环境下表现更好。
MyISAM：只支持表级锁定，当一个事务对表执行写操作时，其他事务无法对整个表进行写操作，这可能导致并发性能较差。

外键支持：

InnoDB：支持外键约束，可以通过外键关联表与表之间的关系，确保数据的完整性。
MyISAM：不支持外键约束，无法在表之间建立关系，这可能在数据关联和一致性方面存在一些限制。

崩溃恢复：

InnoDB：具有自动崩溃恢复的能力，可以在数据库崩溃后进行自动恢复。
MyISAM：不支持自动崩溃恢复，可能需要手动修复和恢复数据。

全文索引：

InnoDB：不支持全文索引，需要使用其他全文搜索引擎如Elasticsearch。
MyISAM：支持全文索引，可以用于全文搜索，这在对文本内容进行搜索的场景下较为有用。

空间占用：

InnoDB：通常比MyISAM的表占用更多的磁盘空间，因为它需要存储额外的数据和索引信息。
MyISAM：通常比InnoDB的表占用更少的磁盘空间，因为它不需要存储那么多额外的数据和索引信息。

innodb核心特性
重点:
MVCC 多版本并发控制
事务
行级锁
热备份
Crash Safe Recovery（自动故障恢复）

存储引擎如何查看

root@localhost [world] >SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)
root@localhost [world] >show table status like 'city';

存储引擎设置

1）在启动配置文件中设置服务器存储引擎#在配置文件的[mysqld]标签下添加
[mysqld]
default-storage-engine=<Storage Engine>  //innodb或者myisam
2）使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置
SET @@storage_engine=<Storage Engine>
3）建表指定存储引擎
root@localhost [world] >create table zls.t2_myisam(id int)engine myisam;

修改表存储引擎

root@localhost [world] >alter table city  engine=innodb;
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

InnoDB特性——表空间

共享表空间
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展

共享表空间存储：
1）undo事务日志
2）系统数据
3）临时表

共享表空间切割

mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name | Value |
+----------------------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+----------------------------------+------------------------+
[root@db01 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

独立表空间

存储：表中的真实数据
查看独立表空间是否开启

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
[root@db03 ~]# ll /app/mysql/data/world/city.*
/app/mysql/data/world/city.frm
/app/mysql/data/world/city.ibd

InnoDB核心特性——事务

事务伴随着交易
事务主要针对DML语句（insert、update、delete）
事务特性：
A：原子性：将一个事务视为一个单元，要么全部成功，有一条失败，就全部回滚
C：一致性：事务执行之前和事务执行之后，状态保持一致
I：隔离性：事务与事务之间互相隔离
D：持久性：当事务被提交后，永久写入磁盘

事务的生命周期

# 成功的事务：
begin;
DML
DML
DML
commit;
# 失败的事务
begin;
DML
DML
DML
rollback;

事务控制语句

begin; start transaction; // 开启一个事务
savepoint; // 将事务保存在某一状态
mysql> savepoint zls_4000;
rollback to savepoint; // 回到指定的位置点
mysql> rollback to savepoint zls_4000;
release savepoint; // 删除位置点
mysql> release savepoint zls_4000;
commit; // 提交事务
rollback; // 回滚事务
## 设置
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式
vim /etc/my.cnf
[msyqld]
autocommit=0

MySQL在默认情况下，只要执行一个DML语句，就会自动开启一个事务

事务的隐式提交

1）在事务执行期间，如果执行了begin，开启下一个事务，自动提交上一个事务。
2）在事务执行期间，如果执行了DDL、DCL也会自动提交事务
3）在事务执行期间，如果执行锁表语句 lock，unlock也会自动提交上一个事务
4）load data in file，做备份
5）select for update 自动提交事务
6）在autocommit开启的时候，自动提交事务

事务日志（CSR 自动故障恢复）

redo

1）Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。
2）作用是什么？
在事务ACID过程中，实现的是“D”持久化的作用。

特性:WAL(Write Ahead Log)日志优先写
REDO：记录的是，内存数据页的变化过程
Mysql数据，包括事务提交后，不是真的把所有数据写入磁盘永久化，而是优先记录日志
Mysql数据写入磁盘，就算将内存中的数量，写入到独立表空间中.ibd文件

# 为什么日志优先写
1.因为在MySQL中，最小单位是页
2.一条数据至少也是16k
3.redo中只记录变化过程（几个字符）
4.写入速度，redo写入速度更快

# 什么时候会把数据写入表空间
1.MySQL重启后
2.每隔一段时间会将数据写入一次表空间

undo

undo,顾名思义“回滚日志”

undo作用是什么？

在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关

[root@db01 ~]# ll /app/mysql/data/
total 254188
-rw-rw---- 1 mysql mysql       56 Jul 18 17:48 auto.cnf  // UUID配置文件
-rw-rw---- 1 mysql mysql   219875 Jul 31 15:08 db01.err  // 错误日志
-rw-rw---- 1 mysql mysql        5 Jul 31 15:08 db01.pid  // PID文件
-rw-rw---- 1 mysql mysql 79691776 Jul 31 15:08 ibdata1   // 共享表空间——undo log
-rw-rw---- 1 mysql mysql 79691776 Jul 28 10:34 ibdata2
-rw-rw---- 1 mysql mysql 50331648 Jul 31 15:08 ib_logfile0  // redo log
-rw-rw---- 1 mysql mysql 50331648 Jul 18 17:41 ib_logfile1
drwx------ 2 mysql mysql      166 Jul 25 17:30 linux50      // 库
drwx------ 2 mysql mysql     4096 Jul 26 15:17 mtlbbdb_19900
drwx------ 2 mysql mysql     4096 Jul 27 17:25 mysql
drwx------ 2 mysql mysql     4096 Jul 18 17:42 performance_schema
drwxr-xr-x 2 mysql mysql       20 Jul 18 17:30 test
drwx------ 2 mysql mysql      144 Jul 27 17:08 world

innodb核心特性——锁

# 锁的粒度
innodb锁粒级：行级锁
myisam锁粒级：表级锁

# 锁的分类
共享锁：在一个事务执行期间，不阻塞查询操作
排它锁：在一个事务执行期间，阻塞其他修改操作
乐观锁：谁先提交，以谁为准
悲观锁：以事务开启为准，谁先执行修改操作，其他人查询会被阻塞

# 多版本并发控制(MVCC)
只阻塞修改类操作，不阻塞查询类操作
乐观锁的机制（谁先提交谁为准）

innodb核心特性——隔离级别

# MySQL的隔离级别
RU: read uncommit 允许事务查看其他事务所进行的未提交更改 （未提交读）
RC: read commit 允许事务查看其他事务所进行的已提交更改 （提交读）
RR：repetable read 确保每个事务的 SELECT 输出一致 (可重复读)
SERIALIZABLE：将一个事务的结果与其他事务完全隔离 （串行化级别）

#查看隔离级别
mysql> show variables like '%iso%';

#修改隔离级别为RU
[mysqld]
transaction_isolation=read-uncommit
mysql> use oldboy
mysql> select * from stu;
mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);

#修改隔离级别为RC
[mysqld]
transaction_isolation=read-commit