常用SQL优化

1，大批量插入数据
alter table tab_name disable keys
load data infile
alter table tab_name enable keys
【注：disable keys 和 enable keys 用来打开或者关闭MyISAM表非唯一索引的更新，在导入大量的数据带到一个非空的MyISAM表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个非空的MyISAM表，默认是先导入数据然后创建索引的】
提高InnoDB表的导入效率：
1，因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键[非空，唯一]的顺序排列，可以有效地提高导入的效率
2，在导入数据前执行set unique_checks=0，关闭唯一性校验，再倒入结束后set unique_checks=1，恢复唯一性校验，可以提高导入效率
3，如果应用使用自动提交的方式，建议在导入之前执行set autocommit=0，关闭自动提交，导入结束后在执行set autocommit=1，打开自动提交，也可以提高导入的效率
2，优化insert语句
1，一条SQL语句插入多条sql语句
2，在事务中进行插入处理
注意事项：
1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packe配置可以修改，默认是1M。
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事务大小达到配置项数据级前进行事务提交。
3. DML[sql语句]语句可以采用事务处理，DDL[库/表]则不支持事务，实际上若对DDL[库/表]使用事务，只有最后一条真正执行。
3，优化order by语句(索引)
1，可以使用索引
SELECT * FROM t1 ORDER BY key_part1,key_part2,… ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
2，不可使用索引
①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
–order by的字段混合ASC和DESC
②SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
–用于查询行的关键字与ORDER BY中所使用的不相同
③SELECT * FROM t1 ORDER BY key1, key2；
–对不同的关键字使用ORDER BY：
4，优化group by语句
查询包含group by但用户想要避免排序结果的消耗，则可以指定order by null禁止排序
5，优化嵌套查询(子查询：使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的sql操作，同事也可以避免事务或者表锁死，并且写起来也很容易，但是，有些情况下，子查询可以被更有效率的(链接)join替代)
用join语句代替子查询【注：MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作】
6，优化分页查询
1，在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容
eg，
explain select id，name from user order by title limit 50,5；
改为
explain select a.id,a.name from user a inner join (select id from user order by title 50,5)b on a.id = b.id;
2，把limit查询转换为某个位置的查询
eg，
explain select * from payment order by rental_id desc limit 410,10;
改为
explain select * from payment where rental_id<15640 order by rental_id desc limit 10;
【注：这样把limit m,n转换成limit n的查询，只适合排序字段不会出现重复值的特定环境，能够减轻翻页的压力；如果排序字段出现大量重复值，而仍然进行这种优化，那么分页环境可能会丢失部分数据，不使用这种环境进行优化 】
7，sql正则【P302】
8，利用group by的with rollup子句
在sql语句中，使用group by 的with rollup字句可以检索出更多的分组聚合信息，它不仅仅能像一般的group by语句那样可以检索出各组的聚合信息，还能检索出本组类的整体聚合信息
【注：当使用rollup时，不能同时使用order by子句进行结果排序，换言之，rollup和order by是相斥的，此外，limit要在rollup后面】
9，用bit group functions做统计
bit_and()十进制转二进制并做一个‘与’操作和bit_or()十进制转二进制并做一个‘或’的操作,【详情可参考P307 】