MySQL优化1之MySQL架构介绍

1、总体概述

MySQL插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎，使得MySQL可以在多种不同场景中应用并发挥良好作用。

2、逻辑架构介绍

2.1、连接层

MySQL最上面的一层，引入了线程池的概念，可以和多个客户端进行连接。主要负责：连接处理、授权认证、安全防护等。

2.2、服务层

大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等

2.3、存储引擎层

存储引擎层，存储引擎负责实际的MySQL数据的存储与提取，服务器通过API 与 存储引擎进行通信。

3、关键组件

3.1、 连接器

客户端连接MySQL时，连接器会对其进行基于用户名、主机信息和密码的认证；除了基本认证之外，连接器还会进行一些线程的处理。

连接命令：

mysql -h$ip -P$port -u$user -p

3.2、 查询缓存

对于select语句，在进入解析器之前会先查询缓存，如果查询缓存中有对应的结果，那么MySQL不用再执行解析、优化和执行的过程，而是直接返回查询缓存中的结果集。

查询缓存弊大于利，不推荐使用。

原因：

1、查询缓存经常失效。只要一张表更新，那么表上的查询缓存全部清空。只适用于静态表或者更新频率低的表，如系统配置表。

2、查询缓存存在严重的可伸缩性问题，且很容易成为严重的瓶颈缓存。MySQL8.0已经取消了查询缓存。

MySQL提供了“按需使用”的方式来使用查询缓存。可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，如下：

select SQL_CACHE * from T where ID=10；

3.3、 解析器

如果缓存没有命中，MySQL会对sql进行解析，将人能看懂的sql解析成MySQL可以识别的语言。

解析步骤：

Step1 词法解析：输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么

Step2 语法解析：根据词法解析的结果，语法解析器会根据语法规则，判断输入的这个SQL语句是否满足MySQL语法

3.4、 优化器

经过了解析器之后MySQL知道我们要干什么，然后MySQL会使用他认为最好的方式来优化sql，并生成一条条执行计划。例如在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，决定各个表的连接顺序。

3.5、 执行器

执行器会根据一系列的执行计划去调用存储引擎的接口去完成SQL的执行。开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

4、其他组件

其他组件

5、存储引擎

查看mysql提供了哪些存储引擎

show engines;

查看mysql当前默认的存储引擎

show variables like '%storage_engine%';

MyISAM和InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务