Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of MySQL query cache optimization, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL query cache optimization

1 Overview

2 Operation flow

3 query cache configuration

4 enable query caching

5 query cache SELECT option

6 query cache invalidation

1. Overview

When the query cache of Mysql is enabled, when the exact same SQL statement is executed, the server will read the results directly from the cache. When the data is modified, the previous cache will be invalidated. Tables that modify frequently are not suitable for query caching.

2. Operation flow

1. The client sends a query to the server

two。 The server first checks the query cache and immediately returns the results stored in the cache if it hits the cache. Or move on to the next stage.

3. The server performs SQL parsing and preprocessing, and then the optimizer generates the corresponding execution plan.

4. According to the execution plan generated by the optimizer, MySQL calls the API of the storage engine to execute the query

5. Return the result to the client.

3. Query cache configuration

Check to see if the current MySQL database supports query caching:

SHOW VARIABLES LIKE 'have_query_cache';mysql > SHOW VARIABLES LIKE' have_query_cache';+-+-+ | Variable_name | Value | +-+-+ | have_query_cache | YES | +-+-+ 1 row in set (0.26 sec)

Query caching is supported on behalf of the current database

Check whether query caching is enabled in the current MySQL:

Mysql > SHOW VARIABLES LIKE 'query_cache_type';+-+-+ | Variable_name | Value | +-+-+ | query_cache_type | OFF | +-+-+ 1 row in set (0.01 sec)

Indicates that query caching is not currently enabled

View the size of the query cache:

Mysql > SHOW VARIABLES LIKE 'query_cache_size';+-+-+ | Variable_name | Value | +-- + | query_cache_size | 16777216 | +-+-+

Represents that the current query cache takes up 16777216 bytes, which takes up about 1.5MB. If the cache is too small, you can change the value of query_cache_size to increase the size of the query cache.

View the status variables of the query cache:

Mysql > SHOW STATUS LIKE 'Qcache%' +-+-+ | Variable_name | Value | +-+-+ | Qcache_free_blocks | 1 | Qcache_free_memory | 16768680 | Qcache_hits | 0 | | Qcache_inserts | 0 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 29 | Qcache_queries_in_cache | 0 | Qcache_total_blocks | 1 | +-+-+

The meanings of each variable are as follows:

Parameter meaning number of available memory blocks in Qcache_free_blocks query cache Qcache_free_memory query cache number of available memory Qcache_hits query cache hits Qcache_inserts number of queries added to the query cache Qcache_lowmen_prunes number of queries deleted from the query cache due to insufficient memory number of Qcache_not_cached non-cache queries (cannot be cached or not cached due to query_cache_type settings) Qcache_queries _ number of queries registered in in_cache query cache total number of blocks in Qcache_total_blocks query cache

4. Enable query cache

Query caching in MySQL is off by default. You need to manually configure the parameter query_cache_type to enable query caching. Query_cache_type there are three available values for this parameter:

Value means OFF or 0 query caching function turns off ON or 1 query caching function is turned on, and the results of SELECT will be cached if they meet the caching conditions. Otherwise, no caching, explicitly specify SQL_NO_CACHE, no caching DEMAND or 2 query caching function is carried out on demand, and the SELECT statement of explicitly specifying SQL_CACHE will be cached; otherwise, the SELECT statement of SQL_CACHE will not be cached

How to set the value of query_cache_type? here we need to modify the configuration file of MySQL.

The blogger's Ubuntu (Linux operating system) version is 16.04 and MySQL version is 5.7. You need to go to / etc/mysql/mysql.conf.d to modify the configuration file mysqld.cnf

Add the following

Then you need to restart the MySQL service

Log in to MySQL again

At this point, you can query whether the MySQL query cache is enabled.

After the configuration, the restart service can take effect.

You can then execute the SQL statement on the command line for verification, execute a more time-consuming SQL statement, and then execute it a few more times to check the execution time of the next few times; get to determine whether to walk the query cache by looking at the cache hits of the query cache.

We can test it. We once built a table, tb_item, with 2.5 million pieces of data in it.

Mysql > select count (*) from tb_item;+-+ | count (*) | +-+ | 2499695 | +-1 row in set (8.57 sec) mysql > select count (*) from tb_item;+-+ | count (*) | +-+ | 2499695 | +-+ 1 row in set (0.00 sec)

As you can see, it only takes 0s to execute the same SQL statement for 8s the first time and the second time

