MySQL从入门到入土

白日梦专家
962 阅读1小时+

前言：

本文是基于掘金大佬的文章结合自己的一点理解，疑问解答梳理，归纳的，原文内容内容更详细

juejin.cn/post/714361…

他强由他强,清风拂山岗,他横由他横,明月照大江--九阴真经心法。

  • 一、整体架构

  •   1.1、架构图

MySQL与我们开发项目时相同，为了能够合理的规划整体架构设计，也会将整个MySQL服务抽象成几个大的模块，然后在内部进行实现，因此先来看看MySQL的整体架构，开局先上一张图：

从上往下看，依次会分为网络连接层、系统服务层、存储引擎层、以及文件系统层，往往编写SQL后，都会遵守着MySQL的这个架构往下走。

  • 连接层：主要是指数据库连接池，会负责处理所有客户端接入的工作。
  • 服务层：主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域。
  • 存储引擎层：这里是指MySQL支持的各大存储引擎，如InnoDB、MyISAM等。
  • 文件系统层：涵盖了所有的日志，以及数据、索引文件，位于系统硬盘上。

1.2、层级介绍

1.2.1、网络连接层

当一个客户端尝试与MySQL建立连接时，MySQL内部都会派发一条线程负责处理该客户端接下来的所有工作。而数据库的连接层负责的就是所有客户端的接入工作，MySQL的连接一般都是基于TCP/IP协议建立网络连接，因此凡是可以支持TCP/IP的语言，几乎都能与MySQL建立连接。

其实MySQL还支持另一种连接方式，就是Unix系统下的Socket直连，但这种方式一般使用的较少。

TCP网络连接建立成功后，MySQL服务端与客户端之间会建立一个session会话，紧接着会对登录的用户名和密码进行校验，MySQL首先会查询自身的用户表信息，判断输入的用户名是否存在，如果存在则会判断输入的密码是否正确，如若密码错误或用户名不存在就会返回1045的错误码。在用户名和密码都正确的情况下，MySQL还会做一些些小动作，也就是会进行授权操作，查询每个用户所拥有的权限，并对其授权，后续SQL执行时，都会先判断是否具备执行相应SQL语句的权限，然后再执行。

经过上述流程后数据库连接就建立成功了，数据库连接建立成功后，MySQL与客户端之间会采用半双工的通讯机制工作。

  • 全双工：代表通讯的双方在同一时间内，即可以发送数据，也可以接收数据。
  • 半双工：代表同一时刻内，单方要么只能发送数据，要么只能接受数据。
  • 单工：当前连接只能发送数据或只能接收数据，也就是“单向类型的通道”。

连接成功后，MySQL会将用于连接的线程释放，并安排另一条线程来维护当前客户端的连接。这样做的好处是可以有效地减少线程频繁地创建和销毁的开销，并提高系统的并发性能。当客户端结束连接时，MySQL会释放该连接所使用的线程以及其他相关资源。

1.2.2、系统服务层

MySQL大多数核心功能都位于这一层，包括客户端SQL请求解析、语义分析、查询优化、缓存以及所有的内置函数（例如：日期、时间、统计、加密函数...），所有跨引擎的功能都在这一层实现，譬如存储过程、触发器和视图等一系列服务。

  • 1.2.2.1、SQL接口

  •   主要作用就是负责处理客户端的SQL语句，当客户端连接建立成功之后，会接收客户端的SQL命令，比如DML、DDL语句以及存储过程、触发器等，当收到SQL语句时，SQL接口会将其分发给其他组件，然后等待接收执行结果的返回，最后会将其返回给客户端。

1.2.2.2、解析器

解析器这一步的作用主要是为了验证SQL语句是否正确，以及将SQL语句解析成MySQL能看懂的机器码指令。

1.2.2.3 、优化器

优化器的主要职责在于生成执行计划，比如选择最合适的索引，选择最合适的join方式等，最终会选择出一套最优的执行计划。优化器生成了执行计划后，维护当前连接的线程会负责根据计划去执行SQL，这个执行的过程实际上是在调用存储引擎所提供的API。

1.2.2.4、缓存&缓冲

这块较为有趣，主要分为了读取缓存与写入缓冲，读取缓存主要是指select语句的数据缓存，当然也会包含一些权限缓存、引擎缓存等信息，但主要还是select语句的数据缓存，MySQL会对于一些经常执行的查询SQL语句，将其结果保存在Cache中，因为这些SQL经常执行，因此如果下次再出现相同的SQL时，能从内存缓存中直接命中数据，自然会比走磁盘效率更高，对于Cache是否开启可通过命令查询。

  • show global variables like "%query_cache_type%";：查询缓存是否开启。
  • show global variables like "%query_cache_size%";：查询缓存的空间大小。

在高版本的MySQL中，移除了查询缓存区，毕竟命中率不高，而且查询缓存这一步还要带来额外开销，同时一般程序都会使用Redis做一次缓存，因此结合多方面的原因就移除了查询缓存的设计。（MySQL 8.0 版本正式移除了查询缓存（Query Cache）功能）

缓冲区的设计主要是：为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响。在数据库中读取某页数据操作时，会先将从磁盘读到的页存放在缓冲区中，后续操作相同页的时候，可以基于内存操作。

一般来说，当你对数据库进行写操作时，都会先从缓冲区中查询是否有你要操作的页，如果有，则直接对内存中的数据页进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后MySQL会在后台利用一种名为Checkpoint的机制，将内存中更新的数据刷写到磁盘。MySQL在设计时，通过缓冲区能减少大量的磁盘IO，从而进一步提高数据库整体性能。毕竟每次操作都走磁盘，性能自然上不去的。同时缓冲区是与存储引擎有关的，不同的存储引擎实现也不同，比如InnoDB的缓冲区叫做innodb_buffer_pool，而MyISAM则叫做key_buffer。

1.2.2.3、存储引擎层

存储引擎也可以理解成MySQL最重要的一层，在前面的服务层中，聚集了MySQL所有的核心逻辑操作，而引擎层则负责具体的数据操作以及执行工作。

存储引擎是MySQL数据库中与磁盘文件打交道的子系统，不同的引擎底层访问文件的机制也存在些许细微差异，引擎也不仅仅只负责数据的管理，也会负责库表管理、索引管理等，MySQL中所有与磁盘打交道的工作，最终都会交给存储引擎来完成。

1.2.2.4、文件系统层

这一层则是MySQL数据库的基础，本质上就是基于机器物理磁盘的一个文件系统，其中包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等各类MySQL运行时所需的文件，这一层的功能比较简单，也就是与上层的存储引擎做交互，负责数据的最终存储与持久化工作。这一层主要可分为两个板块：①日志板块。②数据板块。

1.2.2.4.1、日志模块

在MySQL中主要存在七种常用的日志类型，如下：

  • ①binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
  • ②redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
  • ③undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
  • ④error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。
  • ⑤general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
  • ⑥slow-log：慢查询日志，主要记录执行时间较长的SQL。
  • ⑦relay-log：中继日志，主要用于主从复制做数据拷贝。

1.2.2.4.2、数据模块

前面聊到过，MySQL的所有数据最终都会落盘（写入到磁盘），而不同的数据在磁盘空间中，存储的格式也并不相同，因此再列举出一些MySQL中常见的数据文件类型：

  • db.opt文件：主要记录当前数据库使用的字符集和验证规则等信息。
  • .frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。
  • .MYD文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。
  • .MYI文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。
  • .ibd文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。
  • .ibdata文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。
  • .ibdata1文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。
  • .ib_logfile0/.ib_logfile1文件：用于故障数据恢复时的日志文件。
  • .cnf/.ini：MySQL的配置文件，Windows下是.ini，其他系统大多为.cnf。

二、SQL生命周期详解

2.1、SQL的诞生

一条SQL的诞生都源自于一个用户请求，在开发程序时，SQL的大体逻辑我们都会由业务层的编码决定，具体的SQL语句则是根据用户的请求参数，以及提前定制好的“SQL骨架“（手写SQL、ORM框架自动生成）拼凑而成。当然，在Java程序或其他语言编写的程序中，只能生成SQL，而SQL真正的执行工作是需要交给数据库去完成的。

2.2、SQL执行前的经历

当尝试从连接池中获取连接时，如果此时连接池中有空闲连接，可以直接拿到复用，但如果没有，则要先判断一下当前池中的连接数是否已达到最大连接数，如果连接数已经满了，当前线程则需要等待其他线程释放连接对象，没满则可以直接再创建一个新的数据库连接使用。

当网络连接建立成功后，也就等价于在MySQL中创建了一个客户端会话，然后会发生下图一系列工作:

  • ①首先会验证客户端的用户名和密码是否正确：

    • 如果用户名不存在或密码错误，则抛出1045的错误码及错误信息。
    • 如果用户名和密码验证通过，则进入第②步。

  • ②判断MySQL连接池中是否存在空闲线程：

    • 存在：直接从连接池中分配一条空闲线程维护当前客户端的连接。
    • 不存在：创建一条新的工作线程（映射内核线程、分配栈空间....）。

  • ③工作线程会先查询MySQL自身的用户权限表，获取当前登录用户的权限信息并授权。

到这里为止，执行SQL前的准备工作就完成了，已经打通了执行SQL的通道，下一步则是准备执行SQL语句，工作线程会等待客户端将SQL传递过来。

2.3、SQL执行

2.3.1、读SQL

