Mysql Performance Optimization Case Study-covering index and SQL _NO_CACHE_MySQL

This article mainly introduces the case study of Mysql Performance Optimization-covering indexes and SQL _NO_CACHE. For more information, see Scenario

The product has an image table pics with nearly 1 million data records and a related query statement. due to the high execution frequency, I want to optimize this statement.

The table structure is very simple. main fields:

The code is as follows:

User_id user ID
Picname
Smallimg thumbnail name

A user has multiple image records, and now there is an index created based on user_id: uid, the query statement is also very simple: get a user's image set:

The code is as follows:

Select picname, smallimg from pics where user_id = xxx;

Before optimization

Execute the query statement (to view the actual execution time, it is mandatory not to use the cache, to prevent the time difference caused by reading the cache during the test)

The code is as follows:

Select SQL _NO_CACHE picname, smallimg from pics where user_id = 17853;

Executed 10 times, with an average time of about 40 ms

Use explain for analysis:

The code is as follows:

Explain select SQL _NO_CACHE picname, smallimg from pics where user_id = 17853

The user_id index is used and the const constant is used for searching, which indicates that the performance is good.

After optimization

Because this statement is too simple and there is no optimization space for the SQL itself, the index is taken into account.

Modify the index structure and create a joint Index (user_id, picname, smallimg): uid_pic

Re-run 10 times, with the average time consumption reduced to about 30 ms

Use explain for analysis

We can see that the Index used is changed to the newly created joint Index, and the Extra section shows that the 'using index' is used'

Summary

The code is as follows:

SQL _NO_CACHE means that the query result is not cached. It does not mean that the cache is not used to answer the query.
You may use reset query cache to remove all queries from the cache and then your next query shocould be slow again. Same effect if you change the table, because this makes all cached queries invalid.

When we want to use SQL _NO_CACHE to disable result caching, we find that the results are different from our expectation. the query execution results are still cached. In fact, the real function of SQL _NO_CACHE is to disable caching query results, but it does not mean that the cache does not return results to the query.

To put it bluntly, it is not that the query does not use the cache, but that the query results are not used as the cache for the next query.

In addition, mysql has a mechanism for caching SQL statements. setting a proper mysql cache can reduce the I/O resources of the database. therefore, here we need to look at how to control this comfortable feature.

See the figure below:

The meanings of the items are as follows:

1. have_query_cache
Whether the "YES" table in the cache can be queried.

2. query_cache_limit
The maximum value of cacheable Select query results is 1048576 byte/1024 = kb. that is, the maximum value of cacheable select query results must be less than KB.

3. query_cache_min_res_unit
The size of memory allocated to the query cache results is 4096 bytes by default, that is, 4 KB.

4. query_cache_size
If you want to disable the query cache, set query_cache_size = 0. If the query cache is disabled, there will be no significant overhead.

5. query_cache_type
Query cache (ON by default)

1. the complete query process is as follows:

When the query is in progress, Mysql saves the query results in the qurey cache, but sometimes the results to be saved are relatively large, exceeding the value of query_cache_min_res_unit. at this time, mysql will retrieve the results one by one, save the results slowly. sometimes, instead of saving all the results once, you allocate a memory size of query_cache_min_res_unit to save the result set, next, we need to allocate such a block. if it is not enough, we need to allocate a block and so on. that is to say, mysql may need to perform multiple memory allocation operations in one query, we should know that frequent memory operations are time-consuming.

2. generation of memory fragments

When the allocated memory is not fully used, MySQL will Trim the memory and return the unused part for reuse. For example, for the first allocation of 4 kB, only 3 kB is used, 1 kb is left, and for the second consecutive operation, 4 KB is allocated, 2 kB is used, and 2 kB is left, the remaining 1 kB + 2 kB = 3 kB for these two consecutive operations is not enough to allocate a memory unit. at this time, memory fragments are generated.

3. memory block concept

Take a look at this:

