作者:依然-狠幸福 | 来源:互联网 | 2020-09-28 14:39
一些典型案例如下:
1. 查询不需要的记录
这是一个常见的错误,常常会误以为MySQL只会返回需要的数据,实际上MySQL却是先返回全部结果集再进行计算。
开发者习惯性的先使用SELECT语句查询大量的结果,然后由应用查询或者前端展示层再获取前面的N行数据,例如,在新闻网站中查询100条记录,但是只是在页面上显示前10条。
最有效的解决方法是需要多少记录就查询多少记录,通常会在查询后面加上LIMIT,即: 分页查询。
2. 多表关联时返回全部列
如果你想查询所有在电影Academy Dinosaur中出现的演员,千万不要按下面的方式来进行查询:
select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';
这样将会返回三张表的全部数据列,而实际需求是要查询演员信息,正确的写法应该是:
select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';
3. 总是查询出全部列
每次看到select *的时候一定要用异样的目光来审视它,是不是真的需要返回全部数据列?
在大部分情况下,是不需要的。 select *会导致进行全表扫描,会让优化器无法完成索引扫描这类优化,过多的列还会为服务器带来额外的I/O、内存和CPU的消耗。 即使真的需要查询出全部列,应该逐个罗列出全部列而不是*。
4. 重复查询相同的数据
如果你不太留意,很容易出现这样的错误: 不断地重复执行相同的查询,然后每次都返回完全相同的数据。
例如,在用户评论的地方需要查询用户头像的URL,那么用户多次评论的时候,可能就会反复来查询这个数据。 比较好处理方法是,在初次查询的时候将这个数据缓存起来,后续使用时直接从缓存中取出。
三、是否扫描了额外的记录
确 定查询只查询了需要的数据以后,接下来应该看看查询过程中是否扫描了过多的数据。 对于MySQL,最简单衡量查询开销的三个指标如下:
没有哪个指标能够完全来衡量查询的开销,但它们能够大致反映MySQL内部执行查询时需要访问多少数据,并可以大概推算出查询运行的实际。 这三个指标都会记录到MySQL的慢日志中,所以 检查慢日志记录是找出扫描行数过多查询的办法 。
慢查询: 用于记录在MySQL中响应时间超过阈值(long_query_time,默认10s)的语句,并会将慢查询记录到慢日志中。 可通过变量slow_query_long来开启慢查询,默认是关闭状态,可以将慢日志记录到表slow_log或文件中,以供检查分析。
1. 响应时间
响应时间是两个部分之和: 服务时间和排队时间。 服务时间是指数据库处理这个查询真正花费了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间,可能是等待I/O操作,也可能是等待 行 锁等等。
在不同类型的应用压力下,响应时间并没有什么一致的规律或者公式。 诸如存储引擎的锁(表锁,行锁),高并发资源竞争,硬件响应等诸多因素都会影响响应时间,所以,响应时间既可能是一个问题的结果也可能是一个问题的原因,不同案例情况不同。
当你看到一个查询的响应时间的时候,首先需要问问自己,这个响应时间是否是一个合理的值。
2. 扫描的行数和返回的行数
在分析查询时,查看该查询扫描的行数是非常有帮助的,在此之上也能够分析是否扫描了额外的记录。
对于找出那些糟糕查询,这个指标可能还不够完美,因为并不是所有行的访问代价都是相同的。 较短的行的访问速度相当快,内存中的行也比磁盘中的行的访问速度要快的多。
理想的情况下,扫描的行数和返回的行数应该是相同的。 但实际上这种美事并不多,例如在做一个关联查询的时候,扫描的行数和对返回的行数的比率通常都很小,一般在1:1和10:1之间,不过有时候这个值也可能非常大。
3. 扫描的行数和访问类型
在评估查询开销的时候,需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。 这些访问方式可能需要访问很多行才能返回一条结果,也有些访问方式可能无需扫描就能返回结果。
在执行计划EXPLAIN语句中的type列反映了访问类型。 访问类型有很多种,从全表扫描到索引扫描,范围扫描,唯一索引,常数索引等。 这里列的这些,速度是从慢到快,扫描的行数也是从多到少。
如果查询没有办法找到合适的访问类型,那么解决的最好办法通常就是增加一个合适的索引,这也是我们之前讨论索引的问题。 现在应该明白为什么索引对于查询优化如此重要了。 索引让MySQL以最高效,扫描行数最少的方式找到需要的记录 。
如果发现查询扫描了大量的数据但只返回少数的行,通常可以尝试下面的技巧去优化它:
- 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无需回表获取对应的行就可以返回结果了。
- 优化表结构。 例如使用单独的汇总表来完成查询。
- 重写复杂查询,让MySQL优化器能够以更优化的方式执行这个查询。