当 MySQL 服务器处理一个 SQL 查询时，会按照以下流程判断是否要记录到慢查询 SQL 日志： 1. 查询执行开始时，MySQL 服务器会记录当前时间戳。 2. 查询执行结束时，MySQL 服务器会再次记录当前时间戳，并计算两个时间戳之间的差值，即查询执行时间。 3. 如果查询执行时间超过慢查询阈值，MySQL 服务器会将该 SQL 查询的执行时间、数据库名称、用户名称、主机地址、SQL 语句等信息记录到慢查询 SQL 日志中。 4. 慢查询 SQL 日志可以通过 MySQL 配置文件中的 slow_query_log_file 参数指定记录的文件路径。 5. 如果慢查询 SQL 日志文件不存在，则会自动创建；如果已经存在，则会将新记录追加到文件末尾。 需要注意的是，启用慢查询日志功能会产生一定的性能损耗，因为 MySQL 服务器需要在查询结束时进行额外的时间计算和日志写入操作。因此，在生产环境中，应该根据实际情况权衡利弊，避免影响数据库性能。

2.3.2、写SQL

由于CPU和磁盘之间的性能差距实在过大，因此MySQL中会在内存中设计一个「缓冲区」的概念，主要目的是在于弥补CPU与磁盘之间的性能差距。

任何一条写入类型的SQL都是有状态的，也就代表着只要是会对数据库发生更改的SQL，执行时都会被记录在日志中。首先所有的写SQL在执行之前都会生成对应的撤销SQL，撤销SQL也就是相反的操作，比如现在执行的是insert语句，那这里就生成对应的delete语句....，然后记录在undo-log撤销/回滚日志中。但除此之外，还会记录redo-log日志。

Mysql并不会真正用这种方式去记录UndoLog，而是使用MVCC机制去实现的，具体详情请参照下面的MVCC详解。

redo-log日志是InnoDB引擎专属的，主要是为了保证事务的原子性和持久性，这里会将写SQL的事务过程记录在案，如果服务器或者MySQL宕机，重启时就可以通过redo_log日志恢复更新的数据。在「写SQL」正式执行之前，就会先记录一条prepare状态的日志，表示当前「写SQL」准备执行，然后当执行完成并且事务提交后，这条日志记录的状态才会更改为commit状态

除开上述的redo-log、undo-log日志外，同时还会记录bin-log日志，这个日志和redo-log日志很像，都是记录对数据库发生更改的SQL，只不过redo-log是InnoDB引擎专属的，而bin-log日志则是MySQL自带的日志。

MySQL bin-log是MySQL数据库的二进制日志，它记录了数据库所有的更新操作，包括插入、更新和删除操作等。 MySQL bin-log的作用主要有以下三点： 1. 数据备份与恢复。bin-log可以用于实现数据备份和恢复，可以通过解析bin-log文件，将所有的数据更改操作记录下来，从而可以还原MySQL数据库到指定时间点的状态。 2. 数据复制与主从同步。MySQL bin-log可以用于实现数据库的主从复制和同步，即将主数据库的bin-log文件同步到从数据库中，从而保证从数据库的数据与主数据库是一致的。 3. 数据恢复。如果因为某些原因导致数据库数据丢失或者损坏，可以通过bin-log文件进行数据恢复，从而避免数据的永久性损失。

不过无论是什么日志，都需要在磁盘中存储，而本身「写SQL」在磁盘中写表数据效率就较低了，此时还需写入多种日志，效率定然会更低。对于这个问题MySQL以及存储引擎的设计者自然也想到了，所以大部分日志记录也是采用先写到缓冲区中，然后再异步刷写到磁盘中。

