Today, Baidu, see an article on the MySQL database optimization experience, read probably understand, become their own
- optimize your MySQL query cache queries on the MySQL server to enable high-speed query caching. Having the database engine quietly handled in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is fairly fast if the result is extracted from the cache. But the main problem is that it is so easily hidden that most of our programmers will ignore it. In some processing tasks, we can actually prevent the query cache from working. 1.//query cache does not work 2. $r = mysql_query ("Select username from user WHERE signup_date >= curdate ()"); 3.4. Query Cache works! 5. $today = Date ("y-m-d"); 6. $r = mysql_query ("Select username from user WHERE signup_date >= ' $today '"); 7.8. Query cache does not work 9. $r = mysql_query ("Select username from user WHERE signup_date >= curdate ()"); 10.11. Query Cache works! $today = Date ("y-m-d"); $r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");
- Use explain to make your select query clearer using the Explain keyword is another MySQL optimization trick that lets you understand what query operations MySQL is doing, which can help you find the bottleneck, And it shows where the query or table structure is out of the question. The results of the explain query can tell you which indexes are being referenced, how the tables are scanned and sorted, and so on. Implement a select query (preferably a more complex one, with the Joins method), add your keyword explanation in it, here we can use phpMyAdmin, he will tell you the results in the table. For example, if I was forgetting to add a column to an index when I was executing joins, explain could help me find the problem. After you add an index to group_id field
- use limit 1 to get unique rows Sometimes, when you want to query a table, you know that you only need to look at a single line. A very unique record that you might go to, or just check the number of records that exist, they all satisfy your WHERE clause. In this case, adding a limit of 1 will make your query more efficient. This allows the database engine to discover that only 1 will stop scanning, rather than scanning the entire table or index. 1.//Does I have any users from Alabama? 2.//What isn't to do:3. $r = mysql_query ("SELECT * from user WHERE state = ' Alabama '"); 4. if (mysql_num_rows ($r) > 0) {5.//... 6.} 7. Much better:8. $r = mysql_query ("Select 1 from user WHERE state = ' Alabama ' LIMIT 1"); 9. if (mysql_num_rows ($r) > 0) {10.//... 11.}
- Retrieving a field index in an index is not just a primary key or a unique key. If you want to search for any column in the table, you should always point to the index.
- ensure that the index of the connection is the same type if the application contains multiple connection queries, you need to make sure that the columns you link are indexed on both sides of the table. This affects how MySQL optimizes internal join operations. Additionally, the columns that are joined must be of the same type. For example, if you join a decimal column while adding an int column from another table, MySQL will not be able to use at least one of these metrics. Even if the character encoding must be the same as the string type. 1.//Looking for companies in my State 2. $r = mysql_query ("Select Company_Name from Users 3. Left JOIN companies on (users.state = companies.state) 4. WHERE users.id = $user _id "); 5.6. Both state columns should is indexed 7. And they both should be the same type and character encoding 8. Or MySQL might do full table scans
- do not use the by RAND () command This is a trap for many novice programmers to fall in. You may have made a terrible peace unconsciously. This trap is created when you use the by RAND () command. If you really need to show your results randomly, there are many better ways to achieve them. Admittedly, this will require more code, but it avoids the performance bottleneck. The problem is that MySQL may execute the by RAND () command for each individual row in the table (which consumes the processor's processing power), and then gives you just one row back. 1.//what isn't to do:2. $r = mysql_query ("Select username from the user ORDER by RAND () LIMIT 1"); 3.//much better:4. $r = mysql_query ("SELECT count (*) from user"); 5. $d = Mysql_fetch_row ($r); 6. $rand = Mt_rand (0, $d [0]-1); 7.8. $r = mysql_query ("Select username from user LIMIT $rand, 1");
- Try to avoid the more data that the SELECT * Command reads from the table, and the query becomes slower. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers. Always specify the columns you need, which is a very good habit. 1.//Not preferred 2. $r = mysql_query ("SELECT * from user WHERE user_id = 1"); 3. $d = Mysql_fetch_assoc ($r); 4. Echo "Welcome {$d [' username ']}"; 5.//better:6. $r = mysql_query ("Select username from user WHERE user_id = 1"); 7. $d = Mysql_fetch_assoc ($r); 8. Echo "Welcome {$d [' username ']}"; 9.//The differences is more significant with bigger result sets
- get advice from procedure analyse () PROCEDURE analyse () gives you some advice on MySQL's column structure analysis and the actual data in the table. If the actual data already exists in your table, it can serve your major decision-making.
- prepared statements prepared by the statement can be useful in terms of performance optimization and security. The prepared statement, which filters the already bound variables by default, can protect the application effectively against SQL injection attacks. Of course you can also manually filter, but because most programmers forgetful character, it is difficult to achieve results. 1.//Create a prepared statement 2. if ($stmt = $mysqli->prepare ("Select username from user WHERE state=?")) {3.//Bind Parameters 4. $stmt->bind_param ("s", $state); 5.//execute 6. $stmt->execute (); 7.//Bind result variables 8. $stmt->bind_result ($username); 9.//Fetch value 10. $stmt->fetch (); One. printf ("%s is from%s\n", $username, $state); $stmt->close (); 13.}
- store IP addresses as unsigned integers Many programmers do not realize that they can store IP addresses as integers when they create a varchar (15). When you have an int type, you only occupy 4 bytes of space, which is a fixed-size field. You must make sure that the column you are manipulating is of type unsigned int, because the IP address will use the 32-bit unsigned integer. 1. $r = "UPDATE users SET IP = Inet_aton (' {$_server[' remote_addr ']} ') WHERE user_id = $user _id";
The top ten MySQL optimization tips are introduced here. The above content is transferred from Baidu Experience
MySQL Database optimization
