Analysis of advantages and disadvantages of MySQL stored procedure

Time:2021-6-16

MySQL version 5.0 started to support stored procedures. Stored procedure is a kind of database object which is stored in database for external application to call. Stored procedure is a set of SQL statements to complete specific functions. 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 procedure can effectively improve the reuse rate of SQL statements, and can put a group of related SQL into stored procedure, so as to avoid the delay of connection with MySQL server and network resources occupied by multiple queries of application. The following is an example of a stored procedure, which is used to pass in an ID to delete the student with the specified ID and delete the student information in the extension table at the same time. In this way, the associated data can be processed without the need for 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 in the database layer, so as to reduce the occupation of network bandwidth and the delay of query task execution.
  • It improves the reusability and maintainability of code, aggregates business rules, enhances consistency and improves security.
  • Can bring security advantages and elegant means of access control. A typical example is the transfer stored procedure in the bank. The stored procedure completes the transfer in a transaction and records the complete operation log for subsequent audit. Access can be done through stored procedures without the need to raise the rights of the involved tables.
  • The server will cache the execution of stored procedures, which can reduce the repeated execution load.
  • Stored procedures are stored in the server, so for the deployment, backup and maintenance of service orders, stored procedures are better maintained.
  • It can separate the work of the application developer and the database developer, so it can let the database master write the stored procedure, and avoid the problem that some application developers write SQL at a low level.

Of course, there are both advantages and disadvantages. Stored procedures also have some defects

  • MySQL does not provide good development and debugging tools, so it is relatively difficult to debug stored procedures.
  • The efficiency of SQL language itself is not as high as that of application programming language, and it is relatively elementary. Therefore, it is difficult to deal with complex business.
  • Stored procedures may also increase the complexity of application deployment. Not only application code and database tables need to be deployed, but also stored procedures need to be deployed.
  • The execution plan cache of each connected stored procedure is independent. If many connections call the same stored procedure, repeated caching will cause a waste of resources.
  • The stored procedure transfers the running data to the database server, which makes the expansion of the database server more difficult and more expensive than that of the application server.
  • The resources occupied by stored procedures are difficult to control. If a bug occurs, the server may be down.
  • The code of stored procedure is very difficult to interpret. If you simply call the stored procedure in the form of call XYZ (‘a ‘), it is difficult to analyze the slow query log. Because, this needs to find the code of the stored procedure and check the statements inside.
  • For statement level binlog or replication, there may be many pitfalls in using stored procedures, which lead to the inability to use stored procedures — unless the potential problems are eliminated by strict inspection.

Therefore, it is usually necessary to keep stored procedures small and concise to avoid the above defects. Of course, in some operations, stored procedures run faster, especially when loops are used to complete multiple small queries. If the query is small enough, parsing SQL statements and network communication will become an important factor of high workload. 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,
                                 'aaaaaaaaaaaabbbbbbbbbb',
                                 'aaaaaaaaaaaabbbbbbbbbb');
    SET v1=v1-1;
  END WHILE;
END
//

delemiter ;
	

By comparing with the same functions implemented by the application, we find that the performance of using stored procedure is more than 2 times higher than that of using MySQL agent, and the performance will be 3 times higher than that of using MySQL agent.

Conclusion: stored procedure is not widely used at present, but for some stable services, if it is due to too many network requests with the database server or takes up a lot of network bandwidth, you can consider using stored procedure to optimize performance and improve response speed. However, stored procedures must be verified repeatedly to avoid unexpected errors, which will lead to excessive time-consuming troubleshooting.

The above is the detailed content of the advantages and disadvantages analysis of MySQL stored procedure. For more information about the advantages and disadvantages of MySQL stored procedure, please pay attention to other related articles of developer!

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]