SQL高级优化（二）之MySQL架构

一、架构

      想要学好SQL优化就必须从对应数据库的基本架构开始学习

• 架构图如下

二、架构分析

1. 连接管理与安全验证

​     MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接会验证用户名、密码、主机信息等。

2. 缓存（Cache&Buffer）：

​     缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不在执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。查询缓存的目的是提高查询性能，缺点是做删除修改时影响性能，适合在大量查询很少修改情况时使用。

• 2.1 在MySQL5.7中查询缓存默认不开启。可以通过

SHOW VARIABLES LIKE '%query_cache%

• 2.2 参数中query_cache_type 设置是否开启查询缓存

• • 0（OFF）：关闭Query Cache功能，任何情况下都不会使用Query Cache；

• • 1（ON）：开启Query Cache功能，但是当SELECT语句中使用SQL_NO_CACHE体会后，将不使用Query Cache；

• • 2（DEMAND）：开启Query Cahce功能，但是只有当SELECT语句中使用了SQL_CACHE提示后，才是用Query Cache。

• • 可以通过修改my.ini或命令设置是否开启查询缓存（此命令在Navicat中可能不可用，需要去命令行设置）

set global query_chache_type = 1;

• 2.3 query_cache_size 设置查询缓存大小，默认为0

• • 可以通过下面命令设置、如果设置值太小会导致查询缓存不可用。

set global query_cache_size = 134217728;

• • 可以通过查看缓存被使用次数查看是否使用缓存

show status like '%Qcache%';

• • Qcache_queries_in_cache 当前缓存中数量

• • Qcache_insert 插入到缓存中总数

• • Qcache_hits 缓存命中数

• • Qcache_lowmem_prunes 由于缓存较小，从缓存中山川的查询数量

• • Qcache_not_cached 没有被缓存的次数

三、SQL命令执行流程

　　

• 1、客户端向服务器发送SQL命令。

• 2、服务器端连接模块连接并验证。

• 3、缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。

• 4、解析器解析SQL为解析数，如果出现错误，包SQL解析错误。如果正确，向下传递。

• 5、预处理器对解析数继续处理，处理成新的解析树。

• 6、优化器根据开销自动选择最优执行计划，生成执行计划。

• 7、执行器执行秩相关计划，访问存储引擎接口。

• 8、存储引擎访问物理文件并返回结果。

• 9、如果开启缓存迷魂村管理器把结果放入查询缓存中。

• 10、返回结果给客户端。