MySQL存储程序开发最佳实践

MySQL存储程序开发最佳实践

1，开发流程

1，写代码之前设立标准和指南
2，遇到一个问题30分钟不能解决之后寻求帮助
3，相互review代码
4，利用独立的测试人员进行测试
5，使用代码版本控制

2，编码风格和惯例

1，使用一致的、可读性强的代码风格
2，使用有逻辑意义的、一致的命名规范
3，使用block和loop labels作为自我诠释文档
4，复杂的表达式使用括号
5，使用纵向的代码排列来强调纵向关系
6，注释代码简明扼要并且只写“增值”信息

3，变量

1，使用一致的而且有意义的变量命名风格
2，避免在内部block里覆盖变量声明
3，使用function来替代复杂的表达式
4，移除不用的变量和代码
5，不要假设表达式的结果为TRUE或FALSE，它可能为NULL
6，尽量少使用用户变量作为全局数据
7，在strict mode下创建存储程序来避免非法的数据赋值

4，条件逻辑

1，对互斥的条件语句使用ELSEIF
2，只使用IF...ELSEIF来测试单独的、简单的条件
3，确保CASE语句能够包含所有的情况，或者构建一个Handler来捕获没有匹配的情况
4，不要混合使用CASE和IF

5，循环处理

1，确保循环会终结
2，在循环的终结条件里显式的声明所有条件，不要在内部使用LEAVE或RETURN
3，在简单的循环里使用一个单独的LEAVE
4，使用一个简单的循环来避免循环必需的冗余代码

6，异常处理

1，处理不可避免但又能够预期的异常
2，使用named condition来提高代码可读性
3，在异常处理时使用一致的SQLSTATE或MySQL error code，不要混合使用
4，避免使用全局的SQLEXCEPTION Handler，除非MySQL实现了SIGNAL和SQLCODE特性

7，存储程序里的SQL

1，显式使用START TRANSACTION语句来打开事务
2，不要让事务“悬空”，要负责结束事务
3，避免使用savepoint，它们让程序逻辑变模糊，并降低程序效率
4，使用合适的锁策略
5，保持事务简短
6，在完成cursor循环之后一定要设置NOT FOUND变量
7，当需要更新记录时使用SELECT FOR UPDATE
8，避免在可能用在SQL里的function里引入SQL语句，嵌套查询会严重消耗性能

8，动态SQL

1，在动态SQL字符串里使用绑定而不是连接变量值，这样可以避免SQL注入
2，小心检查任何可能用于构建动态SQL的参数值
3，考虑为执行动态SQL的存储过程添加INVOKER权限

9，程序构建

1，封装业务逻辑和方程到准确命名的function里
2，使用function和procedure模板来标准化模块
3，使用模块化来限制程序代码50-60行
4，避免程序的副作用
5，避免条件和循环的深层嵌套
6，限制执行部分只有一个单独的RETURN语句
7，使用存储程序来实现对多个trigger公用的代码

10，性能

1，专注于SQL调优来提高存储程序性能
2，为你的程序小心创建最好的索引集
3，避免意外的全表扫描
4，优化必要的全表扫描
5，避免将计算消耗过大的逻辑放在存储程序里
6，将循环中不变的表达式移出循环
7，优化条件结构
8，将匹配率较大的条件放在IF和CASE语句的前面