SQL 语句优化

JOIN优化

MySQL中join的实现采用的是Nested Loop Join算法。通过驱动表的结果集作为循环基础数据，然后将该结果集中的数据作为过滤

条件一条条的到下一个表中查询数据，最后合并结果，如果存在后续表，过程也如此。

1. 尽量减少join语句中nested loop循环的次数。最有效的方法是让驱动表的结果集尽可能的小
2. 优先优化nested loop中的内层循环
3. 保证join语句中被驱动表的join字段已经被索引
4. 如果无法满足3，且内层资源充足时，合理设置join buffer（join_buffer_size参数）

ORDER BY 优化

ORDER BY的实现有2种，一种是通过有序索引直接取得有序的数据；另外一种则须通过MySQL排序算法将存储引擎返回的数据进行

排序后，在返回给用户。

利用索引实现排序是MySQL中实现结果集排序的最佳方法，所以在优化ORDER BY时，尽可能的利用已有的索引来避免实际的排序，

甚至可以增加索引字段。

当没有索引可用时，MySQL有以下的方法来完成排序：

1. 取出满足过滤条件的作为排序条件的字段，以及可以定位到行数据的行指针信息，在sort buffer中进行实际的排序，然后利用排序好的数据根据行指针信息返回表中取得客户端其他的请求字段数据
2. 根据过滤条件一次取出所有满足的客户端请求字段以及排序字段，并将不需要排序的字段放在一块内存区域内，然后再sort buffer中将排序字段进行排序，最后在和其他在sort buffer中字段组合返回给用户

相对来说，方法2可以减少IO，但是需要更多的内存空间。

优化选择：

1. 加大max_length_for_sort_data值。因为MySQL根据这个值来决定是否使用方法2.当返回给用户的字段的总长度小于该值是可以使用方法2.所以在内存空间充足时，增大该值可以使排序方法2执行，从而改善IO
2. 去掉不必要的返回字段
3. 增大sort_buffer_size的大小。该值过小的话，会对数据分段的排序。

 

GROUP BY 优化

MySQL中GROUP BY的实现有3中形式，前2中使用索引信息来GROUP BY，最后一种则使用于完全无法使用索引的场景。

使用松散（LOOSE）索引扫描实现GROUP BY

其本意在于当MySQL完全利用索引扫描来实现GROUP bY时，并不需要扫描所有满足条件的索引项即可完成操作。在执行计划的Extra项可以输出信息“Using index for group-by”

。这种实现是最高效的，因为条件过滤的输出即是需要的结果。要利用该方法需满足以下条件：

1. GROUP BY条件字段必须处于同一索引的最前面的连续字段（或是存在的单列索引，当该条件仅为一列时）
2. 在使用了GROUP BY的同时，只能使用MAX和MIN这2个聚合函数
3. 如果引用到了该索引中除了GROUP BY条件之外的字段，则该字段必须为const。

使用紧凑索引扫描实现GROUP BY

它与前者的区别是需要读取所有满足条件的索引键，然后再根据读到的数据来完成GROUP BY。

 

使用临时表实现GROUP BY