简介
通过了解MySQL的逻辑架构,知道各组件之间是如何进行协同工作的,通过了解库表的索引设计、字符集设置一级分库分表等,掌握MySQL的基本开发能力。
MySQL逻辑架构
查询缓存
查询缓存是一个哈希表的结构,key由查询语句本身,查询的数据库和客户端协议的版本等组成的。
虽然查询缓存可以提升查询性能,对应用程序透明,但是写缓存和清缓存会有性能损耗,尤其对写密集的应用。业务侧配置不好会有很大的副作用。
注意:在5.7.20之前的版本还可以使用,但是在5.7.20已经deprecated,并且在8.0开始移除这个功能。
解析器和预处理器
解析器就是进行词法分析和语法分析,主要就是解析关键字,和基本语法规则的检验,比如说关键词的顺序,引号是否闭合等等。
预处理器就是进行表和列的存在判定,以及字段别名是否有歧义和权限校验等等。
查询优化
查询优化包括下面这些方面:

执行计划
EXPLAIN:查看SQL执行计划,也就是优化查询性能的利器。
用法:EXPLAIN + SQL
SHOW WARNINGS:显示最后一个执行语句所产生的的错误、警告和提示等信息,在EXPLAIN之后,执行该语句可以看到重建后的查询语句
执行查询
MySQL对任何关联都执行嵌套循环关联操作
如下图:我们进行一个left join的语句
但是由于我们判断了cityID为null的过滤掉,所以说实际上是下面这样的效果
相当于是使用join的效果,那么将三条结果给执行引擎去处理肯定是更好的,所以执行优化将性能优化了。
储存引擎
存储引擎是负责数据的储存和查询的,主要包括InnoDB和MyISAM。
主要是MyISAM的锁是表级别的锁,当同时写会进行排队,并发特性不如InnoDB。所以MyISAM的查询性能是非常优秀的。
总结:
库表设计
规范

索引

B+Tree和HASH
聚簇索引和非聚簇索引

哪些列建议创建索引?
where 、join、group by、order by等语句使用的列
利用最左前缀
如何选择索引的顺序
- 经常被使用到的列优先
- 区分度大的列优先:比如表中对性别建立索引的意义不大,因为区分度不大。
- 宽度小的列优先:宽度越小,单节点的key值越多,索引数的高度越低,查询复杂度越低。
如何优化索引
使用EXPLAIN的分析
Type
Extra
Possible_keys/Key/Rows
key不能为null,如果null说明没有用索引,使用了全表的查询。rows是越小越好的。
key_len
所以不损失精确性的情况下,key_len越小越好,用不到的字段最好移除。
优化思路
如果还不行的话,我们就调整逻辑
案例:
由于使用了filesort,是不满足我们需求的,性能较差。
可以指定索引来进行查询,但是如果我们倒序的话,又有了using filesort
原因是这样的:
那么我们可以加一个倒序的索引:
加了以后还是有这个using filesort,原因是:index指出设置desc,但是8.0之前的版本会将这个忽略,都按照升序排序。
那么怎么办?
- 和产品写上排序规则
- 如果是整形的话,可以开率储存对应的负值
结果,可以将tagType也进行倒序
原因是:
字符集和校对集
如何查看我们使用的字符集和校对规则呢?
如何设置字符集呢?
案例一
排查:
解决:
案例二
分库分表
什么还是分库分表
为什么要分库分表
什么时候做分库分表
如何做分库分表
案例
- 本文作者: October
- 本文链接: http://www.octber.xyz/2020/10/21/MYSQL逻辑架构及库表设计/
- 版权声明: 本博客所有文章除特别声明外,均采用 MIT 许可协议。转载请注明出处!