文章目录
  1. 1. 慢查询基础:优化数据访问
    1. 1.1. 是否向数据库请求了不需要的数据
    2. 1.2. Mysql是否在扫描额外的记录
  2. 2. 重构查询的方式
    1. 2.1. 一个复杂查询还是多个简单查询
    2. 2.2. 切分查询
    3. 2.3. 分解关联查询
  3. 3. 查询执行的基础
    1. 3.1. Mysql客户端/服务器端通信协议
      1. 3.1.1. 查询状态
    2. 3.2. 查询缓存
    3. 3.3. 查询优化处理
      1. 3.3.1. 语法解析器和预处理
      2. 3.3.2. 查询优化器
      3. 3.3.3. 数据和索引的统计信息
      4. 3.3.4. Mysql如何执行关联查询
      5. 3.3.5. 执行计划
      6. 3.3.6. 关联查询优化器
      7. 3.3.7. 排序优化
    4. 3.4. 查询执行引擎
    5. 3.5. 返回结果给客户端
  4. 4. 优化建议

高性能数据的包含很多部分,合理的查询就是其中之一,如果查询写的很糟糕,即使库表结构再合理,索引再合适,也无法实现高性能。

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

查询性能低下,最基本原因是访问数据太多。可以通过下面两个步奏分析请求

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

  1. 查询不需要的记录

    mysql是先返回结果再计算

  2. 多表关联时返回所有的列
  3. 总是取出全部列
  4. 重复查询相同数据

Mysql是否在扫描额外的记录

三个衡量指标:

  1. 响应时间
  2. 扫描的行数和返回的行数
  3. 扫描的行数和访问类型

    从全表扫描到索引扫描、范围扫描、唯一索引扫描、常数查询等,这里列出的速度从慢到快,扫描行数从小到大。

重构查询的方式

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

mysql在设计上让连接和断开连接都是很轻量级的,在返回一个小的查询结果方面很高效。不过,在应用方面,如果一个查询能够胜任时还写成多个独立查询是不明智的。

切分查询

“分而治之”,将大查询切分成小查询。

如删除旧的数据,一次性完成的话,需要一次锁住很多数据,占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。可以切分成许多小的查询,每次删除之后暂停一会再进行下一次。

分解关联查询

  1. 让缓存效率更高
  2. 减少锁的竞争
  3. 在应用层做关联,可以很容易对数据库进行拆分,更易扩展和高性能
  4. 查询本省效率可能提升
  5. 减少冗余记录的查询
  6. 相当在应用中做了hash关联。

查询执行的基础

查询流程:

  1. 客户端发送一个SQL语句到服务器。
  2. 服务器要检查缓存。如果找到缓存,就会从缓存中返回结果。如果没找到,就到下一步。
  3. 服务器解析,预处理并且优化SQL到一个语句执行计划中。
  4. 语句执行引擎通过调用存储引擎API执行这个语句计划。
  5. 服务器发送结果到客户端。

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

协议是半双工的。意思就是在任意的时间内,MySQL服务端既可以发送或者接受消息,但是不能同时。也意味着没法减少消息。

这种协议是MySQL通信简单而快速。但是也有一些限制。比如,没有流程控制。一旦一端发送一个消息,另一端在响应之前就必须获取整个信息。

客户端发送一个语句到服务端作为一个单独的数据包。如果你有一个大的语句,max_packet_size就对你很重要。一旦一个客户端发送一个语句,如果没有响应,它只能等待结果。

相比较而言,从服务端的响应常常有许多数据包组成。当服务端相应的时候,客户端必须要接受整个结果集。它不能仅仅获取一些行并且之后再要求服务器没必要发剩下的数据。如果客户端仅仅需要不分行返回,它既然要等待服务器所有的数据包并且还要抛弃它们不需要的,或者以不好的方式断开连接。两者都是不好的方法,这就是为什么LIMIT条件如此的重要。

查询状态

每个MySQL连接,或者线程,都有状态来表示某一时刻到底在做什么。有很多方法来查看这些状态,最简单的方法就是使用SHOW FULL PROCESSLIST命令。这个状态会出现在Command列。作为一个查询语句贯穿整个周期,它的状态也变化多次,并且有12个状态。比如:Sleep、Query、Locked、Analyzing and statistics、Copying to tmp table [on disk]等。

查询缓存

在解析一个查询之前,如果缓存开启,MySQL要检查它的缓存。这个操作是一个区分大小写的hash查找。

