MySQL常见知识

By Jellow综合
目录

查询缓存

查询缓存（Query Cache）。查询缓存的主要目的是提高数据库查询的效率，尤其是在频繁查询相同数据的场景下。

MySQL 查询缓存的工作原理

  1. 缓存查询结果：当执行一个 SELECT 查询时，MySQL 首先检查查询缓存，如果发现已缓存的查询结果与当前查询相匹配，它就直接返回缓存的结果，而不是重新执行查询。
  2. 缓存的失效：如果涉及到查询的表发生了更新（如 INSERT、UPDATE、DELETE 操作），与该表相关的所有查询缓存都会被清除。
  3. 配置：可以通过配置来启用或禁用查询缓存，以及设置缓存大小等。

注意事项

  • 版本差异：在 MySQL 8.0 及更高版本中，查询缓存功能已经被移除。在这些版本中，应关注其它性能优化策略。
  • 适用性：查询缓存适用于那些不经常变更但经常被查询的数据。如果数据更新非常频繁，查询缓存可能会因为频繁的失效而适得其反。

被删除的原因

  1. 高并发环境下的性能问题：在高并发的数据库系统中，查询缓存可能成为瓶颈。每当有数据变更时，所有相关的缓存都需要被清除，这会导致大量的缓存失效和重建操作，增加了系统的开销。
  2. 缓存失效问题：在频繁更新的数据库中，缓存的失效率非常高。这意味着缓存的效果并不明显，反而可能因为频繁的缓存更新和失效导致额外的性能损耗。
  3. 不适应现代硬件：随着现代硬件的发展，尤其是 CPU 速度和内存容量的提升，直接从内存读取数据的速度已经非常快。这减少了查询缓存带来的相对优势。
  4. 优化器的进步：数据库查询优化器的进步意味着对于许多类型的查询，即使没有缓存，执行速度也已经足够快。这进一步降低了查询缓存的必要性。
  5. 简化数据库内核：移除查询缓存有助于简化 MySQL 的内核，使得数据库更加稳定和易于维护。这对于数据库的长期发展来说是有益的。
  6. 更有效的替代方案：例如，使用 InnoDB 引擎的数据库可以从 InnoDB 的缓冲池中获得更高效的数据缓存。另外，应用层面的缓存（如 Redis、Memcached）通常提供更灵活和高效的缓存策略。

InnoDB缓冲池

InnoDB 引擎的缓冲池（Buffer Pool）是 MySQL 中一个非常关键的特性，用于提高数据库操作的效率。它是 InnoDB 存储引擎的一部分，主要用于缓存数据和索引信息，以减少对磁盘的访问次数。下面是关于 InnoDB 缓冲池的一些主要特点和工作原理：

缓冲池的作用

  1. 数据页缓存：当从磁盘读取数据页时，InnoDB 首先会将这些数据页缓存到缓冲池中。如果后续有查询需要这些数据，可以直接从缓冲池中读取，而不需要再次访问磁盘。
  2. 索引页缓存：除了数据页，InnoDB 还会将索引页缓存到缓冲池中，这样可以加快索引查找和扫描的速度。
  3. 脏页的处理：当数据页在缓冲池中被修改（称为“脏页”），InnoDB 会在适当的时候将这些更改写回磁盘。这个过程是异步的，可以提高写操作的效率。

缓冲池的管理

  • LRU 算法：InnoDB 使用一种改进的最近最少使用（LRU）算法来管理缓冲池中页的替换。这确保了频繁访问的页保留在缓冲池中，而不常用的页被逐出。
  • 大小配置：缓冲池的大小是可以配置的。在一个系统中，为了最大化性能，通常会将缓冲池配置为尽可能大（考虑到系统的内存容量）。
  • 多实例：在有需要的情况下，可以配置多个缓冲池实例，以提高并发访问时的性能。

与查询缓存的区别

与查询缓存不同，缓冲池不是直接缓存整个查询结果，而是缓存数据和索引的页。这意味着即使数据发生变化，缓冲池仍然可以提供高效的数据访问，而不像查询缓存那样需要频繁地清空和重新填充。

存储引擎

MySQL支持非常多种存储引擎，我这先列举一些：

存储引擎 描述
ARCHIVE 用于数据存档（行被插入后不能再修改）
BLACKHOLE 丢弃写操作，读操作会返回空内容
CSV 在存储数据时，以逗号分隔各个数据项
FEDERATED 用来访问远程表
InnoDB 具备外键支持功能的事务存储引擎
MEMORY 置于内存的表
MERGE 用来管理多个MyISAM表构成的表集合
MyISAM 主要的非事务处理存储引擎
NDB MySQL集群专用存储引擎

最常用的就是InnoDB和MyISAM，有时会提一下Memory。其中InnoDB是MySQL默认的存储引擎。

MySQL 支持多种存储引擎，其中 InnoDB、MyISAM 和 Memory 是最常见的三种。每种存储引擎都有其独特的特点和适用场景。

1. InnoDB

特点

  • 支持事务（ACID兼容）：提供了事务的完整支持，包括提交、回滚和崩溃恢复能力。
  • 行级锁定：减少了查询之间的锁争用，提高并发性能， 使用的是悲观锁。
  • 外键支持：允许在表之间定义外键约束。
  • 自动崩溃恢复：拥有日志记录机制，能够在系统崩溃后恢复数据。
  • 支持MVCC（多版本并发控制）：提高多用户并发操作的性能。
  • 缓冲池：有效地缓存数据和索引，提高数据访问速度。

应用场景

  • 适用于需要高可靠性和事务支持的应用。
  • 适合读写混合的高并发场景。
  • 需要外键约束保证数据完整性的应用。

注意事项

  • 相比于 MyISAM，占用更多的磁盘空间和内存。
  • 需要定期优化和备份。

2. MyISAM

特点

  • 表级锁：锁定整个表，适用于读多写少的场景。
  • 不支持事务：不支持 ACID 事务处理。
  • 高速读取：优化了读取操作，提供了快速的读取能力。
  • 全文索引支持：支持全文索引，适合文本搜索。

应用场景

  • 适用于读操作远多于写操作的应用。

  • 适合静态或者几乎不变的数据，如日志记录。

    • 快速的插入性能：MyISAM 在处理顺序插入操作时非常高效，这是日志记录的常见场景。
    • 读写分离：日志记录通常是写入密集型的，但读取操作不会那么频繁。在许多应用中，日志数据经常是写入后很少读取，或者仅在需要时才读取，这减少了表级锁的影响。
    • 简单的数据结构：日志数据通常具有简单的数据结构，这使得 MyISAM 对其的处理更为高效。
    • 磁盘空间和内存使用效率：MyISAM 相比于 InnoDB 更节省磁盘空间和内存资源，对于存储大量日志数据来说，这是一个优势。
    • 全文索引支持：MyISAM 提供全文索引支持，这在需要对日志数据进行全文搜索时非常有用。

    然而，也有一些注意事项：

    • 数据安全性：MyISAM 不支持事务，也没有崩溃恢复能力，这意味着在系统崩溃的情况下可能会丢失数据。
    • 并发写入：在高并发写入的场景下，MyISAM 的表级锁可能会成为瓶颈。
  • 全文搜索的场景。

注意事项

  • 数据安全性较低，不支持事务和崩溃后的自动恢复。
  • 高并发写入时性能下降。

3. Memory

特点

  • 数据存储在内存中：所有数据和索引都存储在内存中。
  • 表级锁：使用表级锁定。
  • 快速访问速度：内存存储提供了非常快的访问速度。
  • 数据的非持久性：服务器崩溃或重启会导致数据丢失。

