Mysql database optimization Summary (experiences)

1. Optimize Your MySQL query Cache
You can enable the High-Speed query cache for queries on the MySQL server. It is one of the most effective ways to improve performance by quietly processing the database engine in the background. When the same query is executed multiple times, if the result is extracted from the cache, it is quite fast.
But the main problem is that it is so easy to hide that most of our programmers will ignore it. In some processing tasks, we can actually prevent query caching.
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 technique that helps you understand what MySQL is performing for query operations. This helps you find the bottleneck, it also shows where the query or table structure is faulty.

The EXPLAIN query results can tell you how indexes are being referenced, and how tables are scanned and sorted.
Implement a SELECT query (preferably a complicated one with the joins method). Add your keyword explanation in it. here we can use phpMyAdmin, it will tell you the results in the table. For example, if I forget to add a column to an index when executing joins, the explanation can help me locate the problem.

After adding the index to group_id field

3. Use LIMIT 1 to get a unique row
Sometimes, when you want to query a table, you know that you only need to read a row. You may go to a very unique record, or just check the number of any existing records. They all satisfy your WHERE clause.
In this case, adding a LIMIT 1 will make your query more effective. In this way, the database engine will only stop scanning after 1, instead of scanning the entire table or index.
  Copy codeThe Code is as follows:
// Do I have any users from Alabama?
// What NOT to do:
$ R = mysql_query ("SELECT * FROM user WHERE state = 'abama '");
If (mysql_num_rows ($ r)> 0 ){
//...
}
// Much better:
$ R = mysql_query ("SELECT 1 FROM user WHERE state = 'abama' LIMIT 1 ");
If (mysql_num_rows ($ r)> 0 ){
//...
}
 
4. search fields in the Index
An index is not only 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 connected indexes are of the same type
If the application contains multiple connection queries, make sure that the linked columns are indexed on both tables. This affects how MySQL optimizes the internal join operation.
In addition, the columns to be added must be of the same type. For example, if you add a DECIMAL column and an int column to another table, MySQL cannot use at least one of the indexes. Even if the character encoding must be of the same 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 shocould be indexed
// And they both shocould 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 new programmers will fall. You may have created a terrible calm without knowing it. This trap is created when you use the by rand () command.
If you really need to display your results randomly, there are many better ways to achieve it. It is true that this requires more code, but it can avoid performance bottlenecks. The problem is that MySQL may execute the by rand () command for each independent row in the table (which consumes the processing power of the processor) and then return only one row for you.
Copy codeThe Code is as follows:
// 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 (0, $ d [0]-1 );
$ R = mysql_query ("SELECT username FROM user LIMIT $ rand, 1 ");

7. Avoid the SELECT * command whenever possible
The more data you read from a table, the slower the query. It increases the disk operation time, or when the database server and the WEB server are independent. You will experience a very long network latency, just because the data is not required to be transmitted between servers.
It is a good habit to always specify the columns you need.
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 are more significant with bigger result sets

8. Get suggestions from procedure analyse ()
Procedure analyse () allows MySQL's Column Structure Analysis and actual data in the table to give you some suggestions. If the actual data already exists in your table, it can serve your major decision-making.



9. Prepared statements
The prepared statements can be helpful in performance optimization and security.
By default, prepared statements filter Bound variables to effectively protect applications and prevent SQL injection attacks. Of course, you can also manually filter data, but most programmers are hard to achieve results due to their forgetful character.
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.
When creating a VARCHAR (15), many programmers do not realize that they can store IP addresses as integers. 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 operate on is of the unsigned int type, because the IP address will use 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 (UNSIGNED is recommended), and set the AUTO_INCREMENT flag automatically added.
Even if your users table has a primary key field "email", you should not make it a primary key. When the VARCHAR type is used, the primary key performance decreases. In addition, in your program, you should use the table ID to construct your data structure.
In addition, some operations in the MySQL Data Engine require primary keys. In these cases, the performance and settings of primary keys become very important, such as clusters, partitions ......
Here, there is only one exception, that is, the "foreign key" of the "joined table". That is to say, the primary key of the table is composed by the primary keys of several individual tables. We call this a "foreign key ". For example, if a "student table" has a student ID and a "Course table" has a course ID, "Student table" is "Join table", which is associated with the student table and Course table, in the student ID table, the student ID and the course ID are both called "Foreign keys" to form a primary key.

12. Use ENUM instead of VARCHAR
The ENUM type is extremely fast and compact. In fact, it stores TINYINT, but its appearance is displayed as a string. In this way, it is quite perfect to use this field for some option lists.
If you have a field, such as "gender", "country", "nationality", "status", 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 10) to tell you how to reorganize your table structure. When you have a VARCHAR field, we recommend that you change it to The ENUM type. You can get related suggestions using procedure analyse.

