【MySQL高级篇笔记 (上) 】

浅浅的花香味﹌ 2023-10-10 19:33 59阅读 0赞

此笔记为尚硅谷MySQL高级篇部分内容

**目录**

一、MySQL的数据目录

小结

二 、用户与权限管理

MySQL8密码管理(了解)

权限管理

授予权限的原则

收回权限

权限表

访问控制(了解)

角色管理

三、逻辑架构

服务器处理客户端请求

MySQL 中的 SQL执行流程

SQL语法顺序

Oracle中的SQL执行流程(了解)

数据库缓冲池(buffer pool)

四、存储引擎

InnoDB 引擎：具备外键支持功能的事务存储引擎

MyISAM 引擎：主要的非事务处理存储引擎

面试（InnoDB 和MyISAM 区别）

--------------------

# 一、MySQL的数据目录 #

--------------------

### **小结**  ###

> 举例： 数据库a ， 表b 。  
> 1、如果表b采用 InnoDB ，data\\a中会产生1个或者2个文件：  
> **b.frm ：描述表结构文件，字段长度等**  
> 如果采用 **系统表空间** 模式的，数据信息和索引信息都存储在 ibdata1 中  
> 如果采用 **独立表空间** 存储模式，data\\a中还会产生 b.ibd 文件（**存储数据信息和索引信息**）  
> 此外：  
> ① MySQL5.7 中会在data/a的目录下生成 **db.opt 文件用于保存数据库的相关配置**。比如：字符集、比较  
> 规则。而MySQL**8.0不再提供db.opt文件**。  
> ② MySQL8.0中**不再单独提供b.frm，而是合并在b.ibd文件中**。  
> **2、**如果表b采用 MyISAM ，data\\a中会产生**3个文件**：  
> MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。  
> MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等  
> b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)  
> b.MYI (MYIndex)：存放索引信息文件

> InnoDB引擎  
> 1.独立表空间和系统表空间（5.7后是存储数据的是放在独立表空间的）  
> 2.8.0以后frm独立表空间和ibd表结构合并为一个文件放idb里了（可以使用提供的idb2sdi解析器查看idb）  
> MyISAM引擎  
> 1.它的表索引和数据是分开存的

# 二 、用户与权限管理 #

--------------------

> ### **MySQL8密码管理(了解)** ###
> 
> 1. 密码过期策略  
> 在MySQL中，数据库管理员可以 手动设置 账号密码过期，也可以建立一个 自动 密码过期策略。  
> 过期策略可以是 全局的 ，也可以为 每个账号 设置单独的过期策略。
> 
> 2. 密码重用策略

### **权限管理**  ###

![d21999cb1a374cabac40e093f1c49c61.png][]

> ### **授予权限的原则** ###
> 
> 权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：  
> 1、只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可  
> 以了，不要给用户赋予update、insert或者delete权限。  
> 2、创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。  
> 3、为每个用户 设置满足密码复杂度的密码 。  
> 4、 定期清理不需要的用户 ，回收权限或者删除用户。
> 
> 我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组。  
> 所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；  
> 所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是  
> 删除。

> ### **收回权限** ###
> 
> 收回权限就是取消已经赋予用户的某些权限。**收回用户不必要的权限可以在一定程度上保证系统的安全性。**
> 
> **注意：**在将用户账户从user表删除之前，应该收回相应用户的所有权限。

> ### **权限表**  ###
> 
> **user表：**是MySQL中最重要的一个权限表， 记录用户账号和权限信息
> 
> **db表：**
> 
> 1. 用户列db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。  
> 2. 权限列Create\_routine\_priv和Alter\_routine\_priv这两个字段决定用户是否具有创建和修改存储过程的权限。
> 
> **tables\_priv表和columns\_priv表：**tables\_priv表用来 对表设置操作权限 ，columns\_priv表用来对表的 某一列设置权限
> 
> **procs\_priv表：**可以对存储过程和存储函数设置操作权限

> ### **访问控制(了解)** ###
> 
> **1.连接核实阶段**
> 
> 客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication\_string这3个字段匹配客户端提供信息。
> 
> 如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。
> 
> **2.请求核实阶段**
> 
> ![9d88b4def44546ce850dbee87a3026b6.png][]

> ### **角色管理**  ###
> 
> 引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
> 
> ![f168f0c4f39343d5830b51b11ac88453.png][]

# 三、逻辑架构 #

--------------------

### **服务器处理客户端请求**  ###

![e1bc8b0f195d4413b838c47c4d35c71b.png][]

![f228b33d0f134c6397f9a67e0c2f4640.png][]

**小结**

![ad193ff8ccd94a8f9ca65d95f557c88b.png][]

> 简化为三层结构：  
> 1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；  
> 2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；  
> 3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

### MySQL 中的 SQL执行流程 ###

![52b7e92a0b1242bfa39957c7bc85a660.png][]

