当前位置：首页 > news >正文

MySQL深入原理

news 2024/11/17 0:42:01

MySQL深入原理

索引、事务、日志原理、InnoDB引擎、缓存、锁

有4个数据库是属于MySQL自带的系统数据库：

mysql
MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等
information_schema
MySQL 系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。
performance_schema
MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
sys
MySQL 系统自带的数据库，这个数据库主要是通过视图的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

逻辑架构：

MySQL 的架构共分为两层：Server 层和存储引擎层

Server 层：

负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接池，执行器、优化器、解析器、预处理器、查询缓存等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现。

存储引擎层：
负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的。

SELECT 语句执行原理

连接器
当我们通过客户端访问 MySQL 服务器前，要做的第一步就是需要先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的

TCP 网络连接建立成功后，服务端与客户端之间会建立一个 session 会话，紧接着会对登录的用户名和密码进行效验，首先会查询自身的用户表信息，判断输入的用户名是否存在，如果存在则会判断输入的密码是否正确。密码正确后，会从连接池中分配一条空闲线程维护当前客户端的连接；如果没有空闲线程，则会创建一条新的工作线程。之后线程会查询用户所拥有的权限，并对其授权，后续 SQL 执行时，都会先判断是否具备相应的权限。

空闲连接在超过最大空闲时长（wait_timeout）之后，连接器会自动将它断开。

一个处于空闲状态的连接被服务端主动断开后，客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到报错。

连接池
Connection Pool，是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请、使用、释放。主要是为了复用线程、管理线程以及限制最大连接数。

当一个客户端尝试与 MySQL 建立连接时，MySQL 内部都会派发一条线程负责处理该客户端接下来的所有工作。

线程的频繁创建和销毁都会耗费大量资源，通过复用线程的方式，不仅能减少开销，还能避免内存溢出等问题。

数据库连接池可以设置最小连接数和最大连接数：

最小连接数：是连接池一直保持的数据库连接，如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费
最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中

查询缓存
如果查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句的哈希值，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

查询缓存往往弊大于利，因为只要有对表的更新，就会导致表上的所有查询缓存被清空。所以，MySQL8.0 版本直接将查询缓存删掉了。

这里说的查询缓存是 server 层的，也就是 MySQL8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer poll。

自我总结：查询缓存在8.0版本被删掉的原因：

首先是这种机制是消耗数据库性能的
其次是，查询缓存是K - V存储的，key是SQL查询语句的哈希值，每当表有一点修改，就会导致表上的查询缓存被清空

解析 SQL
在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由解析器来完成。解析器可以将输入的 SQL 语句转换为计算机可以理解的形式（语法树，Syntax Tree）。

解析器会做如下两件事情：

词法解析：MySQL 会根据输入的字符串识别出关键字出来，构建出 SQL 语法树；
语法解析：根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法规则。

当词法分析和语法分析出错时，分析器会抛出异常。比如语法结构出错、出现了无法识别的字符等。

表或者字段不存在，并不是在分析器里做的，而是在预处理阶段完成。

执行 SQL
每条 SQL 语句主要可以分为以下这三个阶段：① prepare ，预处理阶段；② optimize ，优化阶段；③ execute ，执行阶段。

预处理器：检查 SQL 查询语句中的表或者字段是否存在；将 select 中的 .* 符号，扩展为表上的所有字段

优化器：化器会根据语法树制定多个执行计划，然后确定最优的执行计划。

在表里存在多个索引的时候，决定使用哪个索引；
在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器：判断用户权限，然后根据执行计划执行 SQL 语句。

总结一下一条查询 SQL 语句的执行流程：

客户端通过连接器连接 MySQL 服务；
连接成功后向 SQL 接口发送 SQL 语句请求；
SQL 接口接收到 SQL 查询语句会先去缓存查询，如果命中返回给客户端，否则交给解析器；
解析器在拿到 SQL 语句后会判断语法是否正确，正确会生成 SQL 语法树交给优化器，否则报错给客户端；
优化器会根据 SQL 语法树生成一个最优的执行计划交给执行器执行；
执行器拿到执行计划调用存储引擎来获取数据响应给客户端；
完成！！！

