【MySQL】数据库——存储引擎

一、存储引擎概述

1.概念

• MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
• 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
• 存储引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作
• MySQL系统中，存储引擎工作在文件系统之上，数据库的数据会先传到存储引擎，按照各个存储引擎的存储格式，保存到文件系统

MySQL 查询数据的执行过程:

1、客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。

2、MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果;否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。

3、MySQL根据执行计划，调用存储引擎来执行查询。

4、将结果返回给客户端，同时缓存查询结果。 

2.常用的存储引擎

• MyISAM：Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
• InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎

二、MyISAM和InnoDB

类型	MyISAM	InnoDB
事务	不支持事务	支持事务
外键约束	不支持外键约束	支持外键约束
锁定方式	只支持表级锁定	支持行级锁定，全表扫描时会表级锁定
读写性能	只支持单独的查询与插入，读写阻塞	读写和事务并发能力较好
全文索引	支持全文索引	支持全文索引（5.5版本以后支持）
硬件	硬件资源占用较小	缓存能力较好，可以减少磁盘IO的压力
存储文件	数据文件和索引文件分开存储，存储为三个文件（.frm的表结构文件，.MYD的数据文件，.MYI的索引文件）	数据文件也是索引文件，存储成两个文件(.frm的表结构文件，.ibd的数据和索引文件)
使用场景	使用场景：使用不需要事务支持，单独的查询和插入的业务场景	使用场景：使用于需要事务支持，一致性要求比较高，数据会频繁更新，读写并发高的业务场景；

三、MySQL储存引擎的管理

1.查看表使用的存储引擎

use 库名;
show create table 表名;
 
show table status [from 库名] where name='表名'\G;

2.查看系统支持的存储引擎

show engines;

3.修改表默认存储引擎

vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb/myisam
 
systemctl restart mysqld
 
mysql -uroot -p密码
 
create table 表名(字段 数据类型,...);  #创建一个表
show create table 表名;                #查看表结构，储存引擎

 4.当表已经存在，想要修改其存储引擎

alter table 表名 engine=innodb/myisam;

5.创建表的时候，直接指定存储引擎

create table 表名 (字段 数据类型,...) engine=innodb/myisam; 
#创建表时指定engine为innoDB

四、死锁：

定义：死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。

如何避免死锁？
1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect

show VARIABLES like  'innodb_deadlock_detect';       #查看当前死锁检测是否开启
 
set global innodb_deadlock_detect = ON;              #ON为开启死锁检测，OFF为关闭

3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用 提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多血少的场景下采用乐观锁机制