13. Get suggestions from procedure analyse () p programmer site
Procedure analyse () will allow MySQL to help you analyze your fields and actual data, and give you some useful suggestions. Only when the table has actual data can these suggestions become useful, because to make some big decisions, we need data as the basis.
For example, if you create an INT field as your primary key, but there is not much data, procedure analyse () suggests that you change the field type to MEDIUMINT. Or you use a VARCHAR field. Because there is not much data, you may get a suggestion that you change it to ENUM. These suggestions may be due to insufficient data, so decision-making is not accurate enough.
In phpmyadmin, you can click "Propose table structure" when viewing the table to view these suggestions.

Be sure to note that these are just suggestions. These suggestions will become accurate only when there is more and more data in your table. Remember, you are the final decision maker.

14. Try to use the not null php programmer site
Unless you use the NULL value for a special reason, you should always keep your field not null. This seems a bit controversial. Please refer to it.
First, ask yourself what is the difference between "Empty" and "NULL" (if it is an INT, It is 0 and NULL )? If you think there is no difference between them, you should not use NULL. (Do you know? In Oracle, the strings of NULL and Empty are the same !)
Do not think that NULL requires no space. It requires additional space. In addition, when you compare, your program will be more complex. Of course, this does not mean that you cannot use NULL. The reality is very complicated. In some cases, you still need to use NULL values.
The following is an excerpt from MySQL's own document:

15. Prepared Statements
Prepared Statements is similar to a stored procedure. It is a collection of SQL statements running in the background. We can get a lot of benefits from using prepared Statements, whether it is a performance issue or a security issue.

Prepared Statements can check some variables that you have bound to protect your program against "SQL injection" attacks. Of course, you can also manually check your variables. However, manual checks are prone to problems and are often forgotten by programmers. When we use some frameworks or ORM, this problem will be better.

In terms of performance, when the same query is used multiple times, this will bring you considerable performance advantages. You can define some parameters for these Prepared Statements, while MySQL only parses them once.

Although the latest version of MySQL uses binary data to transmit Prepared Statements, this makes network transmission very efficient.
Of course, in some cases, we need to avoid using Prepared Statements because it does not support query caching. However, it is said that version 5.1 is supported. To use prepared statements in PHP, you can view its user manual: mysqli extension or use the database abstraction layer, such as PDO.

16. unbuffered Query
Normally, when you execute an SQL statement in your script, your program will stop there until this SQL statement is not returned, then your program continues to run. You can use unbuffered queries to change this behavior.
The PHP document provides a very good description: mysql_unbuffered_query () function:

In the above sentence, mysql_unbuffered_query () sends an SQL statement to MySQL, instead of automatically fethch and caching results like mysql_query. This will save a lot of memory, especially the query statements that produce a large number of results. You don't have to wait until all the results are returned, but you only need to return the first row of data, you can start to work on the query results immediately.

However, there are some restrictions. Because you either read all rows or call mysql_free_result () to clear the result before the next query. In addition, mysql_num_rows () or mysql_data_seek () cannot be used. Therefore, whether to use a non-buffered query requires careful consideration.

17. Save the IP address as an unsigned int.
Many programmers will create a VARCHAR (15) field to store the IP address in string format instead of an integer IP address. If you use an integer to store data, you only need 4 bytes and you can have a fixed length field. In addition, this will bring you query advantages, especially when you need to use the WHERE condition: IP between ip1 and ip2.
We must use the unsigned int, because the IP address will use the entire 32-bit UNSIGNED integer.
For your query, you can use INET_ATON () to convert a string IP address into an integer, and use INET_NTOA () to convert a string IP address into a string IP address. In PHP, such functions as ip2long () and long2ip () are also available ().

18. tables with a fixed length will be faster
If all the fields in the table are "fixed length", the entire table will be considered as "static" or "fixed-length ". For example, the table does not have the following types of fields: VARCHAR, TEXT, BLOB. As long as you include one of these fields, this table is not a "static table with a fixed length". In this way, the MySQL engine will use another method for processing.
A fixed-length table improves performance because MySQL searches faster, because these fixed-length tables are easy to calculate the offset of the next data, so reading will naturally be fast. If the field is not fixed, the program needs to find the primary key for each query.

In addition, tables with a fixed length are more easily cached and rebuilt. However, the only side effect is that a field with a fixed length will waste some space, because a field with a fixed length will be allocated so much space no matter you use it. Php programmer site
Using the Vertical Split technique (see the next one), you can split your table into two tables with a fixed length and one with an indefinite length.

19. vertical segmentation
Vertical segmentation is a way to convert tables in the database into several tables by column, which can reduce the complexity of the table and the number of fields, so as to achieve optimization. (I used to work on projects in a bank. I have seen more than 100 fields in a table, which is terrible)
Example 1: In the Users table, a field is the home address, which is an optional field. In comparison, besides personal information, you do not need to read or rewrite this field frequently. So why don't I put him in another table? This will make your table better