【MYSQL知识点总结】——手撸万字长文！

---

前言

根据DB-Engines的2023年6月份排名，‌MySQL排名第一，‌而Oracle排名第二。‌这一排名反映了MySQL的使用率高于Oracle。‌MySQL是一个开源的关系数据库管理系统（‌RDBMS）‌，‌以其速度、‌可靠性和可扩展性而闻名，‌广泛用于Web应用程序和电子商务平台。‌我们有必要就对其常用技术细节进行学习总结。

---

一、幻读、脏读、不可重复读

幻读 (Phantom Read)

• 定义：在一个事务中，两次查询返回的结果集不一样，即使在这两次查询之间没有其他事务对数据进行修改。
• 原因：在 可重复读 隔离级别下，由于 MVCC（多版本并发控制）机制的存在，一个事务可以读取到另一个事务插入的新记录。
• 示例：假设事务 A 查询了某表中的所有记录，然后事务 B 插入了一条新记录，接着事务 A 再次查询该表的所有记录。这时，事务 A 第二次查询的结果集包含了事务 B 新插入的记录，这就产生了幻读。

脏读 (Dirty Read)

• 定义：一个事务读取到了另一个事务尚未提交的数据。
• 原因：在 未提交读 隔离级别下，一个事务可以读取到另一个事务尚未提交的数据。
• 示例：假设事务 A 更新了一条记录，但还没有提交。事务 B 此时查询这条记录，读取到了事务 A 尚未提交的数据。如果之后事务 A 回滚，事务 B 读取的数据就变成了无效数据，这就是脏读。

不可重复读 (Non-Repeatable Read)

• 定义：在一个事务中，两次查询返回的结果不一样，即使在这两次查询之间没有其他事务对数据进行修改。
• 原因：在 已提交读 隔离级别下，由于 MVCC（多版本并发控制）机制的存在，一个事务可以读取到另一个事务已经提交的数据。
• 示例：假设事务 A 查询了一条记录，然后事务 B 更新了这条记录并提交。接着事务 A 再次查询这条记录，发现数据已经被事务 B 修改了。这时，事务 A 第二次查询的结果与第一次不同，这就产生了不可重复读。

总结

• 幻读：事务 A 两次查询结果集不一致，即使没有其他事务修改数据。
• 脏读：事务 A 读取到了事务 B 尚未提交的数据。
• 不可重复读：事务 A 两次查询结果不一致，因为事务 B 已经提交了数据修改。

注意区分幻读和不可重复读的区别，前者是两次查询的范围的结果集不一样，后者是同一条记录，事务的两次查询的结果不一样。

---

二、MySQL中的事务隔离级别

1. 未提交读 (READ UNCOMMITTED)
   • 在这个隔离级别下，一个事务可以读取到另一个事务尚未提交的数据（脏读）。
   • 这是最弱的隔离级别，它允许事务读取未提交的数据，可能导致数据不一致的问题。
   • 通常不建议在生产环境中使用这个隔离级别。
2. 已提交读 (READ COMMITTED)
   • 在这个隔离级别下，一个事务只能读取到另一个事务已经提交的数据。
   • 这意味着事务不会读取到脏数据，但是仍然可能出现不可重复读和幻读的情况。
   • 这是 SQL 标准中定义的最低隔离级别，许多数据库系统默认使用这个隔离级别。
3. 可重复读 (REPEATABLE READ)
   • 在这个隔离级别下，一个事务在整个事务期间可以多次读取同一数据，并且得到相同的结果，即使有其他事务在此期间进行了修改。
   • 这意味着事务可以重复读取同一数据集，不会受到其他事务的影响。
   • MySQL 的 InnoDB 存储引擎默认使用这个隔离级别。
   • 在这个隔离级别下，仍然可能发生幻读，即事务能看到新插入的记录。
4. 串行化 (SERIALIZABLE)
   • 在这个隔离级别下，事务被完全串行化执行，即一个接一个地执行，以避免任何并发问题。
   • 这是最强的隔离级别，可以避免脏读、不可重复读和幻读等问题。
   • 但是，它可能会导致性能下降，因为事务必须等待其他事务完成才能执行。

下面是一张表格总结这四种隔离级别及可能遇到的问题

