SQL优化指南

2018-12-06 13:23

SQL优化指南



  开启了MySQL慢查询日志之后,MySQL会自动将执行时间超过指定秒数的SQL统统记录下来,这对于搜罗线上慢SQL有很大的帮助。

SQL优化指南

SQL优化指南

  客户端可以用set设置变量的方式让慢查询开启,但是个人不推荐,因为真实操作起来会有一些问题,比如说,重启MySQL后就失效了,或者是开启了慢查询,我又去改变量值,它就不生效了。

  此时发现sql已经被记录到日志里了。(有时候不一定,我看到很多博客讲的是超过指定秒数,但我实验得出的结果是达到指定秒数)

SQL优化指南

SQL优化指南

  id:代表优先级 id值越大,越先执行,id值相同,从上往下执行。(比如示例的这条sql的执行计划,就是先执行第一行,再执行第二行)

  subquery 一般子查询中的子查询被标记为subquery,也就是位于select列表中的查询

  type:表示MySQL在表中查找数据的方式,或者叫访问类型,以下对于type取值的说明 从上往下性能由最差到最好

  eq_ref:类似ref,区别就在于使用的索引是唯一索引,简单来说,就是多表连接中使用primary key或者unique index作为关联条件。

  const/system:单表中最多有一个匹配行,查询起来非常迅速,常见于根据primary key或者唯一索引unique index进行的单表查询

  Extra:执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息,常用取值如下:

  Using where:直接在主键索引上过滤数据,必带where子句,而且用不上索引

  Using filesort:使用了外部文件排序 只要见到这个 就要优化掉

  Using temporary:创建了临时表来处理查询 只要见到这个 也要尽量优化掉

  COUNT()是一个特殊的函数,有两种不同的作用,它可以统计某个列值的数量,也可以统计行数。

  当我们统计行的时候,常见的是COUNT(*),这种情况下,通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数

  这是一个容易产生误解的事情:MyiSAM的count()函数总是非常快。

  不过它是有前提条件的,条件是没有任何where条件的count(*)才非常快,因为此时无须实际的去计算表的行数,mysql可以利用存储引擎的特性直接获得这个值,如果mysql知道某列不可能有null值,那么mysql内部会将count(列)表达式优化为count(*)。

  当统计带有where条件的查询,那么mysql的count()和其他存储引擎就没有什么不同了。

  1.第一种通过有序索引返回数据,这种方式的extra显示为Using Index,不需要额外的排序,操作效率较高。

  2.第二种是对返回的数据进行排序,也就是通常看到的Using filesort,filesort是通过相应的排序算法,将数据放在sort_buffer_size系统变量设置的内存排序区中进行排序,如果内存装载不下,它就会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集。

  了解了MySQL排序的方式,优化目标就清晰了:尽量减少额外的排序,通过索引直接返回有序数据。where条件和order by使用相同的索引。

  2.查询时尽量只使用必要的字段,select 具体字段的名称,而不是select * 选择所有字段,这样可以减少排序区的使用,提高SQL性能。

  事实上,MySQL在所有的group by 后面隐式的加了order by ,也就是说group by语句的结果会默认进行排序。

  既然知道问题了,那么就容易优化了,如果查询包括group by但又不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以指定order by null 禁止排序。

  这时MySQL需要查询1020条记录然后只返回最后20条,前面的1000条都将被抛弃,这样的代价非常高。如果所有页面的访问频率都相同,那么这样的查询平均需要访问半个表的数据。

  子查询,也就是查询中有查询,常见的是where后面跟一个括号里面又是一条查询sql

  当然 这不是绝对的,比如某些非常简单的子查询就比关联查询效率高,事实效果如何还要看执行计划。

  use index 可以让MySQL去参考指定的索引,但是无法强制MySQL去使用这个索引,当MySQL觉得这个索引效率太差,它宁愿去走全表扫描。。。