19 Mysql Performance Optimization Essentials Analysis _mysql

Here are 19 main points of MySQL performance optimization to share with you, learn together.

1, for the query to optimize your query
Most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and it is handled by the MySQL database engine. When many of the same queries are executed many times, the query results are placed in a cache so that subsequent queries do not directly access the cached results without the action table.

The main problem here is that it's easy to ignore for programmers. Because some of our query statements will let MySQL not use caching. Take a look at the following example:

The query cache does not open $r = mysql_query ("Select username from user WHERE  signup_date >= curdate ()");//Open Query Cache $today = Date ("Y -m-d "); $r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");

The difference between the two SQL statements above is curdate (), and MySQL's query cache does not work on this function. So, like now () and RAND () or some other SQL functions, they don't open the query cache because the return of these functions is variable. So all you need is to use a variable instead of the MySQL function to turn on the cache.

2, EXPLAIN your select query
Use the Explain keyword to let you know how MySQL handles your SQL statements.

A query that has table associations, such as the following:

Select Username, group_name from the users U joins groups g on (u.group_id = g.id)

Discovering queries slowly and then adding indexes to the group_id field will speed up the query

3. Use limit 1 when only one line of data is used
When you query a table for some time, you already know that the result will only have one result, simply because you may need to fetch the cursor, or you may check the number of records returned.

In this case, adding limit 1 can increase performance. In this way, the MySQL database engine stops searching after it finds a piece of data, instead of continuing to look for the next record-conforming data.

The following example, just to find out if there are "China" users, it is obvious that the latter will be more efficient than the previous one. (Note that the first one is select * and the second is select 1)

