数据库之十一 索引、事务隔离机制和锁机制

【一】索引

【1】索引的概念

• 索引（在MySQL中也称为"键"）是存储引擎用于快速查找记录的一种数据结构，是数据库中的重要组成部分。索引的基本功能是帮助数据库快速定位和访问数据，从而提高查询性能。

• 索引在数据库中起到类似于书的目录的作用。当我们想要在一本书中找到特定的主题时，我们会先查阅目录，找到对应的页码，然后直接翻到该页码即可查看相关内容。在MySQL中，存储引擎使用类似的方式利用索引来加速查询：

  1. 首先，在索引中找到对应的值。
  2. 然后，根据匹配的索引记录找到对应的数据行。

• 索引的优化是查询性能优化中最有效的手段之一。在数据量较大且负载较高的情况下，良好的索引设计可以将查询性能提高几个数量级。相反，不恰当的索引设计会导致性能下降。

【2】MySQL中索引的类型

1. 主键约束（PRIMARY KEY）
   • 主键是用于唯一标识表中每一行数据的列，它具有唯一性和非空性。
   • 不仅可以加速查询速度，还具有对应的约束条件
2. 唯一约束（UNIQUE）
   • 唯一约束用于确保某一列或多列的值在表中是唯一的。
   • 不仅可以加速查询速度，还具有对应的约束条件
3. 普通索引（INDEX）：
   • 普通索引也称为非唯一索引，它可以加速查询速度，但不具备唯一性约束。
   • 可以在单个列或多个列上创建普通索引。
4. 外键约束（FOREIGN KEY）
   • 外键约束用于建立表与表之间的关联关系。
   • 不是用来加速查询的

【3】索引的本质

• 索引的本质是通过缩小需要筛选的数据范围，从而加快查询速度。它将随机访问转换为顺序访问，类似于在书中先查目录再找到对应的页码，从而提高查询效率。

• 通过索引机制，我们可以以一种固定的方式查询数据，而不需要逐个遍历所有数据。索引可以帮助数据库引擎快速定位和访问数据，从而加速查询过程。

【4】索引的缺点

• 索引的创建速度在表中存在大量数据时会变得较慢。创建索引需要对表中的数据进行排序和组织，这个过程可能会消耗较长的时间。

• 在索引创建完成后，对表的查询性能会显著提升，但写入性能会下降。因为每次对表进行插入、更新或删除操作时，都需要更新索引，这会增加额外的开销。

• 因此，不应该随意地创建索引。需要根据具体的业务需求和数据特点来判断是否需要创建索引，以避免不必要的性能损耗。

【5】索引的使用场景

• 在考虑是否创建索引时，需要综合考虑以下几点：

  1. 数据量较大且经常对某列或某几列进行条件查询。
  2. 对这些列的插入和修改操作频率较低。
  3. 索引会占用额外的磁盘空间。

• 当满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。索引可以加速查询过程，特别是在大数据量的情况下。

• 反之，如果某列不经常用于条件查询，或经常进行插入、修改操作，或磁盘空间有限，就不建议创建索引。创建过多的索引可能会增加存储空间和写入性能的开销，甚至导致索引失效，影响整体性能。因此，需要根据具体情况慎重考虑是否创建索引。

【6】索引操作

（1）创建索引

• 对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index  索引名 on 表名(字段名);

# 索引名的命名规则一般是：index_表名_列名

• 在实际开发中，如果项目的数据量非常大，创建索引的成本是非常高的，所以在实际开发时，在建表的时候索引就要规划好，如果表里有很多数据了，建议不要再额外添加索引了。

（2）查看索引

show index from 表名;

（3）删除索引

drop index 索引名 on 表名;

【7】索引的数据结构：

• 索引背后使用的是B+树数据结构。在了解B+树之前，先要了解B树

（1）B树

• B树是一种N叉搜索树（查找树），它的本质是一种平衡的多路搜索树。
• B树的特点是每个节点可以存储多个键值对，并且节点中的键值对按照键的大小顺序排列。
• B树的每个节点除了存储键值对外，还存储了指向子节点的指针。
• B树通过这种结构可以在较少的磁盘I/O次数下进行高效的查找操作。

（2）B+树

