JZTXT

MySQL_基础

发布时间 2023-11-23 17:02:07作者: Espre-sso

MySQL架构

  1. Server层：建立连接、分析和执行SQL
  2. 存储引擎层：数据的存储和提取。

不同的存储引擎共用一个Server层。
1a.连接器：与客户端三次握手建立TCP连接 -> -u -p错误，报错 -> 正确，读取用户的权限，后面的权限逻辑判断基于此时读取到的权限。
1b. 查询缓存：MySQL8.0后将缓存删掉了。
1c. 解析器：select name from user词法分析（识别出关键字select、from） -> 语法分析（符不符合MySQL语法）
1d. 执行器：1d1. prepare预处理阶段：检查SQL语句中的表和字段是否存在name、user。将SELECT *中的*扩展为表上的所有列。1d2. 优化器：将SQL查询语句的执行方案确定下来。1d3. 执行器：执行SQL语句（执行器和存储引擎交互、三种方式：1主键索引查询2全表扫描3索引下推）

关系型数据库

关系型数据库：
通过表来存储数据的数据库。

存储引擎

作用：
决定数据库中数据存储、获取、更新、查询的方式

InnoDB存储引擎

特点：
事务、外键、行级锁。

何时选择InnoDB存储引擎

何时选择InnoDB：
对事务的完整、并发有较高要求的，核心数据。（绝大部分时候选择InnoDB）。
MyISAM：
对事务的完整性和并发行没有较高要求，增加查询多，更新删除少，非核心数据。被MongoDB替代了。
Memory：
数据都在内存中，常用于缓存、临时表，但是无法存储太大的数据。被Redis替代了。

索引-结构

Mysql的索引是在存储引擎层实现的。
不同索引结构被存储引擎的支持情况：
B+Tree：InnoDB，MyISAM，Memory
Hash：Memory
R-Tree：MyISAM
FULL-text：MyISAM，InnoDB（5.6后版本）

索引-最左前缀法则

最左前缀法则：
如果索引了多列（联合索引），就要遵守最左前缀法则。查询从索引的最左列开始，而不跳过索引中的列，如果跳过某一列，索引将部分失效（后面的字段索引失效）。
范围查询：
联合索引中，出现范围查询<，>范围查询右侧的索引列失效。（解决方法：使用<=，>=时右侧的索引列就不会失效了）

回表

定义：
第一次搜索B+树拿到主键值后再去搜索主键索引的B+树，这个过程就是所谓的回表。

执行一条SQL语句，期间发生了什么

  • 连接器：建立连接，验证身份。
  • （查询缓存）：MySQL8.0已删除。
  • 解析SQL：词法分析，语法分析
  • 执行SQL：
    1. 预处理阶段：检查表或字段是否存在，将SELECT *的*扩展为表上的所有列。
    2. 优化阶段：选择查询成本最小的执行计划。
    3. 执行阶段：执行SQL查询语句，从存储引擎读取记录，返回给客户端。

MySQL行记录的存储

MySQL数据存储在哪个文件？

默认InnoDB存储引擎：

  1. db.opt：数据库默认字符集、字符校验规则
  2. 表名.frm：表结构
  3. 表名.ibd：表数据（这个文件也叫独占表空间文件）
表空间文件的结构是怎么样的？

表空间（从大到小）：
段segment，区extent，页page，行row

  1. 行：记录是按行进行存放的。
  2. 页：InnoDB的数据是按页进行读写的。16KB
  3. 区：B+树链表中相邻的页物理位置也相邻，这样就可以使用顺序IO了，在范围查询（扫描叶子节点）的时候性能会更高。1MB，64个页。
  4. 段：
    1. 索引段：B+树非叶子节点的区的集合。
    2. 数据段：B+树叶子节点的区的集合。
    3. 回滚段：回滚数据的区的集合。
InnoDB的行格式

行格式：
InnoDB 在MySQL5.6后用Dynamic（基于Compact改了一点东西）
Compact行格式：
额外信息+真实数据

  1. 额外信息：
    变长字段长度列表+NULL值列表+记录头信息
    1. 变长字段长度列表：逆序存放、不记录NULL
      为什么逆序？
      提高CPU Cache的命中率（使位置靠前的记录的真实数据和字段长度信息可以同时在一个CPU Cache Line中，NULL值列表逆序原因同理）。
      一定有变长字段列表吗？
      不一定，如果数据表没有变长字段则没有。
    2. NULL值列表：逆序，仅记录可以为NULL的字段
      长度：
      默认1字节8位，若全是NOT NULL，则长度为0，若可以为0的字段多于8个，则为2字节16位。
    3. 记录头信息：
      delete_mask：删除标记，1删除。
      next_record：下一条记录的位置，额外信息和真实数据之间。
      record_type：记录类型，0普通，1非叶子节点，2最小记录，3最大记录。
  2. 真实数据
    1. row_id：6字节，非必须，没有主键&&没有唯一约束列时，InnoDB添加的隐藏字段。
    2. trx_id：6字节，必须，哪个事务生成的。
    3. roll_pointer：7字节，必须，上一个版本的指针，MVCC。
Q：varchar(n)中n的最大取值是多少？

MySQL中除了TEXT，BLOBs外，其它所有列（隐藏列，记录头信息除外）占用的字节长度不能超过65535字节。（ASCII编码时，1字符=1字节）（varchar(n)，n代表字符数）（UTF-8，1字符=3字节）
A：单字段情况-变长字段长度列表2字节，NULL值列表1字节，n最大65532字节。
B：多字段情况-(所有字段长度+变长字段长度列表+NULL值列表)<=65535

行溢出后，MySQL是怎么处理的？

存放到溢出页。（TEXT，BLOB）
Compact行格式：真实数据处保留一部分，其余20字节-溢出页的地址。
Compressed && Dynamic：不保留，只存储20字节-溢出页的地址。