【读书笔记-高性能MySQL】探索查询性能优化的方案

AMANO
180 阅读11分钟

当我们了解如何设计一个高效的数据库结构、如何建立一个高效的索引，这些往往都是必不可少的，但这些还依然不够，我们仍旧需要合理的设计查询。

为什么查询会慢

我们需要将查询看成一个任务，而一个查询任务是由一系列子任务组成的，每个子任务都会消耗一定的时间，这都将影响我们查询的响应速度。如果我们想要优化查询，实际上无非是优化子任务的速度，或是减少子任务的数量。

那些步骤会消耗时间？

按照MySQL的查询声明周期，从客户端到服务器， 再到服务器上的语法解析、生成执行计划、执行、给客户端返回结果。其中”执行”可以认为是整个生命周期中的最重要的阶段。其包了大量对存储引擎的调用和数据处理工作。

同时在完成这些任务的时候，查询也可能在网络、CPU、生成统计信息、执行计划、锁等待等操作时消耗一定的时间。

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

一条查询语句如果性能很差，最常见的原因是访问的数据太多。通常我们可以通过以下两个步骤来分析：

  1. 应用是否存在检索大量且没有必要的数据，这意味着访问了太多的行。
  2. MySQL服务层是否分析了大量不需要的行。

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

有些查询会请求超过实际需要的数据、同时这些数据会被应用丢弃,如以下场景：

  1. 查询了不需要的记录。
  2. 多表连接时返回全部列。
  3. 总是取出全部列。
  4. 重复查询相同的数据。

MySQL是否扫描额外的记录

在确定查询只返回了需要的数据之后，我们应该看看为了查询返回结果MySQL是否扫描了过多的数据。通常可以通过衡量查询的三个开销来判断：

  1. 响应时间（衡量时间是否合理）
  2. 扫描的行数和返回的行数（这两个数相等是最好的，但是往往不可能）
  3. 扫描的行数和访问方式（查找某一行数据的成本）

explain语句中的type意味着什么？

type反映了数据的访问类型，从全表扫描,索引扫描,范围扫描,唯一索引查询,常数引用等，性能由慢到快，扫描的行数由多到少。

优化的思路

MySQL不会告诉我们生成结果实际上需要扫描多少行数据，而只会告诉我们生成结果的时候一共扫描了多少行数据。扫描的行中大部分都可能被WHERE条件过滤。所以如果我们发现查询需要扫描大量的数据却只返回少量行。通常由以下的思路去进行优化：

  1. 使用索引覆盖，将所需要的列放到索引中
  2. 改变数据库表结构
  3. 重写复杂查询

重构查询方式

优化由问题的查询时，我们常常可以将查询转换成返回结果相同的等价形式，以获得更好的性能。

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

设计查询的时候，已经经常需要考虑的问题是，是否可以将一个复杂的查询拆分成多个简单的查询。这往往与人们对于网络通信的优化的思路相悖。但对于MySQL并不适用，MySQL的连接和断开是非常轻量的，在返回一个小的查询结果方面很高效。

切分查询

切分查询实际是“分而治之”的思想，将一个大额查询切分成小查询，每个查询的功能完全一样，但每次只完成一部分。在删除大量旧数据时这就是一个很好的应用。可以有效缩小锁的范围、减少事务日志的大小，防止阻塞其他查询。

分解连接查询

将一个连接查询进行分解，对每一个表进行单表查询并在应用中进行连接往往能带来不少好处：

  1. 让缓存更高效
  2. 单个查询的锁竞争更少
  3. 应用层的连接，可以更容易对数据库进行拆分和拓展
  4. 查询本身效率会上升
  5. 可以减少对冗余数据的访问

查询执行基础

要优化查询的，最好的方式就是理解MySQL如何优化和执行查询。一旦理解这点，查询优化工作实际上就是遵循一些原则让优化器按预想的方式运作。

首先我们需要了解执行一个查询的过程中，MySQL做了些什么。

image.png

  1. 客户端给服务器发送一条SQL查询语句
  2. 服务器端对SQL语句进行解析、预处理、再由优化器生成对应的执行计划
  3. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
  4. 将结果返回客户端