• B+树是在B树的基础上进行改进的一种平衡查找树结构。
• B+树与B树的主要区别在于B+树的非叶子节点只存储键，而不存储值，而所有的值都存储在叶子节点中。
• 叶子节点之间通过链表进行连接，形成一个有序的链表结构。
• B+树的叶子节点存储了所有的数据记录，而非叶子节点只存储了键，这样可以减少非叶子节点的存储空间，提高了存储效率。

（3）B+树的特点

• 每个节点可以存储N个键值对，将节点的键值对划分为N个区间（B树是N+1个区间）。
• 每个节点的键值在子节点中都存在，并且是子节点的最大值。
• B+树的叶子节点之间通过链表相连，形成一个有序的链表结构。
• 所有的数据都包含在叶子节点中，非叶子节点的键最终都会出现在叶子节点中。
• 每个叶子节点关联一个记录，对应数据库中的每一行记录。

（4）B+树的优势

• 每个节点可以存储更多的键值对，使得树的高度相对较矮，减少了磁盘I/O的访问次数，提高查询效率。
• 所有的查询操作都会落在叶子节点上，查询任何一个数据经过的I/O访问次数是一样的，稳定性较好。
• 叶子节点之间通过链表相连，支持更直接的范围查询，同时也方便实现顺序访问。
• 由于数据都在叶子节点上，非叶子节点只存储键，可以将叶子节点的一部分进行缓存，进一步减少I/O次数。

（5）详讲B+树

• B+树是一种平衡查找树的数据结构，被广泛应用于数据库系统和文件系统中的索引结构。它相比于其他树状结构（如二叉搜索树）具有更高的查询性能和更好的存储利用率。

• B+树具有以下特点：

  1. 平衡性：B+树是一棵平衡树，即每个节点的左子树和右子树的高度差不超过1。这样可以保持树的高度相对较小，使得查找操作的复杂度保持在O(log n)级别。
  2. 多路搜索：B+树的每个内部节点可以拥有多个子节点，这使得B+树能够在每个节点上存储更多的关键字，提高了存储的效率。
  3. 顺序访问性：B+树的所有叶子节点按照关键字的大小顺序形成一个有序链表，可以支持范围查询和排序等操作。同时，由于内部节点只包含关键字而不包含具体数据，可以在内存中存放更多的节点，从而提高了缓存的命中率。
  4. 存储利用率高：B+树的内部节点不存储数据，只存储关键字，因此相比于其他树结构（如B树），B+树可以存储更多的关键字。这使得B+树在磁盘或内存中的存储利用率更高。

• B+树的应用主要包括数据库系统和文件系统中的索引结构。在数据库系统中，B+树常被用作数据库表的索引结构，能够高效地支持查找、插入、删除等操作。在文件系统中，B+树常被用来维护文件的目录结构，可以快速定位和访问文件。

• 总之，B+树是一种高效的数据结构，具有平衡性、多路搜索、顺序访问性和存储利用率高等特点，适用于需要高效查询和排序的场景。它的查询次数由树的层级决定，层级越低查询次数越少。另外，B+树的叶子节点存放真实数据，而根和树枝节点存放的是虚拟数据（即关键字）。

【8】主键（聚集、聚簇）索引

（1）定义

• 聚集索引（Clustered Index）是关系型数据库中的一种索引类型，它决定了表中数据的物理存储顺序。
• 在一个表中，只能有一个聚集索引。
• 聚集索引对表进行了重新组织，使得数据按照聚集索引的键值顺序存储在磁盘上。
• 由于聚集索引决定了数据的物理存储顺序，因此通过聚集索引可以快速地找到特定范围内的数据。
• 在MySQL中，聚簇索引是基于B+树的数据结构实现的，它会把数据存储在索引的叶子节点上，叶子节点之间按顺序链接，使得按主键进行搜索时速度最快。
• 如果没有主键，按主键搜索的速度也是最快的。

（2）聚集索引的特点：

• 数据的逻辑顺序和物理顺序是一致的，通过聚集索引可以直接访问特定行，因此聚集索引的查询速度很快。
• 聚集索引的键值必须是唯一的，不允许重复值存在。
• 当表中的数据发生插入、删除或更新操作时，聚集索引需要进行相应的调整以保持数据的有序性，这可能会对性能产生一定影响。
• 如果表中没有定义聚集索引，那么表的数据存储顺序将按照物理地址来存储。
• 表不建立主键时，仍然会有一个隐藏字段作为主键索引，对于按照主键进行查询的性能非常高。

