数据库学习之九：mysql 存储引擎「建议收藏」

九、mysql 存储引擎

1、课程大纲

存储引擎的介绍

mysql中的存储引擎分类

mysql存储引擎设置

mysql，innodb引擎存储结构

mysql中的事物

mysql中的锁

代码100分

2、mysql存储引擎介绍

代码100分mysql存储引擎介绍
文件系统：
操作系统组织和存取数据的一种机制。
文件系统是一种软件。
类型：ext2,3,4,xfs数据：
不管使用什么文件系统，数据内容不会变化
不同的是，存储空间，大小，速度
mysql引擎：
可以理解为，mysql的“文件系统”，只不过功能更加强大。
mysql引擎功能：
除了可以提供基本的存取功能，还有更多功能事物功能，锁定，备份和恢复，优化以及特殊功能。

3、mysql存储引擎种类

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

4、数据库的存储引擎

存储引擎查询

代码100分存储引擎查询：
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
将一个事务的结果与其他事务完全隔离