1、优化SQL语句的一般步骤
（1）通过SHOW STATUS命令了解各种SQL的执行效率。
（2）定位执行效率较低的SQL（慢查询日志）。
（3）通过EXPLAIN分析低效SQL的执行计划。
（4）通过SHOW PROFILE分析SQL。
（5）通过TRACE分析优化器如何选择执行计划。
（6）确定问题并采取相应的优化措施。
2、常用SQL的优化
2.1、使用LOAD命令导入数据
导入大量的数据到非空的MyISAM表时 ，导入前执行“ALTER TABLE tb1_name DISABLE KEYS;”，禁用索引，导入后执行“ALTER TABLE tb1_name ENABLE KEYS;”，开启索引，可以提高导入的效率。
导入大量的数据到InnoDB表时，可以有以下几种方式提高导入效率。
（1）因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
（2）在导入数据前执行“SET UNIQUE_CHECKS=0”，关闭唯一性校验，在导入结束后执行“SET UNIQUE_CHECKS=1”，恢复唯一性校验，可以提高导入的效率。
（3）如果应用使用自动提交的方式，建议在导入前执行“SET AUTOCOMMIT=0”，关闭自动提交，导入结束后再执行“SET AUTOCOMMIT=1”，打开自动提交，也可以提高导入的效率。
2.2、优化INSERT语句
（1）如果同时从同一客户插入很多行，尽量使用多个值的INSERT语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个INSERT语句快。
（2）如果从不同客户插入很多行，可以通过使用INSERT DELAYED语句得到更高的速度。 DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。
（3）如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是这只能对MyISAM表使用。
（4）将索引文件和数据文件分在不同的磁盘上存放。
（5）当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快 20 倍。
2.3、优化ORDER BY语句
（1）尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者降序。
（2）适当加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的Filesort排序算法。当然，假如max_length_for_sort_data设置过大，会造成CPU利用率过低和磁盘I/O过高，CPU和I/O利用平衡就足够了。
（3）适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_size参数是每个线程独占的，设置过大，会导致服务器SWAP严重，要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。
（4）尽量只使用必要的字段，SELECT具体的字段名称，而不是SELECT *选择所有字段，这样可以减少排序区的使用，提高SQL性能。
2.4、优化GROUP BY语句
MySQL在默认情况下会对所有GROUP BY col1, col2, ...的字段进行排序。如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
2.5、优化嵌套查询
嵌套查询在某些情况下可以被更有效率的连接（JOIN）替代。
2.6、优化OR条件
对于含有OR的查询语句，如果要利用索引，则OR之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。
2.7、优化分页查询
（1）在索引上完成排序分页的操作，然后根据主键关联回原表查询所需要的其他列内容，例：“select a.field1, a.field2 from table a inner join (select field1 from table order by field3 limit 50, 5) b on a.field1 = b.field1”。
（2）把LIMIT查询转换成某个位置的查询，例：“select field1, field2 from table where field1 > 10000 order by field1 limit 10”。
2.8、使用SQL提示
（1）在查询语句中表名的后面，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
（2）如果用户只是单纯地想让MySQL忽略一个或者多个索引，则可以使用IGNORE INDEX作为HINT。
（3）为强制MySQL使用一个特定的索引，可以在查询中使用FORCE INDEX作为HINT。
网友评论