（3）语法：

• 为表添加主键索引的语法如下：

ALTER TABLE table_name ADD PRIMARY KEY (column);

• 其中，table_name是表的名称，column是要作为主键的列名。

【4】小结：

• 聚集索引适用于经常需要按照某个特定的列或列组进行查询的情况。
• 聚集索引的选择需要根据具体的业务需求和数据访问模式进行权衡。
• 在一些特定情况下，聚集索引可能并不适合或者不符合最佳实践，此时可以考虑使用非聚集索引等其他索引类型。

【9】辅助索引

（1）说明

• 辅助索引（Non-clustered Index）是关系型数据库中的一种索引类型，与聚集索引相对应。
• 辅助索引的键值并不决定数据在磁盘上的物理存储顺序，而是创建一个额外的数据结构来帮助加快查询速度。
• 一个表可以有多个辅助索引。

（2）辅助索引的特点

• 辅助索引的建立并不直接影响数据的物理存储顺序，它是基于聚集索引（如果存在）或表本身的物理存储结构进行构建的。
• 辅助索引的键值可以重复，允许在同一个键值上有多行数据。
• 辅助索引存储着键值和指向实际数据行的指针，通过辅助索引可以快速定位到具体的数据行。
• 辅助索引的维护对数据的插入、删除、更新操作的性能有一定影响，因为每次操作都需要更新辅助索引。

（3）语法

• 为表添加辅助索引的语法如下：

ALTER TABLE table_name ADD INDEX index_name (column);

• 其中，table_name是表的名称，index_name是索引的名称，column是要作为索引的列名。

（5）小结

• 辅助索引适用于类似于搜索或排序等需要频繁进行的查询操作。

• 当查询涉及到的列不是聚集索引的键值时，辅助索引可以提供更高效的查询性能。

• 需要注意的是，在设计数据库时，应根据实际情况综合考虑聚集索引和辅助索引的选择。

• 聚集索引适用于经常以特定顺序扫描整个表或者频繁按照某个列进行范围查找的场景，而辅助索引则适用于单列或多列的等值或范围查找场景。

• 理想情况下，辅助索引应当能够满足大部分查询需求，从而避免全表扫描。

【10】唯一索引

（1）说明

• 唯一索引是指该索引的所有值都是唯一的，不允许出现重复值。

（2）语法

• 在MySQL中，可以通过以下语法创建唯一索引：

ALTER TABLE table_name ADD UNIQUE KEY index_name (column);

• 其中，table_name是表的名称，index_name是索引的名称，column是要作为唯一索引的列名。

（3）注意事项

• 与普通索引不同的是，如果尝试向包含唯一索引的列中插入重复的值，则会引发错误。唯一索引可以用于确保数据的一致性和完整性，并且可以帮助提高查询性能。

• 唯一索引常用于需要保证某列的值唯一性的情况，例如在用户表中，可以将用户名列设置为唯一索引，以确保每个用户名都是唯一的。这样可以避免重复的用户名被插入，同时在查询用户时也可以通过唯一索引快速定位到对应的用户记录。

• 需要注意的是，唯一索引并不限制该列的值为非空，如果需要同时限制唯一性和非空性，可以使用主键索引或在列上添加非空约束。

【11】组合索引（联合索引）

（1）说明

• 组合索引是一种由两个或更多列组成的索引。当查询涉及多个列时，组合索引可以大大提高查询性能。

（2）语法：

• 在MySQL中，可以通过以下语法创建组合索引：

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

• 需要注意的是，组合索引中的列顺序很重要。
• 在执行查询时，MySQL会首先使用最左的列进行匹配，然后逐步向右扩展。
• 因此，应该将最常用的查询条件放在组合索引的前面。

（3）缺点

• 虽然组合索引可以提高查询性能，但也存在一些缺点。例如，如果添加了不需要的列或删除了不需要的列，可能会导致组合索引变得无效。此外，如果创建了太多的组合索引，也可能会增加索引维护的成本。因此，在创建组合索引时需要谨慎考虑。