UPDATE 语句执行原理

在数据库里面，我们说的 update 操作其实包括了更新、插入和删除。如果大家有看过 MyBatis 的源码，应该知道 Executor 里面也只有 doQuery() 和 doUpdate() 的方法，没有 doDelete() 和 doInsert()。

缓冲池
首先，InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool.

下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB 里面有专门的后台线程把 BufferPool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

BufferPool 是 InnoDB 里面非常重要的一个结构，它的内部又分成几块区域。

InnoDB 内存结构和磁盘结构

BufferPool 主要分为3个部分：Buffer Pool、Change Buffer、AdaptiveHash Index ，另外还有一个**（redo）logbuffer**。

BufferPool
BufferPool 缓存的是页面信息，包括数据页、索引页。查看服务器状态，里面有很多跟 BufferPool 相关的信息：

内存的缓冲池写满了怎么办？InnoDB 用 LRU（Least Recently Used）算法来管理缓冲池（链表实现，不是传统的 LRU，分成了Younf 和 Old），经过淘汰后的数据就是热点数据。

内存缓冲区对于提升读写性能有很大的作用。思考一个问题：当需要更新一个数据页时，如果数据页在 BufferPool 中存在，那么就直接更新好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，有没有优化的方式呢？

ChangeBuffer
如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。

这一块区域就是 ChangeBuffer。5.5 之前叫 InsertBuffer 插入缓冲，现在也能支持 Delete 和 Update。

最后把 ChangeBuffer 记录到数据页的操作叫做 merge。什么时候发生 merge？有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库 shutdown、redolog 写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 ChangeBuffer（写缓冲）。写多读少的业务，调大这个值：

SHOW VARIABLES LIKE ‘innodb_change_buffer_max_size’;
代表 ChangeBuffer 占 BufferPool 的比例，默认 25%。

Log Buffer
思考一个问题：如果 BufferPool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。

为了避免这个问题，InnoDB 把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现 crash-safe）——用它来实现事务的持久性。

这个文件就是磁盘的 Redo Log（叫做重做日志），对应于 /var/lib/mysql/ 目录下的 ib_logfile0 和 ib_logfile1，每个 48M。

这种日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

问题：同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？

我们先来了解一下随机 I/O 和顺序 I/O 的概念：磁盘的最小组成单元是扇区，通常是 512 个字节。操作系统和磁盘打交道，读写磁盘，最小的单位是块 Block。

如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，依次进行此过程直到找完所有数据，这个就是随机 IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序 IO。

刷盘（将内存中的数据写入磁盘）是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。

当然 Redo Log 也不是每一次都直接写入磁盘，在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，认 16M，它一样可以节省磁盘 IO.

需要注意：Redo Log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 bufferpool。Redo Log 写入磁盘，不是写入数据文件。那么，Log Buffer 什么时候写入 log file？在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓冲区写入到磁盘。

Redo Log 的特点：

Redo Log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有；
不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志；
Redo Log 的大小是固定的，前面的内容会被覆盖。
除了 Redo Log之外，还有一个跟修改有关的日志，叫做 Undo Log（撤销日志或回滚日志），记录了事务发生之前的数据状态，分为 insert Undo Log 和 update Undo Log。如果修改数据时出现异常，可以用 Undo Log 来实现回滚操作（保持原子性）。

总结一下一个 Update 操作的流程：

在执行前需要：① 连接器连接数据库；② 分析器通过词法分析和语法分析知道这是一条更新语句；③ 优化器决定要使用的索引等；④ 执行器负责具体的执行过程；

事务开始，从内存（buffer poll）或磁盘（data file）取到包含这条数据的数据页，返回给 Server 的执行器；

Server 的执行器修改数据页的这一行数据的值为 lizhengi；

记录 name=lisa（原值）到 Undo Log；

