Mysql：事务、存储引擎、索引、存储过程、触发器、锁

目录

一、事务

1. 事务特性：

2.事务并发问题：

3.事务隔离级别（解决事务并发问题）

二、存储引擎 

MySQL体系结构：​编辑

相关操作

InnoDB

MyISAM

Memory

存储引擎特点

存储引擎的选择

三、索引

定义

优缺点

索引结构

思考题：为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

索引分类

聚集索引选取规则：

思考题

1. 以下 SQL 语句，哪个执行效率高？为什么？

2. InnoDB 主键索引的 B+Tree 高度为多少？

四、存储过程 

特点：

存储过程和函数的区别：

五、触发器

六、锁

分类

行级锁，主要分为以下三类

InnoDB实现了以下两种类型的行锁

一、事务

1. 事务特性：

• 原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败

• 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态

• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行（事务A和B同时操作数据库 互不影响）

• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的（永久的保留在磁盘当中）

2.事务并发问题：

注意：多个并发事务在执行的过程的当中所出现的 脏读、不可重复读、幻读的问题

脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

3.事务隔离级别（解决事务并发问题）

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

二、存储引擎 

MySQL体系结构：

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。 默认存储引擎是InnoDB。

相关操作

-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(
    ...
) ENGINE=INNODB;
-- 查看当前数据库支持的存储引擎
show engines;

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。

特点：

• DML 操作遵循 ACID 模型，支持事务

• 行级锁，提高并发访问性能

• 支持外键约束，保证数据的完整性和正确性

文件：

• xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table，决定多张表共享一个表空间还是每张表对应一个表空间

知识点：

查看 Mysql 变量： show variables like 'innodb_file_per_table';

从idb文件提取表结构数据： （在cmd运行） ibd2sdi xxx.ibd

InnoDB 逻辑存储结构：

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

• 不支持事务，不支持外键

• 支持表锁，不支持行锁

• 访问速度快

文件：

• xxx.sdi: 存储表结构信息

• xxx.MYD: 存储数据

• xxx.MYI: 存储索引

Memory

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

• 存放在内存中，速度快

• hash索引（默认）

文件：

• xxx.sdi: 存储表结构信息

存储引擎特点

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持（5.6版本之后）	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

Memory表的所有数据都是存储在内存上的，如果内存出现异常会影响到数据的完整性。如果重启机器或者关机，表中的所有数据都将消失，因此，基于Memory存储引擎的表的生命周期都比较短，一般都是一次性的。 基于内存中的特性，这类表的处理速度会非常快，但是，其数据易丢失，生命周期短。

 

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择

• MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。

• Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。

三、索引

定义

索引是一种数据结构，是用来高效获取数据的

索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。

优缺点

优点：

• 提高数据检索效率，降低数据库的IO成本（提高查询效率）

• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗（提高排序效率）

缺点：

• 索引列也是要占用空间的（占用磁盘空间，可忽略因为其比较便宜）

• 索引大大提高了查询效率，但降低了表更新的速度，比如 INSERT、UPDATE、DELETE（维护表）

索引结构

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持B+树索引
Hash	底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-Text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

二叉树缺点：1.顺序插入时会形成链表，查询性能大大降低

                      2.数据量过多时，层级太深，影响查找效率 

排序二叉树（红黑树）也存在大数据量情况下，层级较深，检索速度慢的问题。

以及

1.它太深了 数据所在的(高)深度决定着它的 IO 操作次数，IO 操作耗时比较大。

2.它太小了 每一个磁盘块(节点/页)，保存的数据量太小了，没有很好的利用操作磁盘 IO 的数据交换特性，也没有利用好磁盘 IO 的预读能力（空间局部性原理），从而带来频繁的 IO 操作。

没能很好的利用数据交换特性： 传统I/O 操作，是以一页数据为单位，一次交换的数据是大约为4K。然而(关键字+数据区+子节点引用)这些数据显然是填充不满 4K 的。如果一次加载回来的数据很少，排除用不到的数据后，我们能用到的数据就更少了。二叉树又那么高，IO 操作那么多还那么费时，检索数据会做很多无用功。

没有很好的利用磁盘IO的预读能力： 操作系统在IO操作时，比如说要读 2M txt 文件。先把头部 4k 读回来后，它会认为你马上就会使用接下来的 4k 数据(举个栗子：比如你读书，在读完第1页时，你会马上去读第2页。操作系统提供的空间局部性原理，就是在你读第1页的同时，它会去把第 2 页的内容给你提前加载过来，就是提前加载的意思)。真正做一次数据交换，并不仅仅是做一次 4K 数据量的交换，会根据空间局部性原理，去加载更多的数据，比如：16K，24K。这样就可以提高 IO 交换的能力，这是操作系统给我们定义的空间局部性原理。一页的定义，MySQL定义是16K ，MySQL进行磁盘交互，一次性交互16K。操作系统是一次性交互4K

