Analysis of the advantages and disadvantages of MySQL stored procedures

MySQL version 5.0 began to support stored procedures. A stored procedure is a complex program stored in a database so that it can be called by external applications. A stored procedure is a set of SQL statements that completes a specific function. It is compiled, created, and saved in the database. Users can call and execute it by specifying the name of the stored procedure and giving parameters (optional).

Stored procedures can effectively improve the reuse rate of SQL statements, and can put a group of related SQL statements into a stored procedure, thereby avoiding connection delays with the MySQL server and occupied network resources caused by multiple queries of the application. The following is an example of a stored procedure that passes in an id to delete the student with the specified id and deletes the student information in the extended table at the same time. In this way, related data can be processed without requiring the application to perform two SQL operations.

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 ;

In general, stored procedures have the following advantages:

• It runs directly on the database layer, thus reducing network bandwidth usage and query task execution latency.
• Improves code reusability and maintainability, aggregates business rules, strengthens consistency and improves security.
• It can bring security advantages and elegant permission control methods. A typical example is the stored procedure of transferring money in a bank. The stored procedure completes the transfer in one transaction and records the complete operation log for subsequent review. Access can be accomplished through stored procedures without requiring elevated privileges on the tables involved.
• The server caches the execution of stored procedures, which can reduce the load of repeated execution.
• Stored procedures are stored on the server side, so they are easier to maintain in terms of deployment, backup, and maintenance of service orders.
• The work of application developers and database developers can be separated, so database experts can write stored procedures and avoid the problem of some application developers having low SQL writing skills.
  

Of course, there are always pros and cons, and stored procedures also have some defects:

• MySQL does not provide good development and debugging tools, so debugging stored procedures is relatively difficult.
• The SQL language itself is not as efficient as the application programming language and is relatively more basic. Therefore, it is difficult to handle complex business.
• Stored procedures may also increase the complexity of application deployment. Not only do you need to deploy application code and database tables, you also need to deploy stored procedures.
• The execution plan cache for each stored procedure in each connection is independent. If many connections call the same stored procedure, repeated caching will result in a waste of resources.
• Stored procedures transfer execution to the database server, which makes it more difficult to expand the capacity of the database server and more expensive than expanding the capacity of the application server.
• The resources occupied by stored procedures are difficult to control, and if a bug occurs, it may cause the server to crash.
• The code of the stored procedure is difficult to interpret. If the stored procedure is simply called in the form of CALL XYZ('A'), it is difficult to analyze the slow query log. Because this requires finding the code of the stored procedure and checking the statements inside.
• For statement-level binlog or replication, using stored procedures may have many pitfalls that make it impossible to use stored procedures - unless you strictly check to eliminate potential problems.
  

Therefore, it is usually necessary to keep stored procedures small and concise to avoid the above-mentioned defects. Of course, stored procedures will run faster for some operations, especially when using loops within stored procedures to complete multiple small queries. If the queries are small enough, parsing the SQL statements and network communications become significant factors in the workload being too high. At this time, the advantages of stored procedures will be highlighted. Take the following stored procedure code as an example:

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,
                                 'aaaaaaaaaaaaabbbbbbbbbb',
                                 'aaaaaaaaaaaaabbbbbbbbbb');
    SET v1=v1-1;
  END WHILE;
END
//

delemiter ;
	

By comparing the same functions with applications, it can be found that the performance of using stored procedures is improved by more than 2 times, and if compared with using MySQL proxy, the performance is improved to 3 times.

Conclusion: Stored procedures are not used much at present, but for some stable businesses, if there are too many network requests between the database server or a large amount of network bandwidth is occupied, you can consider using stored procedures to optimize performance and improve response speed. However, the stored procedures must be verified repeatedly to avoid unexpected errors that would waste too much time troubleshooting.

The above is the detailed analysis of the advantages and disadvantages of MySQL stored procedures. For more information about the advantages and disadvantages of MySQL stored procedures, please pay attention to other related articles on 123WORDPRESS.COM!