SQL常见bug及优化（适合数据量大的数据库）

1. SQL走查

1.1. 规范样例

1.1.1. 建索引 一级bug

由各个小组先统计可能需要的索引列，然后讨论统一添加索引

1.1.2. 模糊匹配  一级bug

模糊匹配(like) ，不允许关键字前面模糊，特殊情况除外，定义如下原则：

l 列表总数据量 在1万 以内的 模糊查询 可以支持 前后模糊匹配

l 列表总数据量 大于1万的，最多关键字在前面精确匹配，右边模糊匹配

l 单号查询，走精确匹配

1.1.3. SQL类似子查询逻辑 一级bug

建议抽离到java中单独去查询

子查询列表集合非常大的，建议采用 exists 查询

1.1.4. 多余的查询 一级bug

优化建议：SELECT COUNT(DISTINCT sku) FROM t_im_bill_asn_dtl WHERE asn_no = '123' AND real_a_qty > 0

原因：distinct 底层也是采用 group by, 因它不需要排序，所以采用的是松散索引扫描，扫描索引键的一部分

      group by, 因要排序，所以扫描整个索引键

      结论：同等条件下  distinct效率高些。当 disctinct 无法实现时，采用group by.

1.1.5. Select * 一级bug

1.1.6. Join表数量大于3张表 一级bug

如果预测数据量大的时候，建议拆开处理(目前两年时间 来料+销退 预计18万数据) 

1.1.7. 用于统计时，Group by 多字段(超过3个) 一级bug

Group by 多字段 除了分组排序以外，如果是统计数据 用了group by 多个字段，需要优化。

1.1.8. SQL where 条件 尽量不用使用函数处理  

Ø 1.1.2事例中，采用了replace函数,对入参进行函数操作属二级bug，建议将此操作放入java层面进行处理

Ø 采用函数对表中字段进行操作后，再进行逻辑比较，属一级bug，必须避免

 

 

1.1.9. SQL where 条件硬编码 二级bug

所有业务字段查询条件，建议都通过参数传递，而不是硬编码写死(魔法值)

1.1.10. Mapper.xml  二级bug

Mapper.xml配置sql文件中，尽量采用标签配置，例如 <where>

 

1.1.11. Select 里面采用类似concat函数 二级bug

建议将这的操作 放到java应用程序进行处理

1.1.12. 可枚举的查询字段，建议放到条件最后 二级bug