This article is starting at InfoQ Chinese station. Author: Munling (Dragon), fenng. Note: to reprint the friend please notice the first author of this article!
This article is a summary of Dragon friends to post the discussion after he did. Sorting in DB or sorting in the application is a very interesting topic, dragon in the first email has actually been summed up very well, I added a little bit of advice. Now put it up and share it with everyone. This article was also submitted to the InfoQ Chinese station.
Q: List the reasons for sorting in PHP better than in MYSQL? Give some examples that must be sorted in MySQL?
A: In general, the execution efficiency needs to take into account the load of CPU, memory and hard disk, assuming that the MYSQL server and PHP server are configured in the most appropriate way, then the system scalability (Scalability) and user-perceived performance (user-perceived performance) is the main goal we pursue. In the actual operation, the data in MYSQL is often stored in memory in the form of HASH tables, BTREE and so on, the operation speed is very fast, and INDEX has been pre-sorted; In many applications, MYSQL sequencing is preferred. Sorting in the application layer (PHP) is bound to be in memory, which has the following advantages over MYSQL:
- 1, consider the entire Web site scalability and overall performance, in the Application layer (PHP) sorting will significantly reduce the load of the database, thereby enhancing the expansion of the entire site. And the database sort, actually the cost is very high, consumes the memory, the CPU, if the concurrency sorts many, the DB is easy to the bottleneck.
- 2, if there is a data middle tier between the application layer (PHP) and MySQL, reasonable utilization, PHP will have better benefits.
- 3, PHP in-memory data structure specifically for specific applications to design, more concise and efficient than the database;
- 4, PHP does not consider the data disaster recovery problem, can reduce this part of the operating loss;
- 5, PHP does not exist the table lock problem;
- 6, MySQL sorting, request and result return also need to be done through the network connection, and in PHP after sorting can be returned directly, reduce the network IO.
As for execution speed, the difference should not be great unless the application design is problematic, resulting in a large amount of unnecessary network IO. In addition, the application layer should be aware of the PHP Cache settings, if the outside will report internal errors, at this point to be evaluated according to the application, or adjust the Cache. The specific choice will depend on the specific application.
List some of the more excellent things to do in PHP:
- 1, the data source is not in MYSQL, there is hard disk, memory or requests from the network, etc.;
- 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 sorting faster;
- 3, the data source from the multiple MySQL server, at this time to extract data from multiple MySQL, 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, and so on, it is not appropriate to put these data into MYSQL after sorting;
List some instances that must be sorted in MYSQL:
- 1. This sort index already exists in MYSQL;
- 2, MYSQL data volume is large, and the result set needs a small subset of them, such as 1000000 rows of data, take top 10;
- 3, for a single order, multiple calls, such as statistical aggregation of the situation, can be provided to different services to use, then in MYSQL sorting is preferred. In addition, for data depth mining, it is usually done in the application layer of sorting and other complex operations, the results are stored in MySQL, easy to use multiple times.
- 4, no matter where the data source from, when the amount of data large to a certain scale, due to the memory/cache relationship, no longer suitable for PHP sorting, the data copied, imported or existing MYSQL, and INDEX optimization, is better than PHP. However, it is better to use Java or even C + + to handle this kind of operation. [Some data, such as big data aggregation or aggregation, are not worth the hassle of client sequencing.] Of course, it is also useful to use a search engine-like approach to solve similar applications. ]
From the overall consideration of the website, it is necessary to add manpower and cost considerations. If the site size and load is small, and the manpower is limited (the number and capacity may be limited), at this time in the application layer (PHP) to do a lot of development and debugging work, time-consuming, not worth the candle; For large-scale website, power, server cost is very high, in the system architecture of budget, can save a lot of money, is the company's sustainable development is necessary; If you can sort on the application layer (PHP) and meet the business requirements, try to do it at the application level.
The above describes the temporary Internet files PHP to perform sorting and sorting in MySQL, including the temporary Internet files aspects of the content, I hope that the PHP tutorial interested in a friend helpful.