应用场景

  • 适合临时数据存储，如会话信息、临时计算结果等。
  • 用于高速缓存，减少对磁盘的访问。

注意事项

  • 数据非持久性，不适合存储重要数据。
  • 内存限制，不适合大量数据存储。
  • 高并发写入可能导致性能瓶颈。

结论

  • InnoDB 是最通用的选择，适用于大多数需要高可靠性和事务支持的场景。
  • MyISAM 适用于读密集型应用，但在数据安全性和并发写入方面存在限制。
  • Memory 引擎适用于临时数据存储和高速缓存，但要注意数据的非持久性。

根据应用的具体需求选择合适的存储引擎非常关键。通常在数据安全性、性能和功能需求之间需要做出权衡。

乐观并发控制

主要步骤

  1. 读取阶段：事务开始时，它会读取并处理数据，但不会立即锁定数据。事务在这个阶段执行其所有的读取和计算工作。
  2. 验证阶段：在事务准备提交更改时，会进行冲突检测。这个阶段会检查在读取阶段之后是否有其他事务修改了相同的数据。
  3. 提交/回滚阶段：如果验证阶段检测到冲突（即其他事务已经修改了数据），当前事务会被回滚。如果没有冲突，事务则成功提交其更改。

特点

  • 高并发：由于在大部分时间不需要锁定资源，乐观并发控制允许更高水平的并发。
  • 冲突处理：在提交阶段处理冲突，而不是在每次数据访问时处理，这减少了锁的需求和管理开销。
  • 适用场景：最适合读多写少的应用场景，因为写操作的冲突可能导致事务回滚，从而影响性能。
  • 版本控制：乐观并发控制通常与数据版本控制结合使用，以追踪数据的变化。
  • 回滚可能性：在高冲突的环境下，乐观并发控制可能导致较高的事务回滚率，因为每当有冲突发生时，事务就需要回滚并重试。

乐观并发控制是一种有效的并发处理方法，特别适合那些读操作频繁而写操作相对较少的应用场景。在这些场景中，它可以减少锁的开销，提高系统的整体性能和吞吐量。然而，它也要求系统能够有效地处理事务的回滚和重试。

四个隔离级别

1. 原子性（Atomicity）

  • 定义：原子性意味着事务中的所有操作要么全部完成，要么全部不完成。事务是不可分割的最小操作单位。
  • 隔离级别关系：隔离级别不直接影响原子性。原子性主要通过数据库管理系统的事务日志等机制实现。

2. 一致性（Consistency）

  • 定义：一致性确保事务完成后数据库从一个正确的状态转移到另一个正确的状态。
  • 隔离级别关系：隔离级别影响事务如何查看和修改数据，从而间接影响数据库的整体一致性。例如，较低的隔离级别可能允许一些不一致的现象（如脏读或不可重复读），而较高的隔离级别（如串行化）则提供更强的数据一致性保障。

3. 隔离性（Isolation）

  • 定义：隔离性是指事务的操作和效果必须独立于其他事务。
  • 隔离级别关系：这是四个隔离级别最直接相关的ACID特性。每个隔离级别定义了事务之间可接受的交互程度，从而控制并发事务可能引发的问题（如脏读、不可重复读、幻读）。

4. 持久性（Durability）

  • 定义：一旦事务提交，其所做的更改就会永久保存到数据库中，即使发生系统故障也不会丢失。
  • 隔离级别关系：隔离级别不直接影响持久性。持久性通常通过数据库的日志和恢复机制实现，确保已提交事务的更改即使在系统崩溃后也能恢复。

MYSQL的字符集编码

MySQL中的 utf8（实际上是 utf8mb3）

  • 定义：在 MySQL 中，utf8 实际上是 utf8mb3 的别名。它是一个“阉割版”的 UTF-8 实现，只能使用 1 至 3 个字节来表示每个字符。
  • 限制：由于只使用最多 3 个字节，utf8（或 utf8mb3）无法表示所有 UTF-8 字符。特别是，它无法表示 4 字节的字符，如某些特殊符号、表情符号（emoji）或某些罕见的文字。
  • 兼容性：这种字符集的设计最初是为了与早期的 MySQL 版本兼容，那时 UTF-8 的实现还不够完整。

MySQL中的 utf8mb4

  • 定义：utf8mb4 是 MySQL 中对完整 UTF-8 编码的支持。它能够使用 1 至 4 个字节来表示每个字符，覆盖了 Unicode 标准中的所有字符。
  • 优点：utf8mb4 能够存储任何 Unicode 字符，包括所有语言的字符和符号、表情符号等。
  • 推荐使用：对于需要支持多语言或特殊字符（如表情符号）的应用，推荐使用 utf8mb4。从 MySQL 5.5.3 版本开始，utf8mb4 可用并被推荐为默认字符集。

CHAR与VARCHAR

CHAR

  • 固定长度：CHAR 是一个固定长度的字符串。在创建表时，你需要定义一个长度，例如 CHAR(10)。不管实际存储的字符串长度是多少，CHAR 类型的字段总是使用固定的长度。
  • 空格填充：如果存储的字符串长度小于定义的长度，MySQL 会用空格来填充这个字段，以达到定义的长度。当从 CHAR 类型字段读取数据时，尾随的空格会被移除。
  • 性能：由于固定长度，CHAR 类型的读取速度通常比 VARCHAR 快，特别适合存储长度几乎相同的数据，例如某些代码、状态值等。
  • 空间使用：可能会浪费存储空间，尤其是当字段中存储的字符串远小于定义长度时。

VARCHAR

  • 可变长度：VARCHAR 是一个可变长度的字符串。定义时同样需要指定最大长度，例如 VARCHAR(255)。但它只会使用必要的空间来存储实际的字符串，并在每个字符串值前使用额外的字节来记录字符串的长度。
  • 灵活的空间使用：对于长度不一的字符串，VARCHAR 更加高效，因为它只占用必要的空间。
  • 长度限制：VARCHAR 的最大长度取决于字符集和MySQL的配置，但最大不超过 65,535 字节（包括记录长度的字节）。
  • 尾随空格保留：与 CHAR 不同，VARCHAR 在存储时保留字符串尾部的空格。
  • 如果超过了varchar定义的长度，需要修改表结构才可以存储

使用建议

  • 长度稳定的数据：如果字段值的长度几乎总是固定的，或者长度变化不大，使用 CHAR。
  • 长度可变的数据：对于长度有较大变化的数据，例如文本、描述性字段等，使用 VARCHAR。

注意事项

  • 字符集影响：实际存储所需的空间还受字符集的影响，因为不同的字符集可能需要不同数量的字节来存储一个字符。
  • 性能考量：尽管 CHAR 在某些情况下读取速度更快，但这种性能优势在现代的数据库系统中可能不是非常显著，特别是考虑到存储空间的有效利用。

字符集设置

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

知道这三个字符集设置就好，在实际使用中把这三个设置成统一字符集，减少转换的系统损耗，默认他们三个都是

utf8mb4。

在 MySQL 中，对中文进行升序（ASC）排序的行为取决于该列使用的字符集和排序规则（collation）。MySQL 支持多种字符集和排序规则，其中一些专门针对中文设计。这些排序规则影响了如何比较和排序字符串，包括中文字符。

字符集和排序规则

  1. 字符集（Character Set）：定义了可以在列中存储的字符类型。对于中文，常用的字符集是 utf8 和 utf8mb4。
  2. 排序规则（Collation）：决定了如何比较字符。对于中文，MySQL 提供了多种针对不同语言习惯的排序规则，例如 utf8_general_ci、utf8mb4_chinese_ci 等。

