Detailed explanation of comparison and usage conditions of sorting in database_PHP tutorial

Assuming that both the MySQL server and the PHP server have been configured in the most suitable way, then the system scalability (Scalability) and user-perceived Performance (User-perceived Performance) are the main goals we pursue. In actual operation, data in MYSQL is often stored in memory in HASH tables, BTREE, etc., and the operation speed is very fast; at the same time, INDEX has already performed some pre-sorting; in many applications, MySQL sorting is the first choice.

PHP has the following advantages compared with MySQL:

1. Considering the scalability and overall performance of the entire website, sorting in the application layer (PHP) will obviously reduce the load on the database, thus Improve the scalability of the entire website. In fact, the cost of sorting in the database is very high, consuming memory and CPU. If there are many concurrent sortings, the DB can easily reach a bottleneck.

　2. If there is a data middle layer between the application layer (PHP) and MYSQL, and it is properly utilized, PHP will have better benefits.

　3. PHP’s in-memory data structure is specially designed for specific applications and is more concise and efficient than databases;

　4. PHP does not need to consider data disaster recovery issues and can reduce this part Operation loss;

5. PHP does not have table locking problems;

6. In MySQL, sorting, requesting and result return also need to be carried out through a network connection, while in PHP after sorting You can return directly, reducing network IO.

As for the execution speed, the difference should not be huge, unless there is a problem with the application design, causing a lot of unnecessary network IO. In addition, the application layer should pay attention to PHP's Cache settings. If it is exceeded, an internal error will be reported; at this time, it is necessary to evaluate or adjust the Cache according to the application. The specific choice will depend on the specific application.

List some situations where sorting in PHP is better:

1. The data source is not in MySQL, there are hard disks, memory or requests from the network;

2 , the data is stored in MySQL, the amount is not large, and there is no corresponding index. At this time, it is faster to take out the data and sort it with PHP;

　3. The data source comes from multiple MySQL servers. At this time, it comes from multiple MySQL servers. It is faster to retrieve data from MySQL and then sort it in PHP;

4. In addition to MySQL, there are other data sources, such as hard disk, memory or requests from the network. It is not suitable to store these data at this time. Sort after entering MySQL;

List some examples that must be sorted in MySQL:

1. This sorted index already exists in MySQL;

2. In MySQL The amount of data is large, and the result set requires a very small subset of it; for example, if there are 1,000,000 rows of data, take the TOP 10;

3. For one sorting and multiple calls, such as statistical aggregation, Can be provided to different services, then sorting in MySQL is preferred. In addition, for deep data mining, the usual approach is to complete complex operations such as sorting at the application layer, and then store the results in MySQL for easy use multiple times.

4. No matter where the data source comes from, when the data volume reaches a certain scale, it is no longer suitable for sorting in PHP due to the memory/Cache occupied; at this time, the data should be copied, imported or stored in MySQL , and using INDEX optimization is better than PHP. However, it would be better to handle such operations in Java or even C++. For some data, such as aggregation or summary of large data sets, sorting on the client side outweighs the gain and loss. Of course, ideas similar to search engines can also be used to solve similar application situations.

From the overall consideration of the website, manpower and cost must be considered. If the website size and load are small, and the manpower is limited (the number of people and capabilities may be limited), then sorting at the application layer (PHP) requires a lot of development and debugging work, which is time-consuming and not worth the loss; it is better to process it in DB, Simple and fast. For large-scale websites, the cost of electricity and servers is very high. Careful planning on system architecture can save a lot of costs, which is necessary for the company's sustainable development. At this time, if the application layer (PHP) can be sorted and meet business needs, Try to do it at the application layer.

That’s all the relevant knowledge about sorting in PHP and sorting in MySQL. I hope this introduction can be helpful to you!

http://www.bkjia.com/PHPjc/325125.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/325125.htmlTechArticleAssuming that both the MySQL server and the PHP server have been configured in the most suitable way, then the scalability of the system (Scalability ) and user-perceived Performance are what we pursue...