数据库SQL开发规范

SQL开发规范(针对OLTP)

• 不使用大事务，拆分为小事务。大SQL拆成多个小SQL, 增加缓存命中率和提高CPU利用率(降低IOWait)
• 不使用多表查询, 禁用JOIN
• 不使用 * , SELECT使用具体的列名
  • 开发时不会因为使用*而显著缩短开发时间和设计时间
  • 还能减少QUERY RT
  • 在发生列的增/删时, 发生列名修改时, 最大限度避免程序逻辑中没有修改导致的BUG
  • 后续很多工具需要依赖于此
  • 能够减少网络传输消耗
  • 为利用覆盖索引进行优化提供可能

• 出现大批量操作的导数据/变更数据必须分批进行. 

•  比如示例:

  SELETE `table` WHERE `Updated` > 20001001 LIMIT 2000

  UPDATE `table` ... WHERE `Id` =  ? LIMIT 1

  DELETE FROM `table` WHERE `Id` =  ? LIMIT 1

  UPDATE `table` ... WHERE `Id` >  ? LIMIT 30

  DELETE FROM `table` WHERE `Id` > ? LIMIT 1000

  禁止在MySQL Server端做运算, md5() rand() to_days等, 在索引列中作运算, 无法利用索引

  错误的写法

  select id from tab where id+1=5;

  select id,value from tab where to_days(now())-to_days(gmt_created) <=10;

  正确的写法

  select id from tab where id=5-1;

  select id,value from tab where gmt_created >= DATA_SUB(now(),interval 10 day );

  禁止IN的元素个数超过500，涉及到分片业务的SQL个数应少于64/128 ，建议个数: 30 or 50

  禁止使用%前缀模糊查询，因为B+TREE 无法使用索引，将导致全表扫描

  • 聚合函数与括号直接不得有空格，例如count(*)，才是正确的，新版RDS的SQL MODE 已经统一为NO_ENGINE_SUBSTITUTION
  • 不使用存储过程，保证数据库只是用来存储数据
  • 明确区分列的值类型(尤其是STRING vs Numeric).  SQL是强类型语言
    • 杜绝任何隐式类型的转换
    • 本规范不存在JOIN, 因此忽略考虑联表时的CharacterSet类型的转换

  分页如果有大OFFSET，先通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

  SELECT id, cu_id, nameFROM  tab  where type =‘0’ AND   endt>='2014-05-29' ORDER BY id desc LIMIT 149420 ,20;

  延迟关联

  SELECT a.id a.name,a.gmt_create FROM tab a, (select id from tab where type ='0' AND endt >='2014-05-29'

  ORDER BY id desc LIMIT 149420 ,20 ) b where a.id=b.id;

  数据订正

  • 执行DELETE/UPDATE, 一定要加上LIMIT 1, 确定只操作一行.    相关的SQL,一定找人(水平相当或更好的)做好DoubleCheck.