Qcache_total_blocks indicates all blocks.

Qcache_free_blocks indicates unused blocks.
This value is relatively large, which means that there are many memory fragments. after the flush query cache is used, the value of the used block should be 1 or 0, at this time, all the memory is regarded as a continuous and fast combination.

Qcache_free_memory indicates how much memory is available in the cache.
Qcache_hits indicates the number of hits in the query cache, that is, the number of queries that respond directly from the query cache.
Qcache_inserts indicates the result of querying the total number of query commands cached in the cache area.
Qcache_lowmem_prunes indicates the number of query results that overflow and deletion from the cache when the query is full.
Qcache_not_cached indicates the number of query commands that do not enter the query cache.
Qcache_queries_in_cache queries the results of the number of query commands currently cached in the cache.

Optimization tips:

If the Qcache_lowmem_prunes value is large, the size of the query cache is too small and needs to be increased.
If Qcache_free_blocks is large, it indicates that there are many memory fragments and need to be cleared. flush query cache

For how to optimize the size of query_cache_min_res_unit, the book provides a calculation formula. For more information about the optimization settings, see:

The code is as follows:

Query_cache_min_res_unit = (query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache

Note that the flush query cache command can be used to sort and QUERY the fragments in the CACHE to improve memory usage, but does not clean up the content in the query cache, this is different from the reset query cache. do not confuse it. The latter clears all content in the query cache.
You can specify the query cache option in the SELECT statement. for those queries that must obtain data from the table in real time, or for those queries that are executed only once a day, you can specify the SQL _NO_CACHE option without querying the cache.
For tables that do not change frequently, the query operation is fixed. we can cache the query operation so that the table is not actually accessed and the query is executed during each execution, only obtain results from the cache, which can effectively improve the query performance. use the SQL _CACHE option.
The following is an example of using SQL _NO_CACHE and SQL _CACHE:

The code is 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: to use the query cache, you must also set the server parameters accordingly.

2. covering indexes (lazy sorting, from Baidu Encyclopedia)

Understanding Method 1: select data columns can be obtained only from the index without reading data rows. In other words, the query column must be overwritten by the created index.
Method 2: The index is an efficient way to find rows, but the database can also use the index to find the data of a column, so it does not have to read the entire row. After all, the index leaf nodes store the data they index. when you can obtain the desired data by reading the index, you do not need to read the row. An index contains (or overwrites) data that meets the query results.
Method 3: a non-clustered Composite Index, it includes all the columns used by the Select, Join, and Where clauses in the query (that is, the index creation field exactly overwrites the fields involved in the query condition, that is, the index contains the data being searched ).

Purpose:

If you want to overwrite multiple select columns through the index, you need to create a multi-column index for the required columns. of course, if you have a query condition, the where condition must meet the leftmost prefix principle.

The secondary index leaf node of Innodb contains the primary key column, so the primary key must be covered by the index.

(1) for example, in the inventory table of sakila, there is a combined index (store_id, film_id). for queries that only need to access these two columns, MySQL can use the index as follows:

The code is as follows:

Mysql> explain select store_id, film_id FROM sakila. inventory \ G

(2) for example, when the page is displayed in the article system, the general query is as follows:

The code is as follows:

SELECT id, title, content FROM article order by created desc limit 10000, 10;

In general, the index will be created in the created field (where id is the primary key), but when the LIMIT offset is large, the query efficiency is still very low, change the query:

The code is as follows:

SELECT id, title, content FROM article
Inner join (
SELECT id FROM article order by created desc limit 10000, 10
) AS page USING (id)

At this point, create a composite index "created, id" (as long as you create a created index, Innodb will store the primary key value in the secondary index ), you can use the Covering Index in the subquery to quickly locate the id.

Note: This article draws on the topic of Mysql Performance Optimization case-covering indexes and draws on the knowledge points in the original article, the original text has been reposted many times. I do not know the author or the source. if you need the original text, please search for it by yourself.