前言:
本文是基于掘金大佬的文章结合自己的一点理解,疑问解答梳理,归纳的,原文内容内容更详细
他强由他强,清风拂山岗,他横由他横,明月照大江--九阴真经心法。
-
一、整体架构
-
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…
-