MySQL查询优化

Posted on | In
Words count in article: 2.7k | Reading time ≈ 9

MySQL查询优化

优化数据访问

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化，对于低效的查询，通过以下两个步骤来分析总是很有效：

  • 确认应用程序是否在检索大量超过需要的数据，这通常意味着访问了太多的行，但有时候也可能是访问了太多的列
  • 确认MySQL服务器层是否在分析大量超过需要的数据行

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

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

查询不需要的记录

多表关联时返回全部列

总是取出全部列

重复查询相同的数据

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。

对于MySQL，最简单的衡量查询开销的三个指标如下：

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

响应时间

响应时间包括服务时间和排队时间。

服务时间指的是数据库处理这个查询真正花了多少时间。

排队时间指的是服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁等等。

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有用的。理想情况下，扫描的行数和返回的行数应该是相同的。但实际上，这种“美事”并不多。扫描的行数对返回的行数的比率通常很小。一般在1：1和10：1之间，不过这个值也可能非常大。

扫描的行数和访问类型

MySQL有好几种访问方式可以查找并返回一行结果。有些访问当时可能需要扫描多行才能返回一行结果，也有些访问方式可能无需扫描就能返回结果。

在EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这些类型，扫描速度是从慢到快，扫描列数是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。

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

  1. 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
  2. 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
  3. 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要从数据表中读出记录然后过滤。

如果发现查询需要稻苗大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

  1. 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果了。
  2. 改变库表结构。例如使用单独的汇总表
  3. 重写这个复杂的查询，让MySQL优化器能够以更优化的方法区执行这个查询

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获取实际需要的结果——而不是一定总是需要从MySQL获取一模一样的结果集。有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好，但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

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

切分查询

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

删除旧的数据就是一个很好的例子。定期清除大量数据时，如果使用一个大的语句一次性完成的话，则可能需要一次锁住狠毒数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。讲一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能，同时还可以减少MySQL复制的延迟。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单的，可以对每一个表进行一个单表查询，然后将结果在应用程序中进行关联。例如，下面这个查询：

1
2
3
SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql'

可以分解成下面这些查询来代替：

1
2
3
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post = WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

事实上，用分解关联查询的方式重构查询有如下的优势：

  • 让缓存的效率更高。许多应用程序可以方便地缓存单标查询对应的结果对象。对于MySQL的查询缓存来说，如果关联表中的某个表发生了变化，那么就无法使用查询缓存了，而查分后，如果某个表很少改变，那么急于该表的查询就可以重复利用查询缓存结果了。
  • 将查询分解后，执行单个查询可以减少锁的竞争
  • 在应用层做关联，可以更容易对数据库进行查分，更容易做到高性能和可扩展
  • 查询本身效率也可能有所提升。
  • 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
  • 这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

查询执行的基础

当向MySQL发送一个请求的时候，MySQL到底做了什么？

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

查询缓存

MySQL查询缓存保存查询返回的完整结构。当查询命中该缓存时，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。

查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生了变化，那么和这个表相关的所有缓存数据都将失效。 MySQL将缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了以下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。 当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。

当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会缓存。包含任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。 有一点需要注意，MySQL并不是会因为查询中包含一个不确定的函数而不检查查询缓存，因为检查查询缓存之前，MySQL不会解析查询语句，所以也无法知道语句中是否有不确定的函数。 事实则是，如果查询语句中包含任何的不确定的函数，那么其查询结果不会被缓存，因为查询缓存中也无法找到对应的缓存结果。 有关查询缓存的配置如下所示。

  • query_cache_type:是否打开查询缓存。可以设置为OFF、ON和DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才会放入查询缓存。
  • query_cache_size:查询缓存使用的总内存空间。
  • query_cache_min_res_unit:在查询缓存中分配内存块时的最小单元。较小的该值可以减少碎片导致的内存空间浪费，但是会导致更频繁的内存块操作。
  • query_cache_limit:MySQL能够查询的最大查询结果。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以当结果全部返回后，MySQL才知道查询结果是否超出限制。超出之后，才会将结果从查询缓存中删除。

 对查询缓存的优化是数据库性能优化的重要一环。判断流程大致如下图所示:

参考资料

高性能MySQL

MySQL探秘(二)：SQL语句执行过程详解

0%