查询性能优化

作者
  • 查询效率不高时首先要考虑的因素
  • 深入优化查询的技巧
  • MySQL 优化器内部的机制

一、为什么查询会慢

每个查询都是一个任务，包含很多个子任务。

从一个 sql 执行过程我们可以看到，从客户端到服务端，经过了连接器，分析器，优化器，执行器，最后到存储引擎，包括网络，CPU，IO 等因素，每一步都会花费时间，

因此我们能做的就是，了解查询的步骤和生命周期，搞清楚时间的消耗状况，才能更好的优化查询。

二、慢查询基础：优化数据访问

大部分性能低效的查询都可以通过减少访问数据量的方式来进优化：

  • 看看程序是否检索大量不需要的数据
  • 看看 Mysql 服务器是否分析了大量不需要的数据

1、是否向数据库请求不需要的数据，而不自知

1）查询不需要的记录

查询出 100 条记录，但是只需要显示前 10 条，这样就可以用 LIMIT 来解决

2）多表关联时返回全部列

3）总是取出全部列

这两个问题其实是类似的，建议不要使用通配符，这样会使索引失效，应该使用需要获取的列名

4）重复查询相同数据

可以将这部分数据放入缓存

2、Mysql 扫描额外记录

1）响应时间：服务时间+排队时间。只能用于大概判断是不是合理值

2）扫描行数和返回行数

3）扫描行数和访问类型

解决办法：

  • 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎就无需回表获取对应行就可以返回结果了
  • 改变库表结构，使用单独的汇总表
  • 重写这个复杂的查询

三、重构查询的方式

1、复杂查询或简单查询

2、切分查询：比如定期清理大量数据

3、分解关联查询：可以减少锁竞争，提高查询效率，在应用层做关联。

四、查询执行的基础

查询过程：

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存，如果缓存中有的话，立刻返回缓存中的结果，否则进入下一步。
  3. 服务器端进行 SQL 解析、预处理，然后由查询优化器生成对应的执行计划
  4. MySQL 根据执行计划，调用存储引擎的 API 来执行查询
  5. 将结果返回给客户端

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

2、查询缓存

  • 通过一个 Case sensitive 的 hash 查找，来检查是否命中缓存
  • 直接从缓存中拿到数据并返回给客户端
  • 查询不用解析，不用生成执行计划，不会被执行

3、查询优化处理

解析 SQL-->预处理—>优化 SQL 执行计划

1）语法解析预处理

  • Mysql 通过关键字将 SQL 语句进行解析，生成一棵对应的解析树
  • 解析器验证语句的语法，比如关键字顺序，符号使用
  • 预处理器进一步检查解析树是否合法，比如检查表和列名是否存在
  • 预处理器验证权限

2）查询优化器

可以优化的类型：

  • 重新定义关联表顺序

  • 将外连接转化成内连接

  • 使用等价变换规则简化 sql

  • 优化count(), MIN(), MAX()

  • 预估并转化为常熟表达式

  • 覆盖索引扫描

  • 子查询优化

  • 提前终止查询

  • 等值传播

  • 列表in()的比较

    MySQL 将in()列表中的数据先进行排序，然后通过二分法查找的方式来确定列表中的值是否满足条件。复杂度为 O(log n)。

    不要自以为比优化器更聪明

3）数据和索引的统计信息

  • 统计信息由存储引擎实现
  • 查询优化器在生成执行计划时，需要向存储引擎获取相应的统计信息
  • 比如：每个表或者索引有多少个页面，每个表的每个索引的基数是多少
  • 优化器根据这些信息来选择一个最优的执行计划

4）MySQL 如何执行关联查询

  • MySQL 认为任何一次查询都是一次关联
  • MySQL 对任何关联都执行嵌套循环关联操作
  • MySQL 在碰到子查询时，先执行子查询并将其结果放到一个临时表，临时表没有索引

5）执行计划

  • MySQL 生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果
  • 通过 EXPLAIN EXTENDED，SHOW WARNINGS可以看到重构出的查询

6）关联查询优化器

多表关联的时候，可以有多重不同的关联顺序来获得相同的执行结果。

关联查询优化器通过评估不同顺序的成本来选择一个代价最小的关联顺序。

倒转顺序可以扫描更少的行数，进行更少的嵌套循环操作和回溯操作。

当关联表很多时，优化器不可能逐一评估每一种顺序成本，这时候会采用贪婪模式。

7）排序优化

尽可能避免排序或者对大量数据进行排序。

  • 当索引排序不能用时，MySQL 进行文件排序，在内存或者磁盘中进行排序
  • 当数据量小于排序缓冲区时，MySQL 使用内存进行快速排序，否则分块进行快速排序

两次传输排序：

第一次先读取行指针和需要排序的字段

第二次再读取排序过的所有记录

  • 会产生大量随机 IO，传输成本很高
  • 在排序的时候存储的数据少，让排序缓冲区尽可能的容纳更多的行数进行排序

单次传输排序：

一次读取查询所有需要的所有列，再根据给定列进行排序

  • 只需要一次顺序 IO 读取所有数据，无须任何随机 IO
  • 如果要返回的数据多，将会占用大量空间

MySQL 对每一个排序记录都会分配一个足够大的定长空间来存放。

因此可能会导致排序的临时表很大。

关联表排序：

  • 如果 ORDER BY 字段都来自第一个表，那么 MySQL 在处理第一个表的时候就进行文件排序
  • 否则会将关联查询结果放到临时表，然后再进行文件排序
  • LIMIT 可以减少需要排序的数据

4、查询执行引擎

查询执行引擎根据解析优化生成的执行计划来完成整个查询。

MySQL 简单的根据执行计划给出的指令，逐步调用存储引擎实现的接口来完成。

5、返回结果给客户端

返回结果集是增量过程，开始生成第一条数据时，就可以逐步向客户端返回结果集了。

服务器无须存储太多结果，客户端也可以第一时间获得返回。

每一行都会以封包发送，再通过 TCP 传输。

五、MySQL 查询优化器的局限性

1、关联子查询

mysql 对子查询的实现很糟糕。

七、优化特定的查询类型

1、优化 count 查询

2、优化关联查询

3、优化子查询

使用关联查询代替

4、优化GROUP BY和 DISTINCT

  • 索引优化
    • 按照标识列进行分组
  • 使用临时表或文件排序优化

5、优化 LIMIT 分页

  • 偏移量大的时候代价很高

  • 通过延迟关联等手段，尽可能的使用索引覆盖扫描，而不是查询所有的列

  • 在预知边界的情况下，可以直接通过位置查询
  • 通过书签记录上次读取的位置

6、优化 SQL_CALC_FOUND_ROWS

7、优化 UNION 查询

  • 因为 UNION 通过临时表来执行的，所以需要将 WHERE、LIMIT、ORDERBY 等子句下推到 UNION 的子查询中，以便优化
  • 推荐使用 UNION ALL，可以避免 DISTINCT 唯一性检查

8、静态查询分析

9、使用用户自定义变量