MySql中insert,group by,order by语句优化

我们在MySql中经常进行数据的增删改查操作，这里和大家一起分享一些常用的sql优化技巧。

首先我们来说一下ORDER BY的优化。

ORDER BY 是我们在sql中进行排序的语句，首先我们使用ORDER BY 语句的时候，如果可以按照有序索引顺序扫描来返回有序数据的话，那么这种方式是最快的，比如说我们有一张表A，其主键为ID，ID自增，并且存在主键索引，那么我们进行如下查询：select * from A ORDER BY ID,那么语句将会使用有序的主键索引返回数据。

上面的情况是可以按照有序索引直接返回排序后返回数据的情况，另外一种情况是对返回的数据进行排序，也就是说需要查找到数据，然后进行排序，也就是我们说的filesort排序。

filesort排序是将取得的数据在系统变量sort_buffer_size设置的内存区域内对数据进行排序，如果数据量太大，超过了设置的内存区域，那么会将磁盘上的数据进行分块，各自进行排序，排序以后再整体排序，合并成有序集。但是sort_buffer_size设置的排序区域是各个线程私有的，也就是说MySql可能同时存在多个排序区。

为了更好的说明filesort，这里我们解释一下MySql的两种排序算法。

第一种排序算法是两次扫描算法：首先我们根据条件取出排序字段，以及各个数据行的行指针，然后在排序区中根据排序字段进行排序，排序完成后根据行指针再一次回到表中取出select中的字段，如果排序过程中，排序区不够用，就将数据存储在临时表中，这个算法需要两次访问数据表，第一次是取回排序字段和行指针，第二次是根据行指针取回查找的字段，优点是在排序的时候消耗的空间少，可以充分利用排序区，但是缺点是第二次访问可能会导致大量的IO操作。

第二种算法是一次扫描算法：首先根据条件一次性取出所有select字段，然后根据排序字段进行排序，缺点是对排序区内存消耗较大，但是减少了一次表访问，这种算法排序效率要高于第一种算法。

在MySql的排序中通过系统变量max_length_for_sort_data来标记排序区大小，在排序的时候通过比较该系统变量与Select语句查找返回的数据进行比较，如果Select语句返回的数据量更大，则使用第一种算法，否则使用第二种算法。所以适当的加大内存排序区有利于MySql选择更加有利的filesort算法。

其次在优化排序时，应该尽量的减少额外的排序，如果可以通过索引直接返回有序的数据，那么就使用索引，同事应该注意where条件和ORDER BY应该使用相同的索引，如果索引条件不同，则会产生filesort排序，而且需要保证ORDER BY的顺序和索引的顺序一致，ORDER BY的字段需要全部都是升序或者降序，否则一定会产生filesort。

然后我们来说一下group by语句的优化，是这样子的，MySql在使用GROUP BY语句的时候，会默认根据group by A,B,C....中的分组字段进行排序，所以当我们使用group by A,B,C order by A,B,C时，其实与使用group by A,B,C性能上没有什么差异。如果我们想减少对排序的性能消耗，可以直接指定order by null 来禁止排序，就可以对group by语句进行优化了。

对于insert语句的优化有以下几点：

1.如果我们有多条insert语句对同一个表的相同字段进行插入，则建议整合为一个insert语句，例如insert into test values (1,2),(3,4),(5,6),因为这样可以减少数据库的链接，关闭操作的消耗，使得效率提高。

2.如果我们的多条语句来自不同的客户端，那么可以使用insert delayed 语句提高速度，其实我们在进行插入的时候，数据都存在了内存当中，这条语句的意思就是让insert立刻执行，所以就可以不需要等待其他语句，减少了等待时间。

3.在建表时，可以指定数据文件和索引文件分开存放。

4.当我们需要从文件中导入数据时，使用load data infile要比直接使用insert快20倍左右。

5.对于MyISAM表来说，可以通过增大bulk_insert_buffer_size变量值的方法来提高速度。