隔离级别	特点	可能遇到的问题
未提交读（READ UNCOMMITTED）	允许事务读取未提交的事务 最低隔离级别	脏读 幻读 不可重复读
已提交读（READ COMMITTED）	事务只能读取已提交的数据 不允许脏读	幻读 不可重复读
可重复读（REPEATABLE READ）	事务可以重复读取同一数据集 不允许脏读和不可重复读	幻读
串行化 (SERIALIZABLE)	事务完全串行化执行 不允许脏读、不可重复读和幻读	无

上表中的可能会遇到的问题，是指在某种隔离级别下可能发生的问题，比如可重复读隔离级别，其实MySQL的InnoDB引擎是提供了一些手段来避免幻读的，但如果不正确使用，仍然会发生幻读。

设置MySQL（mysql8及以上）的事务隔离级别的SQL语句分别如下：

1.未提交读

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2.已提交读

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.可重复读

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4.串行化

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

注意以上命令都只在当前会话session下有效，会话关闭后又会恢复默认值REPEATABLE READ。要想设置全局有效，就把SESSION 换成GLOBAL即可。

我们一般不要去修改这个默认隔离级别，很容易出问题，知道有这些命令就可以，真要修改也是需要经过部门负责人或者数据库管理员同意。

查看全局事务隔离级别

SELECT @@GLOBAL.TRANSACTION_ISOLATION;

查看当前会话事务隔离级别

SELECT @@SESSION.TRANSACTION_ISOLATION;

三、MySQL的InnoDB引擎如何解决幻读

InnoDB 存储引擎通过使用间隙锁(Gap Locks) 和 next-key 锁 (Next-Key Locks) 来解决幻读问题。这两种锁机制主要在可重复读 (Repeatable Read) 隔离级别下生效。

间隙锁 (Gap Locks)

• 定义: 间隙锁用于锁定索引中的某个区间，而不是具体的行。
• 作用: 防止其他事务在已锁定的索引区间内插入新的行。
• 示例: 如果索引中存在键值 1 和 5，则间隙锁会锁定这两个键值之间的区间 (1, 5)。

next-key 锁 (Next-Key Locks)

• 定义: next-key 锁是记录锁 (Record Lock) 和间隙锁 (Gap Lock) 的组合。
• 作用: 解决幻读问题，同时减少锁定范围，提高并发性能。
• 组成:
  • 记录锁: 锁定具体的行。
  • 间隙锁: 锁定行所在的区间。

如何工作

在可重复读隔离级别下，当一个事务执行范围查询时，InnoDB 会自动应用 next-key 锁。这意味着对于查询范围内的每一行，InnoDB 都会：

1. 对查询到的具体行加记录锁。
2. 对查询范围内的间隙加间隙锁。

示例

假设有一个简单的表 t，包含一个整数列 id，并且表中有以下记录：(1, ‘a’), (5, ‘b’), (10, ‘c’)。

• 事务 A 执行 SELECT * FROM t WHERE id BETWEEN 5 AND 10 FOR UPDATE;。
• 事务 B 尝试执行 INSERT INTO t (id, value) VALUES (7, ‘d’);。

在这种情况下：

1. 事务 A 会获取 id = 5 和 id = 10 的记录锁。
2. 事务 A 也会获取 (5, 10) 区间的间隙锁。
3. 事务 B 的 INSERT 操作会被阻塞，因为它试图插入到已经被事务 A 锁定的区间内。

注意事项

• 死锁: 使用 next-key 锁可能会导致死锁，因为锁的粒度更大，增加了锁冲突的可能性。
• 性能: 在高并发场景下，过多的 next-key 锁可能会降低系统的整体性能。
• 优化: MySQL 5.6 之后的版本引入了“自适应哈希索引”(Adaptive Hash Index, AHI) 和“自适应 next-key 锁”(Adaptive Next-Key Locking) 来减少锁的开销并提高并发性能。

总结

InnoDB 通过使用 next-key 锁来解决幻读问题，其中包含了记录锁和间隙锁。这种机制确保了在可重复读隔离级别下，事务能看到一致的数据视图，并且不会出现幻读现象。同时，通过适当的配置和优化，可以减少锁的开销，提高系统的并发性能。

---

四、存储引擎

MD5值存储使用VARCHAR还是CHAR

先来了解下VARCHAR和CHAR的区别

1. 存储长度不同CHAR的长度是固定的，而VARCHAR的长度可变。
2. 存储效率上CHAR效率更高，因为其每次修改数据不需要调整长度。
3. 存储空间VACHAR更节省，因为其是按照字符串实际长度和一个记录字符串长度的字节分分配空间

而MD5是一种摘要算法生成的数据，长度是固定的，所以适合用CHAR

