图解MySQL

2023/6/5 计算机基础后端 MySQL 数据库

MySQL的执行过程

1:连接器
2:查询缓存
3:解析SQL
4:执行SQL

MySql的架构有两层：Server层和存储引擎层

Server层：负责建立连接，分析和执行SQL
存储引擎层：负责数据的存储和提取，5.5以上的版本默认使用InnoDb

MySql的连接是基于TCP协议进行传输的，所以在连接过程中会进行三次握手。

查看当前MySQL服务被多少个客户连接了，直接使用命令show processlist，默认空闲连接的最大时长是8小时也就是28880秒。默认最大连接数量是151个。

MySQL中的长连接和短连接

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接:缺点占用内存
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

解决长连接占用内存的问题：

1.定期断开长连接
2.客户端主动重置连接：MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

解析SQL

解析器完成SQL的解析工作，解析器通过语法分析和词法分析实现解析工作。解析器只负责构建语法树和检查语法，但是不会去查表或者字段存不存在。这个工作是由执行SQL的prepare阶段完成对于表或者字段是否存在的判断。

不过，对于 MySQL 5.7 判断表或字段是否存在的工作，是在词法分析&语法分析之后，prepare 阶段之前做的。结论都一样，不是在解析器里做的。正因为 MySQL 5.7 代码结构不好，所以 MySQL 8.0 代码结构变化很大，后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了。

执行SQL

执行语句的三个流程

prepare 阶段，也就是预处理阶段；
optimize 阶段，也就是优化阶段；优化器主要负责将 SQL 查询语句的执行方案确定下来
execute 阶段，也就是执行阶段；

执行器

执行器和存储引擎的交互过程：

主键索引查询
全表扫描
索引下推

主键扫描例如语句：

select * from product where id = 1;

执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。
执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

索引下推：在没有索引下推时，每查询到一条二级索引记录都要进行回表的操作，然后将记录返回给Server，接着Server再判断其是否满足其对应的下一个限制条件。在引入索引下推技术后，每次查询到一条耳机索引记录时，先不进行回表操作，而是先判断该记录是否满足其他限制条件，如果满足就执行回表，将完成的记录返回给Server层。如果不满足就跳过该记录。

MySQL是如何存储的

MySQL存储的行为是由存储引擎实现的，MySQL支持多种不同的存储引擎，不同的存储引擎保存文件自然也不同。InnoDB是我们常用的存储引擎，也是MySQL默认的存储引擎。查看MySQL数据的文件存放位置：

show variables like 'datadir'

MySQL数据库文件中一共有三个文件:

db.opt:用来存储当前数据库的默认字符集和字符校验规则
t_order.frm：t_order的表结构会保存在这个文件。在MySQL中建立一张表都会生成一个.frm文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
t_order.ibd：t_order的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）这个行为是由参数innodb_file_per_table控制的，若设置了参数innodb_file_per_table为1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从MySQL5.6.6版本开始，它的默认值就是1了，因此从这个版本之后， MySQL中每一张表的数据都存放在一个独立的.ibd文件

表空间文件的结构

表空间由段(segment),区(extent),页(page),行(row)组成，InnoDB存储引擎的逻辑存储结构如下所示：

行(row)：数据库表中的记录都是按行存储的，每行记录根据不同的行格式，有不同的存储结构。
页(page)：数据库的读取时按页为单位来读取的，因为按行来读取效率非常低。当需要读一条记录的时候，并不是将这个行记录从磁盘中读取出来，而是以页为单位整体读入内存。每个页的大小默认为16kb，也就是说最多能保证16kb的连续存储空间。页是InnoDB最小单元，意味着数据库每次读写都是以16kb为单位的，一次最少从磁盘中读取16kb的内容到内存中。
区(extent)：B+树中每一层都是通过双向链表连接起来的,如果是以页为单位来分配存储空间,那么链表中相邻的两个页之间的物理位置并不是连续的,可能离得非常远,那么磁盘查询时就会有大量的随机I/O,随机I/O是非常慢的,为了解决这个问题,就是让链表中相邻的页的物理位置也相邻,这样就可以使用顺序I/O了。具体过程是：在表中数据量大的时候，为某个索引分配空间的时候就不再按页为单位分配了，而是按区为单位分配,每个区的大小为1MB,对于16KB来说,连续的64个页就会被划分为一个区。
段(segment)：表空间是由各个段组成的，段是由多个区构成的，段一般分为数据段，索引段和回滚段。其中数据段存放B+树的叶子节点的区的集合，索引段存放B+树的非叶子节点的区的集合，回滚段存放的是回滚数据的区的集合。

InnoDB行格式

Redundant:非常古老，现在已经被废弃了
Compact：紧凑的行格式，MySQL的默认格式
Dynamic
Compressed

