SQL优化

一、查询优化

优化原则：建立索引或者尽最大努力命中索引，避免发生全表扫描

可能优化方向：

（1）     只查必要字段

（2）     考虑在where及order by涉及的列上建立索引，并使用explain查看索引是否生效

（3）     用TRUNCATE替代DELETE

                     Truncate数据不放在回滚段中，立即生效，不会触发trigger

                     Delete数据会放在回滚段中，事物提交后生效，会触发trigger

（4）     避免在where子句中使用is null进行判断以及使用!=/<>操作符

（5）     避免在where中使用or，尽量使用union替代

优化前SQL：select A.id..... from A,B,C,D,E where A.id=xx and B.id=xx and ( C.id=xx or D.id=xx or E.id=xx)
优化后SQL：select A.id..... from A,B,C,D,E where A.id=xx and B.id=xx and C.id=xx union select A.id..... from A,B,C,D,E where A.id=xx and B.id=xx and C.id=xx and D.id=xx ...
使用限制：
------or连接的字段能够使用索引，改成union后才会提升查询速度

------union会查询两次，然后将结果集合并，如果结果集很大，就不太适合了，因为mysql还会用到外存储的临时表来将两个结果集暂存、合并、输出

（6）     in 和 not in要慎用，对于连续数值，用between代替in，对字符串，用exists代替in

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询
如果查询的两个表大小相当，那么用in和exists差别不大
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in  
用一句话概括就是：尽量保证使用大表的索引

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快

对于连续数值，用between代替in，B+Tree索引在叶子节点有顺序指针，以支持区间查询

（7）     应尽量避免在 where 子句中对字段进行表达式操作

（8）     模糊查询：%5% 以及 %5  都使用不到索引，5%会用索引

（9）     全文检索是检索针对单词的，所以如果你在"abcd,efg,hijklmn"中检索"hi"，那么全文检索也没有用，如果你检索efg，那么可以使用全文检索

二、可能导致全表扫描的写法

（1）where 子句中对字段进行 null 值判断
解决思路：在创建表时不使用NULL默认值，而是设计一些特殊值，如0,-1
（2）where 子句中使用!=或<>操作符
解决思路：MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE
（3）where子句中模糊查询使用 like '%xxxx%'
解决思路：使用like 'xxx% '
（4）where子句中对字段进行表达式或函数操作

三、插入优化

（1）合并数据

         示例：

insert into t_student (id,sno,sname,ssex) values (20003,'20003','20003',20003),(20004,'20004','20004',20004);

（2）使用事务

         示例：

START TRANSACTION;
insert into t_student (id,sno,sname,ssex) values (20005,'20005','20005',20005);
insert into t_student (id,sno,sname,ssex) values (20006,'20006','20006',20006);
COMMIT;

（3）插入数据有序（按主键排序）

重要参数：

SELECT @@max_allowed_packet  ：SQL语句长度限制，合并数据时不能超过此限制，MySQL 5.7.17中默认大小为4M

SELECT @@innodb_buffer_pool_size ：事务大小限制，事务太大可能会影响执行的效率，应在数据达到这个这个值前进行事务提交

参考：http://tech.uc.cn/?p=634