如果MySQL在查询缓存中找到一个匹配,在返回已经缓存的查询语句之前,必须要校验权限。

查询优化处理

在语句生命周期的下一步就是把一个SQL查询放入一个可执行的计划中。这个步骤有许多子步骤:解析,预处理并且优化。

语法解析器和预处理

生成解析树

检查解析树是否合法。

查询优化器

MySQL使用了基于成本的优化器。意思就是会尝试多种不同的方法去执行语句,选择一个效率更高的。

多个因素会导致优化器选择错误的执行计划:

  1. 统计信息不准确
  2. 估算成本不等同于实际成本
  3. Mysql的最优可能和你想的最优不一样
  4. MySQL并不会考虑其他正在并发执行的语句。这也影响一个语句的运行。
  5. MySQL也不会总做基于成本的优化。
  6. 优化器不能计算账户内部操作的一些消耗。比如存储函数或者用户自定义的函数就没办法计算了。
  7. 优化器不能评估每个可能执行的计划。因此会错失一个最佳的执行计划。

两种基本优化类型:

  1. 静态优化

    表现为的形式是,查看解析树。举一个例子,通过一个代数的规则,优化器可以把WHERE子句转为一个相等形式。静态优化是独立的值,比如WHERE子句不变的值。它们被执行一次,然后总是有效的。你可以把静态优化想象成”编译时的优化“。

  2. 动态优化

    基于上下文并且依赖于很多因素。比如在WHERE子句的值或者在一个索引中有多少行。每一次语句的执行,它们都必须重写进行评估。你可以把动态优化想象成“运行期的优化”。

Mysql能够处理的优化:

  1. 重新定义关联表的顺序
  2. 外连接变为内连接
  3. 使用等价变换原则
  4. 优化COUNT()、MIN()、MAX()
  5. 预估并转化为常数表达式
  6. 覆盖索引扫描
  7. 子查询优化
  8. 提前终止查询
  9. 等值传播
  10. 列表IN()的比较

    在许多数据库中,IN()和许多OR组成的语句是一样的。因为这两个语句的逻辑上相同。在MySQL中,有些不同,对IN()里的值进行排序,并且用快速的二进制搜索来查找值是否存在。in的时间复杂度为O(log n) ,然而OR的为O(n) (也就是说,如果list很大会慢一些)。

数据和索引的统计信息

查询优化器生成查询的执行计划时,需要向存储引擎获取相关的统计信息。

Mysql如何执行关联查询

UNION查询,香江一系列的单个查询结果放到一个临时表中,然后再重新读出临时表数据来完成UNION查询

MySQL关联执行的策略很简单:对任何关联都执行嵌套循环关联操作。

执行计划

左侧深度优先的树

关联查询优化器

通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

排序优化

应尽可能避免排序或者尽可能避免大量数据排序。

小数量使用内存排序,采用快排算法。

大数据量使用文件排序。

查询执行引擎

mysql在优化阶段为每个表创建一个handler实例,优化器根据这些实例的接口可以获取表的相关信息,包括表的所有列名、索引统计信息等。

返回结果给客户端

增量、逐步返回的过程。

优化建议

  1. 使用左外连接代替子查询
  2. UNION ALL代替UNION,UNION无重复行
  3. 避免使用NULL
  4. 尽可能使用更小的字段
  5. Gruop By:分组是采用查找表的标识列分组的效率会比其他列高。
  6. 优化LIMIT:尽可能使用索引覆盖扫描;
文章目录
  1. 1. 慢查询基础:优化数据访问
    1. 1.1. 是否向数据库请求了不需要的数据
    2. 1.2. Mysql是否在扫描额外的记录
  2. 2. 重构查询的方式
    1. 2.1. 一个复杂查询还是多个简单查询
    2. 2.2. 切分查询
    3. 2.3. 分解关联查询
  3. 3. 查询执行的基础
    1. 3.1. Mysql客户端/服务器端通信协议
      1. 3.1.1. 查询状态
    2. 3.2. 查询缓存
    3. 3.3. 查询优化处理
      1. 3.3.1. 语法解析器和预处理
      2. 3.3.2. 查询优化器
      3. 3.3.3. 数据和索引的统计信息
      4. 3.3.4. Mysql如何执行关联查询
      5. 3.3.5. 执行计划
      6. 3.3.6. 关联查询优化器
      7. 3.3.7. 排序优化
    4. 3.4. 查询执行引擎
    5. 3.5. 返回结果给客户端
  4. 4. 优化建议