失眠网 > 【MySQL进阶篇】学习笔记

【MySQL进阶篇】学习笔记

时间：2022-05-23 22:00:30

相关推荐

【MySQL进阶篇】学习笔记

文章目录

MySQL进阶学习前言1、存储引擎1.1 MySQL体系结构概览1.2 存储引擎介绍1.3 常见存储引擎的特点1.3.1 InnoDB的特点1.3.2 MyISAM1.3.3 Memory1.3.4 总结 2、索引2.1 索引介绍2.2 索引的结构2.2.1 前置知识2.2.2 索引结构的种类 2.3 索引的分类2.4 索引的使用2.4.1 索引的基本使用2.4.2 验证索引的效率2.4.3 联合索引2.4.4 索引的失效情况2.4.5 SQL提示2.4.6 覆盖索引2.4.7 前缀索引2.4.8 单列索引与联合索引 2.5 索引的设计原则 3、SQL优化3.1 SQL性能分析3.2 insert优化3.2.1 普通数据插入3.2.2 大数据插入 3.3 主键优化3.3.1 数据的组织方式3.3.2 页分裂3.3.3 页合并3.3.4 主键的设计原则 3.4 order by优化3.5 group by优化3.6 limit优化3.7 count优化3.8 update优化 4、视图/存储过程/触发器4.1 视图4.1.1 视图概述4.1.2 视图的基本使用4.1.3 选项配置详解 4.2 存储过程4.2.1 存储过程概述4.2.2 存储过程的基本使用4.2.3 变量4.2.4 if4.2.5 case4.2.6 while4.2.7 repeat4.2.8 loop4.2.9 游标 4.3 存储函数4.4 触发器4.4.1 触发器概述4.4.2 触发器的基本使用 5、锁5.1 锁概述5.2 全局锁5.3 表级锁5.3.1 表锁5.3.2 元数据锁5.3.3 意向锁 5.4 行级锁5.4.1 行锁5.4.2 间隙锁&临键锁 6、InnoDB引擎6.1 innoDB概述6.2 InnoDB的架构6.2.1 内存结构6.2.2 磁盘结构6.2.3 后台线程 6.3 事务原理6.3.1 redo log6.3.2 undo log6.3.3 MVCC 7、MySQL管理7.1 MySQL系统数据库7.2 MySQL常用工具总结相关面试题

MySQL进阶学习

前言

本篇文章是对MySQL进阶篇学习所写的笔记，通过进阶篇的学习，我了解了InnoDB底层的大致结构，常见索引结构以及各种索引结构的优缺点，还有很多常见的SQL优化方法，以及如何进行SQL调优，还有一些MySQL高级知识比如触发器、存储函数，MySQL中的各种锁的特点，事务的实现原理等等，很多东西都是在学校课堂中学不到的东西，在此也十分感谢B站黑马
学习视频：MySQL从入门到精通MySQL官方文档：MySQL8

1、存储引擎

1.1 MySQL体系结构概览

连接层：最上层是一些客户端和链接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。存储层：数据存储层，主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.2 存储引擎介绍

大家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。

什么是存储引擎？

在MySQL中，存储引擎是一种负责管理表的物理存储和检索的软件组件。换言之：存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
注意：存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

存储引擎的作用是什么？

数据存储和检索：存储引擎负责将数据存储在磁盘或内存中，并且在查询时根据索引和查询条件快速检索数据。数据的读写性能：存储引擎的实现方式不同，对于数据的读写性能也有较大的影响。比如，InnoDB存储引擎支持事务处理和行级锁定，适合高并发的读写场景；而MyISAM存储引擎则适合大量读取、较少更新的应用。数据完整性：存储引擎负责维护数据的完整性，确保数据的一致性和正确性。比如，InnoDB存储引擎支持事务处理，可以保证数据的ACID属性，避免数据的丢失和不一致。存储引擎的特性：不同的存储引擎提供了不同的特性，例如MyISAM存储引擎支持全文本索引和压缩等功能，InnoDB存储引擎支持外键和行级锁定等特性，Memory存储引擎提供非常快的数据访问速度等。应用程序的可扩展性：通过选择合适的存储引擎，可以为应用程序提供更好的可扩展性。例如，InnoDB存储引擎支持集群模式，可以扩展到多台服务器，以满足不断增长的数据需求。

MySQL中常见的存储引擎有哪些？

InnoDB：InnoDB是MySQL的默认存储引擎，支持事务处理、行级锁定、外键等高级特性，适合于处理高并发、高可靠性、高扩展性的应用。MyISAM：MyISAM是MySQL早期的一种存储引擎，不支持事务处理和行级锁定，但支持全文本搜索等特性，适合于大量读取、较少更新的应用。Memory：Memory存储引擎将表数据存储在内存中，提供了非常快的数据访问速度，但不支持事务处理和持久化存储，适合于缓存、会话数据等需要快速读取的场景。Archive：Archive存储引擎适用于需要存储大量历史数据的场景，它支持高度压缩、快速插入和查询，但不支持索引、更新和删除操作。CSV：CSV存储引擎用于处理以逗号分隔的值（CSV）格式的数据，可以将CSV文件作为MySQL表来处理。

存储引擎常见操作：

# 建表时指定存储引擎# 备注：指定后该表就采用指定的存储引擎，MySQL5.5之后不指定默认就采用InnoDB存储引擎CREATE TABLE 表名(字段1 字段1类型 [ COMMENT 字段1注释 ] ,......字段n 字段n类型 [COMMENT 字段n注释 ]) ENGINE = INNODB [ COMMENT 表注释 ] ;

# 查看当前数据库支持的存储引擎show engines;

# 查看建表语句（可以通过建表语句查看该表使用的存储引擎）show create table 表名;

1.3 常见存储引擎的特点

上面我们介绍了什么是存储引擎，以及如何在建表时如何指定存储引擎，接下来我们就来介绍下来上面重点提到的三种存储引擎 InnoDB、MyISAM、Memory的特点。

1.3.1 InnoDB的特点

InnoDB：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎

特点：

支持事务：InnoDB是一种支持事务的存储引擎，它遵循ACID（原子性、一致性、隔离性、持久性）事务模型，可以保证数据的完整性和一致性。

行级锁定：InnoDB存储引擎支持行级锁定，它可以让多个事务同时对同一表进行读操作，而不会产生锁冲突，提高了系统的并发性能。

外键约束：InnoDB存储引擎支持外键约束，可以通过定义外键关系，保证表之间的数据完整性。

高可靠性：InnoDB存储引擎采用了多版本并发控制（MVCC）机制，可以保证数据的一致性和可靠性。同时，它还提供了自动崩溃恢复和故障转移等功能，保证了系统的高可用性。

支持热备份：InnoDB存储引擎支持在线热备份，可以在系统运行期间进行备份操作，而不会对系统产生影响。

高性能：InnoDB存储引擎采用了一系列优化技术，例如缓冲池、自适应哈希索引等，可以提高系统的查询性能和响应速度。

存储文件：使用InnoDB的表都是以xxx.ibd的形式存储在磁盘，xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（ frm 早期的、8.0后都是 sdi ）、数据和索引

备注：可以在C:\ProgramData\MySQL\MySQL Server 8.0\Data\（这是我C盘下MySQL的存储路径）下的表中进行查看，比如我打开该目录下的javaee_test表，会看到tb_user.ibd这张表结构，在当前目录下打开 cmd 窗口，然后能够输入ibd2sdi tb_user.ibd指令，就能够查看该表的表结构了

# 查看MySQL是否开启 innodb_file_per_tableshow variables like 'innodb_file_per_table';

innodb_file_per_table是MySQL的一个配置选项，用于控制InnoDB存储引擎中表的数据和索引是否存储在单独的表空间文件中。8.0版本的MySQL默认都是配置为ON的，即InnoDB存储引擎为每个表单独创建一个表空间文件，每个文件包含该表的数据和索引，这样做有一下几点好处：

可以更好地管理和维护数据库：单独的表空间文件可以方便地备份、恢复、移动和删除。改善性能：单独的表空间文件可以减小共享表空间的大小，提高数据库的性能。更好的碎片整理和优化：当删除或更新数据时，单独的表空间文件可以更好地整理和优化碎片

注意：启用innodb_file_per_table选项会增加磁盘空间的使用，因为每个表都需要一个单独的表空间文件。因此，在设置此选项时需要仔细考虑系统的磁盘空间和性能需求。innodb_file_per_table的关闭是OFF，如果将其设置为ON、1、YES、TRUE、ENABLED等等，都表示启用该选项

逻辑存储结构

表空间: InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个 Segment 段。段: 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。区: 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为 16K，即一个区中一共有64个连续的页。页: 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。行: InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

1.3.2 MyISAM

MyISAM：是MySQL早期的默认存储引擎特点：简单快速：MyISAM存储引擎的数据文件和索引文件是分开的，数据文件只保存数据，索引文件只保存索引，因此查询速度快。不支持事务：MyISAM存储引擎不支持事务处理，也不支持外键约束，因此对于需要大量的插入、更新和查询操作，但对数据的一致性要求不是很高的应用，使用MyISAM存储引擎可以获得更好的性能。容易损坏：MyISAM存储引擎在遇到硬件故障或操作系统崩溃等问题时容易出现损坏，且难以修复，因此需要定期进行数据备份。支持全文索引：MyISAM存储引擎支持全文索引，可以用于快速的文本搜索。支持压缩表格：MyISAM存储引擎支持压缩表格，可以显著减小数据文件的大小，从而节省磁盘空间。存储文件：xxx.sdi：存储表结构信息，xxx.MYD: 存储数据，xxx.MYI: 存储索引

1.3.3 Memory

Memory：Memory引擎也称（Heap）的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

特点：

数据存储在内存中：Memory存储引擎的数据是存储在内存中的，因此读取和写入数据的速度非常快，特别适合于需要高速读写的应用。不支持持久化：由于数据存储在内存中，因此在MySQL服务器关闭或崩溃时，数据将会丢失，因此Memory存储引擎不支持数据的持久化，需要定期备份数据。不支持BLOB和TEXT字段：Memory存储引擎不支持BLOB和TEXT字段，因此对于需要存储大文本或二进制数据的应用，不适合使用Memory存储引擎。不支持事务：Memory存储引擎不支持事务，因此不能使用START TRANSACTION、COMMIT或ROLLBACK等语句。不支持外键：Memory存储引擎不支持外键约束，因此需要应用程序自行确保数据的一致性。

存储文件：xxx.sdi，存储表结构信息

1.3.4 总结

三者的比较：

InnoDB和MyISAM的区别

事务支持：InnoDB支持事务处理，可以使用ACID特性确保数据的一致性和可靠性，而MyISAM不支持事务处理。锁级别：InnoDB采用行级锁，可以在并发访问时避免锁冲突，提高并发性能；而MyISAM采用表级锁，只能对整个表进行加锁，当多个用户并发访问时容易出现锁冲突。外键约束：InnoDB支持外键约束，可以确保数据的完整性，而MyISAM不支持外键约束。全文索引：MyISAM支持全文索引，可以用于快速的文本搜索，而InnoDB在MySQL 5.6版本之前不支持全文索引。索引缓存：InnoDB的索引缓存采用了缓存池的机制，可以减少磁盘I/O操作，提高查询性能；而MyISAM的索引缓存直接使用系统缓存，对大型数据量的表性能影响较大。数据行格式：InnoDB的数据行格式较MyISAM更为复杂，包括多个版本的数据行和回滚段等结构，这也使得InnoDB在一些特定场景下性能不如MyISAM。

如果应用需要支持事务处理或外键约束，或需要在高并发情况下获得更好的性能，建议使用InnoDB存储引擎；如果应用对数据一致性要求不高，但需要快速查询，可以考虑使用MyISAM存储引擎。

存储引擎的选择

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

相关面试题

2、索引

2.1 索引介绍

什么是索引？

索引是一种数据结构，用于快速定位数据库表中特定数据的位置。索引可以看作是一本书的目录，它记录了书中关键字出现的页码，读者可以通过查阅目录快速定位到所需内容，而不必一个一个地翻阅整本书。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
在数据库中，索引可以提高查询效率，加快数据的检索速度。它们可以减少数据库需要扫描的数据量，从而降低查询所需的时间和资源成本。使用索引可以使得查询操作的速度大大提高，尤其是在处理大量数据时更为明显。
索引可以创建在表的一个或多个列上，通常使用B-tree或哈希等数据结构实现。在查询数据时，数据库会先查找索引，根据索引中的指向数据的指针，然后再去表中查找相应的数据。
需要注意的是，虽然索引可以提高查询效率，但是过多或不恰当的索引也会影响数据库的性能，因此需要根据具体的应用场景和查询需求来选择合适的索引，避免出现索引过多或重复创建的情况。

索引的作用是什么？

