MySQL optimization understanding (reprint)

When we design the database table structure, we need to pay attention to the performance of the data operation when we operate the database, especially the SQL statements when we look at the table. Here, we're not going to talk too much about optimizations for SQL statements, but only for MySQL, the most Web application database. Hopefully the following optimization tips are useful for you.
1. Optimize your query for query caching

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 ()"//   /c5> $today = Date ("y-m-d"

The difference between the two SQL statements above is curdate (), and the MySQL query cache does not work for this function. Therefore, SQL functions such as now () and RAND () or whatever, do not turn on the query cache because the return of these functions is variable. 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

Use the EXPLAIN keyword to let you know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query statement or table structure.
EXPLAIN's query results will also tell you how your index primary key is being leveraged, how your data tables are searched and sorted ... Wait, wait.
Pick one of your SELECT statements (it is recommended to pick one of the most complex, multi-table joins) and add the keyword explain to the front. You can use phpMyAdmin to do this. Then, you'll see a table. In the following example, we forget to add the group_id index and have a table join:

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, but 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. This way, 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)

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

4. Jianjian index for search words

The index does not necessarily give the primary key or the unique field. If you have a field in your table that you will always use to do a search, then index it.

From you can see that search string "last_name like ' a% '", one is built index, one is no index, performance is about 4 times times worse.

In addition, you should also need to know what kind of search is not able to use the normal index. For example, when you need to search for a word in a large article, such as: "WHERE post_content like '%apple% '", the index may be meaningless. You may need to use a MySQL full-text index or make an index yourself (say, search for keywords or tags, etc.)

5. Splitting a large DELETE or INSERT statement

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 //  do only 1000  mysql_query at a time ("DELETE from logs WHERE log_date <= ' 2009-11-01 ' LIMIT ");  if  (mysql_affected_rows () = = 0) { //  There's nothing to delete, quit!  break  ;}  //  take a break every time.  usleep (50000 

6. The smaller the column the 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 hard to see

7. Choose the right 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.

MySQL optimization understanding (reprint)