Comment on explain SQL? Or just a piece of code? Php anti-SQL Injection code

Comment on explain SQL? Or just a piece of code? Php anti-SQL Injection code

I recently saw a blog titled explain a piece of SQL? Or just a piece of code? ", The article illustrates how to use program code to write a join Table query with better readability and maintainability, but the reply opinions are inconsistent. I think the author has not made a better discussion at the theoretical level, I only replied to the post today and found that I could not reply, so I wrote this article separately.

Barrel Law

Table connection query should be avoided as much as possible. Although the SQL efficiency of the next table connection query is higher than that of the two for loops, we should know a lot of applications that rely on complex SQL queries, the database is easy to become a bottleneck, but the server where the application is located is relatively idle. At this time, the result of the application is to wait for the database to return the query result, and the overall time is longer, this is also the embodiment of the "barrel law" in the software. Therefore, the correct approach is to prevent short boards from occurring on each node of the system, without using a join Table query, we can distribute tables to different databases for database sharding and table sharding.Merge row QueryTo improve system resource utilization and program execution efficiency.

Of course, the conclusion above also has the premise that the network I/O for each query cannot become a bottleneck, otherwise it is more appropriate to execute the connection operation in the database, if intensive queries involve a large amount of IO each time, stored procedures should be used in this case. Therefore, whether to write in code or write SQL statements should be analyzed in detail.

28th Principle

According to the actual situation of most projects, 80% of queries are simple single-table queries and join-table queries, which are suitable for Using ORM, combined with the cache, the system efficiency can be greatly improved. The remaining 20% queries involve complex SQL statements and a large number of IO operations. In this case, you should directly use SQL statements or stored procedures, therefore, when selecting a data layer framework for a project, it must support both ORM and SQL. However, it should be in the form of Advanced Support for SQL, centralized management or SQL configuration, SQL-MAP functionality similar to the iBatis framework. If there are a large number of forms, you should also consider that such a data layer framework can support data control binding. So an excellent Data layer framework should have ORM, SQL-MAP, Data Controls function at the same time, there is a domestic SOD framework is recommended!