导致MySQL索引失效的原因有哪些

1. 使用函数或表达式操作索引列
   如果查询条件中对索引列使用了函数或者表达式，那么索引通常无法使用。

2. 使用 OR 连接的条件
   如果一个 WHERE 子句中使用了 OR 连接多个条件，并且其中一个条件涉及未索引的列，则整个索引可能失效。

3. 不匹配的数据类型
   如果查询条件中的数据类型与索引列的数据类型不匹配，索引可能不会被使用。

4. 前缀索引不匹配
   如果使用的是前缀索引，而查询条件没有覆盖整个前缀长度，索引可能不会被使用。

5. 范围查询后的等值查询
   在使用范围查询之后再进行等值查询时，索引可能不会被使用。

6. 字符串不带前导通配符的 LIKE 操作
   如果使用了 LIKE 操作符，并且通配符 % 出现在搜索值的开始位置，索引可能不会被使用。

7. 隐式类型转换
   当比较不同类型的值时，MySQL 可能会进行隐式类型转换，这可能导致索引失效。

8. 全文索引特定条件
   全文索引在某些条件下可能不会被使用，例如使用了禁用词或者短词。

9. 不使用索引列
   如果查询条件中没有涉及到索引列，则索引不会被使用。

10. 索引选择性差
    如果索引的选择性不好（即索引列包含大量重复值），MySQL 可能会选择全表扫描而不是使用索引。

为了确保索引能够被有效利用，可以考虑以下几点：

• 尽量避免在索引列上使用函数或表达式。
• 使用 AND 而不是 OR 来连接条件，尤其是在涉及索引列的时候。
• 对于 LIKE 查询，尽量将通配符放在末尾。
• 确保所有比较都使用相同的数据类型。
• 为经常用于查询的列创建合适的索引。
• 使用 EXPLAIN 命令来检查查询计划，了解MySQL是否正确地使用了索引。

聚集索引和非聚集索引

聚集索引（Clustered Index）

• 定义：聚集索引是一种特殊的索引形式，它决定了表中数据行的物理存储顺序。也就是说，表的数据行按照聚集索引键的顺序存储。
• 特点：
  • 表的数据行存储在索引的叶子节点上。
  • 一个表只能有一个聚集索引，因为数据行只能按一种顺序存储。
  • 聚集索引通常比非聚集索引更快，因为它不需要额外的查找步骤来获取数据行。
  • InnoDB 存储引擎中，如果没有显式指定主键，InnoDB 会自动创建一个隐藏的聚集索引。
• 适用场景：
  • 需要频繁访问数据行的场景。
  • 数据行经常被修改的情况下，因为修改聚集索引会影响数据行的位置。

非聚集索引（Non-Clustered Index）

• 定义：非聚集索引是指索引结构与数据行的物理存储顺序无关的索引。每个非聚集索引都有一个指向实际数据行的指针。
• 特点：
  • 索引结构与数据行分开存储。
  • 一个表可以有多个非聚集索引。
  • 非聚集索引的叶子节点包含指向数据行的指针，而不是数据行本身。
  • 需要额外的查找步骤（index lookup）来获取数据行。
• 适用场景：
  • 需要快速查找数据行的场景，特别是当数据行很大时。
  • 当数据行不经常被修改时。

区别总结

• 存储位置：聚集索引的数据行直接存储在索引中，而非聚集索引的数据行存储在另一个地方，索引只包含指向数据行的指针。
• 索引数量：一个表只能有一个聚集索引，但可以有多个非聚集索引。
• 性能：对于简单的查询，聚集索引通常更快，因为它不需要额外的查找步骤；但对于复杂的查询，非聚集索引可能更优，因为它提供了更多的灵活性。
• 更新成本：更新聚集索引的成本较高，因为数据行的实际位置可能改变；而非聚集索引只需更新指针即可。

---

五、如何理解B树和B+树

树形结构

树型结构是一类重要的非线性数据结构。其中以树和二叉树最为常用，直观看来，树是以分支关系定义的层次结构。把它叫做“树”是因为它常看起来像一棵倒挂的树，也就是说它常是根朝上，而叶朝下的。树形结构主要用来描述一对多的关系，是一种层次化的结构。

树结构在现实世界是大量存在的，并不是只有计算机中才有的概念，比如人类的族谱，社会中的组织机构等等。

下面是一些树结构的属性特点：

1. 根节点：
   • 树有一个唯一的根节点，它是树的最高层节点，没有父节点。
