MySQL中所有常见知识点汇总

半个菜鸟_

已于 2024-06-04 16:25:37 修改

阅读量921

点赞数 21

文章标签： mysql 数据库

于 2024-06-04 16:24:28 首次发布

本文链接：https://blog.csdn.net/qq_62074445/article/details/139446772

版权

存储引擎

这一张是关于整个存储引擎的汇总知识了。

MySQL体系结构

这里是MySQL的体系结构图：

一般将MySQL分为server层和存储引擎两个部分。

其实MySQL体系结构主要分为下面这几个部分：

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
缓存：存储一些查询语句的数据，但是在MySQL8.0后已经被删除了
分析器：包括两个主要功能
- 词法分析：MySQL进行识别字符串是什么，代表什么
- 语法分析：根据词法分析的结构，语法分析器判断SQL语句是否能满足MySQL语法。
  - 在语法分析的时候，会检查词法分析后的表和列是否存在数据库中
优化器：对一个SQL语句的执行提出多种方案，选择一种最佳的方案
- 一般是索引的选择和使用、连接的优化
执行器：这里就是调用存储引擎来执行SQL语句了
- 注意：执行器一般是负责协调和控制查询执行的过程，实际的数据遍历和检索操作大多是由存储引擎来完成

我再来简化一下上述部分的用处：

需求接收（用户->连接器）：
- 场景: 想象您的“上司”（用户）有一个具体的需求（SQL查询），他通过“手机消息”（连接器，如MySQL的网络接口）通知您（MySQL数据库）。
- 动作: MySQL的连接器负责接收用户的SQL请求，建立连接，并验证用户身份及权限。
快速检查过往经验（查询缓存）：
- 场景: 在动手做事之前，您先看看是否之前遇到过相同或相似的问题，即检查“网上有没有相同的需求”（查询缓存）。
- 动作: MySQL会检查查询缓存，看是否有相同的SQL语句及其结果已经存储。如果有，则直接返回缓存的结果，省去后续步骤。
深入理解需求（分析器）：
- 场景: 如果没有现成的答案，您需要仔细分析上司的具体要求，确保理解无误。
- 动作: MySQL的分析器会对SQL语句进行语法解析和语义分析，确保其符合SQL规范，并理解查询的目的（比如要查询哪些表、列，以及条件是什么）。
策划解决方案（优化器）：
- 场景: 明确需求后，您思考多种完成任务的方式，从中选出最高效的方法。
- 动作: MySQL的优化器会基于分析器提供的信息，考虑多种执行计划，评估每种计划的成本（如执行时间、所需资源等），并选择最优的执行策略。
执行并交付成果（执行器）：
- 场景: 最后一步是根据选定的方案编写代码并执行，完成后将结果反馈给上司。
- 动作: MySQL的执行器根据优化器制定的计划，调用存储引擎执行SQL语句，读取或修改数据，并将操作结果返回给用户。

存储引擎

这里一般就是三种最常见的存储引擎了：

InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性要求较高，在并发条件下要求数据的一致性，数据操作除了插入和查询外还有很多更新、删除操作，那么InnoDB是比较合适的。
MyISAM：如果应用是以读和插入操作为主，很少有更新和删除操作，并对事务完整性和并发性要求不高，那么选择这个存储引擎非常合适（已经被Mongodb所替代了）
Memory：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory缺陷就是对表的大小由限制，太大的表无法存储在内存中，而且无法保证数据安全性。(被Redis所替代了)

但是个人感觉一般面试题是围绕下面两种InnoDB和MyISAM进行对比的：

事务支持:
- InnoDB: 支持事务处理（ACID兼容），适合需要高可靠性和数据一致性的应用场景，如银行系统、电子商务等。
- MyISAM: 不支持事务处理，适合读取密集型应用，对数据一致性要求不高的场景。
行级锁与表级锁:
- InnoDB: 采用行级锁，可以更细粒度地控制并发访问，减少了锁争用，提高了并发性能，适合写操作频繁的场景。
- MyISAM: 使用表级锁，当执行写操作时会锁定整张表，导致在有写操作时其他读写操作必须等待，不适合高并发写入场景。
数据存储与索引:
- InnoDB: 数据文件和索引文件存储在一起，默认使用聚集索引（数据和主键索引存放在一起），支持外键约束，有利于提高相关查询效率。
- MyISAM: 数据文件和索引文件分开存储，不支持外键，非聚集索引（索引文件只包含索引，指向数据的指针），这使得某些查询可能效率较低。
崩溃恢复:
- InnoDB: 支持自动崩溃恢复，通过事务日志实现，即使数据库发生异常关闭，也能保证数据的一致性和完整性。
- MyISAM: 崩溃恢复能力较弱，如果数据库在写操作过程中崩溃，可能会导致数据损坏，需要手动修复。

