Create a MySQL index to greatly optimize the performance of a PHP application

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about indexing to greatly optimize the performance of a PHP application. Let's take a look at it together. I hope it will be helpful to everyone.

Cause

A friend of mine was going to do a project two months ago, and for the purpose of rapid online promotion, he directly purchased a company’s Source code and let the seller deploy it online. After seeing the source code, I directly said to my friend: I have been cheated. The quality of this source code is a bit poor, and there may be serious performance problems when the number of users is increased.

I have a basis for making such an evaluation:

• As a near-real-time application, the core code is written in PHP, and the concurrency of many scenarios is controlled through database table records. And repeated requests;

• PHP development is not a problem, but the other engineer does not seem to know that there is a CLI mode, but uses scheduled tasks (crontab) to achieve non-stop running of the program, so it is so vast Dozens of curl scheduled tasks are executed every minute;

• There are many class1.php and class1-1.php files in the code. It is difficult to know their existence at a glance. Purpose;

• There are a lot of codes for looping to read the database, and the naming rules are confusing.

Of course, code that can make money is good code (the other party makes money through these codes), so I don’t worry about it too much. The initial thought was that with a 4-core 8G configuration, it would be difficult to serve 10,000 customers, but 5,000 would be enough.

Turnaround

Just this week, I suddenly received frequent alarm text messages and emails from Alibaba Cloud, saying that the CPU usage was too high. Do you think that the marketing promotion is going well and the number of users has increased significantly? When I asked my friends, there were less than 300 users!

#At this time, I realized that the actual performance of this code was worse than I thought, and there were serious performance problems. According to this resource consumption rate, upgrading hardware is a bottomless pit, and performance optimization is the right way.

Performance Optimization

It has been two months since I got the code. I occasionally look at it in my spare time and already have a general understanding of its structure and main functions. Now that we have serious performance issues, it's time to try some performance optimizations.

In view of the fact that dozens of scheduled tasks are running continuously and continuously drive the system operation, the related functions of scheduled tasks are the first to be understood. Based on my own understanding, I first suspended more than twenty planned tasks that were no longer needed. After pausing useless scheduled tasks, the overall system CPU usage dropped to more than 60%, and the annoying reminder text messages and emails finally stopped. After waiting for a day, my friends did not report that the functions were affected, which shows that the idea and starting point are correct.

But with more than 200 users consuming resources like this, there must be something wrong. I logged into the server again when I was free today, executed the top command, and found that themysqlprocess has been occupying more than 200% of the CPU resources. After reading the source code, I know that there is a reason for MySQL's high usage and it is possible, but I still want to see why it consumes so many resources.

Log in to the MySQL server and check whether the slow log is enabled: show variables like '%slow%'; and find that the slow query log is enabled:

Continue Check the log and find that a certain SQL statement has always appeared in the log:

You can see that more than 380,000 rows of records were scanned when this SQL statement was executed. One of the two tables involved in the statement has more than 600 records, and the other has more than 40,000 records. This is equivalent to scanning the entire table with more than 40,000 records several times. No wonder it is extremely slow.

Then check the indexes of the two tables. Except for the auto-incremented id as the primary key, no other indexes are created. Use explain to execute the statement, which shows that no index is used:

Next, create indexes on the uid and session_id columns of the query conditions on the two tables. After the index creation is completed, the visible CPU usage and system load are reduced. Use explain again to execute the query statement. The index information has been used and the number of scanned rows has been greatly reduced:

After the above optimization, the current overall CPU usage of the application is 5% Around 15%, MySQL's CPU usage dropped from 4 to 0.3. Finally, there is no need to worry about performance issues for the time being. Even if the server configuration is reduced to 1 core CPU, it can still sustain it.

Further check the code and combine it with the logs to create indexes and modify some query statements. The CPU usage dropped to about 6%. Finally, we don’t have to worry about performance issues for the time being

Summary

In development projects, engineers must not only write "usable" code, but also "easy to use" code. In this example, by creating two indexes, the system performance can be greatly improved, which is to change the code from "usable" to "easy to use".

The performance optimization mentioned in this article focuses on operation and maintenance, and the performance optimization in the code has not been touched yet. But a general principle is correct: use more cache and reduce synchronous reads from slow IO devices as much as possible.

Recommended learning: "mysql video tutorial", "PHP video tutorial"

The above is the detailed content of Create a MySQL index to greatly optimize the performance of a PHP application. For more information, please follow other related articles on the PHP Chinese website!