> **MySQL的查询流程：**
> 
> **1. 查询缓存**：查询缓存中发现了这条 SQL 语句，就会将结果返回给客户端；如果没有，就进入到解析器阶段。(MySQL8.0 弃用，因为查询的任何字符(空格、大小写等)不同都不会命中)
> 
> **2. 解析器：**在解析器中对 SQL 语句进行语法分析、语义分析。
> 
> **3. 优化器：**在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索 等。
> 
> **4. 执行器：**截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。

### SQL语法顺序 ###

![a0f08399038149538d647f6bc9b05151.png][]

### Oracle中的SQL执行流程(了解) ###

> Oracle 中采用了 **共享池** 来判断 SQL 语句是否存在缓存和执行计划，通过这一步骤我们可以知道应该采用硬解析还是软解析。最主要的作用是缓存 SQL 语句和该语句的执行计划。

![dc82699021ba446ea955fe7b815bb783.png][]

### 数据库缓冲池(buffer pool) ###

> InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池 ，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。
> 
> InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。

> **查询缓存**是提前把 查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。
> 
> 缓冲池和查询缓存是一个东西吗？不是。

**缓冲池如何读取数据**

![e31bd809bb6240cdad3c753fe7af55d4.png][]

缓存池中的数据会隔一段时间统一再同步到磁盘上

# 四、存储引擎 #

--------------------

### InnoDB 引擎：具备外键支持功能的事务存储引擎 ###

![9b6767169bd04729a5abb0f0ad6439a4.png][]

### MyISAM 引擎：主要的非事务处理存储引擎 ###

![497fdc6ed1524eb6b855c38f00a91219.png][]

### 面试（InnoDB 和MyISAM 区别） ###

![dffa2d5b72494326b725b1cb40b50179.png][]

> Archive 引擎：用于数据存档
> 
> Blackhole 引擎：丢弃写操作，读操作会返回空内容
> 
> CSV 引擎：存储数据时，以逗号分隔各个数据项
> 
>  *  创建CSV表还会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相同，后缀为 CSM 。
> 
> Memory 引擎：置于内存的表
> 
>  *  Memory采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。
> 
> Federated 引擎：访问远程表
> 
> Merge引擎：管理多个MyISAM表构成的表集合
> 
> NDB引擎：MySQL集群专用存储引擎

高级篇笔记PDF自取

链接：[https://pan.baidu.com/s/1pVqrTwIZFoED77i-EFmw6g?pwd=3333 ][https_pan.baidu.com_s_1pVqrTwIZFoED77i-EFmw6g_pwd_3333]  
提取码：3333

[d21999cb1a374cabac40e093f1c49c61.png]: https://img-blog.csdnimg.cn/d21999cb1a374cabac40e093f1c49c61.png
[9d88b4def44546ce850dbee87a3026b6.png]: https://img-blog.csdnimg.cn/9d88b4def44546ce850dbee87a3026b6.png
[f168f0c4f39343d5830b51b11ac88453.png]: https://img-blog.csdnimg.cn/f168f0c4f39343d5830b51b11ac88453.png
[e1bc8b0f195d4413b838c47c4d35c71b.png]: https://img-blog.csdnimg.cn/e1bc8b0f195d4413b838c47c4d35c71b.png
[f228b33d0f134c6397f9a67e0c2f4640.png]: https://img-blog.csdnimg.cn/f228b33d0f134c6397f9a67e0c2f4640.png
[ad193ff8ccd94a8f9ca65d95f557c88b.png]: https://img-blog.csdnimg.cn/ad193ff8ccd94a8f9ca65d95f557c88b.png
[52b7e92a0b1242bfa39957c7bc85a660.png]: https://img-blog.csdnimg.cn/52b7e92a0b1242bfa39957c7bc85a660.png
[a0f08399038149538d647f6bc9b05151.png]: https://img-blog.csdnimg.cn/a0f08399038149538d647f6bc9b05151.png
[dc82699021ba446ea955fe7b815bb783.png]: https://img-blog.csdnimg.cn/dc82699021ba446ea955fe7b815bb783.png
[e31bd809bb6240cdad3c753fe7af55d4.png]: https://img-blog.csdnimg.cn/e31bd809bb6240cdad3c753fe7af55d4.png
[9b6767169bd04729a5abb0f0ad6439a4.png]: https://img-blog.csdnimg.cn/9b6767169bd04729a5abb0f0ad6439a4.png
[497fdc6ed1524eb6b855c38f00a91219.png]: https://img-blog.csdnimg.cn/497fdc6ed1524eb6b855c38f00a91219.png
[dffa2d5b72494326b725b1cb40b50179.png]: https://img-blog.csdnimg.cn/dffa2d5b72494326b725b1cb40b50179.png
[https_pan.baidu.com_s_1pVqrTwIZFoED77i-EFmw6g_pwd_3333]: https://pan.baidu.com/s/1pVqrTwIZFoED77i-EFmw6g?pwd=3333%C2%A0