SQL语句优化

SQL语句优化
怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。
建立索引不是建的越多越好，原则是：

第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。

第二：建立索引的方法论为：
1、多数查询经常使用的列；
2、很少进行修改操作的列；
3、索引需要建立在数据差异化大的列上
利用以上的基础我们讨论一下如何优化sql.

a. ORDER BY + LIMIT组合的索引优化
如果一个SQL语句形如：SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];
这个SQL语句优化比较简单，在[sort]这个栏位上建立索引即可。

b. WHERE + ORDER BY + LIMIT组合的索引优化
如果一个SQL语句形如：SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

c. WHERE+ORDER BY多个栏位+LIMIT
如果一个SQL语句形如：SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
对于这个语句，大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成

1、like语句优化
全模糊改成左模糊或者右模糊

2、where子句使用or的优化
很多时候使用union all 或 union(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。
如SQL：SELECT id FROM A WHERE num =10 or num = 20
优化成：SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

3、where子句使用IN 或 NOT IN的优化
in和not in 也要慎用，否则也会导致全表扫描。

方案一：between替换in
如SQL: SELECT id FROM A WHERE num in (1,2,3)
优化成：SELECT id FROM A WHERE num between 1 and 3

方案二：exist替换in

如SQL:SELECT id FROM A WHERE num in (select num from b )
优化成：SELECT num FROM A WHERE num exists (select 1 from B where B.num = A.num)

方案三：left join替换in

如SQL:SELECT id FROM A WHERE num in (select num from B)
优化成：SELECT id FROM A LEFT JOIN B ON A.num = B.num

4、where子句中使用 IS NULL 或 IS NOT NULL 的优化
在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。
不能用null作索引，任何包含null值的列都将不会被包含在索引中。
如SQL：SELECT id FROM A WHERE num IS NULL 优化成num上设置默认值0，确保表中num没有null值，
然后SQL为：SELECT id FROM A WHERE num=0

5、where子句使用 ！= 或 <> 操作符优化

在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。
如SQL：SELECT id FROM A WHERE ID != 5
优化成：SELECT id FROM A WHERE ID>5 OR ID<5

6、尽量用 union all 替换 union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

7、limit分页优化
当偏移量特别时，limit效率会非常低
SELECT id FROM A LIMIT 1000,10 很快
SELECT id FROM A LIMIT 90000,10 很慢
优化方法：

方法一：select id from A order by id limit 90000,10; 很快，0.04秒就OK。 因为用了id主键做索引当然快

方法二：select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10;

方法三：select id from A order by id between 10000000 and 10000010;

8.【原则】任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段

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

10.【原则】尽可能的使用 varchar/nvarchar 代替 char/nchar