2. 子节点与父节点：
   • 除了根节点之外，每个节点都有且仅有一个父节点。
   • 节点可以拥有零个或多个子节点。
3. 分支：
   • 每个节点可以被视为一个小树的根，形成分支。
4. 叶子节点：
   • 叶子节点是没有子节点的节点。
5. 路径与边：
   • 两个节点之间的连接称为边。
   • 从一个节点到另一个节点的边的序列称为路径。
6. 深度与高度：
   • 节点的深度是从根节点到该节点的路径上的边的数量。
   • 树的高度是树中最深叶子节点的深度。
7. 度数：
   • 节点的度数是指该节点拥有的子节点数量。
   • 树的度数是树中所有节点的最大度数。
8. 无环：
   • 树中不存在环路，即从一个节点出发，不重复经过任何节点不能回到原节点。
9. 层次性：
   • 树形结构具有明确的层次关系，每个节点位于不同的层级。

以上九个属性涵盖了树结构的基本特点，以此为基础我们接着来解释下B树和B+树

二叉树

先来了解下二叉树。二叉树很明显和二这个数字脱离不了关系，它的特点如下：

• 每个节点最多有两个子节点，这两个子节点分别被称为左子节点（Left Child）和右子节点（Right Child）。
• 左右子节点是有区别的，即**左子节点和右子节点的位置不可以互换**。
• 二叉树可以是空树，也可以包含一个根节点和两棵不相交的二叉树（左子树和右子树）。
• 在二叉树中，一个节点如果没有子节点，则称为叶子节点（Leaf Node）。

二叉树主要有以下几个分类：

• 满二叉树：除了最后一层外，每一层上的所有节点都有两个子节点的二叉树。
• 完全二叉树：除了最后一层外，每一层上的节点数都达到最大，且最后一层的节点都集中在该层最左边的二叉树。
• 平衡二叉树：二叉树的左右两个子树的高度差不大于1，并且左右两个子树都是一棵平衡二叉树。

二叉树的常见操作如下：

• 遍历：常见的遍历方法有前序遍历（先访问根节点，然后遍历左子树，最后遍历右子树）、中序遍历（先遍历左子树，然后访问根节点，最后遍历右子树）、后序遍历（先遍历左子树，然后遍历右子树，最后访问根节点）。
• 插入：在二叉树中插入新节点，通常遵循一定的规则，例如在二叉搜索树中，新节点的值小于父节点则插入左子树，大于父节点则插入右子树。
• 删除：删除二叉树中的节点时，需要考虑节点是否有子节点，以及如何调整子节点的位置来保持二叉树的性质。

关于二叉树，这里不做过多介绍，简单了解其结构特性即可，深入学习了解还得等到笔者去研究学习数据结构再讨论吧。说到这里，笔者其实已经厌烦了各种框架技术栈的学习，想开始学习一些基础操作系统、计算机网络、数据结构和算法的知识，这些才是真正的核心。

二叉搜索树

又叫二叉查找树，看名字就知道这种结构的树主要是为查询服务的，注重查询性能。二叉搜索树是二叉树的子集，多了一个规则如下：

• 每个节点的键值大于其左子树中的任何一个节点的键值，小于其右子树中的任何一个节点的键值。

这个规则使得二叉搜索树能够支持高效的查找、插入和删除操作。不需要遍历大量的节点数据。

平衡二叉搜索树

平衡二叉搜索树 (Balanced BST)又是二叉搜索树的一个子集，即插入节点数据过程中根据需要调整树结构以维持平衡。

• 定义: 平衡二叉搜索树是一种特殊的二叉搜索树，它在二叉搜索树的基础上增加了平衡性要求。
• 性质: 不仅有二叉搜索树的所有性质，还要求任何节点的左子树和右子树的高度差不超过预设的常数值（通常是1）。这意味着整棵树的高度始终保持在一个较低的范围内。
• 操作: 由于树的高度被控制在一个较小的范围内，所以查找、插入和删除操作的时间复杂度在最坏情况下也能保持在 O(log n)。为了维持平衡，树在插入或删除节点后可能会执行旋转操作（如左旋、右旋）。

B树

B树（B-tree）是一种自平衡的多路查找树，可以用来存储有序的具有层次结构的数据，并且通过自平衡机制确保数据的查找、插入、删除等操作都能在对数时间内完成。它的主要设计目的是为了优化磁盘等直接存储设备上的数据访问效率。