中文排序

当你对含有中文的列进行升序排序时，具体的排序行为会基于该列的排序规则。例如：

  • 使用 utf8_general_ci 或 utf8mb4_general_ci：这些是通用的排序规则，对于多语言的支持较好，但可能不完全符合特定语言的排序习惯。
  • 使用 utf8mb4_chinese_ci 或类似针对中文的排序规则：这些排序规则会更加符合中文的排序习惯，可能基于拼音、笔画等进行排序。

示例

假设你有一个中文字符串的列，你可以使用 SQL 查询进行排序，如下：

SELECT * FROM your_table ORDER BY your_chinese_column ASC;

在这个查询中，your_chinese_column 是包含中文字符串的列名，ASC 指定了升序排序。排序的具体结果将取决于该列的字符集和排序规则设置。

注意事项

  • 字符集兼容性：确保使用的字符集支持中文（如 utf8mb4），因为一些旧的字符集（如 utf8）可能无法正确存储所有中文字符。
  • 性能考虑：使用特定的排序规则（如针对中文的）可能会对性能有一定影响，特别是在处理大量数据时。
  • 数据库版本：不同版本的 MySQL 可能支持不同的字符集和排序规则，所以要根据你使用的 MySQL 版本来确定可用的选项。

InnoDB 页结构

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。

将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16KB

数据存储方式

  1. 记录的组织：在InnoDB的数据页中，实际的数据记录（User Records）是根据主键顺序存储的。如果表没有显式定义主键，InnoDB会自动生成一个隐藏的主键来维护记录顺序。
  2. 链表结构：每个记录在内部都有一个“next_record”指针，指向主键值下一个更大的记录，形成一个单向链表。这种组织方式便于快速按主键顺序访问记录。
  3. Infimum 和 Supremum 记录：数据页中包含两个特殊的虚拟记录，Infimum（最小记录）和Supremum（最大记录），它们分别代表页中最小和最大的边界值。这两个记录帮助维护页内部记录的有序性。

页目录槽（Page Directory Slots）

  1. 作用：页目录槽的主要作用是提高基于主键的记录查找效率。它是数据页中的一个快速索引，用于快速定位记录。
  2. 槽的组成：每个槽指向页内部的一个“关键记录”（通常是每个组的最大记录）。这些关键记录将页内的记录分成多个小组，每个组包含一定数量的记录。
  3. 查找流程：
    • 首先使用二分查找方法在页目录槽中快速定位到目标记录所在的组。
    • 然后在该组内部使用链表结构顺序遍历，直到找到目标记录。
  4. 效率提升：这种结构大大减少了需要遍历的记录数量，尤其是在大型数据页中，能有效提高查询性能。

数据查询过程

数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

InnoDB表对主键的生成策略

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

索引

  1. 层级结构：B+树是一个多层次的树结构，其非叶子节点不存储数据，只存储键值和指向子节点的指针。叶子节点包含了所有键值的信息及其对应的记录指针。
  2. 叶子节点的特殊结构：所有叶子节点之间以双向链表的形式连接，这种结构便于进行顺序访问和范围查询。
  3. 高效的范围查询：由于叶子节点是按键值顺序排列的，并且相互连接，所以B+树非常适合执行范围查询。
  4. 查询优化器的作用：查询优化器可以利用B+树的特性来优化查询计划，例如，它可以决定使用哪个索引来进行查询以及查询的顺序。
  5. 磁盘I/O效率：B+树的结构减少了磁盘I/O的次数，因为索引的查找可以迅速定位到叶子节点，而不需要在树的每一层都进行磁盘访问。

范围查询的例子

假设有一个名为 Employees 的表，其中有一个列 salary 被加上了索引。现在要查询工资在 5000 到 8000 之间的员工，SQL 查询如下：

sqlCopy code
SELECT * FROM Employees WHERE salary BETWEEN 5000 AND 8000;

在这个查询中，MySQL 会利用 salary 列上的B+树索引：

  1. 首先在树的上层迅速定位到工资值为 5000 的节点。
  2. 由于叶子节点是顺序链接的，MySQL 将顺序遍历叶子节点，直到遇到工资值超过 8000 的记录。
  3. 在这个过程中，所有在这个范围内的员工记录都会被检索出来。

这种范围查询的效率远高于线性搜索，特别是在大数据集的情况下，B+树索引可以显著减少必要的比较次数和磁盘I/O操作。

聚簇索引

  1. 定义：聚簇索引并不是一个单独的结构，而是数据表行记录的物理存储顺序。在 InnoDB 中，聚簇索引是基于表的主键构建的。
  2. 主键作为索引：表的主键成为聚簇索引的一部分。如果没有定义主键，InnoDB 会选择唯一的非空列作为聚簇索引，如果这样的列也不存在，InnoDB 将生成一个隐藏的行ID作为聚簇索引。
  3. 数据与索引的结合：在聚簇索引中，索引结构和行数据是紧密结合的。这意味着索引叶子节点直接包含了行数据。
  4. 优点：提供了快速的数据访问能力，因为索引搜索可以直接定位到包含数据的页。
  5. 限制：由于数据按照聚簇索引的顺序存储，因此主键的更新可能导致数据重新排序，影响性能。同时，聚簇索引只能有一个，因为数据只能按一种顺序物理存储。

二级索引（Secondary Index）

  1. 定义：二级索引是除聚簇索引之外的索引。它们可以基于表中的非主键列构建。
  2. 独立于数据存储：二级索引的叶子节点包含索引的键值和指向聚簇索引记录的指针（通常是主键值），而不是实际的行数据。
  3. 间接访问：通过二级索引检索数据时，首先在二级索引中查找，然后通过索引中的指针去聚簇索引中检索实际的数据。这被称为“回表”。
  4. 优点：允许在不同的列上建立索引，提高这些列上查询和排序操作的效率。
  5. 缺点：由于需要“回表”操作，二级索引的访问通常比聚簇索引慢。此外，维护多个索引会增加数据更新（INSERT、UPDATE、DELETE）操作的成本。

在 MySQL 的 InnoDB 存储引擎中，索引主要分为两种类型：聚簇索引（Clustered Index）和二级索引（Secondary Index，也称为非聚簇索引）。这两种索引在数据的存储和访问方式上有显著的区别。

索引更新代价

聚簇索引（一级索引）

在聚簇索引中，数据实际上存储在索引的叶子节点上。这意味着索引和数据是紧密绑定的。以下是数据变更对聚簇索引的影响：

  • 更新索引键值：如果更新的是索引列本身（通常是主键），这可能是一个成本很高的操作。因为它可能需要移动数据行到新的位置，以保持数据的物理顺序与索引顺序一致。这种情况在聚簇索引中尤其昂贵。
  • 更新非索引列：如果更新的是非索引列的值，那么只需要修改数据页中的数据，而不需要改变索引结构。
  • 插入和删除操作：插入和删除可能导致页分裂或合并，因为数据需要在物理上保持有序。插入新数据时，如果新数据的索引键介于两个现有键之间，可能需要重新调整页的内容。

非聚簇索引（二级索引）

非聚簇索引存储的是索引值和指向数据行的指针（在InnoDB中是指向聚簇索引键的指针）。数据变更对非聚簇索引的影响如下：

  • 更新索引列：如果更新的是非聚簇索引中的某个列的值，这将涉及更新索引中的条目，因为索引值已经改变。这通常比聚簇索引的更新成本低，因为不需要移动实际的数据行。
  • 更新非索引列：如果更新的是数据行中未被索引的列，那么非聚簇索引不需要任何变更。
  • 插入和删除操作：插入和删除操作会影响到索引的结构，可能需要在索引中添加或移除条目。由于索引项较小，这些操作通常比聚簇索引中的同等操作更快。

