Sing Xing interprets the advantages and disadvantages of mysql stored procedures, Xin Xing mysql

Sing Xing interprets the advantages and disadvantages of mysql stored procedures, Xin Xing mysql

Common SQL statements must be compiled and executed before execution. stored procedures are a set of SQL statements to complete specific functions, it is directly compiled and stored in the database. You can call the stored procedure by specifying the name of the stored procedure.

The advantages of stored procedures include the following:

First, the performance is improved. This is relative to the non-applicable stored procedure, because the stored procedure is compiled during creation and will not be re-compiled every call, compared with the case where compilation is required for each call in traditional SQL statements, the performance is improved by two points.

Second, strong reusability. The stored procedure can be used by name, that is, the legendary "one-time writing, just call ". This not only improves reusability, but also reduces the chance of errors and accelerates development, which can be said to be a very good thing.

Third, reduce network traffic. This is generally not reflected in small data volumes. When the data volume is large, we will find that the use of stored procedures will use less bytes than the use of SQL statements, therefore, it reduces the amount of data transmitted.

Fourth, improve security. Because stored procedures can also use permission control, parameterized stored procedures can prevent SQL injection attacks and ensure security to some extent.

Fifth, increased flexibility. Because the stored procedure can be written using flow control statements, it is flexible and can execute different SQL statements according to the actual situation, rather than simply executing commands. In addition, this stored procedure can modify its logic, but other parts do not need to be changed. That is to say, the structure of our table has changed. We only need to modify the corresponding stored procedure, our Java or PHP programs do not need to be changed.

Sixth, when the business is complex, the storage process reduces the workload. Why? The reason is very simple. If we do not apply to the storage process, it will lead to data extraction from the database first, after calculation, and then put it into the database, this overhead is quite large. The overhead includes several operations such as connecting our Java or PHP programs to the database to obtain the result set, if we use the stored procedure, there will be no more things, and we can solve it directly in mysql.

Well, the advantages of the stored procedure are as follows:

First, the workload increases. This is not to say that we can't make mysql do what the program should do, but that mysql itself does not have a decent IDE to develop our storage process. We often need to write it by hand, this will be troublesome, and debugging of the stored procedure is also a problem. There is no decent debugging tool.

Second, the advantage is not obvious. In terms of running speed, for most statement caches, the time overhead for compiling SQL is not very large, but other overhead such as checking permissions are also required to execute the stored procedure. Therefore, for simple SQL statements, stored procedures have no significant advantages.

Third, redundant functions. For web programs, the users who connect to the database are often the same and do not need too many security mechanisms. Therefore, the security detection looks good, but the advantages are redundant.

Fourth, small programs are useless. For a small web application, its use value is smaller, but it will drag down the development progress.

Fifth, for O & M. When our program needs to replace the database, its portability is more complicated than the unsuitable stored procedure. For maintenance, it is better maintained than the server program because it is on the db end.