• 组合索引适用于需要同时查询多个列的情况。例如，在一个订单表中，经常需要按照订单日期和客户ID进行查询，可以创建一个组合索引来加快这类查询的速度。组合索引可以将多个列的值组合在一起进行索引，从而减少磁盘I/O操作，提高查询效率。

• 需要注意的是，组合索引并不是越多越好，过多的组合索引可能会增加索引的维护成本，并且在更新数据时可能会导致性能下降。因此，在设计数据库时，应根据实际情况选择合适的组合索引。

【12】全文索引

（1）说明

• 全文索引是一种特殊的索引，用于存储和检索文本数据。
• 全文索引可以包含单词、短语和其他类型的文本内容，并支持模糊匹配和近似匹配。

（2）语法

• 在MySQL中，可以通过以下语法创建全文索引：

CREATE FULLTEXT INDEX index_name ON table_name (column);

• 其中，index_name是索引的名称，table_name是表的名称，column是要创建全文索引的列名。

（3）注意事项

• 需要注意的是，只有MyISAM和InnoDB存储引擎支持全文索引。其他存储引擎如MEMORY、CSV等不支持全文索引。

• 创建全文索引可能会增加索引维护的成本，并且可能会降低其他类型的查询性能。因此，在创建全文索引时需要权衡其利弊。全文索引适用于需要对文本内容进行搜索和匹配的场景，例如在文章、博客、论坛等应用中搜索关键词。

• 全文索引可以提供更高效的文本搜索功能，支持模糊匹配、近似匹配和排序等操作。它可以帮助用户快速找到包含特定关键词的文本内容，提高查询的效率和准确性。

• 需要注意的是，全文索引的创建和使用需要一定的额外配置和注意事项，例如需要设置最小词长度、停用词列表等。在使用全文索引时，可以根据具体需求进行调整和优化，以获得更好的搜索性能和结果。

【13】前缀索引

（1）说明

• 前缀索引是一种特殊的索引，它只存储索引列的一部分，而不是完整的值。
• 前缀索引通常用于处理非常大的列，例如IP地址或邮政编码。
• 在这种情况下，全列索引可能会消耗大量的存储空间，并且可能会导致查询性能下降。
• 前缀索引可以大大减少索引大小，并且可以更快地执行范围查询。

（2）语法：

• 在MySQL中，可以通过以下语法创建前缀索引：

ALTER TABLE table_name ADD INDEX index_name (column(length));

• 其中，table_name是表的名称，index_name是索引的名称，column是要创建前缀索引的列名，length参数表示要保留的字符数。

（3）示例：

• 例如，假设我们有一个邮政编码列，并且我们只需要后两位数字进行索引，我们可以创建以下前缀索引：

ALTER TABLE table_name ADD INDEX idx_postcode(postcode(2));

• 在这个例子中，索引只会包含邮政编码的最后两位数字。当执行范围查询时，MySQL可以直接使用前缀索引来缩小搜索范围，从而提高查询性能。

• 需要注意的是，使用前缀索引可能会导致一些查询结果的不准确性，因为索引只存储了部分值。因此，在创建前缀索引时需要权衡索引大小和查询准确性之间的关系，并根据具体情况选择合适的前缀长度。

【14】覆盖索引

（1）说明

• 覆盖索引（Covering Index）是指在关系型数据库中创建一个辅助索引，该索引包含了查询所需要的所有列，从而避免了对主表进行额外的数据检索操作。
• 通过覆盖索引，数据库可以直接从索引中获取到查询所需的数据，而不需要再访问主表，提高了查询性能。

（2）覆盖索引的特点

• 覆盖索引包含了查询所需要的所有列，无需再通过辅助索引定位到实际的数据行。这样，数据库可以直接从索引中获取到查询所需的数据，而不需要再访问主表，提高了查询性能。
• 覆盖索引减少了磁盘I/O操作的次数。相比于使用辅助索引定位到数据行后再读取数据，覆盖索引可以直接从索引中读取所需数据，减少了磁盘读取的次数，从而加快了查询速度。
• 覆盖索引在一些特定的查询场景下非常有效，特别是当查询只需要返回索引包含的列时。例如，如果需要查询一个学生表中的学生姓名和年龄，而这两个列在一个名为"student_age"的辅助索引上都有覆盖，那么查询时就可以直接使用该覆盖索引，而无需再去读取主表中的其他列，从而提高查询效率。