综合比较

  • 写入性能：通常，聚簇索引的写入性能低于非聚簇索引，因为它可能涉及更多的数据移动和页的重新组织。
  • 维护成本：维护多个索引意味着更高的维护成本。每当数据变更时，所有相关的索引都需要更新。
  • 性能平衡：合理的索引策略应当平衡查询性能和写入/更新性能。过多或不必要的索引可能会降低数据库的整体性能。

B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。

B树和B+树的区别

B树（B-Tree）和B+树都是自平衡的树数据结构，它们被广泛用于数据库和文件系统中。尽管它们有很多共同点，但也存在一些关键的区别：

  1. 节点结构:
    • B树: 每个节点既存储键（key）也存储数据。非叶子节点包含指向子节点的指针。
    • B+树: 所有的数据都仅存储在叶子节点中，非叶子节点只存储键。叶子节点包含了所有键的值以及指向记录的指针。
  2. 键和数据的存储:
    • B树: 键和数据在同一个节点中存储。
    • B+树: 数据仅存储在叶子节点中；非叶节点仅用于索引。
  3. 叶子节点的链接:
    • B树: 叶子节点是独立的，没有相互连接。
    • B+树: 所有叶子节点通过指针连接在一起，形成一个有序链表。
  4. 空间利用率:
    • B树: 比B+树更多地浪费空间，因为每个节点都存储数据。
    • B+树: 空间利用率更高，因为它只在叶子节点存储数据。
  5. 搜索效率:
    • B树: 可能需要在非叶子节点中搜索数据。
    • B+树: 所有数据都在叶子节点，因此搜索可能更为高效，尤其是范围搜索。
  6. 树的深度:
    • B树: 可能更深，因为它在每个节点中存储数据。
    • B+树: 通常更浅，因为非叶节点仅存储键。

总结来说，B+树通过仅在叶子节点存储数据并将它们链接起来，提高了范围搜索的效率并且更有效地利用空间。B树的结构则更为简单，但可能在空间利用和搜索效率方面略逊一筹。在实际应用中，B+树因其高效的范围搜索能力和较低的磁盘I/O需求，被广泛用于数据库索引结构。

如何减少二级索引回表？

减少二级索引（Secondary Index）的回表操作是提高数据库查询性能的关键。回表指的是在使用二级索引找到数据行的主键后，需要再次查询聚簇索引（Clustered Index）来获取完整的数据行。这个过程在某些情况下可能相当耗时，特别是在涉及大量数据的复杂查询中。以下是一些设计技巧，可以帮助减少回表操作：

1. 使用覆盖索引

  • 定义：覆盖索引是指一个索引包含了查询中需要的所有字段。
  • 应用：如果一个查询可以完全通过二级索引来获取所需的数据，那么就不需要进行回表操作。
  • 实践：在创建二级索引时，考虑包括所有常用查询中需要的字段。

2. 索引选择性优化

  • 定义：索引的选择性是指索引中不重复项的比例。
  • 应用：高选择性的索引可以减少匹配给定索引值的数据行数量，从而减少需要回表的次数。
  • 实践：为具有高唯一值的列创建索引，如用户ID、电子邮件地址等。

3. 索引列顺序

  • 考虑：索引列的顺序对于查询性能有重要影响。
  • 实践：在创建复合索引时，将最常用于过滤条件的列放在索引的前面。

4. 减少索引列的宽度

  • 影响：索引的宽度直接影响其大小和效率。
  • 实践：使用尽可能短的数据类型，避免在索引中包含长字符串等大型数据。

5. 避免不必要的索引

  • 问题：过多的索引会增加写操作的负担，可能导致查询优化器选择不理想的索引。
  • 实践：定期审查和清理不常用或重复的索引。

6. 查询优化

  • 技巧：优化查询逻辑，减少需要的数据量。
  • 实践：使用合适的WHERE子句过滤条件，减少结果集的大小。

7. 分区策略

  • 定义：数据分区是将表中的数据分散存储在不同的部分。
  • 实践：对于非常大的表，考虑使用分区来改善性能。

8. 使用缓存

  • 策略：适当使用查询缓存或应用层缓存，可以减少数据库的访问次数。
  • 实践：缓存常见查询的结果，减少数据库的负担。

通过应用这些设计技巧，可以有效地减少回表操作的次数，从而提高数据库的查询效率和整体性能。这些技巧在处理大量数据和复杂查询的大型数据库系统中尤为重要。

如何选择索引？

详细内容看这篇：https://relph1119.github.io/mysql-learning-notes/#/mysql/07-%E5%A5%BD%E4%B8%9C%E8%A5%BF%E4%B9%9F%E5%BE%97%E5%85%88%E5%AD%A6%E4%BC%9A%E6%80%8E%E4%B9%88%E7%94%A8-B+%E6%A0%91%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BD%BF%E7%94%A8

B+树索引适用于下面这些情况：

  • 全值匹配
  • 匹配左边的列
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列
  • 用于排序
  • 用于分组

在使用索引时需要注意下面这些事项：

  • 只为用于搜索、排序或分组的列创建索引
  • 为列的基数大的列创建索引
  • 索引列的类型尽量小
  • 可以只对字符串值的前缀建立索引
  • 只有索引列在比较表达式中单独出现才可以适用索引
  • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
  • 定位并删除表中的重复和冗余索引
  • 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

覆盖索引

覆盖索引是指一个索引包含了查询所需的所有数据。换句话说，如果一个查询能够仅通过访问索引来获取所需的所有列信息，那么这个索引就被称为覆盖索引。

所以常见的被查询的数据可以放到索引里，这样就不用回表了。

无法利用索引的情况

  1. ASC、DESC 混用
    • 情况：在使用联合索引进行排序时，若各排序列的顺序不一致（如一部分列使用ASC，另一部分使用DESC），则无法有效利用索引。
    • 原因：这是因为B+树索引的结构决定了它只能以一致的顺序（全部升序或全部降序）高效地遍历数据。
  2. WHERE子句中出现非排序使用到的索引列
    • 情况：如果WHERE子句中使用了与ORDER BY子句不同的列，且这些列不是索引的一部分，那么无法利用索引进行排序。
    • 原因：SQL首先需要过滤出符合WHERE条件的记录，这一步无法通过索引完成，因此排序也无法利用索引。
  3. 排序列包含非同一个索引的列
    • 情况：当用于排序的列不属于同一个联合索引时，索引无法用于排序。
    • 原因：一个索引只能按其包含的列顺序进行排序，无法跨索引进行操作。
  4. 排序列使用了复杂的表达式
    • 情况：如果排序列使用了函数或计算表达式（如UPPER(name)），则无法利用索引进行排序。
    • 原因：索引是基于列的实际值建立的，不适用于修改后的值。
  5. 用于分组
    • 情况：当使用GROUP BY进行分组统计时，如果分组的列顺序与索引列不一致，或者分组的列不是索引的一部分，则无法利用索引优化。
    • 原因：分组操作的优化类似于排序，依赖于索引列的顺序。

查询过程

对于单个表的查询来说，MySQL执行方式大致分为下面两种：

  • 使用全表扫描进行查询

    这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是什么查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。

  • 使用索引进行查询

    因为直接使用全表扫描的方式执行查询要遍历好多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式五花八门，又可以细分为许多种类：

    • 针对主键或唯一二级索引的等值查询
    • 针对普通二级索引的等值查询
    • 针对索引列的范围查询
    • 直接扫描整个索引

