Use of stored procedures in MySQL

With regard to the stored procedures for MySQL, some of the largest companies I know have strict restrictions on using stored procedures to implement business logic. I have collected some information and combined my own experience to summarize some of my own knowledge about the use of stored procedures in MySQL. Because of the limited level, if there are errors, please make more bricks.

The advantages of the stored procedure include the following points:

1. Improved performance. Compared to not using stored procedures, because the stored procedure at the time of creation, the database has been parsed and optimized, and then each call will not be compiled again, this is relative to the traditional SQL statement each call needs to compile the situation, performance improved, Stored procedures are compiled faster than a single piece of execution. Once the stored procedure is executed, a copy is kept in memory so that the next time the same stored procedure is executed, it can be read directly from memory.

2. Re-usability is strong. Stored procedures can be executed using a name, which is the legendary "write once, call casually". and is transparent, because it is stored in the database so it can be used for any application. The new application only needs to call the corresponding stored procedure to get the corresponding data service. This not only improves reusability, but also reduces the chance of errors and speeds up development. While not relying on a host language, some common code does not have to be duplicated if developed in multiple languages.

3. Reduce network traffic. This is not obvious when it comes to small amounts of data. The stored procedure runs directly on the database server, and all data accesses are performed inside the server without the need to transmit data to other terminals, thus reducing the frequency of communication between the application server and the database server. Calling a stored procedure with a few rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, its performance will be significantly higher than a single call to the SQL statement.

4. Improved security. Stored procedures provide access to specific data by granting permissions to users (rather than tables), and the parameterized stored procedures prevent SQL injection attacks to a certain extent to ensure security. DBAs can get data services for applications that do not have access to the tables in the database, and to the stored procedures, which are like "interfaces" in our programming. For systems with high security requirements, such as banks, the most commonly used operations are through stored procedures or functions, thus completely "hiding" the table from the application.

5. Increased flexibility. Because stored procedures can be written using Process Control statements, they are flexible enough to execute different SQL statements based on the actual situation, rather than simply executing commands. And the stored procedure can also modify its logic and other parts do not change, that is, the structure of our table has changed, we may only need to modify the corresponding stored procedures, our Java or PHP programs do not need to change.

6. Reduce workload. When the business is complex, if we do not use stored procedures, then we will need to remove the data from the database, and then after the calculation, and then into the database. These are expensive, including our Java or PHP program to connect to the database to obtain a result set, and several other operations. If we use a stored procedure, we can complete the modification directly in MySQL. And can be distributed work, the application and database coding can be done independently, without suppressing each other.

7. High maintainability. Updating stored procedures typically requires less time and effort than changing, testing, and redeploying assemblies. In a production environment, you can modify the business logic (or bugs) without restarting the server by modifying the stored procedure directly. But this convenience has been abused many times. For example, modifying a stored procedure directly on a formal server without a full test result can be very serious.

Well, the advantages of the stored procedure are described above, and then take a look at the drawbacks of the stored procedure:

1. Writing and Debugging trouble. MySQL itself does not have a decent IDE to develop stored procedures, we often need a line of honest handwriting, so it will be more troublesome. The debugging of stored procedures is also a problem, there is no very decent debugging tools, many times it is debug with print, and when using Slowlog to perform performance analysis of MySQL, can only record the entire stored procedure execution, but unable to record the execution of specific statements within the stored procedure, For debugging a stored procedure that is up to hundreds of rows of SQL is a pain in the egg.

2. Performance advantages are not obvious. At run speed, for most SQL statements, the time spent compiling SQL is not very large, but the execution of stored procedures also requires some additional overhead such as checking permissions, so for very simple SQL, stored procedures do not have a significant performance advantage. Moreover, the database is mainly used for data access, is not suitable for complex business logic operations, bear the business pressure will occupy a lot of system resources (CPU, memory).

3. Redundancy function. Typically, the database server serves only the application servers in the intranet, and the users who connect to the database are often the same. For most programs other than the financial world, the security requirements are often less than other aspects of performance, functionality, and so on. Therefore, the security situation looks good, in fact the advantages are somewhat superfluous.

4. Portability is poor. When our program wants to replace the database, its portability is more complicated than the non-applicable stored procedure. and MySQL's stored procedures are less powerful than Oracle, SQL Server, and even PostgreSQL.

5. The cart before the horse. SQL itself is a structured query language, plus some control (assignment, loop and exception processing, etc.), but not oo, essentially or process, a large number of stored procedures for business logic development, because it does not support object-oriented design, the object-oriented approach to the business logic can not be encapsulated, Thus, it is impossible to form a common and reusable business logic framework, which can be difficult to handle in the face of complex business logic.

6. Poor extensibility. A relational database like MySQL, which is extremely difficult to scale when CPU and IO bottlenecks occur, but the application server has CPU and IO bottlenecks, especially in the case of SNA architecture, it is theoretically possible to gain unlimited horizontal scalability, just add servers. This method works faster than any method and is often the lowest cost.

Summarize:

For MySQL, in general, except for some operations that rely heavily on data processing, other business logic should not be implemented using stored procedures, but should be implemented by the application layer.

PS: Off-topic, not every application bottleneck is on the database side. Even if the bottleneck is in the database, the specific analysis of the problem, but also the function, performance, security, cost, efficiency and other trade-offs, in order to make the most suitable choice.

Use of stored procedures in MySQL