（3）小结

• 根据具体的查询需求，我们可以通过创建适当的覆盖索引来提高查询性能。
• 需要考虑的因素包括查询的列、查询的条件、表的大小以及对数据插入和更新操作的影响。
• 创建过多的覆盖索引可能增加了存储空间的占用，并对数据的插入、删除和更新操作性能产生负面影响。
• 因此，在创建覆盖索引时需要权衡索引的选择，避免过度索引化的情况。

【15】非覆盖索引

（1）说明

• 非覆盖索引是指在数据库中的索引结构中，存储了对应的键值（例如：主键、唯一键、普通索引）以及相应的行的定位信息，但没有包含查询所需的其他列数据。
• 当执行一个查询时，使用非覆盖索引需要通过索引定位到对应的行，并进一步访问主表来获取所需的列数据。

（2）非覆盖索引的适用场景和优势：

• 提供高效的筛选能力：非覆盖索引可以根据所建索引的键值快速定位到满足查询条件的行，这可以减少需要扫描的数据量，提供高效的筛选能力。
• 减少磁盘I/O操作：虽然非覆盖索引需要额外的访问主表来获取数据，但相对于全表扫描或需要访问大量数据页的情况，非覆盖索引仍然可以减少磁盘的I/O操作次数，从而提升查询性能。
• 降低内存消耗：非覆盖索引通常比较小，占用的内存空间相对较少。这对于有限的内存资源来说，可以更好地利用内存空间。

（3）注意事项：

• 列选择性：索引的列选择性是指该列上不同值的数量与总行数之间的比率。当列具有较高的选择性时，非覆盖索引的效果通常会更好。因为高选择性的列能够更快地筛选出满足查询条件的行。
• 查询性能评估：在设计索引时，需要仔细评估查询的频率和性能需求。如果某个查询经常执行，而且对性能要求很高，那么建立合适的非覆盖索引可以提升查询效率。

（4）小结：

• 总结而言，非覆盖索引是一种常见的索引类型，在特定场景下可以提供高效的筛选能力和降低磁盘I/O操作的优势。
• 但在选择索引类型时，需要综合考虑查询需求、列选择性以及数据表大小等因素，以选择最合适的索引优化方案。

【二】事务隔离机制

【1】数据库读现象的本质

• 数据库读现象的本质在于高并发场景下多个同时执行的事务对数据的影响。

【2】数据库三大读现象

• 在数据库中，不同的事务隔离级别可能导致脏读、不可重复读和幻读等问题。

（1）脏读

• 脏读是指一个事务读取了其他事务尚未提交的数据。
• 这些数据可能会被回滚，因此可能最终不会存入数据库，
• 也就是说，读取到的数据可能是不存在的。

（2）不可重复读

• 不可重复读是指在一个事务内，多次读取同一批数据时，
• 由于其他事务对数据进行了更新并提交，导致读取结果不一致。

（3）幻读

• 本质上说是不可重复读的一种现象

• 幻读并不是指两次读取获取的结果集不同，而是指某一次的查询操作得到的结果无法支撑后续的业务操作。

• 例如，当判断某条记录不存在并准备插入该记录时，却发现该记录已经存在，导致无法插入，这就是幻读。

（4）解决办法

• 要解决脏读、不可重复读和幻读的问题，
  • 需要引入MVCC（多版本并发控制）机制
  • 事务隔离机制
  • 数据库锁机制

【3】数据库事务隔离机制

• 事务具有原子性、一致性、隔离性、持久性四大特性，简称ACID。

（1）事务的四大特性

• 原子性（Atomicity）：事务是一个不可再分割的单位，要么全部执行成功，要么全部回滚到初始状态，没有中间状态。
• 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。
• 隔离性（Isolation）：多个事务可能同时执行，事务之间应该相互隔离，互不影响。
• 持久性（Durability）：一旦事务提交，对数据库的更改应该是永久性的，即使在系统故障的情况下也不应该丢失。

（2）隔离性的四种级别

