一、执行一个查询过程概述
- 客户端发送一条查询给服务器;
- 服务器先检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;
- 服务器端进行sql解析、预处理,再由优化器生成对应的执行计划;
- mysql根据优化器生成的执行计划,调用存储引擎的api来执行查询;
- 将结果返回给客户端;
![52b7ac3a93f4a31e9b3c92bf57e20e0c.png](https://i-blog.csdnimg.cn/blog_migrate/d2a0d92ab3b53490306517b5656302e7.jpeg)
二、查询缓存
- 查询缓存保存查询返回的完整结构;
- 命中查询缓存时,mysql会立即返回结果,跳过解析、优化和执行阶段;
- 查询缓存系统会跟踪查询中设计的每个表,如果这些表发生变化,和这个表相关的所有缓存数据都将失效;
- 判断缓存是否命中时,不会进行解析查询语句,直接使用sql语句和客户端发送过来的其他原始信息,任何字符上的不同,例如空格、注解等,都会导致缓存不命中;
- 当查询语句有一些不确定的数据时,则不会被缓存;
- 查询缓存配置参数:
- query_cache_type:是否打开缓存。OFF、ON和DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才会放入查询缓存。eg:select sql_cache * from table_name;
- query_cache_size:缓存使用的总内存空间大小,单位是字节;
- query_cache_min_res_unit:分配内存块时的最小单位,较小的该值可以减少碎片导致的内存空间浪费,但会导致更频繁的内存块操作;
- query_cache_limit:mysql能够缓存的最大查询结果,如果查询结果大于这个值,则不会被缓存;由于查询缓存在数据生成的时候就开始尝试缓存数据,所以当结果全部返回后,mysql才知道查询结果是否超出限制。超出之后,才会将结果从查询缓存中删除;
- query_cache_wlock_invalidate:如果某个数据表被其他连接锁住,是否仍然从查询缓存中返回结果,默认OFF,表示仍然可以返回;
三、语法解析器和预处理器
- mysql解析器通过关键字将sql语句进行解析,并生成对应的解析树;
- mysql解析器将使用mysql语法规则验证和解析查询,eg:验证是否使用错误的关键字、使用关键字的顺序是否正确、验证引号是否前后匹配等;
- 预处理器根据一些mysql规则进行进一步检查解析树是否合法,eg:检查数据表和数据列是否存在,解析名字和别名是否有歧义;
- 下一步预处理器验证用户权限,查看用户是否有操作权限,通常很快;
四、查询优化器
- 优化器的作用就是找到最好的执行计划;
- 语法树被认为是合法后,优化器将sql语句转换为执行计划,一条查询可以有多种执行方式,最后都返回相同的结果;
- 生成执行计划过程
- 耗时较多,特别是存在许多可选的执行计划时;
- 在一条sql语句执行过程中将该语句对应的最终执行计划进行缓存,下一次就可以直接使用已缓存的执行计划,从而提高sql语句的执行速度;
- mysql使用基于成本的优化器(CBO cost-based optimizer),会预测一个查询使用某种执行计划的成本,选择其中成本最小的一个;
- 优化器会根据优化规则对关系表达式进行转换,经过优化规则后会生成另一个关系表达式,原有表达式也会保留;
- 经过一系列转换后会生成多个执行计划,然后CBO会根据统计信息和代价模型(cost model)计算每个执行计划的cost,从中挑选cost最小的执行计划;
- 导致mysql优化器选择非最优执行计划的原因
- mysql是根据成本计算得出的最优计划,可能执行时间并不是最短的;
- 有时候可能无法估算所有可能的执行计划,导致可能错过最优的执行计划;
- 执行计划成本估算不等同于实际执行的成本,mysql层面无法知道哪些页面在内存中,哪些在磁盘上,实际执行过程中需要多少次物理IO无法得知;
- mysql不会考虑不受其控制的操作成本,eg:执行用户自定义函数的成本;
- 统计信息不准确,mysql依赖存储引擎提供的统计信息(每个表有多少页面、数据行和索引的长度、索引的分布等)来估计成本,InnoDB因为MVCC的架构,并不能维护一个数据表行数的精确统计信息;
- mysql可以处理的优化类型
- 重新定义关联表的顺序;
- 使用等价转换原则,eg:移除一些恒成立或恒不成立的判断;
- 可能的表达式转换为常数表达式;
- 提前终止查询,使用limit;
五、查询执行引擎
- 查询执行引擎根据执行计划来完成整个查询;
- 执行计划是一个数据结构(指令树),不是和其他关系型数据库那样生成对应的字节码;
- mysql根据执行计划给出的指令逐步执行,在执行过程中,有大量的操作需要调用存储引擎实现的接口来完成,这些接口即为“handler API”;
- 查询中每一个表由一个handler的实例表示。在优化阶段mysql就为每一个表创建了一个handler实例,优化器可以根据这些实例的接口获取表的相关信息,eg:列名、索引、统计信息等;
六、返回客户端结果
- 如果查询可以被缓存,mysql会在这个阶段将结果存放到查询缓存中;
- mysql将结果集返回给客户端是一个增量逐步返回的过程,在查询生成第一条结果时,mysql就可以开始向客户端逐步返回结果了;
- 增量逐步返回的好处:服务端无须存储太多的结果,不会因为返回太多的结果而消耗太多内存;同时让客户端第一时间获得返回结果;
- 结果集中的每一行都会以一个满足mysql客户端/服务端通信协议的包发送,再通过tcp协议进行传输,传输过程中,可能对mysql的包进行缓存然后批量传输;