SQL使用规范

作为一名工程狮, 不只要会写SQL, 还得会写性能优良的SQL. 以下是总结在开发过程中常用的方法.

• 规则1 遵循单SQL最少功能原则
  • 不建议使用复杂的SQL查询数据, 如超过3个表的关联查询;
  • 建议使用简单的SQL查询数, 在业务代码中进行处理以达到最终想要的数据.

如查询某订单详情的例子:

#方法一：分多次简单查询, 然后在代码聚合数据（推荐）
select ... from om_order where order_id = 1;
select ... from om_order_item where order_id = 1;
select ... from om_order_item_ext where order_id = 1;

#方法二：通过关联查询，一次查出数据（亦可）
select ... from om_order t1,om_order_item t2,om_order_item_ext t2
where t1.order_id = t2.order_id and t1.order_id = t3.order_id and t1.order_id = 1;

• 规则2 建议按需查询字段
  • 不建议查询全部字段（SELECT *）
  • 尤其在查询一些字段很多的表或者多表关联查询的时候, 更应该避免查询全部字段

如下例子:

#就算查询全部字段，也不建议使用select *

select * from om_order where order_id = 1; //不合适
select field1, field2... where order_id = 2; //推荐

• 规则3 避免嵌套子查询

• 规则4 注意MySQL隐式转换规则, 否则SQL语句不会匹配到索引

• 规则5 降低数据库交互次数

  • 在 规则1 推荐用多次简单查询获得数据, 权衡有如下好处：
    • 表分散在不同物理库不受影响
    • 业务实现逻辑条理清晰
    • 虽然数据库访问次数变多, 在访问次数控制3次左右, 能达到一个好的平衡. 但是如果单个业务逻辑数据库访问次数过多, 比如超过10次, 那么性能会急剧下降. 这个时候应当寻找更好的方式, 避免多次交互.

  以下为常见例子：

  1. 用批量插入代替单条插入，比如创建订单的时候需要写入20条订单项目，一定要避免循坏20次来进行插入操作
  2. 比如业务需要查询10个订单极其订单项目详情。那么可以用一次查询出10个订单主体信息，再用一次查询查询出这10个订单的订单项，最后在业务代码中将订单和对应的订单项目组合起来。一定不要在查询出订单主体信息以后，再循坏10次调用数据库连接查询对应的订单项信息

• 规则6 在影响行数确定的时候使用limit

  • 在查找数据、更新数据、删除数据时，如影响行数确定需带上limit + 行数
  • 其一加快速度，其二在出错的情况下降低错误影响范围；
  • 有大量数据修改是，可使用limit来限制单次操作行数，减少操作响应时间

  ###以下为常见例子:
  select ... from um_user_info where uid = 1 limit 1; //推荐
  update um_user_info set age = 18 where uid = 1 limit 1; //推荐
  

• 规则7 查询时禁止对索引字段使用函数或计算

  • SQL的查询条件，直接对某字段使用了函数或者进行运算，索引无效

  ### 以下为示例
  select ... from om_order where order_id - 1 = 99;//不会使用索引
  select ... from om_order where order_id = 99 + 1;//会使用索引，但是不推荐
  select ... from om_order where order_id = 100;//会使用索引, 推荐。（变量运算尽量在业务代码中完成）
  

• 规则8 谨慎使用 NOT IN 和NOT LIKE

  • NOT IN 和NOT LIKE会造成全表扫描，避免使用，可用JOIN代替

• 规则9 IN 语句一次查询集合不宜太大

  • 在in语句中, 附带的集合不宜超过500个, 如果查询的集合太大就算有索引, 效率也会受损, 此种情况可以考虑业务上优化.

• 规则10 字符串模糊查询注意事项

  • 单向模糊匹配, 被查询的字段需要建立索引
  • 建立索引后, 需注意查询条件避免造成大范围检索, 比如搜索订单商品, 虽然barcode字段有索引, 但是如果查询条件是 LIKE “DI%”, 那么索引其实不会起到具体作用.
  • 禁止使用双向模糊匹配, 此类优化可以从需求层面解决或者通过全文搜索应用来解决. 如果一定要使用双向模糊匹配, 也一定要将搜索范围控制到非常窄的范围内进行模糊查询.