对于这点则是由刷盘策略来决定的，redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制，而bin-log日志的刷盘策略则可以通过sync_binlog参数控制：

  • innodb_flush_log_at_trx_commit：

    • 0：间隔一段时间，然后再刷写一次日志到磁盘（性能最佳）。
    • 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。
    • 2：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。

  • sync_binlog：

    • 0：同上述innodb_flush_log_at_trx_commit参数的2。

    • 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交事务都会刷盘，默认策略。

    •   2.4、SQL执行完返回

    •   2.4.1、读SQL返回

    •   MySQL执行一条查询SQL时，数据是逐条返回的模式，因为如果等待所有数据全部查出来之后再一次性返回，必然会导致撑满内存。不过这里的返回，并不是指返回客户端，而是指返回SQL接口，因为从磁盘中检索出目标数据时，一般还需要对这些数据进行再次处理。从行记录中筛选出最终所需的结果字段，这个工作是在SQL接口中完成的，也包括多表联查时，数据的合并工作，同样也是在SQL接口完成，其他SQL亦是同理。当查询到数据后，在正式向客户端返回之前，还会顺手将结果集放入到缓存中。

    • 还有一点需要牢记：就算没有查询到数据，也会将执行状态、执行耗时这些信息返回给SQL接口，然后由SQL接口向客户端返回NULL。

    •   2.4.2、写SQL返回

    •   写SQL执行的过程会比读SQL复杂，但写SQL的结果返回却很简单，写类型的操作执行完成之后，仅会返回执行状态、受影响的行数以及执行耗时。

    •   2.4.3、结果返回客户端

    •   整体架构中介绍过，由于执行当前SQL的工作线程，本身也维护着一个数据库连接，这个数据库连接实际上也维持着客户端的网络连接，当结果集处理好了之后，直接通过Host中记录的地址，将结果集封装成TCP数据报，然后返回即可。数据返回给客户端之后，除非客户端主动输入exit等退出连接的命令，否则连接不会立马断开。如果要断开客户端连接时，又会经过TCP四次挥手的过程。不过就算与客户端断开了连接，MySQL中创建的线程并不会销毁，而是会放入到MySQL的连接池中，等待其他客户端复用当前连接。一般情况下，一条线程在八小时内未被复用，才会触发MySQL的销毁工作。

    •   三、SQL库表设计之范式

    •   设计DB库表结构时，也有一些共同需要遵守的规范，这些规范在数据库设计中被称为“范式”，理解并掌握这些设计时的规范，能让咱们在项目之初，设计的库表结构更为合理且优雅。数据库范式中，声名远扬的有三大范式，但除此之外也有一些其他设计规范，如：

    • ①数据库三大范式（1NF、2NF、3NF）

    • ③第四范式(4NF）和第五范式：完美范式（5NF）

    • ②巴斯-科德范式（BCNF）

    • ④反范式设计

    •   3.1、数据库三大范式

    •   三大范式之间，它们是递进的关系，也就是后续的范式都基于前一个范式的基础上推行。

    •   3.1.1、第一范式（1NF）

    •   库表设计时的第一范式，主要是为了确保原子性的，也就是存储的数据具备不可再分性。

    •   简单来说，如果按照原本那张形式去做业务开发，显然操作起来会更加麻烦且复杂一些，但第一范式的原子性，除开对列级别生效之外，行级别的数据也是同理，也就是每一行数据之间是互不影响的，都是独立的一个整体。

    •   确保原子性，表中每一个列数据都必须是不可再分的字段。

    •   3.1.2、第二范式（2NF）

    •   第二范式的要求表中的所有列，其数据都必须依赖于主键，也就是一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系。

    •   确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。

    •   3.1.3、第三范式（3NF）

    •   第三范式要求表中每一列数据不能与主键之外的字段有直接关系。

    •   确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

    •   3.2、其他范式

    •   3.2.1、巴斯-科德范式（BCNF）

    •   巴斯-科德范式也被称为3.5NF，至于为何不称为第四范式，这主要是由于它是第三范式的补充版，第三范式的要求是：任何非主键字段不能与其他非主键字段间存在依赖关系，也就是要求每个非主键字段之间要具备独立性。而巴斯-科德范式在第三范式的基础上，进一步要求：任何主属性不能对其他主键子集存在依赖。

    •   第三范式只要求非主键字段之间，不能存在依赖关系，但没要求联合主键中的字段不能存在依赖，因此第三范式并未考虑完善，巴斯-科德范式修正的就是这点。

    •   3.2.2、第四范式（4FN）

    •   第四范式是基于BC范式之上的，但在理解第四范式之前，首先得理解“多值依赖”的概念。

    • 一个表中至少需要有三个独立的字段才会出现多值依赖问题，多值依赖是指表中的字段之间存在一对多的关系，也就是一个字段的具体值会由多个字段来决定。

    • SELECT * FROM `zz_course_scheduling`;
+--------+------------+--------------+---------------------------+
| course |   classes  | teacher      | book                      |
+--------+------------+--------------+---------------------------+
| 语文   | 计算机一班 | 竹熊老师     | 人教版-新课标教材         |
| 语文   | 计算机二班 | 黑竹老师     | 人教版-现行教材           |
| 语文   | 计算机三班 | 竹熊老师     | 北师大版教材              |
| 数学   | 计算机一班 | 熊竹老师     | 人教版-新课标教材         |
| 英语   | 计算机一班 | 黑熊老师     | 人教版-新课标教材         |
+--------+------------+--------------+---------------------------+

    •   述是一张教师排课表，分别有课程、班级、老师、教材四个字段，一个课程会有多位老师授课，同时一个课程也会有多个版本的教材，此时就无法只根据课程、班级、老师任一字段决定教材字段的值，而是要结合班级、课程、老师三个字段，才能确定教材字段的值，比如计算机一班的语文课程，竹熊老师来上，用的是人教版-新课标教材，因此目前教材字段也存在多值依赖的问题，依赖于班级、课程、老师三个字段。

    •   也正是由于多值依赖的情况出现，又会导致表中出现时数据冗余、新增、删除异常等问题出现。

    •   因此第四范式的定义就是要消除表中的多值依赖关系。怎么做呢？拿前面的权限表举例。

    • SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
+---------+-----------+----------+----------+---------------------+

SELECT * FROM `zz_roles`;
+---------+-----------+---------------------+
| role_id | role_name | created_time        |
+---------+-----------+---------------------+
|       1 | ROOT      | 2022-08-14 15:12:00 |
|       2 | ADMIN     | 2022-08-14 15:12:00 |
|       3 | USER      | 2022-08-14 15:12:00 |
+---------+-----------+---------------------+

SELECT * FROM `zz_permissions`;
+---------------+-----------------+---------------------+
| permission_id | permission_name | created_time        |
+---------------+-----------------+---------------------+
|             1 | *               | 2022-08-14 15:12:00 |
|             2 | BACKSTAGE       | 2022-08-14 15:12:00 |
|             3 | LOGIN           | 2022-08-14 15:12:00 |
+---------------+-----------------+---------------------+

SELECT * FROM `zz_users_roles`;
+----+---------+---------+
| id | user_id | role_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       1 |       3 |
|  4 |       2 |       2 |
|  5 |       2 |       3 |
|  6 |       3 |       3 |
+----+---------+---------+

SELECT * FROM `zz_roles_permissions`;
+----+---------+---------------+
| id | role_id | permission_id |
+----+---------+---------------+
|  1 |       1 |             1 |
|  2 |       2 |             2 |
|  3 |       3 |             3 |
+----+---------+---------------+

    •   观察上述的五张表，如果有做过权限设计，或用过Shiro框架的小伙伴应该会感到额外的亲切，这个正是大名鼎鼎的权限五表，将原本的用户角色权限表，拆分成了用户表、角色表、权限表、用户角色关系表、角色权限关系表。经过这次拆分之后，一方面用户表、角色表、权限表中都不会有数据冗余，第二方面无论是要删除亦或新增一个角色、权限时，都不会影响其他表。

    •   3.2.3、范式开发准则

    •   实际开发中，对于库表的设计最高满足BC范式即可，再往后就没意义了，因为表数量一多，查询也好，写入也罢，性能会越来越差。

    •   3.3、数据库反范式设计

    •   遵循数据库范式设计的结构优点很明显，它避免了大量的数据冗余，节省了大量存储空间，同时让整体结构更为优雅，能让SQL操作更加便捷且减少出错。但随着范式的级别越高，设计出的结构会更加精细化，原本一张表的数据会被分摊到多张表中存储，表的数量随之越来越多。

    •   但随之而来的不仅仅只有好处，也存在一个致命问题，也就是当同时需要这些数据时，只能采用联表查询的形式检索数据，有时候甚至为了一个字段的数据，也需要做一次连表查询才能获得。这其中的开销无疑是花费巨大的，尤其是当连接的表不仅两三张而是很多张时，有可能还会造成索引失效，这种情况带来的资源、时间开销简直是一个噩梦，这会严重地影响整个业务系统的性能。

    •   遵循范式设计也好，反范式设计也罢，本身两者之间并没有优劣之分，只要能够对业务更有利，那就可以称之为好的设计方案。范式的目的仅在于让我们设计的结构更优雅合理，有时候在表中多增加一个字段，从数据库的角度来看，数据会存在冗余问题，会让表结构违反范式的定义，但如若能够在实际情况中减少大量的连表查询，这种设计自然也是可取的。也就是说，在设计时千万不要拘泥于规则之内，一定要结合实际业务考虑，遵循业务优先的原则去设计结构。

    • 牢记的一点是：不是所有不遵循数据库范式的结构设计都被称为反范式，反范式设计是指自己知道会破坏范式，但对业务带来好处大于坏处时，刻意设计出破坏范式的结构。随意设计出的结构，不满足范式要求，同时还无法给业务上带来收益的，这并不被称为反范式设计，反范式设计是一种刻意为之的思想。

    •   四、MySql索引

    •   索引本质是是一种数据结构，最终以文件的信息存储在磁盘上，不同的索引期内部数据接口也不相同，我们常见的有B+Tree索引、hash索引等。索引的目的就是为了减少查询次数，减少磁盘IO，利用数据结构、缓存、缓冲区等提升数据检索效率。

    •   4.1、概述    索引本身是一把双刃剑，用的好能够给我们带来异乎寻常的查询效率，用的不好则反而会带来额外的磁盘占用及写入操作时的维护开销。因此大家一定要切记，既然选择建了索引，那一定要利用它，否则还不如干脆别建，既能节省磁盘空间，又能提升写入效率。    MySQL可以通过CREATE、ALTER、DDL三种方式创建一个索引。

    •     4.1.1、索引创建方式--create语句

    • CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);

    •     4.1.2、索引创建方式--alert语句

    • ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);

    •     4.1.3、索引创建方式--DDL语句

    • CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);

    •     4.1.4、SQL执行指定索引

    •     ORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询，但要牢记的是：如果当前的查询**SQL**压根不会走指定的索引字段，哪这种方式是行不通的，这个关键字的用法是：一条查询语句在有多个索引可以检索数据时，显式指定一个索引，减少优化器选择索引的耗时。

    • SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

    •   4.2、索引的分类

    •   聚簇索引、非聚簇索引、唯一索引、主键索引、联合索引、全文索引、单列索引、多列索引、复合索引、普通索引、二级索引、辅助索引、次级索引、有序索引、B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、空间索引、前缀索引......以下会从不同的角度来解析。

    •   4.2.1、数据结构层次

    • B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。

    • Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。

    • R-Tree类型：MyISAM引擎支持，也就是空间索引的默认结构类型。

    • T-Tree类型：NDB-Cluster引擎支持，主要用于MySQL-Cluster服务中。

    •   除开列出的几种索引结构外，MySQL索引支持的数据结构还有R+、R*、QR、SS、X树等结构。

    •   索引到底支持什么数据结构，这是由存储引擎决定的，不同的存储引擎支持的索引结构也并不同，目前较为常用的引擎就是MyISAM、InnoDB，因此大家未曾听说后面列出的这些索引结构也是正常的。

    •   创建索引时，其默认的数据结构就为B+Tree，如何更换索引的数据结构

    • CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;

    •   4.2.2、字段数量层次

    •   单列索引也会分为很多类型，比如：

    • 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。

    • 主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。

    • 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。

    • .....还有很多很多，只要是基于单个字段建立的索引都可以被称为单列索引。

    •   多列索引的概念前面解释过了，不过它也有很多种叫法，例如：

    • 组合索引、联合索引、复合索引、多值索引....

    •   4.2.3、功能逻辑层次

    •   功能逻辑划分索引类型，这也是最常见的划分方式，从这个维度来看主要可划分为五种：普通索引、唯一索引、主键索引、全文索引、空间索引。

    •   全文索引和空间索引都是MySQL5.7版本后开始支持的索引类型，不过这两种索引都只有MyISAM引擎支持，其他引擎要么我没用过，要么就由于自身实现的原因不支持，例如InnoDB。对于全文索引而言，其实在MySQL5.6版本中就有了，但当时并不支持汉字检索，到了5.7.6版本的时候才内嵌ngram全文解析器，才支持亚洲语种的分词，同时InnoDB引擎也开始支持全文索引，在5.7版本之前，只有MyISAM引擎支持。

    • 全文索引类似于ES、Solr搜索中间件中的分词器，或者说和之前常用的like+%模糊查询很类似，它只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上，而且使用全文索引查询时，条件字符数量必须大于3才生效。

    • 空间索引这玩意儿其实用的不多，至少大部分项目的业务中不会用到，想要弄清楚空间索引，那么首先得知道一个概念：GIS空间数据，GIS是什么意思呢？是地理信息系统，这是一门新的学科，基于了计算机、信息学、地理学等多科构建的，主要就是用于管理地理信息的数据结构，在国土、规划、出行、配送、地图等和地理有关的项目中，应用较为频繁。

    • 地理空间数据主要包含矢量数据、3D模型、影像文件、坐标数据等，说简单点，空间数据也就是可以将地理信息以模型的方式，在地图上标注出来。在MySQL中总共支持GEOMETRY、POINT、LINESTRING、POLYGON四种空间数据类型，而空间索引则是基于这些类型的字段建立的，也就是可以帮助我们快捷检索空间数据。（也有很多类似的，比如Redis、ES、Mongo等支持的geo数据类型）

    •   4.2.4、存储方式层次

    •   存储方式来看，MySQL的索引主要可分为两大类：

    • 聚簇索引：也被称为聚集索引、簇类索引

    • 非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引

    •   聚簇索引中，索引数据和表数据在磁盘中的位置是一起的，而非聚簇索引则是分开的，索引节点和表数据之间，用物理地址的方式维护两者的联系。

    •   不过一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引，或者称之为辅助索引、次级索引。但也不要走进一个误区，虽然MySQL默认会使用主键上建立的索引作为聚簇索引，但也可以指定其他字段上的索引为聚簇索引，一般聚簇索引要求索引必须是非空唯一索引才行。

    • 其实就算表中没有定义主键，InnoDB中会选择一个唯一的非空索引作为聚簇索引，但如果非空唯一索引也不存在，InnoDB隐式定义一个主键来作为聚簇索引。

    • 回表查询正是因为SQL查询走的索引是非聚簇索引，非聚簇索引的叶子节点存放的是指针（指向聚簇索引的字段），真正的数据在聚簇索引的叶子节点上，所以要查询到数据，需要先走一遍非聚簇索引找到指针，再走一遍聚簇索引找到数据，这就是所谓的回表。

    •   4.3、全文索引 的使用

    •   MySQL版本必须要在5.7及以上，同时使用时也需要手动指定，一起来先看看如何创建全文索引，此时需要使用FULLTEXT关键字。

    •   4.3.1、创建

    •   在创建全文索引时，有三个注意点：

    • 5.6版本的MySQL中，存储引擎必须为MyISAM才能创建。

    • 创建全文索引的字段，其类型必须要为CHAR、VARCHAR、TEXT等文本类型。

    • 如果想要创建出的全文索引支持中文，需要在最后指定解析器：with parser ngram。

    •   普通全文索引：

    • -- 方式①
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);

