MySQL学习笔记

学习笔记 评论

数据库基础知识

DML: （Data Manipulation Language）数据操作语言，用于增删改查数据，包括SELECT、INSERT、UPDATE、DELETE等操作。

DDL: （Data Definition Language）数据定义语言，用于定义数据库对象，包括CREATE、ALTER、DROP等操作。

SQL: （Structured Query Language）结构化查询语言，是一种用来操作关系型数据库的语言。

数据库范式

  1. 第一范式（1NF）：属性不可再分
    • 属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。
  2. 第二范式（2NF）：属性完全依赖于主键,消除了非主属性对于码的部分函数依赖
    • 2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。
  3. 第三范式（3NF）：属性不依赖于其他非主属性，消除了非主属性对于码的传递函数依赖
    • 3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。

MySQL

字段类型

问题:MySQL中有哪些字段类型？

  • 数值: INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT、FLOAT、DOUBLE、DECIMAL
  • 字符串: CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
  • 日期时间: DATE、TIME、YEAR、DATETIME、TIMESTAMP

问题:MySQL中的CHAR和VARCHAR有什么区别？
CHAR 是定长字符串，VARCHAR 是变长字符串。CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的。VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

问题:MySQL中DECIMAL 和 FLOAT/DOUBLE 的区别是什么？
DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

问题:为什么不推荐使用 TEXT 和 BLOB？

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储更长的字符串，即长文本数据，BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

BLOB 和 TEXT 类型具有一些缺点和限制

  • 不能有默认值。
  • 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表。
  • 检索效率较低。
  • 不能直接创建索引，需要指定前缀长度。
  • 可能会消耗大量的网络和 IO 带宽。
  • 可能导致表上的 DML 操作变慢。

问题:NULL和’'的区别是什么？
NULL 跟 ‘’(空字符串)是两个完全不一样的值

  • NULL 表示未知的值，是一个特殊的值，表示这个字段没有值。
  • ‘’ 表示一个空字符串，表示这个字段有值，长度为0,不占空间。
  • NULL会影响聚合函数的结果,一般会忽略null值
  • NULL必须使用is null 或者not null比较

MySQL存储引擎

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

问题:InnoDB 和 MyISAM 的区别是什么？

  1. InnoDB 支持事务，MyISAM 不支持事务。
  2. InnoDB 支持外键，MyISAM 不支持外键。
  3. InnoDB 支持行级锁，MyISAM 支持表级锁。
  4. InnoDB 支持 MVCC 多版本并发控制，MyISAM 不支持。
  5. InnoDB 支持崩溃恢复，MyISAM 不支持。
  6. InnoDB 是聚簇索引，MyISAM 是非聚簇索引。

索引详解

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

优点:

  1. 提高查询速度
  2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
    缺点:
  3. 创建索引和维护索引会耗费时间，这种时间随着数据量的增加而增加
  4. 索引需要占用物理空间，除了数据表占用的物理空间之外，每个索引还要占用一定的物理空间

索引类型分类

按数据结构纬度分类:

  • B-Tree 索引
  • Hash 索引
  • 全文索引
  • RTree索引

按底层存储方式分类:

  • 聚簇索引
  • 非聚簇索引

按应用纬度分类:

  • 主键索引:加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
  • 普通索引：仅加速查询。
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
  • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
  • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

索引底层数据结构选型

Hash 表
不支持范围查找，只支持等值查找，适用于等值查询场景。

二叉查找树
二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，二叉查找树的性能非常依赖于它的平衡程度，它具有以下特点：

  1. 左子树所有节点的值均小于根节点的值。
  2. 右子树所有节点的值均大于根节点的值。
  3. 左右子树也分别为二叉查找树。

当二叉查找树是平衡的时候查询的时间复杂度为 O(log2(N))，当二叉查找树不平衡时，树会退化成线性链表（也被称为斜树），导致查询效率急剧下降，时间复杂退化为 O（N）。

平衡二叉树(AVL)
AVL 树的特点是保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树，它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了数据库写操作的性能。并且， 在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。 磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

红黑树
红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

  1. 每个节点要么是红色，要么是黑色。
  2. 根节点是黑色。
  3. 每个叶子节点（NIL 节点，空节点）是黑色的。
  4. 如果一个节点是红色的，则它的子节点必须是黑色的。
  5. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

B树&B+树
B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。二者的异同:

  1. B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。(每次读取内存中的一页,B+树可检索的节点数量多于B树,减少了IO消耗)
  2. B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
  4. 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

  • MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。
  • InnoDB 引擎中，其数据文件本身就是索引文件。其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”，而其余的索引都作为 辅助索引 ，辅助索引的 data 域存储相应记录主键的值而不是地址。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据 (只要检索一次)；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。(检索两次)

索引类型详解

主键索引

数据表的主键列使用的就是主键索引。

二级索引

的叶子节点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。

唯一索引，普通索引，前缀索引等索引都属于二级索引。

  1. 唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  2. 普通索引(Index):普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
  3. 前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
  4. 全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚簇索引与非聚簇索引

聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。
优点:

  • 查询速度非常快
  • 对排序查找和范围查找优化
    缺点:
  • 依赖有序的数据
  • 更新代价大

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。
优点:

  • 更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的
    缺点:
  • 依赖于有序的数据
  • 可能会二次查询(回表)
    • 非聚簇索引不一定回表查询。试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

