SQL语句书写规范分解

1、SQL语句书写和建表规范 写在开头 数据库的性能调优问题是一个长期的过程， 根据数据量的变化 有一些之前看起来没有问题的事情，到后期会非常影响性能。在 数据量不断攀升后一些不合理的SQL语句就成为了整个数据库的 瓶颈。所以在开发阶段就要尽量避免一些常见的问题，为后期的 数据库性能优化工作减少问题。 总纲 保证在实现功能的基础上，尽量减少对数据库的访问次数；通 过搜索参数，尽量少对表的访问行数；最小化结果集，从而减轻网 络负担；能够分开的操作尽量分开处理，提高每次的响应速度； 在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算 法的结构尽量简单 SQL语句部分 一、Select语句 1、

2、在查询时不要过多的使用通配符。 例：select * from tb1;用到几列就选择几列，select col1,col2 from tb1; 2、在可能的情况下尽量限制结果集的行数。 例：select col1,col2 from tb1 limit 10; 3、避免数据类型的隐性转换，有一些id字段会习惯设计成varchar 或char类型，但在执行过程中会发现即使数值不使用引号也不会报 错，因为这发生了隐性转换，耗费了数据库的开销 例：tb1 中 coll 类型为 varchar(10) Select * from tb1 where col1=2; 注：如果coll字段为int类型

3、Select * from tb1 where col1= 可以用到索引 4、 尽量避免在where子句中对字段使用函数或表达式操作，这将 导致引擎放弃索引而使用全表扫描 例：select * from tb1 where substring(col1,1,4)=234 不管col1是何种类型的索引都会失效，应改为 select * from tb1 where coll like 234% 5、 由上面的问题引申出在查询时使用like的问题。 select * from tb1 where coll like 234%这种查询方法要比 select * from tb1 where coll

4、 like %1234% 和 select * from tb1 where coll like %1234这两种方式性能高出4-5倍，因为第二种 和第三种方式将无法使用索引。 6、避免使用 匸或v、IS NULL 或 IS NOT NULL、IN , NOT IN 等这样的操作符，因为这会使系统无法使用索引，而只能直接搜 索表中的数据. 7、尽量使用数字型字段，把数值信息的字段设计为字符型，这会 降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查 询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要 比较一次就够了。 8 合理使用EXISTS,NOT EXISTS子句。例：

5、 1. Select sum(tl.cl) from t1 where (select count(*) from t2 where t2.c2二tl.cl) 0 2. Select sum(t1.c1) from t1 where exists(select * from t2 where t2.c2=t1.c1) 两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不 会产生大量锁定的表扫描或是索引扫描。 9、left join on/right join on ; join 操作 MySQ内部采用了一种叫做 nested loop join的算法。Nested Loop Join

6、实际上就是通过驱动 表的结果集作为循环基础数据，如果使用inner join的话MySQL会 自动地选择小表去驱动大表，而left join 会指定由左边的表去 驱 动右边的表，如果不能确定那个表的结果集更小，就改用inner join 有MySQL!动去选择驱动表。 10、order by如果针对一个字段进行排序操作，尽量使用索引字段 如果针对两个字段排序操作，单独对两个字段建索引， order by 无 法使用索引，需要对两个字段建立一个联合索引，并且order by字句 的字段顺序要与联合索引中的字段顺序保持一致。 11、千万不要使用 order by rand()这个语句。Order

7、by rand() limitl 这条命令可以满足一个 随机取出一 条数据的需求，但rand()函数会 耗费大量的CPU资源。使用rand()将数据行结果集打乱，会使数据库 性能直线下降，可使用其他方式代替。 12、Count 任何情况下SELECT COUNT(*) FROM tt是最优选择； 尽量减少 SELECT COUNT(*) FROM tb WHERE col value 这 种查询； 杜绝 SELECT COUNT(col) FROM tbl的出现。 在没有where条件出现时count(*)和count(col)基本等价，在有 where条件情况下count(*)比count(

8、col)快很多，应为count(*)会自动 去寻找主键。 13、Limit Limit使用有两种方式limit 10和limit 10,10 在表的数据量不大和ofset也不大的情况下没有明显差距 但是如果是limit 10000,10的话这种做法性能会比较差，建议方 法先取出第10000行的id值然后where条件限制从10001开始取值 limit 10 14、where子句中避免出现数学运算，因为对字段做数学运算会 导致执行索引扫描 例：select * from tb1 where col1/2 100 可转换成 select * from tb1 where col1 200 15、

9、联合索引使用方法 1、idx(a,b) where a=xx / a=xx and b=yy可以用至 U索弓丨 where b=yy 无法用到索引 2、idx(a,b,c) 能用到索引 where a=xx / a=xx and b=yy and c=zz / a=xx and b=yy /a=xx and c=zz / b ,c ,a /c,b,a 不能用到索引 where b=xx / c=xx /b=yy and c=zz 一句话二使用联合索引where条件中必须要使用最左侧的索引字段顺 序无所谓。 二、update语句 1，尽量避免update主键字段 2，尽量避免update索引字段

10、 3，尽量避免update where字句中包含的字段 4 update操作where字句中需要带上索引字段，否则会锁表, 导致其他操作锁等待。 二、写在最后 在写SQL语句时尽量按照以上这些原则来实现，能避免很多性 能问题，也能为以后的工作减少时间消耗。 建表部分 一、表名和字段名 表名和字段名的命名规则 见名知意 不要使用保留关键字 使用小写并用“分隔 如无特殊需求建表语句中不要设置存储引擎和字符集 、字段数据类型 1、使用int型的字段作为主键，如果业务上不存在最好设置一个自增 的id作为主键 2、Varchar(255)中的数字代表字符数而不是字节数，尽量够用就可以 不要设置过大，单行的varchar字节数最大为65535。设置过大会使 存储空间浪费 3、存储精确浮点数要使用 DECIMAL替代FLOAT和DOUBLE 4、存储时间(精确到秒)建议使用TIMESTAMP 类型，因为 TIMESTAMP 使用4字节，DATETIME 使用8个字节 5、禁止在数据库中使用 VARBINARY、BLOB存储图片、文件等 6、不建议使用ENUM类型，使用TINYINT来代替 7、建议使用UNSIGNED存储非负数值 &字段名不建议使用 user,key,keys,type,status等这一类的保留关键 字可以加前缀使用。否则每次