MySQL学习笔记（一）基础架构

大体来说，MySQL可以分为Server层和存储引擎层两部分。

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

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，默认存储引擎。

create table建表，默认使用InnoDB。不同引擎的表数据存取方式不同，支持的功能也不同。

不同的存储引擎共用一个Server层

连接器

首先会连接数据库。连接器负责和客户端建立连接、获取权限、维持和管理连接。

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

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。完成TCP握手后，连接器就要开始认证身份，这个时候就是你输入的用户名和密码。认证通过后，连接器会到权限表里面查出你拥有的权限。

长连接指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。（所以尽量使用长连接）

长连接累积下来可能导致内存占用太大，MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开的时候才释放。内存太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启。

解决的方案：

1. 定期断开长连接。或者执行一个占用内存的大查询后，断开连接，之后要查询再重连。

2.如果版本新，可以在每次执行完大操作后，通过执行mysql_reset_connection重新初始化连接资源。这个不需要重连，会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立完后就可以执行select语句了。

MySQL拿到查询请求后会先到查询缓存看看之前是否执行过这条语句，若执行过，则将结果以key-value对的形式，直接缓存到内存中。

若不在查询缓存中，就会继续执行阶段。执行完成后，执行结果会被存入查询缓存中。

但是大多数时候不要用查询缓存，因为弊大于利。

失效非常频繁。因为只要对一个表有更新，查询缓存就会被清空。对于更新压力大的数据库，查询缓存的命中率会非常低。如果业务是一张静态表，才比较适合。（比如系统配置表）

将参数query_cache_type设置成DEMAND，这样默认不使用查询缓存。对于想使用查询缓存的语句，可以用SQL_CACHE显式指定。

mysql> select SQL_CACHE * from T where ID=10；
8.0后彻底删掉查询缓存这个功能。。。艹

分析器

没有命中查询缓存，就执行。

首先“词法分析”。MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

然后“语法分析”。判断是否符合语法。

优化器

分析完了后，在开始执行前，要经过优化器。

在表里有多个索引的时候决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

如：mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

既可以先从表t1里面取出c=10的记录的id值，再根据id关联到表t2，再判断t2里面d的值是否=20

也可先从t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10；

两种方案逻辑一样但是执行效率不同，优化器的作用就是决定选择哪一个方案。

执行器

执行语句。

执行的时候会判断你对这个表T有没有执行查询的权限，如果没有就会返回没有权限的错误。

有权限，就打开表。根据表的引擎定义，去使用这个引擎提供的接口。

mysql> select * from T where ID=10;

例如：这个表T中，ID字段没有索引，那么：

1、调用InnoDB引擎接口取这个表的第一行，判断ID是不是10，如果不是就跳过，如果是则将这行存在结果集中。

2、调用引擎接口取“下一行”，重复相同逻辑，直到取到最后一行。

3、执行器将上述满足条件的行作为结果集返回客户端。