In this way, we verify that the query cache is actually on and in effect.

We can see that the cache state has been hit once and added to the cache once (because the same SQL statement is only added on the first query)

5. Query cache SELECT option

You can specify two options related to query caching in the SELECT statement:

SQL_CACHE: if the query results are cacheable and the value of the query_cache_type system variable is ON or DEMAND, the query results are cached.

SQL_NO_CACHE: the server does not use query caching. It neither checks the query cache, nor does it check whether the results have been cached, nor does it cache the query results.

Note: when the value of the query_cache_type system variable is ON, it will be cached even without SQL_CACHE, while the value of the query_cache_type variable is DEMAND, and caching will be done only if SQL_CACHE is specified in the display.

The first two rows of information in the tb_ item table are as follows

Mysql > select * from tb_item limit 2 +-+ | id | title | | price | num | categoryid | status | sellerid | createtime | updatetime | +- -+-+ | 1 | goods No. 1 | 33494.85 | 0 | 1 | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 | 2 | goods No. 2 | 5617.72 | 24060 | 1 | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37 : 15 | +-+ 2 rows in set (0.04 sec)

We test the query (note that the previous statement has been placed in the cache and the corresponding state value will change)

Mysql > select title,sellerid from tb_item where id=1;+-+-+ | title | sellerid | +-+-+ | item No. 1 | 5435343235 | +-+-+ 1 row in set (0.00 sec) mysql > show status like 'Qcache%' +-+-+ | Variable_name | Value | +-+-+ | Qcache_free_blocks | 1 | Qcache_free_memory | 16764840 | Qcache_hits | 1 | | Qcache_inserts | 3 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3 | Qcache_queries_in_cache | 3 | Qcache_total_blocks | 8 | +-+-+ 8 rows in set (0.00 sec)

Indicates that this statement is also added to the cache

When I don't want to cache, we need to add SELECT_NO_CACHE after select

Mysql > select SQL_NO_CACHE title,sellerid from tb_item where id=2 +-+-+ | title | sellerid | +-+-+ | item No. 2 | 5435343235 | +-+-+ 1 row in set, 1 warning (0.00 sec) mysql > show status like 'Qcache%' +-+-+ | Variable_name | Value | +-+-+ | Qcache_free_blocks | 1 | Qcache_free_memory | 16764840 | Qcache_hits | 1 | | Qcache_inserts | 3 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 4 | Qcache_queries_in_cache | 3 | Qcache_total_blocks | 8 | +-+-+ 8 rows in set (0.03 sec)

The value of Qcache_inserts is still 3, which means it is not cached.

6. Query cache invalidation

We have previously improved that when the value of query_cache_type is set to 1, it caches the results of eligible select statements. The reason is that in some cases, the query cache is invalid.

1) in the case of inconsistent SQL statements, the SQL statements of the query must be exactly the same in order to hit the query cache.

SQL1: select count (*) from tb_item;SQL2: Select count (*) from tb_item

Only the case is different.

Mysql > select count (*) from tb_item;+-+ | count (*) | +-+ | 2499695 | +-1 row in set (0.00 sec) mysql > Select count (*) from tb_item;+-+ | count (*) | +-+ | 2499695 | +-+ 1 row in set (2.02 sec)

2) when there is some uncertainty in the query statement, it will not be cached. Such as: now (), current_date (), curdate (), curtime (), rand (), uuid (), user (), database (). These functions get different results each time.

SQL1: select * from tb_item where updatetime

< now() limit 1;SQL2 : select user();SQL3 : select database(); 3） 不使用任何表查询语句。 比如select一个常量select 'hello'; select 'A'; 4） 查询 mysql， information_schema或 performance_schema 系统数据库中的表时，不会走查询缓存。 MySQL系统数据库包括mysql， information_schema或 performance_schema select * from information_schema.engines; 5） 在存储的函数，触发器或事件的主体内执行的查询。 6） 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。 mysql>

Select count (*) from tb_item;+-+ | count (*) | +-+ | 2499695 | +-+ 1 row in set (0.00 sec) mysql > update tb_item set title='test1' where id=5;Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > Select count (*) from tb_item +-+ | count (*) | +-+ | 2499695 | +-+ 1 row in set (1.23 sec) mysql > Select count (*) from tb_item +-+ | count (*) | +-+ | 2499695 | +-+ 1 row in set (sec) these are all the contents of this article "sample Analysis of MySQL query Cache Optimization". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.