Mysql Performance Optimization Case Study-overlay index and Sql_no_cache_mysql

Scene

The product has a picture table pics, the data volume nearly 1 million, has a related query statement, because the execution frequency is high, wants to be optimized for this statement

Table structure is simple, main fields:

Copy Code code as follows:

USER_ID User ID
Picname Picture Name
Smallimg Small map Name

A user will have multiple picture records, and now there is an index based on user_id: UID, and the query is simple: Get a collection of pictures of a user:

Copy Code code as follows:

Select Picname, smallimg from pics where user_id = xxx;

before optimization

Executing a query statement (forcing no caching for real execution time, to prevent time differences from reading the cache at test time)

Copy Code code as follows:

Select Sql_no_cache picname, smallimg from pics where user_id=17853;

Performed 10 times, averaging around 40ms

Using Explain for analysis:

Copy Code code as follows:

Explain select Sql_no_cache Picname, smallimg from pics where user_id=17853

The user_id index is used, and is a const constant lookup, indicating that the performance has been very good

After optimization

Because this statement is too simple, the SQL itself does not have any optimization space, consider the index

Modify the index structure to establish a (user_id,picname,smallimg) federated index: Uid_pic

10 times, the average time elapsed to around 30ms

Using Explain for analysis

See the index used becomes the federated Index just established, and the extra section shows the use of ' using index '

Summarize

' Using index ' means ' overwrite index ', which is the key to improving SQL performance above

An index that contains the fields required for a query is called "overlay index"

MySQL only needs to go through the index to return the data required by the query, without having to check the index back to the table operation, reduce IO, improve efficiency

For example, the above SQL, the query condition is user_id, you can use the Federated index, the field to query is Picname smallimg, the two fields are also in the federated Index, which implements the "overlay index", which can complete the query work according to the Federated Index, thus improving performance.

Extended Research

One, MySQL cache, Sql_no_cache and Sql_cache difference

The top in the test, in order to prevent the reading cache caused by the impact of the results of the use of the Sql_no_cache this function, for Sql_no_cache's introduction official website is as follows:

Copy Code code as follows:

Sql_no_cache means that ' query ' is not cached. It does not mean that the cache isn't used to answer the query.
You could use RESET QUERY CACHE to remove all queries from the cache and then your next query should be slow again. Same effect If you are the table, because this makes all cached queries.

When we wanted to use Sql_no_cache to suppress the result cache, we found that the result was not the same as our expectation, and the result of the query execution was still the result of the cache. In fact, the real role of Sql_no_cache is to prohibit caching of query results, but does not mean that the cache is not returned to query as a result.

In the white point is, not this query does not use the cache, but the results of this query is not a cache of the next query.

There is, MySQL itself is a mechanism for SQL statement caching, reasonable set our MySQL cache can reduce the database IO resources, therefore, here we need to look at how to control the more comfortable function.

Look at the picture below:

The meaning of each of these is:

1, Have_query_cache
Whether to support query buffer "yes" table is to support query buffer

2, Query_cache_limit
The maximum number of cached select query results 1048576 byte/1024 = 1024kB = maximum cacheable Select query result must be less than 1024KB

3, Query_cache_min_res_unit
The size of the memory allocated to query cache results by default is 4096 byte or 4kB

4, Query_cache_size
If you want to disable query caching, set query_cache_size=0. The query cache is disabled and there is no obvious overhead

5, Query_cache_type
How the query is cached (default is on)

1, the complete query process is as follows

When the query is in progress, MySQL saved the query results in Qurey cache, but sometimes to save the result is larger than the Query_cache_min_res_unit value, this time MySQL will retrieve the results, while slowly save the results, so, Sometimes it is not to get all the results after a one-time save, but each allocated a query_cache_min_res_unit size of memory space to save the result set, after use, and then allocate a block, if not enough, then allocate a block, and so on, That is, it is possible that in a single query, MySQL will have to do a lot of memory allocation operations, and we should know that frequent operation of memory is time-consuming.

2, the production of memory fragments

When a piece of allocated memory is not fully used, MySQL will trim off the memory and return the unused part to reuse. For example, the first allocation of 4KB, only 3KB, the remaining 1KB, the second continuous operation, allocation of 4KB, with 2KB, the remaining 2KB, the two consecutive operations of the remaining 1kb+2kb=3kb, not enough to do a single memory unit allocation, this time, memory fragmentation will occur.