为了解决上述问题，可以使用 B-Tree 结构。 B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）

B+Tree：

与 B-Tree 的区别：

• 所有的数据都会出现在叶子节点

• 叶子节点形成一个单向链表

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

Hash：

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。 如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点：

• Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、...）

• 无法利用索引完成排序操作

• 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

• Memory

• InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

思考题：为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

• 相对于二叉树，层级更少，搜索效率高

• 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

• 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引

• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引

• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

思考题

1. 以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

2. InnoDB 主键索引的 B+Tree 高度为多少？

答：假设一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键假设为bigint，占用字节数为8. 可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字节数，n 表示当前节点存储的key的数量，(n + 1) 表示指针数量（比key多一个）。算出n约为1170。

如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736； 如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856。

另外，如果有成千上万的数据，那么就要考虑分表，涉及运维篇知识。

四、存储过程 

是一种用来封装复杂结构(包括sql语句)的子程序

特点：

1.封装、复用

2.可以接收参数，也可以返回数据

3.减少网络交互，效率提升

存储过程和函数的区别：

1.函数在定义的时候是有一个返回值的     存储过程可通过参数返回n个值

2.函数所有参数类型都是in类型                 存储过程可以是in inout out

3.函数面可以封装很复杂的算法，但是不能封装sql语句（不允许一个函数返回集合）

存储过程功能比函数更强大，放什么都可以

4.函数调用时可通过select直接访问          存储过程需要用call去调用

存储过程和存储函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的工作，减少数据在数据库和应用服务器之间的传输，能够提高数据库的处理效率。存储过程在高并发数据中使用的比较多

五、触发器

一种特殊的存储过程，当指定事件发生时，系统自动调用  对表的实时监测

介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发（比如说 一条语句影响了 5 行 则会被触发 5 次），不支持语句级触发（比如说 一条语句影响了 5 行 则会被触发 1 次）。

触发器类型	NEW 和 OLD
INSERT	NEW 表示将要或者已经新增的数据
UPDATE	OLD表示修改之前的数据，NEW表示将要或已经修改后的数据
DELETE	OLD表示将要或者已经删除的数据

六、锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

NOTE : 针对事物才有加锁的意义。

分类

MySQL中的锁，按照锁的粒度分，分为以下三类：

1. 全局锁：锁定数据库中的所有表。
2. 表级锁：每次操作锁住整张表。
3. 行级锁：每次操作锁住对应的行数据。

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

1. 表锁：对于表锁，分为两类：1.表共享读锁（read lock）所有的事物都只能读（当前加锁的客户端也只能读，不能写），不能写 2.表独占写锁（write lock），对当前加锁的客户端，可读可写，对于其他的客户端，不可读也不可写。
   读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

2. 元数据锁（meta data lock，MDL），MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）;当对表结构进行变更操作的时候，加MDL写锁（排他）。

3. 意向锁: 为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
   一个客户端对某一行加上了行锁，那么系统也会对其加上一个意向锁，当别的客户端来想要对其加上表锁时，便会检查意向锁是否兼容，若是不兼容，便会阻塞直到意向锁释放。

意向锁兼容性：

1. 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
2. 意向排他锁（lX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥。

行锁：

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

行级锁，主要分为以下三类

1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC（read commit ）、RR（repeat read）隔离级别下都支持。
2. 间隙锁（GapLock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。比如说 两个临近叶子节点为 15 23，那么间隙就是指 [15 , 23],锁的是这个间隙。
3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

InnoDB实现了以下两种类型的行锁

1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL	行锁类型	说明
insert	排他锁	自动加锁
update	排他锁	自动加锁
delete	排他锁	自动加锁
select	不加任何锁
select　lock　in　share mode	排他锁	需要手动在SELECT之后加LOCK IN SHARE MODE
select　for　update	排他锁	需要手动在SELECT之后加FOR UPDATE

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key 锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key 锁进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
3. 索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

最后

以上就是忧伤御姐为你收集整理的Mysql：事务、存储引擎、索引、存储过程、触发器、锁的全部内容，希望文章能够帮你解决Mysql：事务、存储引擎、索引、存储过程、触发器、锁所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。