MySQL之查询性能优化(二)

查询性能优化

慢查询根本:优化数据访问

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

• 1.确认应用程序是否在检索大量高出需要的数据。这通常意味着访问了太多的行，但有时候也大概是访问了太多的列
  
• 2.确认MySQL服务器是否在分析大量高出需要的数据行
  

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

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

• 1.查询不需要的纪录:一个常见的错误是经常误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部效果集再进行计算。我们经常会看到一些了解其他数据库体系的人会计划出这类应用程序。这些开发者习惯实用这样的利用，先利用SELECT语句查询大量的效果，然后获取前面的N行后关闭效果集(比方在新闻网站中取出100条纪录，但是只是在页面上表现前面10条)。它们认为MySQL会执行查询，并只返回它们需要的10条数据，然后制止查询。实际环境是MySQL会查询出全部的效果集，客户端的应用程序会接收全部的效果集数据，然后抛弃此中大部分数据。最简朴有效的解决方法就是在这样的查询后面加上LIMIT
  
• 2.多表关联时返回全部列:如果你想查询所有在电影Academy Dinosaur中出现的演员，万万不要按下面的写法编写查询:
  

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

复制代码

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

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

复制代码

• 3.总是取出全部列:每次看到SELECT * 的时候都需要用猜疑的眼光审阅，是不是真的需要返回全部的列？很大概不是必须的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU的消耗。因此，一些DBA是严格克制SELECT * 的写法的，这样做有时候还能避免某些列被修改带来的问题。固然，查询返回高出需要的数据也不总是坏事。在许多案例中，人们会说这种有点浪费数据库资源的方式可以简化开发，由于能进步相同代码片段的复用性，如果打扫这样做的性能影响，那么这种做法也是值得考虑的。如果应用程序利用了某种缓存机制，或者有其他考虑，获取高出需要的数据也大概有其他长处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询相比多个独立的只获取部分列的查询大概就更有长处。
  
• 4.重复查询相同的数据:如果你不太鉴戒，很容易出现这样的错误——不断地重复执行相同的查询，然后每次都返回完全相同的数据。比方，在用户评论的地方需要查询用户头像的URL,那么用户多次评论的时候，大概就会反复查询这个数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。
  

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中的一个查询案例:

1. mysql> SELECT * FROM film_actor WHERE film_id =1;
2. +----------+---------+---------------------+
3. | actor_id | film_id | last_update         |
4. +----------+---------+---------------------+
5. |        1 |       1 | 2006-02-15 05:05:03 |
6. |       10 |       1 | 2006-02-15 05:05:03 |
7. |       20 |       1 | 2006-02-15 05:05:03 |
8. |       30 |       1 | 2006-02-15 05:05:03 |
9. |       40 |       1 | 2006-02-15 05:05:03 |
10. |       53 |       1 | 2006-02-15 05:05:03 |
11. |      108 |       1 | 2006-02-15 05:05:03 |
12. |      162 |       1 | 2006-02-15 05:05:03 |
13. |      188 |       1 | 2006-02-15 05:05:03 |
14. |      198 |       1 | 2006-02-15 05:05:03 |
15. +----------+---------+---------------------+

复制代码

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

1. mysql> EXPLAIN SELECT * FROM film_actor WHERE film_id=1\G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: film_actor
6.    partitions: NULL
7.          type: ref
8. possible_keys: idx_fk_film_id
9.           key: idx_fk_film_id
10.       key_len: 2
11.           ref: const
12.          rows: 10
13.      filtered: 100.00
14.         Extra: NULL
15. 1 row in set, 1 warning (0.00 sec)

复制代码

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

1. mysql> ALTER TABLE film_actor DROP FOREIGN KEY fk_film_actor_film;
2. Query OK, 0 rows affected (18.97 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> ALTER TABLE film_actor DROP KEY idx_fk_film_id;
6. Query OK, 0 rows affected (0.03 sec)
7. Records: 0  Duplicates: 0  Warnings: 0
8. mysql> EXPLAIN SELECT * FROM film_actor WHERE film_id=1\G
9. *************************** 1. row ***************************
10.            id: 1
11.   select_type: SIMPLE
12.         table: film_actor
13.    partitions: NULL
14.          type: ALL
15. possible_keys: NULL
16.           key: NULL
17.       key_len: NULL
18.           ref: NULL
19.          rows: 5462
20.      filtered: 10.00
21.         Extra: Using where
22. 1 row in set, 1 warning (0.00 sec)

复制代码

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

• 1.在索引中利用WHERE条件来过滤不匹配的纪录。这是在存储引擎层完成的
  
• 2.利用索引覆盖扫描(在Extra列出现了Using index)来返回纪录，直接从素银中过滤不需要的纪录并返回掷中的效果。这是在MySQL服务器层完成的，但无须再回表查询纪录
  
• 3.从数据表中返回数据，然后过滤不满足条件的纪录(在Extra列中出现Using WHere)。这在MySQL服务器层完成，MySQL需要从数据表独处纪录后过滤。
  上面这个例子阐明了好的索引多么重要。好的索引可以让查询利用符合的访问了悉尼港，尽大概地只扫描需要的数据行。但也不是说增加索引就能让扫描的行数即是返回的行数。比方下面利用聚合函数COUNT()的查询:
  

1. mysql> SELECT * FROM film_actor WHERE film_id =1;

复制代码

这个查询需要读取几千行数据，但是仅返回200行效果。没有什么索引能够让这样的查询减少需要扫描的行数。不幸的是，MySQL不会告诉我们生成效果实际上需要扫描多少行数据(比方关联查询效果返回的一条纪录通常是由多条纪录组成的)而只会告诉我们生成效果时一共扫描了多少行数据。扫描的行数中的大部分都很大概是被WHERE条件过滤掉的，对最终效果集并没有贡献。在上面的例子中，删除索引后，看到MySQL需要扫描所有纪录然后根据WHERE条件过滤，最终只返回10行效果。明白一个查询需要扫描多少行和实际需要利用的行数需要先去明白这个查询背后的逻辑和思想。
如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它:

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。