为什么数据库中的索引查找大多使用B树来进行数据的查询呢？其他的平衡查找树如AVL、红黑树都是假设数据基于内存上的，数据量不会很大，而数据库中的数据都是在磁盘中的，数据量通常很大，只能以块的形式，磁盘I/O是十分耗时的，所以我们自然想要减少磁盘I/O的次数，B树的出现就是为了解决这个问题，尽量去减少磁盘I/O。即B树特别适合于外部存储数据的查找，如磁盘、固态硬盘等等，当然内部数据内存数据的查找，B树也提供了很好的性能。

B树满足平衡二叉树的规则，可以不断地调整树以满足树高度的要求，但是它允许有多个子节点，而不是某个节点下只允许最多两个子节点。B树的这个多子节点的规则就是为了控制树的深度，减少多次的磁盘访问，B树的节点通常设计的很大，这样一次读取一个节点可读取到更多的信息，自然也就减少了访问次数。

定义

• B树是一种多路平衡查找树，每一个节点可以拥有多个子节点，并且没有空（nil）的子节点指针。
• 节点中的元素按照键值大小排序，并且键值之间的相对位置决定了子树的方向。
• 所有的叶子节点都位于同一层，并且叶子节点之间通过指针相连。

B+树

B+树是B 树的升级改造，在某些场景下有一定的优势，但不能完全取代B树，他们之间的不同点

1. 键值存储位置:

• 在B树中，所有节点（包括非叶子节点）都可以存储键值对(key-value pairs)，这意味着键和值可以存储在树的任意层。
• 而在B+树中，键值对只存储在叶子节点中，非叶子节点只存储键来辅助快速定位。

2. 叶子节点结构:

• B树的叶子节点和其他节点一样，可以存储键值对，并且叶子节点之间没有直接链接。
• B+树的所有叶子节点通过一个链表相互链接起来，这样可以方便地进行范围查询。

3. 数据访问路径:

• 在B树中，由于数据可能存储在非叶子节点，因此查找数据时可能不需要访问到叶子节点。
• 对于B+树，查找数据总是需要到达叶子节点，因为所有的数据都存储在叶子节点上。

4. 区间查找:

• B+树更适合区间查找或排序查找，因为所有的数据记录地址都在叶子节点上，且叶子节点本身是有序且链接的。
• B树不适合区间查找，因为数据分布在各个层级，没有直接的顺序链接。

5. 磁盘I/O效率:

• B+树的单次磁盘I/O能加载更多的数据，因为叶子节点包含实际的数据，而非叶子节点只包含索引信息。
• B树每次磁盘I/O加载的数据可能较少，因为它需要存储更多的指针和值信息。

---

六、Mysql性能优化

对于Mysql数据库服务器的优化，笔者认为可以从下面四个方面分析：

1. 硬件和操作系统层面的优化
2. 架构设计层面优化
3. Mysql程序配置优化
4. SQL优化

6.1 硬件和操作系统层面的优化

从硬件层面来说主要有这四个因素：CPU、可用内存大小、磁盘读写速度、网络带宽

操作系统层面来看，应用程序文件句柄数（指的是应用打开的文件数）、操作系统的网络配置都会影响到Mysql性能

6.2 架构设计层面的优化

主要要应对要求高性能、高并发场景的优化点

1. 搭建mysql集群
   • 避免单点故障导致的服务不可用，搭建主从集群或者主从集群，保证服务的高可用
2. 读写分离设计
   • 读多写少的场景，使用读写分离机制，避免读写冲突导致的性能影响。
3. 分库分表
   • 当数据特别巨大时，分库存储是一个很好的解决方案，避免了单个节点的服务压力，针对个别表数据量很大时采用分表可以提升sql的查询性能
4. 引入分布式缓存
   • 对于一些热点数据，可以使用分布式数据库redis、mongodb等提升性能，减少mysql数据库访问压力，提升数据检索性能

6.3 Mysql程序配置优化

Mysql数据库本身的优化，一般是在它的my.cnf配置文件中修改一些配置参数

• 最大连接数
• binlog日志是否开启
• 缓存池默认的bufferpoll默认大小配置

对于上面的配置分为全局和会话级别的区分：

• 全局配置的修改对已经存在的会话无效
• 会话级别配置的修改对全局无效，会话消失即销毁
• 全局配置一般通过配置文件配置，不要通过sql语句去修改配置，服务重启配置会还原

6.4 SQL优化

SQL优化分为三步曲

1. 慢sql的定位和排查
   可以通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表

