MySQL数据库 - 存储引擎

一. mysql 存储引擎的相关知识

1.1 存储引擎的概念

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

1.2 存储引擎的分类

MyISAM：Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务；

InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎；

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

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

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

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

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

ERFORMANCE_SCHEMA 存储引擎该引擎主要用于收集数据库服务器性能参数；

Mrg_Myisam Merge 存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作；

二. MyISAM  和 InnoDB

1. MyISAM 存储引擎

1.1 MyISAM 相关了解

• MylSAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。
• 访问速度快，对事务完整性没有要求。
• MylSAM 适合查询、插入为主的应用。
• MylSAM 在磁盘.上存储成三个文件，文件名和表名都相同，但是扩展名分别为：
  • .frm 文件存储表结构的定义
  • 数据文件的扩展名为 .MYD (MYData)
  • 索引文件的扩展名是 .MYI (MYIndex)

1.2 MyISAM 的特点

• 表级锁定形式，数据在更新时锁定整个表
• 数据库在读写过程中相互阻塞：
  • 串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来
  • 也会在数据读取的过程中阻塞用户的数据写入数据单独写入或读取，速度过程较快且占用资源相对少。
  • 会在数据写入的过程阻塞用户数据的读取

特性：数据单独写入或读取，速度过程较快且占用资源相对少

MyIsam 是表级锁定，读或写无法同时进行
好处是：分开执行时，速度快、资源占用相对较少（相对）

表级锁介绍；

表级锁是数据库管理系统中用于并发控制的一种机制，它作用于整个数据表，而不是表中的单行或多行。当一个事务对表施加了表级锁之后，其他事务将无法对该表进行写入操作（排他锁，Exclusive Lock），或者在某些情况下也无法进行读取操作（共享锁，Shared Lock），直到第一个事务释放表级锁为止。

表级锁的特点如下：

1. 锁定粒度较粗：表级锁同时锁定的是整张表，因此，相较于行级锁，它的并发性能可能较低，因为即使只是修改表中的某一行，也需要锁定整个表。

2. 易于管理：由于锁定的对象是整个表，所以表级锁的管理和资源开销相对于行级锁较小。

3. 并发性能受限：在多用户同时访问同一张表，并且各事务涉及的数据区域不相交的情况下，表级锁会导致不必要的阻塞，降低并发性能。

4. 适用场景：表级锁通常适用于那些涉及全表扫描的大型DML操作（如DELETE、UPDATE、TRUNCATE等），或者是表设计比较简单，主要以全表操作为主的场景。

1.3 MyISAM 表支持3种不同的存储格式

① 静态（固定长度）表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。

② 动态表

动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，并且出现故障的时候恢复相对比较困难（因为会产生磁盘碎片，而且存储空间不是连续的）。

③ 压缩表

压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。（压缩的过程中会占用CPU性能

1.4 MyISAM 适用的生产场景

• 公司业务不需要事务的支持 
• 单方面读取或写入数据比较多的业务
• MyISAM 存储引擎数据读写都比较频繁场景不适合
• 使用读写并发访问相对较低的业务
• 数据修改相对较少的业务
• 对数据业务一致性要求不是非常高的业务
• 服务器硬件资源相对比较差

MyIsam：适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

2. InnoDB 存储引擎

2.1 InnoDB 相关了解

• 支持事务，支持4个事务隔离级别
• 行级锁定，但是全表扫描仍然会是表级锁定
• MySQL从5.5.5版本开始，默认的存储引擎为InnoDB
• 读写阻塞与事务隔离级别相关
• 能非常高效的缓存索引和数据
• 表与主键以簇的方式存储 BTREE
• 支持分区、表空间，类似 oracle 数据库
• 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
• 对硬件资源要求还是比较高的场合

注意：

• 使用 like 进行模糊查询时，会进行全表扫描，锁定整个表。
• 对没有创建索引的字段进行查询，也会进行全表扫描锁定整个表。
• 使用索引进行查询，则是行级锁定。

行级锁：

行级锁是一种更细粒度的锁定机制，它是数据库管理系统为了实现并发控制而在行级别上进行的锁定。与表级锁不同，行级锁只针对特定行数据进行锁定，不影响其他行的操作，这样可以极大地提高多用户环境下数据库的并发处理能力和效率。

行级锁的主要特点包括：

1. 精细控制：当一个事务更新某一行时，仅对这一行加锁，其他事务仍能同时对同一表中的其他行进行读取或更新操作。

2. 高并发性：由于锁定范围小，多个事务可以同时处理表的不同行，从而提高系统的并发性能。

