Reply content:
My principle is: can put in PHP all put into PHP, DB (such as MySQL) only put data, not put logic (foreign KEY constraints, stored procedures, etc.).
Use MySQL, but remember which database you use can be changed at any time, so especially don't use some of the logic-related features that MySQL supports. Specific problems specific analysis of the current performance bottlenecks are seen in the database layer, the horizontal expansion of the relational database although in the development, but has not been widely accepted stability scheme, so in recent years, NoSQL will be so rapid development. The scale-out of application server is much more mature, and adding application server only requires adding a new machine to the Load Balancer list on the Web server.
Since database scale-out is so much harder than application server scale-out, should you try to put the logic into the code rather than the SQL to try to reduce the pressure on the database? Let's take an example:
For example, the number of visits, there are tbl_hour in the day per hour per user visits, such as Tbl_hour table has three columns: username, hour, visit_couter.
At 1 o'clock in the morning each day, to sums the number of visits per user for the preceding days, generate the total number of day visits per user, as shown in table Tbl_day, with three columns: username, datename, visit_counter.
The total number of days visited for the previous day can now be calculated using the program, or you can use SQL to calculate:
1. Remove all data from the previous day (SELECT * from Tbl_hour WHERE hour betwen last_day_start and Last_day_end), using the program to calculate the number of accesses per user;
2. Calculate the number of accesses per user for the previous day directly through SQL (SELECT username,
sum (visit_counter)From Tbl_hour WHERE hour betwen last_day_start and Last_day_end
GROUP by username
)
Which one would you choose?
Let me choose, if tbl_hour the day before the data volume is particularly large, such as millions or above, or to choose their own calculation it; If the magnitude is not too large, it will be easier to make SQL calculations directly.
How do people choose? Listen to your suggestions. In fact, put logic in the program inside also divided into three kinds of situations.
The first is weaker, invoking command-line commands in the programming language system, which is not recommended, is a last resort. For example, DB2 's load only C API, in Python and Perl do not implement the load module, it can only use the command line.
The second is to use the Language database module to operate the database, in essence, the calculation is placed on the database. It just encapsulates the logic in the program. This is not a difference in efficiency and stored procedures, but because the characteristics of stored procedures are certainly not strong and many, in general, is better than the use of stored procedures.
The third is to take the data out of the database and use the language to handle it in memory. This calculates the pressure on the machine running the program. and is only suitable for business computing of small data volumes. If it involves a few large tables, it is impossible to use this.
In the development process, the use of programs must be better than with stored procedures. The various versions of the program are more mature than the stored procedure.
There is also the case that if you are using a compiled language such as C, and there are frequent changes in logic, there are cases where you put a part in a stored procedure. Say a little bit about the feeling:
Business logic, if encapsulated in the stored procedure, skip the program release time, as long as the SP, and then update the database, the program does not need to modify, it will be much simpler.
In particular, many compiled languages, each release, is disgusting ~~~~~~ personal feeling code maintenance is too much database maintenance.
