数据库系列1 MySQL入门

参考:
数据库三大范式和五大约束：https://www.cnblogs.com/waj6511988/p/7027127.html

一.MySQL介绍

• 由瑞典MySQL AB公司开发，目前属于Oracle
• 开源的关系型数据库管理系统，分社区版和企业版
• 可以存储海量数据，但难以处理海量数据
• 轻量、稳定、易安装、易使用、高性能

二.MySQL基础架构

1.物理层

• 实际物理磁盘上的数据库文件，如数据文件、日志等

2.Server层

• 连接器
  身份认证和权限相关的功能
  包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也不受影响
• 查询缓存(MySQL 8.0 版本后移除)
  缓存执行的 SELECT 语句以及该语句的结果集
  连接建立后，执行查询语句的时候，会先查询缓存，检查这个 sql 是否执行过，是否以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。执行缓存查询时会校验用户的权限，是否有该表的查询条件
  MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的
• 分析器
  MySQL 没有命中缓存，那么就会进入分析器，一是词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。二是语法分析，判断输入的 sql 是否符合 MySQL 的语法
  之后MySQL 就准备开始执行了，但是如何执行是最好的结果呢？此时需要优化器上场了。
• 优化器
  优化器的作用就是它认为的最优的执行方案去执行(有时候可能也不是最优)，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等
• 执行器
  当选择最优执行方案后，执行器会校验该用户有没有执行权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果

3.存储引擎层

• 负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎

MySQL基本架构

4.MySQL体系结构

• 连接池组件
• 管理服务和工具组件
• SQL接口组件
• 查询分析器组件
• 优化器组件
• 缓冲(Cache)组件
• 插件式存储引擎
• 物理文件

三.MySQL存储引擎

• 存储引擎指表的类型以及表在计算机上的存储方式
• MySQL常用存储引擎有InnoDB、MyISAM、NOB和Memory
• 存储引擎是基于表的，而非基于数据库

1.InnoDB

• 设计目标面向在线事务处理(OLTP)应用
• 灾备恢复性好
• 支持全部4种隔离级别事务，默认事务隔离级别是可重复读
• 行级锁
• 支持外键
• 支持全文索引(MySQL5.6版本后)

2.MyISAM

• 设计目标面向在线事务分析(OLAP)应用
• 灾备恢复性不佳
• 不支持事务
• 表级锁，并发性差
• 不支持外键
• 支持全文索引

3.NOB

• 集群存储引擎
• 数据全部放在内存

4.Memory

• 适用于存储临时数据的临时表
• 数据放在内存中，速度快，数据库重启或崩溃将使数据消失

四.语句分析

1.查询语句

select * from tb_student A where A.age='18' and A.name='张三';

• 执行流程：权限校验 - 查询缓存 - 分析器 - 优化器 - 权限校验 - 执行器 - 引擎
• 检查该语句是否有权限
  1）没有权限直接返回错误信息
  2）有权限，(MySQL8.0前)先查询缓存，以此条sql语句为key在内存中查询是否有结果，如果有直接命中缓存，否则执行下一步
• 通过分析器进行词法分析，提取sql语句的关键元素，比如提取上述语句是查询select，需要查询的表名是tb_student，需要查询所有列，查询条件是age=18和name=张三。然后判断这个sql语句是否有语法错误，如果检查没问题则进行下一步
• 优化器确定执行方案，上述语句可能的执行方案
  1）先查询姓名为张三的学生，再判断年龄是否为18
  2）先查询年龄18的学生，再查询其中姓名为张三的
  优化器根据优化算法选择执行效率最好的(实际不一定最好)执行计划，确认后执行
• 进行权限校验
  1）没有权限返回错误信息
  2）有权限则调用数据库引擎接口，返回引擎的执行结果

2.更新语句

update tb_student A set A.age='19' where A.name=' 张三 ';
更新语句基本与查询语句流程一致，但在执行更新时肯定要记录日志，MySQL自带日志模块binlog(归档日志)，适用于所有存储引擎，InnoDB引擎还自带redolog(重做日志)，下面以InnoDB模式探讨上述语句执行流程

• 执行流程：分析器 - 权限校验 - 执行器 - 引擎 - redo log prepare - binlog - redo log commit
• 先查询到要更新的目标数据，如果有缓存，也是会用到缓存
• 然后将 age 改为 19，调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交
• 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态
• 更新完成
  使用双日志模块的InnoDB引擎具有crash-safe能力(即使数据库发生异常重启，之前提交的记录也不会丢失)

五.其他

1.数据库和实例

• 数据库(database)：物理操作系统文件或其他形式文件的集合
• 实例(instance) = 后台线程 + 共享内存区：位于用户与操作系统之间的一层数据管理软件
• 用户通过数据库实例对数据库数据进行操作(数据库定义、数据查询、数据维护等)

2.数据库建表三范式

• 第一范式
  数据表中的每一列(字段)都是不可拆分的最小单元，目的是确保每列的原子性
• 第二范式
  数据库满足第一范式后，要求表中所有的列(字段)，都必须依赖于主键，不能有任何一列与主键无关，目的是确保一张表只描述一个事情
• 第三范式
  满足第二范式后，每一列与主键直接相关，而不是间接相关

3.数据库五大约束

• 主键约束 Primary KEY
• 唯一性约束 UNIQUE
• 默认值约束 DEFAULT
• 非空约束 NOT NULL
• 外键约束 FOREIGN KEY

此夜若无月，一年虚过秋