Mysql Database Optimization Summary (experience) _mysql

1. Optimize your MySQL query cache
Queries on the MySQL server enable high speed query caching. Keeping the database engine in the background quietly is one of the most effective ways to improve performance. When the same query is executed multiple times, if the result is extracted from the cache, it is fairly 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 from working.

Copy Code code 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 lets you know what kind of query MySQL is doing, which can help you find out where the bottleneck is, and show 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 joins), add your keyword explanation here, where we can use phpMyAdmin and he will tell you the results in the table. For example, if I'm forgetting to add a column to an index while I'm performing joins, explain can help me find out where the problem is.

After adding an index to group_id field

3. Use limit 1 to get a unique line
Sometimes, when you want to query a table, you know you just have to look at one line. You may go to a very unique record, or just 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. This allows the database engine to discover that only 1 will stop the scan, rather than scan the entire table or index.

Copy Code code as follows:

Do I have any of the users from Alabama?
What does:
$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 only a primary key or a unique key. If you want to search 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.
In addition, the columns you join must be of the same type. For example, if you add a decimal column and join 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.

Copy Code code as follows:

Looking for companies in I 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 is 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 fall into. You may have unknowingly created a terrible calm. This trap was created when you were using the by RAND () command.
If you really need to show your results randomly, there are a lot of better ways to do that. Admittedly, this requires writing more code, but it avoids the appearance of performance bottlenecks. The problem is that MySQL may execute by the RAND () command for each individual row in the table (which consumes the processor's processing power), and then give you just one line to return.

Copy Code code as follows:

What does:
$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. Try to avoid select * command
The more data you read from a table, the more slowly the query becomes. He increases the time that the disk needs to be operated on, or when the database server is separate from the Web server. You will experience a very long network delay, simply because data is not required to be transferred between servers.
Always specify the columns you need, which is a very good habit.

Copy Code code 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 significant with bigger result sets

8. Advice received from procedure analyse ()
PROCEDURE analyse () allows MySQL column structure analysis and the actual data in the table to give you some advice. If you already have actual data in your table, you can serve your major decisions.



9. Prepared statements
Prepared statements can help you with both performance optimization and security.
Prepared statements filter the variables that are already bound by default, can provide effective protection to the application and prevent SQL injection attacks. Of course, you can also manually filter, but because most programmers forgetful personality, it is difficult to achieve results.

Copy Code code 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 IP address as unsigned integral type
Many programmers create a varchar (15) without realizing that they can store the IP address in integer form. 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 a unsigned int type, 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 up an automatically added Auto_increment flag.
Even if it's you. The Users table has a field with a primary key called "email," and you don't let it be the primary key. Using the varchar type to use as a primary key can degrade performance. 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 require the use of primary keys, in which case the performance and settings of the primary key become very important, such as clustering, partitioning ...
Here, only one exception is the "foreign key" of the association table, that is, the primary key of the table, which is composed of the primary keys of several other tables. We call this the "foreign key". For example: There is a "student table" with the student ID, there is a "timetable" with the course ID, then the "score sheet" is the "association table", which is associated with the student table and curriculum, in the score sheet, the student ID and course ID is called "foreign key" which together form the primary key.

12. Use enum instead of varchar
The enum type is very fast and compact. In practice, it saves tinyint, but its appearance is displayed as a string. As a result, 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 an enum instead of a varchar.
MySQL also has a "suggestion" (see article tenth) that tells you how to rearrange your table structure. When you have a varchar field, this suggestion will tell you to change it to an enum type. Use procedure analyse () you can get relevant advice.

13. From procedure analyse () Get advice p programmer station
PROCEDURE analyse () will let MySQL help you analyze your field and its actual data, and will give you some useful advice. These recommendations become useful only if you have actual data in the table, because there are some big decisions that need to be based on data.
For example, if you create an int field as your primary key, however there is not much data, then PROCEDURE analyse () will suggest that you change the type of the field to Mediumint. Or you use a varchar field, because there's not much data, you might get a suggestion to change it to an enum. All these proposals may be due to the fact that there is not enough data, so the decision-making is not accurate enough.
In phpMyAdmin, you can view these suggestions by clicking "Propose table Structure" while viewing the table.

It is important to note that these are only suggestions that will become accurate only when the data in your table is growing. Be sure to remember that you are the one who makes the final decision.

14. Use NOT NULL PHP Programmer station as much as 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 what is the difference between "Empty" and "null" (if it is int, which is 0 and null)? If you feel that 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 and 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 some cases, you need to use null values.
The following is excerpted from MySQL's own documentation:

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

Prepared statements can check some of the variables you bind so that you can protect your program from "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. This is a better problem when we use some framework or ORM.

In terms of performance, when an identical query is used more than once, this can give you a considerable performance advantage. You can define some parameters for these prepared statements, and MySQL will parse it only once.

Although the latest version of MySQL in the transmission prepared statements is using the binary situation, so this will make the network transport very efficient.
There are, of course, some cases where we need to avoid using prepared statements because it does not support query caching. But it is said that version 5.1 was supported later. To use prepared statements in PHP, you can view its manual: Mysqli extensions or use of database abstraction layers, such as: PDO.

16. No buffer query
Normally, when you execute an SQL statement in your script, your program stops there until the SQL statement is returned, and then your program continues to execute. You can use the no buffer query to change this behavior.
In this case, there is a very good description in the PHP Documentation: the 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 right away when the first row of data returns.

However, there are some limitations. Because you either read all the rows or you want to call Mysql_free_result () to clear the results before you make the next query. Also, mysql_num_rows () or Mysql_data_seek () will not be available. Therefore, you need to consider carefully whether to use a query without buffering.

17. Save IP Address as unsigned INT
Many programmers create a varchar (15) field to hold a string of IP instead of a reshaped IP. If you use plastic to store, you need only 4 bytes, and you can have a fixed-length field. Also, this gives you the advantage of the query, especially if you need to use the Where condition: IP between Ip1 and IP2.
We have to use the unsigned INT because the IP address uses the entire 32-bit unsigned integer.
And your query, you can use Inet_aton () to convert a string IP into an integer 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 table will be faster
If all the fields in the table are of 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 that the MySQL engine handles it in another way.
Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths can easily compute the next data offset, so the readings will be quick. And if the field is not fixed long, then, each time to find the next one, you need the program to find the primary key.

Also, fixed-length tables are more easily cached and rebuilt. However, the only side effect is that fixed-length fields waste a bit of space, because a set of long fields, whether you use it or not, he has to allocate so much space. PHP Programmer Station
Using the vertical split technique (see next), you can split your table into two that are fixed-length and one that is indefinite.

19. Vertical Segmentation
Vertical segmentation is a way to change a table in a database into several tables, which can reduce the complexity of the table and the number of fields to achieve optimization. (Previously, in a bank project, saw a table has more than 100 fields, very scary)
Example one: There is a home address in the Users table, this field is an optional field, and you do not need to read or overwrite this field frequently, except for personal information while you are operating the database. So why not put him in the other table? This will make your watch better.