1、Join语句优化
在MySQL中，支持的Join算法只有一种，那就是Nested Loop Join（嵌套循环链接）算法。简称NLJ算法。
NLJ算法实际上是驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后再合并。
NLJ算法可以分为一下三种算法，分别是
Simple Nested Loop Join（简单嵌套循环链接）算法，简称SNLJ算法，
Index Nested Loop Join（基于索引的循环链接）算法，简称INLJ算法，
Block Nested Loop Join（基于块的循环链接）算法，简称BNLJ算法。
（1）、SNLJ算法
SNLJ算法的逻辑很简单，就是先查出驱动表，在嵌套循环被驱动表。伪代码实现如下：
For each row r in R do -- 扫描R表（驱动表）
For each row s in S do -- 扫描S表（被驱动表）
If r and s satisfy the join condition -- 如果r和s满足join条件
Then output the tuple <r, s> -- 返回结果集
通过两个for循环可以看出，这样的算法是很慢的，如果RS表各自N条记录，那么if中比较的次数将会是N2次。按照时间复杂度来算是O(n2)次。
（2）、INLJ算法
SNLJ算法很简单粗暴，效率太低，所以建议在内表中建立索引，减少循环的次数。伪代码实现如下：
For each row r in R do -- 扫描R表
lookup s in S index -- 查询S表的索引（固定3~4次IO，B+树高度）
If find s == r -- 如果r匹配了索引s
Then output the tuple <r, s> -- 返回结果集
很明显这样的方式，要比SNLJ算法快很多。内表每次查询都可以使用索引，所以查询次数是3N次，按照时间复杂度来算是O(n)次。
所以继续在INLJ算法上优化是，会要求外表（驱动集）尽可能的小，这样可以有效的提高效率
但是需要注意这里的索引最好为聚集索引（主键索引），因为如果是非聚集索引（辅助索引），此时还需要一次回表的操作，效率又会降低。
在具体执行Query时，MySQL优化器会优先选择有索引的表做内表，如果都有索引，那么优化器会选择结果集少的做外部表。还是使用小结果集驱动大结果集的原则。
（3）、BNLJ算法
以上算法的执行过程是，将表的数据加载到内存中，然后再内存中进行比较。如果内存不够的情况下，则需要将之前加载到的数据放弃然后再加载新的数据进来比较，那么将会对表有很多次的IO访问。
为了减少对表的IO访问，提出BNLJ算法。
BNLJ算法提出，将驱动表中的记录加载到Block Buffer中，然后被驱动表中的数据可以一次性和Block Buffer中的多条数据进行比较。而不是驱动表中的数据取一条，被驱动器表里的数据就对比一次。这样有效的减少了对驱动表的IO访问，提高了效率。伪代码的实现如下：
For each tuple r in R do -- 扫描外表R
store used columns as p from R in Join Buffer -- 将部分或者全部R的记录保存到Join Buffer中，记为p
For each tuple s in S do -- 扫描内表S
If p and s satisfy the join condition -- p与s满足join条件
Then output the tuple -- 返回为结果集
所以在平时开发的时候可以尽量将Block Buffer的值设置大一些（参数名：join_buffer_size），增加存储，从而提升加载效率。
MySQL优化器在优化Join的时候，如果查询有索引，则选择INLJ算法，如果没有索引，默认是用BNLJ算法。
（4）、Join优化
用小结果集驱动大结果集
减少循环次数，减少对被驱动表的过滤次数
保证Join语句中被驱动表上的Join条件字段已经被索引
使用索引，可以减少被驱动表的IO次数
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬Join Buffer的设置
根据BNLJ算法可以知道，Join Buffer值设置的越大，对驱动表的IO访问越少。
2、Order By语句优化
Order by语句排序的时候会碰到两种情况：
（1）通过有序索引直接返回有序的数据
（2）获取的数据是无序的，需要MySQL再次进行排序
第一种情况在explain中不会出现排序。下面只讨论第二种情况。
MySQL的排序实现有两种：
（1）MySQL4.1以前版本，取出满足过滤条件的排序字段和对应行的指针，在Sort Buffer中排序完成之后，在根据指针拿出完整的一行的数据
（2）MySQL4.1之后的版本，取出满足过滤条件的排序字段和对应行的所有数据，然后在Sort Buffer中完成排序，将排序后的数据直接返回。
显然如果Sort Buffer的空间充足的话，第二种算法的速度会快很多，因为不需要再次回表。
所以根据以上的算法可以知道优化的基本方向：
1加大max_length_for_sort_data参数的值
MySQL中，如果我们返回的字段长度大于这个值，就会采用第一种算法，如果采用第二种算法，排序的过程中需要将数据分成好多段。得不偿失。如果返回的数据小于max_length_for_sort_data的值，那么优先选择第二种算法。
2去掉不需要的返回
返回不需要的数据，导致返回的字段太大，根据以上所述，MySQL会优先选择较慢的第一种算法。
3增大sort_buffer_size参数
增大sort_buffer_size的参数，可以是MySQL尽量减小在排序过程中对需要排序的数据进行分段。因为这样会不得不让MySQL使用零时表来进行交换排序。
3、Group By语句优化
MySQL中Group By的实现方式有个三种：
使用松散（Loose）索引扫描实现Group By
使用紧凑（Tight）索引扫描实现Group By
使用临时表实现Group By
（1）使用松散（Loose）索引扫描实现Group By
MySQL完全利用索引扫描来实现Group By的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
使用松散索引扫描的条件是：
1、Group By 条件字段必须在同一个索引中最前面的连续位置。
2、使用Group By的同时，只能使用max和min两个聚合参数
3、如果引用到了该索引中的Group By条件之外的字段条件时，必须以常量形式存在。
使用松散索引的效率很高，可以通过explain检查Query语句，如果Extra中显示using index for group-by，就是说明使用了松散索引扫描方式
（2）使用紧凑（Tight）索引扫描实现Group By
和松散索引扫描相比，主要的区别在与紧凑索引的扫描中读取满足条件的所有索引键。
使用这种方式可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有的关键字
（3）使用临时表实现Group By
在没有合适的索引使用的情况下，MySQL就不得不先读取需要的数据，然后通过临时表来完成Group By。
使用explain检测语句，Extra项中显示Using Temporary,Using FileSort
Group By优化选择
1、尽可能利用索引实现Group By，最好是松散索引扫描方式。
2、当是在无法使用索引的时候，必须使用Filesort方式，所有就需要将sort_buffer_size的大小调大。
3、小技巧：在整个语句之后追加 order by null。会小幅提升效率
4、Distinct语句优化
Distinct与Group By相似，Distinct是在group by之后的每组中取出一条记录。
同样Distinct也可以使用松散索引扫描和紧凑索引扫描。
Distinct与Group By的区别是Distinct不进行排序。
优化技巧同Group By
参考资料
1、《MySQL性能调优与架构设计》
2、https://www.cnblogs.com/zuochanzi/p/10409752.html
网友评论