Skip to content

Latest commit

 

History

History
312 lines (200 loc) · 16.9 KB

03.查询.md

File metadata and controls

312 lines (200 loc) · 16.9 KB
Raw

查询性能优化

为什么查询会变慢

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

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

查询性能地下最基本的原因是访问的数据太多。

是否请求了不需要的数据

略

MySQL 是否在扫描额外的记录

查看查询为了返回结果是否扫描了过多的数据。对于MySQL，衡量查询开销的三个指标如下：

  • 响应时间
  • 扫描的行数
  • 返回的行数

响应时间

响应时间=服务时间（真正花了多少时间）+ 排队时间

扫描的行数与访问类型

在评估查询开销的时候，需要考虑以下从表中查找某一行数据的成本（扫描的行数）。

一般 MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏依次为：

  • 在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
  • 使用索引覆盖，直接从索引中过滤不需要的记录并返回。这是在 MySQL 服务器层完成的，但无须再回表查询记录。
  • 从数据表中返回数据，然后过滤不满足条件的记录（在Extra 列中出现 Using where）。这是在 MySQL 服务器层完成，MySQL 需要先从数据表读取记录然后过滤。

重构查询

将查询换一种写法让其返回一样的结果，但是性能更好。

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

略

切分查询

将大查询切分成小查询，每个查询功能一样，只完成一小部分，每次只返回一小部分查询结果。

举例：将一个大的 DELETE 语句切分成多个较小的查询可以尽可能小地影响 MySQL 性能。

分解关联子查询

  • 将查询分解，执行单个查询可以减少锁的竞争
  • 在应用层做关联，可以更容易对数据库进行拆分。
  • 查询性能本身效率也可能会有所提升。比如使用 IN (ids...) 代替关联查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的关联要更高效。
  • 在应用层实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。某些场景下哈希关联的效率要高很多。

查询执行的基础

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

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

客户端用一个单独的数据包将查询传给服务器。当查询的数据很长的时候，参数 max_allowed_packet 就特别重要了。

当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果。因此在必要的时候一定要在查询中加上 LIMIT 限制。

查询状态

对于一个 MySQL 连接，或者说一个线程，任何时刻都有一个状态。该状态表示了 MySQL 当前正在做什么。可以使用 SHOW FULL PROCESSLIST 命令。在一个查询的生命周期中，状态会变化很多次。下面将这些状态列出来

Sleep
线程正在等待客户端发送新的请求

Query
线程正在执行查询或正在将结果发送给客户端

Locked
在 MySQL 服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如 InnoDB 的行锁，并不会体现在线程状态中。对于 MyISAM 来说这是应该比较典型的状态。

Analyzing and statistics
线程正在收集存储引擎的统计信息，并生成执行计划。

Copying to tmp table [on disk]
线程正在执行查询，并且将结果集都复制到一个临时表，这种状态要么是在做 GROUP BY 操作，要么是文件排序操作，或者是 UNION 操作。如果这个状态后面还有应该“on disk”标记，那表示 MySQL 正在将一个内存临时表放到磁盘上。

Sorting result
线程正在对结果集进行排序。

Sending data
这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存

略

查询优化处理

语法解析器和预处理

解析器负责将SQL解析，生成解析树。它将验证是否使用错误的关键字，使用关键字的顺序是否正确，引号是否前后匹配。 预处理器负责检查数据表和数据列是否存在，解析名字和别名是否有歧义，验证权限。

查询优化器

MySQL 使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个 4K 数据页的成本，后面变得更复杂，引入了一些“因子”来估算某些操作的代价。
可以通过查询当前会话的 Last_query_cost 值来得知 MySQL 计算的当前查询的成本。

SHOW STATUS LIKE 'Last_query_cost'

有多种原因导致 MySQL 优化器选择错误的执行计划，如下所示：

  • 统计信息不准确
  • 执行计划中的成本估算不等同于实际执行的成本。比如，有时某个执行计划虽然需要读取更多的页面，但是它的成本可能却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的华，那么它的访问成本将很小。而 MySQL 层面并不知道哪些页面在内存中，哪些在磁盘上，所以查询实际执行过程中导致需要多少次物理I/O是无法得知的。
  • MySQL 的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短，但是 MySQL 只是基于成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。
  • MySQL 从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
  • MySQL 也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则，比如在有全文索引时，使用 MATCH() 子句，则一定会使用全文索引。
  • MySQL 不会考虑不受其控制的操作的成本，比如执行存储过程或者用户自定义函数的成本。

查询优化器使用很多优化策略来生成一个最优的执行计划。优化策略可以分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。（编译时优化）