提高查询速度：通过使用索引，可以快速定位数据库表中的特定数据，从而提高查询效率。加速排序：如果查询涉及到ORDER BY语句，索引可以加速排序操作，使得查询结果更快地返回。加速连接：如果查询涉及到JOIN操作，索引可以加速连接操作，提高查询效率。约束唯一性：通过在列上创建唯一索引，可以确保列中的数据唯一性，避免重复数据的插入。约束外键：通过在表之间创建外键索引，可以确保数据的完整性和一致性，避免出现数据不一致的情况。

索引的优缺点：

优点：加速排序：如果查询涉及到ORDER BY语句，索引可以加速排序操作，使得查询结果更快地返回。加速连接：如果查询涉及到JOIN操作，索引可以加速连接操作，提高查询效率。约束唯一性：通过在列上创建唯一索引，可以确保列中的数据唯一性，避免重复数据的插入。约束外键：通过在表之间创建外键索引，可以确保数据的完整性和一致性，避免出现数据不一致的情况。缺点：增加存储空间：索引会占用一定的存储空间，如果索引过多或者不恰当，会增加数据库的存储空间和维护成本。降低写入性能：在插入、更新和删除操作时，数据库需要同步更新索引，这可能会降低写入性能。不适用于小表：对于小表而言，使用索引可能会降低查询效率，因为数据库需要读取索引数据，这会增加额外的开销。不适用于频繁更新的列：如果列经常需要更新，那么索引会增加数据库的维护成本，并且会降低更新操作的性能

使用索引和不使用索引进行查找

无索引时，查询一条记录，查找方式是全表扫描（一般是直接根据主键 ID 从小到大进行遍历，直到找到带查找的记录），效率特别低；有索引时，如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对 age 这个字段建立一个二叉树的索引结构。此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

备注：这里我们只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构是 B-tree 或 B+tree 数据结构实现

2.2 索引的结构

2.2.1 前置知识

主要是复习一下数据结构树的相关知识

树

什么是树？

树（Tree）是一种非线性数据结构，它由n（n>=0）个节点组成一个有层次关系的集合，其中一个节点被定义为根节点，其余节点可分为m个互不相交的子集，每个子集本身也是一个树，并称为该根节点的子树。

树的特点：每个节点最多有m个子节点；树中不存在环路（回路）；根节点到任意节点的路径唯一；所有节点都可以从根节点到达。

二叉树

什么是二叉树？

二叉树（Binary Tree）是一种特殊的树结构，它的每个节点最多只有两个子节点，分别称为左子节点和右子节点。左子节点和右子节点的顺序是固定的，不可以交换。如果某个节点没有子节点，那么它的左子节点和右子节点都为 null。

二叉树的特点：每个节点最多有两个子节点，左子节点和右子节点。左子节点在树中的位置比右子节点先。每个节点都有一个父节点，除了根节点没有父节点。如果某个节点没有子节点，那么它的左子节点和右子节点都为 null。

二分搜索树

什么是二分搜索树？

二分搜索树（Binary Search Tree），也称为二叉查找树（Binary Sort Tree）或二叉搜索树（BST），是一种基于二叉树的数据结构，能够高效地实现数据的查找、插入和删除等操作。

二分搜索树的特点：每个节点最多有两个子节点，左子节点的值小于父节点的值，右子节点的值大于父节点的值。

二分搜索树的优缺点

优点：数据可以按照大小顺序排列，可以高效地进行查找和排序操作；同时，二分搜索树的结构简单，易于实现和理解。缺点：在特定情况下，可能会导致树的不平衡，从而影响操作效率；此外，如果数据分布较为离散，可能会导致树的高度较高，影响操作效率

平衡树

什么是平衡树？

平衡树（Balanced Tree），也称为自平衡二叉查找树（Self-Balancing Binary Search Tree），是一种能够自动保持平衡的二叉查找树，通过旋转和重新分配节点来保持树的平衡，从而保证树的高度较小，查找、插入和删除等操作的时间复杂度都能够保持在较低的水平。

平衡树的特点：平衡树是一种特殊的二叉树，它的左右子树的高度差不超过 1，也就是说，任何一个节点的左右子树的高度之差都不超过 1。

平衡树的优缺点

优点：能够快速地进行查找、插入和删除等操作，并且可以保证在最坏情况下的时间复杂度不超过 O(log n)。在处理大量数据时，平衡树能够有效地提高数据的查询效率，避免了二叉树退化成链表的情况缺点：实现相对复杂；空间占用较高；可能存在过度调整从而降低性能；不适用于频繁的插入和删除

红黑树

什么是红黑树？

红黑树（Red-Black Tree）是一种自平衡二叉查找树，它具有良好的平衡性和搜索性能，被广泛应用于各种数据结构和算法中，例如C++ STL库中的map和set，Java的TreeMap和TreeSet等。

红黑树的特点：每个节点都是红色或黑色；根节点是黑色的；每个叶子节点都是黑色的；如果一个节点是红色的，则它的两个子节点都是黑色的；任意一条从根节点到叶子节点的路径都包含相同数目的黑色节点；红黑树的最长路径不会超过最短路径的两倍

红黑树的优缺点

优点：插入、删除和查找操作的时间复杂度都是 O(log n)，其中 n 是树中节点的数量。这使得红黑树在各种操作中都具有很好的效率；红黑树具有相对平衡的性质，使得树的高度不会太高，因此在最坏情况下仍然具有较好的时间复杂度；红黑树相对于其他平衡树（如AVL树）来说，它的旋转操作更少，因此在实现上比较简单。缺点：红黑树相对于其他简单的数据结构来说，它的实现较为复杂，需要考虑许多特殊情况，因此在实现时比较困难；红黑树相对于其他数据结构来说，它的存储空间比较大，因为每个节点都需要存储额外的颜色信息

多路搜索树

什么是多路搜索树？

多路平衡查找树是一种数据结构，它可以用来高效地维护一组动态变化的有序数据。它是平衡树的一种变种，相比于传统的二叉平衡树（如红黑树、AVL树等），它允许一个节点拥有多个子节点，从而可以减少树的高度，提高查找、插入、删除等操作的效率。

多路搜索树的特点：一个节点可以拥有多个子节点，通常称为“度”。每个节点可以包含多个关键字，通常按照从小到大的顺序排列。为了保持平衡，各个节点之间的关键字范围是相邻且不重叠的。每个节点的子节点按照一定的顺序排列，可以快速地进行二分查找。

多路搜索树的优缺点：

优点：可以高效地处理大量数据，避免频繁的磁盘I/O操作。插入、删除、查找等操作的时间复杂度都比较稳定，通常为O(log n)。对于范围查询等高级操作，多路平衡查找树比传统的平衡树更加适合。缺点：比传统的平衡树更加复杂，实现难度较大。对于大量随机插入的数据，可能会导致树的高度增加，影响查询性能。节点的度数增加会带来额外的存储空间开销。

B树

什么是B树？

B树（B-Tree）是一种多路搜索树，常用于组织和管理磁盘或其他直接存取辅助设备的文件系统和数据库

B树的特点：能够自适应地调整节点的大小，使得树的高度更低，从而减少了磁盘I/O操作的次数，提高了数据的查询效率

B树的优缺点：

优点：支持高效的随机访问和范围查询；适合磁盘或其他直接存取辅助设备的文件系统和数据库；能够自适应地调整节点大小缺点：实现相对复杂；内存消耗较高；适合大量数据

B+树

什么是B+树？

B+tree 是一种多路平衡查找树，它是B树的一种变体，常用于文件系统和数据库索引等应用中

B+树的特点：所有关键字都在叶子节点上出现，内部节点只包含关键字的索引信息，使得查询时能够更快地定位到叶子节点；所有叶子节点都包含了相同的信息，且按照关键字的大小顺序链接在一起，方便按范围查找和遍历；内部节点与叶子节点的结构相同，且每个节点的关键字数量都介于 n / 2 n/2 n/2 和 n n n 之间，其中 n n n 是节点的最大关键字数量，这保证了树的平衡性和性能的稳定性。

B+树的优缺点：

优点：B+树能够更快地定位到叶子节点，因此查询性能更好；B+树内部节点只存储索引信息，因此相对于B树，它具有更好的空间利用率；B+树的叶子节点都按照关键字大小顺序链接在一起，方便范围查找和遍历。缺点：插入和删除操作需要维护指针和链表，因此相对于B树，它的实现更加复杂；B+树需要较大的节点，因此对于小规模的数据集来说，可能不如其他数据结构来得高效

B-树

什么是B-树？

B-tree 是一种多路平衡查找树，是B+树的变体之一。与B+树类似，B-树也是在每个节点中增加了更多的关键字，以减少树的高度，提高查找效率。

B-树的特点：每个节点存储的关键字个数为m-1个，子节点数目在2~m之间；所有叶子节点都在同一层，且不包含任何信息，只起到承载关键字的作用；非叶子节点存储的关键字数量比B+树更少；相对于B+树，每个节点的存储空间利用率更高；B-树可以作为外部存储的数据结构，因为其在存储时可以将节点存储在磁盘上。

B-树的优缺点：

优点：B-树的节点更大，使得树的高度更低，访问速度更快；B-树能够在不同节点间进行范围查找；B-树支持较高的并发性和事务性操作。缺点：B-树的查询性能比B+树略低；插入和删除操作时需要维护节点之间的平衡，因此B-树的维护成本比B+树高；B-树的节点更大，存储空间的利用率不如B+树。

2.2.2 索引结构的种类

本小节主要了解二分搜索树、红黑树、B+树实现索引时各自的特点，以及hash索引

索引结构的种类

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

不同存储引擎所支持的索引结构：

注意：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

不同数据结构的索引

如果我们使用二分搜索树作为索引结构

理想情况下的二分搜索树：

看着还行，但是假如索引都是按照顺序的（比如索引是自增的)，此时二叉树就变成这样了：

像上面那样，二叉树就变成了一个单向链表，这样极大地降低了查询的性能

如果我们使用红黑树作为索引结构：

但是仍然存在一定的问题，那就是当数据量特别大的时候，二叉树的层次会特别深，会导致查找效率特别低

所以在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+树

所有元素都会出现在叶子节点中，叶子节点形成了一个单向链表，从而提高了访问效率

不同数据结构实现的索引之间的比较

二分搜索树：数据量小的时候，可以进行高效地查找和排序操作，同时结构简单，易于实现和理解大数据量情况下，层级较深，检索速度慢，特别是索引有序的情况下，会导致数据分布特别散，甚至形成用过单向链表红黑树：插入、删除、查找等操作的时间复杂度都是 O ( l o g n ) O(logn) O(logn)，相对而言查找效率比较高，并且比较稳定红黑树相对其它数据结构实现比较复杂，占用内存也比较多，特别是数据量特别大的时候，树的层次会比较深，从而大幅降低查询效率B+数：所有元素都出现在叶子节点上，并且形成一个双向链表，B+树的非叶子节点只存储索引，所以即使在大数据情况下，查询性能依旧比较好但是消耗的内存比较大，不适合存储小规模数据，实现起来也相对更加复杂

相关面试题

还有其它的数据结构实现索引，比如Hash结构

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在 hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点： Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）无法利用索引完成排序操作查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引存储引擎支持：在MySQL中，支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

2.3 索引的分类

索引的分类

唯一索引：在列上创建唯一索引，确保列中的数据唯一性，避免重复数据的插入。主键索引：主键是一种特殊的唯一索引，用于唯一标识表中的每一行数据，主键索引也是一种聚簇索引。组合索引：在多个列上创建联合索引，可以提高查询效率，但需要注意索引顺序和查询条件的匹配度。全文索引：在文本列上创建全文索引，可以加快文本搜索的速度。空间索引：在地理位置数据类型的列上创建空间索引，可以加速地理位置搜索的速度。聚簇索引（聚集索引）：在表上创建聚簇索引，可以将数据按照索引顺序存储在磁盘上，提高查询效率。非聚簇索引（非聚集索引）：在表上创建非聚簇索引，可以在索引中存储指向数据位置的指针，查询时需要先查找索引，再根据指针查找数据。

索引主要分为四大类：主键索引、唯一索引、常规索引、全文索引

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

图示：

备注：

聚集索引的叶子节点下挂的是这一行的数据

二级索引的叶子节点下挂的是该字段值对应的主键值

聚集索引选取规则

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询

具体过程如下：

①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

相关面试题

2.4 索引的使用

2.4.1 索引的基本使用

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;

示例：

示例1：创建一个普通索引

mysql> create idx_user_name on tb_user(name);mysql> show index from tb_user;

示例2：创建一个唯一索引

mysql> create unique index idx_user_phone on tb_user(phone);mysql> show index from tb_user;

示例3：创建联合索引

mysql> create index idx_user_pro_age_sta on tb_user(profession, age, status);mysql> show index from tb_user;

2.4.2 验证索引的效率

事先准备一张存放1000w条记录的表 tb_sku

select * from tb_sku where id = 1\G;# 备注：\G 将查询的结果，将每个字段按行展示，如果按列展示不太方便查看，因为字段太多了

1）按列展示：

