Best 20 + Experience sharing for MySQL performance optimization

1. Optimize your query for query caching

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 the MySQL query cache does not work for this function. So SQL functions like now () and RAND (), or whatever, do not turn on the query cache.
Because the return of these functions is variable and volatile. 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
3. Use LIMIT 1 when only one row of data is used
4. Jianjian index for search words 
5. Use a fairly typed example in the Join table and index it
Also, the fields that are used for join should be of the same type.
For example: If you want to join the DECIMAL field with an INT field,
MySQL will not be able to use their indexes. For those string types, you also need to have the same character set. (Two tables may not have the same character set)

6. Never ORDER by RAND ()
7. Avoid SELECT *
The more data you read from the database, the slower the query becomes. And, if your database server and Web server are two separate servers, this also increases the load on the network transport.
8. Always set an ID for each table
9. Use ENUM instead of VARCHAR
11. Use not NULL where possible
15. Fixed-length tables are faster
If all the fields in the table are 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,
Then the table is not a fixed-length static table, so the MySQL engine will handle it in a different way.
16. Vertical segmentation
Example one: One of the fields in the Users table is the home address, which is an optional field, and when you are working in the database, in addition to your personal information,
You don't need to read or rewrite this field frequently. So, why not put him in another table? This will give your watch a better performance,
Think about it, a lot of the 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.

Also, you need to be aware that the tables formed by these fields are divided, and you do not routinely join them,
Otherwise, such performance will be worse than non-segmentation, and it will be a drop in the extreme number of levels.

1, modify the configuration file, under Windows is my.ini,linux under the my.cnf;

On the last append of the configuration file:

The code is as follows
Query_cache_type = 1 Query_cache_size = 600000

Need to restart MySQL to take effect;

Then adopt the second way;

b) Open the cache in two ways:

A) Use the MySQL command:

The code is as follows
Set global query_cache_type = 1; Set global query_cache_size = 600000;

If error:

Query cache is disabled; Restart the server with query_cache_type=1 ...

On the MySQL command line input

Mysql> Show variables like "%query_cache%"; To see if the settings are successful, you can now use the cache;

Mysql> Show variables like ' Have_query_cache ';

System Variable Query_cache_size

Represents the query cache size, which is the allocation of memory size to the query cache, if you assign a size of 0, then the first step and the second step does not work, or no effect.

Mysql> SELECT @ @global. query_cache_size;

Above is mysql6.0 set default, the previous version as if the default is 0, then it is necessary to set the next.

--Set global.query_cache_size here is set around 1M, more than 900 K. MySQL > SET @ @global. query_cache_size=1000000;

Query_cache_limit control the maximum value of cached query results

For example: If the query result is very large, also cache???? This is obviously out of the question.

MYSQL can set a maximum cache value, and when you query the cache number result data exceeds this value will not be cached. The default is 1M, which means that more than 1M query results are not cached.

Mysql> SELECT @ @global. query_cache_limit;
This is the default value, if you need to modify it, just like setting the cache size, use set to re-specify the size.


MySQL Query cache related variables

Mysql> Show variables like '%query_cache% ';

To view the status of a cache

Mysql> Show status like '%qcache% ';

MySQL provides a series of Global status to record the current state of the Query Cache, as follows:

qcache_free_blocks: Number of memory blocks in Query Cache that are currently idle

qcache_free_memory: Total amount of Query Cache memory currently in idle state

qcache_hits: Query Cache Hit Count

qcache_inserts: The number of times to insert a new query cache into the query cache, that is, the number of missed hits

qcache_lowmem_prunes: When query cache memory capacity is insufficient, you need to remove the old query cache from the number of times it will be used for the new cache object

qcache_not_cached: The number of SQL not being cache, including SQL that cannot be cache, and SQL that will not be cache because of Query_cache_type settings

Qcache_queries_in_cache: Number of SQL currently in Query cache

qcache_total_blocks: Total Block count in Query Cache

Check Query Cache usage

The simplest way to check whether to benefit from the query cache is to check the cache hit rate, and when the server receives a SELECT statement, the qcache_hits and Com_select variables are incremented based on the query cache.

The calculation formula for query cache hit ratio is: qcache_hits/(qcache_hits + com_select).

Mysql> Show status like ' com_select% ';

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSH query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.

Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.

Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%

Sample server query Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit ratio = 1.94%, bad hit, probably write more frequently, and possibly some fragments

Quote a word from a predecessor

If the Qcache_lowmem_prunes value is larger, it indicates that the query buffer size setting is too small and needs to be increased.
If there are more qcache_free_blocks, it means that there is more memory fragmentation, need to clean up, flush query cache
For the tuning of query_cache_min_res_unit size, a calculation formula is given, which can be used for tuning setting reference:
Query_cache_min_res_unit = (query_cache_size-qcache_free_memory)/Qcache_queries_in_cache

Reference Link: http://www.cnblogs.com/gimin/p/5459689.html

Best 20 + Experience sharing for MySQL performance optimization