九、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=
2、使用 SET 命令为当前客户机会话设置:
SET @@storage_engine=;
3、在 CREATE TABLE 语句指定:
CREATE TABLE t (i INT) 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
将一个事务的结果与其他事务完全隔离