This article starts at InfoQ Chinese Station。
Author: Munling (Dragon), fenng.Note: To reprint friends, please pay attention to the first author of this article!
This article is a summary of what he did after a dragon friend came to discuss it. Sorting in DB or sorting in the application is an interesting topic, dragon the first email has been summed up very well, I added a little advice. Now put it up and share it with you. This article was also submitted to the InfoQ Chinese Station。
Q: List The reasons to perform the sort in PHP better than the sort in MYSQL? To some instances that must be sorted in MySQL?
A: Generally speaking, the execution efficiency needs to consider the CPU, the memory and the hard disk load condition, assumes that the MYSQL server and the PHP server have already configured in the most appropriate way, The scalability of the system (scalability) and user-perceived performance (user-perceived performance) are the main goals we pursue. In the actual operation, the data in MYSQL is often in the way of HASH tables, btree, etc. storage in memory, the operation speed is very fast; at the same time, INDEX has been a number of predefined; In many applications, MySQL sorting is preferred. And in the application layer (PHP) sorting, also necessarily in memory, compared with MYSQL has the following advantages:
- 1. Considering the scalability and overall performance of the entire website, sorting in the application layer (PHP) will obviously reduce the load of the database, thus enhancing the expansion ability of the whole website. and the database sorting, in fact, the cost is very high, consuming memory, CPU, if the concurrency of a lot of, DB is easy to the bottleneck.
- 2, if the application layer (php) and MySQL between the data in the middle tier, reasonable use, PHP will have better benefits.
- 3, PHP in memory data structure specifically for the specific application to design, more concise than the database, efficient;
- 4, PHP does not consider the data disaster recovery problem, you can reduce this part of the operation loss;
- 5, PHP does not exist the problem of locking the table;
- 6, MySQL sorting, request and result return also need to go through the network connection, and PHP can be sorted directly after the return, reducing the network IO.
As for execution speed, the difference should not be very large, unless the application design is problematic, resulting in a large number of unnecessary network IO. In addition, the application layer should be aware of the PHP Cache settings, if the excess will report internal errors, at this time to evaluate according to the application, or adjust the cache. The specific choice will depend on the specific application.
List some of the better things to do in PHP:
- 1, the data source is not in MYSQL, there are hard drives, memory or requests from the network;
- 2, the data exists in MYSQL, the amount is small, and there is no corresponding index, at this time the data out to use PHP to sort faster;
- 3, the data source from a number of MySQL server, at this time from a number of MySQL to remove data, and then in PHP to sort faster;
- 4, in addition to MYSQL, there are other data sources, such as hard disk, memory or requests from the network, etc., at this time is not suitable for this data into MYSQL after sorting;
List some instances that must be sorted in MYSQL:
- 1, MYSQL already exists in this sort of index;
- 2, MYSQL data volume is large, and the result set requires a small subset of them, such as 1000000 rows of data, take top 10;
- 3, for a sorting, multiple invocation of the situation, such as statistical aggregation of the situation can be provided to different services to use, then the ranking in MYSQL is preferred. In addition, for the depth of data mining, the common practice is to finish sorting in the application layer and other complex operations, the results are stored in MySQL can be used for many times.
- 4, no matter where the data source from, when the amount of data to a certain scale, due to occupy memory/cache relationship, no longer suitable for the sort of PHP; At this time the data copy, import or exist MYSQL, and INDEX optimization, is superior to PHP. However, it would be better to use Java, or even C + +, to handle such operations. [Some data that is similar to large data aggregation or aggregation is not worth it in the client order.] Of course, there are similar search engine ideas to solve similar applications. ]
From the overall consideration of the site, it is necessary to add human and cost considerations. If the site size and load is small, and manpower Limited (the number and ability may be limited), at this time in the application layer (PHP) do a lot of the development and debugging work, time-consuming, not worth it, as in DB processing, simple and fast. For large-scale web sites, electricity, server costs are very high, in the system structure of the budget, can save a lot of costs, is the company's sustainable development is necessary; At this time, if the application layer (PHP) can be sorted and meet business needs, as far as possible in the application layer.
