MYSQL的常用SQL的优化

1、大批量插入数据

• 针对MyISAM表
  alter table tb1_name DISABLE KEYS;
  loading the data
  alter table tb2_name ENABLE KEYS;
  上述两个句子主要用来打开或者关闭MyISAM表非唯一索引的更新，在导入大量的数据岛一个非空的MyISAM表时，通过这样的设置可以提高导入的效率，对于空的没有影响，因为默认就是先导入输然后才创建索引的。
• 针对InnoDB表
  （1）导入的数据按照主键的顺序排列，因为InnoDB类型的表是按照主键的顺序保存的。
  （2）在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性检验，在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性检验。
  （3）关闭自动提交，SET AUTOCOMMIT=0，导入结束后再执行SET AUTOCOMMIT=1打开自动提交。

2、优化Insert语句

• 如果从同一客户插入很多很多行时，尽量使用多个值表的插入语句insert into test values(1,2),(1,3)…，因为这种方式大大缩减了客户端与数据库之间的连接、关闭等消耗。
• 如果从不同客户插入很多行，可以通过使用INSERT DELAYED语句得到更高的速度，因为可以让INSERT语句马上执行。
• 将索引文件和数据文件分在不同的磁盘上存放。
• 批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度，但仅限于MyISAM表使用。
• 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。

3、两种排序方式

• 有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析查询的时候显示为Using index，不需要额外的排序，操作效率较高。

• 通过对返回数据进行排序，即Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。

  由于索引的排序比Filesort排序更好，所以尽量减少额外的排序，通过索引直接返回有序数据。因此尽量保持三个条件：（1）WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序，否则肯定需要额外的排序操作。
  相对应的SQL与为：
  select * from table order by key_part1,key_part2,…;
  select * from table where key_part1=1 order by key_part1 desc,key_part2 desc;
  select * from table order by key_part1 desc,key_part2 desc;
  当没办法避免需要Filesort时，可以做以下优化：
  适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行，不能太大，否则会导致服务器SWWAP严重；尽量只使用必要的字段，少用select * 所有字段，这样可以减少排序区的使用，提高SQL性能。

3、优化GROUP BY语句
如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
4、优化OR条件
对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；
5、优化分页查询

• 在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。
• 把LIMIT查询转换成某个位置的查询。把LIMIT m,n转换成LIMIT n的查询，但只适合在排序字段不会出现重复值的特定环境。

6、使用SQL提示

• USE INDEX提供参考的索引列表，不再考虑其他可用的索引。
• IGNORE INDEX忽略一个或者多个索引。
• FORCE INDEX强制使用一个特定的索引。