第六章-查询性能优化

——「高性能MYSQL」读书笔记（第六章）

《高性能****MYSQL》

MYSQL经典书籍，常读常新。

查询优化分为三大部分：

• 库表结构优化（第四章）

• 索引优化（第五章）

• 查询优化

本章主要讲述第三部分的优化

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

最基本原因：访问的数据太多

常规解决方法：减少访问的数据量

• 是否在向数据库检索大量超过需要的数据（行或列）

• MySQL****服务器层是否在分析大量超过需要的数据行。可以从响应时间、扫描的行数和返回的行数这三个指标进行评估。

列名	含义
id	每个select操作的唯一标识
select_type	查询的类型，我们可以根据该字段判断查询的性质，包括查询是简单/复杂查询类型
table	查询访问表的别名
type	关联的类型，mysql把查询过程都视为关联，不管是单表/多表。这个字段也是衡量查询性能的关键字段之一
possible_keys	查询可能会使用哪些索引，这列是基于查询访问的列来判断的
key	mysql最终决定使用哪个索引（这个索引不一定出现在possible_keys中）
key_len	mysql在索引里使用的字节数，我们可以根据它推断具体使用了索引中的哪些字段
ref	查找所用的列/常量
rows	mysql估算的预计扫描行数，这个数字和实际扫描的行数可能相差甚远，包括limit语句对于这个估算值也是不起作用的
filtered	表里符合条件的记录数的百分比的估计，我们可以用这个字段大致估计表关联时关联的记录数
extra	包含一些额外信息，也是我们优化时需要重点关注的字段

重构查询

目标：找到更优方法获取实际需要的结果

方法：

1. 改变查询写法

2. 修改应用代码

场景一、一个复杂查询还是多个简单查询

复杂查询考量：让数据库层完成尽量多的工作， 网络通讯、查询解析和优化代价很高

变化点：

• MySQL连接和断开连接都很轻量

• 现代网速快：带宽大和延迟变小

反例：在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。例如，我们看到有些应用对一个数据表做10次独立的查询来返回10行数据，每个查询返回一条结果，查询10次！

场景二、切分查询

示例

删除大量旧数据， 可以limit分批删除

好处

• 避免一次锁住太多数据

• 占满整个事务日志

• 耗尽系统资源

• 阻塞很多小且重要的查询

• 减少MySQL复制的延迟

场景三、分解关联查询

对每一个表进行一次单表查询，将结果在应用中关联

适用条件

• 数据分布到不同的MySQL服务器

• 能用IN() 代替关联查询

• 查询中使用同一个数据表

查询执行的基础

查询执行过程

我们可以看到当向 MySQL 发送一个请求的时候，MySQL 到底做了什么：

1. 客户端发送一条查询给服务器。

2. 服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

3. 服务器进行 SQL 解析、预处理，再由优化器生成对应的执行计划。

4. MySQL 根据优化器生成的执行计划，调用存储引起的 API 来执行查询。

5. 将结果返回给客户端。

MySQL客户端/服务器通讯协议

简单原理： 半双工，任一时刻要么服务器向客户端发送数据，要么客户端向服务器发送数据（不会同时发生），可以用SHOW PROCESSLIST查看状态

好处：简单快速

坏处：无法进行流量控制

查询缓存

通过大小写敏感的哈希查找实现判断是否命中

查询的生命周期的下一步是将一个 SQL 转换成一个执行计划，MySQL 再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误（如语法错误）都可能终止查询。

通过关键字将SQL语句进行解析，并生成一颗对应的****解析树。解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些MySQL规则进一步检查解析树是否合法。

优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后返回相同的结果。优化器的作用就是找出这其中最好的执行计划

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划测成本，并选择其中成本最小的一个。可以通过查询当前会话的Last_query_cost得知查询成本

如上，优化器认为大概需要做1040个数据页的随机查找才能完成。统计信息：每个表或者索引的页面个数、索引的基数、索引和数据行的长度、索引分布情况。 优化器在评估成本的时候并不考虑任何层面的缓存，假设读取任何数据都需要一次磁盘I/O

