MySQL 知识点总结
我正在参加「掘金·启航计划」
MySQL相信大家都耳熟能详了, 毕竟其还不错的性能和免费的特点深受国人的喜爱, 本篇文章将作为我《MySQL》系列的一篇文章, 主要用作整理和简单的概述MySQL相关的一些知识点
ok, 接下来我们开始进入正题, 从最简单的开始
关系型数据库和非关系型数据库
关系型数据库
关系型数据库是一个结构化的数据库, 创建在关系模型(二维表格模型)基础上, 一般面向于记录
SQL 语句就是一种基于关系型数据库的语言, 用于执行对关系型数据库中数据的检索和操作, 主流的关系型数据库包括: Oracle, MySQL, SQL Server等
本篇文章默认以 MySQL的 InnoDB引擎为主
非关系型数据库
非关系型数据库也叫 NoSQL, 采用键值对的形式进行存储. 一般来讲, 除了主流的关系型数据库之外的数据库, 都认为是非关系型
他的读写性能很高, 易于扩展, 主流的 NoSQL有: Redis, MongDB, Hbase等
适合使用的场景:
- 日志系统
- 数据量巨大
- 地理位置存储
- 高可用
关系型数据库与非关系型数据库之间的区别
- 关系型数据库:
- 采用了关系模型来组织数据, 容易理解
- 可以保持数据的一致性
- 数据更新的开销比较小
- 支持复杂查询(where子句等)
- 非关系型数据库
- 不需要经过 SQL层的解析, 读写效率高
- 基于键值对, 数据的扩展性很好
- 可以支持多种类型的数据的存储, 图片, 文档等
MySQL整体架构
SQL 的执行步骤
在 MySQL中, Server层按顺序执行SQL的步骤如下:
- 客户端请求
- 连接器(验证用户身份, 给与权限)
- 查询缓存(存在缓存则直接返回, 不存在则执行后续操作)
- 分析器(对SQL进行词法分析和语法分析操作)
- 优化器(主要对执行的 SQL优化选择最优的执行方案方法)
- 执行器(执行时会先看用户是否有执行权限, 有才去使用这个引擎提供的接口)
- 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)
接下来我将按照 MySQL执行 SQL的步骤来进行讲解
MySQL 的架构图示
图1 展示了 MySQL各组件之间协同工作的逻辑视图, 也是一条SQL查询的执行流程, 接下来我主要围绕这张图来讲述一下他们和 MySQL之间的关系
图1 MySQL架构逻辑视图(图片来自于JavaGuide)
客户端的服务主要是包括连接处理, 身份验证, 确保安全性等.
大多数 MySQL的核心功能都在第二层, 包括查询解析, 分析, 优化, 以及所有的内置函数, 所有跨存储引擎的功能也都在这一层来实现: 存储过程, 触发器, 视图等
第三层是存储引擎层, 负责MySQL中数据的存储和提取. 其架构模式是插件式的, 支持 InnoDB, MyISAM, Memory等多个存储引擎, 现在最常用的存储引擎是 InnoDB, 在 MySQL 5.5版本之后成为了默认存储引擎
连接器
默认情况下, 每个客户端连接都会在服务器进程中拥有一个线程, 该连接的查询只会在这个线程中执行.
当客户端(应用)连接到 MySQL服务器时, 服务器需要对其进行身份验证, 身份验证基于用户名, 密码, 主机ip和端口号, 客户端连接成功之后, 服务器会继续验证该客户端的查询权限(例如, 是否有查看user库的权限, 是否允许对mysql库的user表进行SELECT)
创建新连接时身份验证基本信息
后面可能也会单独出一篇文章讲一下MySQL的权限, 先插个钉子, 希望后面能填坑
分析优化和执行
查询缓存
在执行一条 SELECT查询语句的时候会先去查询缓存看能否直接命中, 能命中就直接返回, 缓存中没有才会去进行下一步
只要表有更新操作, 那么这张表的缓存就会更新, 所以对于一张更新较[]频繁的表来说缓存命中是比较低的
从 MySQL 5.7.20版本开始, 查询缓存已经被官方标注为废弃了, 8.0版本完全移除
在 MySQL 8.0版本之前, 想关闭查询缓存可以将参数 query_cache_type 设置为 DEMADN
分析器
在该步骤主要进行两件事:
- 词法分析根据你的SQL语句识别出
关键字来构建语法树, 方便后面的模块获取表名, 字段名, where条件等 - 语法分析根据词法分析结果, 语法分析会判断你输入的
SQL语句是否满足 MySQL语法
优化器&执行器
在优化器中主要做了三件事:
- prepare 预处理阶段:
- 查询 SQL中的表, 字段是否存在
- 将 select * 上的
*扩列为所有列
- optimize 优化阶段:
- 判断当前 SQL语句使用的索引类型, 主键索引, 普通索引, 覆盖索引, 全表扫描等
- execute 执行阶段:
- 根据优化结果执行 SQL查询, 从存储引擎中获取查询结果并返回
优化器并不关心查询引擎类型, 但是存储引擎对于查询优化是有影响的.
存储引擎
在 MySQL中, 可以使用 show engines命令来查询 MySQL支持的所有存储引擎
可以看到, 当前默认存储引擎是InnoDB, 同时只有InnoDB是支持事务的
MyISAM和InnoDB的区别
老生常谈了, 也是面试高频考点
| MyISAM | InnoDB | |
|---|---|---|
| 行级锁 | 只有表级锁 | 支持行级锁和表级锁, 默认为行级锁 |
| 事务 | 不支持事务 | 实现了四个隔离级别, 具有提交和回滚事务的能力, 默认使用 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的 |
| 外键 | 不支持 | 支持, 在阿里的<java开发手册>中是不推荐使用外键的 |
| 异常崩溃后的安全恢复 | 不支持 | 支持, redo log |
| MVCC | 不支持 | 支持 |
事务
事务的四大特性
原子性: 一个事务中的数据操作, 要么全部成功, 要么全部失败并回滚到执行之前的状态一致性: 事务操作前后, 数据满足完整性约束, 数据库保持一致性状态隔离性: 事务之间相互隔离, 互不影响, 每个事务都有一个完整的数据空间, 在一个事务结束之前对其他事务来讲应该是不存在的持久化: 事务结束之后, 对数据的操作即为永久的, 即便系统故障也不会消失
隔离级别
未提交读 (READ UNCOMMITTED): 在事务中可以查看其他事务未提交的修改- 读取未提交的事务也叫
脏读
- 读取未提交的事务也叫
读提交 (READ COMMITTED):一个事务可以看到其他事务在他开始之后提交的修改- 同一个事务两次执行相同语句可能会看到不同的数据结果,
不可重复读
- 同一个事务两次执行相同语句可能会看到不同的数据结果,
可重复读(REPEATABLE READ): 同一个事务在多次读取相同行数据的结果相同- 当一个事务执行范围查询过程中, 另外一个事务对该范围进行了插入操作, 当再次对该范围进行查询的时候, 就会出现幻行, 也就是
幻读
- 当一个事务执行范围查询过程中, 另外一个事务对该范围进行了插入操作, 当再次对该范围进行查询的时候, 就会出现幻行, 也就是
串行化(SERIALIZABLE):最高的事务隔离级别, 解决了幻读问题. 其会在读取的每一行数据都进行加锁操作
多个事务之间引发的隔离问题
脏读: 读取未提交的事务不可重复读: 同一个事务两次执行相同语句可能会看到不同的数据结果幻读: 当一个事务执行范围查询过程中, 另外一个事务对该范围进行了插入操作, 当再次对该范围进行查询的时候, 就会出现幻行
不可重复度重点在于修改, 幻读重点在于新增或删除
事务隔离总结
SQL的隔离级别
| 隔离级别 | 脏读 | 不可重复度 | 幻读 | 加锁读 |
|---|---|---|---|---|
| 未提交读 | 是 | 是 | 是 | 否 |
| 提交读 | 否 | 是 | 是 | 否 |
| 可重复读 | 否 | 否 | 是 | 否 |
| 串行化 | 否 | 否 | 否 | 是 |
事务死锁
当有两个或两个以上的事务互相持有和请求相同资源上的锁, 就会产生循环依赖, 我们将其称之为死锁
为了解决事务死锁问题, 数据库系统实现了死锁检测和锁超时机制. 在InnoDB引擎中, 检测到循环依赖后会自动返回一个错误信息
MySQL的日志
在 MySQL中一共有三种日志, 分别是:
redo log(重做日志): 是 InnoDB存储引擎层生成的日志, 实现了事务中的持久性, 主要用于故障恢复undo log(回滚日志): 是 InnoDB存储引擎层生成的日志, 实现了事务中的原子性, 主要用于事务回滚和MVCCbinlog(归档日志): 是 Server层生成的日志, 主要用于数据备份和主从复制
索引
建立索引的注意事项
建议建立索引的字段:
- 频繁查询的
- 用以缩小查询范围的
- 排序的字段 不建议用来建立索引的字段:
- 查询少且涉及的列或重复值多的
- 特殊的数据类型, 例如: text
- 可以为null的字段, 索引为null时会导致引擎放弃使用索引而进行全表扫描
MySQL中的索引
- 主键索引: 加速查询 + 列值唯一(不允许为null) + 表中不允许有重复
- 唯一索引: 加速查询 + 列值唯一(允许有null)
- 普通索引: 加速查询
- 组合索引: 多列组合成一个索引
- 哈希索引
- B树索引
- B+树索引
- 聚簇索引: 表数据和主键一起存储, 主键索引的叶节点存储行数据, 二级索引的叶节点存储行的主键值.
- 非聚簇索引
数据库优化
- 范式优化: 消除冗余
- 反范式优化: 增加冗余, 减少 join
- 限定数据的范围: 禁止不带限制的范围查询
- 读/写分离: 主库写, 从库读
- 拆分表: 分库分表, 物理层面上将数据分区, 可以避免扫描全表, 明显缩短查询时间
本文内容到此结束了 如有收获欢迎点赞👍收藏💖关注✔️，您的鼓励是我最大的动力。 如有错误❌疑问💬欢迎各位大佬指出。 我是 宁轩 , 我们下次再见