MySQL客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让MySQL通信简单快速，但也限制了MySQL。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，他能做的事情就只是等待结果了。

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不是简单的只收取前面几条结果，然后让服务器停止发送数据。

查询状态

对于一个MySQL连接，或者一个线程，任何时间都有一个状态，该状态表示MySQL当前正在做什么。使用SHOW FULL PROCESSLIST命令就能查看当前状态。

  • sleep：线程正在等待客户端发送新的请求；
  • query：线程正在执行查询或者正在将结果发送给客户端；
  • locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
  • analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
  • copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。
  • sorting Result：线程正在对结果集进行排序。
  • sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在向客户端返回数据。

语法解析器和预处理

MySQL通过关键字对SQL语句进行解析，并生成一颗对应的“解析树”。MySQL通过解析器对SQL语句进行语法校验和解析查询。

预处理器检查生成的解析树，以查找解析器无法解析的其他语意。同时还会检查数据表和数据列是否存在，还会解析别名。

未命名文件 (5).png

查询优化器

一条语句可以有很多种执行方式，MySQL使用基于成本的优化器，可以尝试预测一个查询使用某种计划时的成本，并选择成本最小的一个。

虽然可能存在很多情况会导致MySQL的优化器选择错误的执行计划：

  1. 统计信息不准确
  2. 成本指标并不完全等于运行查询的实际成本
  3. MySQL基于成本的计划并不一定是最快的执行方式
  4. MySQL不考虑并发的查询
  5. MySQL不会考虑不受其控制的操作成本
查询优化器的静态优化和动态优化

静态优化

静态优化是直接对解析树进行分析，并完成优化，在第一次完成后就会一直有效，可以理解成“编译时优化”

动态优化

动态优化与上下文有关，也可能和很多因素有关，如WHERE条件的取值，索引对应的条数的数据行数等，所以每次运行时都需要重新评估。可以理解成“运行时优化”

MySQL能够处理的优化类型

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

MySQL通过生成一棵指令树，然后通过执行器执行完成这棵树并返回结果最终的执行计划包含了重构查询的全部信息。任何的多表查询都可以使用一棵树来表示。如图：

未命名文件 (6).png

而联接查询优化器决定了多个表联接的顺序，通过不同的联接顺序来获得相同的执行结果，以评估不同顺序带来的成本。

查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。

MySQL简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询MySQL只需要重复执行计划中的各个操作，直到完成所有的数据查询。

优化特性类型的查询

优化COUNT()查询

COUNT()是一个特殊的函数，它可以统计某列值的数量，也可以统计行数，在统计列值的时候要求列值为非空的，如果COUNT()函数中有表达式，则统计的就是满足这个表达式的结果数。通常来说COUNT()需要扫描大量的行，因此很难优化，可以做以下尝试：

  1. 在一个查询统计多个类型的量
  2. 使用近似值
  3. 引入外部缓存

优化联接查询

  1. 确保ON、USING（）子句所在的列上有索引
  2. 确保GROUP BY、ORDER BY表达式中只涉及一个表中的列

优化LIMIT和OFFSET子句

我们在面对偏移量很大的查询时，常常禹都需要查询大量的数据如limit 10000,20，这类语句需要查询10020条数据却只返回最后20条，对于这种查询通常有几种方式可以优化：

  1. 使用索引覆盖：
select uid, name  from user order by age limit 10000, 20;
-- 转换为
select u.name, u.age from user u 
inner join (select uid from user order by age limit 10000,20) as lim on u.uid = limit.uid
  1. 使用书签记录上次的位置
select * from user order by create_time desc limit 20;
-- 转换为
select * from user where uid < 10000 order by create_time desc lmit 20;
  1. 使用预先计算的汇总表

优化UNION查询

MySQL总是通过填充临时表的方式来执行UNION查询，因此很多查询优化策略都无法使用。通常需要将所有的WHERE、LIMIT、ORDER BY等子句下推到每一个UNION查询中。

除非你需要服务器消除重复的行，否则一定要使用UNION ALL,如果没有ALL关键字，MySQL会给临时表加上一个DISTINCT关键字，这将会导致对整个临时表做唯一性检查，代价非常高。

avatar
文章
阅读
粉丝