常用SQL的优化

• 优化INSERT语句
  • 改写所有insert into为insert delayed into
    • delayed的含义是让insert语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多。
  • 如果同时从同一客户插入很多行，应尽量使用多个值表的INSERT语句，这种方式大大缩减客户端与数据库之间的连接、关闭等消耗。
• 优化ORDER BY语句
  • MySQL的两种排序方式
    • 1.通过有序索引顺序扫描直接返回数据。
    • 2.通过对返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort不代表通过磁盘文件排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以同一时刻，MySQL中存在多个sort buffer排序区。
  • 优化方式
    • 尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。
    • Filesort的优化
      • 两次扫描算法：首先根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序。如果排序区sort buffer不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录。效率较低，但是排序的时候内存开销较少。
      • 一次扫描算法：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。
      • 注意：尽量只使用必要的字段，SELECT具体的字段名称，而不是SELECT * 选择所有字段，这样可以减少排序区的使用，提高SQL性能。
• 优化group by语句
  • 默认情况下，MySQL对所有group by col1,col2....的字段进行排序。这与在查询中指定order by col1,col2...类似。因此，如果显示包括一个包含相同列的order by 子句，则对MySQL的实际执行性能没有什么影响。如果查询包括group by但用户想要避免排序结果的消耗，则可以指定order by null禁止排序。
• 优化嵌套查询
  • 使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。能有连接查询尽量用连接查询。连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
• MySQL优化OR条件
  • 对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；如果没有索引，应考虑增加索引。
    • eg:设sales2上有三个索引，在id和year两个字段上分别有1个独立的索引，在company_id和year字段上有1个复合索引。在两个独立索引上做OR操作，explain select * from sales2 where id=2 or year =1998 可以发现查询正确地用到了索引，并且从执行计划的描述中，可以知道在处理含有or字句的查询时，实际上是对or的各个字段分别查询后的结果进行了union操作。但是当在建有复合索引的列company_id和moneys上面做or操作时，却不能用到索引。
• 优化分页查询
  • limit 1000,20,此时MySQL排序出前1020条记录后仅仅需要返回第1001到1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。
  • 在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。
    • eg:原：select film_id,description from film order by title limit 50,5 新：select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id =b.film_id