\G按行展示：

可以看到这条SQL的执行速度约为0.00sec，可以说是相当块了，这是相当快了，这是因为我们是使用主键查询的，主键是有索引的。2）现在我们使用以下的SQL来查询

SELECT * FROM tb_sku WHERE sn = '100000003145001';

可以看到这个使用 sn这个字段进行查询，耗时12.38sec（恐怖如斯😱），这是由于sn是没有使用索引的缘故，

3）现在我们给它添加缘故索引，然后再来测试一下

create index idx_sku_sn on tb_sku(sn);

可以看到构建索引的过程更加耗时，耗时高达1min 13.67ses，这是由于创建索引是构建一个B+树，创建一个数据结构，特别还是B+树这种比较复杂的数据结构肯定是特别耗时的，所以在此建议索引尽量在建表的时候就构建

可以看到构建索引后，耗时直接由之前的12.38sec降低至0.07sec了，从这里可以看出索引的作用是多么巨大了吧😄

总结

使用索引比不使用索引要快得多主键索引要比普通索引快

2.4.3 联合索引

本小节主要学习联合索引中的有关特别重要的法则最左前缀法则，以及范围查询时的最左前缀法则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

示例：

以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引。

1）使用show from tb_user;命令查看索引：

可以看到 tb_user 表有一个联合索引idx_user_pro_age_sta，这个联合索引涉及到三个字段，顺序分别为：profession， age，status。对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。接下来，我们来演示几组案例，看一下具体的执行计划

2）使用explain命令查看下面这条 select 语句的执行计划：

可以看到可以用到联合索引，实际也是用到联合索引，联合索引的长度为54

3）现在去掉status字段，再使用explain命令查看下面这条 select 语句的执行计划：

可以看到可以使用联合索引，实际用到了联合索引，但是联合索引的长度为49（由此可以推断出status字段的索引长度为5）

4）现在去掉age、status字段，再使用explain命令查看下面这条 select 语句的执行计划：

可以看到使用了联合索引，联合索引的长度为47，所以可以推断出prosession字段的索引长度为47，age字段的缩影长度为2

5）现在去掉age字段，再使用explain命令查看下面这条 select 语句的执行计划：

可以看到可以使用联合索引，实际用到了联合索引，但是联合索引的长度为47，所以并没有使用到status字段的索引，这是因为中间跳过了age字段，所以说不能跳字段

6）现在去掉profession字段，再使用explain命令查看下面这条 select 语句的执行计划：

可以看到去掉最左字段后，直接没有使用索引了，而变成了全表扫描

7）现在调整一下三给字段的顺序

可以看到调整完位置后，三个索引都用到了，所以说，索引的使用与位置无关，只跟索引的字段是否存在有关

总结

联合索引遵从最左前缀法则只跟字段是否存在有关，与编写SQL时的顺序无关当索引都存在时，索引长度最长，效率最高中间有一个索引字段不存在时，后面所有的索引字段也不会被使用最左边的索引字段不存在时，该联合索引不会被使用，而是采用全表扫描

范围查询

联合索引中，出现范围查询>或<，范围查询右侧的列索引失效（只针对联合索引）

可以看到索引长度为49，说明没有用到 status 索引，这是因为联合索引的顺序的 profession、age、status，现在age是采用范围查询，所以 age 后面的索引字段直接失效了

注意：和之前一样，只跟字段是否存在有关，与编写SQL时的顺序无关

规避方法：范围查询时尽量使用>=或<=

2.4.4 索引的失效情况

情况一：不要在索引列上进行运算操作，索引将失效

1）不使用运算操作（运算操作包括加减乘除，函数调用）

可以看到使用了索引，type为const，效率比较高

2）使用运算操作

可以看到key为null，表示根本没有用到索引，type为ALL，表示本次查询是采用全表扫描的方式，效率极低

情况二：字符串类型字段使用时，不加引号，索引将失效

1）加引号

可以看到使用了索引，索引为长度为54

2）不加引号

可以看到索引长度为49，显然索引字段status没有被使用，这是因为status字段没有添加引号

情况三：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

1）非头部模糊查询

可以看到使用了索引

2）头部模糊查询

可以看到索引失效了，type为ALL，表示该查询是通过全表扫描得到的结果

情况四：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

1）or两侧的字段都有索引

可以看到，使用了id的索引和name的索引

2）or一侧字段有索引，一侧字段没有索引

可以看到查询结果没有使用索引，而是直接进行全表扫描（age是有联合索引的）

3）or一侧是多个字段，一侧有索引

多个字段都没有索引（age和status是有联合索引的），可以看到使用了name索引，但是type却是ALL，查询结果是通过全表扫描的方式：

多个字段中有一个有索引（age没有索引，id有主键索引），name有索引，可以看到使用了索引

情况四总结：

or两侧是单个字段时，两侧必须都要有索引（联合索引不算，单列索引才算），这样索引才不会失效or两侧，一侧多个字段，一侧是单个字段，单个字段要有索引，多个字段中要至少有一个有索引，这样索引才不会失效

情况五：如果MySQL评估使用索引比全表更慢，则不使用索引（数据分布影响）

tb_user表中的数据大量都符合我们的查询条件，则MySQL的搜索引擎会直接选择全表扫描而不是用索引。tb_user表中第一个数据的phone是17799990000，如果我们查询的数据是>=1799990005的，大量数据都符合我们的查询条件，直接采用全表扫描，因为此时采用索引反而会更慢

我们查询的条件，表中只有部分数据符合，则该字段存在索引就直接采用索引

2.4.5 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中添加一些认为的提示来达到优化操作的目的，比如在执行一个条件查询时，条件中有多个索引可以被使用，一般情况下都是MySQL自动选择使用哪一个索引，此时我们可以人为让MySQL选择使用哪一个索引，这个过程就被称为SQL提示

user index()：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）

这里profession字段有两个索引，一个联合索引，一个单列索引，可以看到我们建议MySQL使用profession字段的单列索引，但是MySQL自己还会评估一下是你建议的快还是自己的快，最终的使用哪一个索引还是得看MySQL，最终可以发现单列索引要比联合索引快，最终MySQL选择遵从我们的建议

ignore index()：忽略指定的索引

这里我们忽略了profession的单列索引，所以MySQL只能使用它的联合索引

force index()：强制使用指定的索引

这里我们强制MySQL使用profession的联合索引，可以看到MySQL最终屈服了(●ˇ∀ˇ●)

2.4.6 覆盖索引

尽量使用覆盖索引，减少select *。那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

覆盖索引：

回表查询（性能更低）：

1）select *

Using index condition表示查找使用了索引，但是需要回表查询数据

2）select 查询有关聚集索引字段，有关二级索引字段

Using where; Using index表示查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。id是聚集索引，而profession、age是联合索引，联合索引属于二级索引，二级索引是挂在聚集索引下面的，所以需要查询的结果我们可以直接得到

3）select 查询两个聚集索引字段，一个二级索引字段

id是聚集索引，而profession、age、status是联合索引，这一部分不需要回表，但是还需要查找name，name是列索引，列索引属于聚集索引，所以还需要进行回表查询（回表查询可以参考前面）

相关面试题

2.4.7 前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将该列值的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

基本语法：

create index 索引名 on 表名(列名(n));

备注：n表示用使用前多少个字符，它决定了前缀索引的长度

前缀长度如何选取？

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

示例：计算email的选择性

# 查找该表的总记录数select count(*) from tb_user; -- 查询结果为24# 查早email的总记录数（要去重）select count(distinct email) from tb_user; -- 查询结果为24

所以我们可以计算出email字段的选择性为：总记录数 / e m a i l 不重复的记录数 = 24 / 24 = 1 总记录数/email不重复的记录数=24/24=1 总记录数/email不重复的记录数=24/24=1

也可以计算email字段的前5个字符的选择性：

select count(distinct substring(email,1,5)) / count(*) from tb_user ; -- 结果是1

所以此时我们可以直接使用email前五个字符作为前缀索引

前缀索引的查询流程：

2.4.8 单列索引与联合索引

单列索引：即一个索引只包含单个列联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

示例

示例1：and条件查询，and两侧都是单列索引

可以看到只走了phone字段的索引，没有走name字段的索引，此时必然会进行回表查询

示例2：创建联合索引，然后在进行查询

create unique index idx_user_phone_name on tb_user(phone,name);explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';

此时MySQL还是自动选择使用phone字段的索引，并没有用到我们创建的联合索引，会进行回表查询

此时我们使用use index建议MySQL使用我们创建的联合索引，提交查询效率

联合索引查询流程：

2.5 索引的设计原则

索引的设计原则（尽量遵循，能够让给索引更加高效）

针对于数据量较大（数据量超过100w），且查询比较频繁的表建立索引针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。

优化索引的建议：

索引查询效率：主键索引>唯一索引>单列索引>多列索引（聚合索引）。进行条件查询时，尽量使用联合索引，虽然单列索引的效率高，但是会在进行多条件查询时，容易发生回表查询，降低效率。所以少用 select * 而应该选择手动显示地指出查询字段使用联合索引时，遵从最左前缀法则使用联合索引时，如果索引字段太长，应该使用前缀索引，同时要确保前缀选择性要高能覆盖索引就覆盖索引，避免回表降低查询效率条件查询时，存在多个索引，应该使用SQL提示，保障使用高效率的索引要熟悉索引的失效情况，避免索引失效而导致全表查询

……还有哪些建议呢？欢迎补充说明(●’◡’●)

3、SQL优化

3.1 SQL性能分析

本小节主要学习：查看SQL的执行频率、查看慢查询日志、查看SQL的执行时间（profile）、查看SQL的执行计划（explain）

查看SQL的执行频率

通过下面的指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。如果是以增删改为主，我们可以考虑不对其进行索引的优化。如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

show [session|global] status

status可以使用以下几个值：

Com_______：查询各种操作的使用次数Com_insert：查询插入操作的次数Com_delete：查询删除操作的次数Com_update：查询更新操作的次数Com_select：查询查询操作的次数

show global status like 'Com_______'; -- 备注: Com后面根7个下划线

查看慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。

查看慢查询日志是否开启（MySQL默认慢查询日志是OFF，没有开启的，ON表示开启）

show variables like 'slow_query_log';

开启慢查询日志

Step1：进入MySQL的配置文件

vi /etc/f

Step2：编写慢查询配置

# 开启MySQL慢日志查询开关slow_query_log=1# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志long_query_time=2

Step3：重启MySQL

systemctl restart mysqld# 重启之后可以使用下面的指令查看慢查询是否已经开启show variables like 'slow_query_log';

备注：MySQL的慢查询日志默认在该路径下/var/lib/mysql/，进入下面的目录，然后使用ll | grep slow.log查看到一个*-slow.log的文件(*是你的主机名)，然后使用cat命令可以查看到其中的相应信息

tail -f node1-slow.log命令可以即时查看慢查询日志的内容变化

profile：查看SQL的执行时间

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

# 查看当前数据库是否支持profileselect @@have_profiling ;# 查看当前数据库是否开启profile（默认是0表示关闭的，1表示开启）select @@profiling;# 开启profilingset profiling = 1;# 查看每一条SQL的耗时基本情况show profiles;# 查看指定query_id的SQL语句各个阶段的耗时情况show profile for query query_id;# 查看指定query_id的SQL语句CPU的使用情况show profile cpu for query query_id;

explain：查看SQL的执行计划

EXPLAIN或者DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

备注：

type是优化的一个重要指标，不查询任何表（select ‘A’，查询一个字符A，直接返回A），它的性能是最好的为NULL，查询系统表，type一般为system，根据主键或者唯一索引查询type一般是const，根据非唯一性索引查询一般type为eq_ref或ref，index表示用过索引但是是索引扫描，all一般是对全表扫描，性能最差extra表示额外信息，查询到的值最终没有展示就会在这进行展示

3.2 insert优化

3.2.1 普通数据插入

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

优化前：

insert into tb_test values(2,'tom');insert into tb_test values(1,'cat');insert into tb_test values(3,'jerry');

优化一：批量插入数据

Insert into tb_test values(2,'Tom'),(1,'Cat'),(3,'Jerry');

批量插入比一次一次的插入操作效率要高主要有以下几个原因：

减少了交互次数：一次一次的插入操作会产生大量的数据库操作，每次操作都需要与数据库进行一次交互，包括建立连接、发送请求、接收响应等等。而批量插入可以将多个数据行一次性提交到数据库中，从而减少了交互次数，提高了效率。提高了数据压缩率：批量插入可以将多个数据行一次性提交到数据库中，这样可以提高数据压缩率，从而减少了数据在网络传输过程中的大小，降低了网络带宽的消耗。减少了索引更新次数：在大量数据插入时，如果每次插入一行数据，那么数据库需要对索引进行多次更新，这会导致性能下降。而批量插入可以将多个数据行一次性提交到数据库中，从而减少了索引更新次数，提高了效率。

优化二：手动控制事务

