MySQL执行计划

MySQL
  1. 1，mysql 客户端/服务端通信
  2. 2，查询缓存
    1. 工作原理
    2. 判断标准
    3. 相关参数
    4. 不会缓存的情况：
    5. 为什么mysql默认关闭了缓存开启？
    6. 查询缓存 适用场景
  3. 3，查询优化处理
    1. 查询优化处理的三个阶段：
  4. 4，查询执行引擎
  5. 5，返回客户端
  6. CSV
    1. 特点：
    2. 应用场景：
  7. Archive
    1. 特点：
    2. 应用场景：
  8. Memory/heap
    1. 特点：
    2. 应用场景：
  9. MyISAM
    1. 特点：
  10. InnoDB：
    1. 特点：

转载请标注原文地址：https://lilyssh.cn/mysql/3-mysql-explain-plan/

查询执行流程分析
执行计划详解
存储引擎介绍

#一、理解mysql体系结构
MySQL结构体系：

Client Connectors
接入方 支持协议很多
Management Serveices & Utilities
系统管理和控制工具， mysqldump、 mysql复制集群、 分区管理等
Connection Pool
连接池： 管理缓冲用户连接、 用户名、 密码、 权限校验、 线程处理等需要缓存的需求
SQL Interface
SQL接口： 接受用户的SQL命令， 并且返回用户需要查询的结果
Parser
解析器， SQL命令传递到解析器的时候会被解析器验证和解析。 解析器是由Lex和YACC实现的
Optimizer
查询优化器， SQL语句在查询之前会使用查询优化器对查询进行优化
Cache和Buffer（高速缓存区）
查询缓存， 如果查询缓存有命中的查询结果， 查询语句就可以直接去查询缓存中取数据
pluggable storage Engines
插件式存储引擎。 存储引擎是MySql中具体的与文件打交道的子系统
file system
文件系统， 数据、 日志（redo， undo） 、 索引、 错误日志、 查询记录、 慢查询等

#二、查询执行路径

1，mysql 客户端/服务端通信

mysql客户端与服务端的通信方式是“半双工” ；

全双工： 双向通信， 发送同时也可以接收。（打电话）
半双工： 双向通信， 同时只能接收或者是发送， 无法同时做操作。（对讲机）
单工： 只能单一方向传送。（听广播）

半双工通信 特点和限制：
客户端一旦开始发送消息， 另一端要接收完整个消息才能响应。
客户端一旦开始接收数据没法停下来发送指令。

对于一个mysql连接， 或者说一个线程， 时刻都有一个状态来标识这个连接正在做什么
查看命令 show full processlist / show processlist
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html (状态全集)
Sleep
线程正在等待客户端发送数据
Query
连接线程正在执行查询
Locked
线程正在等待表锁的释放
Sorting result
线程正在对结果进行排序
Sending data
向请求端返回数据
可通过kill {id}的方式进行连接的杀掉

2，查询缓存

工作原理

缓存SELECT操作的结果集和SQL语句；
新的SELECT语句， 先去查询缓存， 判断是否存在可用的记录集；

判断标准

与缓存的SQL语句， 是否完全一样， 区分大小写。
(简单认为存储了一个key-value结构， key为sql， value为sql查询结果集)

相关参数

1
show variables like 'query_cache%';


query_cache_type
值：0 -– 不启用查询缓存， 默认值；
值：1 -– 启用查询缓存， 只要符合查询缓存的要求， 客户端的查询语句和记录集
都可以缓存起来， 供其他客户端使用， 加上 SQL_NO_CACHE将不缓存
值：2 -– 启用查询缓存， 只要查询语句中添加了参数： SQL_CACHE， 且符合查询
缓存的要求， 客户端的查询语句和记录集， 则可以缓存起来， 供其他客户端使用
query_cache_size
允许设置query_cache_size的值最小为40K， 默认1M， 推荐设置 为： 64M/128M；
query_cache_limit
限制查询缓存区最大能缓存的查询记录集， 默认设置为1M

