高性能Mysql＿笔记3（查询性能优化）

作者: 小名源治 | 来源:发表于2022-09-29 10:35 被阅读0次

1为什么查询会慢

查询的生命周期大致可以分为：从客户端到服务器端，然后在服务器上解析，生成执行计划，执行，然后返回结果给客户端。

其中执行是生命周期中最重要的阶段：其中包含了大量为了检索数据到存储引擎的调用，以及调用后的数据处理（分组，排序等）

2优化方法

2.1优化数据访问

性能低下的查询基本原因是因为访问的数据量太多了，某些查询不可避免的需要筛选大量的数据。

解决办法：

确认MySql服务器层是否在分析大量超过需要的数据行
确认应用程序是否在检索大量超过需要的数据。（这意味着访问了太多的行或者列了）

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

有些查询会请求超过实际需要的数据，这些数据最后是用不到的。这会给Mysql服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的内存资源。

2.1.2MySql是否在扫描额外的记录

最简单的衡量查询的三个指标：

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

这三个指标都会被记录到MySql的慢日志记录中，检查慢日志记录是找出扫描行数过多查询的最好的办法

2.2重构查询的方式

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

MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候,使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。

2.2.2切分查询

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

2.2.3分解关联查询（联表查询变成多个单表查询）

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询

分解查询的优势：

让缓存的效率更高。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

2.3执行查询的基础

我们要优化查询，那么就必须先知道查询是怎样执行的!

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

查询执行路径

2.3.1查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果"，这种情况下查询就会进入下一阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析﹐不用生成执行计划,不会被执行。

2.3.2查询优化处理

这里分为三个步骤：SQL语法解析 --> 预处理 --> 优化器生成执行计划

语法解析器：MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。
预处理：预处理器则根据一些MySQL 规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
优化器：现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

2.3.3查询执行引擎

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

在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实例表示。前面我们有意忽略了这点，实际上，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

2.3.4返回结果集给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个 查询的一些信息，如该查询影响到的行数。 如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。 MySQL将结果集返回客户端是一个增量、逐步返回的过程。 这样处理有两个好处：

服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让 MySQL客户端第一时间获得返回的结果。
结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

3 MySql优化器的局限性

3.1 关联子查询

有时候关联子查询的速度会很慢，因此我们需要换一种方式进行查询（使用左外连接，EXISTS（）等效的改写查询来获取更好的效率）。但是并不是所有的关联子查询效率都很差，需要具体情况具体分析；

关联子查询的缺陷：

例如，我们希望找到Sakila数据库中，演员Penelope Guiness(他的actor_id为1）参演过的所有影片信息。很自然的，我们会按照下面的方式用子查询实现:

mysql>SELECT FROM sakila.film
->WHERE fi1m_id IN(
 SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

我们觉得它会这样实现：

SELECT GROuP_CONCAT(film_id)FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

但是并不会，MySql中会有自己的优化策略。MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率地查找到数据行。也就是说，MySQL会将查询改写成下面的样子。如果遇到大的表，这样的查询效率很慢，因此我们需要改写。

SELECT *FROM sakila.film
WHERE EXISTS (
SELECT *FROM sakila.film_actor 
 WHERE actor_id = 1 
 AND fil1m_actor.fi1m_id = fi1m.film_id);</pre>

可以用一个内连接（等值连接）查询来解决,解决办法有很多，一般建议用左外连接重写关联子查询；

mysql> SELECT fi1m.*FROM sakila.film
->INNER JOIN sakila.fi1m_actor USING(fi1m_id)->WHERE actor_id = 1;

3.2UNION的限制

3.2.1什么是UNION和UNION ALL

mysql union 用于把来自多个select 语句的结果组合到一个结果集合中。语法为：

select column,......from table1

union [all]

select column,...... from table2

...

在多个select 语句中，对应的列应该具有相同的字段属性，且第一个select 语句中被使用的字段名称也被用于结果的字段名称。

3.2.2union 与 union all 的区别

当使用union 时，mysql 会把结果集中重复的记录删掉，

而使用union all ，mysql 会把所有的记录返回，且效率高于union 。

3.2.3使用示例

UNION 关键字是去除重复的记录，例如：

#查询两张表中的文章 id 号及标题，并去掉重复记录：
SELECT aid,title FROM article
UNION 
SELECT bid,title FROM blog

UNION 查询结果说明:

重复记录是指查询中各个字段完全重复的记录，如上例，若 title 一样但 id 号不一样算作不同记录。
第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称，如上例的 aid。
各 SELECT 语句字段名称可以不同，但字段属性必须一致。

使用UNION连接两个结果集，不会去除重复字段，例如：

#查询两张表中的文章 id 号及标题，
SELECT aid,title FROM article
UNION ALL
SELECT bid,title FROM blog

3.2.4 UNION 的限制，可以优化的办法

MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。有的查询就会因此慢很多，下面是例子：

如果需要将两个子查询的查询结果连接起来，然后再取其中的前20条，我们一般是这么写。这样写的执行查询是这样的：取出actor中的所有数据（假如2000条）和customer中的所有数据（假如1000条），放到临时表中，然后再去临时表中取出前20条，这样的查询效率很慢；

(SELECT first_name，last_name FROM sakila.actor
ORDER BY last_name)
UNION ALL
(SELECT first_name,last_name FROM sakila.customer
ORDER BY last_name)
LIMIT 20;

我们可以通过在UNION的两个子查询中分别加上一个LIMIT20来减少临时表中的数据:

(SELECT first_name,last_nameFROM sakila.actor
ORDER BY 1ast_nameLIMIT 20)
UNION ALL
(SELECT first_name,last_nameFROM saki1a.customer
ORDER BY last_nameLIMIT 20)
 LIMIT 20;

注意：从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一个全局的ORDER BY和LIMIT操作。