SQL语句书写规范

1/1SQL语句书写规范SQL语句书写和建表规范写在开头数据库的性能调优问题是一个长期的过程，根据数据量的变化有一些之前看起来没有问题的事情，到后期会非常影响性能。

在数据量不断攀升后一些不合理的SQL语句就成为了整个数据库的瓶颈。

所以在开发阶段就要尽量避免一些常见的问题，为后期的数据库性能优化工作减少问题。

总纲保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量少对表的访问行数;最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单SQL语句部分一、Select语句1、在查询时不要过多的使用通配符。

例：

select*fromtb1;用到几列就选择几列，selectcol1,col2fromtb1;2、在可能的情况下尽量限制结果集的行数。

例：

selectcol1,col2fromtb1limit10;3、避免数据类型的隐性转换,有一些id字段会习惯设计成varchar或char类型，但在执行过程中会发现即使数值不使用引号也不会报错，因为这发生了隐性转换，耗费了数据库的开销例：

tb1中col1类型为varchar（10）Select*fromtb1wherecol1=2;注：

如果col1字段为int类型Select*fromtb1wherecol1=2;可以用到索引4、尽量避免在where子句中对字段使用函数或表达式操作，这将导致引擎放弃索引而使用全表扫描例：

select*fromtb1wheresubstring(col1,1,4)=1234;不管col1是何种类型的索引都会失效，应改为select*fromtb1wherecol1like1234%;5、由上面的问题引申出在查询时使用like的问题。

select*fromtb1wherecol1like1234%;这种查询方法要比select*fromtb1wherecol1like%1234%和select*fromtb1wherecol1like%1234这两种方式性能高出4-5倍，因为第二种和第三种方式将无法使用索引。

6、避免使用!=或＜＞、ISNULL或ISNOTNULL、IN，NOTIN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据.7、尽量使用数字型字段，把数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

8、合理使用EXISTS,NOTEXISTS子句。

例：

1.Selectsum(t1.c1)fromt1where(selectcount(*)fromt2wheret2.c2=t1.c1)02.Selectsum(t1.c1)fromt1whereexists(select*fromt2wheret2.c2=t1.c1)两者产生相同的结果，但是后者的效率显然要高于前者。

因为后者不会产生大量锁定的表扫描或是索引扫描。

9、leftjoinon/rightjoinon；join操作MySQL内部采用了一种叫做nestedloopjoin的算法。

NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据，如果使用innerjoin的话MySQL会自动地选择小表去驱动大表，而leftjoin会指定由左边的表去驱动右边的表，如果不能确定那个表的结果集更小，就改用innerjoin有MySQL自动去选择驱动表。

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

11、千万不要使用orderbyrand()这个语句。

Orderbyrand()limit1这条命令可以满足一个随机取出一条数据的需求，但rand()函数会耗费大量的CPU资源。

使用rand()将数据行结果集打乱，会使数据库性能直线下降，可使用其他方式代替。

12、Count任何情况下SELECTCOUNT(*)FROMtb是最优选择；尽量减少SELECTCOUNT(*)FROMtbWHEREcol=value这种查询；杜绝SELECTCOUNT(col)FROMtbl的出现。

在没有where条件出现时count(*)和count(col)基本等价，在有where条件情况下count(*)比count(col)快很多，应为count(*)会自动去寻找主键。

13、LimitLimit使用有两种方式limit10和limit10,10在表的数据量不大和offset也不大的情况下没有明显差距但是如果是limit10000,10的话这种做法性能会比较差，建议方法先取出第10000行的id值然后where条件限制从10001开始取值limit1014、where子句中避免出现数学运算，因为对字段做数学运算会导致执行索引扫描例：

select*fromtb1wherecol1/2100可转换成select*fromtb1wherecol120015、联合索引使用方法1、idx(a,b)wherea=xx/a=xxandb=yy可以用到索引whereb=yy无法用到索引2、idx(a,b,c)能用到索引wherea=xx/a=xxandb=yyandc=zz/a=xxandb=yy/a=xxandc=zz/b,c,a/c,b,a不能用到索引whereb=xx/c=xx/b=yyandc=zz一句话=使用联合索引where条件中必须要使用最左侧的索引字段顺序无所谓。

二、update语句1，尽量避免update主键字段2，尽量避免update索引字段3，尽量避免updatewhere字句中包含的字段4update操作where字句中需要带上索引字段，否则会锁表，导致其他操作锁等待。

三、写在最后在写SQL语句时尽量按照以上这些原则来实现，能避免很多性能问题，也能为以后的工作减少时间消耗。

建表部分一、表名和字段名表名和字段名的命名规则见名知意不要使用保留关键字使用小写并用_分隔如无特殊需求建表语句中不要设置存储引擎和字符集二、字段数据类型1、使用int型的字段作为主键，如果业务上不存在最好设置一个自增的id作为主键2、Varchar(255)中的数字代表字符数而不是字节数，尽量够用就可以不要设置过大，单行的varchar字节数最大为65535。

设置过大会使存储空间浪费3、存储精确浮点数要使用DECIMAL替代FLOAT和DOUBLE4、存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节5、禁止在数据库中使用VARBINARY、BLOB存储图片、文件等6、不建议使用ENUM类型，使用TINYINT来代替7、建议使用UNSIGNED存储非负数值8、字段名不建议使用user,key,keys,type,status等这一类的保留关键字可以加前缀使用。