1
show status like 'Qcache%';

查看缓存情况

select sql_no_cache * from user where id = 1; 就不会使用缓存。

不会缓存的情况：

1.当查询语句中有一些不确定的数据时， 则不会被缓存。 如包含函数NOW()，
CURRENT_DATE()等类似的函数， 或者用户自定义的函数， 存储函数， 用户变
量等都不会被缓存。
2.当查询的结果大于query_cache_limit设置的值时， 结果不会被缓存。
3.对于InnoDB引擎来说， 当一个语句在事务中修改了某个表， 那么在这个事务
提交之前， 所有与这个表相关的查询都无法被缓存。 因此长时间执行事务，
会大大降低缓存命中率。
4， 查询的表是系统表。
5， 查询语句不涉及到表。

为什么mysql默认关闭了缓存开启？

1.在查询之前必须先检查是否命中缓存,浪费计算资源。
2.如果这个查询可以被缓存， 那么执行完成后， MySQL发现查询缓存中没有这个查询， 则会将结果存入查询缓存， 这会带来额外的系统消耗。
3.针对表进行写入或更新数据时， 将对应表的所有缓存都设置失效。
4.如果查询缓存很大或者碎片很多时， 这个操作可能带来很大的系统消耗。

查询缓存 适用场景

以读为主的业务， 数据生成之后就不常改变的业务，比如门户类、 新闻类、 报表类、 论坛类等。

3，查询优化处理

查询优化处理的三个阶段：

• 解析sql
通过lex词法分析,yacc语法分析将sql语句解析成解析树
https://www.ibm.com/developerworks/cn/linux/sdk/lex/
• 预处理阶段
根据mysql的语法的规则进一步检查解析树的合法性， 如： 检查数据的表
和列是否存在， 解析名字和别名的设置。 还会进行权限的验证
• 查询优化器
优化器的主要作用就是找到最优的执行计划

4，查询执行引擎

5，返回客户端

#三、各大存储引擎介绍

CSV

数据存储以CSV文件

特点：

不能定义没有索引、 列定义必须为NOT NULL、 不能设置自增列
–>不适用大表或者数据的在线处理
CSV数据的存储用,隔开， 可直接编辑CSV文件进行数据的编排
–>数据安全性低
注： 编辑之后， 要生效使用flush table XXX 命令

应用场景：

数据的快速导出导入。
表格直接转换成CSV。

Archive

压缩协议进行数据的存储。数据存储为ARZ文件格式。

特点：

只支持insert和select两种操作。
只允许自增ID列建立索引。
行级锁。
不支持事务。
数据占用磁盘少。

应用场景：

日志系统。
大量的设备数据采集。

Memory/heap

数据都是存储在内存中， IO效率要比其他引擎高很多。服务重启数据丢失。内存数据表默认只有16M。

特点：

支持hash索引，B tree索引，默认hash（查找复杂度0(1)）。
字段长度都是固定长度varchar(32)=char(32)。
不支持大数据存储类型字段如 blog，text。
表级锁

应用场景：

等值查找热度较高数据。
查询结果内存中的计算， 大多数都是采用这种存储引擎。
作为临时表存储需计算的数据。

MyISAM

Mysql5.5版本之前的默认存储引擎
较多的系统表也还是使用这个存储引擎
系统临时表也会用到Myisam存储引擎

特点：

a，select count(*) from table 无需进行数据的扫描
b，数据（MYD） 和索引（MYI） 分开存储
c，表级锁
d，不支持事务

InnoDB：

Mysql5.5及以后版本的默认存储引擎

特点：

支持事务
支持行级锁
聚集索引（主键索引） 方式进行数据存储
支持外键关系保证数据完整性

本文由 lilyssh创作。可自由转载、引用，但需署名作者且注明文章出处。

当前网速较慢或者你使用的浏览器不支持博客特定功能，请尝试刷新或换用Chrome、Firefox等现代浏览器