-- 方式②
CREATE FULLTEXT INDEX indexName ON tableName(columnName);

    •   支持中文的全文索引：

    • ALTER TABLE 
    zz_article ADD 
FULLTEXT INDEX 
    ft_article_name(article_name) 
WITH PARSER NGRAM;

    •   4.3.2、使用

    •   在使用全文索引之前需要先了解两个概念：最小搜索长度和最大搜索长度，以及几个重要参数：

    •   其中的几个重要参数：

    • ft_min_word_len：使用MyISAM引擎的表中，全文索引最小搜索长度。

    • ft_max_word_len：使用MyISAM引擎的表中，全文索引最大搜索长度。

    • ft_query_expansion_limit：MyISAM中使用with query expansion搜索的最大匹配数。

    • innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小搜索长度。

    • innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大搜索长度。

    • 对于长度小于最小搜索长度和大于最大搜索长度的词语，都无法触发全文索引。

    • 最小值可以手动调整为1，MyISAM引擎的最大值可以调整为3600，但InnoDB引擎最大似乎就是84。

    •   全文索引中有两个专门用于检索的关键字，即MATCH(column)、AGAINST(关键字)，同时这两个检索函数也支持三种搜索模式：

    • 自然语言模式(默认搜索模式)

    • 布尔搜索模式

    • 查询拓展搜索

    •   MATCH()主要是负责指定要搜索的列，这里要指定创建全文索引的字段，AGAINST()则指定要搜索的关键字，也就是要搜索的词语，接下来简单的讲一下三种搜索模式。

    •   4.3.2.1、自然语言模式

    • SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL');

    •   4.3.2.2、布尔搜索模式

    •   布尔搜索模式有些特殊，因为在这种搜索模式中，还需要掌握特定的搜索语法：

    • +：表示必须匹配的行数据必须要包含相应关键字。

    • -：和上面的+相反，表示匹配的数据不能包含相应的关键字。

    • >：提升指定关键字的相关性，在查询结果中靠前显示。

    • <：降低指定关键字的相关性，在查询结果中靠后显示。

    • ~：表示允许出现指定关键字，但出现时相关性为负。

    • *：表示以该关键字开头的词语，如A*，可以匹配A、AB、ABC....

    • ""：双引号中的关键字作为整体，检索时不允许再分词。

    • "X Y"@n：""包含的多个词语之间的距离必须要在n之间，单位-字节，如：

      • 竹子 熊猫@10：表示竹子和熊猫两个词语之间的距离要在10字节内。
    • -- 查询文章名中包含 [MySQL] 但不包含 [设计] 的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL -设计' IN BOOLEAN MODE);

-- 查询文章名中包含 [MySQL] 和 [篇] 的数据，但两者间的距离不能超过10字节
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL 篇"@10' IN BOOLEAN MODE);
    
-- 查询文章名中包含[MySQL] 的数据，
--    但包含 [执行] 关键字的行相关性要高于包含 [索引] 关键字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL +(>执行 <索引)' IN BOOLEAN MODE);

-- 查询文章名中包含 [MySQL] 的数据，但包含 [设计] 时则将相关性降为负
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL ~设计' IN BOOLEAN MODE);

-- 查询文章名中包含 [执行] 关键字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('执行*' IN BOOLEAN MODE);

