文章目录
MySQL存储引擎
存储引擎概述
作为可插拔式的组件提供
- MySQL服务软件自带的功能程序,处理表的处理器
- 不同的存储引擎有不同的功能和数据存储方式
- MySQL 5.0/5.1----> MyISAM(不支持外键)
- MySQL 5.5/5.6----> InnoDB(支持外键)
show engines; #查看当前数据库支持那些结构
修改服务存储引擎
设置数据库服务使用的存储引擎
- 修改/etc/my.cnf 配置文件
vim /etc/my.cnf
[mysqld]
...
#default-storage-engine=存储引擎
default-storage-engine=myisam
#存储引擎列表就是上表显示出的,在日常使用中还是主要使用InnoDB和MyISAM
...
systemctl restart mysqld
mysql -uroot -p123456
show engines;
可以看出现在数据库的默认存储引擎已经修改为MyISAM
下面是我之前未修改默认引擎之前建的库和表,可以看出表的默认引擎为InnoDB,(engine=innodb)
show databases;
use db2;
show tables;
show create table user;
我们可以看出InnoDB存储数据是把数据存储为两个文件(这两个文件下文会有介绍)。
接下来新创建一张表进行测试。因为我们修改了默认表引擎为MyISAM,他和innodb是有所不同的,
create database db3;
use db3;
create table db3.a(name char(10));
show create table db3.a;
system ls /var/lib/mysql/db3/a*
MyISAM存储引擎的表,对应了三个存储文件
MyISAM存储引擎
主要特点
- 支持表级锁
- 不支持事务、事务回滚、外键
表文件
- 表名.frm #表结构
- 表名.MYI #索引
- 表名.MYD #数据
InnoDB存储引擎
主要特点
- 支持行级锁定
- 支持事务、事务回滚、外键
表文件
- 表名.frm #表结构
- 表名.idb #表空间结构(数据+索引)
事务日志文件(记录对表的SQL操作)
- ibdata1 #记录未提交
- ib_logfile0 #记录已提交
- ib_logfile1 #记录已提交
MySQL锁机制
为什么加锁:
为了解决并发访问冲突(同一时刻,对同一张表进行写操作)
锁粒度
- 表级锁:对整张表加锁
- 行级锁:仅对被访问的行分别加锁
锁类型
- 读锁(共享锁):支持并发读(加读锁之后,如果在进行一个读操作是允许的,不允许写操作)
- 写锁(互斥锁,排他锁):是独占锁,上锁期间其他线程不能读表或者写表(加写锁之后,不允许读和写)
查看当前锁状态
- show status like “table_lock%”;
Table_locks_waited:表锁等待为0(当前没有等待释放写锁的表)
事务特性(ACID)
事务:从连接到数据库服务开始,到执行完操作断开连接称作一次事务
事务回滚:在没有断开的期间,有任何一步出现错误,都恢复到错误之前的状态称为事务回滚(根据事务日志文件进行回滚)
Atomic:原子性
- 事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败
Consistency:一致性
- 事务操作的前后,表中的记录没有变化
Isolation:隔离性
- 事务操作是相互隔离不受影响的
Durability:持久性
- 数据一旦提交,不可改变,永久改变表数据
为了测试事务特性,关闭自动提交
相关命令:
show variables like "autocommit"; #查看提交状态
set autocommit=off; #关闭自动提交
#这时如果要提交命令就需要自行提交
rollback; #数据回滚
commit; #提交数据
再开一个终端进行测试,下面简称2
现在我们第一个终端关闭了自动提交,第二个终端没有关闭自动提交
#因为我们把默认引擎改为myisam,myisam默认不支持事务,所以这里创建一个innodb的表
mysql -uroot -p123456
create table db3.test(id int)engine=innodb;
现在测试他的隔离性:
#第一个终端(关闭了自动提交的终端)
insert into db3.test values(100);
select * from db3.test;
#在第二个终端进行查看
select * from db3.test;
#这时在第二个中端并不能看到数据,因为事务的隔离性,终端1的操作在没有提交前,对于终端2来言是看不到的
#第一个终端进行提交
commit;
#再到终端2进行查看
select * from db3.test;
#这时就能查看到数据
第一个终端
第二个终端
持久性,一旦提交不可改变,永久改变了表数据,这时我们再开一个终端,数据也没有发生变化,这就是持久性
一致性:
操作前后数据一致,提交前后所有终端的数据都一致
回滚功能:
#在终端1进行删除表数据操作,
delete from db3.test;
select * from db3.test;
终端1:
终端2:
当我们在终端一上删除数据,因为隔离性,所以在终端2上还能看到数据。
rollback; #回滚,没提交前,执行错误操作,可以让事务回滚到最初状态
这个时候再次在终端1上再次查看,数据是否恢复
建表时,如何决定表使用那种存储引擎?
select 操作多的表适合使用myisam存储引擎,比较节省系统硬件资源
insert/update/delect 操作多的表适合使用innodb存储引擎,支持对单张表的并发写