MySQL基础篇1 mysql的基础架构

大体来说, mysql可以氛围server层和存储引擎层 两部分

逻辑架构图

基本逻辑架构

存储引擎层

负责数据的存储和提取,架构模式是插件式的

支持innodb, myisam, memory, MySQL5.5.5 以后默认innodb,不同存储引擎的表数据存取方式也不同

不同的存储引擎共用一个server层,也就是连接器到执行器的部分

server层

包括连接器,查询缓存,分析器, 优化器, 执行器等, 完成了mysql大多数核心服务功能以及所有的内置函数, 所有跨存储引擎功能都在这一层实现, eg 存储过程 触发器 视图等

连接器

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

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

连接命令中的mysql是客户端工具, 用来与服务器建立连接, 连接的方式也是tcp, 认证用户名和密码

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

show processlist

这个命令可以看到连接mysql服务的所有客户端, 若是空闲连接, 则commod会显示sleep

show processlist

自动断开:客户端长见识sleep连接器会自动将他断开, 时间由参数 wait_timeout控制, 默认8小时

长连接:

• 是指连接成功后,如果客户端持续有请求, 则一直使用同一个连接, 短连接则是每次执行完很少的几次查询就断开连接,下次查询再重新建立
• 由于建立连接比较复杂, 尽量减少连接动作, 建议尽量使用长连接
• 问题是, 全部使用长连接,mysql占用内存会很大, 因为mysql在执行过程中临时使用的内存是管理在连接对象里面的,只有在断开的时候才会释放, 所以长连接可能会被系统杀掉, 从现象看就是mysql异常重启了, 所以说如何解决呢?
  • 定期断开长连接, 使用一段时间或者程序中判断执行过一个占用内存大的查询后,就断开重连
  • 若使用mysql5.7及以上通过执行mysql_rest_connection来重新初始化连接资源, 不需要重连和重新做权限验证

查询缓存

建立完连接以后, 就可以执行select语句了, 执行逻辑就会来到第二步: 缓存查询

mysql拿到一个查询, 会先到查询缓存中, 若之前执行过, 则可能会以key-value 的形式存在, key为查询语句, value为结果, 若key存在,则会直接返回给客户端

!!但是绝大多数时间不建议使用查询缓存, 因为往往弊大于利.

• 失效频繁, 只要对一个表更新, 这个表上所有的查询缓存都会被清空, 这对于更新压力大的数据库来说, 查询缓存的命中率会很低
• 按需使用, 可以将参数query_cache_type设置为DEMAND, 这样默认不是用缓存查询
• 对于确定要使用缓存查询的语句可以用SQL_CACHE显示指定,如下

mysql> select SQL_CACHE * from T where ID=10；

• 注意: 在mysql8.0 之后, 缓存查询功能不存在了

分析器

如果查询缓存未命中,就开始执行真正的语句了,第一步就是解析sql语句

mysql需要识别出里面的字符串分别是什么, 代表着什么

• 识别:mysql从你输入的select这个关键字识别出这是个查询语句,然后将字符串"T"识别为"表名T",将"字符串ID"识别成"列ID"
• 语法分析:语法分析器会根据语法规则,判断你输入的这个sql语句是否满足mysql语法
• 如果语句不对,会收到错误提示

优化器

经过分析器后, mysql知道你要做什么, 在开始执行前还要经过优化器的处理

优化器是在表里面有多个索引的时候,决定使用哪个索引, 或者在一个语句有多表关联(join)时, 决定各个表的连接顺序

eg:

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。

尽管两种执行逻辑的结果是一样的, 但是效率会不同, 所以总言之, 优化器的作用是决定执行的逻辑

执行器

mysql通过分析起知道了你要做什么, 通过了优化器知道了该怎么做, 于是就进入进行阶段,开始执行语句

开始执行前, 首先会验权, 若无权限, 则会返回error,(如果命中查询缓存, 则会在查询返回前做验权, 查询也会在优化器之前调用precheck验权)

验权通过后, 会调用选择引擎提供的借口去查询, 具体执行逻辑如下:

1. 调用innodb引擎取这个表的第一行, 判断id是不是10, 若不是则跳过, 若是则将这行存入结果集
2. 调用引擎接口取"下一行", 重复相同的逻辑判断, 直到最后一行.
3. 执行器将上述结果集返回给客户端

对于有索引的表,执行逻辑基本也就这样,第一次调用的是"取满足条件的第一行", 后续调用的是"满足条件的下一行", 这些接口引擎中已经定义好了

数据库慢查询日志中有rows_examined字段, 表示这个语句执行过程中扫描了多少行.

一些问题:

为什么验权不在优化器之前处理?

原因是因为sql语句要操作的表不只是sql字面上哪些, 比如有个触发器, 只有在执行过程中才能确定

大白话

连接器：门卫，想进请出示准入凭证（工牌、邀请证明一类）。“你好，你是普通员工，只能进入办公大厅，不能到高管区域”此为权限查询。 分析器：“您需要在公司里面找一张头发是黑色的桌子？桌子没有头发啊！臣妾做不到” 优化器：“要我在A B两个办公室找张三和李四啊？那我应该先去B办公室找李四，然后请李四帮我去A办公室找张三，因为B办公室比较近且李四知道张三具体工位在哪” 执行器：“好了，找人的计划方案定了，开始行动吧，走你！糟糕，刚门卫大哥说了，我没有权限进B办公室”