mysql

高性能Mysql笔记-第六章(1)

第六章 查询性能优化

  • 前面章节的内容：
  1. 最优库表结构；
  2. 最优索引。
  • 本章内容：
    设计合理的查询。

6.1 查询任务的组成

  • 查询的生命周期
  1. 客户端到服务器；
  2. 服务器解析，生成执行计划；
  3. 执行；
  4. 返回结果给客户端。

其中第三步执行可以分解为：

  • 执行：
  1. 检索数据到存储引擎；
  2. 数据处理。（排序，分组）

查询时间消耗包括：

  • 网络；
  • CPU计算；
  • 生成统计信息和执行计划；
  • 锁等待（互斥等待）；
  • 其他。

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

6.2.1 减少数据量

慢查询的基本原因之一是数据量太大了，针对这个方面，方法有两个：

  1. 减少访问的行数；（使用limit）
  2. 减少访问的列数。（利用覆盖索引）

此外，对于重复查询，在应用层进行缓存也是一个比较常见的方法。

6.2.2 检查是否扫描了额外的记录

衡量查询开销的三个指标：

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

响应时间

响应时间可分为：

  1. 服务时间： 真正处理的时间；
  2. 排队时间： 等待资源时的时间。

扫描和返回的行数

理想情况：
扫描行数=返回行数。

较短的行代价较小。

扫描的行数和访问类型

访问类型：
由Explain语句的type字段可以得到。包括：

  1. ALL，全表扫描；（差）
  2. Index,索引扫描；
  3. Range,范围扫描；
  4. 唯一索引查询；
  5. 常数引用。（优）
    等等…

访问类型越优，扫描的成本越小。

预估的扫描行数
查询优化器预估的扫描行数，可以通过Explain语句的rows字段得到。

Where的三种应用方式
三种方式由好到坏依次为：

  1. 索引中使用。在存储引擎层完成；（Extra列为空）
  2. 使用索引覆盖扫描（Extra列出现Using index）。扫描后，在服务器层过滤，但不用回表。
  3. 不使用索引。从存储引擎返回行，在服务器层过滤。(Extra列为Using Where)。

相关的查询优化技巧：

  1. 覆盖索引；
  2. 改变库表结构，使用单独的汇总表；
  3. 重写复杂查询。（后续讨论）

6.3 重构查询的方式

6.3.1 复杂查询分解成多个简单查询

由于现代mysql的连接更轻量，网速也更快，因此分解复杂大查询成简单小查询也是很有必要的。
（也要适度权衡，不能过度分解）

6.3.2 切分查询

一个简单查询，但锁住大量行，可以分解成几个锁住少量行的查询，分时间段执行，减轻服务器瞬发压力。
比如删除100万行数据，可以分成删除100个1万行数据。

6.3.3 分解关联查询

分解关联查询后，在应用层做关联。

  • 原查询

    1
    2
    3
    4
    5
    select *
    from tag
    join tag_post on tagid=id
    join post on post.tagid=id
    where tag='mysql'

  • 分解后：

    1
    2
    3
    select * from tag where tag='mysql';
    select * from tag_post where tagid=1234;
    select * from post where id in (1,2,3,4)

上述分解的好处：

  1. 缓存的效率更高；
  2. 分解后，锁竞争更少；（锁粒度更细）
  3. 数据库拆分更容易；（不需要跨库查询）
  4. 关联可能会更高效；
  5. 减少记录的重复查询；
  6. 相当于应用中实现了哈希关联（效率高）。

6.4 查询执行的基础

查询流程：

  1. 客户端：发送查询到服务器；
  2. 服务器：检查缓存；
  3. 服务器：SQL解析，预处理，优化器生成执行计划；
  4. 调用存储引擎的API执行查询；
  5. 返回结果给客户端。

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

特点：

  1. 半双工

优点：

  • 简单快捷；