3. 资源消耗：尽管行级锁能够提供更高的并发性，但其管理成本相对较高，因为它需要跟踪每一行的锁定状态，特别是在大量并发事务处理时，可能会增加内存消耗。

4. 死锁可能性：由于行锁粒度较小，多个事务相互之间请求不同的行锁时，更容易产生死锁的情况，因此数据库系统通常包含相应的死锁检测和解决机制。

5. 典型使用场景：行级锁主要用于那些频繁进行单行记录增删改查操作的应用场景，尤其对于存在大量并发更新且更新范围不重叠的数据表。

在MySQL等关系型数据库中，InnoDB存储引擎支持行级锁定，而MyISAM存储引擎则支持表级锁定。

出现问题：死锁

死锁（Deadlock）是指在并发环境下，两个或多个事务在执行过程中，因争夺资源而造成的一种相互等待的现象，使得事务无法继续执行下去。每个事务都在等待对方释放自己所需要的资源，因此陷入僵局，无法向前推进。

在数据库系统中，死锁通常是由以下因素引起的：

1. 资源争抢：多个事务同时要求获取已经被其他事务锁定的资源（如行级锁、表级锁等）。

2. 循环等待：事务间形成了一个等待链条，A事务等待B事务释放资源，B事务又在等待C事务释放资源，而C事务可能又在等待A事务释放资源，这就构成了一个循环等待。

例如，在数据库中，假设存在两个事务T1和T2：

• T1对表A的行1加了排他锁，并尝试对表B的行1加锁；
• 同时，T2对表B的行1加了排他锁，并尝试对表A的行1加锁。

这时，T1和T2就陷入了死锁状态，双方都无法继续执行，因为各自持有的资源正是对方需要的，而又都在等待对方释放资源。

为了解决死锁问题，数据库管理系统通常采用以下策略：

• 预防死锁：通过限制事务的加锁顺序或一次性申请所有需要的锁等方式来防止死锁的发生。
• 检测并解除死锁：周期性检查是否存在死锁，并通过撤销（Rollback）其中一个或多个事务来打破死锁环路。
• 超时等待：设置锁请求的超时时间，超过这个时间还没有获取到锁的事务将会自动回滚或重新尝试。

良好的应用程序设计和数据库事务管理策略有助于减少死锁的发生概率。

2.2 InnoDB 的特点

InnoDB 中不保存表的行数，如 select count(*) from table; 时，InnoDB 需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。需要注意的是当count(*)语句包含where 条件时 MyISAM 也需要扫描整个表。

对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引。

delete 清空整个表时，InnoDB 是一行一 行的删除，效率非常慢。MyISAM 则会重建表。

2.3 InnoDB 适用的生产场景

• 业务需要事务的支持。
• 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成。
• 业务数据更新较为频繁的场景。
  • 如：论坛，微博等。
• 业务数据一致性要求较高。
  • 如：银行业务。
• 硬件设备内存较大，利用 InnoDB 较好的缓存能力来提高内存利用率，减少磁盘IO的压力。

2.4 MyISAM 和 InnoDB 的区别

• InnoDB支持事物，而MyISAM不支持事物。
• lnnoDB支持行级锁，而MyISAM支持表级锁，
• InnoDB支持MVCC，而MyISAM不支持。
• lnnoDB支持外键。而MyISAM不支持。
• lnnoDB全文索引，而MyISAM支持。

MyISAM：不支持事务和外键约束，占用资源较小，访问速度快，表级锁定，支持全文索引，适用于不需要事务处理，单独写入或查询的应用场景。 存储格式： 表名.frm（表结构文件）  表名.MYD（数据文件）   表名.MYI（索引文件）

InnoDB：支持事务处理、外键约束，缓存能力较好，支持行级锁定，读写并发能力较好，5.5版本后支持全文索引，适用于一致性要求高、数据更新频繁的应用场景。表名.frm（表结构文件）  表名.idb（表数据文件/索引文件）  db.opt（表属性文件）

功能	MyISAM	InnoDB
存储限制	256TB	64TB
事务	不支持	支持
全文索引	支持	不支持
B树索引	支持	支持
哈希索引	不支持	不支持
集群索引	不支持	支持
数据索引	不支持	支持
数据压缩	支持	不支持
空间使用率	低	高
外键	不支持	支持

2.5 企业选择存储引擎依据

• 需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景。
• 支持的字段和数据类型
  • 所有引擎都支持通用的数据类型
  • 但不是所有的弓|擎都支持其它的字段类型，如二进制对象.
