MySQL的存储引擎，常用存储引擎InnoDB与Myisam，以及InnoDB的事物特性

目录

一、理论

二、数据库存储引擎的相关操作

三、常用存储引擎InnoDB与Myisam

四、事务特性，commit和rollback的作用

---

一、理论

1.概述

    存储引擎是Mysql自带的功能程序，是处理表的处理器，MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

Mysql在5.5版本之前默认使用的是MyISAM存储引擎，直到5.6之后就改成了InnoDB

 

2.存储类型种类

MyISAM： 拥有较高的插入，查询速度，但不支持事务

InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定

BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性

Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

Merge ：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用

Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差

Federated： 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用

Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用

CSV： 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。

BlackHole ：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继

 

3.Mysql体系结构

上从到下

Connectors：Mysql的应用程序结构，端口为3306,Mysql为多种语言提供了连接接口，如Java,Python,PHP等

Connection Pool：数据库连接池，提前创建了多个数据库进程，在使用时直接从连接池拿，减少创建和销毁过程中造成的资源浪费，提高存储查询效率

Management Service&Utilltiles：管理服务，工具，如数据库安全管理，数据集群管理，多为第三方插件管理程序

SQL Interface：结构化查询语言，结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和关系数据库系统

Parser：解析器，将sql语言转换为计算机看的懂的东西，二进制文件

Optimizer：优化数据处理速度

Cache&Buffers：数据库缓存，将经常使用的数据存在内存中，提高数据读取的速度

Pluggabie Storage Engines：存储引擎，决定数据存储的方式，每一种存储引擎都有不同的功能

File system & Logs：系统格式，与文件日志，如中继日志，binlog日志

 

二、数据库存储引擎的相关操作

1.查看当前数据库支持的存储引擎

mysql-> SHOW ENGINES\G

2.修改默认的引擎

查看默认数据库存储引擎

SHOW VARIABLES LIKE 'default_storage_engine';

我们创建表时默认使用的存储引擎是InnoDB，修改默认引擎指令：

mysql-> SET default_storage_engine=MyISAM(InnoDB/BDB/ARCHIVE);

或者修改配置文件

1. [root@dbsvr1 ~]# vim /etc/my.cnf
2. [mysqld]
3. .. ..
4. default_storage_engine=myisam                             //改用myisam引擎
5.  
6. [root@dbsvr1 ~]# systemctl restart mysqld.service         //重启服务

3.查看表引擎

show create table user \G;

1. *************************** 1. row ***************************
2. Table: user
3. Create Table: CREATE TABLE `user` (
4. `id` int(11) NOT NULL AUTO_INCREMENT,
5. `name` char(50) DEFAULT NULL,
6. `age` tinyint(3) unsigned DEFAULT '19',
7. `password` char(1) DEFAULT NULL,
8. `uid` int(11) DEFAULT NULL,
9. `gid` int(11) DEFAULT NULL,
10. `comment` char(150) DEFAULT NULL,
11. `homedir` char(50) DEFAULT NULL,
12. `shell` char(50) DEFAULT NULL,
13. PRIMARY KEY (`id`)
14. ) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1 //存储引擎是InnoDB
15. 1 row in set (0.00 sec)

4.在建表时指定存储引擎

在结尾除括号后填写引擎类型

mysql> create table stuinfo( name char(10), age int )engine=InnoDB;

5.建表后修改存储引擎

mysql> alter table stuinfo engine=innodb;

 

三、常用存储引擎InnoDB与Myisam

1.Myisam

拥有较高的插入，查询速度,但是不支持事务，像一些第三方插件如Mybatis就必须使用InnoDB，以此来做一些事务回滚操作.Myisam也不能用来做数据库集群，因为没有日志文件。

特点：

- 支持表级锁

- 不支持事务、事务回滚、外键

表文件对应后缀名：

- 表结构： 表名.frm

- 索引： 表名.MYI

- 数据：表名.MYD

 

2.InnoDB

InnoDB是一个功能强大的数据库引擎，支持行级锁定，支持事务，拥有日志文件，支持外键

因此它可以用于做数据库集群，通过外键来建立表间关系

特点：

- 支持行级锁

- 支持事务、事务回滚、外键

表文件对于后缀名：

- 表结构：表名.frm

- 数据：表名.ibd

事务日志文件：

- ibdata1

- ib_logfile0

- ib_logfile1

 

3.Mysql锁机制

Myisam支持表级锁，InnoDB支持行级锁，在高并发情况下行级锁情况要比表级锁表现优异

锁类型分为写锁和读锁，读锁规定了在允许并发读，写锁规定了在写时不允许读和写

锁粒度：

- 表级锁：对整张表加锁

- 行级锁：仅对被访问的行分别加锁

锁类型：

- 读锁（共享锁）：支持并发读

- 写锁（互斥锁、排它锁）：是独占锁，上锁期间其他线程不能读表或写表

查看当前锁状态：

- show status like "table_lock%"

 

四、事务特性，commit和rollback的作用

1.事务特性ACID

Atomic:原子性

事务的整个操作是一个整体，不可分割，要么全部成功要么全部失败

Consistency：一致性

事务操作的前后，表中的记录没有变化，也就是说事务级别的操作并不影响表数据

Isolation：隔离性

事务操作是相互隔离不受影响的

Durability：持久性

