常用SQL书写规范

常用SQL书写规范

Select语句

1.在查询时不要过多的使用通配符。
例：select * from tb1; 用到几列就选择几列，select col1,col2 from tb1;

2.在可能的情况下尽量限制结果集的行数。
例：select col1,col2 from tb1 limit 10;

3.避免数据类型的隐性转换,有一些数值类型字段会习惯设计成varchar或char类型，但在执行过程中会发现即使数值不使用引号也不会报错，因为这发生了隐性转换，耗费了数据库的开销
例：tb1 中 col1 类型为varchar（10）
Select * from tb1 where col1=2;
注：如果col1字段为int类型
Select * from tb1 where col1=’2’;
可以用到索引

4.尽量避免在where子句中对字段使用函数或表达式操作，这将导致引擎放弃索引而使用全表扫描
例：select * from tb1 where substring(col1,1,4)=‘1234’;
不管col1是何种类型的索引都会失效，应改为
select * from tb1 where col1 like ‘1234%’;

5.由上面的问题引申出在查询时使用 like 的问题。
select * from tb1 where col1 like ‘1234%’;
这种查询方法要比
select * from tb1 where col1 like ‘%1234%’
和 select * from tb1 where col1 like ‘%1234’
这两种方式性能高出4-5倍，因为第二种和第三种方式将无法使用索引。
但第一种也不是所有情况下都会使用索引。

6.尽量避免使用!=或<>(不等于)、IS NULL或IS NOT NULL、NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。 IN 操作符在子查询数据数据量较小会使用索引。

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

8.合理使用EXISTS,NOT EXISTS子句。例：
①Select sum(t1.c1) from t1 where (select count(*) from t2 where t2.c2=t1.c1) >0
②Select sum(t1.c1) from t1 where exists(select * from t2 where t2.c2=t1.c1)
两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。

9.left join on/right join on ；join操作MySQL内部采用了一种叫做 nested loop join的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，如果使用inner join 的话 MySQL会自动地选择小表去驱动大表，而left join会指定由左边的表去 驱动右边的表，如果不能确定那个表的结果集更小，就改用 inner join 有MySQL自动去选择驱动表。

10.order by如果针对一个字段进行排序操作，尽量使用索引字段；如果针对两个字段排序操作，如果是单独对两个字段建索引，order by将无法使用索引，需要对两个字段建立一个联合索引，并且order by 字句的字段顺序要与联合索引中的字段顺序保持一致。

11.（仅限MYSQL）千万不要使用order by rand() 这个语句。Order by rand() limit 1 这条命令可以满足一个 随机取出一 条数据的需求，但rand()函数会耗费大量的CPU资源。使用rand()将数据行结果集打乱，会使数据库性能直线下降，可使用其他方式代替。

12.Count
任何情况下SELECT COUNT() FROM tb 是最优选择；
尽量减少SELECT COUNT(col) FROM tb WHERE col = ‘value’ 这种查询；
杜绝SELECT COUNT(col) FROM tbl的出现。
在没有where条件出现时 count()和count(col)基本等价，在有where条件情况下count()比count(col)快很多，因为count()会自动去寻找主键。

13.where子句中避免出现数学运算，因为对字段做数学运算会导致执行索引扫描
例：select * from tb1 where col1 / 2 > 100
可转换成 select * from tb1 where col1 >200

14.联合索引使用方法
①index(a,b)
where a=xx 或 where a=xx and b=yy 可以用到索引
where b=yy 无法用到索引
② index(a,b,c)
a （a，b） （a，b，c）
where a=xx
where a=xx and b=yy 或 where b=yy and a=xx
where a=xx and b=yy and c=zz 或 b ,c ,a 或 c,b,a等
where a=xx and c=zz
能用到索引
where b=xx
where c=xx
where b=yy and c=zz
不能用到索引
遵循最左侧匹配法，按照联合索引从左到右逐个匹配。若能匹配上，在where条件中顺序可以任意，因为数据库优化器会自动从where条件中优先寻找最左侧元素进行索引查找。若未找到最左侧索引元素，则不会使用索引。

15.（1）索引字段范围查询，where条件里使用>、<、>=、<=时，数据量较大时不会使用索引
例：在col 字段上建立索引
Select * from table where col>0 数据量大时不使用索引
Select * from table where col>0 and col<5 数据量小时使用索引
（2）在索引列上进行between and 范围查询时，是否会使用到索引，根据表数据量以及查询结果数据量而定的，具体情况可以在实际写sql时查看执行计划，判断是否使索引失效。

16.in 和 exists、not in 和not exists
（1）in 和 exists:
select * from student s where s.stuid in
(select stuid from score ss where ss.stuid <10)
首先会执行from语句找出student表，然后执行 in 里面的子查询，再然后将查询到的结果和原有的student表做一个笛卡尔积，再根据我们的student.stuid IN score.stuid的条件，将结果进行筛选（既比较stuid列的值是否相等，将不相等的删除）。最后，得到符合条件的数据
select * from student s where
EXISTS(select stuid from score ss where ss.stuid = s.stuid)
使用exists关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表，然后，根据表的每一条记录，执行exists中的子查询，依次去判断where后面的条件， 如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。
总结：如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。
（2）not in 和not exists
　　　　如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

update语句

1.尽量避免update主键字段
2.尽量避免update 索引字段
3.尽量避免update where字句中包含的字段