目录
MySQL存储引擎
存储引擎概述
-
作为可插拔式的组件提供
—— MySQL服务软件自带的功能程序，处理表的处理器
—— 不同的存储引擎有不同的功能和数据存储方式
—— MySQL 5.0/5.1 ---> MyISMA
—— MySQL 5.5/5.6 ---> InnoDB
[root@host50 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show variables like "%version%";
//如图所示，当前MySQL的版本号是5.7.17
-
列出可用的存储引擎类型
—— show engines;
mysql> show engines;? ? ?? //查看数据库存储引擎
//如图所示，DEFAULT是指当前数据库使用的默认存储引擎，如当前数据库使用默认存储引擎是InnoDB。YES是支持的存储引擎类型，NO是不支持的存储引擎类型。Comment是存储引擎的描述信息，如InnoDB存储引擎的描述信息是支持事务、行级锁和外键。
MySQL体系结构
配置存储引擎
修改表存储引擎
-
建表时手动指定
—— 未指定时，使用默认存储引擎
—— show create table 表名\G;
-
-
建表时未指定表的存储引擎
-
mysql> create database db10;
mysql> create table db10.a(id int);? ? ? ? //建表时未指定表的存储引擎
mysql> show create table db10.a\G;
//如图所示，建表时未指定表的存储引擎，该表使用的是存储引擎是默认存储引擎InnoDB
-
-
建表时指定表的存储引擎
-
mysql> create table db10.b(id int) ENGINE=MyISAM;? ? ?? //建表时指定表的存储引擎
mysql> show create table db10.b\G;
//如图所示，建表时指定了表的存储引擎，所以该表的存储引擎是MyISAM，而不是默认存储引擎InnoDB，
修改服务存储引擎
-
设置数据库服务使用的存储引擎
—— 修改 /etc/my.cnf 配置文件
—— ]#vim /etc/my.cnf
? ? ? ?? default-storage-engine=存储引擎
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam? ? ?? #存储引擎名不区分大小写
[root@host50 ~]# systemctl restart mysqld? ? ?? //重启服务使配置文件生效
验证修改后服务的存储引擎：
[root@host50 ~]# mysql -uroot -p123456
mysql> show engines;
//如图所示，服务的默认存储引擎变为MyISAM
mysql> create table db10.c(name char(10));? ? ? ? //创建表c，未指定表的存储引擎
mysql> show create table db10.c;
//如图所示，修改完服务的存储引擎为MyISAM后，创建表不指定表的存储引擎，那么表的默认存储引擎就为MyISAM。
存储引擎的特点
MyISAM存储引擎
-
主要特点（功能）
—— 支持表级锁
—— 不支持事务、事务回滚、外键
-
表文件
—— 表名.frm? ? ? ? //存储表结构，即存储的内容就是命令 desc 表名; 的输出的结果
—— 表名.MYI? ? ?? //存储索引
—— 表名.MYD? ?? //存储数据?
mysql> show create table db10.b\G;
//如图所示，该表使用的存储引擎是MyISAM
mysql> system ls /var/lib/mysql/db10/b*? ? ? ? //存储引擎是MyISAM的表，表文件有三个?
/var/lib/mysql/db10/b.frm /var/lib/mysql/db10/b.MYD /var/lib/mysql/db10/b.MYI
InnoDB存储引擎
-
主要特点（功能）
—— 支持行级锁
—— 支持事务、事务回滚、外键
-
表文件
—— 表名.frm? ? ?? //存储表结构
—— 表名.ibd? ? ?? //表空间文件，存储索引和数据的
-
事务日志文件
—— 事务日志文件是记录对表的执行操作（即SQL命令）
—— ibdata1? ? ? ? ?? //存放的是没有被提交完成的SQL操作
—— ib_logfile0? ? ? //存放的是提交完成的SQL操作
—— ib_logfile1? ? ? //存放的是提交完成的SQL操作
mysql> show create table db10.a\G;
//如图所示，该表使用的是存储引擎是InnoDB
mysql> system ls /var/lib/mysql/db10/a*? ? ? //存储引擎是InnoDB的表，表文件有两个
/var/lib/mysql/db10/a.frm? /var/lib/mysql/db10/a.ibd
[root@host50 ~]# ls /var/lib/mysql
//如图所示，数据库的存储引擎为InnoDB时，会自动创建事务日志文件。上面我们修改了数据库的默认存储引擎，将存储引擎修改为MyISAM，所以当前存储引擎修改为MyISAM，但是不会删除之前的事务日志文件。
MEMORY存储引擎
-
主要特点（功能）
—— 支持表级锁
—— 数据是存放在内存中，一旦数据库服务或操作系统重启数据就会丢失
—— 是临时表
-
表文件
—— 表名.frm? ? ?? //存储表结构
mysql> create table db10.d(name char(10)) engine=memory;
mysql> system ls /var/lib/mysql/db10/d*? ? ? //只有一个表结构文件
/var/lib/mysql/db10/db.opt? /var/lib/mysql/db10/d.frm? ? ? //.opt是用来存放字符集的
修改已创建表的存储引擎
mysql> show create table db10.a\G;
//如图所示，表a的存储引擎是默认存储引擎InnoDB
mysql> system ls /var/lib/mysql/db10/a*? ? ? //存储引擎是InnoDB，表文件有两个
/var/lib/mysql/db10/a.frm? /var/lib/mysql/db10/a.ibd
mysql> alter table db10.a engine=myisam;? ? ? ? //将表a的存储引擎修改为MyISAM
mysql> show create table db10.a;
//如图所示，表a的存储引擎修改为了MyISAM
mysql> system ls /var/lib/mysql/db10/b*? ? ? ? //存储引擎变为MyISAM，表文件有三个，存储方式也发生了改变
/var/lib/mysql/db10/b.frm /var/lib/mysql/db10/b.MYD /var/lib/mysql/db10/b.MYI
MySQL锁机制
-
加锁的目的
—— 是为了解决并发访问冲突问题，即同一时刻，多个客户端对同一张表进行操作。
-
锁粒度
—— 表级锁：对整张表加锁。即同一时刻，多个客户端不能对同一张表进行操作。
—— 行级锁：仅对被访问的行分别加锁。即同一时刻，多个客户端能对同一张表进行操作，但不能对同一张表中的同一行进行操作。
-
锁类型
—— 读锁（共享锁）：支持并发读（即允许同时读操作，不允许同时写操作）
—— 写锁（互斥锁、排他锁）：是独占锁，上锁期间其他线程不能读表或写表母（即不允许同时读或写）
-
查看当前锁状态
—— show status like "table_lock%";
mysql> show status like "table_lock%";
//如图所示，Table_locks_waited的值为0，意思是当前没有等待释放写锁的表
事务特性（ACID）
-
事务的概念
—— 事务：指的是从连接到数据库服务器开始，到执行完操作后断开连接，这整个过程被称作事务。
—— 事务回滚：指的是从连接到数据库服务器开始，到执行完操作后断开连接这个期间，出现任何一步操作错误，都会恢复到错误之前的状态，该过程称为事务回滚。（事务回滚是基于事务日志文件实现的）
—— 只有InnoDB存储引擎支持事务和事务回滚
-
Atomic：原子性
—— 事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败。
-
Consistency：一致性
—— 事务操作的前后，表中的记录没有变化。
-
Isolation：隔离性
—— 事务操作是相互隔离不受影响的。
-
Durability：持久性
—— 数据一旦提交，不可改变，永久改变表数据
注：比如db10中的表a有三条数据，执行insert table db10.a(name) values("bob");这条命令。
一致性是指输入这条命令，没有执行（即没有按回车）这条命令，任何客户端看到的a表都只有三天数据；执行（即按回车）这条命令后，任何客户端看到的a表都有四条数据。
隔离性是指执行却没有提交这条命令，只有输入这条命令的客户端才能看见这条数据（即能看见四条数据），其他任何客户端是看不到输入的这条命令，只能看到数据库中已有的那三条记录。
为了测试事务的特性，我们关闭自动提交功能（即输入完命令后，按回车不会提交，需要输入命令才能提交）
-
相关命令
—— mysql> show variables like "autocommit";? ? ? //查看提交状态
—— mysql> set autocommit=off;? ? ? ? ? ? ? ? ? ? ? ? ?? //关闭自动提交
—— mysql> rollback;? ? ? ? //数据回滚
—— mysql> commit;? ? ? ? //提交数据
测试事务与事务回滚
测试环境准备
1、关闭自动提交功能
mysql> show variables like "autocommit";? ? ?? //查看提交状态
//如图所示，自动提交功能默认是开启的状态
mysql> set autocommit=off;? ? ?? //关闭自动提交
mysql> show variables like "autocommit";? ? ?? //查看提交状态
2、新开一个终端连接数据库
再新开一个终端，连接数据库，称为终端二：
[root@host50 ~]# mysql -uroot -p123456
mysql> show variables like "autocommit";
//如图所示，第二个终端连接的数据库的自动提交功能是开启的状态。第一个终端的自动提交功能是关闭的状态
3、创建一张存储引擎是InnoDB的表
因为只有InnoDB存储引擎的表才支持事务和事务回滚，事务与事务回滚是针对于数据而言的（即对数据的增删改查）。
终端一：
mysql> create table db10.t1(id int) engine=innodb;
mysql> show create table db10.t1;
终端二：
mysql> show create table db10.t1;
测试事务特性
终端一：
mysql> insert into db10.t1 values(100);
mysql> select * from db10.t1;
终端二：
mysql> select * from db10.t1;? ? ? //由于终端一关闭了自动提交，且没有提交数据，所以终端二看不见终端一插入的数据
Empty set (0.00 sec)
终端一：
mysql> commit;? ? ? //终端一提交数据
终端二：
mysql> select * from db10.t1;? ? ? ? //终端一提交数据后，终端二就能看见数据了
测试事务回滚
终端一：
mysql> delete from db10.t1;
mysql> select * from db10.t1;? ? ? ? //由于终端一删除后没有提交，只有终端一可以看见数据被删除了
Empty set (0.01 sec)
终端二：
mysql> select * from db10.t1;? ? ? //由于终端一删除后没有提交，所以终端二依旧可以看见原数据
终端一：
mysql> rollback;? ? ? //终端一由于删除数据后没有提交，所以可以进行事务回滚
mysql> select * from db10.t1;? ? ? //事务回滚后，依旧可以看见原来的数据
//综上所述，说明了持久性，一旦提交数据就不可发生改变了，就永久的改变了表数据。也说明了一致性，在操作前后数据一致，即提交前后除执行命令的终端，其余终端的所有数据都是一致的。
建表时，如何决定表使用哪种存储引擎
—— select 查询操作多的表适合使用MyISAM存储引擎，比较节省系统硬件资源。因为MyISAM是表级锁，只需给一张表加读锁即可，不用像InnoDB存储引擎给表里的每一行加读锁。
—— insert/update/delect 插入\更新\删除操作多的表适合使用innodb存储引擎，支持对单张表的并发写。因为MyISAM是表级锁，给一张表加写锁后，只能一个人对表进行数据的增删改；然而InnoDB是行级锁，给表里的每一行加写锁，那么就能让更多的人对表进行数据的增删改，从而增加写的并发量。