menu

Junjielee Blog

在平凡中坚持前行,总会遇见不凡的自己

Mysql查询性能优化技巧

了解查询执行的过程

客户端 -> 半双工通信协议 -> 服务器 -> 查询缓存 -> 解析器 -> 预处理 -> 查询优化器 -> 执行计划 -> 执行引擎 -> 调用存储引擎API接口 -> 返回结果

这里说到半双工通信协议,意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。

其中返回结果:是一个增量、逐步的返回过程,只要服务端查询第一条数据了,就开始返回

慢查询

可能会引起慢查询的案列:

  • 查询不需要的记录
  • 多表关联时返回全部列
  • 总是取出全部列
  • 重复查询相同的数据

查询优化处理的基础

优化之前会执行语法解析、检查以及预处理,然后到进入优化器

优化策略分两种:

  • 静态优化:直接从解析树上获取查询信息,进行一些等值查询转换,不考虑WHERE任何数值和行数据信息。
  • 动态优化:指的是查询和实际数值分布、索引中条目对应的数据行等上下文有关。

下面是一些MySQL能处理的优化类型:

  • 重新定义关联表的顺序
  • 将外连接转化成内连接
  • 使用等价转换规则
  • 优化COUNT()、MIN()和MAX()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表IN()的比较

手动优化查询

优化COUNT()

给COUNT指定列,如果列为NULL,则不会统计在内,当统计的col确定是唯一的话,可以使用COUNT(*)代替

例如查询ID大于等于500的记录数,可以这样写:

SELECT (SELECT COUNT(*) FROM users) - COUNT(*) FROM users id<500;

优化关联查询

  • 确保ON或者USING子句中的列上有索引
  • 确保任何GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列
  • 注意MySQL升级的影响

优化子查询

根据实际情况,时候关联查询还是拆分为多个查询进行

优化LIMIT分页

可以使用延迟关联的方法,让MySQL扫描尽可能少的页面,获取需要访问的记录后在根据关联列回原表查询所有的列。

在LIMIT OFFSET的时候,可以通过使用id值来抛弃行,先查询得到起始id值,然后limit,记录最后的id值,然后下一页也可以通过id来查询了

优化UNION查询

除非确实需要服务器消除重复的行,否则就一定要使用UNION ALL

参考

《高性能MySQL》