缺点：

  • 无法进行流量控制，一旦一端开始发送消息，另一端要完整接收后才能响应它。

  1. 服务器端：
    参数max_allowed_packet的作用：如果查询太大，服务端会拒绝接收更多的数据。

  2. 客户端：
    如果服务器发送的数据特别多，客户端必须完整地接收完整个结果。粗暴地断开连接不是个好主意，应该使用Limit进行限制。
    服务器端发送完全部数据，才会释放相应资源。

查询状态
相关命令：

1
SHOW FULL processlist;

相关状态值含义：

  1. Sleep: 等待客户端发送请求；
  2. Query：正在执行或发送结果；
  3. Locked:（MyIsam有，Innodb没有） 服务器层，线程等待表锁；（行锁等存储引擎级别的锁不会体现在这里。）
  4. Analyzing and statistics: 线程正在收集存储引擎的统计信息，并声称查询的执行计划。
  5. Copying to tmp table[on disk]： 线程正在执行查询，并且将其结果集都复制到一个临时表中。可能的操作包括： (1) GROUP BY；(2) 文件排序；(3) UNION操作。
  6. Sorting result: 对结果集进行排序；
  7. Sending data: (1)多个状态之间传送数据；
    (2)生成结果集中；
    (3)向客户端返回数据。

6.4.2 查询缓存(详见第7章)

缓存匹配原理:

  • Key: SQL的哈希值；
  • Value: 缓存(查询结果数据)。

其中对于函数Hash(SQL)是大小写敏感的，因此即使轻微的改动，也无法利用缓存。（Perconan版本的Mysql能够忽略注释。）

6.4.3 查询优化处理

SQL计划的生成:

  1. 解析SQL;
  2. 预处理；
  3. 优化SQL执行计划。

语法解析器和预处理

  • 语法解析器：
    SQL-语法解析器->解析树

  • 预处理：
    检查解析树是否合法，验证权限。
    例如检查数据表和数据列是否存在，检查名字和别名是否有歧义。

查询优化器

（上述语法树合法后）

  • 优化器：
    语法树-查询优化器->执行计划

一条查询可以有很多种执行方式，优化器的作用就是找到这其中最好的执行计划。

  • Mysql的优化器实现：

基于成本。
尝试预测一个查询使用某种执行计划的成本，选择其中成本最小的一个。

  • 成本计算示例
  1. 执行一次Where条件比较的成本，可以通过查询当前会话的Last_query_cost的值来得知Mysql计算的当前查询的成本。
    相关命令：
    1
    Show status like 'Last_query_cost'
    上述结果的Value列如果为1040，则意思是优化器认为大概需要1040个数据页的随机查找才能完成上面的查询。

优化器评估成本的时候并不考虑任何层面的缓存，假设读取任何数据都需要一次磁盘IO。

  • 优化器出错：
    选择的执行计划不是最优。（结果还是对的）

  • Mysql优化器出错的原因：

  1. 统计信息不准确；（存储引擎）
  2. 优化器不考虑磁盘数据页分布，成本估算出错；(可能因为顺序IO和随机IO情况不清楚)
  3. 优化器不考虑是否有并发执行；
  4. 优化器使用的目标函数并不以时间为目标，而是以估算的操作成本（有多少个数据页被读取，数据量）。
  5. 有些规则架凌于目标函数之上。例如：如果存在全文搜索的MATCH()子句，则在存在全文索引的时候就使用全文索引。（即使使用别的索引可能更快）。
  6. 优化器不考虑一些操作的成本。例如，存储过程的执行成本，UDF的执行成本；
  7. 优化器不会穷举所有可能的执行计划，可能错过最优解。

优化策略

  1. 静态优化；（类似编译时优化）
  2. 动态优化。（运行时优化）

  • 静态优化：
    直接对解析树进行分析，优化。例如通过代数变换把Where条件转换成另一种形式（但不会进行类型转换）。

  • 动态优化：
    与查询的上下文有关。例如Where条件中的具体取值，索引中条目对应的数据行数，等等。