start transaction;Insert into tb_test values(2,'Tom'),(1,'Cat'),(3,'Jerry');commit;

MySQL默认是自动控制事务的，一条SQL执行后就会自动提交事务，频繁地提交事务会大量消耗系统资源，从而降低系统性能

注意：手动控制事务与自动控制事务的效率高低并不是固定的，而是取决于具体的应用场景和实现方式。

在一些特定的场景下，手动控制事务可能会比自动控制事务效率更高。这通常是因为手动控制事务可以更精确地控制事务的范围和生命周期，从而避免了不必要的锁和等待。手动控制事务可以将多个操作合并为一个事务，避免了多个事务之间的开销和隔离级别的切换。例如，在批量数据处理的场景中，手动控制事务可以将多个数据操作包装在一个事务中，从而减少了事务的开销和隔离级别的切换。在高并发场景下，手动控制事务可以更加精细地控制锁的范围和时间，避免了不必要的等待和阻塞，提高了并发能力和响应速度。然而，在一些场景下，自动控制事务可能会更加高效。自动控制事务可以通过数据库引擎自动管理事务的范围和生命周期，从而避免了手动控制事务中可能出现的错误和风险。自动控制事务可以更加灵活地处理异常和回滚，保证事务的一致性和可靠性。

优化三：主键顺序插入，性能要高于乱序插入

start transaction;Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');commit;

主键按顺序插入性能比乱序插入高的原因主要有两个：

减少了索引分裂：主键按顺序插入可以保证新插入的数据总是在索引的末尾，这样可以避免索引分裂。当索引发生分裂时，数据库需要重新组织索引结构，这会导致性能下降。而按顺序插入可以减少索引分裂的发生，从而提高性能。

减少了随机磁盘访问：主键按顺序插入可以让新插入的数据与已有的数据在磁盘上的物理位置相邻，这样可以减少磁盘寻址的次数，从而提高性能。相反，如果插入数据的主键是随机生成的，那么新插入的数据可能会分散在磁盘上的不同位置，这样就需要进行多次随机磁盘访问，从而降低了性能。

注意：如果主键是自增长的，按顺序插入和按乱序插入的性能差距可能不太明显。因为自增长主键也可以保证新插入的数据总是在索引的末尾，从而避免索引分裂，而且自增长主键的值也可以按顺序生成，从而减少随机磁盘访问。但是，在一些特定的场景下，按顺序插入仍然可能比按乱序插入更高效，例如在使用InnoDB引擎时，因为InnoDB使用聚簇索引，按顺序插入可以避免页分裂，提高性能。

3.2.2 大数据插入

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入（之前那张 1000w数据量的 tb_sku表就是采用这种方式插入的）。操作如下：

Step1：使用local-infile参数登录MySQL

mysql -u 用户名 -p --local-infile

备注：local-infile表示赋予当前登录用户导入数据的权限

Step2：开启数据导入权限

set global local_infile=1;

备注：MySQL默认是关闭数据导入功能的

Step3：建表

假设建立 tb_sku表，用于数据导入

Step4：导入数据

load data local infile '/root/sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

3.3 主键优化

在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的。这一小节，就来介绍一下具体的原因，然后再分析一下主键又该如何设计。

3.3.1 数据的组织方式

数据的组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table，IOT)

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

3.3.2 页分裂

页分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列

主键顺序插入效果：

①从磁盘中申请页，主键顺序插入

② 第一个页没有满，继续往第一页插入

③当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

④当第二页写满了，再往第三页写入

主键乱序插入效果：

①假如1#,2#页都已经写满了，存放了如图所示的数据

②此时再插入id为50的记录，我们来看看会发生什么现象

会再次开启一个页，写入新的页中吗？

不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后

③但是47所在的1#页，已经写满了，存储不了50对应的数据了。那么此时会开辟一个新的页 3#

④但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50

⑤移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。所以，此时，需要重新设置链表指针

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作

3.3.3 页合并

页合并：删除数据时，删除到一定程度，MySQL就会查询该页左右的页是否具有合并到可能

目前表中已有数据的索引结构(叶子节点)如下：

当我们对已有数据进行删除时，具体的效果如下：

①当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用

②当我们继续删除2#的数据记录

③当页中删除的记录达到 MERGE_THRESHOLD^{1（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用}

④删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页

这个里面所发生的合并页的这个现象，就称之为 “页合并”

3.3.4 主键的设计原则

主键的设计原则满足业务需求的情况下，尽量降低主键的长度。因为二级索引存储的主键索引，主键索引过常，会浪费大量的内存，同时增大磁盘的IO，从而降低查询的性能插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。使用主键自增能够避免页分裂现象，页分裂会消耗大量的时间尽量不要使用UUID做主键或者是其他自然主键，如身份证号。因为UUID或者是身份证会比较长，并且是无无序的，容易发生页分裂业务操作时，避免对主键的修改。修改主键会改动表的索引结构，所以主键一般不要动（主键一般是选择没有任何逻辑含义的数据）

3.4 order by优化

MySQL的排序，有两种方式：

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index

示例：

①数据准备：

②直接使用order by根据年龄进行排序（MySQL中，order by默认是采用升序排序ASC）

可以看到此时没有使用索引，而是采用全表扫描的方式得到结果，排序方式为 Using filesort

备注：即使此时age和phone有各自的单列索引，此时查询仍然是全表扫描，但强制使用单列索引是成功的Why?

③为age和phone创建一个联合索引，然后再进行查询，之后进行升序排序（MySQL默认是升序的，所以ASC可以省略）

可以看到这次排序使用到了索引，并且排序方式变成了 Using index

⑤按照age进行降序排序

也出现 Using index，但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan（它的效率和Using index相差无几）。在 MySQL8 版本中，支持降序索引，我们也可以创建降序索引

⑥order by排序不按照最左前缀法则

可以看到出现了 Using filesort，显然效率要比Using index要低，这是因为order by也需要遵循最左前缀法则，否则就是采用Using filesort排序

⑦根据age, phone进行降序一个升序，一个降序

因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort

⑧为了解决这个问题，我们需要再创建索引时指定顺序

现在我们再来查看索引的collection字段

总结：
MySQL中ODER BY 默认采用升序排序ASC，如果查询的是多个字段，需要使用联合索引效率对比：Using index>Backward index scan>Using filesort（大致，Using index和Backward index scan效率差不多）order by也是需要遵循最左前缀法则使用联合索引时，oder by按一个字段排序，升序、降序无所谓；如果是按多个字段进行排序，升序排序无所谓，因为MySQL默认是采用升序排序，但是如果是降序，则需要创建降序索引，否则是Using filesor，效率很低

升序/降序联合索引结构：

oderby优化原则：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则尽量使用覆盖索引多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

3.5 group by优化

分组操作，我们主要来看看索引对于分组操作的

示例：

①数据准备：tb_user表只有主键索引

②直接查询没有索引的字段

③创建联合索引，然后进行查询（满足最左前缀法则的情况下）

④查询，但不满足最左前缀法则，此时会使用到临时表，效率较低

groupby优化原则：在分组操作时，可以通过索引来提高效率分组操作时，索引的使用也是满足最左前缀法则的

3.6 limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低

示例

①数据准备：准备一张还有1000w条记录的tb_sku表

②分页查询前10条数据select * from tb_sku limit 0,10;耗时0.0sec

③分页查询第100w条记录起始的10条记录select * from tb_sku limit 1000000,10;耗时3.09sec

④分页查询900条记录起始的10条记录select * from tb_sku limit 900000,10;耗时14.11 sec

通过测试我们会看到，limit查询越往后，分页查询效率越低，这就是分页查询的问题所在。因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

原始SQL：（耗时14.11 sec）

select * from tb_sku limit 900000,10;

覆盖索引+子查询优化：（耗时8.12 sec）

select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

备注：将select id from tb_sku order by id limit 2000000,10;的查询结果看作一张表，进行多表联合查询

3.7 count优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。InnoDB 引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)

count的用法：count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。用法：count（*）、count（主键）、count（字段）、count（数字）

效率对比： c o u n t ( 字段 ) < c o u n t ( 主键 i d ) < c o u n t ( 1 ) ≈ c o u n t ( ∗ ) ，所以尽量使用 c o u n t ( ∗ ) count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*) count(字段)<count(主键id)<count(1)≈count(∗)，所以尽量使用count(∗)

3.8 update优化

update注意事项：进行条件更新时，如果条件字段有索引，则MySQL是使用行锁；如果条件字段没有索引，则MySQL是使用表锁。所以在执行更新操作时，最好根据索引字段进行更新行锁：将表中一行数据锁住，只有等事务提交后才能被访问表锁：将一整张表锁住，只有等事务提交后才能被访问

注意：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁

4、视图/存储过程/触发器

4.1 视图

4.1.1 视图概述

什么是视图？

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图的作用？

简化查询操作：视图可以将复杂的查询操作封装起来，形成一个简单易用的查询接口，避免了重复编写复杂查询语句的麻烦。提高查询效率：视图可以预先计算查询结果，并将结果缓存起来，多次查询时可以直接使用缓存结果，提高查询效率。实现数据安全性：通过视图，可以限制用户对某些敏感信息的访问，只允许用户查看有限的信息，保护数据的安全性。管理数据访问权限：视图可以对不同用户分配不同的访问权限，限制用户的数据访问范围，维护数据的安全性和保密性。数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响

什么是基表？

在数据库中，基表（Base Table）是指实际存储数据的表，也称为物理表。基表包含了数据表中的所有记录和字段，它是数据库中最基本的数据存储单元。
注意：视图本身不存储数据，数据都是存储在基表中，视图的创建、查询也依赖于基表，如果基表被删除了，基表对应的视图也会跟着消失

4.1.2 视图的基本使用

创建视图

