MySQL之查询性能优化(二)

csdn推荐

查询性能优化 慢查询基础:优化数据访问

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

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

有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这回给MySQL服务器带来额外的负担,并增加网络开销(如果应用服务器和数据库不在同一台主机上,网络开销就显得很明显了。即使在同一台服务器上仍然会有数据传输的开销)。另外也会消耗应用服务器的CPU和内存资源。下面是一些典型案例:

mysql> SELECT * FROM actor
    -> INNER JOIN film_actor USING(actor_id)
    -> INNER JOIN film USING(film_id)
    -> WHERE film.title='Academy Dinosaur';

这将返回这三个表的全部数据列。正确的方式应该时像下面这样只取需要的列:

mysql>SELECT actor.* FROM actor .....

MySQL是否扫描额外的记录

在确定查询只返回需要的数据以后,接下来应该看看查询为了返回结果是否扫描了许多的数据。对于MySQL,最简单的衡量查询开销的三个指标如下:

1.响应时间

2.扫描的行数

3.返回的行数

没有哪个指标能够完美地衡量查询的开销,但它们大致反映了MySQL在内部执行查询时需要访问多少数据,并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法

响应时间

要记住, 响应时间只是一个表面的值。这样说可能看起来和前面关于响应时间的说法有矛盾?其实并不矛盾,响应时间仍然是最重要的指标,这有一点复杂,后面细细道来。响应时间是两个部分之和:服务时间和排队时间。服务时间是指数据处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等待IO操作完成,也可能是等待行锁,等等。遗憾的是,我们无法把响应时间细分到上面这些部分,除非有什么办法能够逐个测量上面这些消耗,不过很难做到,一般最常见和重要的等待是IO和锁等待,但实际情况更加复杂。所以在不同类型的应用压力下,响应时间并没有什么一致的规律或者共识。诸如存储引擎的锁(表锁、行锁)、高并发资源竞争、硬件响应等诸多因素都会影响到响应时间。所以,响应时间既可能是一个问题的结果也可能是一个问题的原因,不同案例情况不同,除非我们能深入测量出每个环节。

当你看到一个查询的响应时间的时候,首先需要问问自己,这个响应时间是否是一个合理的值。实际上可以使用"快速上限估计"法来估算查询的响应时间,这是由Lahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers一书中提到的技术。概括地说,了解这个查询需要哪些索引以及它的执行计划是什么,然后计算大概需要多少个顺序和随机IO,再用其乘以在具体硬件条件下一次IO的消耗。最后把这些消耗都加起来,就可以获得一个大概参考值来判断当前响应时间是不是一个合理得值

扫描的行数和返回的行数

分析查询时,查看该查询扫描的行数时非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。对于找出哪些"糟糕"的查询,这个指标可能还不够完美,因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快,内存中的行也比磁盘中的行访问速度要快得多。理想情况下扫描得行数和返回的行数应该是相同的。但实际情况中这种"美事"并不多。例如在做一个管来奶查询时,服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小,一般在1:1和10:1之间,不过有时候这个值也可能非常非常大

扫描的行数和访问类型

在评估查询开销的时候,需要考虑一下从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果,也有些访问方式可能无须扫描就能返回结果。在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些,速度时从慢到快,扫描的行数也是从多到少。你不需要记住这些访问类型,但需要明白扫描表、扫描索引、范围访问和单值访问的概念.如果查询没有办法找到合适的访问类型,那么解决的最好办法通常就是增加一个合适的索引。现在应该明白为什么索引对于查询优化如此重要了。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。例如,我们看看示例库Sakila中的一个查询案例:

mysql> SELECT * FROM film_actor WHERE film_id =1;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|        1 |       1 | 2006-02-15 05:05:03 |
|       10 |       1 | 2006-02-15 05:05:03 |
|       20 |       1 | 2006-02-15 05:05:03 |
|       30 |       1 | 2006-02-15 05:05:03 |
|       40 |       1 | 2006-02-15 05:05:03 |
|       53 |       1 | 2006-02-15 05:05:03 |
|      108 |       1 | 2006-02-15 05:05:03 |
|      162 |       1 | 2006-02-15 05:05:03 |
|      188 |       1 | 2006-02-15 05:05:03 |
|      198 |       1 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+

这个查询将返回10行数据,从EXPLAIN的结果可以看到,MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询:

mysql> EXPLAIN SELECT * FROM film_actor WHERE film_id=1G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: const
         rows: 10
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAIN的结果也显示MySQL预估需要访问10行数据。换句话说,查询优化器认为这种访问类型可以高效地完成查询。如果没有合适地索引会怎样呢?MySQL就不得不使用一种更糟糕地访问类型,下面我们来看看如果我们删除对应的索引再来运行这个查询:

mysql> ALTER TABLE film_actor DROP FOREIGN KEY fk_film_actor_film;
Query OK, 0 rows affected (18.97 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE film_actor DROP KEY idx_fk_film_id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> EXPLAIN SELECT * FROM film_actor WHERE film_id=1G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5462
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

正如我们预测的,访问类型变成了一个全表扫描(ALL),现在MySQL预估需要扫描5462条记录来完成这个查询。这里的"Using Where"表示MySQL将通过WHERE条件来筛选存储引擎返回的记录。一般MySQL能够使用如下三种方式应用WHERE条件,从好到坏依次为:

mysql> SELECT * FROM film_actor WHERE film_id =1;

这个查询需要读取几千行数据,但是仅返回200行结果。没有什么索引能够让这样的查询减少需要扫描的行数。不幸的是,MySQL不会告诉我们生成结果实际上需要扫描多少行数据(例如关联查询结果返回的一条记录通常是由多条记录组成的)而只会告诉我们生成结果时一共扫描了多少行数据。扫描的行数中的大部分都很可能是被WHERE条件过滤掉的,对最终结果集并没有贡献。在上面的例子中,删除索引后,看到MySQL需要扫描所有记录然后根据WHERE条件过滤,最终只返回10行结果。理解一个查询需要扫描多少行和实际需要使用的行数需要先去理解这个查询背后的逻辑和思想。

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

文章来源:https://blog.csdn.net/Cover_sky/article/details/139425544



微信扫描下方的二维码阅读本文

© 版权声明
THE END
喜欢就支持一下吧
点赞14 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容