Inefficient: $r = mysql_query ("SELECT * from user WHERE country = ' the '"); if (mysql_num_rows ($r) > 0) {  //...}//efficient: $r = mysql_query ("Select 1 from user WHERE country = ' Country ' LIMIT 1 "); if (mysql_num_rows ($r) > 0) {//...}

4. Jianjian Index for search word
An index is not necessarily a primary key or a unique field. If you have a field in your table that you will always use to do a search, then make an index of it.

5, in the join table when the use of a considerable type of column, and its index
If your application has a lot of join queries, you should be sure that the fields of join in the two tables have been indexed. In this way, within MySQL, you will start a mechanism to optimize the SQL statements for your join.

Also, these fields that are used for joins should be of the same type. For example, if you were to join a decimal field with an int field, MySQL would not be able to use their index. For those string types, it is also necessary to have the same character set (the character set of two tables may not be the same).

6. Never order by RAND ()

7. Avoid SELECT *
The more data you read from the database, the slower the query will become. And, if your database server and Web server are two separate servers, this will also increase the load on the network transport.

So, you should develop a good habit of taking whatever you need.

$r = mysql_query ("SELECT * from user WHERE user_id = 1") is not recommended; $d = Mysql_fetch_assoc ($r); echo "Welcome {$d [' username ']}"; Recommended $r = mysql_query ("Select username from user WHERE user_id = 1"); $d = Mysql_fetch_assoc ($r); echo "Welcome {$d [' username ']}";

8. Always set an ID for two sheets
We should set an ID for each table in the database as its primary key, and the best is an int (recommend using unsigned) and set up the auto increment logo on the automatic growth.

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

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

10, from PROCEDURE analyse () to obtain recommendations?
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.

11. Use not NULL 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 how different the "Empty" and "null" are (if it is int, that 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 excerpt from MySQL's own documentation

"NULL columns require additional spaces in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra and rounded up to the nearest byte.

12. Save the 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 need to use the unsigned INT because the IP address uses the entire 32-bit unsigned integer

13, fixed length of the 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.

14, 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 table have better performance, we think is not, a lot of time, I for the user table, only the user ID, username, password, user role, etc. will be often used. Smaller tables always have good performance.

Example two: You have a field called "Last_login" that will be updated every time a user logs on. However, each update causes the table's query cache to be emptied. So, you can put this field in another table so that it doesn't affect your constant reading of the user ID, username, and user role, because the query cache will help you to add a lot of performance.

Also, you need to be aware that the tables that are formed by the separated fields do not frequently join them, otherwise, this performance will be worse than the undivided, and it will be a drop in the extreme number.

15, split large DELETE or INSERT statement
If you need to perform a large DELETE or INSERT query on an online Web site, you need to be very careful to avoid your actions so that your entire site stops accordingly. Because these two operations will lock the table, the table is locked, no other operation can enter.

Apache will have a lot of child processes or threads. So, it works fairly efficiently, and our servers don't want to have too many child processes, threads and database links, which are great for server resources, especially memory.

If you lock your watch for a while, for example, 30 seconds, then for a very high access site, this 30-second cumulative access process/thread, database links, the number of open files, may not only let you park Web services crash, may also let your entire server immediately hung.

So, if you have a big deal, you're going to have to split it up, and using the LIMIT condition is a good way to do it. Here is an example:

while (1) {//1000 mysql_query per session ("DELETE from logs WHERE log_date <= ' 2009-11-01 ' LIMIT 1000"); if (Mysql_affected_r     OWS () = = 0) {//no delete, quit! Break ///Take a break every time usleep (50000); }

16, the smaller the column will be faster
For most database engines, hard disk operations can be the most significant bottleneck. So, getting your data compact can be very helpful in this case, because it reduces access to the hard disk.

See the MySQL documentation Storage Requirements View all data types.

If a table has only a few columns (for example, a dictionary table, a configuration table), then we have no reason to use INT to make the key, using Mediumint, SMALLINT or smaller TINYINT will be more economical. If you don't need to record time, using DATE is much better than DATETIME.

Of course, you also need to leave enough space for expansion, otherwise, you will do this later, you'll die very difficult to see, refer to the example of Slashdot (November 06, 2009), a simple ALTER TABLE statement took 3 hours, because there are 16 million data.

17. Choose a correct storage engine
There are two storage engines MyISAM and InnoDB in MySQL, and each engine has its pros and cons. Cool Shell before the article "Mysql:innodb or MyISAM?" Discussion and this matter.

MyISAM is suitable for some applications that require a large number of queries, but it is not good for a large number of write operations. Even you just need to update a field, the entire table will be locked up, and other processes, the read process can not operate until the read operation is complete. In addition, MyISAM is extremely fast for calculations such as SELECT COUNT (*).

The InnoDB trend will be a very complex storage engine, and for some small applications it will be slower than MyISAM. He is it supports "row lock", so in write operation more of time, will be more outstanding. And, he also supports more advanced applications, such as: transactions.

18, careful "permanent link"
The purpose of "permanent link" is to reduce the number of times you re-create a MySQL link. When a link is created, it will always be in the state of the connection, even if the database operation is over. And since our Apache began reusing its subprocess-that is, the next HTTP request will reuse the Apache subprocess and reuse the same MySQL link.

PHP Manual: Mysql_pconnect ()

In theory, this sounds very good. But from personal experience (and also for most people), this feature creates more trouble. Because, you only have a limited number of links, memory problems, file handles number, and so on.

Also, Apache runs in extremely parallel environments and creates many, many processes. This is why this "permanent link" mechanism does not work well. Before you decide to use a "permanent link," you need to consider the architecture of your entire system.

19, when the query is slow, you can use join to rewrite the query to optimize

Mysql> Select Sql_no_cache * from Guang_deal_outs where deal_id into (select ID from guang_deals where id = 100017151);  Empty Set (18.87 sec)  mysql> select Sql_no_cache a.* from guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151;  Empty Set (0.01 sec)

Reason

Mysql> DESC Select Sql_no_cache * from Guang_deal_outs where deal_id in (select ID from guang_deals where id = 1000171 51); +----+--------------------+-----------------+-------+---------------+---------+---------+-------+----------+--- ----------+ | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +----+--------------------+-----------------+-------+---------------+--------- +---------+-------+----------+--- ----------+ | 1 | PRIMARY | guang_deal_outs | All | NULL |  NULL | NULL | NULL | 18633779 | Using where | | 2 | DEPENDENT subquery | Guang_deals | Const | PRIMARY |  PRIMARY | 4 |  Const | 1 | Using Index | +----+--------------------+-----------------+-------+---------------+--------- +---------+-------+----------+--- ----------+ 2 rows in Set (0.04 sec) mysql> desc Select Sql_no_cache a.* from guang_deal_outs a inner join guang_deals B on a.deal_id = b.id where b.id = 100017151; +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+---- ---------+ | 1 | Simple | B | Const | PRIMARY | PRIMARY | 4 | Const | 1 | Using Index | | 1 | Simple | A | Ref |  Idx_guang_dlout_dlid | Idx_guang_dlout_dlid | 4 | Const |    1 | | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+----
 ---------+ 2 rows in Set (0.05 sec)

In fact, the guang_deal_outs on the deal_id is also indexed. Actually I want the butt query set to

SELECT * from Guang_deal_outs where deal_id to (select ID from guang_deals where id = 100017151);

Into the bottom.

SELECT * from Guang_deal_outs where deal_id in (100017151);

Unfortunately, the reality is the opposite. MySQL is trying to make contact with the table outside to "help" optimize the query, and it thinks the following exists form is more efficient

SELECT * from guang_deal_outs where exists (SELECT * from guang_deals where id = 100017151 and id = guang_deal_outs.deal_i D);

In the form of this in subquery, the efficiency of the external table (such as the guang_deals above) is very poor (if not significantly affected by the smaller table).

This is the MySQL performance optimization of 19 key points to resolve, I hope to optimize the MySQL performance help.