项目必备知识-------SQL开发规范整理

1.强制要求

1.所有的表必须要有主键，最好是自增主键，Mysql一定要用innodb引擎，并且要谨慎更新主键

• 《MySQL为什么一定要有一个主键》https://www.jianshu.com/p/1203fd140cc2

2. 禁止在系统的生产状态执行DDL(包括加索引)
3. 不允许使用数据库的存储过程/函数/触发器
4. 生产状态下禁止大事务，例如有的sql一次性更新上百万条记录

• 规则说明
  • 1.大事务会消耗大量的数据库资源，导致业务处理性能波动
  • 2.应该避免一次性更新几十上百万条记录的场景，如果有，拆分成多个小事务
  • 3.大事务的场景，应尽量化复杂为简单，复杂sql拆分为多步骤实现
    5.不允许在表中使用text lob blob等字段，如果是存储文件或是图片，仅在数据库表中保存路径，因为文件或者图片一般情况下较大，容易导致数据库占用空间大量增加，给备份恢复带来负担
    6.对于实时查询，查询条件要求都要使用到索引，实时查询性能要求较高，走索引能够提升查询效率
    7.所有的数据量会随着时间明显增长，表都应该设计历史表
    8.同一个事务多表更新时，需要按照约定的表的更新顺序更新，以避免死锁
• 规则说明
  • 在高并发的情况下，多表更新顺序如果正好相反的话，就会出现AB，BA相互等的死锁情况
    9.在统计行数时，要使用count(*)，禁止使用count(列名)
• 规则说明
  • 因为count(*)会统计值为null的行，而count(列名)不会，并且还要进行一次非空的判断
  • count()和count(常量)，mysql的官网说了，没区别，对他们的优化是一样的，不过为了更好的适应规范，应使用count()来统计行数
    10.使用count(distinct col) 计算该列除null之外的不重复行数
    11.使用sum()时需注意NPE问题，可以使用ISNULL()
    12.使用ISNULL()来判断是否为null
• 规则说明
  • null与任何值的直接比较都为null
    13.在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句，避免后续的处理占用系统资源
    14.不得使用外键和级联，一切外键概念必须在应用层解决
    15.数据订正时，删除和修改记录时，需要关注数据库的主键或者唯一索引
• 规则说明
  • 更新时，可能想更新的只有一条记录，但是未关注主键，唯一索引，实际更新的数据可能就不止一条了
    16.数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能执行更新语句
    17.禁止在批量查询的sql中使用数据库函数，要求改为值传递。
• 正确用法示例：
  • 1. v_date := sf_init_date();
  • select fund_id,fund_name, v_date from tfundinfo where status in(‘1’,‘2’);
    18.禁止使用视图
• 规则说明
  • 视图的性能难以把控和优化
  • 已有模块，无明显问题保持不变，后续如果重构，则应废弃视图
    19.可以用基本sql的，不要使用函数，包括系统函数，会影响性能
    20.禁止使用select * 来查询所有字段，可能导致查询出不需要的字段，浪费服务器资源
    21.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
• 规则说明
  • 不要以为唯一索引影响了insert速度，这个速度是可以忽略的，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生
    22.超过三个表禁止join，需要join的字段，数据类型必须绝对一致，多表关联查询时，保证被关联的字段需要有索引
    23.在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可

2.推荐要求

1.单表数据量不要超过千万

• 规则说明
  • 1.单表数据量过大，在数据库增加字段时，目前工具生成的脚本是：增加字段的过程是通过建立新表，在复制数据在新表，再删除原表和重命名新表为原表，数据量越大，阻塞DML的时间越长
  • 2.单表数据量过大时，做表关联查询也会比较慢
  • 3.建议创建表时加几个保留字段，比如bigint， varchar都预留1-2个
  • 4.单表数据库超过千万时，通过实现mysql分库分表，以提升单表的性能
    2.建议慎用子查询
• 规则说明：
  • 1.尽量采用join方式实现表关联，避免使用子查询，子查询较影响性能
  • 2.字典类的翻译，可以使用后端内存表进行翻译之后再给前端
    3.能用整型一定不同varchar，因为整型的处理优于字符型
    4.查询频率高，且计算复杂的功能，可以采用服务器端定时计算快照的方式，查询时直接从快照中读取数据，能加快性能
    5.对需要做汇总查询的数据做一些预统计汇总，尽量避免对大表的group by操作
    6.所有的查询都限制查询记录数不超过1000条
• 规则说明：
  • 查询太多，用户看不了那么多
  • 查询太多，浪费服务器资源
  • 查询太多，还显得系统慢很多，用户体验差

7.如果有order by的场景，请注意利用索引的有序性
8.利用覆盖索引来进行查询操作，避免回表
9.利用延迟关联或者子查询优化超多分页场景
10.建立组合索引时，区分度最高的在最左边。
11.防止因字段类型不同造成的隐式转换，导致索引失效