索引

索引结构

MySQL的索引是存储引擎层实现的，不同的存储引擎其实由不同的结构。

但是我们这里只对B+Tree 索引进行来介绍，其他的还有Hash索引、R—Tree空间索引等等。

下面是一个B+树的结构：

一般这里会提出一个非常常见的问题，为什么采用的是B+树索引：

相对二叉树，层次更少，搜索效率高。
相对红黑树，因为红黑树是一种大致的平衡，会导致树的高度变高，性能下降，所以不采用这种。

（图出处JavaGuide）
对于B树，因为B树的叶子节点和非叶子节点都会存储数据，导致一页中的数据存储较少，只能通过增加树的高度来实现，但这样会降低性能，所以不推荐这个。
相对Hash索引，B+树支持范围匹配及排序操作。

索引分类

基本的索引有以下这些：

主键索引
- 针对主键创建的索引
- 默认自动创建，只有一个
- 关键字：Primary
唯一索引
- 某列中数据不能重复
- 可以有多个
- 关键字：unique
普通索引
- 快速定位特定属性数据
- 可以有多个
全文索引
- 全文索引是查找文本的关键字，而不是比较索引的值
- 可有多个
- fulltext

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

聚集索引：将数据存储和该索引放到了一块，索引结构的叶子节点保存的是行数据（必须有，且只有一个）
二级索引：数据存储与索引分块存储。索引结构的叶子节点关联的是对于主键（可以有多个）

个人理解可以将聚集索引理解成主键索引，其他索引就是二级索引。

接下来由于二级索引的叶子节点关联的是主键，而不是行数据，这里会出现一个叫回表查询的知识点（面试常问）：

前提：我们已经对name这个创建了索引。

开始我们执行查询语句：

select * from user where name ='Arm';

下面是执行过程（这个过程称为回表查询）：

我们先根据name的二级索引，查询到该索引的叶子节点中的数据——该数据的主键值
得到主键后，我们根据主键再到聚集索引中查询到该行数据。

explain执行计划

explain命令：获取MySQL如何执行Select语句执行过程中如何连接和连接顺序

比如说下面就是对一个sql语句的分析：

索引使用

这里只挑选重点讲了，可以去看我之前的文章，在“MySQL个人总结——索引”篇章中（有具体讲述（下面仅个人的理解）：

最左前缀法则

最左前缀法则：查询从索引建立时候的最左列开始，并且不跳过索引的列。当跳过一列的时候，索引将部分失效（后面字段的索引失效）

索引失效情况

范围索引：联合索引中，出现范围查询（>或<)，范围查询右侧的列索引失效。
- 如果使用>= 或 <= 的话，索引还是会存在。
索引列上进行运算操作
字符串类型字段不加引号
头部采用模糊匹配
- 类似这种语句 like %adf
or连接的时候，只有两端都有索引，索引才生效
使用索引比全表查询慢，不采用索引

覆盖索引

覆盖索引是什么：查询使用了索引，并且需要返回的列，在索引中已经全部能找到。

为什么说要尽量使用索引覆盖？

因为一般采用select * 的话，容易出现回表查询，导致查询效率变低

为了看看你是否理解了覆盖索引，现在提出一个思考题：

如果存在一张表，有四个字段(id, username, password, status)，由于数据量巨大，需要对下面SQL语句进行优化，该怎么进行设计才是最优方案呢？
select id,username,password from user where username = 'xxx';

回答：

我们可以通过对username和password两个字段设置联合索引实现最优方案。

因为使用了这种方案，我们可以直接进行一次的辅助索引就可以拿到username和password。一般查询的时候还会遵循最左前缀法则，一般查询的都是username，索引也不会失效。

索引设计原则

其实这个也是一个面试题，可以将这个知识点改动一下：

在工作中，怎么来合理的设计索引呢？

针对数据量大的、且查询比较频繁的表建立索引。

针对常作为查询条件（where）、order by（group by）操作的字段建立索引

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

如果字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引

尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率

如果索引列不能存储NULL值，在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，可以更好的确定哪个索引最有效的用于查询

索引下推

这是一个补充的知识点（纯个人理解）：

就是在执行二级索引的时候，同时MySQL执行SQL语句中的条件判断，将一些不符合条件的情况剔除，最后进行回表查询的时候，会减少一些不必要的数据，使得效率更高。

二级索引查询过程：当使用二级索引（非聚簇索引，通常索引非主键列）进行查询时，MySQL首先通过索引找到满足索引列条件的记录，然后根据这些记录的主键值（或聚簇索引键）去聚簇索引中查找完整的行数据，这个过程被称为“回表查询”。

索引下推优化：在没有索引下推的情况下，MySQL先通过二级索引定位到所有匹配索引列的行，然后再对这些行进行条件过滤（那些不在WHERE子句中的其他条件）。而启用索引下推后，MySQL能够在扫描二级索引的过程中就对WHERE子句中的其他条件进行判断，直接在索引层就筛选掉不满足条件的记录，减少需要回表查询的行数。

效率提升：通过这种方式，索引下推显著减少了需要从二级索引返回并进行回表查询的记录数量，从而减少了磁盘I/O操作，提高了查询效率，尤其是在那些二级索引列匹配很多，但经过进一步条件过滤后实际符合条件的记录较少的场景中效果尤为明显。

SQL优化

SQL优化，实际上就是对索引的合理使用，发挥出优势。

这里直接用一张图总结了，如果要深度了解的可以看看之前写的“MySQL进阶---SQL优化”

插入数据

insert：批量插入、手动控制事务、主键顺序插入、大批量插入：load data local infile 主键优化

主键长度尽量短，顺序插入 AUTO_INCREMENT UUID

order by优化

using index：直接通过索引返回数据，性能高 using filesort：需要将返回的结果在排序缓冲区排序 gourp by 索引，多字段分组满足最左前缀法则

limit优化覆盖索引 + 子查询

count优化： count(*) =count(1) > count(主键) > count(字段)

update优化尽量根据主键/索引字段进行数据进行更新

锁

作用：保证数据一致性、完整性，提供并发安全、控制访问顺序。

全局锁

先说说我对这个理解吧，下面是简单讲述：

全局锁就是将整个数据库锁起来，只能进行读取操作。一般使用在全局备份的情况，当备份的时候，创建一个当前数据的视图（类似隔离级别中的可重复读），然后将这个视图中的数据进行备份

全局锁的概念

全局锁作用于整个数据库实例的一个锁，它限制了对数据库的写入操作。

当全局锁生效时，所有需要修改数据的事务或语句（如INSERT、UPDATE、DELETE）都会被阻塞，直到锁释放。这意味着全局锁的主要目的是为了在特定操作期间保护数据库的一致性状态，而不仅仅是为了支持读取。

使用场景

全局锁最典型的使用场景之一是数据库备份，尤其是逻辑备份（例如使用mysqldump工具）。逻辑备份过程中，为了保证备份数据的一致性，通常需要在备份开始前锁定整个数据库，防止备份过程中数据发生变化。

FLUSH TABLES WITH READ LOCK (FTWRL)

在MySQL中，实现全局锁的一种常见命令是FLUSH TABLES WITH READ LOCK (FTWRL)。这个命令的作用是：

关闭所有打开的表，这会强制执行任何未提交的事务，确保所有表处于一致状态。
对整个数据库加读锁，阻止新的写操作，但允许已有的读操作继续。

使用FTWRL进行备份的流程大致如下：

执行FLUSH TABLES WITH READ LOCK获取全局读锁。
启动备份进程（比如运行mysqldump）。
备份完成后，释放锁（通过执行UNLOCK TABLES）。

表级锁

这是我对表级锁的理解：

表级锁分为表锁和元数据锁；表锁的话，就是直接锁主了一张表；元数据锁的话，就是当查询的时候，可以默认的加上元数据锁，对于读操作可以任意的进行，但是当要进行对表结构修改的时候，要等到之前读取的所有事务全部关闭的时候，才可以拿到元数据锁，同时，在修改表结构的这个事务之后的请求，都要等到这个修改的事务结束

表级锁

作用于整个表的锁。MySQL中的MyISAM存储引擎主要使用这种类型的锁。

表锁有两种模式：