动态优化则和查询的上下文有关，也可能和很多其它因素有关，例如 WHERE 条件中的取值、索引中条目对应的数据行数等。

MySQL 对查询的静态优化只需要做一次，但对查询的动态优化则在每次执行时都需要重新评估。有时甚至在查询的出行过程中也会重新优化。

下面是 MySQL 能够处理的优化类型：

① 重新定义关联表的顺序

② 将外连接转化成内连接

③ 使用等价变换规则 比如移除一些恒成立或恒不成立的判断。

④ 优化 COUNT(), MIN()和 MAX() 要找某一列的最小值，只需要查询对应B-Tree索引的最左端记录，MySQL可以直接获取索引的第一行记录。在 B-Tree 索引中，优化器会将这个表示式作为一个常数对待。如果 MySQL 使用了这种类型的优化，那么在 EXPLAIN 中就可以看到 "Select tables optimized away"。

⑤ 预估并转化为常数表达式 比如 type 为 const 的查询。

⑥ 覆盖索引扫描

⑦ 子查询优化 MySQL 在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据的访问。

⑧ 提前终止查询 举例:

EXPLAIN SELECT film.film_id
FROM sakila.film
  LEFT JOIN film_actor USING (film_id)
  WHERE film_actor.film_id IS NULL;

这个查询将会过滤掉所有有演员的电影。每一部电影可能有很多演员，但是上面的查询一胆找到任何一个，就会停止并立刻判断下一部电影，因为只要有一名演员，那么 WHERE 条件则会过滤这类电影。

⑨ 等值传播 如果两个列这值通过等式关联，那么 MySQL 能够把其中一个列的 WHERE 条件传递到另一个列上。例如：

SELECT film.film_id
FROM sakila.film
    INNER JOIN sakila.film_actor USING (film_id)
WHERE film.film_id > 500;

因为这里使用了 film_id 字段进行等值关联，MySQL 知道这里的 WHERE 子句不仅使用于 film 表，而且对于 film_actor 表同样适用。

⑩ 列表 IN() 的比较 在很多数据库系统中，IN() 完全等同于多个 OR 条件的子句，但在 MySQL 中这点是不成立的，MySQL 将 IN() 列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个 O(log n) 复杂度的操作， 等价地转换成 OR 查询的复杂度为 O(n)，对于 IN() 列表中有大量取值的时候，MySQL 的处理速度将会更快。

数据和索引的统计信息

略

MySQL 如何执行关联查询

MySQL 中的“关联”一词包含的意义比一般意义上理解的要更广泛。总的来说，MySQL 认为任何一个查询都是一次“关联” —— 并不仅仅是一个查询需要到两表匹配才叫关联。

对 UNION 查询，MySQL 先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成 UNION 查询。在 MySQL 的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

当前 MySQL 关联执行的策略很简单：MySQL 对任何关联都执行嵌套循环关联操作，即 MySQL 先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中循环匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL 返回到上一层次关联表，看能否找到更多的匹配记录，依此类推迭代执行。

从本质上说，MySQL对所有类型的查询都以同样的方式运行。例如，MySQL 在 FROM 子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中（不过，在 MySQL 5.6 中有了重大改变）（MySQL 的临时表是没有任何索引的），然后将这个临时表当作一个普通表对待。MySQL 在遇到外连接查询时，会将其改写成等价的左外连接。

执行计划 MySQL 总是从一个表开始一直嵌套循环、回溯完成所有表关联。MySQL 的执行计划是一棵左侧深度优先的树。

关联查询优化器 优化器通过评估不同顺序时的成本来选择一个的代价最小的关联顺序。不过有的时候，优化器给出的并不是最优的关联顺序，这是可以使用 STRAIGHT_JOIN 关键字重写查询，让优化器按照你认为的最优的关联顺序执行。

排序优化 当无法使用索引排序(type=index)时，MySQL 需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过 MySQL 将这个过程统一称为文件排序(filesort)，即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”，MySQL 使用内存进行“快速排序”操作。如果内存不够排序，那么 MysQL 会将数据分快，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并(merge)，最后返回排序结果。

根据排序所取的数据量不同，MySQL 有两种排序算法：

两次传输排序（旧版本使用） 只读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。需要从数据表中读取两次数据。

单次传输排序（新版本使用） 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。这个算法只有在 MySQL 4.1 和后续更新的版本才引入。

当查询需要所有列的总长度不超过参数 max_length_for_sort_data 时，MySQL 使用“单次传输排序”。