记录 name=lizhengi 到 Redo Log；

调用存储引擎接口，记录数据页到 buffer pool（修改 name= lizhengi）；

事务提交。

完成！！！

存储引擎：

存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

MySQL引擎是数据库的核心组件之一，决定了数据库的性能、可靠性和功能特性。

MySQL引擎的作用包括但不限于以下几个方面：

存储数据：MySQL 引擎负责将数据存储在物理存储介质上，包括硬盘或固态硬盘等。它将数据组织成表、索引等结构，并提供对这些数据的高效访问方式。
管理数据：MySQL 引擎管理数据的增删改查操作，包括插入新数据、更新已有数据、删除数据以及查询数据等操作。它负责确保数据的完整性、一致性和持久性。
提供事务支持：MySQL 引擎支持事务，可以确保一组操作要么全部成功执行，要么全部失败回滚，保证数据的一致性和可靠性。
提供并发控制：MySQL 引擎提供并发控制机制，确保多个用户可以同时访问数据库而不会相互干扰，保证数据的正确性和可靠性。
优化查询性能：MySQL 引擎负责优化查询语句，提高查询性能，包括选择合适的索引、执行查询计划优化等。

常见引擎及其区别和特点：
MySQL支持多种不同类型的存储引擎，每种引擎都有其自身的特点和适用场景。以下是 MySQL 支持的一些常见引擎及其区别和特点：

① InnoDB：

InnoDB 是 MySQL 默认的事务安全引擎，提供了对事务的支持，包括 ACID（原子性、一致性、隔离性、持久性）特性。
支持行级锁定和外键约束，适合于需要高并发和数据完整性的应用场景。
InnoDB 引擎使用聚簇索引，将数据和索引存储在同一个B树结构中，提高了查询性能。

② MyISAM：

MyISAM 是 MySQL 的另一种常见引擎，不支持事务和行级锁定。
MyISAM 引擎适合于读操作频繁、写操作较少的应用场景，如数据仓库、日志分析等。
支持全文索引和压缩表格，对于特定类型的查询有一定的优势。

事务支持：InnoDB 和 NDB Cluster 支持事务，而 MyISAM 和 MEMORY等引擎不支持。
锁定机制：InnoDB 支持行级锁定，而 MyISAM 通常使用表级锁定。
存储引擎特性：不同引擎支持的特性不同，如全文索引、压缩表格、分布式存储等。
性能和可靠性：不同引擎在性能、可靠性和适用场景上有所差异，根据具体需求选择合适的引擎。
在选择 MySQL 引擎时，需要根据应用场景和需求综合考虑各种因素，包括事务需求、性能要求、可靠性需求等，以选择最适合的引擎。

InnoDB 与 MyISAM 存储引擎之间的比较

事务的支持
InnoDB 支持 ACID 的事务处理，MyISAM 并不支持事务，这里需要注意，如果我们在程序需要进行事务处理，所有的数据都要使用 Innodb 存储引擎，如果中间有 MyISAM 存储引擎的数据表，如果进行事务回滚，MyISAM 并不会回滚到之前的状态，因此 MyISAM 不支持事务。

索引与主键处理
InnoDB 存储引擎使用的是聚集索引，InnoDB 主键的叶子节点是该行的数据，而其他索引则指向主键，而 MyISAM 存储引擎使用的是非聚集索引，主键与其他索引的叶子节点都存储了指向数据的指针。

另外一个是 MyISAM 数据表允许没有主键和其他索引，而 InnoDB 数据表如果没有主键的话，而会生成一个用户不可见6字节的主键。

外键
MyISAM 不支持外键，而 Innodb 则支持建立数据表之间的外键关联。

存储文件的不同
Innodb 存储文件有 frm、ibd，而 MyISAM 是 frm、MYD、MYI，Innodb 存储文件中 frm 是数据表结构定义文件，ibd 是数据文件，MyISAM 中 frm 是数据表结构定义文件，MYD 是数据的文件，MYI 则是存储索引的文件。

