此笔记为尚硅谷MySQL高级篇部分内容
目录
一、MySQL的数据目录
小结
举例: 数据库a , 表b 。
1、如果表b采用 InnoDB ,data\a中会产生1个或者2个文件:
b.frm :描述表结构文件,字段长度等
如果采用 系统表空间 模式的,数据信息和索引信息都存储在 ibdata1 中
如果采用 独立表空间 存储模式,data\a中还会产生 b.ibd 文件(存储数据信息和索引信息)
此外:
① MySQL5.7 中会在data/a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如:字符集、比较
规则。而MySQL8.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. 密码重用策略
权限管理
授予权限的原则
权限控制主要是出于安全因素,因此需要遵循以下几个 经验原则 :
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.请求核实阶段
角色管理
引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定,可以确保数据的安全性,这是至关重要的。
三、逻辑架构
服务器处理客户端请求
小结
简化为三层结构:
1. 连接层:客户端和服务器端建立连接,客户端发送 SQL 至服务器端;
2. SQL 层(服务层):对 SQL 语句进行查询处理;与数据库文件的存储方式无关;
3. 存储引擎层:与数据库文件打交道,负责数据的存储和读取。
MySQL 中的 SQL执行流程
MySQL的查询流程:
1. 查询缓存:查询缓存中发现了这条 SQL 语句,就会将结果返回给客户端;如果没有,就进入到解析器阶段。(MySQL8.0 弃用,因为查询的任何字符(空格、大小写等)不同都不会命中)
2. 解析器:在解析器中对 SQL 语句进行语法分析、语义分析。
3. 优化器:在优化器中会确定 SQL 语句的执行路径,比如是根据 全表检索 ,还是根据 索引检索 等。
4. 执行器:截止到现在,还没有真正去读写真实的表,仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。
SQL语法顺序
Oracle中的SQL执行流程(了解)
Oracle 中采用了 共享池 来判断 SQL 语句是否存在缓存和执行计划,通过这一步骤我们可以知道应该采用硬解析还是软解析。最主要的作用是缓存 SQL 语句和该语句的执行计划。
数据库缓冲池(buffer pool)
InnoDB 存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。而磁盘 I/O 需要消耗的时间多,而在内存中进行操作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS 会申请占用内存来作为数据缓冲池 ,在真正访问页面之前,需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。
InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。
查询缓存是提前把 查询结果缓存 起来,这样下次不需要执行就可以直接拿到结果。
缓冲池和查询缓存是一个东西吗?不是。
缓冲池如何读取数据
缓存池中的数据会隔一段时间统一再同步到磁盘上
四、存储引擎
InnoDB 引擎:具备外键支持功能的事务存储引擎
MyISAM 引擎:主要的非事务处理存储引擎
面试(InnoDB 和MyISAM 区别)
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
提取码:3333