选择错误执行计划原因：

• 统计信息不准确

• 执行计划中的成本估算不等同于实际执行的成本

• MySQL的最优可能和你想的最优不一样

• MySQL从不考虑其他并发执行的查询

• MySQL也不是任何时候都是基于成本的优化

• MySQL不会考虑不受其控制的操作的成本

优化策略：

• 静态优化。对解析树进行分析，并完成优化

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

优化类型：

• 重新定义关联表的顺序： ** 类似优化两个for循环**

• 将外连接转化成内连接

• 等价交换原则; 比如合并或减少恒成立或恒不成立的判断

• 优化COUNT()、MIN()和MAX()： 缓存， 索引最大、最小值

策略：MySQL对任何关联都执行嵌套循环关联操作

简单理解for循环， 尽量外层数据量越少越好

排序是一个成本很高的操作，从性能角度出发，尽量避免排序或避免大量数据排序。 数据量大且不是索引排序时：使用磁盘排序

在解析和优化阶段，MySQL 将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。相比于查询优化阶段，查询执行阶段比较简单，MySQL 只是简单地根据执行计划给出的指令逐步执行。

如果查询可被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果返回给客户端是一个增量、逐步返回的过程。当生成结果时就可以开始向客户端逐步返回结果集了。

MySQL的子查询实现非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()，可以考虑用EXISTS替换：

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

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

那么优化器会将IN()列表都复制应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。在本书写作的时候，除了修改MySQL源代码，目前还没有什么办法能够绕过该问题（不过这个问题很少会碰到）。

MySQL****无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。

在本书写作的时候，MySQL并不支持哈希****关联——MySQL的所有关联都是嵌套循环关联。不过，可以通过建立一个哈希索引来曲线地实现哈希关联。如果使用的是Memory存储引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。可以参考第5章的“创建自定义哈希索引”部分。另外，MariaDB已经实现了真正的哈希关联。

COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。

关注点：

• 确保 ON 或者 USING 子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列 c 关联的时候，如果优化器的关联顺序是 B、A，那么就不需要在 B 表的对应列上建上索引，没有用到的索引只会带来额外的负担。一般来说，除非有其它理由，否则只需要再关联顺序中的第二个表的相应列上创建索引。

• 确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程。

• 当升级 MySQL 的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果等。

很多场景下，MySQL 都使用统一的办法优化这2种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。他们都可以使用索引来优化，这也是最有效的优化办法。

在 MySQL 中，当无法使用索引的时候，GROUP BY 使用2种策略来完成：使用临时表或者文件排序来做分组。

在系统中需要进行分页操作的时候，我们通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 子句。如果有对应的索引，通常效率会不错，否则，MySQL 需要做大量的文件排序操作。

一个常见但是又令人头疼的问题是，在偏移量非常大的时候，例如可能是 LIMIT 1000, 20 这样的查询，这时 MySQL 需要查询 10020 条记录然后只返回最后 20 条，前面的 10000条记录都将被抛弃，这样的代价很高。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页****查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。

 mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
 mysql> SELECT film.film_id, film.description         
     -> FROM sakila.film         
     ->    INNER JOIN (         
     ->       SELECT film_id FROM sakila.film         
     ->       ORDER BY title LIMIT 50, 5         
     ->    ) AS lim USING(film_id);

LIMIT 和 OFFSET 的问题，其实是 OFFSET 的问题，它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从书签记录的位置开始扫描，这样就可以避免使用 OFFSET。

mysql> SELECT * FROM sakila.rental 
    -> WHERE rental_id < 16030 
    -> ORDER BY rental_id DESC LIMIT 20

MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询，因此很多优化策略在 UNION 查询中都没法很好的使用。经常需要手工地将 WHERE、LIMIT、ORDER BY 等子句“下推”到 UNION 的各个子查询中，以便优化器可以充分利用这些条件进行优化。

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