select count()
使用 MyISAM 存储引擎的数据表会记录一个数据表的总行数，所以对使用 MyISAM 存储引擎的数据表进行 select count()，可以很快得到一个数据表的总行数，而对于 InnoDB 存储引擎的数据表，想要查询总行数需要进行全表扫描才能得到。

锁的级别
InnoDB 支持行级锁，而 MyISAM 只支持表级锁，因此 InnoDB 更能支持高并发。

索引：

索引目的：

索引的目的在于减少磁盘IO次数，提高查询效率，可以类比字典，比如当我们要查 “mysql” 这个单词，我们肯定需要定位到 ‘m’ 字母，然后从下往下找到 ‘y’ 字母，再找到剩下的 “sql”。如果没有索引，那么我们可能需要把所有单词看一遍才能找到想要的。

在 MySQL 中，索引是一种帮助存储引擎快速获取数据的数据结构，形象的说就是索引是数据的目录。它一般是以包含索引键值和一个指向索引键值对应数据记录物理地址的指针的节点的集合的清单的形式存在。通过使用索引， MySQL 可以在不需要扫描整个表的情况下快速找到与查询条件匹配的记录。

索引原理:

在 MySQL 索引设计中，核心目标是有效平衡数据检索的速度与存储效率。就像图书目录帮助我们快速找到特定章节，MySQL索引使数据库能迅速定位数据。但数据库的挑战更为复杂，因为它需要处理各种查询类型，如等值查询、范围查询和模糊查询等。

为了有效平衡数据检索速度与存储效率，MySQL 通过其 InnoDB 存储引擎采取了以下具体措施：

B+ 树索引结构：① 高效范围查询：InnoDB 索引使用 B+树数据结构，其平衡树特性保证了即使在大量数据中也能保持较低的查询深度，特别是对于范围查询，可以快速通过叶节点链表遍历相关数据；② 优化读写性能：B+ 树的结构减少了节点分裂的频率，保持了树的平衡，从而提高了读写操作的效率；

聚簇索引设计：① 直接存储数据：InnoDB 使用聚簇索引，其中表数据直接存储在索引的叶节点上。这意味着数据物理顺序与键值顺序一致，优化了顺序访问的性能；② 减少I/O操作：通过聚簇索引，查询主键时直接定位到数据，无需额外的数据指针跳转，从而减少了磁盘 I/O 操作；

数据页及预读机制：① 数据页单位操作：InnoDB 以数据页为基本的 I/O单位（默认 16 KB），这比单条记录的读写更高效，因为一次 I/O 可以加载多条记录到内存；② 预读优化：利用操作系统的预读特性，InnoDB 预测并提前加载可能访问的数据页到内存，减少了未来的I/O需求，尤其在顺序访问模式下效果显著；

自适应哈希索引：内存级索引加速：当某些数据页被频繁访问时，InnoDB 会在内存中自动构建哈希索引来加速这些数据页的访问，进一步减少了数据查找时间；
写入缓冲与日志：写入合并：使用写入前日志（Write-Ahead Logging, WAL）和更改缓冲区（Change Buffer）技术， InnoDB 能够合并多个写入操作，减少对磁盘的直接写入，优化了写操作的性能。

MySQL 通过上述措施，在保证数据检索速度的同时，优化存储效率和减少磁盘I/O成本，实现了数据管理的高效平衡。这些技术不仅提高了查询性能，也保证了数据的安全性和一致性。

MySQL 索引的数据结构:

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

B+ 树索引

MySQL 索引的实现采用的是 B+ 树，B+ 树是 B- 树的变体，也是一棵多路搜索树。B+ 树相较于 B- 树最主要的特点是：数据只出现在叶子节点；所有叶子节点增加了一个链指针。

在 B+ Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+ 树的高度。

B+ 树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得 B+ 树非常适合做范围查询。

默认情况下，如果不指定索引类型，MySQL 将创建 B+ 树索引。下面显示了基于表的存储引擎允许的索引类型：

