mysql学习整理

MySQL 可以分为 Server 层和存储引擎层两部分,Server 层包括连接器、查询缓存、分析器、优化器、执行器等,而存储引擎层负责数据的存储和提取,架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

在完成经典的TCP握手后,连接器就要开始认证身份,如果不对,就会提示Access denied for user,如果认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限(这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限)。

连接完成后,没有后续的动作,这个连接就处于空闲状态,可用show processlist命令查看(command为sleep就是空闲连接),客户端如果太长时间没动静,连接器就会自动将它断开,默认是8小时,如果被断开之后,客户端再次发送请求的话,就会收到一个错误提醒: Lost connection to MySQL server during query。

建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。但全部使用长连接后, MySQL 占用内存涨得特别快,这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是 MySQL 异常重启了。

怎么解决这个问题呢?有两种方案:

定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。

MySQL 5.7 以上版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

查询缓存

MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。

之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。

查询缓存往往弊大于利,查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了。

MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND,MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

分析器

MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析

分析器先会做“词法分析”,MySQL 需要识别出里面的字符串分别是什么,代表什么。

根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒,一般语法错误会提示第一个出现错误的位置,所以你要关注的是紧接“use near”的内容(如下:)


mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

优化器

经过了分析器,MySQL 就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。比如你执行下面这样的语句,这个语句是执行两个表的 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。

这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。

执行器

执行器就如字面意思是执行sql语句

执行器执行之前,会先判断对表有没有执行查询的权限,如果没有,就会返回没有权限的提示(如果是命中查询缓存的话,就会在上一步优化器之前调用 precheck验证权限)

如果有权限的话,就会调用表定义的引擎接口打开表继续执行,如InnoDB 引擎,会从表中取第一行,判断值是不是要查询的结果(不是则跳过),继续读取下一行,当遍历表的所有结果将返回记录集。

执行器有一个rows_examined 的字段表示sql语句执行过程中扫描的行数,但这个值是执行器每次调用引擎获取数据行的时候累加的。在某些场景下与引擎扫描行数并不一致,原因:执行器调用一次,在引擎内部则扫描了多行。

总结

sql是怎么执行的?

1、先通过连接器连接上mysql服务器,并获取当前权限

2、通过查询缓存命中最新的查询,返回结果(此步骤8.0版本将不再用)

3、通过分析器分析要做什么操作(select?update?delete……)

4、通过优化器确定要怎么做(先明确表的操作顺序、语句的执行顺序)

5、通过执行器调用表引擎读取处理数据,并返回结果(执行之前会做权限检验)