文章目录
- 数据库、数据库管理系统以及SQL之间的关系
- 关系型数据库与非关系型数据库
- 安装
- mysqld
-
- 选项
- 配置文件
-
- 语法
- 包含其它配置文件
- 系统变量
- 系统状态
- 字符集和比较规则
- 逻辑架构
-
- 连接层
- 服务层
- 引擎层
- 存储层
- InnoDB
-
- 数据目录
- 内存和磁盘模型
- 数据存储结构
-
- 页
-
- FileHeader
- PageHeader
- Infimum和Supremum
- UserRecords和FreeSpace
- PageDriectory
- FileTrailer
- 行
-
- 变长字段长度列表
- null值列表
- 记录头列表
- 真实记录
- 索引
-
- 聚簇索引
- 二级索引
- 二级索引的类型
- 事务
-
- 事务的ACID特性
- 事务的状态
- 显式事务
- 隐式事务
- 事务中的并发问题
- 事务的隔离级别
- 事务日志
-
- 重做日志
- 回滚日志
- 锁
-
- 读写锁
- 表锁
-
- 意向锁
- 自增锁
- 元数据锁
- 行锁
-
- 记录锁
- 间隙锁
- 临键锁
- 插入意向锁
- 页锁
- 乐观锁和悲观锁
- 全局锁
- 死锁
- 锁结构
- MVCC
- 日志
-
- 通用查询日志
- 错误日志
- 数据定义语句日志
- 二进制日志
- 中继日志
- 主从复制
- 数据库的备份与还原
数据库、数据库管理系统以及SQL之间的关系
| 名称 | 说明 |
|---|---|
| 数据库（Database） | 即存储数据的仓库，其本质是一个文件系统。它保存了一系列有组织的数据。 |
| 数据库管理系统（Database Management System） | 是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。 |
| 结构化查询语言（Structured Query Language） | 专门用来与数据库通信的语言。 |
文章图片
关系型数据库与非关系型数据库 关系型数据库模型是把复杂的数据结构归结为简单的二元关系 （即二维表格式），它以行和列的形式存储数据，这一系列的行和列被称为表，一组表组成了一个库。表与表之间的数据记录存在关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。因此关系型数据库，就是建立在关系模型基础上的数据库。SQL 就是关系型数据库的查询语言。非关系型数据库，可看成关系型数据库的阉割版本，它基于键值对存储数据，不需要经过SQL层的解析。NoSQL 一词泛指非关系型数据库，常用的非关系型数据库如下：
| 类型 | 说明 | 代表 |
|---|---|---|
| 键值型数据库 | 键值型数据库通过 Key-Value 键值的方式来存储数据，优点是查找速度快，缺点是无法像关系型数据库一样使用条件过滤。键值型数据库典型的使用场景是作为 内存缓存 | Redis |
| 文档型数据库 | 文档型数据库可存放并获取文档，在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。 | MongoDB |
| 搜索引擎数据库 | 虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。 | Elasticsearch |
| 图形数据库 | 图形数据库是一种存储图形关系的数据库。它利用了图这种数据结构存储了实体之间的关系。关系型数据用于存储明确关系的数据，但对于复杂关系的数据存储却有些力不从心。 | Neo4J |
从这里下载发行包然后传送到Linux并通过以下命令安装：
sudo yum install
安装MySQL
sudo yum install mysql-community-server
打开服务
systemctl start mysqld
服务初始化
为了保证数据库目录和文件的所有者为MySQL登录用户，需要执行下面的命令进行服务初始化：
mysqld --initialize --user=mysql
服务初始化会创建一个
root@localhost用户并生成一个随机密码保存到日志文件中。查看root密码
sudo grep 'temporary password' /var/log/mysqld.log
登录MySQL并修改root密码
新密码至少包含1个大写字母、1个小写字母、1个数字和1个特殊字符，密码总长度至少为8个字符。
alter user 'root'@'localhost' identified by 'newPwd';
允许root远程登录
use mysql;
update user set host =where user='root';
# %为任意主机
刷新权限
flush privileges;
设置开机自启动
systemctl enable mysqld.service
防火墙打开端口号
firewall-cmd --zone=public --add-port=3306/tcp --permanent
重启防火墙
firewall-cmd --reload
mysqld MySQL由许多程序组成。每个MySQL程序都有许多不同的选项。常用的程序如下：
- mysql：mysql是一个简单的 SQL shell，具有输入行编辑功能。
- mysqld：mysqld也称为 MySQL 服务器，是一个单一的多线程程序，它在 MySQL 安装中完成大部分工作。当 MySQL 服务器启动时，它会侦听来自客户端程序的网络连接并代表这些客户端管理对数据库的访问。
- mysqldump ：一个数据库备份程序，它能产生一组能够被执行以再现原始数据库对象定义和表数据的SQL语句。
- 在命令行程序名后面以单或双破折号开头的参数指定程序选项。
- 在配置文件内指定选项。
- 通过设置环境变量指定选项。
mysqld-auto.cnf选项文件最后处理，因此它甚至优先于命令行选项。配置文件 mysql按照如下顺序读取配置文件：
| 路径 | 说明 |
|---|---|
| /etc/my.cnf | 全局配置 |
| /etc/mysql/my.cnf | 全局配置 |
| $MYSQL_HOME/my.cnf | 服务器配置 |
| defaults-extra-file | 使用defaults-extra-file指定的文件(如果有的话) |
| ~/.my.cnf | 自定义服务器配置 |
| ~/.mylogin.cnf | 自定义客户端配置 |
defaults-file 选项指定搜索位置。语法
在选项文件中指定选项的语法类似于命令行语法，但是，在选项文件中可以采用以下任何一种形式：
- [group]：group是要为其设置选项的组名。在该行之后，任何选项设置行应用于该组，，直到选项文件结束或给出另一个组行为止。选项组名称不区分大小写。
- opt_name：相当于命令行上的
--opt_name。 - opt_name=value：这相当于命令行上的
--opt_name value。在选项文件中，value可以选择用单引号或双引号括起来。
| 组名 | 可读取的程序 |
|---|---|
| [server]、[mysqld] | mysqld |
| [client]、[mysql] | mysql |
可以使用以下指令包含其它配置文件：
!include
也可以使用以下指令搜索特定目录文件中的配置文件
!includedir
MySQL不保证读取目录中选项文件的顺序，并且在处理配置文件时仅使用当前程序用到的配组，用不到的配置组将被忽略。
系统变量 mysqld在运行过程中会用到许多影响程序行为的变量，它们被称为系统变量。每个系统变量都有一个默认值，我们可以使用命令行或者配置文件中的选项在启动服务器时改变一些系统变量值，大多数系统变量的值也可以在程序运行过程中修改，而无须停止并重新启动服务器。在MySQL中系统变量有两种作用范围：
- global（全局范围）：影响服务器的整体操作。
- session（会话范围）：影响某个客户端连接的操作。
show [global|session] variables
|like
mysqld中的系统变量以两个@开头，其中
@@global用于标记全局系统变量，
@@session用于标记会话系统变量。服务器在启动时，会将每个全局变量初始化为其默认。 服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值进行初始化。可以通过以下语句设置变量值：set [global|session] =
此方式设置的系统变量只会临时生效 。 数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。值得注意的是有些变量只具有全局范围，有些变量只具有会话范围，有些变量是只读的不能修改。
系统状态 为了让我们更好地了解mysqld的运行情况mysqld中维护了好多关于程序的运行状态，由于状态是用来显示服务器程序运行状态的，所以它们的值只能由服务器程序自己来设置，不能入为设置，查看状态变量的方法如下：
show [global|session] status
|like
字符集和比较规则 MySQL支持大量的字符集，可以通过以下指令查看：
show charset
|like
默认的字符集为utf8，并且mysql对这些常用的字符集进行了优化以节省存储空间，比如utf8其实是utf8mb3字符集的别名，这种utf8mb3是阉割过的utf8字符集，它最多使用3个字节存储字符。每种字符集都会支持一些比较规则，比较规则用于定义查询时系统进行比较的默认规则，查看的指令如下：
show collation
|like
mysql提供四个级别的字符集和比较规则：
- 服务器级别：服务器使用
- 数据库级别：数据库使用，如果创建或修改数据库时没有显式指定字符集和比较规则，则该数据库默认使用服务器的字符集和比较规则。
- 表级别：表使用，如果创建或修改表时没有显式指定字符集和比较规则，则该表默认使用数据库的字符集和比较规则。
- 列级别：列使用，如果创建或修改列时没有显式指定字符集和比较规则，则该列默认使用表的字符集和比较规则。
文章图片
连接层 连接层包含本地socket通信和大多数基于客户端/服务器工具实现的类似于TCP/IP的通信，主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层引入了线程池的概念，为通过安全认证接入的客户端提供线程，同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层 服务层主要完成大多数的核心服务功能，如SQL接口、缓存查询、SQL分析优化部分以及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现。在该层服务器会解析查询并创建相应的内存解析树，并对其完成相应的优化，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。
SQL接口
- 接收用户的SQL命令，并且返回用户需要查询的结果。
- MySQL支持DML、DDL、存储过程、视图、触发器、自定义函数等多种SQL语言接口。
- 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
- 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树 ，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。
- SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划 。
- 这个执行计划表明应该 使用哪些索引 进行查询，表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
- 优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。相反，动态优化则和查询的上下文有关，也可能和很多其他因素有关。查询优化器对查询的静态优化只需要做一次，但对查询的动态优化则在每次执行时都需要重新评估。有时侯甚至在查询的执行过程中也会重新优化。
- MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
- 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
- 这个查询缓存可以在 不同客户端之间共享 。
- 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。
存储层 所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统 上，以文件的方式存在的，并完成与存储引擎的交互。
InnoDB InnoDB是一种兼顾高可靠性和高性能的通用存储引擎。它的优势如下：
- 支持事务
- 支持行级锁
- 支持外键
- 每个InnoDB表都有一个聚簇索引的主键索引。
tableName.ibd文件。show variables like 'datadir';
#查看数据目录
内存和磁盘模型
文章图片
| 名称 | 说明 |
|---|---|
| 缓冲池 | 缓冲池用来缓存已使用的表和索引数据。缓冲池使得经常被使用的数据能够直接在内存中获得，从而提高速度。 |
| 更改缓冲区 | 当被更新的二级索引不在缓存池中时，更改缓冲区就会缓存对二级引的更改。当二级索引被其它读取操作时会加载到缓存池，缓存的更改内容就会被合并。 |
| 自适应哈希索引 | 自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行，不需要降低事务上的性能或可靠性。 |
| 日志缓冲区 | 日志缓冲区用于存放要放入重做日志的数据，它会定期地将日志文件刷入磁盘。日志缓冲区使得大型事务能够正常运行而不需要写入磁盘。 |
| 系统表空间 | 系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引数据。多表共享，系统表空间被视为共享表空间。 |
| 双写缓冲区 | 用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存后，InnoDB才会将数据页写入合适的位置。 |
| 撤销日志 | 撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这些片段包含于回滚片段中。 |
| 独立表空间 | 独立表空间用于存储表数据，它由一个单独的.ibd数据文件代表，该文件默认被创建在数据库目录中。 |
| 通用表空间 | 使用create tablespace语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数据目录之外能够管理多个表并支持所有行格式的表。 |
| 撤销表空间 | 撤销表空间由一个或多个包含撤销日志的文件组成。 |
| 临时表空间 | 用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。 |
| 重做日志 | 重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间，重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自动在初始化期间重新进行 |
- 表空间：表空间是一个逻辑容器，在一个表空间中可以有一个或多个段，数据库由多个表空间组成。
- 段：由一个或多个区组成，段是数据库中分配的单位，不同类型的数据库对象以不同的段形式存在。
- 区：区在文件系统中是一个连续分配的空间，一个区会分配64个连续的页，因此一个区的大小是1MB。
文章图片
页
InnoDB将数据划分为若干个页，以页作为内存和磁盘之间交互的基本单位，页的大小一般为16KB 。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。InnoDB 为了不同的目的而设计了多种不同类型的页。
| 名称 | 说明 |
|---|---|
| FIL_PAGE_TYPE_ALLOCATED | 最新分配，还未使用 |
| FIL_PAGE_UNDO_LOG | 撤销日志页 |
| FIL_PAGE_INODE | 段信息结点 |
| FIL_PAGE_IBUF_FREE_LIST | Change Buffer空闲列表 |
| FIL_PAGE_IBUF_BITMAP | Change Buffer的一些属性 |
| FIL_PAGE_TYPE_SYS | 系统页 |
| FIL_PAGE_TYPE_TRX_SYS | 事务系统数据 |
| FIL_PAGE_TYPE_FSP_HDR | 表空间头部信息 |
| FIL_PAGE_TYPE_XDES | 扩展描述页 |
| FIL_PAGE_TYPE_BLOB | 溢出页 |
| FIL_PAGE_INDEX | 索引页 |
文章图片
FileHeader FileHeader通用于各种类型的页，它描述了一些通用于各种页的信息。
| 名称 | 说明 |
|---|---|
| FIL_PAGE_SPACE_OR_CHKSUM | 页的校验和 |
| FIL_PAGE_OFFSET | 页号 |
| FIL_PAGE_PREV | 上一个页的页号 |
| FIL_PAGE_NEXT | 下一个页的页号 |
| FIL_PAGE_LSN | 页面被最后修改时对应的LSN(日志序列号) |
| FIL_PAGE_TYPE | 该页的类型 |
| FIL_PAGE_FILE_FLUSH_LSN | 仅在系统表空间的第一个页中定义，代表文件至少被刷新到了对应的LSN值 |
| FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID | 页属于哪个表空间 |
| 名称 | 说明 |
|---|---|
| PAGE_N_DIR_SLOTS | 在PageDirectory中的槽数量 |
| PAGE_HEAP_TOP | 还未使用的空间最小地址 也就是说从该地址之后就是FreeSpace |
| PAGE_N_HEAP | 堆中的记录数。 |
| PAGE_FREE | 各个己删除的记录通过next_record组成 个单向链表，这个单向链表中的记录所占用的存储空间可以被重新利用 PAGE FREE 表示该链表头节点对应记录在页面中的偏移量 |
| PAGE_GARBAGE | 已删除记录的字节数，即行记录结构中，delete flag为1的记录大小的总数。 |
| PAGE_LAST_INSERT | 最后插入记录的位置。 |
| PAGE_DIRECTION | 最后插入的方向。可能的取值为PAGE_LEFT（0x01），PAGE_RIGHT（0x02），PAGE_SAME_REC（0x03），PAGE_SAME_PAGE（0x04），PAGE_NO_DIRECTION（0x05）。 |
| PAGE_N_DIRECTION | 一个方向连续插入记录的数量。 |
| PAGE_N_RECS | 该页中记录的数量。 |
| PAGE_MAX_TRX_ID | 修改当前页的最大事务ID，注意该值仅在二级索引定义。 |
| PAGE_LEVEL | 当前页在B+树中的层级 |
| PAGE_INDEX_ID | 索引ID，当前页属于哪个索引 |
| PAGE_BTR_SEG_LEAF | B+树的叶节点段的首指针位置。注意该值仅在B+树的Root页中定义。 |
| PAGE_BTR_SEG_TOP | B+树的非叶节点段的首指针位置。注意该值仅在B+树的Root页中定义。 |
UserRecords和FreeSpace 我们插入表中的记录会按照指定的行格式存储到UserRecords部分，但是在一开始生成页的时候，其实并没有 UserRecords 部分，每当插入一条记录时都会从FreeSpace部分申请一个记录大小的空间，并将这个空间划分到UserRecords部分。当 FreeSpace 部分的空间全部被 UserRecords 部分替代掉之后，也就意味着这个页使用完了，此时如果还有新的记录插入，就需要去申请新的页了 。
PageDriectory InnoDB将所有正常的记录划分为几个组，每个组的最后一条记录相当于带头大哥，组内其余的记录相当于小弟，带头大哥记录的头信息中的n_owned 属性表示该组内共有几条记录。将每个组中最后一条记录在页面中的地址偏移量单独提取出来，按顺序存储到PageDirectory中，页目录中的这些地址偏移量称为槽，每个槽占用2字节，页目录就是由多个槽组成的。InnoDB对每个分组中的记录条数是有规定的，对于Infimum记录所在的分组只能有1条记录，Supremum 记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能是在4~8条之间。
FileTrailer FileTrailer这个部分由8个字节组成，前4个字节代表页的校验和，这个部分与 File Header 中的校验和相对应，每当一个页在内存中发生修改时 ，在刷新之前就要把页面的校验和算出来。因为 FileHeader 在页面的前边，所以 FileHeader 中的校验和会被首先刷新到磁盘，当完全写完后，校验和也会被写到页的尾部，如果页面刷新成功，则页首和页尾的校验和应该是一致的，如果刷新了一部分后断电了 ，那 FileHeader 中的校验和就代表着己经修改过的页，而FileTrailer中的校验和代表着原先的页，二者不 同则意味着刷新期间发生了错误。后4个字节代表页面被最后修改时对应的 LSN 的后4个字节，正常情况下应该与FileHeader 部分的 FEL_PAGE_ LSN 的后4字节相同，这个部分也是用于校验页的完整性。
行
我们插入表中的记录称为行，MySQL中有四种格式的行。主要掌握MySQL8默认的
Dynamic行格式：
文章图片
变长字段长度列表 在compact行格式中，所有变长字段的真实数据占用 字节数都存放在记录的开头位
置，从而形成 个变长字段长度列表，各变长字段的真实数据占用的字节数按照列顺序逆序存放。，变长字段长度列表中只存储值为非null的列的内容长度，不存储值为 null列的内容长度。
null值列表 一条记录中的某些列可能存储null值，如果把这些null值都放到记录的真实数据中存储会很占地方，所以compact行格式把一条记录中值为null的列统一管理起来 ，存储到null值列表中。它的处理过程如下：
- 首先统计表中允许存储null值的列。
- 如果表中没有允许存储null值的列，则null值列表也就不存在了，否则将每个允许存储null的列对应一个二进制位，二进制的位按照列顺序的逆序排列。
- mysql 规定null值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。
文章图片
deleted_flag
标记记录行是否被删除，被删除的记录行不会从磁盘上移除，因为在移除它们之后还需要在磁盘上重新排列其他的记录行，这会带来性能消耗，所以只打一个删除标记就可以避免这个问题，所有被删除掉的记录会组成一个垃圾链表，记录在这个链表中占用的空间称为可重用空间，之后若有新记录插入到表中，它们就可能覆盖掉被删除的这些记录占用的存储空间。
min_rec_flag
B+树中每层非叶子节点中的最小的目录项记录都会添加该标记。
n_owned
一个页中的记录会被分成若干个组，每个组中有一个记录是带头大哥，其余的记录都是小弟，带头大哥记录的n_owned 值代表该组中所有的记录条数，小弟记录的n_owned 值都为0
heap_no
为了方便管理堆，把一条记录在堆中的相对位置称之为heap_no，每新申请一条记录的存储空间时，该条记录比物理位置在它前边的那条记录的 beap_no值大1。另外还需要注意的一点是，堆中记录的 heap_no值在分配之后就不会发生改动了，即使之后删除了堆中的某条记录，这条被删除记录的 heap_no值也仍然保持不变。
record_type
表示当前记录类型，0表示普通记录，1表示目录项记录，2表示 Infimum记录，3表示 Supremum记录。
next_record
表示当前记录的真实记录区与下一条记录的真实记录区的相对位置，下一条记录指的并不是插入顺序中的下一条记录，而是按照主键值由小到大的顺序排列的下一条记录。
真实记录 真实记录区除了记录我们自定义的数据外，InnoDB还会为每一行添加三个字段实现：
- 6字节的
DB_TRX_ID字段表示插入或更新该行的最后一个事务的事务标识符。此外，删除在内部被视为更新，在该行中设置一个特殊的位将其标记为删除。 - 一个7字节的
DB_ROLL_PTR字段称为回滚指针。回滚指针指向写入回滚段的撤销日志记录。如果该行已更新，则撤消日志记录将包含在该行更新之前重建该行内容所需的信息。 - 一个6字节的
DB_ROW_ID字段包含一个行ID，随着新行插入而单调增加。如果InnoDB自动生成一个聚集索引，则索引包含行ID值。否则，DB_ROW_ID列不会出现在任何索引中。
索引 索引是存储引擎用于快速找到记录的存储在存储引擎层面的一种数据结构。
聚簇索引
聚簇索引就是InnoDB在存储页时默认使用的索引结构，它是一颗B+树：
文章图片
其中行与行之间存储为一个单向链表，页与页之间存储为双向链表，整体存储为一个多叉树。所有的用户数据都会存储到叶子结点，非叶子节点的行只存储下一层的页码和最小主键两个值，就这样层层缩减直至根节点。在真实创建聚簇索引时的创建的顺序是从根节点开始的，当根节点存满时就创建两个子节点一个子节点复制根节点的内容，另一个子节点继续存放新插入的数据，而根节点的行存储这两个子节点的最小索引和页码，当另一个根节点存满时再反复重复这个过程。每个InooDB表都必须有一个聚簇索引，它的实现规则如下：
- 当在表上定义一个主键时，InnoDB使用它创建聚集索引。
- 如果一个表没有主键, InnoDB会使用第一个所有的键列都定义为
not null的unique索引作为聚集索引。 - 如果一个表没有主键和合适的
unique索引，InnoDB会在包含DB_ROW_ID行值的列上生成一个名为GEN_CLUST_INDEX的隐藏聚集索引。
聚簇索引只能在搜索条件是主键值时才能发挥作用，因此我们可以多建几棵 B+ 树，并且不同B+树中的数据使用不同的列进行排序，这种索引称为二级索引。二级索引的叶子节点并不保存完整的用户数据，而是保存存储索引列的值和主键列的值，在搜索时通过二级索引确定一个主键列的值，然后根据这个主键列的值到聚簇索引搜索完整的用户记录。然后再返回二级索引继续搜索下一个满足条件的值，这一过程被称为回表。二级索引可以同时将多个列作为排序条件创建索引，这种索引又称为联合索引，联合索引在排序是先通过第一个建值进行排序，在第一个键值相同时再根据第二个键值进行排序。二级索引也可以使用索引列值的一部分，这种索引被称为前缀索引。使用前缀索引后就不能用该搜因字段排序，因为有可能导致排序不准确。
二级索引的类型
- 普通索引：在创建索引时不附加任何限制条件，只是用于提高查询效率。
- unique索引：在创建唯一索引时，索引列的值必须是唯一的，但允许包含
null值。 - fulltext索引：在创建全文索引时允许索引列的值重复或空值，但只能创建在
char、varchar和text类型及其系列类型的字段上，并且不能为前缀索引。全文索引的作用类似于ES。
事务的ACID特性
- 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
- 一致性：一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是语义上的而不是语法上的，跟具体的业务有关。
- 隔离性：隔离性是指一个事务的执行不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对 并发的其它事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。
- 活动的：事务对应的数据库操作正在执行过程中时，事务就处在活动的状态。
- 部分提交的：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并 没有刷新到磁盘时，我们就说该事务处在 部分提交的 状态。
- 失败的：当事务处在活动的或者部分提交的状态时，可能遇到了某些错误而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
- 中止的：如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态。
- 提交的：当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了 提交的 状态。
文章图片
显式事务
开启事务
#read only:标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
#read write（默认）:标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
#with consistent snapshop:启动一致性读，可以和第一个或第二个配合使用
start transaction [read only|read write|with consistent snapshop]
rollback #回滚事务
commit #提交事务
提交事务
commit #提交事务
回滚事务
rollback
设置保存点
savepoint >
删除保存点
release savepoint >
回滚到保存点
rollback savepoint >
隐式事务
如果不显式地开启一个事务，每个DML语句都被当作一个事务执行提交操作，可以通过修改变量的值关闭自动提交事务：
set autocommit= #1：自动提交，0：取消自动提交
在显式开启事务时，在本次事务提交或者回滚前也会暂时关闭掉自动提交的功能。还有一些情况不受该变量的影响依旧还会自动提交事务。
事务中的并发问题
- 脏读：所谓脏读，就是指事务A读到了事务B还没有提交的数据，比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。
- 不可重复读：所谓不可重复读，就是指在一个事务里面读取了两次某个数据，读出来的数据不一致。还是以银行取钱为例，事务A开启事务，查出银行卡余额为1000元，此时切换到事务B事务B开启事务，取走100元，提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致，这就是不可重复读。
- 幻读：所谓幻读，就是指在一个事务里面的操作中发现了未被操作的数据。比如学生信息，事务A开启事务，修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务，插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。
在SQL标准中定义了四种隔离级别，每一种隔离级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也小。可以通过以下语句设置隔离级别，新的隔离级别会在下一个事务开始的时候生效。
set [global|session] transaction isolation level
| 隔离级别 | 说明 | 可能出现的问题 |
|---|---|---|
| read uncommitted（读未提交） | 即使没有提交，对其它事务也是可见的。 | 脏读，不可重复读，幻读 |
| read committed（读已提交） | 一个事务从开始直到提交之前，所做的任何修改对其它事务都是不可见的。 | 不可重复读，幻读 |
| repeatable read（可重复读） | 一个事务读取了某个数据，只要这个事务不结束，其它事务就不能修改这条数据，这是mySQLd的默认隔离级别 | 幻读 |
| serializable（可串行化） | 事务串行化，在读取每一条数据上都加锁。 | 加锁读 |
事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证。
重做日志 重做日志在事务执行过程中不断记录对页的具体操作，当对内存中的页完成了事务操作但没来得及刷新到磁盘服务器就宕机时，可以通过重做日志恢复事务操作，它保证了事务的持久性。重做日志由两部分组成：
- 重做日志缓冲：保存在内存中，是易丢失的，可以通过
innodb_log_buffer_size变量设置缓冲区的大小。 - 重做日志文件：保存在硬盘中，是持久的。
- 第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
- 第2步：生成一条重做日志并写入重做日志缓冲，记录的是数据被修改后的值
- 第3步：当事务提交时，将重做日志缓冲中的内容同步到重做日志文件，对重做日志文件采用追加写的方式
- 第4步：定期将内存中修改的数据同步到磁盘中。
innodb_flush_log_at_trx_commit 变量，该变量可以控制事务提交时重做日志缓冲同步到重做日志文件的策略：- 设置为0 ：事务提交时什么也不做，
- 设置为1（默认） ：事务提交时把重做日志缓冲写入到文件系统缓存中并同步到磁盘。
- 设置为2 ：事务提交时只把重做日志缓冲写入缓冲页中而不进行同步。
锁
事务的隔离性可以由锁机制实现，并发事务访问相同记录的情况大致可以划分为3种：
- 读-读：即并发事务相继 读取相同的记录 。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。
- 写-写：即并发事务相继对相同的记录做出改动，在这种情况下会发生脏写的问题。
- 读-写或写-读：即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读 、 不可重复读 、幻读的问题。
读写锁 从数据操作的类型可以将锁分为读锁和写锁：
- 读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
- 写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。
lock table [read|write]
unlock tables #解锁表
意向锁 意向锁是一种不与行锁冲突的表锁。当某个事务给某表加了一个行锁时，会自动给这个表加一个意向锁，当另一个事务为这个表加表锁的时候，就不用再一行行的寻找这个表是否被其它事务加了行锁。意向锁也分为意向读锁和意向写锁。但是它们之间并不是互斥的。意向锁是由存储引擎自己维护的 ，用户无法手动操作。
自增锁 自增锁是当向使用含有
auto_increment列的表中插入数据时需要获取的一种特殊的表级锁。InnoDB通过innodb_autoinc_lock_mode变量来设置不同的锁定模式：- 设置为0：在这种模式下，所有的插入语句都会竞争这个表级锁，极大限制了并发能力。
- 设置为1：在这种模式下，对于知道要插入多少行的插入语句，通过在轻量锁的控制下获得所需数量的自动递增值来避免表级自增锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。
- 设置为2（默认）：所有插入语句都不会使用递增锁，自动递增列保证在所有并发执行的插入语句中是唯一且单调递增的。但可能生成的值不是连续的。
行锁 行锁会锁定某个行，它只实现于存储引擎，它的开销大并且会有死锁问题，但是具有较高的并发率。
for [share|update]#读锁|写锁[nowait|skip lock]#立即报错返回|立即返回，只返回结果集中不被锁定的行
记录锁 记录锁就是把一条记录锁上，记录锁也有读锁和写锁之分。
间隙锁 在可重复读的隔离级别下使用的锁，当对一个没有的行添加行锁时，此时就是添加了一个间隙锁，间隙锁保证了表中小于这个行的最大值和大于这个行的最小值的真实记录范围之间不能插入其它行。间隙锁之间不会互斥，不同的事务都可以添加间隙锁。
临键锁 在可重复读的隔离级别下使用的锁，临键锁既可以锁住某条记录 ，又可以阻止其它事务在该记录前边的间隙插入新记录。
插入意向锁 InnoDB规定，当某个事务因为某个间隙锁而等待插入时也需要加锁，表明有事务想在某个间隙中插入新记录，但是现在在等待。这种类型的锁就是插入意向锁。插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。
页锁 页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
乐观锁和悲观锁 乐观锁和悲观锁是一种锁的设计思想。悲观锁总是假设最坏的情况，共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程，悲观锁可以基于数据库锁机制实现，悲观锁适合写操作多的场景。乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。
全局锁 全局锁就是对整个数据库实例加锁。当你需要让整个库处于 只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句、数据定义语句和更新类事务的提交语句。全局锁的典型使用场景是做全库逻辑备份 。
flush tables with read lock
死锁 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。MySQL中死锁有两种：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过变量
innodb_lock_wait_timeout来设置。 - 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。将变量
innodb_deadlock_detect设置为on ，表示开启这个逻辑。
文章图片
| 名称 | 说明 |
|---|---|
| 锁所在的事务信息 | 不论是 表锁 还是 行锁 ，都是在事务执行过程中生成的，哪个事务生成了这个 锁结构 ，这里就记录这个事务的信息。 |
| 索引信息 | 对于 行锁 来说，需要记录一下加锁的记录是属于哪个索引的。 |
| Space ID | 记录所在表空间。 |
| Page Number | 记录所在页号。 |
| n_bits | 对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits 属性代表使用了多少比特位。 |
| type_mode | 用于记录锁的模式和类型。 |
| 其他信息 | 为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。 |
| 一堆比特位 | 如果是行锁结构的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性表示的。InnoDB数据页中的每条记录在 记录头信息 中都包含一个 heap_no 属性，伪记录 Infimum 的heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，之后每插入一条记录， heap_no 值就增1。 锁结构 最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no ，即一个比特位映射到页内的一条记录。 |
事务的隔离性也可以由MVVC实现，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制 。它可以做到即使有读写冲突时，也能做到不加锁 ， 非阻塞并发读。本质是采用乐观锁思想的一种方式。MVCC只在可重复读和读已提交两个隔离级别中起作用。
日志 MySQL日志如下：
- 慢查询日志
- 通用查询日志
- 错误日志
- 二进制日志
- 中继日志
- 数据定义语句日志
通用查询日志 通用查询日志用来记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。
错误日志 错误日志用于记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。在MySQL数据库中，错误日志功能是默认开启的。而且是无法被禁止的。默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为
mysqld.log。数据定义语句日志 二进制日志 二进制日志也叫作变更日志。它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句。当MySQL创建二进制日志文件时，先创建一个以
.index为后缀的文件，再创建一个以.000001为后缀的文件。MySQL服务重新启动一次 ，以.000001为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；但如果日志长度超过了 max_binlog_size 的上限，就会创建一个新的日志文件。中继日志 【数据库|关系型数据库之MySQL8——由内而外的深化全面学习】中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件中，这个从服务器本地的日志文件就叫中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的 数据同步 。搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式是：
从服务器名 -relay-bin.序号 。中继日志还有一个索引文件： 从服务器名 -relay-bin.index ，用来定位当前正在使用的中继日志。主从复制 数据库的备份与还原
推荐阅读
- 数据库|全面、详细的SQL学习指南（MySQL方向）
- 面试总结|[ 渗透测试面试篇 ] 大厂面试经验分享
- php|pikachu之xss靶场详细通关教程
- #|【SpringBoot】微服务中异步调用数据提交数据库的问题
- 数据库|实时计算，连接时序数据库和核心业务
- 实习|时序数据库
- ElasticSearch笔记|#ElasticSearch笔记#利用ElasticSearch实现全文搜索（一）简单介绍及环境搭建
- java|ceph-msg-messager|simple 代码分析
- JAVA|redis之缓存穿透、缓存击穿、缓存雪崩