innodb存储引擎

Mysql存储引擎介绍

　　MySQL引擎：可以理解为，MySQL的“文件系统”，只不过功能更加强大。
　　MySQL引擎功能：除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。

1. MySQL存储引擎种类

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

2.innodb功能总览

 

3.查看数据库引擎设置

1、使用 SELECT 确认会话存储引擎：
SELECT @@default_storage_engine;
2、使用 SHOW 确认每个表的存储引擎：
show engines;
show create table city;
show table status like 'city'\G
3、使用 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';
4、在启动配置文件中设置服务器存储引擎：
[mysqld]
default-storage-engine=<Storage Engine>
5、使用 SET 命令为当前客户机会话设置：
SET @@storage_engine=<Storage Engine>;
6、在 CREATE TABLE 语句指定：
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

4.innodb体系结构---物理存储结构(表空间)

 

共享表空间：主要存放系统元数据等
独立表空间：主要存放用户数据

4.1 innodb---共享表空间

InnoDB系统表空间：
　　默认情况下，InnoDB 元数据、撤消日志和缓冲区存储在系统“表空间”中。
　　这是单个逻辑存储区域，可以包含一个或多个文件。
　　每个文件可以是常规文件或原始分区。
　　最后的文件可以自动扩展。

 

InnoDB系统表空间配置

通过添加数据文件增加表空间大小。
在 my.cnf 文件中使用 innodb_data_file_path 选项。 
[mysqld]
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
配置示例：创建一个表空间，其中包含一个名为 ibdata1 且大小为 50 MB（固定）的数据文件和一个名为 ibdata2 且大小为 50 MB（自动扩展）的数据文件：
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
默认情况下将文件放置在 data 目录中。
如果需要，显式指定文件位置。

4.２ innodb---独立表空间

除了系统表空间之外，InnoDB 还在数据库目录中创建另外的表空间，用于每个 InnoDB 表的 .ibd 文件。
InnoDB 创建的每个新表在数据库目录中设置一个 .ibd 文件来搭配表的 .frm 文件。
可以使用 innodb_file_per_table 选项控制此设置
更改该设置仅会更改已创建的新表的默认值。
mysql> show variables like '%per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
永久生效需要在my.cnf配置文件添加innodb_file_per_table={0|1}，然后重启mysql服务
注：在mysql5.6开始，默认的配置为：
| innodb_file_per_table | ON    |

4.3 Innodb存储引擎——事务ACID

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

4.4 Innodb存储引擎——事务SQL控制语句

START TRANSACTION（或 BEGIN）：显式开始一个新事务
COMMIT：永久记录当前事务所做的更改
ROLLBACK：取消当前事务所做的更改
SAVEPOINT：分配事务过程中的一个位置，以供将来引用
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT：删除 savepoint 标识符
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

4.5 Innodb存储引擎——Autocommit模式设置

在MySQL5.5开始，开启事务时不再需要begin或者start transaction语句。并且，默认是开启了Autocommit模式，作为一个事务隐式提交每个语句。
在有些业务繁忙企业场景下，这种配置可能会对性能产生很大影响，但对于安全性上有很大提高。
将来，我们需要去权衡我们的业务需求去调整是否自动提交。
我们可以通过以下命令进行修改关闭（0是关闭，1是开启）：
    SET GLOBAL AUTOCOMMIT=0;  #所有新建会话
    SET SESSION AUTOCOMMIT=0; #当前会话
    SELECT @@AUTOCOMMIT;      #查看设置结果
我们也可以修改配置文件让其永久生效：
    vi /etc/my.cnf    
    [mysqld]
    AUTOCOMMIT=0

4.6 Innodb存储引擎——其他触发隐式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

4.7 Innodb存储引擎——事务日志

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

<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">