MySQL Database optimization summary (experience)

1. Optimize your MySQL query cache
Querying on the MySQL server enables 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.

Copy CodeThe code is as follows:
Query cache does not work
$r = mysql_query ("Select username from user WHERE signup_date >= curdate ()");
Query Cache works!
$today = 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 works!
$today = 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 trick that lets you understand what the MySQL query is doing, which can help you find out where the bottleneck is, and shows where the query or table structure is going wrong.

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

3. Use limit 1 to obtain a unique row
Sometimes, when you want to query a table, you know you just need to look at one 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.

Copy CodeThe code is as follows:
Does I have any users from Alabama?
What isn't 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. Retrieving fields in the index
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.

5. Ensure that the index of the connection is the same type
If your 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.

Copy CodeThe code is as follows:
Looking for companies in my state
$r = mysql_query ("Select Company_Name from Users"
Left 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

6. Do not use the by RAND () command
This is a trap that many novice programmers will fall into. 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.

Copy CodeThe code is as follows:
What isn't to do:
$r = mysql_query ("Select username from the user ORDER by RAND () LIMIT 1");
Much better:
$r = mysql_query ("SELECT count (*) from user");
$d = Mysql_fetch_row ($r);
$rand = Mt_rand (0, $d [0]-1);
$r = mysql_query ("Select username from user LIMIT $rand, 1");

7. Try to avoid the SELECT * command
The more data is read from the table, the more slowly the query becomes. 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.

Copy CodeThe code is as follows:
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 is more significant with bigger result sets

8. 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.



9. Prepared statements
Prepared statements can be useful both 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.

Copy CodeThe code is as follows:
Create a prepared statement
if ($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%s\n", $username, $state);
$stmt->close ();
}

10. Store the IP address as an unsigned integer
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.
$r = "UPDATE users SET IP = Inet_aton (' {$_server[' remote_addr ']} ') WHERE user_id = $user _id";

11. Always set an ID for each table
We should set an ID for each table in the database as its primary key, and the best is an int type (recommended to use unsigned), and set the automatically added Auto_increment flag.
Even if you have a field in the users table that has a primary key called "email", you don't have to make it a primary key. Use the varchar type to degrade performance when the primary key is used. In addition, in your program, you should use the ID of the table to construct your data structure.
Also, under the MySQL data engine, there are some operations that need to use primary keys, in which case the performance and settings of the primary key become very important, such as clustering, partitioning ...
In this case, there is only one exception, which is the "foreign key" of the "association table", that is, the primary key of the table, which consists of the primary key of several other tables. We call this the "foreign key". For example: There is a "student table" has a student ID, there is a "curriculum" has a course ID, then, "Score table" is the "association table", which is associated with the student table and curriculum, in the score table, student ID and course ID is called "foreign key" it together to form a primary key.

12. Use enum instead of varchar
The enum type is very fast and compact. In fact, it holds the tinyint, but it appears as a string on its appearance. In this way, using this field to make a list of options becomes quite perfect.
If you have a field such as "gender", "Country", "nation", "state" or "department", you know that the values of these fields are limited and fixed, then you should use Enum instead of varchar.
MySQL also has a "suggestion" (see article tenth) to show you how to reorganize your table structure. When you have a varchar field, this suggestion will tell you to change it to an enum type. With procedure analyse () you can get advice.

13. Get advice from procedure analyse () p programmer Station
PROCEDURE analyse () will let MySQL help you analyze your fields and their actual data, and will give you some useful advice. These suggestions will only become useful if there is actual data in the table, because it is necessary to have data as a basis for making some big decisions.
For example, if you create an int field as your primary key, but there is not much data, then PROCEDURE analyse () suggests that you change the type of the field to Mediumint. Or you use a varchar field, because there is not much data, you might get a suggestion that you change it to an enum. These suggestions are probably because the data is not enough, so the decision-making is not accurate.
In phpMyAdmin, you can view these suggestions by clicking "Propose table Structure" while viewing the table.

It is important to note that these recommendations only become accurate when the data in your table is getting more and more. Keep in mind that you are the one who will make the final decision.

14. Use NOT NULL PHP programmer station whenever possible
Unless you have a very special reason to use null values, you should always keep your fields not NULL. This may seem a bit controversial, please look down.
First, ask yourself how big the difference is between "Empty" and "null" (if it is int, that is 0 and null)? If you think there is no difference between them, then you should not use NULL. (You know what? In Oracle, NULL and empty strings are the same!)
Do not assume that NULL does not require space, that it requires extra space, and that your program will be more complex when you compare it. Of course, this is not to say that you cannot use NULL, the reality is very complex, there will still be cases where you need to use a null value.
Here is an excerpt from MySQL's own documentation:

Prepared statements
Prepared statements is much like a stored procedure, a collection of SQL statements running in the background, and we can derive many benefits from using Prepared statements, whether it's a performance issue or a security issue.

Prepared statements can check some of the variables you've bound so that you can protect your program from "SQL injection" attacks. Of course, you can also manually check these variables, however, manual checks are prone to problems and are often forgotten by programmers. When we use some framework or ORM, this problem is better.

In terms of performance, this gives you a considerable performance advantage when the same query is used multiple times. You can define some parameters for these prepared statements, and MySQL will parse only once.

While the latest version of MySQL in the transmission prepared statements is using the binary situation, this makes the network transfer very efficient.
Of course, there are some cases where we need to avoid using prepared statements because it does not support query caching. But it is said that after version 5.1 was supported. To use prepared statements in PHP, you can view its user manual: Mysqli extension or using the database abstraction layer, such as PDO.

16. Non-buffered queries
Normally, when you execute an SQL statement in your script, your program will stop there until the SQL statement is returned, and your program continues to execute. You can use unbuffered queries to change this behavior.
In this case, there is a very good description in the PHP Documentation: Mysql_unbuffered_query () function:

The above sentence translates to say that mysql_unbuffered_query () sends an SQL statement to MySQL instead of automatically fethch and caches the results like mysql_query (). This can save a lot of considerable memory, especially those that produce a lot of results, and you don't have to wait until all the results are returned, and you can start working on the query results as soon as the first row of data is returned.

However, there are some limitations. Because you either read all the lines, or you want to call Mysql_free_result () to clear the results before making the next query. Also, mysql_num_rows () or Mysql_data_seek () will not work. So, you need to think carefully about whether to use unbuffered queries.

17. Save the IP address as unsigned INT
Many programmers create a varchar (15) field to hold IP in the form of a string rather than a shaped IP. If you use plastic to store it, you only need 4 bytes, and you can have a fixed-length field. And, this will bring you the advantage of querying, especially when you need to use such a where condition: IP between Ip1 and IP2.
We must use unsigned INT because the IP address uses an entire 32-bit unsigned shaping.
Instead of your query, you can use Inet_aton () to turn a string IP into a shape, and use Inet_ntoa () to turn an integer into a string IP. In PHP, there are also functions such as Ip2long () and Long2ip ().

18. Fixed-length tables are faster
If all the fields in the table are fixed length, the entire table is considered "static" or "Fixed-length". For example, there are no fields of the following type in the table: Varchar,text,blob. As long as you include one of these fields, the table is not a fixed-length static table, so the MySQL engine will handle it in a different way.
Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key.

Also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether you use it or not. PHP Programmer Station
Using the "vertical split" technique (see the next one), you can split your table into two that are fixed-length and one that is indefinite.

19. Vertical Segmentation
"Vertical Segmentation" is a method of turning a table in a database into several tables, which reduces the complexity of the table and the number of fields for optimization purposes. (Previously, in a bank project, saw a table with more than 100 fields, very scary)
Example one: One of the fields in the Users table is the home address, which is an optional field, and you do not need to read or rewrite this field frequently in addition to your personal information when working in a database. So, why not put him in another table? This will make your watch better.

MySQL Database optimization summary (experience)