Php code optimization improves MySQl running efficiency. If you encounter similar problems, refer. Several websites of the company are deployed on virtual hosts in the United States. the mysql service on the server suddenly crashes every day, and then recovers after a while, it is suspected that the server is automatically terminated because it exceeds the cpu usage limit, but the traffic on the server is actually very small. As a result, I contacted the server provider's aosan customer service in India earlier and wanted to see if other users were killed together, the long-haired chest was vowed not to solve their problems. Hanging is not a problem, so I had to check it myself. Fortunately, I can access the information_schema database and read it. it's okay. The data in user_statistics shows that one of our mysql users is in busy_time, cpu_time and other indicators are not high enough. I did my own thing. Fortunately, aosan did not find it. So I quickly checked the program. The previous website program was not implemented by me, but I knew there were many problems in it and there were problems with the architecture and implementation. However, there were not many pages, and the code was mixed with html, it seems like the past is not dead. (in this case, we are particularly aware of the advantages of mvc.) at ordinary times, we can work together to run it. there is no access volume.
Since it is a heavy burden on mysql, find this first. run a local website image and add it in my. ini.
The code is as follows:
[Mysqld]
Log = "d:/temp/mysql. log"
Log_slow_queries = "d:/temp/mysql_slow.log"
Long_query_time = 1
This directory must already exist. Restart the mysql service and you will be able to record it.
I was surprised when I checked the SQL records. the number of queries was amazing. on any page, there were dozens of SQL queries, with thousands more!
For the forum, the number of database queries on a page is 10 times, and the cache usage can be lower. In this case, it is equivalent to dozens of times the original burden?
No one can write hundreds of queries with perseverance, so it must be a loop query. The SQL statement also indicates this. If you know the reason, you can find the relevant page and change the circular query. for example, if you have a page that displays the number of articles under all regions and categories, first, we will not consider database structure optimization. as far as the program is concerned, it is probably like this.
The code is as follows:
$ Sql1 = "SELECT aid, count (*) as cc FROM pz_content WHERE uid = $ uid group by aid ";
$ Rs1 = $ db-> query ($ sql1 );
If (is_array ($ rs1 )){
Foreach ($ rs1 as $ r1 ){
Output...
Echo id2name ($ r1-> aid );
}
}
............
Function id2name ($ aid)
{
$ SQL = "select ename from pz_area_a where aid_a =". $ id;
$ Result = mysql_query ($ SQL );
$ Row = mysql_fetch_object ($ result );
Return $ row-> ename;
}
Regardless of the error tolerance of the code, you can see the implementation. he first reads the user's articles and groups and counts by region ID, then, the name of each region is output to each region. Therefore, if there are 1 million regions, we need to query 1 million times. Put a timing code and read it. the memory consumption is about 6 MB, the execution time is 16 seconds, and the total number of queries is 1001.
In fact, here is something that can be done with an SQL statement without loops.
The code is as follows:
$ Sql1 = "select pz_area.aid, pz_area.ename, tb1.cc from pz_area right join (SELECT aid, count (*) as cc FROM pz_content WHERE uid = $ uid group by aid) as tb1 on pz_area.aid = tb1.aid ";
$ Rs1 = $ db-> query ($ sql1 );
If (is_array ($ rs1 )){
Foreach ($ rs1 as $ r1 ){
Output...
Echo $ r1-> ename;
}
}
The problem can be solved. Re-run, memory consumption is almost the same, query once, CPU execution time is only 647 milliseconds, 26 times worse than the original! Looking at it again, we found that the pz_content table has a large number of records, which are often divided by regional queries, but there is no index in the past. By the way, you can add an index to aid, and the execution time is reduced to 432 milliseconds.
I have forgotten this page. first come here and continue next time.