2. 执行计划分析
   针对慢sql，可以通过关键字explain来查看当前sql的执行计划，重点关注type、key、rows、filterd等字段，从而定位到执行慢的问题进行解决，下面对这几个字段进行解释

   • type： 表示访问表的方式或连接类型，这是执行计划中最重要的一列。从最差到最好的顺序大致为 ALL, index, range, ref, eq_ref, const, system, NULL。
     • ALL: 表示MySQL将遍历整个表来找到所需的数据。这是最慢的方式之一，因为需要扫描每一行数据。通常发生在没有合适的索引可用于查询的情况。
     • index: 表示全索引扫描，即MySQL使用索引而不是实际的数据表来找到数据。如果查询只需要索引中的列，那么这种方式可以很快，因为不需要访问实际的数据行。但是，如果还需要其他未包含在索引中的列，那么MySQL需要通过索引找到对应的行，这称为“回表”。
     • range: 表示MySQL使用索引范围查询来查找数据。这通常发生在使用了比较运算符（如<, <=, >, >=, BETWEEN, IN）的查询中。MySQL会查找一定范围内的索引条目。
     • ref: 表示MySQL使用一个索引来查找行，但不是通过等值比较，而是通过一个引用（通常是另一个表中的索引项）。例如，在连接查询中，当一个表被用于查找另一个表中的行时。
     • eq_ref: 类似于ref，但是用于相等比较。通常出现在多表连接查询中，当一个表的索引被用于基于另一个表的主键或唯一索引来查找行时。
     • const 或 system: 如果MySQL能够通过一次查找就确定结果，通常是因为查询的是一个常量或者是单行的系统表（system类型），那么就会使用这种类型。例如，查询条件中包含了主键或者唯一索引的等值比较。
     • NULL: 通常表示不需要访问表或索引，因为查询的结果可以直接从缓存或其他方式获得。也可能表示MySQL优化器还没有决定使用哪种访问方法。

3. 使用show profile工具
   Show Profile 是 MySQL 提供的可以用来分析当前会话中，SQL 语句资源消耗情况的
   工具，可用于 SQL 调优的测量。在当前会话中.默认情况下处于 show profile 是关闭状
   态，打开之后保存最近 15 次的运行结果，针对运行慢的 SQL，通过 profile 工具进行详细分析.可以得到 SQL 执行过程中所有的资源开销情况. 如 IO 开销,CPU 开销,内存开销等。

---

七、如何理解MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种在数据库管理系统中用于提高读取操作并发性的技术。它允许多个事务同时读取同一数据项的不同版本，从而减少了事务之间的锁竞争。

MVCC的一些关键点有如下几点

• 版本管理：每个数据项可以有多个版本，每个版本与创建它的事务关联。
• 读不阻塞写：读取操作不会阻塞写入操作。读取时会访问旧的数据版本，允许写入操作同时进行而不被阻塞。
• 写可能阻塞写：当两个事务试图修改同一数据项时，第二个写入操作可能会被阻塞，直到第一个事务完成并提交其更改。
• 快照隔离：每个事务都有一个与其开始时刻相关的“快照”，决定了事务能看到哪些数据版本。这有助于实现事务的隔离级别。

MVCC原理的实现依赖于以下几个核心原理，包括 版本链、Read View（一致性视图）、以及 区分快照读和当前读。

版本链

每当一个事务更新一行数据时，InnoDB存储引擎并不会直接覆盖旧的数据，而是会产生一个新的记录，并且旧的记录会被保存在一个称为版本链的结构中。每个版本都有关联的时间戳或事务ID，这样就可以追踪到每个版本的历史状态。

Read View（一致性视图）

一致性视图是用来决定一个事务能够看到哪些数据版本的一个数据结构。每个事务在其开始时都会创建一个Read View，这个视图包含了所有当前活跃事务的信息。Read View包含以下几部分：

• m_ids：一个列表，包含所有活跃事务的事务ID。
• min_trx_id：最小的事务ID，小于这个ID的所有事务都已经提交。
• max_trx_id：最大的事务ID，大于这个ID的事务尚未启动。
• creator_trx_id：创建此Read View的事务ID。

通过这些信息，数据库可以确定一个给定的版本是否对当前事务可见。如果一个版本是由一个已经提交的事务产生的，并且其事务ID不在当前事务的Read View中（意味着该事务已经结束），那么这个版本就是可见的。

快照读 vs 当前读