存储引擎允许的索引类型
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH, BTREE

为什么是 B+ 树？

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构呢？

MySQL 选择使用 B+树作为索引结构，主要是因为 B+树提供了许多适合数据库索引的优点：

高效的查找和范围查询性能：B+树的结构使得查找操作非常高效。所有的叶节点都按键值的顺序存储，并且相互链接，这使得对于范围查询（如找出所有在某个值范围内的记录）特别高效。
节省磁盘空间：在 B+树中，只有叶节点包含数据指针或实际的数据值，而内部节点只存储键值。这样的设计减少了内部节点所需的空间，使得更多的键值可以存储在一个节点中，从而减少了磁盘I/O次数。
优化磁盘I/O操作：数据库系统常常运行在存储数据的磁盘驱动器上。B+树的结构减少了节点分裂的频率，并且由于叶节点是顺序访问的，所以它们特别适合磁盘的顺序读取特性。
更好的缓存利用性：由于内部节点不包含实际数据，而只包含键值，这意味着更多的键值可以被缓存在内存中，从而减少访问磁盘的需要。
支持顺序和随机访问：B+树通过其叶节点的链表结构支持高效的顺序访问，同时也支持随机数据访问。
写操作的性能：B+树减少了因插入或删除操作而导致的树重新平衡的频率，这在频繁更新的数据库环境中是一个重要的优势。
2.2.1、B+Tree vs Hash
Hash 在做等值查询的时候效率很高，搜索时间复杂度为 O(1)。但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

并且如果二叉树受插入顺序影响特殊化为一个链表，相当于全表扫描。

平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。但是平衡二叉树可是每个节点只存储一个键值和数据的，并且每次新增数据，平衡二叉树都会进行大量的平衡判断。

B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双向链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

MySQL 聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据 ；

聚簇索引列的选择

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

非聚簇索引和二级索引

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

因此，如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。

其他索引的分类

索引的分类，可以根据角度的不同来分，在前面的内容，我们已经了解到了按 “数据结构（Hash 索引、B+ Tree 索引）” 以及按 “物理存储（聚合索引、二级索引）” 两种角度的索引分类，那么MySQL 中还存在着哪些形式的索引呢。

按字段特性分类
从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引
主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

联合索引（按字段个数分类）

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；
通过将多个字段组合成一个索引，该索引就被称为联合索引。
联合索引范围查询：

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

最左前缀匹配原则：在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。比如我们配置了一个 A、B、C 三个字段的联合索引，我们用 A、AB、ABC 的方式都是可以走到联合索引的，但如果是 AC、BC、C 的这种情况则不会使用索引。

索引的失效

在有些时候因为使用上的一些瑕疵就会导致索引的失效，无法达到我们使用索引的预期效果，下面介绍几种索引失效的场景：

列于列的对比：例如：某个表中，有两列 id 和 c_id 都建了单独索引，Where 条件后为 id=c_id，这种情况会被认为还不如走全表扫描；
存在 Null 值条件：如果索引列是可空的，是不会给其建索引的；
存在 Not 条件：当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描;
Like 通配符：前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走 INDEX RANGE SCAN。所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符；
条件上包括函数：查询条件上尽量不要对索引列使用函数，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引；
复合索引前导列区分大：当复合索引前导列区分小的时候，我们有 INDEX SKIP SCAN，当前导列区分度大，且查后导列的时候，前导列的分裂会非常耗资源，执行计划想，还不如全表扫描来的快，然后就索引失效了；
数据类型的转换：当查询条件存在隐式转换时，索引会失效。比如在数据库里 id 存的 number 类型，但是在查询时，却用了下面的形式：select * from sunyang where id=‘123’;
使用非最左前缀查询：若索引是多列的（比如(a, b, c)），查询条件不是以索引的最左列开始，则索引可能不会被使用。例如，若只有b或c作为查询条件，而没有a，则索引可能不生效。

索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能差生障碍，设计高效的索引需要遵循一些原则和最佳实践，以确保查询性能的同时避免不必要的资源消耗。以下是索引设计的一些核心原则：

