超详细图解!!从入门到精通的MySQL合集【第九集】:MySQL存储引擎
Java架构没有996 770 2

1、课程大纲

存储引擎的介绍

mysql中的存储引擎分类

mysql存储引擎设置

mysql,innodb引擎存储结构

mysql中的事物

mysql中的锁


2、mysql存储引擎介绍

文件系统:

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

类型:ext2,3,4,xfs数据:

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

mysql引擎:

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

mysql引擎功能:

除了可以提供基本的存取功能,还有更多功能事物功能,锁定,备份和恢复,优化以及特殊功能。

3、mysql存储引擎种类

MySQL 提供以下存储引擎:
InnoDB
MyISAM
MEMORY
ARCHIVE    
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
还可以使用第三方存储引擎(TokuDB)。

4、数据库的存储引擎

存储引擎查询

存储引擎查询:

show engines;
select @@default_storage_engine;
show create table city;
show table status like 'city'\G
use information_schema 
select table_schema,table_name,engine from information_schema.tables where table_schema='world';
select table_schema,table_name,engine from information_schema.tables where table_schema='mysql';
select table_schema,table_name,engine from information_schema.tables where engine='csv';

存储引擎的配置:

查看存储引擎:

show engines;
select @@default_storage_engine;
mysql> show variables like '%engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+

将存储引擎设置在配置文件中,重启mysql生效。 配置存储引擎:

1、在启动配置文件中设置服务器存储引擎:
[mysqld]
default-storage-engine=<Storage Engine>
2、使用 SET 命令为当前客户机会话设置:
    SET @@storage_engine=<Storage Engine>;
3、在 CREATE TABLE 语句指定:
    CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

mysql> show variables like '%engine%';
mysql> use world
mysql> create table test3 (id int ) engine=innodb;
mysql> show create table test3;

5、innodb体系结构:

表空间:

共享表空间:主要存放系统元数据

独立表空间:主要存放用户数据

逻辑概念,数据存储的概念

表空间数据文件:idb文件,在/app/mysql/data/lufei

共享表空间的设置:

默认的配置:
innodb_data_file_path    /application/mysql/data/  ibdata1:12M:autoextend 

共享表空间的设置:
共享表空间设置:

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend    ----错误的配置XXX
在 /application/mysql/data/查看idbdata1实际的大小,然后在增加idbdata2的大小,进行扩展。此命令配置在mysql的配置文件中。
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend    -----正确的配置

独立表空间:

除了系统表空间之外,InnoDB 还在数据库目录中创建另外的表空间,用于每个 InnoDB 表的 .ibd 文件。
InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的 .frm 文件。
可以使用 innodb_file_per_table 选项控制此设置
更改该设置仅会更改已创建的新表的默认值。

注:在mysql5.6开始,默认的配置为:
| innodb_file_per_table | ON    |
独立表空间删除表空间命令:

alter table testtab discard tablespace;

该命令执行后/app/mysql/data/lufei中的testtab的ibd文件就被删除。

6、Innodb引擎-事务

组数据操作执行步骤,这些步骤被视为一个工作单元: 用于对多个语句进行分组 可以在多个客户机并发访问同一个表中的数据时使用 所有步骤都成功或都失败: 如果所有步骤正常,则执行 如果步骤出现错误或不完整,则取消

事务ACID: Atomic(原子性) 所有语句作为一个单元全部成功执行或全部取消。 Consistent(一致性) 如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态。 Isolated(隔离性) 事务之间不相互影响。 Durable(持久性) 事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

7、mysql事务控制语句

事务中的标准语句:DML语句
insert,update,delete三类事物语句。
如果遇到以上三个命令,都是事物型的操作,都会默认在前面添加begin命令。

START TRANSACTION(或 BEGIN):显式开始一个新事务
1
2
3
COMMIT:永久记录当前事务所做的更改
BEGIN
1    打标记a
2    打标记b(savepoint时候)
3
ROLLBACK:取消当前事务所做的更改(回滚到所打的标记)

SAVEPOINT:分配事务过程中的一个位置,以供将来引用
ROLLBACK TO SAVEPOINT:取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT:删除 savepoint 标识符
SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit 模式

保存:commit是自动保存的:

mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| autocommit                  | ON    |
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 64    |
| innodb_autoinc_lock_mode    | 1     |
| innodb_stats_auto_recalc    | ON    |
| sql_auto_is_null            | OFF   |
+-----------------------------+-------+
8 rows in set (0.00 sec)
需要将自动保存的commit改成关闭:(只修改当前会话)
mysql> set autocommit=0;
查看全局commit配置:
mysql> show global variables like "%auto%";
在有些业务繁忙企业场景下,这种配置可能会对性能产生很大影响,但对于安全性上有很大提高。
将来,我们需要去权衡我们的业务需求去调整是否自动提交。
我们可以通过以下命令进行修改关闭(0是关闭,1是开启):
SET GLOBAL AUTOCOMMIT=0;  -所有新建会话
SET SESSION AUTOCOMMIT=0; -当前会话
SELECT @@AUTOCOMMIT;      -查看设置结果
我们也可以修改配置文件让其永久生效:
vi /etc/my.cnf    
[mysqld]
AUTOCOMMIT=0    
隐式提交语句(commit):
用于隐式提交的 SQL 语句:
START TRANSACTION
SET AUTOCOMMIT = 1 
导致提交的非事务语句:
DDL语句:    (ALTER、CREATE 和 DROP)
DCL语句:    (GRANT、REVOKE 和 SET PASSWORD)
锁定语句:(LOCK TABLES 和 UNLOCK TABLES)
导致隐式提交的语句示例:
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
如:
BEGIN
1
2
3

BGEIN(开始之前就默认commit)
1
2
3

8、事务的日志

Redo是什么?    
redo,顾名思义“重做日志”,是事务日志的一种。
作用是什么?
在事务ACID过程中,实现的是“D”持久化的作用。
undo是什么?    
undo,顾名思义“回滚日志”,是事务日志的一种。
作用是什么?
在事务ACID过程中,实现的是“A、C”原子性和一致性的作用。
什么是“锁”?
“锁”顾名思义就是锁定的意思。
“锁”的作用是什么?
在事务ACID过程中,“锁”和“隔离级别”一起来实现“I”隔离性的作用。
锁的粒度:
1、MyIasm:低并发锁——表级锁
2、Innodb:高并发锁——行级锁
四种隔离级别:
READ UNCOMMITTED        
允许事务查看其他事务所进行的未提交更改
READ COMMITTED
允许事务查看其他事务所进行的已提交更改
REPEATABLE READ******
确保每个事务的 SELECT 输出一致
InnoDB 的默认级别
SERIALIZABLE
将一个事务的结果与其他事务完全隔离

最后,祝大家早日学有所成,拿到满意offer,快速升职加薪,走上人生巅峰。

可以的话请给我一个三连支持一下我哟??????【获取资料】

超详细图解!!从入门到精通的MySQL合集【第九集】:MySQL存储引擎

评论区

索引目录