mysql语句编写注意事项_SQL语句编写注意事项

1、SQL编写注意事项

1.1 null 列

null 列使用索引是无意义的，任何包含null 值的列都不会包含在索引中。因此where 语句中的is null 或者 is not null 的语句，导致索引失效。

1.2 concat 或 ||

concate 或 || 是mysql 和 oracle 的字符串连接操作，如果对列进行函数操作，就会忽略索引的使用，比如下面的查询语句：

--忽律索引

select ...from .. where first_name || '' || last_name = 'bill gates';--使用索引

select ...from .. where first_name = 'bill' and last_name = 'bill gates' ;

1.3 like

使用like进行模糊查询时，如果通配符%在首位则索引会失效，如下sql语句：

--无法使用索引

select ..from .. where name like'%t%'

--可以使用索引

select ..from .. where name like 't%' ;

1.4 order by

order by 子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

1.5 使用 !=或<> 操作会使索引失效

--索引无效

select ..from .. where sal != 3000;

select ..from .. where sal <>3000 ;

1.6  使用 or 需要注意

1、or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

2、如果or 两边字段都有索引，用explain 也可能出现索引失效的情况

对于使用or导致索引失效的情况

select idfrom t where c1=1 or c2=2改为

select idfrom t where c1=1 UNION ALL select id from t where c2=2

1.7 where 和 having

select .. from .. on .. where .. group by .. having .. order by .. limit ..，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

1.8 exists 和 in 的选择

select * from 表A where id in (select id from表B)

相当于

select* from 表A where id exits(select id from 表B where 表B.id =表A.id)

在选择使用exits 和 in 的原则根据驱动顺序的进行选择，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以

1、当A表数据量大，B表数据量小情况下使用IN

2、当B表数据量大，A表数据量小使用exitsts

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

select colname … from A表 where a.id not in (select b.id fromB表)

修改为：

select colname …from A表 left join B 表 where a.id=b.id and b.id is null

1.9 where 后面的‘=‘后面使用函数、算术运算或其他表达式运算，系统将可能无法正确使用索引

select id from t where num/2=100可以更改为

select idfrom t where num=100*2(该方法索引不失效)

select idfrom t where substring(name,1,3)='abc'可以更改

select idfrom t where name like "abc%"

1.10 避免对where 后面的字段进行隐私类型转换

where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

select name from t where int(age)=20 对字段进行了类型转换，索引失效

1.11 对于联合索引注意最左法则

举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

1.12  注意范围查询 between > <

使用范围查询会使范围查询后面的索引失效

1.13 对于join的优化

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

1)mysql 每有fulljoin

select * from A left join B on B.name = A.name where B.name is null union allselect * from B;

2)尽量使用inner join，避免left join：

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

3)合理利用索引：

被驱动表的索引字段作为on的限制字段。

4)利用小表去驱动大表：

5)巧用STRAIGHT_JOIN：

inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。