• 锁定类型：不同的存储引擎支持不同级别的锁定
  • 表锁定：MyISAM 支持
  • 行锁定：InnoDB 支持
• 索引的支持
  • 建立索引在搜索和恢复数据库中的数据时能显著提高性能
  • 不同的存储引擎提供不同的制作索引的技术
  • 有些存储引擎根本不支持索引
• 事务处理的支持
  • 提高在向表中更新和插入信息期间的可靠性
  • 可根据企业业务是否要支持事务选择存储引擎

2.6 表锁定和行锁定的区别

表锁定和行锁定是数据库管理系统中用于并发控制的两种不同级别的锁定机制，它们的主要区别在于锁定的范围和并发性能：

表锁定（Table Locks）：

• 表锁定作用于整个数据表，当一个事务对表进行锁定时，其他事务无法对该表进行任何类型的读写操作，直到锁定事务结束并释放表锁。
• 表锁定粒度大，管理相对简单，开销较小，但并发性能较差，因为它阻止了同一表上其他事务的同时执行，即使这些事务可能只是修改不同的行。
• 表锁定通常适用于那些涉及全表操作（如大规模数据导入导出、表结构调整等）或者表数据相对较小，不需要高度并发访问的场景。

行锁定（Row Locks）：

• 行锁定只作用于表中的单行数据，这意味着一个事务可以锁定表中的特定行，而其他事务仍可以读取或修改表中未被锁定的其他行。
• 行锁定粒度小，可以提供更好的并发性能，因为不同事务可以同时对同一表的不同行进行操作，降低了并发事务间的冲突。
• 行锁定虽然提高了并发访问能力，但管理开销相对更大，因为需要跟踪和维护每行的锁定状态，尤其是在高并发环境下，可能出现更多锁竞争和死锁问题。
• 行锁定广泛应用于需要频繁更新单行数据，同时又要保持高并发读写的场景，如在线交易系统、银行账户转账等。

综上所述，表锁定和行锁定的选择取决于具体的应用需求和数据库系统的特点。在支持行锁定的数据库中（如MySQL的InnoDB存储引擎），通常可以根据事务的特性选择最适合的锁定级别，以达到最佳的性能和并发控制效果。

三. MySQL 存储引擎的管理

1. 存储引擎的查看

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

show engines;

1.2 查看当前的默认的存储引擎

show variables like '%storage_engine%';

1.3 查看指定表的存储引擎

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

2. 存储引擎的修改

2.1 创建表时直接指定

create table 表名(...) engine=引擎名;

2.2 直接修改表结构中的存储引擎

alter table 表名 engine = 引擎名称;

2.3 修改默认的存储引擎

通过修改 /etc/my.cnf 配置文件

修改默认的存储引擎后，对已经创建好的表的存储引擎没有影响，影响的是新建的表。

找到mysql安装目录下的模块主配置文件 /etc/my.cnf ： 找到default-storage-engine=INNODB 改为目标引擎，default-storage-engine=MYISAM 重启mysqld服务

测试:

不指定就是默认

也可以在建表的时候指定

3. InnoDB 的索引和锁的关系

 InnoDB 行锁是通过给索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁（一般称为表锁）。

测试表准备：

insert into class5 values(1,'小明',18,'男');
insert into class5 values(2,'小红',19,'女');
insert into class5 values(3,'小刚',19,'男');
insert into class5 values(4,'小绿',17,'女');
insert into class5 values(5,'小黑',20,'男');

3.1 InnoDB 行锁 演示

事务A:

事务B:

3.2 InnoDB 表锁演示

事务A:

事务B：

3.3 死锁演示

行锁如果使用不当会导致死锁（死锁一般是事务相互等待对方释放资源，最后形成环路造成的）

事务A	事务B
begin;	begin;
delete from class6 where id=2;#事务结束前，id=2的行会被锁定
	select * from t1 where id=1 for update; #加排他锁，模拟并发情况，锁定id=1的行
delete from t1 where id=1; #死锁产生
	update t1 set name='abc' where id=2; #死锁产生。因为会话1中id=5的行还在删除过程中，该行已被锁定
rollback; #回滚，结束事务。id=5的行被解锁
	update t1 set name='abc' where id=2; #成功更新数据

事务A:

事务B:

事务A：

事务A:

事务B:

避免死锁的解决方案 
1.使用更合理的业务逻辑，以固定的顺序访问表和行。

2、大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4、降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5、为表添加合理的索引。如果不使用索引将会为表的每一行记录添加上锁，死锁的概率大大增加。

总结：