• 读未提交（Read uncommitted）：
  • 一个事务可以读取到另一个事务尚未提交的数据
  • 最低的隔离级别，存在脏读、不可重复读和幻读问题。
• 读已提交（Read committed）：
  • 一个事务只能读取到已经提交的数据
  • 避免了脏读问题，但可能会出现不可重复读和幻读问题。
• 可重复读取（Repeatable read）：
  • 事务开始读取数据后，其他事务无法修改这些数据
  • 避免了脏读和不可重复读问题，但可能会出现幻读。
• 串行化（Serializable）：、
  • 要求事务串行执行，事务之间完全隔离
  • 最高级别的隔离级别，避免了脏读、不可重复读和幻读问题，但在效率方面有所牺牲。

（3）MySQL的存储引擎默认的隔离级别

• 存储引擎InnoDB默认的隔离级别是可重复读。

（5）MySQL幻读的解决办法

• 引入了Next-key lock的行级锁来解决幻读问题。

【3】MVCC机制

（1）什么是MVCC机制

• MVCC，全称为Multi-Version Concurrency Control，即多版本并发控制协议。
• 是MySQL 的InnoDB存储引擎实现的一种并发控制协议。

（2）MVCC的优势

• MVCC的主要优势在于它实现了读不加锁，避免了读写冲突，从而提高了系统的并发性能。
• 同时，MVCC也解决了数据的脏读问题，保证了数据的一致性。

（3）MVCC的读操作

• 在MVCC的并发控制系统中，读操作可以分为两类：快照读和当前读。

• 快照读：

  • 快照读是指对数据库进行简单的select操作时，不会加锁，而是通过查询当前事务开始时的系统时间点，获取该时间点之前的数据副本。
  • 由于每个事务都有自己的时间点，所以每个事务看到的都是自己创建时的状态，从而避免了脏读的发生。

• 当前读：

  • 当前读是指对数据库进行插入、更新或删除操作时，需要加锁，以防止其他事务修改已经锁定的数据。
  • 只有当事务完成所有更新后，才会将其提交到数据库，并释放所有的锁。
  • 这种机制可以确保数据的一致性和完整性，但会降低系统的并发性能

【三】锁机制

【1】什么是锁机制

• 锁机制是数据库管理系统用来管理并发操作中的冲突，保证数据一致性和完整性的一种技术。
• 在数据库中，锁是用来解决并发控制问题的一种机制，它可以防止多个事务同时修改同一份数据，从而避免数据的不一致性。

【2】为什么要用锁机制

• 在并发环境中，多个事务可能会同时操作同一份数据，如果没有适当的控制，就可能会导致数据的不一致性。

• 例如，两个事务同时读取同一份数据，然后基于读取的数据进行修改，最后写回数据库，这就可能导致数据的不一致性。

• 锁机制可以确保在任何时刻，只有一个事务能够操作被锁定的数据。

• 这样，即使有多个并发的事务，也能保证数据的一致性和完整性。

• 虽然锁机制可能会降低系统的并发性能，但是它能够提高数据的安全性，防止数据的不一致性，这是在数据库系统中非常重要的。

【3】行级锁

（1）按粒度分数据库的锁

• 数据库的锁可以按照粒度分为行级锁、表级锁和页级锁。

• 行级锁：

  • 行级锁是数据库中锁定粒度最细的一种锁，只针对当前操作的行进行加锁。
  • 行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。
  • 行级锁分为共享锁和排他锁。

• 表级锁：

  • 表级锁是数据库中锁定粒度最大的一种锁，一次锁定整张表。
  • 表级锁的加锁开销小，但并发度最低。

• 页级锁：

  • 页级锁介于行级锁和表级锁之间，一次锁定一整页的数据。
  • 页级锁的加锁开销和并发度介于行级锁和表级锁之间。

（2）行级锁的特点

• 开销大，加锁慢：

  • 因为行级锁需要对每一行数据都进行加锁，所以其开销大，加锁慢。

• 会出现死锁：

  • 当多个事务试图锁定同一行数据时，可能会出现死锁。

• 锁定粒度最小，发生锁冲突的概率最低，并发度也最高：

  • 因为行级锁只锁定需要操作的数据，所以其发生锁冲突的概率最低，能提供最高的并发度。

（3）行级锁的使用