最左前缀原则：对于复合索引，确保查询条件能够利用索引的“最左前缀”。这意味着，查询条件应该从复合索引的第一个字段开始匹配，并且按照索引字段的顺序进行；
选择性原则：优先为具有高选择性的列创建索引。选择性是指列中唯一值的比例，唯一值越多的列（接近列的总行数），选择性越高，作为索引时效果越好；
避免冗余和重复索引：检查并避免创建冗余（完全相同的索引）或重复（一个索引是另一个索引前缀的）索引，因为这会增加额外的维护成本和空间消耗，而不会带来任何查询性能的提升；
考虑查询模式：根据应用的查询模式（如等值查询、范围查询、排序、分组等）设计索引。例如，如果经常根据某个字段排序，可以考虑在该字段上创建索引；
控制索引数量：虽然索引可以提高查询性能，但每个额外的索引都会增加写操作（INSERT、UPDATE、DELETE）的成本。因此，应该避免在低选择性的列上创建索引，同时根据实际需要合理控制索引的总数量；
考虑索引覆盖：如果一个查询可以通过访问索引就能获取所需的全部数据，那么这个索引被称为“覆盖索引”。设计时尽可能让索引覆盖更多查询，可以显著减少对磁盘的访问次数，提高查询效率；
使用前缀索引以节约空间：对于长文本字段，可以考虑使用字段的前缀作为索引。前缀索引可以节省索引空间，降低维护成本，但需要权衡前缀长度和查询效率；
索引维护：定期维护索引，包括重建或优化索引，以确保索引结构的效率。随着数据的增加和变化，索引可能会变得碎片化。
遵循这些设计原则可以帮助开发者和数据库管理员创建出既能满足查询需求又能优化性能的索引策略。正确的索引策略能够在提升查询性能和控制资源消耗之间找到一个平衡点。

InnoDB数据存储结构：

MySQL 数据页结构

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

数据页包括七个部分：

文件头（File Header）：表示页的信息；
页头（Page Header）：表示页的状态信息；
最小（Infimum）、最大记录（supermum）：两个虚拟的伪纪录，分别表示页中的最小记录和最大记录；
用户记录（User Records）：存储行记录内容；
空闲空间（Free Space）：页中还没有被使用的空间；
页目录（Page Directory）：存储用户记录的相对位置，对记录起到索引作用；
文件尾（File Tailer）：校验页是否完整。
在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

User Records 如何组织数据:

数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。

那 InnoDB 是如何给记录创建页目录的呢？

页目录创建的过程如下：

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为 “已删除” 的记录；
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。
页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。
索引的创建与设计原则：
性能分析工具的使用：
索引优化与查询优化：
数据库其他调优策略：

事务：

事务基础知识：

事务是数据库区别于文件系统的重要特性之一，事务会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点，保证已提交到数据库的修改不会因为系统崩溃而丢失

事务：一组逻辑单元，使数据从一种状态转变到另一种状态

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交（commit），那么这些修改就永久地保存下来；要么数据库管理系统放弃所作的所有修改，整个事务**回滚（rollback）**到最初状态

事务ACID特性：

事务的隔离性是由锁机制实现的

事务的原子性、一致性、持久性由事务的redo日志和undo日志来保证

A：原子性

事务是不可分割的单位

C：一致性

数据从一个合法性状态到另一个合法性状态，这种状态是跟具体的业务有关

I：隔离性

事务互不干扰

D：持久性

事务提交后，它对数据库中数据的改变就是永久性的

持久性是通过事务日志来保证的

数据并发问题：

脏写：事务A修改了未提交事务B修改过的数据，但是事务A提交了，事务B就不能回滚了
脏读：事务A读取了已经被事务B更新但未提交的字段，若事务B回滚，事务A读取的内容就是临时且无效的
不可重复读：事务A读取了一个字段，然后事务B更新这个字段，事务A再去读取同一个字段，值就不同了
幻读：事务A从一个表中读取一个字段，然后事务B插入一些新的行，事务A再去读取同一个字段，就会多出几行