• 快照读：这是默认的行为，当一个事务执行SELECT语句时，它会从数据的一致性视图中读取数据，而不是最新的版本。这意味着读取操作不会影响其他事务，并且不会锁定数据。
• 当前读：这种读取总是获取最新的数据版本，并且通常涉及锁定机制，例如通过使用SELECT … FOR UPDATE或SELECT … LOCK IN SHARE MODE语句。当前读保证了事务可以看到最新的数据状态，并且可以在读取的同时防止其他事务修改数据。

---

八、Mysql索引失效详解

注意：下面的索引失效场景都不是绝对失效的，因为Mysql的存储引擎、查询优化器、查询条件的选择等可能会智能处理某些失效场景，从而使用索引。我们只需要关注如何让这些索引可能失效的场景变得不会失效或者尽量不会失效，即采用正确的，保证查询优化器大概率会走索引的方式。

8.1 参数类型与字段类型不匹配

如果字段类型为varchar，但在SQL语句中使用了int类型，或者反之，这种类型的不匹配会导致索引失效。

假设有一个表t1，其中包含字段id（INT类型）和name（VARCHAR类型）。假设id字段上有索引

正确使用索引的例子：

-- 假设`id`字段上有索引
SELECT * FROM t1 WHERE id = 123;

可能导致索引失效的例子：

-- 尝试用字符串去匹配整数字段
SELECT * FROM t1 WHERE id = '123';

如果使用的字符串为纯数字，这种情况下MySql查询优化器可能会进行隐式的类型转换从而使索引继续生效，也可能不会进行转换而使索引失效。

8.2 使用OR操作

如果查询条件使用OR关键字连接，并且其中一个字段没有创建索引，则可能导致整个查询语句索引失效。然而，如果所有相关字段都建立了索引，并且MySQL优化器认为可以有效地使用它们，索引仍然可能会被使用。

假设我们有一个表 t1，包含字段 id（INT 类型，有索引）和 name（VARCHAR 类型，没有索引）。

情况一：其中一个字段没有创建索引

如果查询条件使用 OR 关键字连接，并且其中一个字段没有创建索引，那么整个查询语句可能会导致索引失效。这是因为 MySQL 优化器需要处理多个条件，其中一个条件没有索引会导致整体性能下降。

SELECT * FROM t1 WHERE id = 123 OR name = 'John';

在这种情况下，虽然 id 字段有索引，但 name 字段没有索引。MySQL 优化器可能会选择全表扫描，因为处理 name 字段的条件会带来较大的开销。

情况二：所有相关字段都建立了索引

如果所有相关字段都建立了索引，并且 MySQL 优化器认为可以有效地使用它们，索引仍然可能会被使用。这是因为在某些情况下，优化器可以有效地利用多个索引。

SELECT * FROM t1 WHERE id = 123 OR name = 'John';

在这种情况下，虽然有两个条件，但每个条件都有相应的索引。MySQL 优化器可能会选择使用这两个索引之一，或者结合使用两个索引来优化查询。

情况三：使用复合索引
还有一种方法是使用复合索引（多列索引），这样可以在一个索引中包含多个字段。例如，创建一个包含 id 和 name 的复合索引

CREATE INDEX idx_id_name ON t1 (id, name);

在这种情况下，即使使用 OR 关键字连接多个条件，MySQL 优化器仍然可以有效地利用这个复合索引。

8.3 使用 LIKE 通配符

在MySQL中，使用LIKE通配符进行查询时，索引的有效性取决于通配符的位置以及查询的具体模式。以下是几种情况下的行为：

• %在后面：如果LIKE查询中的通配符“%”位于查询字符串的末尾，例如 column LIKE ‘prefix%’，则MySQL可以有效地使用索引来加速查询。这是因为索引可以用来快速定位所有以prefix开头的记录。

• %在前面：如果LIKE查询中的通配符“%”位于查询字符串的开头，例如 column LIKE ‘%suffix’，那么索引将无法被有效利用，因为在这种情况下，MySQL必须从索引的每个条目开始检查，这几乎等同于全表扫描。

• %在中间：如果通配符“%”出现在查询字符串的中间，例如 column LIKE ‘prefix%suffix%’，那么索引的使用取决于索引的结构和查询优化器的决策。在某些情况下，MySQL可能会尝试使用部分索引来优化查询，但这不是最佳实践，因为性能通常不佳。

