1.优化数据访问
查询常见问题:向数据库请求了不需要的数据
查询不需要的记录,没有加上limit导致返回大量结果集。 select*返回全部列,尤其是多表关联时。 重复查询相同数据,没有采用缓存。
2. 衡量查询开销
1. 衡量查询开销的三个指标
响应时间:服务时间(数据库处理该查询的时间)+排队时间(等待I/O以及等待锁的时间)。 扫描的行数以及返回的行数:它们两个的比例越接近1:1就越好,能够说明查询效率问题。 扫描的返回类型:Explain语句的type类型反映了返回类型,从好到坏分别为全表扫描,索引扫描,范围扫描,唯一索引查询,常数引用等,扫描速度从慢到块。
2. Explain的使用
如下图,因为定义了了索引idx_fk_film_id,所以MYSQL在该索引上使用了ref访问类型,扫描行数为10行。换句话说,这是一次高效的查询。
如果删除索引,访问类型会变成一个全表扫描(ALL),预估需要扫描5037条记录,Using Where表示使用where筛选存储引擎返回的记录。
对于WHERE条件,MYSQL的扫描条件从好到坏依次为:
速度最快的,利用索引,在索引中利用WHERE条件过滤掉不匹配的记录,这是存储引擎层完成的(Explain的Extra为空)。 稍慢的,使用索引覆盖扫描(Explain的Extra出现Using index),直接从索引中过滤掉不需要的记录并返回命中的结果。这是MYSQL服务器层完成的,无须再回表查询记录。 最慢的,从数据表中返回全部数据,然后过滤掉不必要的记录(Explain的Extra出现Using Where)。这也是MYSQL服务器层完成的。
2. 优化技巧
使用索引覆盖扫描,把所有查询需要用的列都放在索引中。 改变库表结构,例如使用单独的汇总表 重写复杂的查询,让MYSQL优化器能以更优化的方式查询。
3. 重构查询
1. 切分查询
将大的查询拆分成小的查询,每个查询功能完全一样,每次只返回一部分的查询结果。 例如,删除大量数据的时候,如果一次性删除的话,可能会一次锁住很多数据、占满事务日志、消耗资源。将一个大的DELETE语句拆成小的可能尽可能减少对性能的影响,如下图:
切分查询,一次删除一万行数据一般是比较高效的。
2. 分解关联查询
将连接查询分解成单表查询,如下图:
这样做的优势如下:
更好的利用MYSQL的查询缓存。 将查询分解后,执行单个查询可以减少锁竞争。 单表查询需要在应用层关联各个表的数据,因此更容易对数据库进行分库分表。 减少冗余数据的查询。
4. 查询执行的基本原理
1.查询流程
客户端发送一条查询给服务器。 服务器先检查缓存,如果缓存命中,则立刻返回缓存中的结果,否则进入下一阶段。 服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划。 MYSQL根据优化器生成的执行计划,调用存储引擎的API来查询。 将结果返回给客户端。