数据一旦提交，不可改变，永久 改变表数据，此时事务一致性实效

 

 

下面验证一下事务的作用，主要介绍commit提交事务与rollback回滚事务

2.创建innodb存储引擎的表

数据库管理员root登录，创建新库、新表。引擎为InnDB，支持事务

1. [root@host50 ~]# mysql -uroot -p123456 //访问服务
2. mysql> create database db10; //建库
3. Query OK, 1 row affected (0.05 sec)
4.  
5. mysql> use db10; //切换库
6. Database changed
7. mysql>
8. mysql> create table a(id int)engine=innodb; //建表并指定存储引擎
9. Query OK, 0 rows affected (0.52 sec)

3.关闭服务的自动提交功能

   Mysql默认是自动提交事务，在做增删改数据库管理员root登录，关闭服务的自动提交功能。

1. mysql> show variables like "%commit%"; //查看所有包涵commit 字样的变量
2. +-----------------------------------------+-------+
3. | Variable_name | Value |
4. +-----------------------------------------+-------+
5. | autocommit | ON |     //自动提交功能开启
6. | binlog_group_commit_sync_delay | 0 |
7. | binlog_group_commit_sync_no_delay_count | 0 |
8. | binlog_order_commits | ON |
9. | innodb_api_bk_commit_interval | 5 |
10. | innodb_commit_concurrency | 0 |
11. | innodb_flush_log_at_trx_commit | 1 |
12. | slave_preserve_commit_order | OFF |
13. +-----------------------------------------+-------+
14. 8 rows in set (0.01 sec)
15. 1）
16. mysql> set autocommit=off ; 关闭自动提交
17. Query OK, 0 rows affected (0.00 sec)
18.  
19. mysql> show variables like "autocommit"; //查看
20. +---------------+-------+
21. | Variable_name | Value |
22. +---------------+-------+
23. | autocommit | OFF |            //已处于关闭状态
24. +---------------+-------+
25. 1 row in set (0.00 sec)

4.测试事务特性

1） 插入新记录，不执行提交命令commit

1. mysql> insert into db10.a values(101); //插入记录
2. Query OK, 1 row affected (0.00 sec)
3.  
4. mysql> select * from db10.a ;//查看记录
5. +------+
6. | id |
7. +------+
8. | 101 |
9. +------+
10. 1 row in set (0.00 sec)

2）打开新终端访问数据服务，查看不到插入的数据

注：此处打开的终端称为终端2 ， 此处之前终端被称为终端1

1. [root@host50 ~]# mysql -uroot -p123456
2. mysql> select * from db10.a; //没有记录
3. Empty set (0.00 sec)

3）终端1 执行提交命令commit

1. mysql> select * from db10.a ;
2. +------+
3. | id |
4. +------+
5. | 101 |
6. +------+
7. 1 row in set (0.00 sec)
8.  
9. mysql> commit ; 执行提交命令
10. Query OK, 0 rows affected (0.08 sec)

4）第终端2执行查看命令

1. mysql> select * from db10.a;
2. Empty set (0.00 sec)
3.  
4. mysql> select * from db10.a; //查看到数据
5. +------+
6. | id |
7. +------+
8. | 101 |
9. +------+
10. 1 row in set (0.00 sec)

5）在终端1删除记录 ，不执行提交命令commit

将/etc/passwd文件复制到/var/lib/mysql-files/目录下，

1. mysql> select * from db10.a ; //删除前查看
2. +------+
3. | id |
4. +------+
5. | 101 |
6. +------+
7. 1 row in set (0.00 sec)
8.  
9. mysql> delete from db10.a ;//删除表记录
10. Query OK, 1 row affected (0.00 sec)
11.  
12. mysql> select * from db10.a ; //查看不到记录
13. Empty set (0.00 sec)

6）在终端2 依然可以查看到记录

1. mysql> select * from db10.a;
2. +------+
3. | id |
4. +------+
5. | 101 |
6. +------+
7. 1 row in set (0.00 sec)

7）在终端1，执行回滚命令

在未提交之前可以回滚

1. mysql> select * from db10.a ; //回滚前查看
2. Empty set (0.00 sec)
3.  
4. mysql> rollback ; //数据回滚
5. Query OK, 0 rows affected (0.03 sec)
6.  
7. mysql> select * from db10.a ; //回滚后查看
8. +------+
9. | id |
10. +------+
11. | 101 |
12. +------+
13. 1 row in set (0.00 sec)

如果是提交后回滚，则不予以回滚

1. mysql> delete from db10.a ; //删除记录
2. Query OK, 1 row affected (0.00 sec)
3.  
4. mysql> commit ; //提交
5. Query OK, 0 rows affected (0.08 sec)
6.  
7. mysql> rollback ; //数据回滚
8. Query OK, 0 rows affected (0.00 sec)
9.  
10. mysql> select * from db10.a ; //查看不到记录
11. Empty set (0.00 sec)
12.  

8）在终端2 也查看不到记录

1. mysql> select * from db10.a;
2. Empty set (0.00 sec)

5.事务特性总结

　commit：提交事务，只要不提交事务，我们所做的任何操作(sql）都不会改变数据的真实情况

　rollback：数据回滚，回滚到上一次提交之前的状态，比如我删除了某条记录却没有提交，这时候可以利用回滚，回到删除指令发生之前的状态