MySQL performance optimization (from Pinterest)

1. Optimize your query for queries

Most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and this is handled by the MySQL database engine. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results.

The main problem here is that this is a very easy thing to ignore for programmers. Because, some of our query statements will let MySQL not use the cache. Take a look at the following example:

// 查询缓存不开启$r = mysql_query("SELECT username FROM user WHERE     signup_date >= CURDATE()");// 开启查询缓存$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 the MySQL query cache does not work for this function. 所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的. So all you need to do is use a variable instead of the MySQL function to turn on the cache.

2. EXPLAIN your Select query

Using EXPLAIN keywords lets you know how MySQL handles your SQL statements.

There are tables associated with the query, such as the following:

select username, group_namefrom users ujoins groups g on (u.group_id = g.id)

When you find that the query is slow and then increase the index on the group_id field, the query is accelerated

3. Use limit 1 when only one row of data is used

When you query a table, you already know that the result will only have one result, because you might need to fetch the cursor, or you might want to check the number of records returned.
In this case, adding limit 1 can increase performance. As such, the MySQL database engine stops searching after it finds a piece of data, instead of continuing to look for the next record-compliant data.
The following example, just to find out if there are users of "China", 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)

// 没有效率的：$r = mysql_query("SELECT * FROM user WHERE country = ‘China‘");if (mysql_num_rows($r) > 0) { // ...}// 有效率的：$r = mysql_query("SELECT 1 FROM user WHERE country = ‘China‘ LIMIT 1");if (mysql_num_rows($r) > 0) {// ...}

4. Jianjian Index for search word

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。

5. Use a fairly typed column when the join table is used and index it

如果你的应用程序有很多JOIN查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和一个INT字段JOIN在一起，MYSQL就无法使用他们的索引。对于那些STRING类型，还需要有相同的字符集才行（两个表的字符集有可能不一样）

6. Never order by RAND () 7, avoid SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

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

// 不推荐$r = mysql_query("SELECT * FROM user WHERE user_id = 1");$d = mysql_fetch_assoc($r);echo "Welcome {$d[‘username‘]}";// 推荐$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 tables

我们应该为数据库里的每张表都设置一个ID作为其主键，而最好的是一个INT型（推荐使用UNSIGNED），并设置上自动增长的AUTO INCREMENT标志。就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

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

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

10, from PROCEDURE analyse () get advice?

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.

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 big the difference is between "Empty" and "null" (if it's int, that's 0 and null)? If you feel that there is no difference between them, then you should not use NULL. (Do you know?) 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.

The following excerpt from MySQL's own documentation

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

12. Save the IP address as UNSIGNED INT

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。

We have to use unsigned INT because the IP address uses the entire 32-bit unsigned shaping

13, fixed-length table will be faster

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

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.

14, Vertical Division

"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 table better performance, we think is not, a lot of time, I for the user table, only the user ID, user name, password, user role, etc. will be used frequently. A smaller table will always have good performance.

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

In addition, you need to note that these separated fields form the table, you do not regularly join them, otherwise, this performance will be worse than not split, and, it will be a drop of magnitude.

15. Splitting large DELETE or INSERT statements

If you need to perform a large DELETE or INSERT query on an online website, you need to be very careful to avoid your actions to keep your entire site from stopping accordingly. Because these two operations will lock the table, the table is locked, the other operations are not in.

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

If you lock your watch for a period of time, say 30 seconds, for a site with a high level of access, the 30-second cumulative number of access processes/threads, database links, and open files may not only allow you to park the Web service crash, but may also leave your entire server hanging up.

So, if you have a big deal, you make sure you split it, using the LIMIT condition is a good way. Here is an example:

while (1) {//每次只做1000条mysql_query("DELETE FROM logs WHERE log_date <= ‘2009-11-01‘ LIMIT 1000");if (mysql_affected_rows() == 0) { // 没得可删了，退出！ break;}// 每次都要休息一会儿usleep(50000);

}

16, the smaller the column will be faster

For most database engines, hard disk operations can be the most significant bottleneck. So it's very helpful to have your data compact, because it reduces access to the hard drive.

See 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 master the keys, 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 do this later, you will die very difficult to see, see Slashdot example (November 06, 2009), a simple ALTER TABLE statement took 3 hours, because there are 16 million data.

17. Select a proper storage engine

There are two storage engines MyISAM and InnoDB in MySQL, each with a few pros and cons. Cool Shell before the article "Mysql:innodb or MyISAM?" Discussion and this matter.

MyISAM is suitable for applications that require a large number of queries, but it is not very good for a lot of write operations. Even if you just need to update a field, the entire table will be locked and other processes will be unable to manipulate the read process until the read operation is complete. In addition, MyISAM's calculations for SELECT COUNT (*) are extremely fast.

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 the writing operation more time, will be more excellent. Also, he supports more advanced applications, such as: transactions.

18. Be careful of "permanent link"

The purpose of the permanent link is to reduce the number of times the MySQL link is recreated. When a link is created, it will always be in a connected state, even if the database operation is finished. And since our Apache has started reusing its child processes-that is, the next HTTP request will reuse Apache's subprocess and reuse the same MySQL link.

PHP Manual: Mysql_pconnect ()
In theory, this sounds very good. But from personal experience (and most people), this function creates more trouble. Because, you only have limited number of links, memory problems, file handles, and so on.

And, Apache runs in an extremely parallel environment, creating a lot of processes. This is why this "permanent link" mechanism is not working well. Before you decide to use permanent link, you need to think about the architecture of your entire system.

Reference

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

Mysql>Select Sql_no_cache *From Guang_deal_outswhere deal_idIn (Select IDFrom Guang_dealsWHERE id =100017151);EmptySet (18.87 sec) mysql>Select Sql_no_cache a.*From Guang_deal_outs a innerJoin Guang_deals b on a.deal_id = b.IDwhere b.id =100017151;EmptySet (0.01 sec) Reason mysql> descSelect Sql_no_cache *From Guang_deal_outswhere deal_idIn (Select IDFrom Guang_dealsWHERE id =100017151) +----+--------------------+-----------------+-------+---------------+---------+---------+-------+--- -------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |Ref | Rows | Extra |+----+--------------------+-----------------+-------+---------------+---------+---------+-------+-------- --+-------------+|1 | PRIMARY | guang_deal_outs | All | NULL | NULL | NULL | NULL |18633779 | Usingwhere | |2 | DEPENDENT subquery | Guang_deals |Const | PRIMARY | PRIMARY |4 |Const |1 | Using index |+----+--------------------+-----------------+-------+---------------+---------+---------+-------+-- --------+-------------+2RowsInchSet (0.04 sec) mysql> descSelect Sql_no_cache a.*From Guang_deal_outs a innerJoin Guang_deals b on a.deal_id = b.IDwhere 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, guang_deal_outs there are also indexes on the deal_id.
Actually, I want the handle query set to

    select * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151);

It looks like the following.

select * from guang_deal_outs where deal_id in (100017151);

But unfortunately, the situation is the opposite. MySQL tries to make it contact with the outside table 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_id);

This in-subquery form is very inefficient when the amount of data in an external table (such as the guang_deals above) is larger (if the smaller table is not significantly affected)

Reference:
http://codingstandards.iteye.com/blog/1344833
Http://coolshell.cn/articles/1846.html
Http://hi.baidu.com/yzx110/item/74892ab6fc4601a5eaba93e1

MySQL performance optimization (from Pinterest)