问题严重性：脏写》脏读》不可重复读》幻读

SQL中的四种隔离级别：

都解决了脏写问题

读未提交
读已提交
可重复读
串行化

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

MySQL事务日志：

redo log 重做日志：提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性

undo log 回滚日志：回滚行记录到某个特定版本，用来保证事务的原子性、一致性、

redo和undo都可以视为是一种恢复操作，但是：
- redo log ：记录的是物理级别上的页修改操作，比如页号xxx、偏移量yyy、写入了zzz数据。主要保证了数据的可靠性
- undo log ：记录的是逻辑操作日志，比如对某一行进行insert操作，undo log就记录相应的一条delete操作。主要用于事务的回滚（undo log 记录了每个修改操作的逆操作）和一致性非锁定读（undo log 回滚行记录到某种特定的版本–MVCC，即多版本并发控制）
redo log：

缓冲池（内存）帮我们消除CPU和磁盘之间的鸿沟，以保证整体的性能不会下降太快，所有数据先更新到缓冲池，脏页再以一定的频率刷新到磁盘(checkpoint机制)

但是如果计算机在刷新脏页的中途宕机了，可能会导致数据的丢失

InnoDB引擎的事务采用了WAL技术（Write - Ahead - Logging），先写日志，再写磁盘，只有日志写成功了，，才算事务的提交成功，当出现上述那种情况，可以通过redo log 来恢复，保证了事务的持久性

redo log特点： 顺序写入磁盘，事务的执行过程中，redo log不断记录

redo log 组成：
- 重做日志缓冲（redo logh buffer）
- 重做日志文件（redo log file）
redo logh buffer刷盘到redo log file的过程中，并不是直接刷新到磁盘中，先写入到文件系统缓存（page cache）中，真正的刷盘会交给系统自己来决定

针对这种情况，InnoDB给出的innodb_flush_log_at_trx_commit参数，该参数控制commit提交事务时，如何将redo log buffer 中的日志刷新到redo log file中，它支持三种策略：
- 设置为0：每次事务提交时不进行刷盘操作（系统默认master thread每隔1s进行一次redo log 的同步）
- 设置为1：每次事务提交时都进行刷盘操作(默认值)
- 设置为2：每次事务提交时都只把redo log buffer 内容写入page cache，不进行同步，由OS决定何时同步到磁盘文件
数值0的话，是一种折中的做法，IO效率高于1，低于2，最多丢失一秒钟内的事务

undo log：

undo log 是事务原子性的保证。

在事务中更新数据的前置操作就是写一个undo log

原子性就是事务中的从操作不可分割，只能全部成功或者什么都不做

但有时，事务执行到一半会出现一些情况，比如：遇到一些服务器、OS、甚至是断电的错误，或是程序员手动rollback

以上这些情况，我们需要把数据改回成原来的样子，称为rollback回滚，造成一种假象：彷佛什么都没有发生过

每当我们进行更新操作（insert、delete、update），都需要留一手，把相应逆操作记录下来

undo log的产生会伴随产生redo log

undo log 作用：回滚数据，MVCC

undo log 的存储结构：

回滚段与undo页：

InnoDB对undo log的管理采用段的方式，也就是回滚段（rolleback segment）。

每个回滚段记录了1024个undo log segment，在每个undo log segment中进行undo 页的申请

当事务开启时，需要写undo log就得去undo log segment找到一个空闲位置，然后申请undo页，在这个申请得来的undo页中写undo log
undo页的重用：

undo页是可重用的，事务提交后，不会立即删除undo页。因为重用，这个undo页可能混杂其他事务的undo log。

