参考文献《高性能MySQL(第三版)》
上一期我们深入理解了MySQL的索引,有了MySQL索引可以一定程度上提高MySQL的查询速度。这一期我们来学习下MySQL查询性能优化的一般方法。
1 为什么查询速度会慢?
其实很简单,在一个应用程序中,我们要查询一些数据,通常是从客户端出发,请求经过网络传输到达服务端后,在服务端进行解析,然后把查询命令发送给MySQL,MySQL经过一系列解析、优化等,最终将结果查询出来,返回给客户端,最终给到我们用户。
在这一系列操作里,由于网络时延、CPU、内存、锁竞争、系统调用、上下文切换、存储引擎检索数据触发的一系列操作等,查询的速度或多或少会受到一定的影响,条件不利时,查询速度就会变慢。
大致地知道查询速度受到这些因素影响后,我们就可以找到优化查询速度的一些方向了。
2 慢查询基础:优化数据访问
查询性能低的最基本原因是访问的数据太多,我们可以通过以下两步来分析低效查询:
- 确认应用程序是否在检索大量超过需要的数据行
- 确认MySQL服务器层是否在分析大量超过需要的数据行
2.1 是否向数据库请求了不需要的数据
应用程序从MySQL查询请求了较多不需要数据时,这些多余数据其实会在应用程序的逻辑层中被丢弃掉,这种多余的操作会给MySQL服务器带来额外的负担,并增加网络开销,还会消耗应用服务器的CPU和内存资源。以下几种情况均是这种类型:
- 查询了不需要的记录。像应用程序select * 时其实并不需要全部的数据,却没有去加limit进行限制,从而把全表的数据都捞出来,显然多此一举。
- 多表关联时返回全部列。多个表进行关联,像一些没有什么意义的列也全部查出来,也是会影响性能。
- 总是取出全部的列。像select * 这类查询,取出全部列,不一定会完成索引覆盖这类优化,会触发各种回表查询,为服务器带来额外的IO、内存和CPU消耗。
- 重复查询相同的数据。对于每次查询都返回同样结果的这类查询,其实查一遍就够了,把结果存到Redis这类缓存中,减轻MySQL的压力。
2.2 MySQL是否扫描了额外的记录
对于MySQL,衡量性能开销的三个指标是:响应时间、扫描行数、返回行数。
响应时间
扫描的行数与返回的行数
理想情况下,扫描的行与返回的行之间的比率通常要小,MySQL额外扫描的记录就少。
扫描的行数与访问类型
在评估查询开销时,需要考虑下从表中找到某一行数据的成本。MySQL有些访问方式可能要扫描很多行才能返回一行结果。
使用explain语句中的type列反应了访问类型。 访问类型有索引扫描、范围扫描、唯一索引查询、常数引用等。
一般MySQL能使用这下列三种方式应用where条件,从好到坏依次为:
- 在索引中使用where条件来过滤不匹配的记录,这是在存储引擎完成的