数据库学习之九:mysql存储引擎

database

九、mysql 存储引擎

1、课程大纲

存储引擎的介绍

mysql中的存储引擎分类

mysql存储引擎设置

mysql,innodb引擎存储结构

mysql中的事物

mysql中的锁

2、mysql存储引擎介绍

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

将一个事务的结果与其他事务完全隔离

以上是 数据库学习之九:mysql存储引擎 的全部内容, 来源链接: utcz.com/z/533530.html

回到顶部