MySQl执行查询语句的过程叫访问方法，有很多种

const

通过聚簇索引或者二级索引定位一条记录的方法叫做const

SELECT * FROM single_table WHERE key2 = 3841;

ref

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。

SELECT * FROM single_table WHERE key1 = 'abc';

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下面这个查询：

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

MySQL 首先使用索引找到所有与给定值相等的行，然后再次使用相同的索引找到所有为 NULL 的行。

range

利用索引进行范围匹配的访问方法称之为：range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';Copy to clipboardErrorCopied

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下面这两个条件：

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
  • 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设计MySQL的大佬就把这种采用遍历二级索引记录的执行方式称之为：index。

这个属于覆盖索引

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大佬把这种使用全表扫描执行查询的方式称之为：all。

事务与隔离级别

事务是一个执行单元，在这个执行单元中的所有操作要么全部完成，要么全部不完成。

事务的ACID特性：

  1. 原子性（Atomicity）：事务被视为最小的不可分割的工作单位，事务中的所有操作要么全部完成，要么全部不发生。
  2. 一致性（Consistency）：事务必须确保数据库从一个一致性状态转移到另一个一致性状态，即事务的执行结果必须使数据库从一个正确的状态变到另一个正确的状态。
  3. 隔离性（Isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不会相互干扰。
  4. 持久性（Durability）：一旦事务提交，则其所做的修改将永久保存在数据库中，即使系统崩溃也不会丢失。

如果严格遵守事务的ACID特性，事务的处理只能是串行化了，性能会很差。所以为了性能出现了不严格遵守ACID的事务，但他们要根据场景不同遵守不同的隔离级别

SQL标准中设立了4个隔离级别：

  • READ UNCOMMITTED：这是最低的隔离级别，允许读取未提交的数据（可能导致脏读）。
  • READ COMMITTED：只能读取已提交的数据。这个级别可以避免脏读，但不能防止不可重复读。
  • REPEATABLE READ：保证在同一个事务中多次读取同一数据的结果是一致的，避免了不可重复读，但可能出现幻读。
  • SERIALIZABLE：最高的隔离级别，完全遵守ACID原则，防止脏读、不可重复读和幻读，但性能损耗最大。

这四个隔离级别是要解决下面的问题：

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not Possible Possible Possible
REPEATABLE READ Not Possible Not Possible Possible
SERIALIZABLE Not Possible Not Possible Not Possible

在这四个之外还有脏写，脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

事务并发执行遇到的问题

脏写（Dirty Write）

如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写，示意图如下：

!https://markdown.cztcode.com/2bca8a45e9d08db8dd467424d73f8360.png

img

如上图，Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为'关羽'，然后Session A中的事务接着又把这条number列为1的记录的name列更新为张飞。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。这时Session A中的事务就很懵逼，我明明把数据更新了，最后也提交事务了，怎么到最后说自己什么也没干呢？

脏读（Dirty Read）

如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读，示意图如下：

!https://relph1119.github.io/mysql-learning-notes/images/24-02.png

img

如上图，Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为'关羽'，然后Session A中的事务再去查询这条number为1的记录，如果du到列name的值为'关羽'，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

不可重复读（Non-Repeatable Read）

如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读，示意图如下：

!https://relph1119.github.io/mysql-learning-notes/images/24-03.png

img

如上图，我们在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了number列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

幻读（Phantom）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读，示意图如下：

!https://relph1119.github.io/mysql-learning-notes/images/24-04.png

如上图，Session A中的事务先根据条件number > 0这个条件查询表hero，得到了name列值为'刘备'的记录；之后Session B中提交了一个隐式事务，该事务向表hero中插入了一条新记录；之后Session A中的事务再根据相同的条件number > 0查询表hero，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。

有的同学会有疑问，那如果Session B中是删除了一些符合number > 0的记录而不是插入新记录，那Session A中之后再根据number > 0的条件读取的记录变少了，这种现象算不算幻读呢？明确说一下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

那对于先前已经读到的记录，之后又读取不到这种情况，算什么呢？其实这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。

MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的（间隙锁）。

MVCC（多版本并发控制）

MVCC（多版本并发控制）是一种用于数据库管理系统的并发控制的方法。它允许多个事务同时对数据库进行读写操作，而不会互相干扰。MVCC 的核心思想是为数据库中的每个数据项保持不同版本的历史信息，这样不同的事务可以看到同一数据的不同快照。这种机制使得读操作不会阻塞写操作，写操作也不会阻塞读操作，从而大大提高了数据库系统的并发性能。

MVCC 的工作原理概述如下：

  1. 版本创建：当事务对数据进行更新操作时，系统会创建该数据的新版本，而不是覆盖旧版本。这意味着每个数据项可能有多个版本。
  2. 事务时间戳：每个事务被分配一个唯一的时间戳（或ID）。时间戳用于决定事务能看到哪些数据版本。
  3. 读操作：当事务读取数据时，它会看到在该事务开始之前最新的数据版本。这意味着读操作可以无视正在进行的写操作，实现非阻塞读。
  4. 写操作：当事务写入数据时，它会创建数据的新版本。这个新版本只能被时间戳晚于当前事务的其他事务看到。
  5. 可见性规则：数据库使用时间戳或事务ID来判断数据版本对特定事务是否可见。
  6. 垃圾收集：随着时间的推移，旧版本的数据可能不再被任何事务需要，因此系统会定期清理这些不再需要的数据版本。

MVCC 的优势包括减少锁的需求，提高系统的并发能力，以及减少读-写冲突。但它也有一些缺点，比如可能增加存储开销（因为要存储多个版本的数据），并且需要额外的机制来处理垃圾收集。主流的数据库管理系统如 PostgreSQL 和 MySQL 的 InnoDB 存储引擎都采用了 MVCC 机制。

MVCC通过ReadView控制事务能够看到的数据版本：

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用SERIALIZABLE隔离级别的事务来说，设计InnoDB的大佬规定使用加锁的方式来访问记录。

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，设计InnoDB的大佬提出了一个ReadView的概念。

READ COMMITTED —— 每次读取数据前都生成一个ReadView，事务中每次需要读取的操作前获取这个数据的最新版本

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView，事务开始之前获取版本，在事务中一直是这个版本

所以这两种隔离级别生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

锁

并发事务会遇到四种情况，读-读、写-写、读-写和写-读

一致性读（读-读）

事务利用MVCC进行的读取操作称之为一致性读，所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是一致性读，一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。

锁定读

  1. 共享锁（S锁）：
    • 当一个事务对数据加上共享锁后，表示该事务将读取数据。
    • 其他事务可以再获得该数据的共享锁，也就是说共享锁可以被多个事务同时持有。
    • 但是，如果任何事务持有数据的共享锁，那么其他事务不能对该数据加排他锁（直到所有共享锁都被释放）。
  2. 排他锁（X锁）：
    • 当一个事务对数据加上排他锁时，表示该事务将修改数据。
    • 排他锁确保没有其他事务可以对同一数据加任何锁（无论是共享锁还是排他锁），直到持有排他锁的事务释放锁。
    • 这意味着，一旦一个事务获得了某个数据项的排他锁，其他任何事务都不能再对该数据项加锁，直到排他锁被释放。

非读取的情况（变异）

1. DELETE 操作的锁定

在InnoDB中进行DELETE操作时，锁定机制的主要步骤如下：

  1. 定位记录：首先在B+树中找到需要删除的记录。
  2. 获取排他锁（X锁）：在找到待删除记录后，系统会对该记录加上排他锁。这意味着其他事务不能读取或修改这条记录，直到当前事务完成操作。
  3. 执行删除：在获得排他锁之后，系统进行删除操作，通常是标记该记录为删除状态（delete mask）。
