MySQL——常用SQL优化（二）_mysql一次扫描算法与两次扫描算法-优快云博客

一、优化insert语句。

当进行数据插入，insert语句的优化，可以考虑以下方式。

（1）、如果同时从同一客户插入很多行数据，应尽量使用多个值表的insert语句，这种方式将大大缩减客户端和数据库服务器之间的连接，关闭等消耗。使得效率比使用单个insert语句要快好几倍，下面是例子。

insert into table values(1,1'),(2,'2'),(3,'3'),....,(n,'n');

（2）、如果从不同的客户插入很多行，可以通过使用INSERT DELAYED 语句得到更高的速度，DELAYED的含义是让INSERT 语句立马执行，其实数据都被放到内存的队列中，并没有真正写入磁盘，这比每条记录分别插入要快得多;LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完成后才进行插入数据。

（3）、将索引文件和数据文件分在不同的磁盘上进行存放（利用建表中的选项）

（4）、如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值得方法来提高速度。但是，这只能对MyISAM表有效。

（5）、当从一个文本文件装载一个表时，使用LOAD DATA INFILE 。这比使用insert语句要快20倍。

二、优化ORDER BY语句。

优化ORDER BY前，首先需要了解MySQL的排序方式。

使用命令：show index from tablename \G 查看表tablename的排序方式。

1、MySQL中有两种排序方式。

第一种方式：通过有序索引顺序扫描直接返回有序数据，这种方式在使用EXPLAIN方式查看SQL的执行计划时显示为USING INDEX ,不需要额外的排序，操作效率更高。例如：

第二种方式：通过对放回数据进行排序，也就是Filesort排序，所有不是通过索引排序直接返回排序结果都是Filesort排序，Filesort并不代表通过磁盘文件进行排序。而是说明了一个排序操作，至于排序操作是否使用磁盘文件或者临时表等。取决于MySQL服务器对排序参数的设置和需要排序数据的大小。

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不了，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个快合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独立占有的，所以同一时刻，MySQL中存在多个sort buffer排序区。

了解MySQL的排序后，优化就清晰了：尽量减少额外的排序，通过索引直接返回有序结果。where条件和order by使用相同的索引，并且order by 的顺序和索引的顺序相同，同时order by的顺序都是升序或者降序。否则肯定需要额外的排序操作，这样就会出现Filesort排序。

2、Filesort排序的优化。

通过创建合适的索引能够减少Filesort的出现，但是在某些特殊条件下，条件限制不能减少Filesort的消失，那就需要考虑如果让Filesort更快。对于Filesort排序，MySQL有两种排序算法。

a、两次扫描算法：首先需要根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序，如果排序区sort Buffer中不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录，该算法是在MySQL4.1之前使用的，需要两次访问数据。第一次获得排序字段和行指针信息，第二次根据行指针记录获取数据，尤其第二次的读取数据，有可能导致大量的I/O操作；优点是排序的时候，内存开销小。

b、一次扫描算法：一次性读取满足条件的行的所有字段，然后再排序区sort Buffer中直接排序，输出结果集，排序的时候内存开销大。排序效率比两次扫描的要高。

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小来判断使用那种算法。如果max_length_for_sort_data大，使用两次扫算法，否则使用一次扫描算法。

适当加大系统变量max_length_for_sort_data的值，能够让MySQL选择Filesort排序算法。当然，如果max_length_for_sort_data设置过大，会造成CPU的利用率较低和磁盘I/O过高，CPU和I/O平衡就好。

适当加大sort_Buffer_size的值，尽量让排序在内存中完成。而不是通过创建临时表放到文件中进行排序。当然了，sort_Buffer_size的值也不能过大，这是每个线程独立占有的，会导致服务器SWAP严重。

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