一、MYSQL存储引擎：

InnoDB存储引擎
作用：规划和存取数据，类似于Linux文件系统

1、查看存储引擎类型：


mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

查看默认的存储引擎：

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

mysql>

2、InnoDB核心特性

2.1事物（ACID）

事物定义：事物是访问和更新数据库的程序执行单元，事务中包含一条或多条SQL语句，这些语句要么全部执行成功，要么都不执行
A :原子性
原子性定义：原子性是指一个事物是一个不可分割的工作单位，其中操作要么都做，要么都不做，
如果事物中的一条SQL语句执行失败，则已经执行的语句必须全部回滚，数据库退回
到执行事物之前的状态
C ：一致性
一致性定义：一致性是指事物执行后，数据库的完整性约束没有被破坏，事物执行前后都是合法的数据状态。
I :隔离性
隔离性定义：隔离性是指事物内部的操作与其他的事物都是隔离的，并发执行的各个事物之间不能相互干扰
严格的隔离性，对应事务隔离级别中的”可串行化“,但实际应用中出于性能方面的考虑会使用可串行化
D :持久性
持久性定义：持久性是指事物一旦提交，他对数据库的
改变就是永久性的，接下来的其他操作或者故障不应该对其有任何影响。
MVCC多版本并发控制
支持行级锁
支持热备份
支持ACSR（自动故障恢复）
支持外键

2.2 redo 事物日志

作用：主要保证D特性、对AC特性也有保证，自动故障恢复期间，实现了前滚的功能。

2.2.1redo 日志文件

ib_logfile0
ib_logfile1

2.3 undo 事物日志

2.3.1日志文件

undo日志文件: ibdata1

2.3.2 undo作用

作用：主要保证了A、C的特性，并且对I的特性也有相关的作用，在ACSR过程作用实现的是未提交事物回滚的功能

2.4 I的特性的保证

通过锁来保证：行及所动，事物在对某行修改时，会持有数据行的锁，其他事物不能同时更新此行。

2.5 隔离级别、

RU 读未提交
RC 读已提交
RR 可重复读
S 可串行化

3、事物的控制语句

3.1 标准的，事物的生命周期

mysql> begin;
mysql> delete from t100w where id<5;
mysql> rollback;

mysql> begin;
mysql> delete from t100w where id<5;
mysql> commit;

3.2 自动提交机制，生命周期

查看启动提交机制是否开启
1为开启
0为关闭

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql>

在配置文件中（my.cnf）中，添加autocommit=0即可关闭事物自动提交功能

3.3隐式提交

begin
a
b
begin

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、隔离级别：

4.1 RR(可重复读),默认级别

可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

4.2 RC (读已提交)

可能出现幻读,可以防止脏读.
开启参数（写入配置文件即可）：transaction_isolation=READ-COMMITTED

影响到数据的读取,默认的级别是 RR模式.
transaction_isolation 隔离级别(参数)

负责的是,MVCC,读一致性问题
RU: 读未提交,可脏读,一般不允许出现
SR: 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.

5、InnoDB存储引擎相关操作

修改某表的存储引擎

mysql> alter table t1 engine=innodb; #既有修改存储引擎的作用也能整理存储碎片
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> optimize table t1; #整理存储碎片

6、表空间迁移

.frm文件：表结构文件
.ibd文件：表数据文件
.frm+.idb=表空间

[root@db01 /data/mysql/data/test1]# ll
total 252
-rw-r-----. 1 mysql mysql   8730 Aug 27 20:38 ansible.frm
-rw-r-----. 1 mysql mysql  98304 Aug 27 20:38 ansible.ibd
-rw-r-----. 1 mysql mysql     67 Aug 26 21:54 db.opt
-rw-r-----. 1 mysql mysql   8801 Aug 27 21:24 t1.frm 
-rw-r-----. 1 mysql mysql 131072 Aug 27 22:05 t1.ibd
[root@db01 /data/mysql/data/test1]#

6.1 创建和原表结构相同的空表

 CREATE TABLE city (
  ID int(11) NOT NULL AUTO_INCREMENT,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (ID),
  KEY CountryCode (CountryCode)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

6.2 删除空表表空间文件

mysql> alter table city discard tablespace;

6.3 将有数据的表空间拷贝过来并导入`

[root@db01 /data/3307/data/world]# cp -a /data/mysql/data/world/city.ibd ./
mysql> alter table city import  tablespace;

7 InnoDB引擎核心参数（写入my.cnf文件中）

第一个参数：innodb_flush_log_at_trx_commit=1 #在每次事务提交时,都立即刷写redo buffer 到OS buffer ,在立即到磁盘
(a) 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

(b) 当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。

(c) 当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
————————————————
第二个参数 innodb_buffer_pool_size=2G #缓冲区池的大小，最大调整为物理内存的95%，实际使用中给到 70%-80% 就可以了。
————————————————
第三个参数innodb_flush_method=O_DIRECT
(1) fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。

(2) O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成

(3) O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲

innodb_flush_method三种模式

MYSQL-InnoDB引擎