为什么标记为删除状态而不是实际删除数据？
  1. 事务的回滚支持：通过仅标记记录为删除状态，数据库能够在必要时轻松地撤销删除操作（即事务回滚）。如果立即物理删除数据，则在事务需要回滚时恢复这些数据会更加复杂和耗时。
  2. 减少磁盘I/O操作：物理删除操作通常涉及更多的磁盘I/O操作，比如重组数据文件。标记记录为删除状态相比之下是一种轻量级操作，可以迅速完成，从而提高了整体性能。
  3. 并发控制和锁定：在多用户并发环境下，标记删除而不是物理删除可以更好地控制并发访问。例如，在一个长事务正在读取一个数据页时，另一个事务可能需要删除该页上的一条记录。通过标记删除，长事务仍然可以看到原始数据，直到它完成。
  4. MVCC（多版本并发控制）：InnoDB等支持MVCC的数据库系统通过保留数据的旧版本来支持高效的并发控制和非锁定读取。标记删除而非物理删除，是保持多个版本的一种简单方式。
  5. 空间回收和重用：被标记为删除的空间可以在后续的插入操作中被重用。这种方式允许数据库有效地管理存储空间，避免了频繁的空间分配和回收。
  6. 碎片整理：物理删除记录可能导致数据文件碎片化，降低存储效率。通过定期的碎片整理过程（例如，在数据库维护期间），这些标记为删除的记录可以被清理，从而优化数据存储。

2. INSERT 操作的锁定

INSERT操作在InnoDB中的锁定机制相对简单：

  • 过程：插入新记录时通常不显式加锁。
  • 隐式锁定：使用隐式锁机制保护新插入的记录，使其在当前事务提交前对其他事务不可见。
  • 目的：减少锁竞争，提高并发性能，同时保持数据的一致性和事务的隔离性。

3. UPDATE 操作的锁定

键值是指在数据库表中用于唯一标识每条记录的值。这些值通常对应于表的主键（Primary Key）或唯一键（Unique Key）。

UPDATE操作的锁定机制取决于是否修改了键值：

  1. 未修改键值，存储空间不变：
    • 过程：在B+树中定位到记录，获取X锁，然后在原位置进行修改。
    • 锁定：加锁是为了确保在修改过程中，没有其他事务可以读取或修改这条记录。
  2. 未修改键值，存储空间变化（比如修改了一个字段的长度）：
    • 过程：
      1. 定位记录：在B+树中找到要更新的记录。
      2. 获取X锁：对该记录加排他锁（X锁），阻止其他事务访问或修改。
      3. 彻底删除：将原记录从存储中完全删除，而不是仅标记为删除（delete mark）。
      4. 插入新记录：在适当位置插入修改后的新记录。
    • 锁定：定位并加X锁是为了在更新过程中保护原记录。插入新记录时，使用隐式锁保护新记录。
  3. 修改了键值：
    • 过程：
      1. 执行DELETE操作：对原记录执行标准的删除操作，通常包括定位记录、获取X锁以及标记记录为删除（delete mark）。
      2. 执行INSERT操作：插入一个新的记录，具有新的键值。
    • 锁定：DELETE操作按照删除锁定规则进行，INSERT操作涉及到隐式锁定。

多粒度锁

1. 行锁

行锁是数据库管理系统中最细粒度的锁类型，它仅锁定数据中的单个行。在InnoDB中，行锁是通过索引来实现的，这意味着如果操作不使用索引，则行锁可能会退化为表锁。

  • 优点：行锁可以最大限度地减少锁冲突，提高系统的并发能力。
  • 缺点：行锁的管理需要更多的资源，且在某些情况下可能导致死锁。

行锁和索引的关系

  1. 锁定索引项：在InnoDB中，当对一条记录进行操作（如读取、更新或删除）时，实际上是在锁定与该记录相关联的索引项。如果这个操作使用了索引，例如通过主键或唯一索引来定位记录，那么InnoDB会在这个索引项上放置一个行锁。
  2. 准确锁定：使用索引可以精确地锁定目标行。例如，如果一个查询条件是WHERE id = 10，并且id是一个被索引的列，那么只有id为10的那一行会被锁定。

操作不使用索引时的情况

当执行的操作没有利用到索引时，情况就有所不同：

  1. 全表扫描：如果一个操作没有使用索引，例如一个没有有效过滤条件的查询或一个更新操作的过滤条件没有涉及到索引列，数据库就会进行全表扫描。
  2. 锁定多行：在这种情况下，为了保证操作的一致性和隔离性，InnoDB可能不得不对表中的多行甚至整个表加锁。这种行为相当于行锁“退化”为表锁，因为数据库无法确定哪些行会受到操作的影响。
  3. 影响并发性能：当行锁退化为表锁时，会显著影响数据库的并发性能。其他事务可能无法访问该表的其他行，即使这些行实际上并不会受到当前操作的影响。

优化建议

为了避免行锁退化为表锁，建议：

  1. 使用索引：尽量确保查询和更新操作使用到索引，特别是在涉及大量数据的表上进行操作时。
  2. 索引设计：合理设计索引，确保常用的查询和过滤条件能够利用到这些索引。

2. 表锁

表锁是数据库中较为粗粒度的锁，它会锁定整个表。在一些操作中，比如全表扫描或者某些存储引擎（如MyISAM）中，会使用表锁。

  • 优点：表锁的管理相对简单，开销小。
  • 缺点：锁定整个表会显著降低数据库的并发能力，特别是在高并发的读写场景中。

意向锁

意向锁是InnoDB中用于支持多级锁定协议的一种锁，分为意向共享锁（IS锁）和意向排他锁（IX锁）：

  • 意向共享锁（IS锁）：事务想要获得一张表中某几行的共享锁时，它首先必须获取该表的IS锁。
  • 意向排他锁（IX锁）：事务如果想要获得一张表中某几行的排他锁，它必须首先获得该表的IX锁。

意向锁是表级别的锁，主要用于指示某个事务意图对表中的行加锁，而不是实际加锁。它们的作用是：

  • 表明意图：让其他事务知道有一个事务打算对表中的一行或多行进行加锁操作。
  • 优化锁定检查：当一个事务请求对整个表加锁时，可以通过检查意向锁来判断是否存在冲突，而不需要检查表中的每一行锁。

3. AUTO_INCREMENT

在MySQL的InnoDB存储引擎中，AUTO_INCREMENT列的值分配和锁定是一个重要的特性，尤其是在处理并发插入操作时。AUTO-INC锁和轻量级锁是两种不同的机制，用于管理AUTO_INCREMENT列值的生成和分配。让我详细解释这两种机制，并通过示例来说明它们的区别。

AUTO-INC锁

AUTO-INC锁是一种表级别的锁，用于控制对AUTO_INCREMENT列的访问，确保在插入过程中生成的递增值是连续的。

  • 工作方式：
    • 当一个事务开始插入操作时，它会在表级别获取AUTO-INC锁。
    • 随后，事务为每条待插入记录的AUTO_INCREMENT列分配递增值。
    • 插入操作完成后，AUTO-INC锁被释放。
  • 示例：假设有一个表t，其中有一个AUTO_INCREMENT列。如果事务A开始插入记录，它会首先获取AUTO-INC锁，然后为插入的每条记录分配一个连续的递增值。在事务A完成插入并释放锁之前，其他事务的插入操作会被阻塞。
  • 优点：保证了事务中分配的递增值是连续的。
  • 缺点：在高并发插入的场景下，可能成为性能瓶颈，因为其他事务必须等待AUTO-INC锁被释放才能进行插入。

