DYUE.TOP - mysql
type: channel
link: https://www.dyue.top/index.php/tag/mysql
items: mysql两万字精华总结

mysql两万字精华总结
type: item
date: 2021-12-25T19:12:04+08:00
description: 基础架构这是官网的一个架构图，总体是可以分为四层的。 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互​可以从sql的执行过程分析总体的架构，比如下图： 连接器客户端请求连接数据库时，连接器就会负责跟客户端建立连接、获取权限、维持和管理连接。MySQL服务器端也会有一个连接池，因为一般都会有多个系统与MySQL建立很多个连接，MySQL通过这个连接池去维护与客户端的数据库连接。 除此之外，连接器还会根据请求的账号和密码，进行安全认证，库表权限认证。如果用户名或密码不对，就会收到一个"Access denied for user"的错误。如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这也意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。 连接完成后，如果没有后续的动作，这个连接就处于空闲状态。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。 对于一个 MySQL连接，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。可以使用 **SHOW FULL PROCESSLIST **命令查看当前连接的状态（Command列）。在一个查询的生命周期中，状态会变化很多次。 MySQL官方手册中给出了如下状态：Sleep：处于空闲状态，线程正在等待客户端发送新的请求。Query：线程正在执行查询或者正在将结果发送给客户端。Locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做 GROUP BY 操作，要么是文件排序操作，或者是 UNION 操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。Sorting result：线程正在对结果集进行排序。Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。​缓存在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。 如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行，这个效率会很高。 但大多数情况下建议不要使用查询缓存，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能缓存起来的结果还没使用，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。 需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。查询解析器解析器来拆解这个SQL，生成一棵对应的解析树，将其变成MySQL能理解的东西。 首先会进行词法分析，SQL语句是由多个字符串和空格组成的，MySQL 需要识别出里面的…