[MySQL] MySQL 存储过程的优缺点分析

　　MySQL 5.0 版本开始支持存储过程。存储过程（Stored Procedure）是数据库中存储的复杂程序，以便外部应用调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（可选）来调用执行。

DROP PROCEDURE IF EXISTS delete_student_by_id;
delimiter $$
CREATE PROCEDURE delete_student_by_id(IN p_id INT)
BEGIN
DELETE FROM t_students
  WHERE id = p_id;
    
  DELETE FROM t_students_info
  WHERE student_id = p_id;
END
$$
  
delimiter ;

   

• 直接在数据库层运行，从而减少网络带宽的占用和减少查询任务执行的延迟。   
  
• 提高了代码的复用性和可维护性，可以聚合业务规则，加强一致性并提高安全性。   
  
• 可以带来安全性优势以及优雅的权限控制手段。一个典型的例子就是银行中的转账存储过程。存储过程在一个事务里完成转账及记录用于后续审核的完整操作日志。可以通过存储过程完成访问而无需对涉及到的表进行提权。   
  
• 服务端会缓存存储过程的执行，这样可以减少重复执行的负荷。   
  
• 存储过程存储在服务端，因此对于服务单的部署、备份和维护而言，存储过程更好维护。   
  
• 可以将应用开发者与数据库开发者的工作分离，因此可以让数据库牛人来写存储过程，而避免某些应用开发者编写 SQL水平不高的问题。
  

   

• MySQL没有提供好的开发和调试工具，因此存储过程的调试相对来说不那么容易。   
  
• SQL 语言本身的效率没有应用程序的编程语言效率高，且相对更初级。因此，难以处理复杂的业务。   
  
• 存储过程也可能增加应用部署的复杂度，不单单需要部署应用代码和数据库表，还需要部署存储过程。   
  
• 每个连接的存储过程的执行计划缓存是各自独立的。如果有很多连接调用同一个存储过程，反复地缓存会造成资源的浪费。   
  
• 存储过程将运行符合转移到了数据库服务器，这会导致数据库服务器的扩容更难，且比应用服务器的扩容代价更高。   
  
• 存储过程占用的资源难以控制，如果发生一个 bug 可能导致服务器宕机。   
  
• 存储过程的代码很难解读，如果单纯地调用 CALL XYZ('A')这种形式调用存储过程的话，很难分析慢查询日志。因为，这需要找到存储过程的代码并且检查里面的语句。   
  
• 对于语句级的 binlog或复制，使用存储过程可能会有很多陷阱导致无法使用存储过程——除非严格检查排除潜在的问题。
  

DROP PROCEDURE IF EXISTS insert_many_rows;
delemiter //
CREATE PROCEDURE insert_many_rows(IN loops INT)
BEGIN
DECLARE v1 INT;
  SET v1=loops;
  WHILE v1 > 0 DO
  INSERT INTO test_table values(NULL, 0,
                 'aaaaaaaaaaaabbbbbbbbbb',
                 'aaaaaaaaaaaabbbbbbbbbb');
  SET v1=v1-1;
  END WHILE;
END
//
delemiter ;

　　结语：存储过程目前用得其实不多，但是对于一些稳定的业务，如果是因为与数据库服务器之间的网络请求过多或占用了大量的网络带宽，则可以考虑使用存储过程来优化性能，提高响应速度。但是，存储过程务必反复验证，避免出现意向不到的错误导致耗费过多的时间排查问题。