Shulou(Shulou.com)05/31 Report--

This article mainly explains "detailed explanation of ten optimization techniques of MySQL database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the detailed explanation of the Ten Optimization techniques of MySQL Database.

1. Optimize your MySQL query cache

Query caching can be enabled by querying on the MySQL server. Letting the database engine process quietly in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is quite fast if the result is extracted from the cache.

But the main problem is that it is so easy to hide that most of our programmers ignore it. In some processing tasks, we can actually prevent query caching from working.

Double-click the code to select all 1 2 3 4 5 6 7 8 9 10 query cache does NOT work$r / query cache does NOT work$r = mysql_query ("SELECT username FROM user WHERE signup_date > = CURDATE ()"); / / query cache worksmatching codes = date ("Y-m-d"); $r = mysql_query ("SELECT username FROM user WHERE signup_date > ='$today'"); / / query cache does NOT work$r = mysql_query ("SELECT username FROM user WHERE signup_date > = CURDATE ()") / / query cache worksqualified employees = date ("Y-m-d"); $r = mysql_query ("SELECT username FROM user WHERE signup_date > ='$today'")

2. Use EXPLAIN to make your SELECT query clearer

Using the EXPLAIN keyword is another MySQL optimization technique that allows you to understand what query operations MySQL is doing, which can help you identify bottlenecks and show what went wrong with the query or table structure.

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 joins mode) and add your keyword explanation to it. Here we can use phpMyAdmin, which will tell you the results in the table. For example, if I forget to add a column to an index when I am executing joins, EXPLAIN can help me find the problem.

After adding the index to group_id field:

3. Use LIMIT 1 to get the unique line

Sometimes, when you want to query a table, you know you only need to look at a row. You may go to a very unique record, or just happen to check the number of records that exist, and they all satisfy your WHERE clause.

In this case, adding a LIMIT 1 will make your query more efficient. In this way, the database engine will stop scanning when it finds only 1, instead of scanning the entire table or index.

Double-click the code 1 2 3 4 5 6 7 8 9 10 11 do I have any users from Alabama?// what NOT to do:$r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'"); if (mysql_num_rows ($r) > 0) {/ /...} / / much better:$r = mysql_query ("SELECT 1 FROM user WHERE state =' Alabama' LIMIT 1"); if (mysql_num_rows ($r) > 0) {/ /...}

4. Search fields in the index

An index is not just a primary or unique key. If you want to search for any column in the table, you should always point to the index.

5. Make sure that the connected indexes are of the same type

If the application contains multiple join queries, you need to make sure that the columns you link are indexed on both sides of the table. This affects how MySQL optimizes inner join operations.

In addition, the added columns must be of the same type. For example, if you add a DECIMAL column while adding an int column in another table, MySQL will not be able to use at least one of these metrics. Even if the character encoding must be of the same string type.

Double-click the code 1 2 3 4 5 6 7 looking for companies in my state$r / mysql_query ("SELECT company_name FROM usersLEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id"); / / both state columns should be indexed// and they both should be the same type and character encoding// or MySQL might do full table scans

Do not use the BY RAND () command

This is a trap that many novice programmers will fall into. You may have unwittingly created a terrible peace. This trap is created when you use the BY RAND () command.

If you really need to display your results at random, there are many better ways to achieve it. It's true that this requires more code, but it avoids performance bottlenecks. The problem is that MySQL may execute the BY RAND () command for each separate row in the table (which consumes the processor's processing power) and then returns you only one row.

Double-click the code to select 1 2 3 4 5 67 what NOT to do:$r / what NOT to do:$r = mysql_query ("SELECT username FROM user ORDER BY RAND () LIMIT 1"); / / much better:$r = mysql_query ("SELECT count (*) FROM user"); $d = mysql_fetch_row ($r); $rand = mt_rand (0memento [0]-1); $r = mysql_query ("SELECT username FROM user LIMIT $rand, 1")

7. Avoid SELECT * commands as much as possible

The more data you read from the table, the slower the query becomes. He increases the amount of time the disk needs to operate, or if the database server is separate from the WEB server. You will experience a very long network delay simply because data is transferred unnecessarily between servers.

It is a very good habit to always specify the columns you need.

Double-click code 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 not preferred$r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc ($r); echo "Welcome {$d ['username']}"; / / better:$r = mysql_query ("SELECT username FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc ($r); echo "Welcome {$d [' username']}"; / / the differences are more significant with bigger result sets

8. Get advice from PROCEDURE ANALYSE ()

PROCEDURE ANALYSE () allows MySQL's column structure analysis and the actual data in the table to give you some advice. If you already have actual data in your table, it can serve your important decisions.

9. Prepared statements

Prepared statements can help you both in terms of performance optimization and security.

Prepared statements can effectively protect the application from SQL injection attacks by filtering bound variables by default. Of course, you can also filter manually, but because of the forgetful character of most programmers, it is difficult to achieve results.

Double click the code to select all 12 3 4 5 6 7 9 10 11 12 13 create a prepared statementif ($stmt = $mysqli- > prepare ("SELECT username FROM user WHERE state=?")) {/ / bind parameters$stmt- > bind_param ("s", $state); / / execute$stmt- > execute (); / / bind result variables$stmt- > bind_result ($username); / / fetch value$stmt- > fetch (); printf ("% s is from% sn", $username, $state); $stmt- > close ();}

10. Store IP addresses as unsigned integers

Many programmers create a VARCHAR (15) without realizing that they can store IP addresses as integers. When you have an INT type, you only take up 4 bytes of space, which is a fixed size field.

You must make sure that the column you are operating on is of type UNSIGNED INT, because the IP address will use 32-bit unsigned integer.

Double-click the code to select 1$ r = "UPDATE users SET ip = INET_ATON ('{$_ SERVER ['REMOTE_ADDR']}') WHERE user_id = $user_id"; at this point, I believe you have a deeper understanding of the "detailed understanding of the ten optimization techniques of MySQL database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.