MySQL再叙（体系结构、存储引擎、索引、SQL执行过程）

最新推荐文章于 2024-10-01 17:33:05 发布

2401_84814920

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量359

点赞数 5

分类专栏：程序员文章标签： mysql sql adb

本文链接：https://blog.csdn.net/2401_84814920/article/details/138965822

版权

程序员专栏收录该内容

116 篇文章 0 订阅

订阅专栏

系统管理和控制工具，mysqldump、 MySQL复制集群、分区管理等

SQL接口（SQL Interface）

接受用户的sql命令，返回用户需要查询的结果

查询分析器（Parser）

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的

查询优化器（Optimizer）

对sql语句进行查询优化。

优化器的作用主要是对查询语句进行优化操作，包括选择合适的索引，数据的读取方式，包括获取查询的开销信息，统计信息等，

缓存和高速缓存区（Cache和Buffer）

查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

插件式存储引擎（pluggable storage Engines）
负责数据的存储和提取，基于表，非数据库。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
文件系统（File System）

文件系统，数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等。

文件系统层主要是将数据库的数据存储在操作系统的文件系统之上，并完成与存储引擎的交互。

参数文件：告诉Mysql实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置。用文件存储，可编辑，若启动时加载不到则不能成功启动（与其他数据库不同）
日志文件：用来记录Mysql实例对某种条件做出响应时写入的文件。如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等
错误日志：通过show variables like 'log_error’来查看错误日志存放地址
慢查询日志：通过show variables like ‘%long%’ 查看慢查询日志记录的阈值，新版本设成了0.05；通过show variables like 'log_slow_queries’查看是否开启了，默认为关闭的；通过show variabes like 'log_queries_not_using_indexes’查看是将没有使用索引的查询记录到慢日志中。mysql中可以直接通过mysqldumpslow命令来查看慢日志。
二进制文件：不记录查询，只记录对数据库所有的修改操作。目的是为了恢复(point-in-time修复)和复制。通过show variables like 'datadir’查看存放路径。二进制日志支持STATEMENT、ROW、MIX三种格式，通过binlog_format参数设定，通常设置为ROW，可以为数据库的恢复和复制带来更好的可靠性，但会带来二进制文件大小的增加，复制时会增加网络开销。mysql中通过mysqlbinlog查看二进制日志文件内容。
socket文件：当用Unix域套接字方式进行连接时需要的文件。
pid文件：Mysql实例的进程ID文件。
Mysql表结构文件：用来存放Mysql表结构定义文件。因为Mysql插件式存储引擎的体系结构，每个表都有一个对应的文件，以frm后缀结尾。
存储引擎文件：存储自己的文件来保存各种数据，真正存储了数据和索引等数据。下面主要介绍InnoDB的存储引擎下的表空间文件和重做日志文件。
表空间文件：InnoDB默认的表空间文件为ibdata1,可通过show variables like 'innodb_file_per_table’查看每个表是否产生单独的.idb表空间文件。但是，单独的表空间文件仅存储该表的数据、索引和插入缓冲等信息，其余信息还是存放在默认的表空间中。
重做日志文件：实例和介质失败，重做日志文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。参数innodb_log_file_size指定了重做日志文件的大小；innodb_log_file_in_group指定了日志文件组中重做日志文件的数量，默认为2，innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，代表只有一个日志文件组，没有镜像；innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。

存储引擎

===============================================================

存储引擎是MySQL区别于其他数据库的一个最重要特性。

插拔式的插件方式
存储引擎是指定在表之上的，即一个库中的每一个表都可以指定专用的存储引擎。
不管表采用什么样的存储引擎，都会在数据区，产生对应的一个frm文件（表结构定义描述文件)

SQL执行过程

==================================================================

select的执行过程

我们来看一个select的查询语句，在数据库层面都经过了哪些过程呢？

select * from dh_admin_user where lid=‘42’;

1.连接到数据库，对应的是连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。 mysql -h port -u$user -p
查看连接正在做什么，命令: show full processlist / show processlist

2.查询缓存

key为sql，value为sql查询结果集缓存中有直接返回，直接返回；没有的话，执行后续步骤，执行结果存入缓存中。
默认关闭，使用query_cache_type设置

小插曲，为什么默认关闭呢？

1.在查询之前必须先检查是否命中缓存,浪费计算资源

2.如果这个查询可以被缓存，那么执行完成后，MySQL发现查询缓存中没有这个查询，则会将结果存入查询缓存，这会带来额外的系统消耗

3.针对表进行写入或更新数据时，将对应表的所有缓存都设置失效。

4.如果查询缓存很大或者碎片很多时，这个操作可能带来很大的系统消耗

3.查询优化处理

解析sql(词法分析): sql语句解析成解析树
预处理阶段（语法分析）:检查解析树的合法性：表和列是否存在，权限校验
查询优化器：执行计划生成，索引选择

4.执行器：查询执行引擎

权限校验，根据表选择引擎

5.返回客户端

结果集返回客户端

和mysql的体系结构相结合

update的执行过程

update T set c=c+1 where ID=2;

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。