3. The concept of memory block

Let's take a look at this:

Qcache_total_blocks represents all the blocks

Qcache_free_blocks represents unused blocks
This value is relatively large, which means that more memory fragmentation, with the flush query cache cleanup, for the used block its value should be 1 or 0, because this time all the memory as a continuous fast together.

Qcache_free_memory indicates how much memory is available in the query cache now
Qcache_hits represents the number of hits in the query buffer, that is, the number of queries that respond directly from the query buffer
Qcache_inserts indicates that the query cache has always had the result of how many query commands were cached before
Qcache_lowmem_prunes indicates the number of query results that have been overrun and deleted from the query buffer full
Qcache_not_cached indicates the number of query commands that do not enter the query buffer
Qcache_queries_in_cache Query Cache The results of how many query commands are currently cached

Tuning tips:

If the Qcache_lowmem_prunes value is large, the query buffer size setting is too small and needs to be increased.
If Qcache_free_blocks more, indicating more memory fragments, need to clean up, flush query cache

About the size of the Query_cache_min_res_unit, the book gives a calculation formula, you can set the reference for tuning:

Copy Code code as follows:

Query_cache_min_res_unit = (query_cache_size-qcache_free_memory)/qcache_queries_in_cache

Also note that the FLUSH Query cache command can be used to organize the query cache fragments, improve memory usage, but will not clean up the contents of the query buffer, and reset query cache is different, do not confuse, the latter is to clear all the contents of the query buffer.
You can specify the options for the query cache in the SELECT statement, and for those queries that are sure to get the data from the table in real time, or for those queries that are executed only once a day, we can specify no query caching and use the Sql_no_cache option.
For those infrequently changing tables, the query operation is very fixed, we can cache the query operation, so that every time the execution does not actually access the table and execute the query, just get results from the cache, can effectively improve the performance of the query, using the Sql_cache option.
The following are examples of using Sql_no_cache and Sql_cache:

Copy Code code as follows:

Mysql> Select Sql_no_cache id,name from test3 where ID < 2;
Mysql> Select Sql_cache id,name from test3 where ID < 2;

Note: The query cache usage also needs to match the settings of the corresponding server parameters.

Second, the coverage index (lazy finishing, from Baidu Encyclopedia)

One way to understand it is to select a data column that can be obtained only from an index, without having to read rows of data, in other words, the query column is overwritten by the index being built.
Understanding mode Two: An index is a way to efficiently find a row, but a generic database can also use an index to find data for a column, so it does not have to read the entire row. After all, index leaf nodes store the data they index; When you can get the data you want by reading the index, you don't need to read the rows. An index that contains (or overwrites) data that satisfies the query's results is called an overlay index.
Understanding three: A form of a nonclustered composite index that includes all the columns that are used by the Select, join, and WHERE clauses in the query (that is, the fields that are indexed are exactly the fields covered in the query criteria, i.e., the index contains the data that the query is looking for).

Role:

If you want to overwrite select multiple columns by index, you need to set up a multiple-column index for the column you want, and of course, if you have a query condition, the where condition requires that you satisfy the leftmost prefix principle.

InnoDB Secondary Index The leaf node contains the primary key column, so the primary key must be covered by the index.

(1) For example, in the Sakila inventory table, there is a composite index (store_id,film_id) that MySQL can use for queries that only need access to both columns, as follows:

Copy Code code as follows:

Mysql> EXPLAIN SELECT store_id, film_id from Sakila.inventory\g

(2) Again for example in the article system pagination display, the general query is this:

Copy Code code as follows:

SELECT ID, title, content from article order by created DESC LIMIT 10000, 10;

Usually such queries build the index in the Created field (where the ID is the primary key), but when the limit is very large, query efficiency is still very low, change the query:

Copy Code code as follows:

SELECT ID, title, content from article
INNER JOIN (
SELECT ID from article to created DESC LIMIT 10000, 10
) as page USING (ID)

At this point, the establishment of a composite index "created, id" (as long as the establishment of created index can be, InnoDB will be in the secondary index to store the primary key value), you can use the subquery in the covering index, quick location ID, query efficiency whining

Note: This article is a reference to the MySQL performance optimization case-overlay index of an article exaggerated, reference to the original knowledge point, I did a bit of play and research, the original text has been reproduced several times, I do not know who the author, also do not know where the source, if the original text please search.