读锁（Shared Locks / S-Locks）：允许其他事务读取被锁定的表，但会阻止任何写操作（包括更新、插入、删除）。
写锁（Exclusive Locks / X-Locks）：独占访问，不仅阻止其他事务写入，也阻止其他事务读取。当一个事务获取了表的写锁，其他事务既不能读也不能写该表。

缺点：粒度较大，可能导致并发性能下降，尤其是在高并发读写场景下

元数据锁（MDL，Metedata Locks）

元数据锁是MySQL为保护表的元数据（即表的结构定义，如列定义、索引等）而引入的一种锁机制，适用于所有存储引擎。

主要用途：

防止并发的DDL操作（如ALTER TABLE、DROP TABLE）相互冲突，确保数据定义的一致性。
保护查询的一致性，确保查询看到的数据结构不会在查询执行过程中被DDL操作改变。

这里是一个例子：

session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是 MDL读锁，因此可以正常执行。
之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。
如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。

总结：事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

意向锁

这是一个补充的内容。

前提介绍：意向锁是在事务准备获取表级锁或行级锁时加入的，它们并不是实际的锁，而是一种锁定意图的标识，用于帮助协调并发事务之间的锁定操作。

原理

举一个例子，说说意向锁的原理：

当事务A想要对表中的任意行加行级锁（共享锁S或排他锁X）时，InnoDB会自动在表级别加上意向锁（如果是读操作，则加意向共享锁IS；如果是写操作，则加意向排他锁IX）。
如果事务B随后尝试对整个表加表锁（X锁），它不需要检查每行的具体行锁状态，只需要查看表上的意向锁。如果发现表上有意向排他锁IX，事务B就知道有其他事务打算或已经在表中的某些行上执行写操作，此时事务B将被阻塞，等待所有持有意向排他锁的事务完成。

这个机制有效地避免了事务B需要逐一检查表中每一行的锁状态，从而提高了并发处理的效率。意向锁作为表级的信号锁，提供了足够的信息来快速决定是否可以安全地授予表锁，而无需深入到行级细节的检查。

什么时候会给表加上意向锁呢？

意向锁是在事务试图获取行级锁之前，由数据库系统自动在表级别加上的一种轻量级锁。它的主要目的是作为行锁的一个前置信号，告诉其他事务该表内有行可能即将被锁定，或者已经被锁定，从而帮助快速判断是否可能存在锁冲突，特别是在有事务尝试对整个表进行加锁操作时。

行级锁

在此之前，我先要介绍一下两阶段锁协议，这里通过举例说明：

当一个A事务进行对id为1，2 的行数据进行修改的时候，分别对两个数据加上了行锁，只有等到事务结束后才会释放两个行级锁，而不是不使用时将锁释放

等同下面：

加锁阶段：事务开始后，当需要访问某个数据项时，会先请求并获取相应的锁（读锁或写锁）。在这个阶段，事务可以根据需要不断申请锁，但不会释放任何已经获得的锁。
解锁阶段：事务只有在所有操作完成，准备提交时，才会进入第二阶段，开始释放所有之前获取的锁。这个阶段是不可逆的，一旦开始释放锁，事务就不能再申请新的锁。

死锁

由于我们知道：当一个事务不会在开始的时候自动对所有要操作的行数据加上行锁，而是动态的按需进行。所以，就有可能出现死锁情况，下面举例说明死锁：

事务A锁定了表中的一行记录R1，准备更新。
事务B锁定了表中的另一行记录R2，也准备更新。
事务A接下来尝试锁定事务B已经锁定的记录R2，因为需要执行某个逻辑或保持数据一致性。
事务B同时尝试锁定事务A已经锁定的记录R1，原因类似。
这时，事务A在等待事务B释放R2的锁，而事务B在等待事务A释放R1的锁，形成了相互等待的循环，即死锁。

有两种策略可以解决死锁：

设置进入等待超时时间
- 通过参数：innodb_lock_wait_timeout来设置。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
- 将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

一般采用的是第二种策略：主动检测死锁。

其实这里还是会出现一个问题：就是说如果100万个并发线程，要同时更新一行数据，在执行的时候，每个新来的线程都要进行死锁检测，这样会消耗大量的CPU资源，但是却执行不了不多的事务，我们该怎么解决这个问题呢？

问题的核心就是：对并发的控制。
解决思路：对相同行的更新，在进入引擎前排队
具体实现：利用消息队列（Message Queue, MQ）来实现对相同行更新请求的排队是一个有效的方法，可以显著减少数据库层面的压力，避免高并发导致的死锁检测开销