undo log在commit后，会被放在一个链表中，然后判断undo页的使用空间是否小于3/4，如果小于3/4的话，则表示当前的undo页可以被重用，那它就不会被回收
回滚段与事务：
1. 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能服务于多个事务
2. 但一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段
3. 回滚段中，事务会不断填充盘区，盘区不够会申请拓展盘区
4. 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间
5. 当事务提交时，InnoDB会做两件事：
  - 将undo log放入列表中，以供之后的purge操作
  - 判断undo log 所在的页是否可以重用，若可以，分配给下一个事务使用
事务提交后并不能马上删除undo log 和undo log 所在的页。因为可能还有其他事务需要通过undo log来得到行记录之前的版本。

事务提交后，将undo log 放入一个链表中，是否可以删除undo log 及undo log所在页，这是purge线程来判断

锁：

锁结构：trx信息（代表这个锁是哪个事务生成的）、is_waiting（当前事务是否等待）

三种并发场景：

读-读（不用理会

写-写（会发生脏写，但隔离级别已经解决

读-写（会发生脏读、不可重复读、幻读

基本就关注读-写的解决方案：

读操作（MVCC），写操作（加锁，因为改必须是最新数据）

采用MVCC方式的话，读写操作彼此不冲突，性能更高
读和写操作（都加锁）

采用加锁的方式，读写操作彼此需要排队执行，影响性能

一般情况，我们喜欢采用MVCC来解决读写操作的问题，但有些业务在某些特殊情况下，要求必须加锁执行

读操作可以加共享锁或排它锁

写操作必须只能加排它锁 fvfff

表级锁：

表锁会锁定整张表，并不依赖存储引擎，表锁的开销是最小的(因为粒度比较大)，但并发能力大打折扣。

表锁一次锁一张表，可以很好的避免死锁

表级别的S锁、X锁：

进行curd操作时InnoDB是不会去加表锁的，在进行alter table、drop table这类DDL语句时会在server层加元数据锁（Metadata Locks，MDL），其他crud再去操作就会堵塞
意向锁：
InnoDB支持多粒度锁，它允许行锁和表锁共存，而意向锁即使其中一种表锁
1. 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存
2. 意向锁是一种不与行级锁冲突的表锁
3. 表明“某个事务正在某些行持有了锁或该事务准备去持有锁”
意向锁有两种：
1. 意向共享锁（IS）：事务有意向对表中的某些行加S锁
2. 意向排它锁（IX）：事务有意向对表中的某些行加X锁
意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁

如果我们给某行数据加上了排它锁，数据库会自动给更大的一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了
自增锁：

AUTO - INC锁是想使用含有自增列的表中插入数据时需要获取的一种特殊的表级锁
元数据锁（MDL锁）

行锁：

行锁锁住一行数据，行锁在存储引擎层实现

优点：锁定粒度小，发生锁冲突概率低，可以实现的并发度高

缺点：对于锁的开销比较大，加锁比较慢，容易出现死锁情况

记录锁
间隙锁：

gap锁的提出仅仅是为了防止插入幻影记录而提出的
临键锁：

联合记录锁和间隙锁的特征，既想锁住某条记录，又想防止其他事务在该记录前面的间隙插入新记录，InnoDB想出了Next - Key 锁

本质就是一个记录锁和一个间隙锁的合体
插入意向锁：

InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构

页锁：

页锁的粒度（开销）位于行锁和表锁之间，并发度一般

死锁:

两个事务都持有对方的锁，并且在等待对方释放，并且双方都不会释放自己的锁

产生死锁的条件：

两个或者两个以上事务
每个事务都已经持有锁并且申请新的锁
锁资源同时只能被同一个事务持有或者不兼容
事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于：两个（或以上）的session加锁的顺序不一致

如何处理死锁：

等待，直到超时（innodb_lock_wait_timeout=50s）
使用死锁检测进行死锁处理

等待超时检测死锁太过被动，InnoDB还提供了wait - for graph 算法来主动进行死锁检测，每次加锁请求无法立即满足需要并需要进入等待时，算法就会被触发

这是一种主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息

一旦检测到回路、有死锁，这时候InnoDB会选择回滚undo量最小的事务,让其他事务继续执行

如何解决：控制并发访问的数量。比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作