SQL优化策略

SQL优化这个，在项目上线初期的时候可能感觉有优化无优化没什么关系，但是随着项目数据的增加，业务的曾多，这个时候SQL的效率就会对程序的造成影响，此时SQL优化就显得很重要了。
SQL优化有很多，这里简单的写一些优化：
在查询的时候应避免全表扫描这样会导致SQL的效率低下，首先因考虑在where及order by 设计的列上简历索引，这里列举一些会到时全表扫描的例子：

查询优化

1：在查询的时候应尽量避免在where子句中对字段进行null值判断

2：尽量避免在where查询中使用！= 或< >操作符，

3：尽量避免在where子句中使用or来连接条件，例如：

select id from t where num = 10  or  num = 20

可以改为：

select id from t where num = 10 
nuion all
select id  from t where num = 20 

4： in 和 not in 也要慎用，如：

select id from t where num in (1,2,3)

可改为：

select id from t where num between 1 and 4

5：模糊查询也应该慎用 全模糊查询 （%...%）,左模糊查询（%...），都会导致全表扫描，但是右模糊查询（...%）则不会全表查询。

6：应尽量避免在where字句中对字段进行表达式操作，如：

where id from t where num/2 = 100

可改为：

select id from t where num = 100*2

7：因尽量避免在where 子句中对字段进行函数操作，如：
select id from t where substring（name,1,3）= 'abc' --name以abc开头的id
可改为：

select id from t where name like 'abc%'

8：不要在where字句中的 “=” 左边进行函数，算数运算或其他表达式运算

以上这些会导致索引失效使用全表扫描。

索引

9：在使用索引作为条件是，若有复合索引，那么必须使用该索引中第一个字段作为条件时才能保证系统使用该索引，否则该索引不会被使用，并且应尽可能的让字段顺序与索引保持一致

10：很多时候用exists 代替 in 较好，如：

select num from a where num in （select num from b）
可改为：
select num from a where exists（select 1 from b where num = a.num）

11：并不是所有的索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引有大量数据重复时，SQL查询可能不会利用索引 ，

12：索引并不是越多越多越好，索引固然可以提高相应select 的效率，但同时也降低了insert 以及update 的效率，因为insert 或update 可能会重建索引，所以建索引需慎重考虑，视具体情况而定。
一个表的索引最好不要超过6个。

字段

13：尽量使用数字类型字段，若只含数值信息尽量不要设计为字符型，这样会降低性能，并会增加存储开销；因为引擎在处理查询和连接时会逐个比较字符串中的每一个字符，对于数字类型而言只需要比较一次就够了。

14：尽量使用varchar 代替char ，因为varchar 储存空间小，可以节省空间，其次对于查询而言，在一个较小的字段内搜索效率显然要高些；

临时表

15：不要使用select * from t ，这样的全表查询，用具体的字段代替 *，不要返回用不到的字段。

16：应避免平凡创建和删除临时表，以减少内存的消耗。

17：临时表并不是不可使用的，适当的使用他们可以使某些例程更有效，例如，当需要重复使用大型表或常用表中的某个数据集使，但是，对于一次性事件最好使用导出表。

18：在兴建临时表时，如果一次性插入数据量很大，name可以使用select into 代替 create table ，避免大量log 以提高速度；若数据量不大，为了缓和数据表的资源，应先create table ，再insert

19：如果使用到了临时表，在储存过程的最后务必将所有的临时表格式删除，先 truncate table ，然后再drop table 这样可以避免系统表的较长时间锁定。

游标事物等

20：应尽量避免使用游标，因为游标效率较差，如果游标操作的数据查过1万行，那么可以考虑改写

21：使用基于游标的方法或临时游标的方法前，应现寻找基于集的解决方案，来解决问题，基于及的方法通常更有效。

22：与临时表一样，游标并不是不可使用的，对小型数据集使用FAST_FORWARD游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得需要的数据时。在结果集中包括“合计”的案例通常要比使用游标执行的速度快，据实际情况而定。

23：尽量避免大事物操作，提高系统并发能力。

24：尽量避免向客户端返回大数据量，托数据量过大，应考虑相应需求是否合理。

分页查询的相关优化：

常见的分页查询查询到的"limit 2000，20 "；会出现先查询前面2200个抛弃前面2000个，造成查询和排序的代价增大，优化方式：
1：在索引上完成分页的操作，分局主键关联回原表查询所需的其他内容，

 explain select a.last_name , a.first_name from user a inner join (select id from user order by id limit 2000,20) b on a.id=b.id;

2：吧limit查询转换成一个位置查询，可以通过吧上一页最后一条数据记录下来

例：select * from payment order by rental_id desc limit 2000,20;  这样效率较低

如果通过rental_id 降序排列的，那么我们在查询limit 1800,20 的时候，记录下2000位置的 rental_id 加入这里的 rental_id 的值，假设这里的值为5000，那么SQL语句就可以转换为：

select * from payment where rental_id < 5000 order by rental_id desc limit 10;

注意：这个只适合在 排序字段不会出现重复的特定环境，能够减轻分页的压力，如果字段出现重复值那么就会出现记录丢失。

SQL提示

常见的SQL提示如下： 1：user index 这个表示希望SQL去参考的索引，可以让MySQL不考虑其他可用的索引了

explain select count(*) from user user index(idx_user_id);

2：ingore index 知识单纯的希望MySQL 忽略这一个索引，或者多个；例如：

explain select count(*) from rental ignore index(idx_rental_date)

3：force index 强制MySQL使用一个索引

explain select * from user  use index (idx_fk_inventory_id) where inventory_id >1;

默认inventory_id都是大于1的，所以一般会全表扫描，如果强制使用这个，那么MySQL还是会使用这个索引。