PHP and MySQL. This article was first published on the InfoQ Chinese site. Author: dragon, Fenng. Note: The first author of this article should be noted! This article was first published on the InfoQ Chinese site after a friend of dragon came to discuss it via email.
Author: dragon, Fenng.Note: Please Note the first author of this article if you want to reprint it!
This article is a summary of dragon's discussion. Sorting in DB or in applications is a very interesting topic. The first dragon email is actually well summarized. I just added some suggestions. Now, let's share it with you. This article was also contributed to the InfoQ Chinese site.
Q: listed in PHPWhy is the execution of sorting in MYSQL better than that in MYSQL? For some instances that must be sorted in MYSQL?
A: Generally, the execution efficiency needs to take into account the CPU, memory, hard disk and other loads, assuming that the MYSQL server and PHPServers have been configured in the most appropriate way, so Scalability and User-perceived Performance are our main goals. In actual operation, data in MYSQL is often stored in memory in HASH tables, BTREE, and other ways, and the operation speed is very fast. at the same time, indexes have been pre-sorted. in many applications, MYSQL sorting is the first choice. However, sorting in the application layer (PHP) must also be performed in the memory. compared with MYSQL, it has the following advantages:
- 1. Considering the scalability and overall performance of the entire website, sorting at the application layer (PHP) will significantly reduce the database load and thus improve the scalability of the entire website. In fact, the cost of database sorting is very high, which consumes memory and CPU. if there is a lot of concurrent sorting, it is easy for DB to reach the bottleneck.
- 2. ifThere is also a data middle layer between MYSQL and PHP, which can be used properly, so PHP will have better benefits.
- 3. the data structure of PHP in the memory is specially designed for specific applications, which is more concise and efficient than the database;
- 4. PHP does not need to consider data disaster recovery issues, which can reduce operation loss;
- 5. PHP does not have the table lock problem;
- 6. sorting in MYSQL, requests and returned results still need to be performed through a network connection. after sorting in PHP, you can directly return results, reducing network I/O.
As for the execution speed, there should be no big difference, unless there is a problem with the application design, resulting in a large number of unnecessary network IO. In addition, the application layer should pay attention to the PHP Cache settings. if it exceeds the threshold, an internal error will be reported. in this case, make an evaluation based on the application or adjust the Cache. The specific selection depends on the specific application.
List some PHPBetter sorting:
- 1. the data source is not in MYSQL, and there are hard disks, memory, or network requests;
- 2. data is stored in MYSQL, with a small amount of data and no corresponding indexes. in this case, the data is retrieved and sorted in PHP faster;
- 3. data sources come from multiple MYSQL servers. in this case, data is retrieved from multiple MYSQL servers and sorted in PHP faster;
- 4. in addition to MYSQL, there are other data sources, such as hard disk, memory, or network requests. it is not suitable to store the data in MYSQL and sort it again;
List some instances that must be sorted in MYSQL:
- 1. the sorting index already exists in MYSQL;
- 2. the data volume in MYSQL is large, and the result set requires a small subset. for example, for the first 1000000 rows of data, TOP 10 is used;
- 3. for one-time sorting and multiple calls, such as statistical aggregation, which can be used by different services, sorting in MYSQL is the first choice. In addition, for deep data mining, complex operations such as sorting are usually performed at the application layer, and the results can be saved to MYSQL for multiple use.
- 4. no matter where the data source comes from, when the data volume reaches a certain scale, it is no longer suitable for PHP due to the memory/Cache usage.Data is copied, imported, or MYSQL exists, and optimized with INDEX, which is better than PHP.. However, it is better to use Java or even C ++ to handle such operations. [Some data similar to big dataset aggregation or summary is not worth the cost of sorting on the client side. Of course, similar search engines are also used to solve similar applications.]
From the overall perspective of the website, you must consider manpower and costs. If the website size and load are small, and the manpower is limited (the number of people and capabilities may be limited), the sorting at the application layer (PHP) requires little development and debugging, which takes time and outweighs the loss; it is better to process in DB, which is simple and fast. For large-scale websites, the cost of electricity and servers is very high, and careful calculation of the system architecture can save a lot of cost, which is necessary for the company's sustainable development; at this time, if the application layer (PHP).
InfoQ Chinese site. Author: dragon, Fenng. Note: The first author of this article should be noted! This article was discussed by a dragon friend after he came to discuss it...
