mysql一条sql语句的执行过程

sql的具体执行过程

• 客户端发送一条查询给服务器
• 服务器下先检查查询缓存，如果命中了缓存，返回缓存中的结果
• 否则就需要服务器端进行sql的解析、预处理，再由优化器生成对应的执行计划
• 根据执行计划，调用存储引擎的api来执行查询
• 将结果返回给客户端

客户端发送sql给服务器：

• mysql的通信协议简单快速，但是也有很多限制了，比如无法进行流量控制，一旦开始发送数据，另一端需要完整的接收数据才能响应它，所以在必要的时候，查询一定要加 limit 进行限制
• 如果有很长的查询语句，可能需要设置mysql服务器和客户端在一次传送数据包的过程中最大允许的数据包大小（max_allowed_packet）

查询缓存:

• 应用程序不需要关心是通过查询缓存返回的结果还是实际执行查询返回的结果，因为两者的结果是完全相同的，而且查询缓存也不需要使用任何语法

• 在解析一个查询语句之前，如果查询缓存是打开的，那么会优先检查这个查询是否命中查询缓存中的数据，如果命中，就立刻返回结果，跳过解析、优化和执行阶段

• 在判断缓存是否命中的时候，mysql不会解析sql语句，而是直接使用客户端发送过来的原始信息进行对比，任何字符上的不同，都会导致缓存不命中

• 缓存未命中的可能情况有：

  • 首先查询语句本身可能无法被缓存。例如查询中包含任意用户自定义函数、用户变量等，都不会被缓存
  • 或者数据库第一次处理这个查询，也有可能缓存没有预热，mysql还没有机会把查询结果都缓存起来
  • 还有可能是缓存失效了：表被更新了，或者虽然缓存了查询结果，但是内存不足，会将某些缓存剔除

• 查询缓存适用的情况

  • 只有当缓存带来的资源节约大于本身资源消耗才会带来性能提升
  • 查询缓存可以降低查询的执行时间，但是不能降低查询结果传输的网络消耗，如果系统的瓶颈是网络传输，那么查询缓存的意义不大
  • 对于复杂的查询语句，每次执行的消耗非常大，返回的结果集却很小，而且表更新不频繁，这种情况比较适合查询缓存

查询缓存的缺点:

• 查询缓存有可能成为服务器的资源竞争点，所以默认应该关闭查询缓存
• 查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，和这个表相关的所有缓存数据都会失效，如果查询缓存使用了大量的内存，缓存失效可能是一个非常严重的问题，会导致整个系统的卡顿
• 另外打开查询缓存会带来额外的消耗，例如：
  • 查询开始之前必须先检查是否命中缓存
  • 如果这个查询可以被缓存，会将结果存入查询缓存，这会有额外的系统消耗
  • 对于写操作来说，向某个表写入数据的时候，还需要额外把这个表所有的缓存都要设置为失效
  • 如果缓存的结果再失效前没有被任何其他查询使用，这次缓存就是浪费时间和内存

查询缓存的优化：

• 用多个小表代替一个大表
• 写入时采用批量写入，这样查询缓存就只需要一次失效
• 合理设置缓存空间大小，缓存空间太大，过期操作可能会导致服务器卡死
• 写密集型应用，直接禁用缓存查询

innodb的查询缓存

• innodb因为有mvcc机制，和查询缓存的交互会更加麻烦
• innodb会控制再一个事务中，是否可以使用查询缓存，可以同时控制对查询缓存的读和写（向查询缓存写入数据）
• 事务是否可以访问查询缓存，取决于当前事务id，以及对应表上是否有锁，每一个innodb表的内存数据字典都会保存一个事务id号，如果当前事务id小于该事务id，就无法访问查询缓存
• 如果表上有锁，这个表的任何查询语句都是无法被缓存的

mysql8移除查询缓存：

• 对于大多数场景下，查询缓存是比较鸡肋的，因此，从MySQL 5.6版本开始，将查询缓存设置为了默认关闭，并在MySQL 8.0版本中，彻底移除了查询缓存
• 官方的解释是：
  • 假设可扩展性可以得到改善，那么查询缓存的限制因素是，由于只有击中缓存的查询才能看到改善；它不太可能改善性能的可预测性。
  • 对于面向用户的系统来说，减少性能的可变性往往比提高峰值吞吐量更重要。

sql的解析、预处理，优化器:

• 语法解析器和预处理，也就是mysql通过关键字把sql语句进行解析，生成一颗解析树，这里会检查语法规则是否正确，表和列是否存在等
• 查询优化器会将语法树转换为执行计划

  • 一条sql有很多种执行方式，虽然最后都会返回相同结果，但是执行过程并不一样，优化器的作用就是找到其中最好的执行计划

  • mysql使用的是基于成本的优化器，它会预测执行一个某种执行计划的成本，然后选择最小的一个

  • 但是又很多种原因会导致mysql优化器选择错误的执行计划，比如：

    • mysql的最优只是基于成本模型的最优执行计划，并不一定是最快的，而且预估成本不等于实际成本

    • mysql也不考虑其他并发执行的查询，这有可能会影响到当前的查询速度

    • 另外mysql不会考虑用户自定义的函数成本，如果包含全文搜索match()的字句，就会使用全文索引

  • mysql能处理的优化类型有：

    • 重新定义关联表的顺序
    • 优化count(),max(),min()等
    • 如果一个表达式能转换为常数，就会一直把表达式作为常数处理
    • 当索引中的列包含查询需要使用的所有列，可以使用索引返回需要的数据，也就是覆盖索引
    • 如果发现已经满足查询要求的时候，能够立刻终止查询
    • 如果两个列通过等式关联，mysql能够把其中一个列的条件传递到另一个列上
    • mysql将 in() 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，所以mysql中的 in() 不完全等于多尔 or 条件字句，in（) 的速度会更快

  • 对于查询优化器，最好不要做多余的工作，不仅可能带不来收益，还会增加维护的难度

查询状态：

• 对于一个mysql的连接，任何时刻都有一个状态。可以使用show full processlist 来查看这个状态
• sleep ：正在等待客户端发送新的请求
• query : 正在执行查询或者正在将结果发送给客户端
• locked ： 在mysql服务层正在等待表锁，在innodb不会出现这个状态
• Copying to tmp table : 正在执行查询，并将结果集复制到临时表中
• sorting result : 正在对
• 结果集进行排序