MySQL学习笔记-底层原理结构分析

一、sql语句执行原理底层分析

参考文章

Server层结构总结：

1.连接器

• 作用：负责与客户端连接，权限验证，并管理维持连接

• 连接建立与验证：

  net start mysql
  net stop mysql
  mysql -u root - p
  

• 连接管理：

  • 连接一旦建立，不能被影响，只有重新建立新的连接，修改的权限等才能生效

  • 可以通过 show processlist 查看连接状态，客户端超过一定时间无动静，连接自动断开，由wait_timeout控制，一般8小时

    

  • 由于建立连接较复杂，一般采用长连接，但长连接弊端是内存管理在对象里，不断开，资源内存就一直得不到释放，导致内存不断扩大。解决方式有两种：

    • 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
    • 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行客户端的mysql_reset_connection 函数，来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2.查询缓存

• 查询请求先访问缓存(key 是查询的语句， value 是查询的结果)。命中直接返回。
• 不推荐 ，因为由于表的更新，查询缓存的失效很快。
• 关闭缓存：参数 query_cache_type 设置成 DEMAND ，MySQL 8.0后没有查询缓存这功能

3.分析器

• 词法词法解析分析，识别并判断sql语句是否正确
• 涉及问题Q1，Q2

4.优化器

• 决定使用哪个索引，多表关联（ join）的时候，决定各个表的连接顺序。

5.执行器

• 先判断用户有无查询权限
• 打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。逐行遍历执行
• 遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

常见问题汇总：

Q1：数据表和数据列的错误检查在哪个阶段报出。

• 是分析器，《高性能 mysql》里提到解析器和预处理器。 解析器处理语法和解析查询, 生成一课对应的解析树。预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。
• 不是执行器的原因，虽然在执行器阶段打开表读取数据，但，表中的字段，即表名，列名不是数据，是事先定义好的，可以在执行器前读取，因此在分析器阶段就开始解析验证。

Q2：我创建了一个没有 select 权限的用户， 执行 select * from T where k=1， 报错“select command

denied”， 并没有报错“unknown column”， 是不是可以说明是在打开表之后才判断读取的列不存在？

• 涉及到安全问题，先返回有无权限，若无权限，其他检查有无问题都是错误，不能先给列的信息，若无权限，告诉列不对，给的信息就过多了。
• 故，内部逻辑应该是在分析器中分析出 unknown column.
  不是立马返回给客户端，而是去执行器阶段验证该用户是否具有针对该表的操作权限，如果有。则
  返回 unkuown column,如果没有。则返回 select command denied.

Q3：Server 层和存储引擎层各是什么作用?

• Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功
  能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一
  层实现，比如存储过程、触发器、视图等。
• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、 MyISAM、 Memory 等
  多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引
  擎。

Q4: 对于表的操作权限验证在哪里进行?

• 执行器

Q5：执行器的执行查询语句的流程是什么样的?

• 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则
  将这行存在结果集中；
• 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端