-- 查询文章名中必须要包含 [MySQL架构篇] 关键字的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL架构篇"' IN BOOLEAN MODE);

    •   4.3.2.3、查询拓展搜索

    •   查询拓展搜索其实是对自然语言搜索模式的拓展，比如举个例子：

    • SELECT 
    COUNT(article_id) AS '搜索结果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL' WITH QUERY EXPANSION);

    • 全文索引存在的几个主要意义： 高效的全文搜索：全文索引可以让用户在文本内容中快速查找到所需的信息，而不需要逐个检索每个条目。相比于模糊查询，全文索引可以提供更快的响应速度和更准确的搜索结果。 支持复杂查询：全文索引支持复杂的查询操作，如布尔查询、短语查询、模糊查询等，这些查询操作可以进一步提高搜索的准确性和效率。 处理大量文本数据：当需要处理大量文本数据时，全文索引可以提供更高效的数据检索和分析能力，从而满足大规模数据处理的需求。 支持多语言搜索：全文索引可以支持多种语言的搜索，这在国际化应用中尤其重要。 总之，全文索引在某些情况下可以提供更高效、更准确和更灵活的搜索能力，因此在需要进行高效文本搜索和分析的应用中具有重要的意义。

    • 全文索引和模糊查询两者并非完全相同，它们适用于不同的场景，有不同的优点和局限性。 全文索引是一种基于倒排索引的算法，在特定的文本领域中进行搜索和匹配，可以实现多字段全文检索、高亮显示、权重排序、模糊查询等功能。它可以快速的找到包含匹配关键词的文本记录，并对匹配的内容进行高亮显示，使得用户可以更加方便地查找到自己需要的信息。与普通索引不同的是，全文索引在构建索引时，对文本内容进行了分词和处理，以便在搜索时更加精准地匹配。它适用于长文本、多字段、多语言等场景，并且支持丰富的查询语法和扩展性。 相比于全文索引，模糊查询更加适用于关键词未知或者不确定的场景。它可以使用包含通配符的表达式进行查询，进行模糊匹配。但是，模糊查询存在的主要问题是效率低，随着数据量的增大，查询速度会变得越来越慢。此外，模糊查询无法处理复杂的查询语句，例如带有文本逻辑操作符的查询，也无法很好地解决多语言的问题等。 因此，全文索引和模糊查询两者之间并不矛盾，它们都是用于解决不同的查询问题的。在实际应用中，我们可以根据具体的业务需求和查询场景，选择合适的索引技术来提高查询效率和准确性。

    •   4.4、索引优劣分析

    •   引入索引机制后，能够给数据库带来的优势很明显：

    • ①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。

    • ②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。

    • ③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。

    • ④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。

    • ⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。

    • ⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

    •   看着上面一条又一条的好处，似乎感觉索引好处很大啊，对于这点确实毋庸置疑，但只有好处吗？No，同时也会带来一系列弊端，如：

    • ①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。

    • ②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。

    • ③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

    •   当然，但对数据库整体来说，索引带来的优势会大于劣势。不过也正由于索引存在弊端，因此索引不是越多越好，合理建立索引才是最佳选择。

    • 在MySQL中，一个表只能有一个聚簇索引，因此如果表中已经有一个聚簇索引，则其他索引都是非聚簇索引。下列情况可能会使用非聚簇索引： 1. 主键或唯一索引被定义为BLOB、TEXT或VARBINARY类型，MySQL将使用普通索引来代替聚簇索引。 2. 当主键或唯一键的值被频繁更新时，聚簇索引的性能会下降，因为需要重新排序索引。在这种情况下，非聚簇索引可能是更好的选择。 3. 当需要按照非唯一值进行排序或分组时，非聚簇索引可能更适合。 综上所述，非聚簇索引多用于搜索、排序、聚合操作等，而聚簇索引则更适合于频繁修改的表。

    • 在创建表时，可以通过PRIMARY KEY或UNIQUE关键字来指定主键或唯一索引，从而指定聚簇索引。例如： sql `` CREATE TABLE example ( `` id INT PRIMARY KEY, `` name VARCHAR(50), `` age INT `` ); ``如果需要创建非唯一索引，则需要使用`INDEX` 关键字，并可以选择是否为聚簇索引。例如： sql CREATE TABLE example ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX age_index(age) -- 非聚簇索引 ) ENGINE=InnoDB; ``可以在`CREATE INDEX`或`ALTER TABLE`语句中使用`CLUSTERING` 关键字来指定聚簇索引。例如： sql CREATE INDEX age_index ON example(age) CLUSTERING; ALTER TABLE example ADD INDEX age_index(age) CLUSTERING; ``` ``需要注意的是，CLUSTERING关键字只能用于InnoDB存储引擎。MyISAM存储引擎不支持聚簇索引。

    •   4.4.1、主键索引--为什么推荐数据库自增ID

    •   一张表中大多数情况下，会将主键索引以聚簇的形式存在磁盘中，聚簇索引在存储数据时，表数据和索引数据是一起存放的。同时，MySQL默认的索引结构是B+Tree，也就代表着索引节点的数据是有序的。如果使用UUID作为主键，那么每当插入一条新数据，都有可能破坏原本的树结构，几乎每次插入都有可能导致树结构要调整。但使用自增ID就不会有这个问题，所有新插入的数据都会放到最后。

    •   因此大家数据表的主键，最好选用带顺序性的值，否则有可能掉入主键索引的“陷阱”中。

    •   4.4.2、联合索引存在的矛盾

    •   联合索引需要满足最左匹配原则

    •   比如一个表存在联合索引（a,b,c）,查询条件是（a,c）只能使用条件a的索引。查询条件（b,c）是无法使用索引的（但实际上这条规则也并不是100%遵循的。具体参照4.7 中，MySql 8.x版本推出的Index Skip Scan）。

    •   MySQL的最左前缀原则，匹配到范围查询时会停止匹配，比如>、<、between、like这类范围条件，并不会继续使用联合索引，举个例子：

    • SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="...";

    •   当执行时，虽然上述SQL使用到X、Y、Z作为查询条件，但由于Y字段是>范围查询，因此这里只能使用X索引，而不能使用X、Y或X、Y、Z索引。

    •   4.5、建立索引的原则

    •   建立索引时，需要遵守的一些原则：

    • ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。

    • ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。

    • ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。

    • ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。

    • ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。

    • ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。

    • ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。

    • ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

    •   同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

    • ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。

    • ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。

    • ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。

    • ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。

    • ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

    • ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。

    • ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

    •   对于索引机制，在建立时应当参考上述给出的意见，这每一条原则都是从实际经验中总结出来的，前面八条不一定要全面思考，但后面七条注意点，一定要牢记，如若你的索引符合后面七条中的描述，那一定要更改索引。

    •   4.6、索引失效与正确使用姿势

    •   4.6.1、执行分析工具--ExPlain

    • EXPLAIN SELECT * FROM `zz_users`;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | zz_users | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+

    • id：这是执行计划的ID值，这个值越大，表示执行的优先级越高。

    • select_type：当前查询语句的类型，有如下几个值：

      • simple：简单查询。
      • primary：复杂查询的外层查询。
      • subquery：包含在查询语句中的子查询。
      • derived：包含在FROM中的子查询。

    • table：表示当前这个执行计划是基于那张表执行的。

    • type：当前执行计划查询的类型，有几种情况：

      • all：表示走了全表查询，未命中索引或索引失效。
      • system：表示要查询的表中仅有一条数据。
      • const：表示当前SQL语句的查询条件中，可以命中索引查询。
      • range：表示当前查询操作是查某个区间。
      • eq_ref：表示目前在做多表关联查询。
      • ref：表示目前使用了普通索引查询。
      • index：表示目前SQL使用了辅助索引查询。

    • possible_keys：执行SQL时，优化器可能会选择的索引（最后执行不一定用）。

    • key：查询语句执行时，用到的索引名字。

    • key_len：这里表示索引字段使用的字节数。

    • ref：这里显示使用了哪种查询的类型。

    • rows：当前查询语句可能会扫描多少行数据才能检索出结果。

    • Extra：这里是记录着额外的一些索引使用信息，有几种状态：

      • using index：表示目前使用了覆盖索引查询（稍后讲）。
      • using where：表示使用了where子句查询，通常表示没使用索引。
      • using index condition：表示查询条件使用到了联合索引的前面几个字段。
      • using temporary：表示使用了临时表处理查询结果。
      • using filesort：表示以索引字段之外的方式进行排序，效率较低。
      • select tables optimized away：表示在索引字段上使用了聚合函数。

    •   4.6.2、索引失效的具体场景

    •   1.查询中带有OR会导致索引失效

    •   2.模糊查询中like以%开头导致索引失效

    •   3.字符类型查询时不带引号导致索引失效

    •   4.索引字段参与计算导致索引失效（这里的运算也包括+、-、*、/、!.....等）

    •   5.字段被用于函数计算导致索引失效

    •   6.违背最左前缀原则导致索引失效（8.X之前的版本）

    •   7.不同字段值对比导致索引失效（从一张表中查询出一些值，然后根据这些值去其他表中筛选数据，这个业务也是实际项目中较为常见的场景）

    •   8.反向范围操作导致索引失效（一般来说，如果SQL属于正向范围查询，例如>、<、between、like、in...等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时，就会出现问题）

    •   9.索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查（此这种情况下走索引的顺序磁盘IO，反而不一定有全表的随机磁盘IO快）

    •   4.6.3、如何正确使用索引

    •   总结如下：

    • ①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。

    • ②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。

    • ③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。

    • ④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。

    • ⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。

    • ⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。

    • ⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。

    • ⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。

    •   实际上无非就是根据前面给出的索引失效情况，尽量让自己编写的SQL不会导致索引失效即可，写出来的SQL能走索引查询，那就能在很大程度上提升数据检索的效率。

    •   4.7、索引优化机制等

    •   4.7.1、索引覆盖

    •   为了提升查询效率，避免回表，当使用联查索引时尽量索引覆盖（查询结果包含在复合索引中）。

    •   4.7.2、索引下推

    •   索引下推是MySQL5.6版本以后引入的一种优化机制。优化机制就是将Server层筛选数据的工作，下推到引擎层处理。

    •   怎么理解索引下推呢？正常情况下我们查询出符合条件的数据后会交给Server层，server层会返回的数据做回表查询，再根据结果过滤无法走索引条件的数据，这个过程涉回根据满足索引条件的数据回表查询。而索引下推机制介入后，返回给server 层的数据是符合全部查询条件的，数据过滤下推到引擎层中处理了，会明显减少server层的回表查询，从而提升效率。

    •   4.7.3、MRR(Multi-Range Read)机制

    •   Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施。

    • 一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低IO次数，但在很多时候往往又不得不做回表才能查询到数据，但回表显然会导致产生大量磁盘IO，同时更严重的一点是：还会产生大量的离散IO。

    •   如何理解MRR机制，这个和磁盘空间的数据页紧密相关，当我们查询的数据分布在不同的数据页上时，我们如果按照循环查询的方式，会存在循环查询同一个数据页的情况，如果我能够把多次查询同一数据页的操作合并成查询一次数据页，这样可以较少磁盘IO的次数，从而提高效率，我个人理解的MRR就是实现这种机制的策略。

    • MRR机制就主要是针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

    •   MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

    •   MySQL5.6及以后的版本是默认开启的。可以通过命令开启和关闭：

    • SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

    •   4.7.4、Index Skip Scan索引跳跃式扫描

    •   在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

    •   MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，MySQL优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍，一句话来概述就是：虽然你没用第一个字段，但我给你加上去，今天这个联合索引你就得用，不用也得给我用。

    •   但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发.....，总之有很多限制条件，具体的可以参考《MySQL官网8.0-跳跃扫描》。

    •   我们可以使用命令开启关闭该机制，当然8.0以下的版本就不用考虑这个问题了

    • set @@optimizer_switch = 'skip_scan=off|on';

    •   4.8、索引的底层实现

    •   B+Tree的数据结构：

    •   MySQL在设计索引结构时，对于原始的B+Tree又一次做了改造，叶子节点之间除开一根单向的指针之外，又多新增了一根指针，指向前面一个叶子节点，也就是MySQL索引底层的结构，实际是B+Tree的变种，叶子节点之间是互存指针的，所有叶子节点是一个双向链表结构。

    •   五、Mysql事务

    •   5.1.ACID原则

    • A/Atomicity：原子性，指组成一个事务的一组SQL要么全部执行成功，要么全部执行失败，事务中的一组SQL会被看成一个不可分割的整体，当成一个操作看待。

    • C/Consistency：一致性，一个事务中的所有操作，要么一起改变数据库中的数据，要么都不改变，对于其他事务而言，数据的变化是一致的。

    • I/Isolation：独立性/隔离性，多个事务之间都是独立的，相当于每个事务都被装在一个箱子中，每个箱子之间都是隔开的，相互之间并不影响。

    • D/Durability：持久性持久性是指一个事务一旦被提交，它会保持永久性，所更改的数据都会被写入到磁盘做持久化处理，就算MySQL宕机也不会影响数据改变，因为宕机后也可以通过日志恢复数据

    •   5.2.事务机制综述

    •   ACID原则是数据库事务的四个特性，也可以理解为实现事务的基础理论。在MySQL默认情况下，一条SQL会被视为一个单独的事务，同时也无需咱们手动提交，因为默认是开启事务自动提交机制的，如若你想要将多条SQL组成一个事务执行，那需要显式的通过一些事务指令来实现。

    • 事务回滚点：在某些SQL执行成功后，但后续的操作有可能成功也有可能失败，但不管成功亦或失败，你都想让前面已经成功的操作生效时，此时就可在当前成功的位置设置一个回滚点。当后续操作执行失败时，就会回滚到该位置，而不是回滚整个事务中的所有操作，这个机制则称之为事务回滚点。

    •   5.3.事务隔离级别

    •   MySQL中，事务隔离机制分为了四个级别：

    • ①Read uncommitted/RU：读未提交

    • ②Read committed/RC：读已提交

    • ③Repeatable read/RR：可重复读

    • ④Serializable：序列化/串行化

    •   MySQL的事务隔离级别，默认为第三级别：Repeatable read可重复读

    •   5.4.事务隔离级别的问题以及解决方案

    •   5.4.1.问题

    •   数据库的脏读问题：脏读的意思是指一个事务读到了其他事务还未提交的数据，也就是当前事务读到的数据，由于还未提交，因此有可能会回滚。

    •   数据库的不可重复读问题：不可重复读问题是指在一个事务中，多次读取同一数据，先后读取到的数据不一致。

    •   数据库的幻读问题：发生幻读问题的原因是在于：另外一个事务在第一个事务要处理的目标数据范围之内新增了数据，然后先于第一个事务提交造成的问题。

    •   数据库脏写问题：也就是多个事务一起操作同一条数据，例如两个事务同时向表中添加一条ID=88的数据，此时就会造成数据覆盖，或者主键冲突的问题，这个问题也被称之为更新丢失问题。

    •   5.4.2.解决

    •   ①读未提交：处于该隔离级别的数据库，脏读、不可重复读、幻读问题都有可能发生。

    •   ②读已提交：处于该隔离级别的数据库，解决了脏读问题，不可重复读、幻读问题依旧存在。

    •   ③可重复读：处于该隔离级别的数据库，解决了脏读、不可重复读问题，幻读问题依旧存在。

    •   ④序列化/串行化：处于该隔离级别的数据库，解决了脏读、不可重复读、幻读问题都不存在。

    •   5.4.3.原理

    •   读未提交级别：这种隔离级别是基于「写互斥锁」实现的，当一个事务开始写某一个数据时，另外一个事务也来操作同一个数据，此时为了防止出现问题则需要先获取锁资源，只有获取到锁的事务，才允许对数据进行写操作，同时获取到锁的事务具备排他性/互斥性，也就是其他线程无法再操作这个数据。但虽然这个级别中，写同一数据时会互斥，但读操作却并不是互斥的，也就是当一个事务在写某个数据时，就算没有提交事务，其他事务来读取该数据时，也可以读到未提交的数据，因此就会导致脏读、不可重复读、幻读一系列问题出现。但是由于在这个隔离级别中加了「写互斥锁」，因此不会存在多个事务同时操作同一数据的情况，因此这个级别中解决了前面说到的脏写问题。

    •   读已提交级别：在这个隔离级别中，对于写操作同样会使用「写互斥锁」，也就是两个事务操作同一数据时，会出现排他性，而对于读操作则使用了一种名为MVCC多版本并发控制的技术处理，也就是有事务中的SQL需要读取当前事务正在操作的数据时，MVCC机制不会让另一个事务读取正在修改的数据，而是读取上一次提交的数据（也就是读原本的老数据）。也就是在这个隔离级别中，基于同一条数据而言，对于写操作会具备排他性，对于读操作则只能读已提交事务的数据，不会读取正在操作但还未提交的事务数据

    •     可重复读级别：在这个隔离级别中，主要就是解决上一个级别中遗留的不可重复读问题，但MySQL依旧是利用MVCC机制来解决这个问题的，只不过在这个级别的MVCC机制会稍微有些不同。在读已提交级别中，一个事务中每次查询数据时，都会创建一个新的ReadView，然后读取最近已提交的事务数据，因此就会造成不可重复读的问题，而在可重复读级别中，则不会每次查询时都创建新的ReadView，而是在一个事务中，只有第一次执行查询会创建一个ReadView，在这个事务的生命周期内，所有的查询都会从这一个ReadView中读取数据，从而确保了一个事务中多次读取相同数据是一致的，也就是解决了不可重复读问题。

    • 略微提一嘴：其实在***RR***级别中也可以解决幻读问题，就是使用临键锁（间隙锁+行锁）这种方式来加锁。

    •   5.5、事务实现的原理

    •   **MySQL**的事务机制是基于日志实现的。

    •   redo-log是一种WAL(Write-ahead logging)预写式日志，在数据发生更改之前会先记录日志，也就是在SQL执行前会先记录一条prepare状态的日志，然后再执行数据的写操作。

    •   MySQL是基于磁盘的，但磁盘的写入速度相较内存而言会较慢，因此MySQL-InnoDB引擎中不会直接将数据写入到磁盘文件中，而是会先写到BufferPool缓冲区中，当SQL被成功写入到缓冲区后，紧接着会将redo-log日志中相应的记录改为commit状态，然后再由MySQL刷盘机制去做具体的落盘操作。

    •   因为默认情况下，一条SQL会被当成一个事务，数据写入到缓冲区后，就代表执行成功，因此会自动修改日志记录为commit状态，后续则会由MySQL的后台线程执行刷盘动作。

    •   redo-log在写入缓冲区后虽然会被标记成commit状态，但实际上并没有被持久化到磁盘中。如果在这个时候发生断电或宕机等异常情况，那么缓冲区中的redo-log会丢失，数据也无法恢复。 为了解决这个问题，数据库引擎引入了WAL（Write-Ahead Logging）机制。WAL机制要求在将数据写入到缓冲区之前，必须先将修改操作记录到redo-log中，并且在执行事务提交操作后，将redo-log写入磁盘中。这样即使在提交事务之前发生了宕机或其他异常情况，redo-log中的操作也可以被重新执行来保证数据的一致性。 具体来说，当数据库引擎启用WAL机制时，在写入缓冲区之前，会先将修改操作记录到redo-log文件。在提交事务之前，redo-log中的日志记录被标记为prepare状态。当事务提交后，redo-log中的prepare状态日志记录才被标记为commit状态，并且写入磁盘中。如果在提交事务之前发生异常情况，redo-log中的prepare状态日志记录可以被用来恢复未完成的事务，从而保证数据的一致性。

    • WAL（Write-Ahead Logging）机制是数据库引擎中一种常见的日志管理技术，主要用于保证数据的持久性，避免在发生故障时数据的丢失。 在WAL机制下，当事务提交时，将会把redo-log先写入到硬盘上的一个特定文件（比如redo-log文件），这时候redo-log被称为已提交的log。在写入redo-log文件之后，数据库引擎再将修改数据的操作写入缓冲区（即内存），这时候缓冲区中的数据就可以被视为已经提交的数据。 为了保证已提交的redo-log能够被持久化到磁盘上，数据库引擎采用了一种叫做“强制日志写入”的机制（又称“强制日志刷盘”），意思是将redo-log刷到磁盘上，而不仅仅是存储在缓存中。在该机制下，可以采用以下两种方式来刷盘： 1. 同步写入：将redo-log直接写入磁盘，这种方式可靠性很高，但是对于性能方面比较有影响，因为直接写入磁盘需要等待I/O操作的完成，这会耗费很多时间。 2. 异步写入：将redo-log先写入磁盘中的缓存区中，在适当的时候将缓存区的redo-log异步批量写入磁盘，这种方式可以减少I/O操作带来的性能损耗，但是可靠性稍差。 通过WAL机制和强制日志刷盘机制的配合，可以保证在遇到系统崩溃之类的意外事件时，保障“已提交的数据”可靠性及重启恢复的顺利性。

    •   六、Mysql锁

    •   数据库的锁机制本身是为了解决并发事务带来的问题而诞生的，主要是确保数据库中，多条工作线程并行执行时的数据安全性。多个事务共同操作一张表、多个事务一起操作同一行数据等这类情景，这才是所谓的并发事务。

    •   6.1.锁分类

    •   MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同，这里是指不同的引擎实现的锁粒度不同。但除开从锁粒度来划分锁之外，其实锁也可以从其他的维度来划分，因此也会造出很多关于锁的名词，下面先简单梳理一下MySQL的锁体系：

    • 以锁粒度的维度划分：

      • ①表锁：

        • 全局锁：加上全局锁之后，整个数据库只能允许读，不允许做任何写操作。
        • 元数据锁 / MDL锁：基于表的元数据加锁，加锁后整张表不允许其他事务操作。
        • 意向锁：这个是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的。
        • 自增锁 / AUTO-INC锁：这个是为了提升自增ID的并发插入性能而设计的。

      • ②页面锁

      • ③行锁：

        • 记录锁 / Record锁：也就是行锁，一条记录和一行数据是同一个意思。
        • 间隙锁 / Gap锁：InnoDB中解决幻读问题的一种锁机制。
        • 临建锁 / Next-K``ey锁：间隙锁的升级版，同时具备记录锁+间隙锁的功能。

    • 以互斥性的维度划分：

      • 共享锁 / S锁：不同事务之间不会相互排斥、可以同时获取的锁。
      • 排他锁 / X锁：不同事务之间会相互排斥、同时只能允许一个事务获取的锁。
      • 共享排他锁 / SX锁：MySQL5.7版本中新引入的锁，主要是解决SMO带来的问题。

    • 以操作类型的维度划分：

      • 读锁：查询数据时使用的锁。
      • 写锁：执行插入、删除、修改、DDL语句时使用的锁。

    • 以加锁方式的维度划分：

      • 显示锁：编写SQL语句时，手动指定加锁的粒度。
      • 隐式锁：执行SQL语句时，根据隔离级别自动为SQL操作加锁。

    • 以思想的维度划分：

      • 乐观锁：每次执行前认为自己会成功，因此先尝试执行，失败时再获取锁。
      • 悲观锁：每次执行前都认为自己无法成功，因此会先获取锁，然后再执行。

    •   放眼望下来，是不是看着还蛮多的，但总归说来说去其实就共享锁、排他锁两种，只是加的方式不同，加的地方不同，因此就演化出了这么多锁的称呼。

    •   6.2.共享锁和排它锁

    •   共享锁的意思很简单，也就是不同事务之间不会排斥，可以同时获取锁并执行但这里所谓的不会排斥，仅仅只是指不会排斥其他事务来读数据，但其他事务尝试写数据时，就会出现排斥性。

    • SELECT ... LOCK IN SHARE MODE;
-- MySQL8.0之后也优化了写法，如下：
SELECT ... FOR SHARE;

    •   上面简单的了解了共享锁之后，紧着来看看排他锁，排他锁也被称之为独占锁，当一个线程获取到独占锁后，会排斥其他线程，如若其他线程也想对共享资源/同一数据进行操作，必须等到当前线程释放锁并竞争到锁资源才行。（读写都会排他）

    • SELECT ... FOR UPTATE;

    •   6.3.表锁

    •   不同引擎的表锁也在实现上以及加锁方式上有些许不同，但归根结底，表锁的意思也就以表作为锁的基础，将锁加在表上，一张表只能存在一个同一类型的表。

    • InnoDB是一个支持多粒度锁的存储引擎，它的锁机制是基于聚簇索引实现的，当SQL执行时，如果能在聚簇索引命中数据，则加的是行锁，如无法命中聚簇索引的数据则加的是表锁。

    •   6.3.1.元数据锁

    •   MySQL5.5版本后再开始支持元数据锁，当你的表结构正在发生更改，假设此时有其他事务来对表做CRUD操作，自然就会出现问题，所以需要MDL锁。

    •     意向锁则是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的。

    •   比如当事务T1打算对ID=8888888这条数据加一个行锁之前，就会先加一个表级别的意向锁，比如目前T1要加一个行级别的读锁，就会先添加一个表级别的意向共享锁，如果T1要加行级别的写锁，亦是同理。

    •   此时当事务T2尝试获取一个表级锁时，就会先看一下表上是否有意向锁，如果有的话再判断一下与自身是否冲突，比如表上存在一个意向共享锁，目前T2要获取的是表级别的读锁，那自然不冲突可以获取。但反之，如果T2要获取一个表级的写锁时，就会出现冲突，T2事务则会陷入阻塞，直至T1释放了锁（事务结束）为止。

    •   6.3.2.自增锁

    •   自增锁，这个是专门为了提升自增ID的并发插入性能而设计的。

    •   自增锁有三种模式，可以通过innodb_autoinc_lock_mode参数控制。

    •   innodb_autoinc_lock_mode = 0：传统模式。

    •   innodb_autoinc_lock_mode = 1：连续模式（MySQL8.0以前的默认模式）。

    •   innodb_autoinc_lock_mode = 2：交错模式（MySQL8.0之后的默认模式）。

    •   简单理解传统模式就是阻塞等待，连续模式就是根据插入的数据预先分配一定的ID，并发执行，交错模式利用自增列的步长机制实现，不过由于插入可能会出现空隙，因此对后续的主从复制也有一定影响。

    •   6.3.3.全局锁

    •   全局锁是基于整个数据库来加锁的，加上全局锁之后，整个数据库只能允许读，不允许做任何写操作，一般全局锁是在对整库做数据备份时使用。

    • -- 获取全局锁的命令
FLUSH TABLES WITH READ LOCK;

-- 释放全局锁的命令
UNLOCK TABLES;

    •   6.4.行锁

    •   MySQL诸多的存储引擎中，仅有InnoDB引擎支持行锁（不考虑那些闭源自研的），InnoDB会将表数据存储在聚簇索引中，每条行数据都会存储在树中的叶子节点上，因此行数据是“分开的”，所以可以对每一条数据上锁，但其他引擎大部分都不支持聚簇索引，表数据都是一起存储在一块的，所以只能基于整个表数据上锁，这也是为什么其他引擎不支持行锁的原因。

    •   6.4.1.间隙锁

    •   间隙锁是对行锁的一种补充，主要是用来解决幻读问题的。

    •   当对一个不存在的数据加锁后，默认就是锁定前后两条数据之间的区间，当其他事务再尝试向该区间插入数据时，就会陷入阻塞，只有当持有间隙锁的事务结束后，才能继续执行插入操作。间隙锁是遵循左右开区间的原则。

    •   6.4.2.临键锁

    •   临键锁是间隙锁的Plus版本，或者可以说成是一种由记录锁+间隙锁组成的锁。锁定左开右闭的区间

    •   实际上在InnoDB中，除开一些特殊情况外，当尝试对一条数据加锁时，默认加的是临键锁，而并非记录锁、间隙锁。

    •   6.4.3.插入意向锁

    •   插入意向锁，听起来似乎跟前面的表级别意向锁有些类似，但实际上插入意向锁是一种间隙锁，这种锁是一种隐式锁，也就是咱们无法手动的获取这种锁。通常在MySQL中插入数据时，是并不会产生锁的，但在插入前会先简单的判断一下，当前事务要插入的位置有没有存在间隙锁或临键锁，如果存在的话，当前插入数据的事务则需阻塞等待，直到拥有临键锁的事务提交。当事务执行插入语句阻塞时，就会生成一个插入意向锁，表示当前事务想对一个区间插入数据（目前的事务处于等待插入意向锁的状态）。

    •   6.5.乐观锁、悲观锁

    •   乐观锁机制，一般都是基于CAS思想实现的，而在MySQL中则可以通过version版本号+CAS的形式实现乐观锁，也就是在表中多设计一个version字段。

    •   悲观锁类型，也就是在每次执行前必须获取到锁，然后才能继续往下执行，而数据库中的排他锁，就是一种典型的悲观锁类型。

    •   6.6.共享排它锁

    •   MySQL5.7.2版本中引入了一种新的锁，被称之为(SX)共享排他锁，这种锁是共享锁与排他锁的杂交类型。至于为何引入这种锁呢？聊它之前需要先理解SMO问题。

    • 在SQL执行期间一旦更新操作触发B+Tree叶子节点分裂，那么就会对整棵B+Tree加排它锁，这不但阻塞了后续这张表上的所有的更新操作，同时也阻止了所有试图在B+Tree上的读操作，也就是会导致所有的读写操作都被阻塞，其影响巨大。因此，这种大粒度的排它锁成为了InnoDB支持高并发访问的主要瓶颈，而这也是MySQL 5.7版本中引入SX锁要解决的问题。

    •   最简单的方式就是减小SMO问题发生时，锁定的B+Tree粒度，当发生SMO问题时，就只锁定B+Tree的某个分支，而并不是锁定整颗B+树，从而做到不影响其他分支上的读写操作。

    •   七、MVCC机制

    •   7.1.MVCC综述

    •   MySQL基于读-写并存的场景，推出了MVCC机制，在线程安全问题和加锁串行化之间做了一定取舍，让两者之间达到了很好的平衡，即防止了脏读、不可重复读及幻读问题的出现，又无需对并发读-写事务加锁处理。

    •   MVCC机制的全称为Multi-Version Concurrency Control，即多版本并发控制技术，主要是为了提升数据库并发性能而设计的，其中采用更好的方式处理了读-写并发冲突，做到即使有读写冲突时，也可以不加锁解决，从而确保了任何时刻的读操作都是非阻塞的。

    •   那如果读、写的事务操作的不是同一个版本呢？比如写操作走新版本，读操作走老版本，这样是不是无论执行写操作的事务干了啥，都不会影响读的事务。

    • MySQL中仅在RC读已提交级别、RR可重复读级别才会使用MVCC机制，RU读未提交级别，既然都允许存在脏读问题、允许一个事务读取另一个事务未提交的数据，那自然可以直接读最新版本的数据，因此无需MVCC介入。

    •   7.2.MVCC原理解析

    •   MVCC机制主要通过隐藏字段、Undo-log日志、ReadView这三个东西实现的。

    •   MySQL除开会构建你显式声明的字段外，通常还会构建一些InnoDB引擎的隐藏字段，在InnoDB引擎中主要有DB_ROW_ID、DB_Deleted_Bit、DB_TRX_ID、DB_ROLL_PTR这四个隐藏字段。（隐藏主键 - ROW_ID（6Bytes）、删除标识 - Deleted_Bit（1Bytes）、最近更新的事务ID - TRX_ID（6Bytes）、回滚指针 - ROLL_PTR（7Bytes））。

    •   ROLL_PTR全称为rollback_pointer，也就是回滚指针的意思，这个也是表中每条数据都会存在的一个隐藏字段，当一个事务对一条数据做了改动后，都会将旧版本的数据放到Undo-log日志中，而rollback_pointer就是一个地址指针，指向Undo-log日志中旧版本的数据，当需要回滚事务时，就可以通过这个隐藏列，来找到改动之前的旧版本数据，而MVCC机制也利用这点，实现了行数据的多版本。

    • MySQL中也不例外，同样存在purger线程的概念，为了防止“已删除”的数据占用过多的磁盘空间，purger线程会自动清理Deleted_Bit=1/true的行数据。当然，为了确保清理数据时不会影响MVCC的正常工作，purger线程自身也会维护一个ReadView，如果某条数据的Deleted_Bit=true，并且TRX_ID对purge线程的ReadView可见，那么这条数据一定是可以被安全清除的（即不会影响MVCC工作）。

    •   Undo-log日志中会存储旧版本的数据，但要注意：Undo-log中并不仅仅只存储一条旧版本数据，其实在该日志中会有一个版本链。最新的旧版本数据，都会插入到链表头中，而不是追加到链表尾部。

    •   如果T2事务要查询一条行数据，此时这条行数据正在被T1事务写，那也就代表着这条数据可能存在多个旧版本数据，T2事务在查询时，应该读这条数据的哪个版本呢？此时就需要用到ReadView，用它来做多版本的并发控制，根据查询的时机来选择一个当前事务可见的旧版本数据读取。

    •   那究竟什么是ReadView呢？就是一个事务在尝试读取一条数据时，MVCC基于当前MySQL的运行状态生成的快照，也被称之为读视图，即ReadView，在这个快照中记录着当前所有活跃事务的ID（活跃事务是指还在执行的事务，即未结束（提交/回滚）的事务）。

    •   ①当事务中出现select语句时，会先根据MySQL的当前情况生成一个ReadView。

    •   ②判断行数据中的隐藏列trx_id与ReadView.creator_trx_id是否相同：

    • 相同：代表创建ReadView和修改行数据的事务是同一个，自然可以读取最新版数据。

    • 不相同：代表目前要查询的数据，是被其他事务修改过的，继续往下执行。

    •   ③判断隐藏列trx_id是否小于ReadView.up_limit_id最小活跃事务ID：

    • 小于：代表改动行数据的事务在创建快照前就已结束，可以读取最新版本的数据。

    • 不小于：则代表改动行数据的事务还在执行，因此需要继续往下判断。

    •   ④判断隐藏列trx_id是否小于ReadView.low_limit_id这个值：

    • 大于或等于：代表改动行数据的事务是生成快照后才开启的，因此不能访问最新版数据。

    • 小于：表示改动行数据的事务ID在up_limit_id、low_limit_id之间，需要进一步判断。

    •   ⑤如果隐藏列trx_id小于low_limit_id，继续判断trx_id是否在trx_ids中：

    • 在：表示改动行数据的事务目前依旧在执行，不能访问最新版数据。

    • 不在：表示改动行数据的事务已经结束，可以访问最新版的数据。

    •   如果Undo-log日志中的旧数据存在一个版本链时，此时会首先根据隐藏列roll_ptr找到链表头，然后依次遍历整个列表，从而检索到最合适的一条数据并返回。

    •   规则大致如下:

    •   1.旧版本的数据，其隐藏列trx_id不能在ReadView.trx_ids活跃事务列表中。

    •   2.根据版本链寻找符合条件的第一条数据

    •   还有两个特殊场景：

    •   1.此时当T1事务查询数据时，突然蹦出来一条ID=6的数据，经过判断之后会发现新增这条数据的事务还在执行，所以要去查询旧版本数据，但此时由于是新增操作，因此roll_ptr=null，即表示没有旧版本数据，此时会不会读取最新版的数据呢？答案是NO，如果查询数据的事务不能读取最新版数据，同时又无法从版本链中找到旧数据，那就意味着这条数据对T1事务完全不可见，因此T1的查询结果中不会包含ID=6的这条新增记录。

    •   2.如果这个一个修改数据的事务正好快照生成结束后才开启的，并且多次修改了目前select操作要读取的目标数据行，因此在Undo版本链中会产生一系列旧数据，但根据前面的一系列判断，最终select事务会去版本链中找数据，此时后面这个修改事务的ID，恰巧不在快照到trx_ids列表中怎么办呢？ 面对于这种情况，当MVCC发现旧版本的数据，其隐藏列的trx_id大于目前快照的最大事务ID时，MVCC会自动跳过该版本的数据，Why？因为MySQL在分配事务ID时，都是以递增的顺序分配，所以当旧版本上的trx_id大于快照的最大事务ID时，说明这条旧版本数据是在快照生成之后产生的，所以会跳过对应的旧版本数据不读取。

    •   RC级别下，MVCC机制是会在每次select语句执行前，都会生成一个ReadView，在RR级别中，一个事务只会在首次执行select语句时生成快照，后续所有的select操作都会基于这个ReadView来判断，这样也就解决了RC级别中存在的不可重复问题。

    •   八、Mysql日志

    •   8.1.Undo-log撤销日志

    •   当一个事务尝试写某行表数据时，首先会将旧数据拷贝到xx.ibdata文件中，将表中行数据的隐藏字段：roll_ptr回滚指针会指向xx.ibdata文件中的旧数据，然后再写表上的数据。在共享表数据文件中，有一块区域名为Rollback Segment回滚段，每个回滚段中有1024个Undo-log Segment，每个Undo段可存储一条旧数据，而执行写SQL时，Undo-log就是写入到这些段中。不过在MySQL5.5版本前，默认只有一个Rollback Segment，而在MySQL5.5版本后，默认有128个回滚段，即支持128*1024条Undo记录同时存在。

    •   8.2.Redo-log重做日志

    •   Undo-log主要用于实现事务回滚和MVCC机制，而Redo-log则用来实现数据的恢复。

    •   MySQL绝大部分引擎都是是基于磁盘存储数据的，但如若每次读写数据都走磁盘，其效率必然十分低下，因此InnoDB引擎在设计时，当MySQL启动后就会在内存中创建一个BufferPool，运行过程中会将大量操作汇集在内存中进行，比如写入数据时，先写到内存中，然后由后台线程再刷写到磁盘

    •   虽然使用BufferPool提升了MySQL整体的读写性能，但它是基于内存的，也就意味着随着机器的宕机、重启，其中保存的数据会消失，那当一个事务向内存中写入数据后，MySQL突然宕机了，岂不代表这条未刷写到磁盘的数据会丢失吗？答案是Yes，也正由于该原因，Redo-log应运而生！

    •   8.3.Bin-log变更日志

    •   Bin-log日志也被称之为二进制日志，作用与Redo-log类似，主要是记录所有对数据库表结构变更和表数据修改的操作，对于select、show这类读操作并不会记录。bin-log是MySQL-Server级别的日志，也就是所有引擎都能用的日志，而redo-log、undo-log都是InnoDB引擎专享的，无法跨引擎生效。

    •   它跟redo-log、undo-log的缓冲区并不同，前面分析的两种日志缓冲区，都位于InnoDB创建的共享BufferPool中，而bin_log_buffer是位于每条线程中的。

    •   MySQL-Server会给每一条工作线程，都分配一个bin_log_buffer，而并不是放在共享缓冲区中，这是为啥呢？因为MySQL设计时要兼容所有引擎，直接将bin-log的缓冲区，设计在线程的工作内存中，这样就能够让所有引擎通用，并且不同线程/事务之间，由于写的都是自己工作内存中的bin-log缓冲，因此并发执行时也不会冲突。

    •   在bin-log的本地文件中，其中存储的日志记录共有Statment、Row、Mixed三种格式。

    • Statment：每一条会对数据库产生变更的SQL语句都会记录到bin-log中，但虽然优势不小，但缺点页很明显，即恢复数据、主从同步数据时，有时会出现数据不一致的情况，如SQL中使用了sysdate()、now()这类函数
这种模式就是为了解决Statment模式的缺陷，Row模式中不再记录每条造成变更的SQL语句，而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。
Mixed：这种被称为混合模式，即Statment、Row的结合版，因为Statment模式会导致数据出现不一致，而Row模式数据量又会很大，因此Mixed模式结合了两者的优劣势，对于可以复制的SQL采用Statment模式记录，对于无法复制的SQL采用Row记录。

    •   九、MySql存储引擎与触发器

    •   9.1.存储过程

    •   Stored Procedure存储过程是数据库系统中一个十分重要的功能，使用存储过程可以大幅度缩短大SQL的响应时间，同时也可以提高数据库编程的灵活性。存储过程是一组为了完成特定功能的SQL语句集合，使用存储过程的目的在于：将常用且复杂的SQL语句预先写好，然后用一个指定名称存储起来，这个过程经过MySQL编译解析、执行优化后存储在数据库中，因此称为存储过程。

    •   对比常规的SQL语句来说，普通SQL在执行时需要先经过编译、分析、优化等过程，最后再执行，而存储过程则不需要，一般存储过程都是预先已经编译过的，这就好比咱们在讲《JVM-执行引擎》聊到过的JIT即时编译器一样，为了提升一些常用代码的执行效率，JIT会将热点代码编译成本地机器码，以此省略解释器翻译执行的步骤，从而做到提升性能的目的。

    •   使用存储过程的优点：

      1. 复用性：存储过程被创建后，可以在程序中被反复调用，不必重新编写该存储过程的SQL语句，同时库表结构发生更改时，只需要修改数据库中的存储过程，无需修改业务代码，也就意味着不会影响到调用它的应用程序源代码。
      2. 灵活性：普通的SQL语句很难像写代码那么自由，而存储过程可以用流程控制语句编写，也支持在其中定义变量，有很强的灵活性，可以完成复杂的条件查询和较繁琐的运算。
      3. 省资源：普通的SQL一般都会存储在客户端，如Java中的dao/mapper层，每次执行SQL需要通过网络将SQL语句发送给数据库执行，而存储过程是保存在MySQL中的，因此当客户端调用存储过程时，只需要通过网络传送存储过程的调用语句和参数，无需将一条大SQL通过网络传输，从而可降低网络负载。
      4. 高性能：存储过程执行多次后，会将SQL语句编译成机器码驻留在线程缓冲区，在以后的调用中，只需要从缓冲区中执行机器码即可，无需再次编译执行，从而提高了系统的效率和性能。
      5. 安全性：对于不同的存储过程，可根据权限设置执行的用户，因此对于一些特殊的SQL，例如清空表这类操作，可以设定root、admin用户才可执行。同时由于存储过程编写好之后，对于客户端而言是黑盒的，因此减小了SQL被暴露的风险。

    •   适用存储过程的缺点：

      1. CPU开销大：如果一个存储过程中涉及大量逻辑运算工作，会导致MySQL所在的服务器CPU飙升，因而会影响正常业务的执行，有可能导致MySQL在线上出现抖动，毕竟MySQL在设计时更注重的是数据存储和检索，对于计算性的任务并不擅长。
      2. 内存占用高：为了尽可能的提升执行效率，因此当一个数据库连接反复调用某个存储过程后，MySQL会直接将该存储过程的机器码放入到连接的线程私有区中，当MySQL中的大量连接都在频繁调用存储过程时，这必然会导致内存占用率同样飙升。
      3. 维护性差：一方面是过于复杂的存储过程，普通的后端开发人员很难看懂，毕竟存储过程类似于一门新的语言，不同语言之间跨度较大。另一方面是很少有数据库的存储过程支持Debug调试，MySQL的存储过程就不支持，这也就意味着Bug出现时，无法像应用程序那样正常调试排查，必须得采取“人肉排查”模式，即一步步拆解存储过程并排查。
    • DELIMITER $

-- 创建的语法：指定名称、入参、出参
CREATE 
    PROCEDURE 存储过程名称(返回类型 参数名1 参数类型1, ....)
    [ ...这里在后面讲... ]
-- 表示开始编写存储过程体
BEGIN
    -- 具体组成存储过程的SQL语句....
-- 表示到这里为止，存储过程结束
END $

DELIMITER ;

    •   9.2.触发器

    •   触发器本质上是一种特殊的存储过程，但存储过程需要人为手动调用，而触发器则不需要，它可以在执行某项数据操作后自动触发，就类似于Spring-AOP中的切面一样，当执行了某个操作时就会触发相应的切面逻辑。

    • CREATE TRIGGER 触发器名称
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名
FOR EACH ROW
    -- 触发器的逻辑（代码块）;

    • CREATE TRIGGER zz_users_insert_before   
BEFORE INSERT ON zz_users 
FOR EACH ROWBEGIN
insert into `register_log` values(NOW(),"北京市海淀区","IOS");         
END

    •   更多细节参照：juejin.cn/post/716166…

avatar
文章
阅读
粉丝