Compact格式

记录的额外数据：变长字段长度，NULL值列表，记录头信息

变长字段的真实数据占用的字节数会按照列的顺序逆序存放：这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率

NULL值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

二进制位的值为1时，代表该列的值为NULL。
二进制位的值为0时，代表该列的值不为NULL。

记录的真实数据

真实数据部分除了定义的字段，还有三个隐藏字段：

row_id:如果建表时指定了主键或者唯一约束将不再有这一字段，这是非必需的字段，默认占用6字节内存
trx_id:事务id，表示这个数据是由哪个事务生成的，必需字段，占用6字节内存
roll_pointer:记录上一版本的指针，必须字段，占用7字节

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。这个最大长度指的是一行，而不是一列。而在varchar(n)中的n最大值表示的最大存储字符数，而非字节数。

行溢出后，MYSQL是如何处理的？

对于一些大对象如TEXT，BLOB时，一条记录的大小就会超过磁盘和内存交互的基本单元页的大小(16KB)，这时会发出行溢出，多的数据就会存到另外的溢出页中。这时记录的真实数据出只会保留一部分数据，会单独留出20字节表示溢出数据存储的溢出页的地址。

索引

索引就是数据的目录，帮助存储引擎快速获取数据的一种数据结构。索引的本质是数据结构，索引的作用是提高数据查询的效率。

索引的类型

按数据结构分类

B+树索引：最常见的索引类型，InnoDB使用的索引类型
HASH索引：只有精确匹配索引所有列的查询才有效，不能用于排序，范围查询，字符串匹配等操作，只有Memory引擎支持
Full-Text索引：全文索引，只能用于MyISAM引擎，用于查找文本中的关键词，而不是直接与索引中的值相比较
R-Tree索引：空间索引，只能用于MyISAM引擎，用于地理空间数据类型，如地理位置坐标

B+树索引是最常见的索引类型，InnoDB使用的索引类型。创建索引的过程：

1.如果有主键，默认使用主键作为聚簇索引
2.如果没有主键，就选择第一个不包含NULL值的唯一索引作为聚簇索引的索引key
3.当上述情况都没有时，InnoDB会生成一个隐藏的row_id作为聚簇索引的索引key

辅助索引也被叫做二级索引或非聚簇索引，创建的主键索引和二级索引默认使用的B+Tree索引。

B+树的结构：

B+树是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，每个节点里的数据是按主键顺序存放的。叶子节点之间有指针相连，每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表，这样可以方便范围查询。

按物理存储分类

聚簇索引：叶子节点存放的是数据，InnoDB使用的索引类型
二级索引：叶子节点存放的是主键，InnoDB使用的索引类型
在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

按字段特性分类

普通索引：最基本的索引，没有任何限制

create Table t(
    ....
    index(index_col_name1,index_col_name2...)
)
create index index_name on table_name(index_col_name1,index_col_name2...)

主键索引：特殊的唯一索引，不允许有空值

create Table t(
    ....
    primary key(id)using btree
)

前缀索引：对字符串的前缀进行索引，可以节约索引空间，提高索引效率

create Table t(
    column_list,
    index(index_col_name1(length))
)
create index index_name on table_name(index_col_name1(length))

唯一索引：可以有多个唯一索引，但索引列的值必须唯一，但允许有空值

create Table t(
    ....
    unique key(index_col_name1,index_col_name2...)
)
create unique index index_name on table_name(index_col_name1,index_col_name2...)

按字段个数分类

单列索引：一个索引只包含单个列
联合索引：一个索引包含多个列，联合索引遵循最左前缀原则，即查询条件从左到右依次匹配索引的最左前缀列，如果遇到范围查询，那么范围之后的列都无法使用索引，如果遇到不等于查询，那么不等于之后的列都无法使用索引

索引的优缺点

优点：

1.大大减少了服务器需要扫描的数据量
2.帮助服务器避免排序和临时表
3.将随机IO变为顺序IO
4.可以将随机写变为顺序写

缺点：

1.创建和维护索引需要时间，随着数据量的增加，索引维护的时间也会增加
2.索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
3.当对表中的数据进行增删改时，索引也需要动态维护，降低了数据的维护速度

什么时候使用索引：

1.字段有唯一性限制，如主键、唯一索引
2.经常需要搜索的字段，如经常用where、order by、group by的字段

什么时候不使用索引：

1.表记录太少，如表记录小于5
2.频繁增删改的表，如日志表
3.数据重复且分布平均的表，如性别、状态等字段
4.起不到定位作用的字段，如在where，group by，order by中用不到的字段。

优化索引的方法