MySQL 在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于 MySQL 在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串，例如，如果是 VARCHAR 列则需要分配其完整长度；如果使用 UTF-8 字符集，那么 MySQL 将会为每个字符预留三个字节。

在关联查询时如果需要排序，MySQL 会分两种情况来处理这样的文件排序。如果 ORDER BY 子句中的所有列都来自关联的第一个表，那么 MySQL 在关联处理第一个表的时候就进行文件排序。 如果是这样，那么在 MySQL 的 EXPLAIN 结果中可以看到 Extra 字段会有 “Using filesort”。除此之外的所有情况，MySQL 都会将关联的结果存放到一个临时表中，然后在所有关联都结束后，再进行文件排序。在这种情况下，在 MySQL 的 EXPLAIN 结果的 Extra 字段中可以看到 “Using temporary; Using filesort”。

查询执行引擎

略

MySQL 查询优化器的局限性

关联子查询

MySQL 对 IN() 列表中的选项有专门的优化策略：MySQL 会将相关的外层表压到子查询中，它认为这样可以更高效地查找到数据行。

原 sql:

SELECT * FROM film
WHERE film_id IN (
    SELECT film_id FROM film_actor WHERE actor_id = 1);

采用 “全表+关联子查询” （MySQL 5.5 之前的内部的优化方式）优化后的 sql:

SELECT * FROM film
WHERE EXISTS(
    SELECT * FROM film_actor WHERE actor_id = 1
    AND film_actor.film_id = film.film_id);

MySQL 会选择对这个 film 表进行全表扫描，然后根据返回的 film_id 逐个执行子查询。如果外层表是一个很大的表，那么这个查询的性能将会非常糟糕。我们可以使用下面的方法进行改写：

EXPLAIN SELECT film.* FROM film
INNER JOIN film_actor USING (film_id)
WHERE actor_id = 1;

UNION 的限制

有时，MySQL 无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内存查询的优化上。

比如，如果希望 UNION 的各个子句能够根据 LIMIT 只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在 UNION 的各个子句中分别使用这些子句。

例如，想将查询结果联合起来，再取前 20 条记录，那么 MySQL 会将两个表都存放到同一个临时表中，然后再取出前 20 行记录。

注意如果有排序需求，主句与每个子句都都要加一个 ORDER BY。

索引合并优化

在 5.0和更新的版本中，当 WHERE 子句中包含多个复杂条件的时候，MySQL 能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

等值传递

略

并行执行

MySQL 无法利用多核特性并行执行查询。

哈希关联

MySQL 8.0 多表连接查询支持 hash join。MySQL 实现了用于内连接查询的 hash join 方式。

SELECT * 
    FROM t1 
    JOIN t2 
        ON t1.c1=t2.c1;

Hash join 不需要索引的支持。大多数情况下，hash join 比之前的 Block Nested-Loop 算法在没有索引时的等值连接更加高效。

使用 EXPLAIN FORMAT=TREE 可以查看执行计划中的 hash join。

松散索引扫描（相当于 oracle 中的跳跃索引扫描）

多索引列时，通过松散索引扫描，利用索引的顺序特性，提前终止扫描。从而避免全表扫描。但由于历史原因，MySQL 不能很好地支持松散索引扫描。不过， 在某些特殊的场景下也是可以使用松散索引扫描的，例如，在一个分组查询中找到分组的最大值和最小值。

EXPLAIN SELECT actor_id, MAX(film_id)
FROM film_actor
GROUP BY actor_id;

在 EXPLAIN 中的 Extra 字段显示 “Using index for group-by”，表示这里将使用松散索引扫描。

最大值与最小值优化

对于 MIN() 和 MAX() 查询，MySQL 的优化做得并不好。比如：

EXPLAIN SELECT MAX(actor_id) FROM actor
        WHERE first_name = 'PENELOPE';

因为在 first_name 字段上并没有索引，因此 MySQL 将会执行一次全表扫描。如果 MySQL 能够进行主键扫描，那么理论上，当 MySQL读到第一个满足条件的记录的时候，就是我们需要的最小值了，因为主键是严格按照 actor_id 字段的大小顺序排列的。但是 MySQL 只会做全表扫描，我们可以通过查看 SHOW STATUS 的全表扫描计数器来验证这一点。

一个曲线优化方法是移除 MIN()，然后使用 LIMTI 来将查询重写如下：

EXPLAIN SELECT actor_id FROM actor use INDEX (`PRIMARY`)
WHERE first_name = 'PENELOPE' LIMIT 1;

在同一个表上查询和更新

略

查询优化器的提示 (hint)