查询性能优化

建立索引对高性能来说,必不可少,但这些还不够,还需要合理的设计查询,如果查询写的很糟糕,即使数据库表结构再合理,索引再合适,也无法实现高性能

为什么查询会慢

真正重要的是相应时间，如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间，如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数。要么让子任务运行的更快。

通常来说,查询的生命周期可以按照顺序来看,从客户端,到服务器,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端,其中,执行可以认为是整个生命周期中最重要的阶段,这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序,分组

慢查询基础:优化数据访问

查询性能低下最基本的原因是访问的数据太多,某些查询可能不可避免的需要筛选大量数据,但这并不常见,大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化

是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃,这会给MySQL服务器带来额外的负担,并增加网络开销,同时也会消耗应用服务器的cpu和内存资源

MYSQL是否在扫描额外的记录

在确定查询只返回需要的数据后,接下来应该看看查询为了返回结果是否扫描了过多的数据,对于MySQL,三个简单的衡量开销的指标1相应时间,2扫描的行数,3返回的行数

重构查询的方式

一个复杂查询还是多个简单查询

是否需要将一个复杂的查询分成多个简单的查询,

MySQL从设计上让连接和断开连接都很轻量级,在返回一个小的查询结果方面很高效,有时候,将一个大查询分解成多个小查询是很有必要的.

切分查询

有时候对于一个大查询我们需要分而治之,将大查询切分成小查询,每个查询功能完全一样,只完成一部分,每次只返回一小部分查询结果

删除旧数据就是一个很好的例子,定期的清除大量数据时,如果用一个大的语句一次性完成的话,可能需要一次锁住很多数据,占满整个事务日志,耗尽系统资源,阻塞很多小的但是重要的操作,将一个大的delete语句切分成很多小的,可以尽可能小的印象MySQL性能,还可以减少MySQL复制的延迟.

分解关联查询

很多高性能的应用都会对关联查询进行分解,简单的,可以对每一个表进行一次单表查询,然后将结果在应用程序中进行关联. 这样做的好处有 让缓存的效率更高,执行单个查询减少对锁的竞争,在应用层关联,可以更容易对数据库进行拆分,更容易做到高性能和可拓展,查询本身的效率也会有所提升,较少冗余记录的查询

查询执行的基础

当向MySQL发送一个请求的时候,MySQL到底做了什么.

1 客户端发送一条查询给服务器

2 服务器先检查缓存,如果命中了缓存,则立刻返回存储在缓存中的结果,否则进入下一阶段

3 服务器端进行sql解析,预处理,再由优化器生成对应的执行计划

4 MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询

5 将结果返回客户端

MySQL客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是半双工的,这意味着,在任何一个时刻,要么由客户端向服务端发送数据,要么反过来,这两个动作不能同时发生.

这种协议让MySQL简单快速,但是也在很多地方限制了MySQL,比如无法进行流量控制.

查询状态

对于一个MySQL连接,任何时刻都有一个状态,该状态表示了MySQL正在做什么,可以使用show full processlist ,常见的状态有

  • sleep 线程正在等待客户端发送新的请求
  • query 线程正在执行查询或者正在将结果发送给客户端
  • locked 在MySQL服务器层,表示正在等待表锁
  • analyzing and statistics 线程正在收集存储引擎的统计信息,并生成查询的执行计划
  • copying to tmp table 正在执行查询,并将结果复制到一个临时表中
  • sorting result 对结果集进行排序
  • sending data 线程多个状态之间传送数据
查询缓存

在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据,这个检查通过一个对大小写敏感的哈希查找实现的.一个字节不同都不会匹配结果

命中缓存后还要检查用户权限,如果权限没问题,直接返回缓存中的数据

查询优化处理

查询的声明周期的下一步是将一个sql转化成一个执行计划,MySQL再按照这个执行计划和存储引擎进行交互,包括多个子阶段,解析sql,预处理,优化SQL执行计划,这个过程中有任何错误都会终止查询.

  • 语法解析器和预处理

首先通过关键字将SQL进行解析,生成解析树, 预处理器根据一些SQL规则进一步检查结息树是否合法,下一步会进行验证权限,权限验证通常很快

  • 查询优化器

现在的语法树已经合法,将由优化器转化成执行计划,一条查询可以有很多种执行方式,最后都返回相同的结果,优化器的作用就是找出这里面最好的执行计划

MySQL使用基于成本的优化器,将尝试预测一个查询使用某种执行计划的成本,并选择其中成本最小的一个

查询执行引擎

在解析和优化阶段,MySQL将生成对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询.

查询执行阶段,只是简单的根据执行计划给出的指令逐步执行,在根据计划逐步执行的过程中,大量操作需要调用存储引擎实现的接口完成,

存储引擎接口有很丰富的功能,但是底层接口只有几十个,这些接口像搭积木一样能够完成查询的大部分操作.

返回结果给客户端

查询执行的最后一步是将结果返回给客户端.即使查询不需要返回结果给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响的行数.