行锁

作用：操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低

InnoDB引擎

逻辑存储结构

表空间（ibd文件）：一个MySQL实例可以对应多个表空间，用于存储记录、索引等数据。
段：
1. 分为：数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。
2. InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为 B+树的非叶子节点，段用来管理多个Extent（区）
区：表空间单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中共有64个连续的页。
页：是InnoDB存储引擎磁盘管理的最小单位，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。
行：InnoDB存储引擎数据是按行进行存放的。
1. Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。（最后一次操作时id）
2. Roll_pointer：每次对某条记录进行改动时，都会把旧的版本写入undo日志中，然后这个隐藏列就相当一个指针，可以通过它来找到该记录修改前的信息。（通过这个指针可以改动前的数据）

事务原理

特性：AICD

原子性（Atomicity）：事务是不可分割的最小操作单元。

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

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

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。

事务隔离级别

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non- repeatable read）、幻读（phantomread）的问题，为了解决这些问题，就有了“隔离级别”的概念。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

用视图的方式说：

在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。
这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
“串行化”隔离级别下直接用加锁的方式来避免并行访问。

redo log（重做日志，实现持久性）

用户提交修改，到Buffer Pool中，并将数据页的变化写入到Redolog Buffer中。（当前页不会立即进行刷新，而是有规律时间的刷新。前面讲过，如果提交到Buffer Pool后，页变成脏页了）
Buffer Pool将内存中的信息存入磁盘中，先将Redolog 信息先写入，然后再是Buffer Pool中的信息
1. 顺利进行，插入成功。
2. 如果失败，就要用到Redolog中的保留的日志了，通过日志重新写入数据。
系统会过期清理不需要的在磁盘上的redolog文件。

undo log（回滚日志，实现原子性）

两个作用：

回滚
MVCC

就是当用户进行一个delete记录时，undo log中会多出一条之前行数据的记录，用于进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。

MVCC

了解三个基本概念：

当前读：每次读取的都是当前数据库中的最新版本数据
快照读：读取是某一个视图中的数据记录，可能是之前事务开启的那个版本的
MVCC：维护一个数据的多个版本，使得在读写操作时没有冲突

在MVCC中，我们要搞清楚下面这些概念

undo log：insert、update、delete的时候产生的便于数据回滚的日志
undo log 版本链：不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧数据，尾部是最早的旧数据。
readView：是快照读SQL执行时候创建的一个视图，记录并维护当前活跃的事务id（也可以说是未提交事务的id）

MVCC执行过程：

事务开始：当一个事务开始时，InnoDB会为该事务分配一个唯一的事务ID（transaction ID），这个ID在事务的整个生命周期中保持不变，是MVCC机制中的重要标识。
数据读取：
- 快照读：在可重复读（Repeatable Read）隔离级别下，默认的SELECT查询采用快照读，即事务看到的是事务开始时数据库状态的一个快照。InnoDB通过read view（读视图）来确定事务能看到哪些数据版本。read view包含了当时活跃事务的ID列表，事务只能看到这些ID之前提交的版本。（就是说：仅在事务第一次执行快照时生成一个ReadView，之后都是复用这个ReadView）
- 当前读：对于需要读取最新数据的查询（如SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE），会执行当前读，直接读取最新已提交的数据版本。（一般是RC隔离级别下，在事务中每次执行快照读都会生成ReadView）
数据写入：
- 当事务执行INSERT、UPDATE或DELETE操作时，InnoDB并不会直接修改原数据，而是：
  - 为修改前的数据生成undo log记录，包含旧数据的完整信息，用于事务回滚和历史版本查询。
  - 在数据页中插入新的数据版本，并标记旧版本为已删除，但实际并未立即物理删除，而是等待purge线程清理。
  - 更新数据的隐藏列（如row_id、事务ID、删除标记等），以指向新版本和关联undo log。
事务提交与回滚：
- 提交事务时，InnoDB会记录事务的提交ID，但已修改的数据版本并不会立即清理，以维持 MVCC 的多个版本。
- 回滚事务时，使用undo log恢复数据到事务开始前的状态。
版本清理：
- Purge线程会周期性地检查数据页，根据当前活跃事务的read view和已提交事务的信息，清理不再需要的旧版本数据和对应的undo log，以回收空间。