CREATE [OR REPLACE] VIEW [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

参数说明：

OR REPLACE：如果视图已存在，则删除已存在的然后创建一个新的（如果存在不加这个参数直接创建，会报错”对象已存在“）ALGORITHM：可选。表示视图选择的算法。（一般情况直接使用MySQL默认的就好了）UNDEFINED：表示MySQL将自动选择所要使用的算法。MERGE：表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句。WITH CHECK OPTION：可选。表示修改视图时要保证在该视图的权限范围之内，数据不符合条件直接报错CASCADED：可选。表示修改视图时，需要满足跟该视图有关的所有相关视图和表的条件，该参数为默认值。LOCAL：表示修改视图时，只要满足该视图本身定义的条件即可

查询视图

# 查看创建视图语句：SHOW CREATE VIEW 视图名称;# 查看视图数据：SELECT * FROM 视图名称 ...... ;

修改视图

# 方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]# 方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

删除视图

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

更新视图

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）DISTINCTGROUP BYHAVINGUNION或者UNION ALL

……

# 创建一个视图create view stu_v_count as select count(*) from student;# 往视图中插入一条记录insert into stu_v_count values(10);

插入操作直接爆粗The target table stu_v_count of the INSERT is not insertable-into，这是因为向视图插入数据时，插入的数据必须与基表存在一对一的关系，我们这张视图它的基表是student，但是视图中是数据并不是一对一的关系，它是count(*)，显然是一对多的关系，所以此时我们插入就会直接报错

示例：

# 创建视图create or replace view stu_v_1 as select id,name from student where id <= 10;# 查询视图-- 查询视图的创建语句show create view stu_v_1;-- 查询视图中的数据select * from stu_v_1;select * from stu_v_1 where id = 1;# 修改视图-- 方式一：使用create语句create or replace view stu_v_1 as select id,name,no from student where id <= 10;-- 方式二：使用alter语句alter view stu_v_1 as select id,name from student where id <= 10;# 删除视图drop view if exists stu_v_1;# 利用视图屏蔽敏感字段-- 开发人员只能看到用户的基本字段，屏蔽手机号和邮箱两个字段create or replace view view_tb_user as select id,name,profession,age,gender,status,createtime from tb_user;select * from view_tb_user;# 利用视图简化多表联合查询-- 2.查询每个学生所选修的课程（三表联查，这个功能在很多业务中都要用到，所以可以直接定义一个视图，提高查询效率）select s.name student_name, s.no student_no, c.name course_name from student s, student_course sc, course c where s.id = sc.studentid and c.id = sc.courseid;create or replace view view_student_course as select s.name student_name, s.no student_no, c.name course_name from student s, student_course sc, course c where s.id = sc.studentid and c.id = sc.courseid;select * from view_student_course;

4.1.3 选项配置详解

检查选项：WITH CHECK OPTION

前面我们说过，视图是一张虚拟表，在向视图中插入数据时，我们可能会遇到，往视图中插入数据成功了，但是视图却查不到！

这是什么原因呢？这是因为我们在创建视图的时候，指定的条件为 id<=10, id为30的数据，是不符合条件的，所以没有查

询出来，但是这条数据确实是已经成功的插入到了基表中。那如何避免这种情况呢？这就需要用到视图的检查选项了

可以看到添加了检查选项后，我们再插入不符合条件的数据时，MySQL会直接报错：

检查范围

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如：插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为 CASCADED 。

CASCADED：强制级联，会强制检查父级视图的索引。在进行视图插入、更新、删除操作时，不仅检查是否满足当前视图的约束，还要检查是否满足所有的父级视图的约束。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

# 视图1 插入数据的id没有限制create or replace view stu_v_1 as select id,name from student where id <= 20;insert into stu_v_3 values(9,'Tom'); -- 插入成功insert into stu_v_3 values(16,'Tom'); -- 插入成功insert into stu_v_2 values(30,'Tom'); -- 插入成功# 视图2 插入数据的id必须 >=10 && <=20，否则直接报错create or replace view stu_v_2 as select id,name from stu_v_1 where id >= 10 with cascaded check option;insert into stu_v_3 values(9,'Tom'); -- 插入失败insert into stu_v_3 values(16,'Tom'); -- 插入成功insert into stu_v_2 values(30,'Tom'); -- 插入失败（强制检查父级视图的约束）# 视图3 插入数据的id必须 >=10 && <=20，否则直接报错create or replace view stu_v_3 as select id,name from stu_v_2 where id <=30 with cascaded check option;insert into stu_v_3 values(9,'Tom'); -- 插入失败insert into stu_v_3 values(16,'Tom'); -- 插入成功insert into stu_v_2 values(30,'Tom'); -- 插入失败（强制检查父级视图的约束）

LOCAL：本地级联，不会强制检查父级视图。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，只会检查v2，不会检查v2的关联视图v1

# 视图1 插入数据的id没有限制create or replace view stu_v_1 as select id,name from student where id <= 20;insert into stu_v_1 values(9,'Tom'); -- 插入成功insert into stu_v_1 values(16,'Tom'); -- 插入成功insert into stu_v_1 values(30,'Tom'); -- 插入成功# 视图2 插入数据的id必须 >=10，否则直接报错create or replace view stu_v_2 as select id,name from stu_v_1 where id >= 10 with local check option;insert into stu_v_2 values(9,'Tom'); -- 插入失败insert into stu_v_2 values(16,'Tom'); -- 插入成功insert into stu_v_2 values(30,'Tom'); -- 插入成功（不会强制检查父级视图的约束）# 视图3 插入数据的id必须 >=10 && <=30，否则直接报错localcreate or replace view stu_v_3 as select id,name from stu_v_2 where id <=30 with local check option;insert into stu_v_3 values(9,'Tom'); -- 插入失败insert into stu_v_3 values(16,'Tom'); -- 插入成功insert into stu_v_3 values(30,'Tom'); -- 插入成功（不会强制检查父级视图的约束）

4.2 存储过程

4.2.1 存储过程概述

什么是存储过程？

存储过程（Stored Procedure）是事先经过编译并存储在数据库中的一段 SQL 语句的集合（其实说白了就是一个函数），调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

存储过程的作用是什么？

提高性能：存储过程可以在数据库服务器上执行，减少了客户端与服务器之间的网络通信，从而提高了性能。重用代码：存储过程可以在多个应用程序中重复使用相同的代码逻辑，从而提高了开发效率。简化复杂操作：存储过程可以实现复杂的操作和逻辑，比如条件判断、循环、异常处理等，从而简化了开发人员的工作。改善数据安全：存储过程可以限制用户对数据库的访问，从而提高了数据的安全性。此外，存储过程还可以防止SQL注入攻击。维护方便：存储过程可以在数据库中进行存储、修改和调用，从而方便了数据库的维护和管理。

存储过程的特点有哪些？

预编译：存储过程在创建时被编译，所以它的执行速度比动态SQL语句要快。执行效率高：存储过程在数据库服务器上执行，减少了客户端与服务器之间的网络通信，从而提高了执行效率。如果涉及多多条SQL，没执行一次都是一次网络传输，但是使用存储过程后，只需要进行一次网络交互即可可重用：存储过程可以在多个应用程序中重复使用相同的代码逻辑，从而提高了开发效率。可扩展性强：存储过程可以实现复杂的操作和逻辑，比如条件判断、循环、异常处理等，从而提高了可扩展性。数据安全性高：存储过程可以限制用户对数据库的访问，从而提高了数据的安全性。此外，存储过程还可以防止SQL注入攻击维护方便：存储过程可以在数据库中进行存储、修改和调用，从而方便了数据库的维护和管理可接受参数，也可以返回数据：在存储过程中，既可传递参数，又可以接收返回值

4.2.2 存储过程的基本使用

创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])BEGIN-- SQL语句END;

PS：参数列表请参考4.2.3

调用

CALL 名称 ([ 参数 ]);

查看

-- 查询指定数据库的存储过程及状态信息SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';-- 查询某个存储过程的定义SHOW CREATE PROCEDURE 存储过程名称;

删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称;

示例

# 创建存储过程create procedure p1()beginselect count(*) from student;end;# 调用存储过程call p1();# 查看存储过程-- 查看指定数据库的存储过程及状态信息select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'mysql_study';-- 查询存储过程的创建语句show create procedure p1;# 删除存储过程drop procedure if exists p1;

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符，因为命令行中默认是以;为结束符的

delimiter $$指定$$为结束符

4.2.3 变量

在MySQL中变量分为三种类型:系统变量、用户定义变量、局部变量

系统变量：系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）

查看系统变量

-- 查看所有系统变量SHOW [ SESSION | GLOBAL ] VARIABLES ;-- 可以通过LIKE模糊匹配方式查找变量SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 查看指定变量的值SELECT @@[SESSION | GLOBAL] 系统变量名;

备注：

全局变量(GLOBAL)：全局变量针对于所有的会话。会话变量(SESSION)：会话变量针对于单个会话，只在本会话窗口有效，在另外一个会话窗口就不生效了

设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;SET @@[SESSION | GLOBAL] 系统变量名 = 值 ;

注意：

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量MySQL 服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/f中配置

示例

# 查看系统变量-- 查看所有的系统变量（MySQL默认的是Session）show variables;-- 查看所有的会话系统变量show session variables;-- 查看所有的全局系统变量show global variables;-- 查看指定的系统变量（查看事务开启自动提交，默认查看的是Session级别的）select @@autocommit;-- 查看指定的Session级别的系统变量select @@session.autocommit;# 设置系统变量set global autocommit = 1;

用户定义变量：用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用@变量名使用就可以。其作用域为当前连接

赋值

SET @var_name = expr [, @var_name = expr] ... ;SET @var_name := expr [, @var_name := expr] ... ;SELECT @var_name := expr [, @var_name := expr] ... ;SELECT 字段名 INTO @var_name FROM 表名;

使用

SELECT @varname;

注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL

示例

# 为用户定义的变量进行赋值-- 方式一set @my_name = 'ghp1';-- 方式二（推荐使用，因为=在MySQL中有赋值的意思，以示区分）set @my_name := 'ghp2';-- 方式三select count(*) into @my_count from tb_user;-- 批量赋值set @my_gender = '男', @my_age = 18;# 查看用户自定义的变量select @my_name, @my_gender, @my_age, @my_count;

备注：假如我们查看我们没有定义的变量，MySQL是不会报错的，而是显示为null

局部变量：局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块

声明

DECLARE 变量名变量类型 [DEFAULT ... ] ;

备注：变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

赋值

SET 变量名 = 值 ;SET 变量名 := 值 ;SELECT 字段名 INTO 变量名 FROM 表名 ... ;

示例

# 定义一个存储过程，在begin end块中定义一个名为stu_count的局部变量create procedure p1()begin-- 声明一个局部变量declare stu_count int default 0;-- set stu_count := 1; -- 方式一，直接赋值select count(*) into stu_count from student; -- 方式二，into赋值select stu_count;end;# 调用存储过程call p1();

4.2.4 if

if：用于做条件判断

IF 条件1 THEN.....ELSEIF 条件2 THEN -- 可选.....ELSE -- 可选.....END IF;

示例：

根据定义的分数score变量，判定当前分数对应的分数等级：
score<0 或者 score>100，分数非法
score >= 85分，等级为优秀
score >= 60分且 score < 85分，等级为及格
score < 60分，等级为不及格

drop procedure if exists p1;create procedure p1()begindeclare score int default 99;declare result varchar(10);if score < 0 || score > 100 thenset result := '分数非法！';elseif score < 60 thenset result := '分数不合格！';elseif score >= 60 && score < 85 thenset result := '分数合格';elseset result := '优秀';end if;select score, result;end;call p1();

参数

上述的需求我们虽然已经实现了，但是也存在一些问题，比如：score 分数我们是在存储过程中定义死的，而且最终计算出来的分数等级，我们也仅仅是最终查询展示出来而已。那么我们能不能，把score分数动态的传递进来，计算出来的分数等级是否可以作为返回值返回呢？答案是肯定的，我们可以通过接下来所讲解的参数来解决上述的问题。

参数的类型

用法

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名参数类型 ])BEGIN-- SQL语句END ;

示例

示例一：in、out参数的演示

动态传入分数，然后直接将结果返回

drop procedure if exists p1;create procedure p1(in score int, out result varchar(10))beginif score < 0 || score > 100 thenset result := '分数非法！';elseif score < 60 thenset result := '分数不合格！';elseif score >= 60 && score < 85 thenset result := '分数合格';elseset result := '优秀';end if;-- 查看传入的分数select score;end;call p1(60, @result);-- 查看输出的结果select @result;

示例二：inout参数的演示

输入一个分数，然后计算及格线是多少

drop procedure if exists p1;create procedure p1(inout score double)beginset score := score * 0.6;end;set @score := 710;call p1(@score);-- 查看输出的结果select @score;

4.2.5 case

case：作用类似于Java中的switch… case… default…，用于流程控制，case有两种语法

语法一：

-- 含义：当case_value的值为 when_value1时，执行statement_list1，-- 当值为 when_value2时，执行statement_list2，否则就执行 statement_listCASE case_valueWHEN when_value1 THEN statement_list1[ WHEN when_value2 THEN statement_list2] ...[ ELSE statement_list ]END CASE;

语法二：

-- 含义：当条件search_condition1成立时，执行statement_list1，-- 当条件search_condition2成立时，执行statement_list2，否则就执行 statement_listCASEWHEN search_condition1 THEN statement_list1[WHEN search_condition2 THEN statement_list2] ...[ELSE statement_list]END CASE;

示例

这里还是使用前面if的哪个示例，将if else 换成 case

drop procedure if exists p1;create procedure p1(in score int)begindeclare result varchar(10);case scorewhen score >= 0 and score < 60 then set result := '不合格!';when score >= 60 and score < 85 then set result := '合格';when score >= 85 and score <=100 then set result := '优秀';else set result := '非法!';end case;select concat('您的分数', score, result);end;call p1(2);

why?

4.2.6 while

while：类似于Java中的while循环

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑WHILE 条件 DOSQL逻辑...END WHILE;

示例

计算1~100的和

drop procedure if exists p1;create procedure p1(in n int)begindeclare sum int default 0;declare i int default 1;while i <= n do -- 条件成立就执行循环set sum = sum + i;set i = i + 1;end while;select concat('1到', n, '的和为', sum);end;call p1(100);

4.2.7 repeat

repeat：类似于Java中的do while循环。具体语法为：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环REPEATSQL逻辑...UNTIL 条件END REPEAT;

示例

计算1~100的和

drop procedure if exists p1;create procedure p1(in n int)begindeclare sum int default 0;declare i int default 1;repeatset sum := sum + i;set i := i + 1;until i > n -- 条件满足就退出循环end repeat;select concat('1到', n, '的和为', sum);end;call p1(100);

4.2.8 loop

loop：LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。

LOOP可以配合一下两个语句使用：

LEAVE：配合循环使用，退出循环。ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

[begin_label:] LOOPSQL逻辑...END LOOP [end_label];# begin_label，end_label，label 指的都是我们所自定义的标记LEAVE label; -- 退出指定标记的循环体ITERATE label; -- 直接进入下一次循环

示例

示例一：

计算从1累加到n的值，n为传入的参数值

drop procedure if exists p1;create procedure p1(in n int)begindeclare sum int default 0;declare i int default 1;s:loop -- s类似于Java中的OUT:if i > n then -- 条件成立就结束循环，类似于Java中的break OUT;leave s;end if;set sum := sum + i;set i := i + 1;end loop s;select concat('1到', n, '的和为', sum);end;call p1(100);

示例二：

计算从1到n之间的偶数累加的值，n为传入的参数值

drop procedure if exists p1;create procedure p1(in n int)begindeclare sum int default 0;declare i int default 1;s:loopif i > n thenleave s;end if;-- ---- 这段代码报错 Why?if i%2 = 1 thenset i := i + 1;iterate s; -- 相当于Java中的Continue，跳过本次循环直接进行下次循环end if;-- ----set sum := sum + i;set i := i + 1;end loop s;select concat('1到', n, '的和为', sum);end;call p1(100);

4.2.9 游标

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE

什么是游标？

游标（Cursor）是一种用于从结果集中逐行检索数据的数据库对象。游标可以被认为是一个指向结果集的指针，可以在结果集中移动，从而选取不同的行

游标能干嘛？

游标在数据库中的作用是对查询结果集进行逐行处理和操作。使用游标可以使查询结果集更灵活和精确地控制，并逐行处理结果集而不必将整个结果集加载到内存中。

游标的使用场景有哪些？

逐行处理结果集：使用游标可以对结果集逐行进行处理，这在需要按照某种特定顺序或需要对每一行进行特定操作时非常有用。执行复杂的数据处理任务：游标可以帮助处理一些复杂的数据处理任务，例如计算平均值、总和等。通过游标，可以逐行分析数据并进行一些特定的计算。处理大量数据：当需要处理大量数据时，使用游标可以使处理更加高效。相比于一次性将整个结果集加载到内存中处理，游标可以逐行处理结果集，减少内存的使用

声明游标

DECLARE 游标名称 CURSOR FOR 查询语句

打开游标

OPEN 游标名称

获取游标记录

FETCH 游标名称 INTO 变量 [ 变量 ]

关闭游标

CLOSE 游标名称

示例

示例一：使用自定义的局部变量接收多行多列数据

在之前我们在存储过程中，定义的局部变量只能接收单行单列数据，对于多行多列数据使用自定义的局部变量去接收会直接报错！

drop procedure if exists p1;create procedure p1()begindeclare stu_count int default 0;select * into stu_count from student;select stu_count;end;call p1();

示例二：

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中

drop procedure if exists p1;create procedure p1(in u_age int)begindeclare u_name varchar(100);declare u_pro varchar(100); -- 这两个局部变量的声明一定要在游标声明之前-- 申明游标，查询结果集，然后存储到游标中declare u_cursor cursor for select name,profession from tb_user where age <= u_age;-- 创建表结构drop table if exists tb_user_pro; -- 如果该表已经存在就直接删除create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));-- 开启游标open u_cursor;-- 将游标中的数据插入tb_user_pro表中while true dofetch u_cursor into u_name, u_pro;insert into tb_user_pro values(null, u_name, u_pro);end while;-- 关闭游标close u_cursor;end;call p1(30);

注意：游标的声明一定要在局部变量之后，否则报错Variable or condition declaration after cursor or handler declaration

可以看到上面的SQL执行成功了，但是由于while是死循环，所以当所有大家记录被遍历完后，会直接报错No data - zero rows fetched, selected, or processed。解决方法，需要使用条件处理程序

条件处理程序：条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

具体语法为：

详情请参考MySQL官方文档

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;handler_action 的取值：CONTINUE: 继续执行当前程序EXIT: 终止执行当前程序condition_value 的取值：SQLSTATE sqlstate_value: 状态码，如 02000SQLWARNING: 所有以01开头的SQLSTATE代码的简写NOT FOUND: 所有以02开头的SQLSTATE代码的简写SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

现在使用条件处理程序解决上面 while 死循环的bug：

drop procedure if exists p1;create procedure p1(in u_age int)begindeclare u_name varchar(100);declare u_pro varchar(100);-- 申明游标，查询结果集，然后存储到游标中declare u_cursor cursor for select name,profession from tb_user where age <= u_age;-- 声明条件处理程序（关闭游标，然后退出，02000表示SQL状态码，意思是退出）declare exit handler for sqlstate '02000' close u_cursor;-- declare exit handler for sqlstate not fount close u_cursor; -- 写法二-- 创建表结构drop table if exists tb_user_pro; -- 如果该表已经存在就直接删除create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));-- 开启游标open u_cursor;-- 将游标中的数据插入tb_user_pro表中while true dofetch u_cursor into u_name, u_pro;insert into tb_user_pro values(null, u_name, u_pro);end while;-- 关闭游标close u_cursor;end;call p1(30);

备注：通过SQLSTATE指定具体的状态码，通过SQLSTATE的代码简写方式NOT FOUND，02开头的状态码，代码简写为NOT FOUND

4.3 存储函数

存储函数是什么？

在MySQL中，存储函数（FUNCTION）是一段被封装在函数体内的SQL语句，可以在需要时被调用。存储函数可以接受输入参数，并且可以返回一个值或一个结果集

存储函数的作用是什么？

重用SQL代码：存储函数可以封装一段常用的SQL代码，使其可以在需要时被反复调用，从而避免了重复编写相同的代码提高性能：存储函数可以通过减少数据传输和减少网络延迟等方式，提高SQL语句的执行效率，从而提高数据库的性能提高安全性：存储函数可以使用存储过程语言提供的安全机制，例如变量声明、条件语句、循环语句等，从而提高数据库的安全性简化复杂的数据处理：存储函数可以帮助处理一些复杂的数据处理任务，例如计算平均值、总和等。通过存储函数，可以将复杂的处理过程封装在一个函数中，从而简化SQL代码和数据处理过程

存储函数的基本语法：

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])RETURNS type [characteristic ...]BEGIN-- SQL语句RETURN ...;END ;

characteristic说明：DETERMINISTIC：相同的输入参数总是产生相同的结果N0SQL：不包含SQL语句READS SOL DATA：包含读取数据的语句，但不包含写入数据的语句

示例

计算从1累加到n的值，n为传入的参数值

-- 创建存储函数create function fun1(n int)-- 声明存储函数的返回类型returns int deterministicbegindeclare total int default 0;-- 计算1~n的和while n > 0 doset total := total + n;set n := n - 1;end while;return total;end;select fun1(100);

注意：MySQL8.x二进制日志默认是开启的，BinLog二进制日志开启则必须添加charracteristic参数

4.4 触发器

4.4.1 触发器概述

什么是触发器？

触发器（Trigger）是一种用于在特定表上执行的特殊类型的存储过程。它是由数据库定义并自动执行的，不需要手动调用。触发器通常用于在数据库中定义一些自动化操作，例如在特定表上插入、更新或删除数据时触发某些操作。当满足特定的条件时，触发器会自动执行一些SQL语句。

触发器的组成

事件类型：触发器可在特定表上的INSERT、UPDATE或DELETE操作中触发执行。触发时机：触发器可在事件发生之前（BEFORE）或之后（AFTER）触发执行。触发器主体：触发器的主体包含执行的SQL语句，可以是一条或多条SQL语句。

触发器的作用？

数据约束：触发器可以用于实现数据约束，例如在插入、更新或删除数据时对数据进行验证，以确保数据的完整性和正确性。数据审计：触发器可以用于记录数据库操作的审计信息，例如记录用户在何时对数据库执行了哪些操作。数据修改：触发器可以用于在数据库中自动修改数据，例如在插入或更新数据时，自动更新相关的计算结果。数据同步：触发器可以用于在数据库中实现数据同步，例如在一个数据库中的表更新时，自动将更新同步到另一个数据库的相应表中。数据备份：触发器可以用于在数据库中实现数据备份，例如在对表进行修改时，自动将修改操作记录到备份表中，以便于数据恢复。

触发器的分类：

根据事件类型进行分类：根据触发器执行的事件类型，触发器可以分为INSERT、UPDATE和DELETE触发器。根据触发时机分类：根据触发器执行的时机，触发器可以分为BEFORE和AFTER触发器。BEFORE触发器在事件发生之前触发执行，而AFTER触发器在事件发生之后触发执行。根据触发对象分类：根据触发器的对象，触发器可以分为表级触发器和行级触发器。表级触发器在整个表上触发执行，而行级触发器在表的每一行上触发执行。根据触发次数分类：根据触发器执行的次数，触发器可以分为FOR EACH ROW和FOR EACH STATEMENT触发器。FOR EACH ROW触发器在每一行数据上触发执行，而FOR EACH STATEMENT触发器在每一条SQL语句执行一次。

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触

4.4.2 触发器的基本使用

创建

CREATE TRIGGER trigger_nameBEFORE/AFTER INSERT/UPDATE/DELETEON tbl_name FOR EACH ROW -- 行级触发器BEGINtrigger_stmt ; -- SQL语句END;

查看

SHOW TRIGGERS;

删除

DROP TRIGGER [schema_name]trigger_name; -- 如果没有指定schema_name，默认为当前数据库

示例

示例一：插入数据的触发器

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中, 包含增加,修改 , 删除 ;

①准备工作：准备一张 users_logs表

-- 准备工作 : 日志表 user_logsdrop table if exists user_logs;create table user_logs(id int(11) not null auto_increment,operation varchar(20) not null comment '操作类型, insert/update/delete',operate_time datetime not null comment '操作时间',operate_id int(11) not null comment '操作的ID',operate_params varchar(500) comment '操作参数',primary key(`id`))engine=innodb default charset=utf8;

②编写一个触发器

-- 删除已存在的同名触发器drop trigger if exists tb_user_insert_trigger;-- 创建插入触发器create trigger tb_user_insert_triggerafter insert on tb_user for each row -- 插入操作之后触发器执行begininsert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null, 'insert', now(), new.id, concat('插入的数据内容为: id=', new.id, ', name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));end;-- 查看当前数据库中所有的触发器show triggers;-- 往tb_user表中插入一条数据insert into tb_user(id, name, phone, email, profession, age, gender, status,createtime) VALUES (26,'三皇子','18809091212','erhuangzi@','软件工程',23,'1','1',now());

向MySQL中插入了一条数据后，是能够在 users_logs 中查看到相关操作的

示例二：修改数据的触发器

-- 删除已存在的同名触发器drop trigger if exists tb_user_update_trigger;-- 创建修改触发器create trigger tb_user_update_triggerafter update on tb_user for each row -- 更新操作之后触发器执行begininsert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null, 'update', now(), old.id, concat('更新之前的数据内容为: id=', old.id, ', name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,' | 更新之后的数据内容为: id=', new.id, ', name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));end;-- 查看当前数据库中所有的触发器show triggers;-- 修改tb_user表中的一条数据update tb_user set profession = '会计' where id = 2;update tb_user set profession = '会计' where id < 5; -- 此SQL语句，会导致触发器被触发5次，因为当前触发器是行级触发器

示例三：删除数据的触发器

