简单来说 MySQL 主要分为 Server 层和存储引擎层:
连接器:主要和身份认证和权限相关的功能相关,就好比一个级别很高的门卫一样。
主要负责用户登录数据库,进行用户的身份认证,包括校验账户密码,权限等操作,如果用户账户密码已通过,连接器会到权限表中查询该用户的所有权限,之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据,也就是说,后续只要这个连接不断开,即使管理员修改了该用户的权限,该用户也是不受影响的。
查询缓存:主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
如果缓存 key 被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,完成后也会把结果缓存起来,方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限,是否有该表的查询条件。MySQL 查询不建议使用缓存,因为查询缓存失效在实际业务场景中可能会非常频繁,假如你对一个表更新的话,这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说,使用缓存还是可以的。MySQL 8.0 版本后删除了缓存的功能。
分析器:MySQL 没有命中缓存,那么就会进入分析器,分析器主要是用来分析 SQL 语句是来干嘛的,分析器也会分为几步:
优化器:优化器的作用就是它认为的最优的执行方案去执行(有时候可能也不是最优),比如多个索引的时候该如何选择索引,多表查询的时候如何选择关联顺序等。可以说,经过了优化器之后可以说这个语句具体该如何执行就已经定下来。
执行器:当选择了执行方案后,MySQL 就准备开始执行了,首先执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果。
查询该语句是否有权限,如果没有权限,则直接返回错误信息;如果有权限,在8.0版本之前会先查询缓存,以SQL语句为key在内存中查询是否有结果,如果有则直接返回,没有则执行下一步
通过分析器进行词法分析,提取 SQL 语句的关键元素,比如提取上面这个语句是查询 select,提取需要查询的表名为 tb_student,需要查询所有的列,查询条件是这个表的 id=‘1’。然后判断这个 SQL 语句是否有语法错误,比如关键词是否正确等等,如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案,一条 SQL 语句可能有多种执行方案,如:
a.先查询学生表中姓名为“张三”的学生,然后判断是否年龄是 18。
b.先找出学生中年龄 18 岁的学生,然后再查询姓名为“张三”的学生。
那么优化器根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为,有时候不一定最好)。那么确认了执行计划后就准备开始执行了。
进行权限校验,如果没有权限就会返回错误信息,如果有权限就会调用数据库引擎接口,返回引擎的执行结果。
数据库崩溃时进行恢复:
如果redo log里的事务是完整的,也就是又了commit标识,则直接提交
如果redo log里的事务处于prepare状态,则判断对应的事务在binlog里是否完整
如果完整则直接提交事务
否则则使用undo log进行回滚
因为binlog没有记录的话,从库在进行同步时无法执行这个操作,主库如果不回滚继续执行,就会导致主从不一致;同理如果binlog里的是完整的就算redo log状态不为commit也应该提交。
具体细节可以查看文章MySQL事务详解与隔离级别的实现
MySQL ⽀持多种存储引擎,你可以通过 show engines 命令来查看 MySQL ⽀持的所有存储引擎
MySQL 5.5.5 之前,MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后,InnoDB 是 MySQL 的默认存储引擎
MySQL 存储引擎采用的是插件式架构,⽀持多种存储引擎,我们甚⾄可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的,而不是数据库。
InnoDB与MyISAM的对比:
事务:MyISAM不支持事务,InnoDB支持事务,对于InnoDB每一条语句都默认被封装成事务自动提交
锁:MyISAM只有表级锁,InnoDB支持行级锁和表级锁,默认为行级锁
外键:MyISAM不支持外键,InnoDB支持外键(一个包含外键的InnoDB表转换为MyISAM会失败)
外键对维护数据一致性有帮助,但是对性能有一定的损耗,通常不使用外键而是在代码中进行约束
索引:MyISAM和InnoDB都是采用B+树作为索引结构,但是MyISAM是非聚集索引(索引和数据分离),而InnoDB的主键索引是聚集索引
安全:MyISAM不支持数据库崩溃后的安全恢复,而InnoDB支持(依赖于redo log)
MVCC:MyISAM不支持,InnoDB支持。MyISAM连行级锁都不支持,MVCC作为行级锁的一种升级自然更不支持
count:MyISAM将数据总行数存储在了磁盘,查询总行数时可以直接返回,而InnoDB数据表的行数无法预先确定(因为InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的),因此只能在执行查询时根据当前事务进行确定,调用count时需要重新计算行数
- count(*): 对key_len最短的二级索引进行遍历,如果没有,那么就根据聚簇索引进行遍历**(不读取聚簇索引的值)**
- count(1):对聚簇索引进行遍历**(不读取聚簇索引的值)**
- count(索引字段):对聚簇索引进行遍历**(读取索引的值,不统计null)**
- count(非索引字段):全表扫描
全文索引:MyISAM支持全文索引,且查询速率更快,InnoDB5.7之前不支持全文索引
主键:MyISAM可以没有,InnoDB必须有,若没有指定则系统会自己找一列有元素都不相等的列,用这一列去组织一颗B+tree。如果不存在该列则创建一个隐藏列row_id来维护
从 InnoDB 逻辑存储结构来看,所有的数据都被逻辑的存放在一个空间中,这个空间就叫做表空间(tablespace)。**表空间由 段(segment)、区(extent)、页(page)**组成。
当我们创建一个使用InnoDB存储引擎的表后,便会在磁盘中创建一个表名.ibd文件,这就是其对应的表空间。
一个表包括索引段,数据段,回滚段等。其中索引段就是非叶子结点部分,而数据段就是叶子结点部分,回滚段用于数据的回滚和多版本控制。
一个段包含256个区,一个区对应64个数据页,一个数据页默认为 16K,一个数据区就是1MB
执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后,具体的执行方式。
执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN
的结果,可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
EXPLAIN
语句并不会真的去执行相关的语句,而是通过查询优化器对语句进行分析,找出最优的查询方案,并显示对应的信息。
执行结果总共有如下12列:
id:一个SQL语句中可能包含多个SELECT,id就是用来标识整个查询中 SELELCT 语句的顺序。如果id相同则从上往下执行,id不同则id大的优先级高,如果引用了其他行的并集结果,则该值可以为NULL
select_type:查询的类型,主要用于区分普通查询、联合查询、子查询等复杂的查询
table:查询用到的表名,每行都有对应的表名,表名除了正常的表之外,也可能是以下列出的值
: 本行引用了 id 为 M 和 N 的行的 UNION 结果
: 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询
: 本行引用了 id 为 N 的表所产生的的物化子查询结果partitions:匹配的分区,对于未分区的表,值为 NULL
type:查询执行的类型,描述了查询是如何执行的。所有值的顺序从最优到最差排序为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys:表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ,则表示没有可能用到的索引;这种情况下,需要检查 WHERE 语句中所使用的的列,看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。
key:表示 MySQL 实际使用到的索引。如果为 NULL,则表示未用到索引。
key_len:列表示 MySQL 实际使用的索引的最大长度;当使用到联合索引时,有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ,则 key_len 列也显示 NULL 。
ref:当使用索引等值查询时,与索引作比较的列或常量
rows:表示根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好。
filtered:按表条件过滤后,留存的记录数的百分比
Extra:包含了 MySQL 解析查询的额外信息,通过这些信息,可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下:
Using filesort:在排序时使用了外部的索引排序,没有用到表内索引进行排序。
Using temporary:MySQL 需要创建临时表来存储查询的结果,常见于 ORDER BY 和 GROUP BY。
Using index:表明查询使用了覆盖索引,不用回表,查询效率非常高。
Using index condition:表示查询优化器选择使用了索引条件下推这个特性。
Using where:表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop):连表查询的方式,表示当被驱动表的没有使用索引的时候,MySQL 会先将驱动表读出来放到 join buffer 中,再遍历被驱动表与驱动表进行查询。
当 Extra 列包含 Using filesort 或 Using temporary 时,MySQL 的性能可能会存在问题,需要尽可能避免
参考文章:
InnoDB存储引擎对MVCC的实现 | JavaGuide(Java面试+学习指南)
Mysql_CodingALife的博客-CSDN博客
上一篇:字体设置。
下一篇:区块链溯源是个伪命题