覆盖索引和联合索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index） 。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则
最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。包含这些列的所有查询都会走索引而不会全表扫描。

最左匹配原则会一直向右匹配，直到遇到 范围查询如 >、< 为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配

索引下推优化

索引下推（Index Condition Pushdown，简称 ICP） 允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

1
2
3
# 查询名字以"Aoki"开头且年龄为30岁的用户
EXPLAIN SELECT * FROM usr
WHERE name LIKE 'Aoki%' AND age = 30;
  • 没有索引下推之前，即使 name 字段建立的索引可以帮助我们快速定位到了以“张”开头的用户，但我们仍然需要对每一个找到的以“张”开头的用户进行回表操作，获取完整的用户数据，再判断 age 字段是否等于 30。
  • 有了索引下推之后，存储引擎会在使用 name 索引查找以"张"开头的记录时，同时检查 age 字段是否等于 30。这样，只有同时满足 name 和 age 条件的记录才会被返回，减少了回表次数。

没有索引下推之前：

  • 存储引擎层先根据 name 索引字段找到所有以“张”开头用户的主键 ID，然后二次回表查询，获取完整的用户数据。
  • 存储引擎层把所有以“张”开头的用户数据全部交给 Server 层，Server 层根据age 字段是否等于 30 这一条件再进一步做筛选。

有了索引下推之后：

  • 存储引擎层先根据 name 索引字段找到所有以“张”开头的用户，然后直接判断age 字段是否等于 30，筛选出符合条件的 主键 ID。
  • 二次回表查询，根据符合条件的主键 ID 去获取完整的用户数据，
  • 存储引擎层把符合条件的用户数据全部交给 Server 层。

索引失效情况

  1. 创建了组合索引，但查询条件未遵守最左匹配原则;
  2. 在索引列上进行计算、函数、类型转换等操作;
  3. 以%开头的LIKE查询;
  4. 在查询条件中使用OR,且OR的千户条件中有一个列没有索引,则设计到的索引也不会被使用
  5. In的取值范围过大，超过了优化器的阈值

日志详解

重做日志（Redo Log）

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

  • MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。
  • 后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
  • 更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。
  • 然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
  • 每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成(存储位置的哪个数据做了哪个修改)

刷盘时机

  1. 事务提交时，会把 redo log buffer 中的日志刷到 redo log 文件里。(设置0:每次提交都不刷盘可能丢一秒数据,1:每次提交的刷盘,2:提交时把redo log 写入page cache(文件系统缓存))
  2. log buffer空间不足时(redo log占log buffer的50%时)，会把 redo log buffer 中的日志刷到 redo log 文件里。
  3. 事务日志缓冲区满
  4. Checkpoint:InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
  5. 后台线程刷盘:InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页（已修改但尚未写入磁盘的数据页）刷新到磁盘，并将相关的重做日志一同刷新。
  6. 正常关闭服务器

日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。采用的是环形数组形式，从头开始写，写到末尾又回到头循环写

  • write pos 是当前记录的位置，一边写一边后移
  • checkpoint 是当前要擦除的位置，也是往后推移
  • 每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。
  • 每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新
  • 如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

问题:只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

  • 数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没有必要把完整的数据页刷盘，而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。
  • 如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

逻辑日志（Binlog）

  • redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。
  • binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
  • binlog用来同步数据,数据备份、主备、主主、主从都离不开binlog，保证数据一致性。

记录格式

  1. Statement 格式:记录的是 SQL 语句，例如update T set update_time=now() where id=1
  2. Row 格式:记录的是行的内容，解决了now()具体值不明确的问题
  3. Mixed 格式:根据执行的 SQL 语句来决定使用 Row 格式还是 Statement 格式

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

两阶段提交

  • redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。
  • binlog（归档日志）保证了MySQL集群架构的数据一致性。
  • redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样

问题情景
假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2
假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案
将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。


使用两阶段提交后

  • MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。
  • redo log设置commit阶段发生异常,并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

回滚日志（Undo Log）

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

事务隔离级别

并发事务带来的问题

  1. 脏读:一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据

  2. 丢失修改:在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

  3. 不可重复读:指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

  4. 幻读:幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

并发事务的控制方式

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

  • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

事务隔离级别

  1. 读未提交（Read Uncommitted）:最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  2. 读取已提交（Read Committed）:允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  3. 可重复读（Repeatable Read）:对相同字段的多次读取结果都是一致的，除非数据是被本身事务所修改，可以阻止脏读、不可重复读，但幻读仍有可能发生。InnoDB 存储引擎的默认支持的隔离级别
  4. 可串行化（Serializable）:最高的隔离级别，完全服从ACID的隔离级别，确保不发生脏读、不可重复读、幻读，通过强制事务排序来防止多个事务并发执行。
隔离级别 脏读 不可重复读 幻读
读未提交 是 是 是
读已提交 否 是 是
可重复读 否 否 是
可串行化 否 否 否

MySQL锁

表级锁和行级锁对比：

  • 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的
  • InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

InnoDB 支持三种行锁定方式:

  • 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：间隙锁是锁定一个范围，但不包括记录本身。
  • 临键锁（Next-Key Lock）：锁定一个范围，包含记录本身，主要目的是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

GladdduckKB Master

我写的个人简介。