-- 删除已存在的同名触发器drop trigger if exists tb_user_delete_trigger;-- 创建修改触发器create trigger tb_user_delete_triggerafter delete on tb_user for each rowbegininsert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null, 'delete', now(), old.id, concat('删除的数据内容为: id=', old.id, ', name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));end;-- 查看当前数据库中所有的触发器show triggers;-- 修改tb_user表中的一条数据delete from tb_user where id = 26;

5、锁

5.1 锁概述

什么是锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
简而言之：锁（Lock）是一种用于控制并发访问的机制。当多个用户同时访问同一数据时，锁可以保证数据的一致性和完整性。

锁的作用：

并发控制：锁可以用于控制并发访问，避免数据冲突。当多个用户同时访问同一数据时，锁可以保证数据的一致性和完整性。数据一致性：锁可以用于保证数据的一致性。当一个用户对数据进行修改时，其他用户无法对数据进行读取或修改操作，直到当前用户完成操作并释放锁为止。数据安全性：锁可以用于保证数据的安全性。当一个用户对数据进行修改时，其他用户无法对数据进行读取或修改操作，以保证数据不被恶意修改或删除。数据可靠性：锁可以用于保证数据的可靠性。当一个用户对数据进行修改时，其他用户无法对数据进行读取或修改操作，以避免数据被同时修改导致数据损坏或丢失。

锁的分类：

按照锁的粒度：锁可以分为表级锁和行级锁。表级锁可以锁定整个表，以控制对表的并发访问；行级锁可以锁定表中的单个行，以控制对行的并发访问。按照锁的类型：锁可以分为共享锁和排他锁。共享锁允许多个用户同时读取同一数据，但不允许任何用户对数据进行修改操作；排他锁只允许一个用户对数据进行修改操作，其他用户无法对数据进行读取或修改操作。按照锁的粒度和类型的组合：锁可以根据粒度和类型的组合进行分类，可以分为全局锁、表级锁和行级锁。全局锁锁定数据库中的所有表，表级锁每次操作锁住整张表，行级锁每次操作锁住对应的行数据。

锁的使用场景：

表级共享锁：适用于多个用户同时读取同一数据的场景，保证数据的一致性和完整性。例如，在备份或复制数据库时，需要对整个表进行共享锁定。表级排他锁：适用于需要对整个表进行修改操作的场景，保证数据的安全性和完整性。例如，在对整个表进行结构变更或数据清理时，需要对表进行排他锁定。行级共享锁：适用于多个用户同时读取同一数据的场景，但不允许任何用户对数据进行修改操作。例如，在查询操作中，需要对行进行共享锁定。行级排他锁：适用于需要对单个行进行修改操作的场景，保证数据的安全性和完整性。例如，在对单个行进行更新、删除或插入操作时，需要对行进行排他锁定。

5.2 全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

什么是逻辑备份？

逻辑备份是指将数据库中的数据通过SQL语句等逻辑方式进行备份的操作。逻辑备份通常生成一个包含数据库中所有数据的脚本文件，该文件包含一系列SQL语句，可以用于恢复数据库中的数据。

逻辑备份的优缺点

优点：灵活性和可读性较强，备份文件可以人工查看和编辑。它适用于小型数据库或需要对数据库进行部分备份的情况缺点：备份和还原时间较长，因为需要执行大量的SQL语句。同时，备份文件的大小也比物理备份文件要大，因为备份文件中包含了SQL语句等元数据信息

为什么全库逻辑备份需要加全局锁呢？

全库逻辑备份需要加全局锁是因为在备份过程中，备份工具需要读取整个数据库的数据并生成备份文件，而在此期间如果有其他用户对数据库进行修改操作，可能会导致备份数据的不一致性，从而影响备份的可靠性和完整性。因此，为了保证备份数据的一致性，需要在备份过程中对整个数据库加全局锁，以防止其他用户对数据库进行修改操作。

加全局锁

flush tables with read lock ;

数据备份

mysqldump -uroot -p32345678 mysql_study > mysql_study.sql

释放锁

unlock tables;

示例

①在窗口一种，给mysql_study加上全局锁：

②现在窗口二，只能进行读操作，不能进行写操作

③在窗口二种释放全局锁，然后进行写操作

④在窗口一种释放全局锁，这样就能在窗口二中能进行写操作了

⑤逻辑备份，注意逻辑备份的语句不算SQL语句，所以需要直接在命令行运行

mysqldump -h你的主机IP -u你的MySQL用户名 -p你的数据库密码数据库名 > 导出的文件# 比如：mysqldump -h192.168.88.141 -uroot -p32345678 mysql_study > mysql_study.sql

存在的问题：

如果在主库上备份，那么在备份期间不能执行更新操作，业务基本得停摆如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟，从而导致主从数据不一致

备注：在InnoDB引擎中，我们可以在备份时添加一个参数--single-transaction参数来完成不加锁的一致性数据库备份（MySQL底层是通过快照服务实现的）

mysqldump --single-transaction -h192.168.88.141 -uroot -p32345678 mysql_study > mysql_study.sql

5.3 表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

表级锁的分类：

表锁：表锁是MySQL中最简单的一种锁，它可以对整个表进行加锁。当一个用户对表进行修改时，可以使用表锁将整个表锁定，以防止其他用户对表进行读写操作，保证数据的一致性和完整性。表锁可以分为共享锁和排他锁两种类型。

元数据锁（meta data lock，MDL）：元数据锁是一种特殊的锁，它可以对数据库中的元数据^{2进行加锁，例如表结构、索引等。当一个用户对表结构进行修改时，可以使用元数据锁将表的元数据锁定，以防止其他用户对表进行读写操作，保证数据的一致性和完整性。主要是为了避免DML与DDL主键的冲突，保证读写的正确性}

意向锁：意向锁是一种特殊的锁，它不锁定任何数据，只用于指示事务或语句要锁定什么类型的锁。当一个事务或语句要锁定某个表或行时，会先尝试获取相应的意向锁，以告诉其他事务或语句这个表或行已经被锁定了，避免其他事务或语句重复加锁或进行无效的等待。意向锁分为意向共享锁和意向排他锁两种类型

注意：意向锁不会对表或行进行实际的加锁操作，只是用于指示事务或语句要锁定什么类型的锁

5.3.1 表锁

表锁的分类

表共享读锁（read lock）：表共享锁（Table shared lock）是一种锁定机制，用于控制并发访问数据库中的表。当一个事务需要读取一张表中的数据时，它会请求该表的共享锁，以确保读取操作不会与其他事务的更新操作发生冲突表独占写锁（write lock）：表独占锁（Table exclusive lock）是一种锁定机制，用于控制并发访问数据库中的表。当一个事务需要对一张表进行更新操作时，它会请求该表的独占锁，以确保更新操作不会与其他事务的读取或更新操作发生冲突

表共享锁和表独占锁的应用场景：

表独占锁的应用场景：

当一个事务需要更新一张表中的数据时，它会请求该表的独占锁，以防止其他事务同时对该表进行更新操作而导致数据不一致。当一个事务需要对一张表进行结构变更（如添加或删除列）时，它必须获取该表的独占锁，以防止其他事务同时对该表进行读取或更新操作而导致结构不一致。

表共享锁的应用场景：

当一个事务需要读取一张表中的数据时，它会请求该表的共享锁，以确保读取操作不会与其他事务的更新操作发生冲突。当一个事务需要对一张表进行读取操作，并且不需要对其进行更新操作时，它可以请求该表的共享锁，以允许其他事务同时读取该表。

备注：表独占锁和表共享锁都需要谨慎使用，以避免锁竞争和死锁等问题

加锁\释放锁的操作：

# 加锁-- 加表共享读锁lock tables 表名 read;-- 加表独占写锁lock tables 表名 write;# 释放锁unlock tables;

示例

示例一：

客户端一给student表加表共享读锁，然后客户端一和客户端二分别尝试进行读操作和写操作。
预期效果：客户端一和客户端二的读操作都成功，客户端一的写操作直接报错，客户端二的写操作处于阻塞状态，当客户端一释放了读锁时，客户端二的写操作执行成功（成功验证）

示例二：

客户端一给student添加表独占写锁，然后客户端一和客户端二分别进行读和写操作。
预期效果：客户端一的读和写操作都成功了，客户端二的读和写操作都处于阻塞状态（成功验证）

5.3.2 元数据锁

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。主要是为了避免DML与DDL冲突，保证读写的正确性。
这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变
更操作的时候，加MDL写锁(排他锁）。

常见的SQL操作时，所添加的元数据锁：

示例

示例一：

当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的

可以看到当我们在窗口一进行查询操作的同时，在窗口二进行查询操作和更新操作都是成功了，并没有报错，这是因为查询和更新操作都是共享锁（查询操作是共享读锁，更新操作是共享写锁），共享锁之间是兼容的

示例二：

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的

①窗口一开启事务，执行DQL语句（查询student表的所有数据），但是此时提交事务（一旦事务提交元数据锁就被释放了）

备注：MySQL默认是自动提交事务的，所以不显示开启事务，DQL语句一执行元数据锁就被释放了，所以为了更加明显地验证共享锁与排他锁是否发生冲突，这题需要显示地开启事务，当然，你也可以将MySQL事务的提交方式由自动改为手动提交

②窗口二执行DDL操作（为student表新增一个字段），可以发现操作执行后，进入了阻塞状态，这是由于DDL操作要给表添加排他锁，此时表已经由了共享锁，两种锁互斥，所以DDL操作需要等待共享被释放后才能添加排他锁

③窗口一提交事务，此时窗口一给student表添加的共享锁被释放了，所以此时窗口二处于阻塞状态的DDL直接可以给表添加排他锁，然后DDL就能够执行成功了

5.3.3 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查（意向锁是一种不与行级锁冲突表级锁）。
备注：意向锁（Intention Lock）是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁，一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

意向锁的分类

意向共享锁（Intent share lock，IS）：由语句select ... lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥意向排他锁（Intent exclude lock，IX）：由insert、update、delete、select...for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥，也就是说意向排他锁只与别家人（普通的排他 / 共享锁）互斥，不与自家人（意向锁）互斥

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：

①首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁

②当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较

有了意向锁之后：

①客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向

②而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况

示例

可以通过以下SQL，查看意向锁及行锁的加锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data fromperformance_schema.data_locks;

示例一：

检验意向共享锁与表读锁是兼容的

①客户端一添加意向共享锁

②客户端二查看意向锁和行锁的情况，然后为表添加一个表级共享锁

可以看到表级共享锁添加成功了，这说明意向共享锁与表级共享锁不冲突

③但是客户端二添加表级独占锁，发生了阻塞，这说明意向共享锁与表级共享锁发生了冲突

示例二：

检验意向排他锁与表读锁、写锁都是互斥的

略……

5.4 行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

5.4.1 行锁

行锁的分类共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁（与共享锁兼容，与排他锁互斥）排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁（与共享锁和与排他锁互斥）

备注：行锁（Intention Lock）是有数据引擎自己维护的，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应行锁，一旦事务提交了，共享锁、排他锁，都会自动释放

常见的SQL语句，在执行时，所加的行锁如下：

示例

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data fromperformance_schema.data_locks;

示例一：

普通的select语句，执行时，不会加锁

示例二：

select…lock in share mode，加共享锁，共享锁与共享锁之间兼容，共享锁与排他锁之间互斥，排它锁与排他锁之间互斥

①共享锁和共享锁是兼容的

②共享锁与排他锁是互斥的

略……

③排他锁与排他锁是互斥的

略……

示例三：

无索引行锁升级为表锁（这个在前面【3.8 update优化】已经演示过了）

①不为name建立索引，然后依据name来进行数据更新，此时行锁升级为表锁

②为name建立索引

5.4.2 间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key（临键锁）锁进行搜索和索引扫描，以防止幻读索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁（gap lock）索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock (临建锁)退化为间隙锁（gap lock）索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

示例

示例一：

索引上的等值查询(唯一索引)，给不存在的记录加锁时, 临键锁退化为间隙锁。
原因：当我们给一个不存在的值进行更新操作时，由于数据不存在会直接更新失败，如果不加间隙锁，此时其它用户插入这条数据，这就导致出现了幻读（之前更新没有这条记录，现在查询又有这条记录了），所以干脆直接将这个间隙给锁住，防止其它用户进行插入操作出现幻读现象，不使用临键锁是因为临键锁会将左右两边的那条记录也锁主，锁的范围太大了，没必要

此时如果我们插入记录 id为5、6、7这三条记录时，会进入阻塞状态

示例二：

索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock（临键锁）退化为间隙锁。
原因：因为是非唯一索引，这个结构中可能有多个6的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是8）。此时会对6加临键锁，并对8之前的间隙加锁

①根据非唯一索引进行等值查询（name字段有非唯一索引）

②此时，赵六这条记录会添加临键锁，然后6~8之间会添加间隙锁

示例三：

索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

查询的条件为id>=6，此时我们可以根据数据库表中现有的数据，将数据分为三个部分：[6]、(6,8]、(8,+∞]

所以数据库数据在加锁是，就是将6加了行锁，8的临键锁（临建锁包含8及8之前的间隙），正无穷的临键锁(正无穷及之前的间隙)

6、InnoDB引擎

6.1 innoDB概述

InnoDB的逻辑存储结构如下图所示：

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

关于 InnoDB的特点以及 InnoDB与其它存储引擎的区别可以看前面

备注：MySQL的数据存储目录cd /var/llib/mysql

6.2 InnoDB的架构

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。
详情请参考官网：MySQL8.0官方手册

下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构

6.2.1 内存结构

内存结构：

在左侧的内存结构中，主要分为这么四大块儿：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。接下来介绍一下这四个部分

Buffer Pool：缓存池，InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

空闲页（free page）：未被使用被使用页（clean page）：数据没有被修改过。脏页（dirty page）：数据被修改过，也中数据与磁盘的数据产生了不一致。

在专用服务器上，通常将多达80％的物理内存分配给缓冲池。参数设置：

# 查看缓存池大小（默认值134217728，约为1.3GB）show variables like 'innodb_buffer_pool_size';

Change Buffer：更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer存在的意义何在？
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

Adaptive Hash Index：自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持

hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。（自适应哈希索引，无需人工干预，是系统根据情况自动完成）

# 查看自适应hash是否开启(默认值ON，表示开启)show variables like 'hash_index';

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

# 查看日志缓冲区的大小（默认大小为16777216，约为16MB）show variables like 'innodb_log_buffer_size';# 查看日志刷新到磁盘的时机（三个取值，1、0、2）show variables like 'innodb_flush_log_at_trx_commit';-- 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。-- 0: 每秒将日志写入并刷新到磁盘一次。-- 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

6.2.2 磁盘结构

磁盘结构

System Tablespace：系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建

的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)

# 查看系统表空间（默认值 ibdata1:12M:autoextend ）show variables like 'innodb_data_file_path';

备注：系统表空间，默认的文件名叫ibdata1，可以通过cd /var/lib/mysql，进入MySQL数据存储目录，在这个目录可以看到 ibadata1 这个文件

File-Per-Table Tablespaces：每表文件表空间，如果开启了innodb_file_per_table开关，则每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中

# 查每表文件表空间（默认值为ON，表示开启）show variables like 'innodb_file_per_table';

General Tablespaces：通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间

# 创建表空间CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;-- 示例：CREATE TABLESPACE ts_mysql_study ADD DATAFILE 'mysql_study.ibd' ENGINE = innodb;# 创建表时指定表空间CREATE TABLE xxx ... TABLESPACE ts_name;-- 示例：create table test(id int primary key auto_increment, name varchar(10)) engine=innodb tablespace ts_mysql_study;

Undo Tablespaces：撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储

undo log日志（Undo Log 被称为撤销日志、回滚日志，记录回滚操作）

Temporary Tablespaces：临时表空间。存储用户创建的临时表等数据

Doublewrite Buffer Files：双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

备注：文件名#ib_xxx_xxx.dblwr

Redo Log：重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo logbuffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用

以循环方式写入重做日志文件，涉及两个文 ib_logfile0 和 ib_logfile1

6.2.3 后台线程

前面我们介绍了InnoDB的内存结构，以及磁盘结构，那么内存中我们所更新的数据，又是如何到磁盘中的呢？此时，就涉及到一组后台线程，接下来，就来介绍一些InnoDB中涉及到的后台线程

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread

Master Thread：核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回

IO Thread：在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回

# 查看查看到InnoDB的状态信息，其中就包含IO Thread信息show engine innodb status \G;

Purge Thread：主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收

Page Cleaner Thread：协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻

6.3 事务原理

什么是事务？

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务的ACID特性：

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久

实际上，我们研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性的。而对于这四大特性，实际上分为两个部分。其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。而持久性是通过数据库的**锁+MVCC**来保证的。我们在讲解事务原理的时候，主要就是来研究一下redo log，undo log以及MVCC

备注：

原子性：undo log持久性：redo log一致性：undo log + redo log隔离性：锁 + MVCC

6.3.1 redo log

什么是redolog?

redo log 被称作重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
当数据库执行写入操作时，redo log会记录下所做的修改。这些修改被记录下来之后，即使在写入操作未被写入到磁盘之前，数据库系统也可以通过redo log中的信息重新执行这些修改操作。
redo log通常是一个循环的、固定大小的文件，其中包含了数据库系统中最近的一些写入操作的信息。当redo log文件已满时，数据库系统会将其中的内容写入到磁盘上的数据文件中，并重新开始记录新的写入操作。

redolog的作用：

保证事务的持久性：redo log记录了所有修改数据库的操作，包括数据的插入、更新和删除等操作。这样，即使在事务提交之前，如果系统崩溃或断电，redo log中的信息也可以用来恢复数据，确保事务对数据库的修改是持久的。提高系统的性能：当数据库执行写入操作时，redo log可以将这些操作记录到内存中，而不必立即写入磁盘。这样可以避免频繁地访问磁盘，提高系统的性能。提高系统的可靠性：当系统崩溃或断电时，redo log可以用来恢复数据库，使其恢复到崩溃之前的状态。这样可以避免数据的丢失和损坏，提高系统的可靠性。支持热备份：redo log可以被用来支持热备份，即在运行中的数据库上进行备份。热备份可以避免对数据库的停机时间，对于需要24小时不间断运行的系统非常重要。

redolog的组成：

重做日志缓冲（redo log buffer）：是内存中的一块缓冲区，用于暂存即将写入重做日志文件中的重做日志记录。当数据库执行写入操作时，修改操作会被先写入重做日志缓冲中，而不是直接写入重做日志文件。这样能够减少磁盘I/O的开销，提高数据库的性能重做日志文件（redo log file）：是一个或多个固定大小的日志文件，用于持久化存储已经写入到重做日志缓冲中的重做日志记录。重做日志文件是循环使用的，当一个重做日志文件写满时，数据库会自动切换到下一个重做日志文件，以确保重做日志的连续性

如果没有redo log会怎样？
不加redo log的情况：
我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池（buffer pool），在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
添加redo log的情况：
有了redo log之后，我们在进行DML语句（增删改操作）时，数据照样先加载到缓冲池中，同时还会加入到 redo log buffer 中。在事务提交时，会将redo log buffer中的数据刷新到 redo log磁盘文件中。如果缓冲池(buffer pool) 在刷新脏数据到磁盘时，发生了错误，这时就可以借助 redo log 进行数据恢复了，从而避免脏数据的丢失，从而保证数据的持久化；如果buffer pool成功将树刷新到磁盘中，此时 redo log就没有作用了，就可以将这个 redo log文件删除
那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?
因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

6.3.2 undo log

什么是undolog？

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制)。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回
当数据库执行写入操作时，undo log会记录下所做的修改的反向操作，也就是撤销操作。这些反向操作被记录下来之后，可以用于回滚事务和支持MVCC。undo log通常是一个循环的、固定大小的文件，其中包含了数据库系统中最近的一些写入操作的反向操作。当undo log文件已满时，数据库系统会将其中的内容写入到磁盘上的数据文件中，并重新开始记录新的写入操作

