风险SQL 规范及案例

一、 原则

1、程序处理优先：数据库最容易也通常是一个系统的瓶颈，因此不要给数据库加压力，能够程序处理就程序处理。
2、简单操作数据库：一个系统越简单越稳定越不容易出问题， 因此要尽量简单使用数据库， 如SQL简单，事务小
3、数据存储评估：数据库资源宝贵，是很难水平扩容的， 要水平扩容动作是很大的， 出了故障影响也是很大的， 不像应用可以很容易水平扩容。因此不要什么都往里面保存， 要预估好数据量
4、对数据负责：自己的数据负责，DBA只是管理。出现高负载，慢SQL,，还是开发人员自己花时间修 改程序与优化， 因此不要想着DBA给提供性能更强的机器与扩容硬盘来解决问题

二、 建表设计规范

1、建表时根据业务需求和操作频率评估，建立索引

优化：

• 每个表都要有create_time, update_time字段，create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’， update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘创建时间’。都加索引，业务一般都会使用到，也方便查问题。备注： create_time可以视情况加不加索引，如果遇到排序，则要加索引。
• 表中已经存在大量数据的前提下，为某个字段段建立索引，会导致锁表，创建索引执行时间长，影响业务

2、一个表数据量最好不要超过500万行，超过1000w行，要考虑分库分表，索引也对提高性能有限

3、 反范式设计：把经常需要join查询的字段，在其他表里冗余一份。如user_name属性在user_account，user_login_log等表里冗余一份，减少join查询

4、保存相同数据的字段在不同的表，其命名与类型要一致。例如user_id在t_user表为bigint则在t_user_login也要为user_id bigint。严禁使用不同类型， 这在join语句中会引起类型转换，导致使用不了索引

5、其他常见：略

三、风险违规—优化案例总结

1、风险违规项：limit更新删除

优化： 必须要小批量变更， 一次不要超过1000行。禁用update | delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致，导致数据错乱。建议加上order by id 或者根据主键锁定范围更新

2、 风险违规项：隐式转换

查询列发生隐式或显示的类型转换，查询列是什么字段类型，列的值就应该用什么字段类型，否则会导致发生类型转换而使用不到引

3、风险违规项：使用函数或表达式

优化：严禁where条件字段使用函数或表达式，否则无法利用索引 : 如where date_format(update_time, ‘%Y-%m-%d’)=’2019-09-09’,

4、风险违规项： 不同字段where条件使用or

优化：不同字段where条件or尽量用union代替

5、风险违规项：同字段where条件使用or

优化：严禁同字段where条件使用or,请使用 in（in通常是走索引的，当in后面的数据在数据表中超过30%的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系）。在MySQL数据中，截止5.7.18版本，对IN子查询，仍要慎用