优化SQL数据访问的策略介绍

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

对于一些低效的查询，我们通常可以使用下面两个步骤来分析：

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

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

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

:railway_track:这里有一些经典案例：

1. 查询不需要的记录

   很多人会以为MySQL只会返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。一些开发者会先使用select语句查询大量的结果，然后获取前面的N行后关闭结果集。你以为mysql只返回了你需要的前几条信息，实际上MySQL是返回了全部结果集，然后丢弃了大部分的数据。最简单有效的解决方法就是加limit。

2. 多表关联时返回全部列

   比如说你想查询电影FLY 中出现的演员，你可千万千万不要像下面这样写：

   select * FROM actor
   inner join  film_actor using(actor_id)
   inner join  film using(film_id)
   where film.title = 'FLY';
   

   你这样写就把三个表的全部数据列都返回了，正确的方式是下面这么写，只取需要的列：

   select actor.* FROM actor
   inner join  film_actor using(actor_id)
   inner join  film using(film_id)
   where film.title = 'FLY';
   

3. 总是取出全部列

   每次看到**SELECT ***的时候都需要仔细想想，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化， 还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些DBA是严格禁止 SELECT *的写法的，这样做有时候还能避免某些列被修改带来的问题。何乐而不为呢？

   当然，你话不能说死，查询返回查过需要的数据也不总是坏事。因为这种有点浪费数据库资源的方式是可以简化开发的，因为它能提高相同代码片段的复用性。

4. 重复查询相同的数据

   你比如说，一个用户多次评论的时候，你每次都要查询它的id，这就很不好，我们呢可以采取的一种方案是，初次查询的时候就将这个数据缓存起来，需要的时候从缓存中取出来，这样性能显然会更好。

MySQL是否在扫描额外的记录

我们上面讲的是确定查询只返回需要的数据，那么我们还要关注什么呢？

:deciduous_tree:我们要去研究为了返回这个结果，有没有扫描过多的数据这一现象。

对于mysql，最简单的三个衡量查询开销的指标就下面这三哥们：

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

当然，没有哪个指标能够完美的说衡量出查询的开销，你只能通过这三指标去权衡罢了。

这三个指标都会记录到MySQL的慢日志中去，所以检查慢日志是找出扫描行数过多的查询的好办法。

1.响应时间

响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个査询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行査询，在那等资源所消耗的时间——可能是等I/O操作完成，也可能是等待行锁之类的。

但是上面所说的这些情况在实际情况下是更加复杂的情况，所以响应时间是没有什么一致的规律或者公式的。我们只能算个大致的时间然后去判断是不是一个合理的值。

2.扫描的行数和返回的行数

分析査询时，査看该査询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。

当然，这个指标可能不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

3.扫描的行数和访问类型

在评估查询的开销的时候，我们还需要考虑一下从表中找到一行数据的成本。因为MySQL有好几种访问方式可以査找并返回你想要的一行结果。有些访问方式可能需要扫描很多行才能返回一行 结果，但是也有些访问方式可能无须扫描就能返回结果。

在EXPLAIN语句中的type列可以体现出你的访问类型。访问类型有很多种，如全表扫描，索引扫描，范围扫描，唯一索引査询，常数引用等。这些访问的速度是从慢到快的，扫描的行数也是从小到大。当然我们是不需要记住这些访问类型。

如果你的查询没有办法找到合适的访问类型，俺么最好的解决方法就是增加一个合适的索引，这我们之前文章已经介绍了。

为什么索引岁查询性能的优化这么重要呢？索引让MySQL以最高效，扫描行数最少的方式找到你想要的结果。

一般MySQL能够使用如下的三种方式应用where条件，也是从好到坏的排序：

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

如果我们发现查询需要扫描大量的数据但是只是返回少数的行，那么我们通常可以尝试这些策略技巧去优化：

• 使用索引覆盖扫描，把所有需要用到的列都放到索引中。
• 改变库表结构。例如使用单独的汇总表
• 重写这个复杂的査询，让MySQL优化器能够以更优化的方式执行这个査询。