MySQL从入门到入土
前言：
本文是基于掘金大佬的文章结合自己的一点理解，疑问解答梳理，归纳的，原文内容内容更详细
他强由他强,清风拂山岗,他横由他横,明月照大江--九阴真经心法。
-
一、整体架构
-
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` 关键字，并可以选择是否为聚簇索引。例如：sqlCREATE TABLE example (id INT PRIMARY KEY,name VARCHAR(50),age INT,INDEX age_index(age) -- 非聚簇索引) ENGINE=InnoDB;``可以在`CREATE INDEX`或`ALTER TABLE`语句中使用`CLUSTERING` 关键字来指定聚簇索引。例如：sqlCREATE 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会将热点代码编译成本地机器码，以此省略解释器翻译执行的步骤，从而做到提升性能的目的。 -
使用存储过程的优点：
-
- 复用性：存储过程被创建后，可以在程序中被反复调用，不必重新编写该存储过程的
SQL语句，同时库表结构发生更改时，只需要修改数据库中的存储过程，无需修改业务代码，也就意味着不会影响到调用它的应用程序源代码。 - 灵活性：普通的
SQL语句很难像写代码那么自由，而存储过程可以用流程控制语句编写，也支持在其中定义变量，有很强的灵活性，可以完成复杂的条件查询和较繁琐的运算。 - 省资源：普通的
SQL一般都会存储在客户端，如Java中的dao/mapper层，每次执行SQL需要通过网络将SQL语句发送给数据库执行，而存储过程是保存在MySQL中的，因此当客户端调用存储过程时，只需要通过网络传送存储过程的调用语句和参数，无需将一条大SQL通过网络传输，从而可降低网络负载。 - 高性能：存储过程执行多次后，会将
SQL语句编译成机器码驻留在线程缓冲区，在以后的调用中，只需要从缓冲区中执行机器码即可，无需再次编译执行，从而提高了系统的效率和性能。 - 安全性：对于不同的存储过程，可根据权限设置执行的用户，因此对于一些特殊的
SQL，例如清空表这类操作，可以设定root、admin用户才可执行。同时由于存储过程编写好之后，对于客户端而言是黑盒的，因此减小了SQL被暴露的风险。
- 复用性：存储过程被创建后，可以在程序中被反复调用，不必重新编写该存储过程的
-
适用存储过程的缺点：
-
- CPU开销大：如果一个存储过程中涉及大量逻辑运算工作，会导致
MySQL所在的服务器CPU飙升，因而会影响正常业务的执行，有可能导致MySQL在线上出现抖动，毕竟MySQL在设计时更注重的是数据存储和检索，对于计算性的任务并不擅长。 - 内存占用高：为了尽可能的提升执行效率，因此当一个数据库连接反复调用某个存储过程后，
MySQL会直接将该存储过程的机器码放入到连接的线程私有区中，当MySQL中的大量连接都在频繁调用存储过程时，这必然会导致内存占用率同样飙升。 - 维护性差：一方面是过于复杂的存储过程，普通的后端开发人员很难看懂，毕竟存储过程类似于一门新的语言，不同语言之间跨度较大。另一方面是很少有数据库的存储过程支持
Debug调试，MySQL的存储过程就不支持，这也就意味着Bug出现时，无法像应用程序那样正常调试排查，必须得采取“人肉排查”模式，即一步步拆解存储过程并排查。
- CPU开销大：如果一个存储过程中涉及大量逻辑运算工作，会导致
-
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…
-
转载自:https://juejin.cn/post/7230989445728731191