优化类型

  1. 重定义关联表的顺序；
  2. 外连接转换成内连接；（等价时）
  3. 使用等价变换规则；（简化表达式）
  4. 优化Count,Min,Max：当Explain中看到Select tables optimized away时，进行了此种优化，把表的聚合计算用一个常数进行了替换；
  5. 常数替换：用常数替换某个表达式，或者某个唯一查询。（例如用主键或者唯一键查找的）（const）
  6. 覆盖索引扫描。（无需回表）
  7. 子查询优化；（?没说清，TODO）
  8. 提前终止查询：发现已经满足查询需求的时候，Mysql总能立即终止查询。(例如使用Limit时、发现不成立条件、空结果时)
  9. 等值传播：如果两列有等式关联，优化器能把其中一列的Where条件传递到另一列上。
  10. 列表In的比较：Mysql处理In列表的流程(O(NlogN))：（1）先对In列表进行排序；（2）二分查找确定值是否在列表中。其他数据库是转换成N个OR条件，因此是O(N)。IN列表很长时，Mysql速度更快。
  11. 其他。

Mysql如何执行关联查询

  • 关联
    每一次查询都是一次关联。

  • 执行关联的策略
    先在一个表中循环取出单条数据，再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。

上述策略无法应用于Full Join，因此Mysql没有Full Join。

  • 多表关联：
    Mysql选取某张表作为驱动表，然后按照上述逻辑进行循环，逐个关联其他表。

  • 缺点
    可并发度低。理想情况下，应该以平衡树形式进行关联。例如4张表关联，应该两两关联。而Mysql的做法就像使用一棵左侧深度优先的树，可并发度较低。

执行计划

SQL->指令树->存储引擎执行完成这棵指令树并返回结果。

  • 相关命令
    1
    2
    3
    explain extended <sql>;
    show warnings;
    -- 可以得到重构出的查询。

###　关联查询优化器
－目标
优化多个表关联时的顺序。

  • 案例SQL
    1
    2
    3
    4
    5
    6
    select *
    from film AS a 
    join film_actor AS b
      ON a.id=b.film_id
    join actor AS c 
      ON b.actor_id=c.id

一种可能的优化下，关联顺序是倒序，也就是使用actor作为驱动表。原因：预估的扫描数据页数量较少，通过存储引擎的索引统计信息，预估actor表较小，数据量减少得比较快。（其实就是小表优先）

n个表的关联，顺序的可能有n!种，所以搜索空间非常大，优化器不可能全部都预估一遍。相关参数optimizer_search_depth指定相应的阈值大小。超过阈值以后，优化器使用贪婪模式进行搜索。

排序优化

  • 排序的方法：
  1. 使用索引生成排序结果；
  2. 内存排序：数据量小于排序缓冲区；
  3. 外排。（需要使用磁盘）

Mysql将上述第二种和第三种统称为filesort。

  • Mysql使用的排序算法
  1. 两次传输；（旧版本）
    数据 = (行指针，需要排序的字段);
    对上述数据排序，然后根据结果行指针，回表获取全部数据。

  • 优点：
    排序数据量小(只用了排序字段)，可以更多使用内存排序；

  • 缺点：
    回表时，随机IO很多。而且需要两次传输。

  1. 单次传输；（新版本）
    数据 = (所有需要返回的列)
    对上述数据排序，然后直接返回结果。
    算法开始时一次顺序IO读取所有数据，

  • 优点
    随机IO少，适用于IO密集型应用。

  • 缺点
    如果返回列很多，单行很长，需要外排。

上述两种算法相关参数：
max_length_for_sort_data
如果所有列的总长度不超过这个参数，就使用单次传输，反之使用两次传输。

  • 关联查询+排序：
    (1) 如果Order by的所有列都来自关联的第1个表，Mysql在关联处理第一个表的时候就会进行排序。
    此时Explain结果的Extra字段会有Using filesort。
    (2) 其他情况下，Mysql会等整个关联结果出来后，再进行排序。
    此时Explain结果的Extra字段会有Using temporary; Using filesort。
    如果有Limit话，Limit也会在排序之后应用。
  • 启发
    关联查询排序时候，尽量把Order by的列提到第一张表中。

推荐文章