mysql学习笔记（九）- 增删改查的优化

一、大批量插入数据

当使用load命令导入数据的时候，可以适当的提高导入的速度。对于myisam存储引擎的表可以通过下述方法快速的导入大量的数据。

Alter table tablename disable keys;
Loading date
Alter table tabename enable keys;

其中disable keys和enable keys用来打开或者关闭myisam表非唯一索引的更新。在导入大量的数据到一个非空的myisam表的时候，通过设置这两个命令可以提高导入的效率。对于当如大量的数据到一个空的myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。通过对比发现其有六倍的速率变化。

Load data infile  ‘/home/data/file.sql’ into table film;
Alter table file disable keys;
Load data infile ‘/home/data/file.sql’ into table film;
Alter table file enable keys;

上边是对myisam表进行数据导入时的优化方法，对于innodb类型的表，这种方式不能提高导入数据的效率，但是可以通过下面几种方式提高innodb表的导入效率。

（1）因为innodb类型的表是按照主键的顺序保存的，所以将要导入的表的数据按照主键的顺序排列，可以有效的提高导入数据的效率。类如text3.sql是按照表的主键顺序存储的，那么导入共耗时27.8秒。而没有任何顺序的文本存储居然要耗时31.16秒。

（2）在导入数据前执行set unique_check=0，关闭唯一性校验，在导入结束后执行set unique=1，恢复唯一性校验，可以提高导入的效率。

（3）如果应用使用自动提交的方式，建议在导入前执行set autocommit=0，关闭自动提交，导入结束之后再执行set autocommit=1，打开自动提交，这样也可以提高导入的效率。

二、优化insert语句

当进行数据的insert的时候，可以考虑采用以下几种优化方式。

如果同时从同一客户插入很多行，应该尽量使用多个值表的insert语句。这种方式将大大缩减客户端与数据库之间的关联，关闭等的消耗。使得效率比分开执行单个insert语句要快。

Insert into test value(1,2),(2,3),(3,4),....

如果从不同的客户端插入很多行，可以通过insert delayed语句得到更高的速度，delayed的含义是让insert语句马上执行，其实数据被放到内存的队列中，并没有真正的写入磁盘，这比每条语句分别插入要快的多；low priority刚好相反，在所有用户对表的读写完成之后才进行插入。

将索引文件和数据文件分在不同的磁盘上存储

如果进行批量插入，可以通过bulk_insert_buffer_size变量值的方法来提高速度，但是这只能对myisam表使用。

当从一个文本文件装载到一个表时，使用load data infile。这通常比使用很多insert语句快20倍。

三、优化order by语句

大家都知道order by在某些情况下order by是不支持排序的，但是如果将待排序的字段设置成为索引，而且该索引还有顺序，那么就可以在排序的时候快速一些。在mysql中将不能直接通过索引排序，而是要通过返回值进行排序操作的排序叫做filesort排序，filesort并不代表通过磁盘文件进行排序，而只是说进行了排序操作，至于排序操作是否使用磁盘文件或者零时表等，择取决于mysql服务器对排序参数的设置和需要排序的数据大小。例如，按照商店store_id排序返回所有客户记录时，出现了对全表扫描的结果排序。

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

在我们了解了mysql排序方式下，优化的目标就清晰了，尽量减少额外的排序，通过索引直接返回有序数据，where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。否则肯定会有额外的排序操作，就会出现filesort。

Filesort的优化

通过创建合适的索引能够减少filesort的出现，但是在某些情况下，条件限制不能出现让filesort消失，那就需要想办法加快filesort的操作，对于filesort来说，mysql有两种排序算法。分别为两次扫描算法和一次扫描算法。两次扫描首先通过条件取出排序字段和行指针信息，然后在sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果，完成排序之后根据行指针回表读取记录。因为需要访问两次数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量的io操作，优点是排序的时候内存开销较小。一次扫描是一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集，排序的时候内存开销比较大，但是排序的效率要比两次扫描高。

Mysql通过比较系统变量max_length_for_sort_data的大小和Query语句取出字段总大小判断使用哪种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法，否则采用第一种。

所以适当的修改max_length_for_data的值能够让mysql选择更优化的filesort排序算法。当然，假如max_length_for_data设置的过大，会造成mysql利用率过低和磁盘IO过高，所以平衡很重要。

适当的加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建零时表放到文件中进行，当然也不能无限加到sort_buffer_size排序区，因为sort_buffer_size参数是每个线程独占的，设置的过高。也会导致服务器swap严重，要考虑数据库活动连接数和服务器内存的大小来适当的设置。

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