MyISAM 和 InnoDB 的区别：
MyISAM： 不支持事务和外键约束，占用空间较小，访问速度快，表级锁定，适用于不需要事务处理、单独写入或查询的应用场景。（写入和查询不一起使用的场景）

InnoDB： 支持事务处理、外键约束、占用空间比MyISAM 大，支持行级锁定，读写开发能力较好，适用于需要事务处理、读写频繁的应用场景。

查看系统支持的存储引擎：
show engines;

查看表使用的存储引擎：
方法一：show table status from 库名 where name='表名'\G

方法二：show create table 表名;

修改存储引擎：
方法一：修改已存在的表使用的存储引擎

alter table 表名 engine=存储引擎名称;

方法二：修改配置文件，指定默认存储引擎

vim /etc/my.cnf

[mysqld]

default-storage-engine=InnoDB #修改这一行，指定默认存储引擎为InnoDB

systemctl restart mysqld #重启服务

方法三：创建表时指定存储引擎

create table 表名(字段1 数据类型,...) engine=存储引擎名称;

四. 关于锁机制

锁机制 是为了避免，在数据库有并发事务的时候，可能会产生数据的不一致而诞生的的一个机制。
锁从类别上分为：

•  共享锁：又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。
•  排他锁：又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁，排他锁只可以加一个，他和其他的排他锁,共享锁都相斥。

MySQL有三种锁的级别：页级、表级、行级。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度

死锁
MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在 InnoDB 中，锁是逐步获得的，就造成了死锁的可能。
两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
产生死锁的原因主要是
（1）系统资源不足。
（2）进程运行推进的顺序不合适。
（3）资源分配不当等。
如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
产生死锁的四个必要条件
死锁4大要素：互斥，持有并请求，不可剥夺，持续等待
（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。
解决方法
1、撤消陷于死锁的全部进程；
2、逐个撤消陷于死锁的进程，直到死锁不存在；
3、从陷于死锁的进程中逐个强迫放弃所占用的资源，直至死锁消失。
4、从另外一些进程那里强行剥夺足够数量的资源分配给死锁进程，以解除死锁状态
如何避免死锁

• 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
• 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
• 优化程序，检查并避免死锁现象出现；
• 对所有的脚本和SP都要仔细测试，在正式版本之前；
• 所有的SP都要有错误处理(通过@error)；
• 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁。
• 以固定的顺序访问表和行。
  • 分为两种情景：
  • 对于不同事务访问不同的表，尽量做到访问表的顺序一致；
  • 对于不同事务访问相同的表，尽量对记录的id做好排序，执行顺序一致；
• 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大

在MySQL中，死锁、悲观锁和乐观锁是并发控制中三种不同的情景和技术：

1. 死锁（Deadlock）： 死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种相互等待的现象，使得事务无法继续执行下去。例如，事务A持有资源X并请求资源Y，而事务B持有资源Y并请求资源X，如果双方都不释放已持有的资源，就形成了一个死循环，导致死锁。MySQL的InnoDB存储引擎通过检测死锁并自动回滚其中一个事务来解决死锁问题。

2. 悲观锁（Pessimistic Locking）： 悲观锁是一种保守的并发控制策略，它假设会发生并发冲突，因此在事务开始操作数据前就先行锁定该数据，防止其他事务修改。在MySQL中，可以通过SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE等语句实现悲观锁。当一个事务对数据加悲观锁后，其他事务将无法修改该数据，直到第一个事务释放锁。

3. 乐观锁（Optimistic Locking）： 相比之下，乐观锁策略较为乐观，它假设并发冲突不太可能发生，因此在读取数据时不立即加锁，而是在更新数据时才检查在此期间是否有其他事务修改过该数据。在MySQL中，乐观锁的实现通常不依赖于数据库本身的锁机制，而是通过在表中添加一个版本号或时间戳字段来实现。当事务尝试更新数据时，会对比之前读取时的版本号或时间戳，如果发现已经被其他事务修改过，则此次更新失败，抛出异常，由应用程序决定如何处理这种并发冲突。

举例来说，使用乐观锁时，事务在更新记录时会添加一个版本号的检查条件，如UPDATE table SET column=value WHERE id=xx AND version=old_version_number，如果旧版本号与当前数据库中的版本号不符，则更新失败，表示数据在读取后被其他事务修改过。

总之，在MySQL中，死锁是并发控制中需要避免的问题，而悲观锁和乐观锁是两种不同的并发控制策略，适用于不同的并发场景，根据业务需求和预期的并发程度选择合适的锁策略。