轻量级锁

轻量级锁是一种更优化的机制，用于减少AUTO_INCREMENT值分配过程中的锁竞争。

  • 工作方式：
    • 在为插入语句生成AUTO_INCREMENT值时，仅临时获取轻量级锁。
    • 分配完必要的值后，立即释放这个轻量级锁。
  • 示例：在同一个表t中，如果事务B要插入2条记录，它会短暂地获取轻量级锁，分配两个递增值，然后立即释放锁。这样，即使在事务B还在处理其他操作时，其他事务也可以进行插入操作。
  • 优点：提高了并发插入的性能，减少了锁等待时间。
  • 缺点：在某些情况下（特别是当innodb_autoinc_lock_mode设置为2时），可能导致不同事务中的插入操作生成的递增值不是连续的。

innodb_autoinc_lock_mode的作用

innodb_autoinc_lock_mode是一个系统变量，用于控制InnoDB如何为AUTO_INCREMENT列分配值：

  • 值为0：总是使用AUTO-INC锁。
  • 值为1：混合模式。当可以预先确定插入记录的数量时，使用轻量级锁；否则，使用AUTO-INC锁。
  • 值为2：总是使用轻量级锁。

在主从复制的场景中，innodb_autoinc_lock_mode的设置为2可能会导致问题，因为不同事务中的插入操作可能生成交叉的递增值，从而影响复制的一致性。

结论

在选择AUTO-INC锁和轻量级锁之间，需要权衡事务中递增值连续性的需求和系统的并发插入性能。对于大多数应用来说，innodb_autoinc_lock_mode的默认设置（通常是1）提供了合理的平衡。对于高并发插入的场景，可能需要考虑使用轻量级锁以提高性能。

在MySQL的InnoDB存储引擎中，Gap Locks（间隙锁）和Next-Key Locks（临键锁）是用于事务处理和隔离级别控制的两种重要的锁机制。它们在处理并发事务时，尤其是在防止幻读（Phantom Reads）方面起着关键作用。

4. Gap Locks（间隙锁）与Next-Key Locks（临键锁）

Gap Locks（间隙锁）

Gap Locks锁定一个范围，但不包括该范围内的记录本身。

  • 作用：主要用于防止其他事务向这个范围内插入新的记录，从而解决幻读问题。
  • 应用场景：通常出现在可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）隔离级别下。

示例

假设有一个表：

| id |
|----|
| 1  |
| 2  |
| 4  |
| 5  |

如果一个事务执行了如下查询：

SELECT * FROM table WHERE id > 2 FOR UPDATE;

这将在id为2和4之间的间隙上加锁，但不包括2和4这两条记录。这意味着其他事务不能插入id值为3的记录，但可以更新id为2或4的记录。

Next-Key Locks（临键锁）

Next-Key Locks是行锁和间隙锁的结合。它们锁定一个范围，并包括范围的起始记录。

  • 作用：除了防止其他事务在范围内插入记录外，还防止修改范围开始的记录。
  • 应用场景：Next-Key Locks是InnoDB在可重复读隔离级别下默认的锁类型。

示例

同样的表格，如果一个事务执行：

SELECT * FROM table WHERE id >= 4 FOR UPDATE;

这将对id为4的记录以及4之后的所有间隙加锁。因此，其他事务不能插入id值大于等于4的记录，也不能修改id为4的记录。

结论

Gap Locks和Next-Key Locks是InnoDB实现高并发事务处理和隔离级别保证的关键机制。它们通过锁定记录和间隙，帮助维护数据的一致性，尤其是在可重复读和串行化隔离级别下。然而，过度使用或不恰当的使用可能会导致性能问题，比如锁竞争和死锁。因此，在设计数据库交互和事务处理时，理解和适当地使用这些锁机制非常重要。

5. 隐式锁

显式加锁

对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

对读取的记录加X锁：

SELECT ... FOR UPDATE;

隐式锁是MySQL中的一个重要概念，特别是在InnoDB存储引擎中。这种锁机制是由数据库系统自动管理的，不需要用户直接干预。在某些操作过程中，数据库会自动施加和释放锁，以保持数据的一致性和完整性。

隐式锁的特点

  1. 自动管理：隐式锁是由数据库系统在后台自动管理的，无需用户手动施加或释放。
  2. 透明性：对于用户来说，隐式锁的存在和操作通常是不可见的。
  3. 保护数据完整性：隐式锁的主要目的是确保数据在并发访问和修改时的一致性和完整性。

隐式锁的应用场景

隐式锁在InnoDB存储引擎中的应用非常普遍，以下是一些典型的应用场景：

  1. 记录级锁：在InnoDB中，即使没有明确的LOCK TABLES或SELECT ... FOR UPDATE等语句，对于更新（比如UPDATE、DELETE）操作，InnoDB会自动在被操作的行上施加行级锁（行锁是一种隐式锁）。
  2. 自动增量锁：在处理AUTO_INCREMENT字段时，InnoDB会自动使用一种特殊的锁机制来确保自增值的唯一性和连续性。
  3. 间隙锁和临键锁：在使用范围查询并进行修改操作（如SELECT ... FOR UPDATE）时，InnoDB会自动使用间隙锁或临键锁来防止幻读。

隐式锁的影响

尽管隐式锁大大简化了数据库操作并保证了数据的安全性，但在高并发场景下，它们可能会导致一些问题：

  1. 锁竞争：在高并发环境下，大量的隐式锁可能会导致锁竞争，影响数据库性能。
  2. 死锁风险：在复杂的查询和更新操作中，隐式锁可能导致死锁，特别是当多个事务涉及相同数据集时。

管理隐式锁

虽然隐式锁是自动管理的，但了解它们的存在和行为对于优化数据库性能和避免锁冲突是非常重要的。通过合理设计数据库架构、优化查询语句和适当的事务管理，可以减少隐式锁带来的负面影响。此外，了解InnoDB的锁机制和事务隔离级别也有助于更好地理解隐式锁在实际操作中的行为。

6. 悲观锁与乐观锁

MySQL，特别是其最常用的存储引擎InnoDB，实际上采用了多种锁机制，包括悲观锁和乐观锁。每种锁机制在不同的场景下发挥作用。下面是这两种锁在MySQL中的应用实例：

悲观锁

MySQL的InnoDB存储引擎主要使用悲观锁来处理并发数据修改。悲观锁在MySQL中主要表现为行锁（Row Locks）和表锁（Table Locks）。

示例：行锁

当你在InnoDB表中执行一个SELECT ... FOR UPDATE语句时，MySQL会对返回的所有行施加排他锁（X锁），这是一种悲观锁的体现。例如：

BEGIN;SELECT * FROM users WHERE id = 101 FOR UPDATE;

这个查询会锁定ID为101的用户记录，直到事务结束。在这个事务完成之前，其他任何试图修改或锁定这条记录的事务都会被阻塞。

乐观锁

MySQL并不直接支持乐观锁，但你可以在应用层实现乐观锁机制。这通常通过在表中添加一个版本号字段或时间戳字段来实现。

示例：版本号字段

假设有一个名为users的表，其中包含一个version字段，你可以使用这个字段来实现乐观锁：

  1. 读取数据时获取版本号：

    SELECT name, version FROM users WHERE id = 101;

  2. 更新数据时检查版本号并更新：

    UPDATE users SET name = 'new name', version = version + 1 WHERE id = 101 AND version = [之前读取的版本号];

