mysql常见的几个SQL性能考虑

最近项目碰到了一些多表联查的需求,发现mysql基础统统忘光了,现在补一补几个核心概念 :mrgreen:

1,join的原理,

  • mysql处理时,采取小表驱动大表的方式,关联了几张表就相当于嵌套几个for循环,不需要临时表。
  • mysql会自己做查询语句计划,不需要我们关心where的顺序和table的顺序。
  • mysql做查询计划时,会对每张表按非关联字段进行条件筛选后估算最小的集合作为小表。
  • 如果要对join结果排序,那么只有小表有索引才有加速效果,否则都是对最终临时表排序。

2,子查询的原理,

  • 子查询会产生临时表,可能是父查询,也可能是子查询。
  • 通常是外查询出全量结果后,将结果逐个拼接到子查询上进行再次查询,而不是我们认为的先得到子查询结果集再放到父查询中执行!不要用in语句做子查询,因为一定会命中这个悲剧的原理,但exists除外。
  • 不要用in,而是要使用join取代,但是临时表没有索引,所以还是尽量避免子查询。

3,临时表,

  • 如果查询产生临时表,会优先放到内存里(innodb_buffer_size有关),过大会dump到磁盘进一步影响性能。
  • 如果临时表不可避免的话,那么不要在临时表里包含大字段,因为将占用内存空间。可以在SQL末尾再进行一次关联将大字段带入。

4,索引生效,

  • SQL解析的优先级:where > group by > having > order by。mysql会先按where筛出一批记录,然后按group by进行分组,然后对所有组按having进行过滤丢掉不满足条件的组,最后对剩余结果进行排序(至于order by是否可以走索引,因素很多,可以见下面的链接)。

 

理解索引原理和mysql处理sql语句的思路,有助于分析和改善sql性能。

 

详细学习原理见下面的链接:

Join原理:http://www.cnblogs.com/ggjucheng/archive/2012/11/15/2772148.html

小表驱动大表:http://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html

子查询原理:http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html

索引生效原理:http://www.9usb.net/201003/mysql-suoyin-youhua-order-by.html

发表评论

电子邮件地址不会被公开。