undolog的作用：

支持事务的回滚：当数据库执行事务时，undo log可以记录下所有修改操作的反向操作，也就是撤销操作。如果事务执行失败或者需要回滚时，undo log中的信息可以被用来撤销所有的修改操作，使得数据库回滚到之前的状态。这样可以确保事务的一致性和完整性，保护数据的可靠性。支持MVCC机制：在MVCC（多版本并发控制）机制中，数据库系统会为每个事务创建一个独立的视图，用于隔离并发事务之间的读写操作。在这个视图中，undo log记录了事务开始之前的所有修改操作，这样可以保证事务在读取数据时获取到的是之前的版本，从而避免了脏读等问题。

undolog操作：

undolog销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。undolog存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segmenundolog版本链：每当一个事务被提交时，就会生成一个对应的undo日志记录。这些undo日志记录以链的形式连接在一起，形成undo log版本链

6.3.3 MVCC

本小节我们将要学习
认识MVCC数据库的三种隐藏字段readviewMVCC实现原理

什么是MVCC？

MVCC是数据库系统中的一种并发控制技术，全称为Multi-Version Concurrency Control，即多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readView。
在MVCC中，每个事务在开始时会创建一个独立的事务视图，该视图可以看作是一个数据库的快照^{3，它记录了当前事务开始时数据库中的所有数据。当事务需要读取某个数据时，会从该事务视图中获取数据的版本，并执行相应的操作。}
在MVCC中，每个数据都有多个版本，每个版本都有一个时间戳，用于标识该版本的创建时间。当事务执行写操作时，会创建一个新的数据版本，并将新版本的时间戳设置为当前时间戳。当事务执行读操作时，会根据当前事务的时间戳和数据版本的时间戳来选择合适的数据版本，从而保证事务读取到的数据是符合要求的。
MVCC机制在许多流行的数据库系统中得到广泛应用，如PostgreSQL、MySQL、Oracle等。

MVCC的作用？

支持并发读取操作：MVCC允许多个事务同时读取数据库的数据，不会因为读取操作而对数据的一致性造成影响。避免读取脏数据：在MVCC中，每个事务读取的是数据库中的一个快照（snapshot），而不是实时数据。这样可以避免读取到其他事务正在修改的数据，保证读取的数据是一致的。支持事务的回滚操作：在MVCC中，每个事务对数据库的修改操作都会被记录在undo log中，这样可以在事务回滚时，根据undo log中的信息将数据恢复到修改前的状态。支持并发写入操作：在MVCC中，每个事务对数据库的修改操作会生成一个新的版本（version），而不是直接修改原始数据。这样可以避免多个事务同时修改同一份数据时产生冲突，从而提高并发写入操作的效率。避免锁竞争：在MVCC中，每个事务对数据库的修改操作都不会对其他事务产生锁竞争的影响，不会出现死锁等问题。

相关概念

当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ...for update、update、insert、delete(排他锁)都是一种当前读快照读：简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞Read Committed：每次select，都生成一个快照读。Repeatable Read：开启事务后第一个select语句才是快照读的地方。Serializable：快照读会退化为当前

隐藏字段</

MVCC机制的实现离不开MySQL提供的三个隐藏字段，每当我们建立一张表，都会出现三个或两个隐藏字段（DB_ROW_ID只有在表没有指定主键时才会出现）

实例

之前我们建立了应该 mysql_study 的数据库，我们在该数据库中建立了一张名为 student 的表，该表具有主键 id，现在就让我们来看一看它的隐藏字段吧

Step1：进入改变的数据存储空间

cd /var/lib/mysql/mysql_study

Step2：查看表的结构

ibd2sdi student.ibd

版本链

下面是一张原始表：

DB_TRX_ID: 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的

DB_ROLL_PTR：由于这条数据是才插入的，没有被更新过，所以该字段值为null

然后，有四个并发事务同时在访问这张表

A. 第一步

当事务2执行第一条修改语句时，会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

B.第二步

当事务3执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

C. 第三步

当事务4执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本

最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

readview

ReadView（读视图）是快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

ReadView中包含了四个核心字段：

而在readview中就规定了版本链数据的访问规则， trx_id 代表当前undolog版本链对应事务 ID

不同的隔离级别，生成ReadView的时机不同：

READ COMMITTED(读已提交)：在事务中每一次执行快照读时生成ReadViewREPEATABLE READ（可重复读）：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

MVCC实现原理

RC隔离级别下，在事务中每一次执行快照读时生成ReadView

我们就来分析事务5中，两次快照读读取数据，是如何获取数据的? 在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下：

那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，到undolog版本链中匹配数据，最终决定此次快照读返回的数据

A. 先来看第一次快照读具体的读取过程

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

1）先匹配这条记录，这条记录对应的 trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足，都不满足，则继续匹配undo log版本链的下一条

2）再匹配第二条，这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也不满足，都不满足，则继续匹配undo log版本链的下一条

3）再匹配第三条这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据

B. 再来看第二次快照读具体的读取过程:

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

2）再匹配第二条这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②满足。终止匹配，此次快照读，返回的数据就是版本链中记录的这条数据

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的，那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了

我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该 ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样，那么最终快照读返回的结果也是一样的

所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。而一致性则是由redolog 与 undolog保证

7、MySQL管理

7.1 MySQL系统数据库

Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：

7.2 MySQL常用工具

本小节我们将要学习MySQL常用的几种工具
mysqlmysqladminmysqlbinlogmysqlshowmysqldumpmysqlimport/source

mysql

该mysql并并不是指MySQL服务，而是指MySQL客户端

语法: mysql [options] [database]选项:-u, --user=name #指定用户名-p, --password[=name] #指定密码-h, --host=name #指定服务器IP或域名-P, --port=port #指定连接端口-e, --execute=name #执行SQL语句并退出

-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便

示例：

# 查询db01数据库中的stu表mysql -h127.0.0.1 -P3306 -uroot –p123456 db01 -e "select * from stu";

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等

语法:mysqladmin [options] command ...选项:-u, --user=name #指定用户名-p, --password[=name] #指定密码-h, --host=name #指定服务器IP或域名-P, --port=port #指定连接端口

示例：

# 删除数据库test01mysqladmin -uroot –p1234 drop 'test01';# 查看mysql版本mysqladmin -uroot –p1234 version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具

语法:mysqlbinlog [options] log-files1 log-files2 ...选项:-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。-o, --offset=# 忽略掉日志中的前n行命令。-r,--result-file=name 将输出的文本格式日志输出到指定文件。-s, --short-form 显示简单格式，省略掉一些信息。--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。

示例：

# 查看二进制日志文件（如果使用cat查看，会出现乱码）mysqlbinlog mysql-bin.000101

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引

语法:mysqlshow [options] [db_name [table_name [col_name]]]选项: --count 显示数据库及表的统计信息（数据库，表均可以不指定）-i 显示指定数据库或者指定表的状态信息

示例：

# 查询test库中每个表中的字段数，及行数mysqlshow -uroot -p2143 test --count# 查询test库中book表的详细情况mysqlshow -uroot -p2143 test book --count

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句

语法：mysqldump [options] db_name [tables]mysqldump [options] --database/-B db1 [db2 db3...]mysqldump [options] --all-databases/-A连接选项：-u, --user=name 指定用户名-p, --password[=name] 指定密码-h, --host=name 指定服务器ip或域名-P, --port=# 指定连接端口输出选项：--add-drop-database 在每个数据库创建语句前加上 drop database 语句--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)-n, --no-create-db 不包含数据库的创建语句-t, --no-create-info 不包含数据表的创建语句-d --no-data 不包含数据-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

示例：

# 备份db01数据库(将数据库db01中的数据和结构拷贝到db01.sql文件)mysqldump -uroot -p1234 db01 > db01.sql # 含drop、create、insert语句# 备份db01数据库中的表数据，不备份表结构(-t)mysqldump -uroot -p1234 -t db01 > db01.sql # 只含insert语句# 将db01数据库的表的表结构与数据分开备份(-T)mysqldump -uroot -p1234 -T /root db01 score

注意：第三个示例会直接报一个错误，因为/root目录不是MySQL官方指定的目录（MySQL认为root目录不安全），所以需要将被备份的文件放到MySQL推荐的目录/var/lib/mysql-files，该目录可以通过show variables like '%secure_file_priv%';进行查看

mysqlimport/source

mysqlimport

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件

语法:mysqlimport [options] db_name textfile1 [textfile2...]

示例：

# 将city.txt文件中的数据导入到test表中mysqlimport -uroot -p2143 test /tmp/city.txt

source

如果需要导入sql文件,可以使用mysql中的source 指令

语法: source /root/xxxxx.sql