总的来说，LIKE查询是否使用索引主要取决于通配符的位置。为了保证查询性能，建议尽可能地避免在LIKE查询中使用以“%”开头的模式。如果确实需要进行模糊查询，并且性能是关键因素，可以考虑使用全文索引（FULLTEXT index）来替代传统的B-tree索引，特别是当需要处理大量的文本数据和复杂的全文搜索时。全文索引专门用于处理文本搜索，并且可以很好地支持含有通配符的查询。

8.4 在索引列上使用函数

在MySQL中，在索引列上使用函数通常会导致索引失效，这是因为索引是为了加速基于列值的查找而设计的数据结构。当在索引列上应用函数时，MySQL无法直接利用索引来查找结果，因为索引中存储的是原始值，而不是经过函数处理后的值。

那么为什么在索引列上使用函数就会导致索引失效呢？

1. 索引的设计目的：索引是按照原始值排序的，如果在索引列上应用了函数，那么排序规则就被打破了。例如，UPPER(column) 和 column 的排序方式不同，索引无法直接应用于这种变化后的值。

2. 函数改变了列值：当对索引列使用函数时，函数改变了列的值，这使得索引中的数据不再适用于查询条件。例如，SELECT * FROM table WHERE UPPER(column) = ‘VALUE’，这里UPPER(column)的结果无法直接通过column的索引来定位。

尽管如此，有些简单的情况下MySQL还是可以使用索引来优化查询的：

• 对于某些简单的函数，如LENGTH(column)，MySQL可以使用索引来优化查询，因为它不需要改变排序。

• 如果函数的应用不影响查询的执行路径，MySQL可能会使用索引。例如，SELECT * FROM table WHERE column IS NOT NULL AND UPPER(column) = ‘VALUE’，这里的IS NOT NULL可以帮助MySQL使用索引。

8.5 索引列的值出现大量重复

在MySQL中，索引的存在是为了加速数据检索过程。然而，当索引列的值出现大量重复时，索引的效果可能会受到影响。以下是几种情况及其影响：

1. 唯一索引：如果尝试在一个有许多重复值的列上创建唯一索引（UNIQUE INDEX），将会失败，因为唯一索引不允许有重复的值。只有当列中的值确实是唯一的时候，才能成功创建唯一索引。
2. 普通索引：对于普通的B树索引来说，即使索引列中有大量的重复值，索引仍然会被使用。但是，由于每个重复的值都会在索引中占用空间，这会导致索引变得相对庞大，从而影响到索引的性能。此外，如果一个索引的大部分值都是重复的，那么这个索引的效率就会大大降低，因为它不能有效地减少搜索空间。
3. 索引选择性：索引的选择性是指索引能够区分不同记录的能力。如果索引列的值高度重复，那么这个索引的选择性就很低，MySQL优化器可能会决定不使用这个索引，因为使用它并不能显著减少需要检查的数据行数。
4. 性能影响：尽管索引可以被使用，但是如果索引列的值非常重复，那么在执行查询时，MySQL可能需要遍历整个索引来找到所有匹配的行，这实际上并没有带来多少性能上的好处。

总结来说，索引列值的大量重复并不会阻止MySQL使用索引，但它确实会影响索引的有效性和性能。为了最大化索引的好处，通常建议在选择性高的列上创建索引，即那些值分布均匀、重复少的列。

---

九、Mysql数据库cpu飙升问题

对于这个问题，要按照以下步骤分析，现在默认服务器是linux系统

定位问题

• 使用top或ps命令查看哪些进程占用了较高的CPU资源。
• 检查MySQL慢查询日志，找出执行效率低下的SQL语句。

优化查询

• 对于发现的慢查询，分析其执行计划（使用EXPLAIN），并根据结果优化这些查询。
• 添加适当的索引以加快查询速度。
• 调整查询逻辑或使用更高效的数据访问模式。

管理连接

• 如果存在大量的数据库连接，考虑增加wait_timeout和interactive_timeout的值，或者调整连接池的设置。
• 使用SHOW PROCESSLIST命令查看当前所有活动的连接，并杀死不必要的长时间运行的查询。

调整MySQL配置

• 根据服务器硬件调整innodb_buffer_pool_size，确保它适合你的工作负载。
• 调整query_cache_size。需要注意的是，在MySQL 5.7中默认已经禁用了查询缓存，因为它往往带来的好处不如预期。
• 调整thread_cache_size以减少创建新线程的成本。

监控和报警

• 设置监控和报警机制，以便及时发现CPU使用率过高的情况。
• 使用工具如Percona Toolkit或MySQL Enterprise Monitor来持续监控数据库性能。