如果version字段在读取和更新之间被其他事务修改过，UPDATE语句不会影响任何行。这意味着数据在读取后已被修改，乐观锁策略会拒绝这次更新。

结论

在MySQL中，悲观锁主要以行锁和表锁的形式存在，直接由数据库引擎管理。而乐观锁通常需要应用程序在业务逻辑层面实现，通常通过版本控制来完成。正确地使用这两种锁机制可以帮助提高数据库操作的效率和一致性。

当然，以下是对您提到的MySQL中JOIN操作的完整和详细介绍，包括了您的原始内容和我所建议的补充内容：

JOIN操作

连接查询的基础

  • 连接的本质：连接操作的目的是将不同表中的记录根据特定条件组合起来，形成一个包含所需信息的新的结果集。
  • 驱动表的选择：首先确定第一个需要查询的表，即驱动表。这个选择对查询性能有重要影响。
  • 记录匹配：针对从驱动表产生的每条记录，分别在被驱动表中查询并进行匹配。

内连接与外连接

  • 内连接：仅当两个表都有匹配时，才返回结果。
  • 外连接：左连接（LEFT JOIN）和右连接（RIGHT JOIN）根据左表或右表为驱动表，即使在被驱动表中找不到匹配的记录，也能返回结果，未找到匹配的部分以NULL填充。

过滤条件

  • WHERE子句：通用过滤条件，不符合条件的记录不会被加入最终结果。
  • ON子句：专用于外连接的场景，用于确定哪些记录即使在被驱动表中无匹配也应加入结果集。

索引的作用

  • 加速查询：在连接列上使用索引可以显著提高查询效率，减少磁盘I/O。

不同类型的连接

  • 交叉连接（CROSS JOIN）：产生笛卡尔积，即两个表的每条记录都与另一个表的每条记录匹配。

  • 自连接（Self Join）是一种特殊类型的SQL连接，用于将一个表与自身进行连接。这种连接通常用于处理那些存储在同一表中但需要作为两个独立实体进行比较或关联的数据。

    自连接的特点：

    1. 同一表的不同实例：在自连接查询中，同一张表被当作两个独立的表来处理，通常通过给表分配不同的别名来实现。
    2. 应用场景：当一个表中的数据需要与该表中的其他数据进行比较或关联时，自连接是非常有用的。例如，在员工表中找到所有员工及其直接上级，或者在一个包含日期数据的表中比较连续的日期。
    3. 查询方式：自连接可以是内连接、外连接等，具体取决于查询的需求。

    自连接的例子：

    假设有一个员工表 Employees，其中包含EmployeeID、Name 和 ManagerID（表示每个员工的直接上级）等字段。要找出每个员工及其直接上级的名字，可以使用自连接：

    SELECT    E1.Name AS EmployeeName,    E2.Name AS ManagerNameFROM    Employees AS E1LEFT JOIN    Employees AS E2 ON E1.ManagerID = E2.EmployeeID;

    这里，Employees 表以 E1 和 E2 两个别名出现，分别代表员工和他们的上级。通过在 ON 子句中比较 E1.ManagerID 和 E2.EmployeeID，实现了自连接的目的。

    总的来说，自连接是一种在同一表上进行的连接操作，它允许在同一查询中比较或关联表中的行。

连接顺序与优化器

  • 成本估算：MySQL优化器基于成本估算来决定表的连接顺序，以找到最佳执行计划。

连接算法

在数据库系统中，尤其是在进行表的连接操作时，连接算法的选择对于查询性能至关重要。主要有两种类型的连接算法：嵌套循环连接（Nested Loop Join）和散列连接（Hash Join）。

嵌套循环连接（Nested Loop Join）

嵌套循环连接是最基本和最常见的连接算法，适用于处理小到中等大小的数据集。

  • 工作原理：
    • 此算法通过两个表的嵌套循环来实现连接。首先，从第一个表（驱动表）中取出一行，然后在第二个表（被驱动表）中搜索匹配的行。
    • 对于驱动表中的每一行，都会执行被驱动表的完整扫描，直到找到所有匹配的行。
  • 应用场景：
    • 当连接的表较小或者连接列上有有效索引时，嵌套循环连接非常高效。
    • 适用于返回少量匹配行的查询，比如基于高选择性条件的查询。
  • 性能考虑：
    • 如果没有适当的索引，尤其是在被驱动表上，这种连接可能会导致大量不必要的磁盘I/O，从而影响性能。
    • 性能依赖于驱动表的大小和被驱动表的索引效率。
    • 使用Join Buffer在内存中缓存驱动表的记录，减少对被驱动表的重复磁盘访问。

散列连接（Hash Join）

散列连接是一种高效处理大型数据集的连接算法，特别是在没有索引或者数据量较大的场景下。这种算法在MySQL 8.0及更高版本中得到了支持。

  • 工作原理：
    • 首先，选择一个表（通常是较小的那个）来构建哈希表。算法遍历这个表，对每行的连接键应用哈希函数，并将结果存储在哈希表中。
    • 然后，算法遍历第二个表，同样对每行的连接键应用哈希函数，并在哈希表中查找匹配的行。
    • 由于哈希表提供了快速的查找能力，这种方法可以有效地处理大量数据。
  • 应用场景：
    • 当处理大型数据集，尤其是其中一个或两个表都没有有效索引时，散列连接特别有效。
    • 适合于需要返回大量匹配行的查询，例如大规模的全表连接。
  • 性能考虑：
    • 散列连接的性能主要取决于内存。哈希表需要足够的内存空间来存储，如果内存不足，可能会导致性能下降。
    • 由于不依赖于数据的物理存储顺序，散列连接在处理无序或无索引数据集时更有优势。

使用Join Buffer的场景：

  1. 无适用索引的嵌套循环连接：
    • 当连接操作无法利用索引时（特别是在被驱动表上），使用Join Buffer可以减少对被驱动表的重复访问，提高效率。
  2. 小到中等规模的数据集：
    • 对于不太大的数据集，Join Buffer可以有效地提高嵌套循环连接的性能。
  3. 单个或少数几个行的重复使用：
    • 当驱动表中的行需要与被驱动表中的多行进行比较时，Join Buffer特别有用。

使用Hash Join的场景：

  1. 处理大型数据集：
    • Hash Join特别适合于处理大规模数据集，尤其是当表之间没有有效的索引时。
  2. MySQL 8.0及以上版本：
    • 如果您使用的是MySQL 8.0或更高版本，可以充分利用Hash Join的优势，因为这个版本对Hash Join做了优化。
  3. 大量匹配行的查询：
    • 当预期的匹配行数较多时，Hash Join通常比嵌套循环连接更有效率。

嵌套循环连接因其简单性在小型数据集中广泛使用，特别是当存在高效索引时。相比之下，散列连接适用于处理大型数据集或缺乏索引的场景，特别是在MySQL的新版本中。散列连接通过哈希表来优化查找过程，使得它在这些情况下更为高效。

连接的性能影响

  • 性能考量：大型表的连接可能导致性能下降。通过优化查询或调整表结构可以改善性能。

锁机制

  • 并发控制：在多用户环境下，连接操作可能涉及锁定机制，特别是涉及写操作时。

不推荐使用外键

阿里的《Java 开发手册》明确规定禁止使用外键。不过这样用起来就和MongoDB差不多了…

Untitled

尽可能把所有列定义为 NOT NULL

除非有特别的原因使用 NULL 值，应该总是让字段保持 NOT NULL。

  • 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；
  • 进行比较和计算时要对 NULL 值做特别的处理。

相似文章:

多级评论结构设计

消息队列

自建博客简明教程

发表评论