Mysql实战45讲学习详情----一条SQL查询语句是如何执行的？

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

 

 

 

 

select * from t where id=10；

这条查询语句的执行过程：

外部层：

　　用户与server层交互的媒介

　　　　一.客户端【用于连接数据库，输入命令/语句】

　　　　　　界面化连接数据库

　　　　　　输入 select * from t where id=10；

server层：

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

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

　　　　　　输入连接命令认证身份--mysql -h$ip -p$port -u$user -p
　　　　　　　　认证通过：连接器会到权限表里查询帐号权限　　

　　　　　　　　　　　p：之后所有的权限判断逻辑都会依赖此时读到的权限

　　　　　　　　　　　　　　这意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

　　　　　　　　认证失败：access denied for user

　　　　　　连接完成后可以使用命令：show processlist查看状态

　　　　　　command 列显示为“sleep”的这一行，就表示现在连接处于空闲状态

　　　　　　客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

　　　　　　连接方式分为两种 ：【长连接和短连接】

　　　　　　　　定义：

　　　　　　　　　　长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

　　　　　　　　　　短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

　　　　　　　　利弊：

　　　　　　　　　　建立连接过程复杂，尽量使用长连接，减少建立连接操作

　　　　　　　　　　全部使用长连接内存增长过快（ps：mysql在执行时临时使用的内存管理器在连接对象里，这些资源只有在连接断开的时候释放，长连接累积下来内存占用过大，会被系统杀掉，现象就是异常重启）

　　　　　　　　弊端的优化方法：

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

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

　　　　三.查询缓存

　　　　　　连接建立完成后，进入执行阶段

　　　　　　　　当mysql拿到一个查询请求后，会先到查询缓存内查看key值，如果命中直接回返回结果。

　　　　　　　　　　ps：之前执行过的语句及结果会以键值对的形式缓存到内存中，｛key（查询语句）：value（查询结果）｝

　　　　　　　　弊端：

　　　　　　　　　　查询缓存的失效--只要对表更新，此表上的所有查询缓存就会失效。。so一半只有在很长时间不会更新的表才会使用查询缓存，例如：系统配置表

　　　　　　　　　　如果不需要使用查询缓存功能，将参数 query_cache_type 设置成 demand，而对于需要使用的语句可以使用“sql_cache”单独指定出来：select sql_cache * from t where id=10；

　　　　四.分析器

　　　　　　现在是查询语句并没有在查询缓存中找到相应的key值，因此它需要对语句进行解析。

　　　　　　　　①，词法分析

　　　　　　　　　　输入的语句是由多个字符串和空格组成的，mysql 需要识别出里面的字符串分别是什么，代表什么

　　　　　　　　　　例如：mysql 从你输入的"select"这个关键字识别出来，这是一个查询语句。

　　　　　　　　②，语法分析

　　　　　　　　　　错误提示you have an error in your sql syntax，要关注的是紧接“use near”

　　　　五.优化器

　　　　　　优化器的作用是确定执行方案

　　　　　　在经过分析器后，mysql已经知道你要干什么，现在需要优化器来确定使用哪一个执行方案，例如：

　　　　　　　　select * from t1 join t2 using(id) where t1.c=10 and t2.d=20;

　　　　　　　　这条语句是t1和t2两个表的join，它会有多种执行方式，虽然结果相同但是效率会有很大的不同

　　　　六.执行器

　　　　　　mysql通过分析器知道了要干什么，通过优化器知道了要怎么做，现在到了执行器，进入执行语句阶段

　　　　　　①，判断权限

　　　　　　　　判断你是否有查询权限（此时的判断依据为连接器时读取到的权限），如有权限就可以打开表继续执行。

　　　　　　②，判断引擎

　　　　　　　　打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

　　　　　　③，执行语句

　　　　　　　　　　select * from t where id=10;这条查询语句的执行过程为--【表 t 中，id 字段没有索引】

　　　　　　　　　　　　调用 innodb 引擎接口取这个表的第一行，判断 id 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

　　　　　　　　　　　　调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

　　　　　　　　　　　　执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。　　

存储层：

　　存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 innodb、myisam、memory 等多个存储引擎。现在最常用的存储引擎是 innodb，它从 mysql 5.5.5 版本开始成为了默认存储引擎。

　　create table建表时不指定引擎使用的默认引擎，可以在语句中添加engine=memory指定引擎

 

附加：

　　　如果表 t 中没有字段 k，而你执行了这个语句 select * from t where k=1, 那肯定是会报“不存在这个列”的错误： “unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

【个人理解】

　　　分析器会对语法和词法进行分析判断，在此阶段会判断语句的正确性