SQL-mysql架构入门

一 前言

本篇是关于mysql架构层面的内容,之前的知识追寻者发布的文章都是基础系列文章,如果读者们学习到这边就算是步入MYSQL的高阶内容了,你将理解什么MYSQL的架构与引擎,读写锁,等进阶知识,为以后的MYSQL参数配置,引擎选择,语句优化打下坚实的基础;学习本篇的内容是知识追寻者发布过的基础系列文章

《SQL-你真的了解什么SQL么?》

《SQL-小白最佳入门sql查询一》

《SQL-小白最佳入门sql查询二》

《SQL- 多年开发人员都不懂的插入与更新删除操作注意点》

《SQL-SQL事物操作》

《SQL-Mysql数据类型》

《SQL-mysql视图的前世今生》

《SQL-mysql储存过程》

《SQL-mysql游标与触发器》

《SQL-mysql用户权限管理》

公众号:知识追寻者

知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)

二 mysql 架构

2.1 MYSQL架构概览

MYSQL 的层级大概可以分为3类;第一层 为 连接层,只要负责MYSQL的数据库连接,安全认证的功能;

第二层是MYSQL的核心层面,其主要功能包括,MYSQL的查询,缓存,执行计划,优化等都在第二层实现;

第三层是引擎层,为MYSQL指定不同的引擎将达到不同的数据操作效果;

SQL-mysql架构入门

2.2 Query Cache

MYSQL 的 Query Cache 是 基于 hash 值计算进行匹配的缓存机制;通常在大数据量的情况下如果开启Query Cache 会 频繁的计算Hash ,会增加性能的消耗,得不偿失,生产环境中建议关闭该选项;

可以使用 语句 show VARIABLES like ‘%query_cache%‘ 查看 Query Chach 是否关闭;我们主要关注的是 参数

query_cache_type 是否关闭 ,如果是OFF就是关闭状态,ON是开启状态;而不是 query_cache_size参数,其是缓存分配大小;更多关于 Query Chach 的内容可以参考如下文章

https://blog.csdn.net/dongnan591172113/article/details/52084041

https://www.jianshu.com/p/3ab10180fbd5

2.3 读锁

关于锁的知识希望读者学习过高并发相关知识,对所有的锁分类有个清晰的认识,学习本篇关于锁的概念将不会遇到阻碍;在MYSQL中根据不同的引擎,主要会出现三类锁的情况,即 表锁,读锁 和写锁;读锁很好理解,在MYSQL 中 读锁也是共享锁, 即 多用户状态下同一时间对资源的读取是互不影响,但不能对数据进行修改等操作

一般情况下我们手动给一条或者某个范围内(一般使用在储存过程)的数据加上读锁;

使用语法示例如下

SELECT 字段 from 表名 [where 条件]  lock in share mode;

2.4 写锁

写锁是排他锁,也称为独占锁;使用的情况一般是写入数据的情况下,一个用户如果获得写锁,其它用户将不能获取写锁或者读锁,直到该用户执行完操作并释放锁;其使用方式为在执行语句后加上for update 语句即可

格式示例

SELECT 字段 from 表名 [where 条件] for update;

2.5 锁粒度

锁粒度是指对资源锁定范围的一个程度,使用不同的锁定策略达到并发性能较优的结果;通常锁粒度使用策略情况分为,行锁,表锁,页锁的情况;

表锁:即对整张表进行加锁,其性能开销较小,加锁的速度较快,但缺点也很明显,其锁粒度大,并发低;如果要手动加表锁,语法示例 lock tables 表名,释放锁 unlock tables 表名

行锁:即对行进行锁定,能够最大支持并发量,故锁粒度最小,但其枷锁速度慢,性能消耗大,会出现死锁;行锁的种类又有 记录锁(主键或者唯一索引都属于记录锁),间隙锁(GAP),记录锁和间隙锁的组合(next -key lock);间隙锁一般用于查询条件是范围情况下,而非相等条件;

页锁:通常情况下遇不到页锁,其开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间;

Tip: MyISAM和 Memory 引擎 支持表锁,其会自动给SELECT,UPDATE,INSERT,DELETE 自动加表锁;InnoDB 支持表锁和行锁,对于UPDATE, INSERT ,DELETE 语句 InnoDB 会自动给数据加排他锁,SELECT语句不加锁;

还有锁的其它分类也会使用到比如乐观锁(基于版本号实现),注意点是条件必须是主键,读取时将数据版本号读出,更新数据时,版本号加1;将查询的数据进行对比,如果版本号不一致就是过期数据;

查询示例

select id,value,version from 表名 where id = #{id}

更新示例

update 表名
set value=2,version=version+1
where id=#{id} and version=#{version}

悲观锁(如表锁,行锁,读写锁都是悲观锁);

如果看了知识追寻者写的锁知识还有困惑可以参考如下链接

https://juejin.im/post/5b82e0196fb9a019f47d1823

2.6 引擎简介

在上面的图例中可以看见MYSQL支持多种引擎,当然远不止图中显示的引擎数量;我们主流使用的引擎就是 InnoDB,其次是 MyISAM,特殊情况下会使用到Memory;引擎的知识是一本书都无法概括的内容,知识追寻者在这边给小伙伴们做个简介,有个大概的了解就好;

InnoDB 是使用最广泛的引擎,也是最重要的引擎,读者有必要了解其储存性能;InnoDB 是 可重复读的事物隔离级别,但其实现了next key lock ,防止的幻读出现;其基于聚簇索引实现;其组要组成结构为内存结构,线程,磁盘文件组;

MyISAM在早期版本是MYSQL的默认引擎,在MYSQL5.1之后不再使用;其不支持事物,不支持行锁,默认表锁,并发量低,;

Menory引擎故名思意,其储存内容都是存放在引擎当中,支持Hash和Btree索引,其数据读取快,但缺点也很明显,服务器如果发生故障重启后就会造成数据丢失;

相关推荐