• 在进行数据的增删改查操作时，我们可以对数据加锁以保证数据的一致性和完整性。
• 写操作（增删改）默认会加排他锁，读操作可以选择是否加锁，如果加锁，可以选择加共享锁或排他锁。
• 读操作加互斥锁 for update
• 读操作加共享锁 lock in share mode

（4）行级锁锁的是索引

• 行级锁实际上是锁定了数据行的索引，如果查询语句命中了索引，那么数据库系统就会对该索引对应的数据行加锁。
• 如果查询语句没有命中索引，那么数据库系统会对整张表加锁。
• 具体来说
  1. 如果查询命中了主键索引，InnoDB会锁定这条主键索引对应的行。
  2. 如果查询命中了辅助索引，InnoDB会先锁定这条辅助索引，然后再锁定相关的主键索引。这是因为在InnoDB中，辅助索引的叶子节点存储的是主键值，所以在通过辅助索引查找数据时，需要先找到辅助索引，然后通过辅助索引找到主键值，最后通过主键值找到数据。
  3. 如果查询没有命中任何索引，InnoDB会锁定整张表。虽然本质上还是行锁，但是因为锁定的是所有行，所以效果等同于表锁。

（5）行级锁的三种算法

• 行级锁有三种算法：Record lock（记录锁）、Gap lock（间隙锁）和Next-key lock（下一个键锁）。
• 其中，Next-key lock是MySQL默认的锁机制，它是Record lock和Gap lock的结合，可以解决幻读问题。

【4】按级别分

• 数据库的锁按级别分为共享锁和排他锁，也被称为读锁和写锁。

（1）共享锁（S锁）

• 多个事务可以共享同一把锁，每个事务可以读取数据，但不能修改。
• 如果事务T对数据A加上共享锁，则其他事务只能对A再加共享锁或不加锁（在其他事务中不能再加排他锁），但在事务T自己中是可以加排他锁的。

（2）排他锁（X锁）

• 如果一个事务获取了数据的排他锁，其他事务无法再次获得该数据的任何锁。
• 排他锁支持文件读取、修改和写入。

【5】按使用方式分

• 数据库的锁按使用方式分为悲观锁和乐观锁。

（1）悲观锁（Pessimistic Locking）

• 对外界将要进行的数据修改操作持悲观态度，在整个数据处理过程中将数据处于锁定状态。
• 然而，由于互联网的高并发架构，即使加上悲观锁也无法保证数据不被外界修改，因此不推荐使用。

（2）乐观锁（Optimistic Locking）

• 相对悲观锁而言，乐观锁假设数据一般情况下不会造成冲突。
• 在数据提交更新时，才会对数据的冲突与否进行检测。如果发现冲突，则返回错误信息，让用户决定如何处理。
• 通常，乐观锁的实现是在表中加一个字段（如时间戳或版本号），在写入时查询版本号，如果版本号没有改变，则写入数据库并同时改变版本号。
• 乐观锁本质上并没有加锁，因此效率会大大提升，但可能导致一部分任务的写入失败。

【6】死锁问题

• 死锁是指两个或多个进程（或事务）互相等待对方释放资源而无法继续执行的情况。

（1）第一种情况的死锁

事务1（终端1）	事务2（终端2）
start transaction;	start transaction;
select * from user_id where id=6 for update;	delete from user_id where id=3;
update user_id set age=18 where id=3;	delete from user_id where id=6; – 阻塞

• 在这种情况下，每个事务执行两条SQL语句，分别持有了一把锁，然后又尝试获取另一把锁，导致死锁的发生。
• 大多数情况下，InnoDB存储引擎会检测到并抛出异常来处理死锁问题。然而，有一种死锁问题非常隐蔽。

（2）第二种情况的死锁

• 与第一种情况不同，这种死锁现象只有在两个事务同时运行的情况下才可能发生。
• 假设事务1根据一定的过滤条件筛选出两条辅助索引，根据索引的有序性，在锁定辅助索引后锁定主键索引时，先锁定主键1对应的记录，然后再锁定主键2。如果同时，事务2通过另一个辅助索引也访问到了这两条数据，但顺序却是先锁定主键2再锁定主键1，就会互相锁住，产生死锁现象。这种情况非常隐蔽，较难排查。