1.前缀索引优化：对字符串的前缀进行索引，可以节约索引空间，提高索引效率，但是有一定的局限性：order by就无法使用前缀索引，无法把前缀索引用作覆盖索引
2.覆盖索引优化：为了避免二级索引的回表操作，可以建立一个联合索引，将需要查询的字段都放在联合索引中，这样就可以直接从二级索引中获取数据，而不需要回表，这个过程就是覆盖索引，大大降低了I/O操作的次数。
主键索引最好是自增的，这样可以保证插入新数据时，不会出现页分裂，提高插入效率

InnoDB存储数据的方式

记录是按行来存储的，但是数据库的读取并不是以行为单位的，因为按行为单位读取数据的效率太低了，所以数据库会以页为单位来读取数据，每一页的大小默认是16KB，也就是说，每次读取的数据量是16KB，如果一行数据太大，一页放不下，那么就会分多页存储，这就是页分裂。

数据页的格式：

文件头：表示页的信息
页头：表示页的状态信息
最小和最大记录：两个虚拟记录，分别表示页中的最小记录和最大记录
用户记录：存储行记录的内容
空闲空间：页中还没有使用的空间
页目录：存储页中记录的相对位置，对记录起到索引作用
文件尾：检验页是否完整

数据页中的记录是按主键顺序组成的单向链表，插入和删除较为方便，但是检索效率低下，所以数据页中有一个页目录，起到记录的索引作用。

页目录的创建过程是：

1.将所有的记录划分为几个组，这些记录包括最小记录和最大记录，但不包括标记为”删除”的记录
2.每个记录的最后一个记录就是组内最大的记录，并且最后一条记录的头信息会存储该组一共有多少条记录，作为n_owned的值
3.也目录用来存储每组最后一条记录的地址偏移量，这些地址偏移位会按照先后顺序存储起来，每组的地址偏移量也被称为槽，每个槽相当于指针指向了不同组的最后一个记录。

页目录就是有多个槽组成的，槽相当于记录的索引。通过槽查找记录时，可以使用二分法快速定位要查询的记录，定位到槽的位置后，再遍历槽内的记录，找到对应的记录，无需从最小记录开始遍历。

InnoDB中的B+树的特点：

只有叶子节点存储数据，非叶子节点只存储目录项作为索引
非叶子节点分为不同层次，通过层次来减少查找次数
所有节点按照索引键大小排序，构成一个双向链表，便于范围查找

聚簇索引和二级索引

聚簇索引：叶子节点存储的是实际数据，非叶子节点存储的是主键索引，所有完整的用户记录都被存放在聚簇索引的叶子节点
二级索引的叶子节点存放的是主键值，而不是实际数据。

使用聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

磁盘读写的最小单位是扇区，扇区的大小只有512B大小，操作系统一次会读取多个扇区，使用操作系统的最小读写单位是块，块的大小一般是4KB，也就是说，操作系统一次会读取4KB的数据，也就是说一次磁盘I/O操作会直接读取8个扇区。

索引失效的情况

Read View在MVCC里如何工作

Read View有四个字段：

trx_id：表示创建该Read View的事务ID
m_ids：表示创建该Read View时，活跃未提交的事务ID列表
min_trx_id：表示创建该Read View时，活跃未提交系统中最小的事务ID
max_trx_id：表示创建该Read View时，活跃未提交系统中最大的事务ID
在创建Read view后，将记录划分为三种情况：

通过版本链控制并发事务访问同一个记录时的行为就叫MVCC。根据隔离的级别使用ids对min_trx_id和max_trx_id进行判断。

MYSQL中锁的分类

全局锁：对整个数据库实例加锁，对于整个数据库实例加锁，只能在数据库重启时加锁，一般用于全库逻辑备份。
加上全局锁意味着整个数据库都是只读状态。
表级锁：对表加锁，分为表锁和元数据锁，意向锁和AUTO-INC锁。
表锁：对表加锁，分为读锁和写锁，读锁之间不互斥，写锁之间互斥，读锁和写锁之间互斥。
元数据锁：对表结构加锁，当对表结构进行修改时，会对表结构加元数据锁，防止其他事务对表结构进行修改。
意向锁：对表加锁，分为意向读锁和意向写锁，意向锁是表锁的一种，当对表加读锁时，会对表加意向读锁，当对表加写锁时，会对表加意向写锁。
AUTO-INC锁：对表加锁，当对表进行插入操作时，会对表加AUTO-INC锁，防止其他事务对AUTO-INC值进行修改。

间隙锁：只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下的幻读现象。间隙锁是锁定一个范围，而不是某一条记录，间隙锁是在索引上加的锁，间隙锁的目的是为了防止其他事务在这个范围内插入数据，导致幻读现象。间隙锁之间是兼容的，也就是说，如果两个事务都对同一个间隙加锁，那么这两个事务可以同时对这个间隙加锁成功。

LOADING