Advanced explanation of MySQL query cache optimization

MySQL query cache optimization

• 1 Overview
• 2 Operation process
• 3 Query Cache configuration
• 4 Turn on query cache
• 5 Query cache SELECT option
• 6 Query cache failure

(Free learning Recommended: mysql video tutorial)

1. Overview

Enable Mysql's query cache. 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 invalid. Tables that are modified frequently are not suitable for query caching.

2. Operation process

1. The client sends a query to the server;
2. The server The query cache is first checked, and if the cache is hit, the results stored in the cache are returned immediately. Otherwise, enter the next stage;
3. The server performs SQL parsing and preprocessing, and then the optimizer generates the corresponding execution plan;
4. MySQL calls the API of the storage engine based on the execution plan generated by the optimizer. Execute the query;
5. Return the results to the client.

3. Query cache configuration

1. Check whether the current MySQL database supports query cache:

 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)

represents The current database supports query caching

2. Check whether the current MySQL query cache is turned on:

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

means that the query cache is not currently turned on

3. Check the query Cache occupancy size:

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

means that the current query cache occupies 16777216 bytes, approximately 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.

4. 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 meaning of each variable is as follows:

Parameters	Meaning
Qcache_free_blocks	Query the number of available memory blocks in the cache
Qcache_free_memory	Query the amount of available memory for the cache
Qcache_hits	Query the number of cache hits
Qcache_inserts	Number of queries added to the query cache
Qcache_lowmen_prunes	Number of queries removed from the query cache due to insufficient memory
Qcache_not_cached	Number of non-cached queries (cannot be cached or not cached due to query_cache_type setting)
Qcache_queries_in_cache	Query cache Number of registered queries
Qcache_total_blocks	Total number of blocks in the query cache

4. Turn on Query cache

The query cache of MySQL is turned off by default. You need to manually configure the parameter query_cache_type to turn on the query cache. query_cache_type This parameter has three possible values:

Value	Meaning
OFF or 0	The query cache function is turned off
ON or 1	The query cache function is turned on. The SELECT result meets the cache conditions. Will be cached, otherwise, will not be cached, explicitly specify SQL_NO_CACHE, will not be cached
DEMAND or 2	Query caching function is performed on demand, explicitly specify SQL_CACHE Only SELECT statements will be cached; others will not be cached

如何设置query_cache_type的值呢，这里我们需要修改MySQL的配置文件
博主的Ubuntu（Linux操作系统）版本为16.04，mysql版本为5.7。需要进入到/etc/mysql/mysql.conf.d下修改配置文件mysqld.cnf

添加以下内容

然后需要重启MySQL服务

再登录MySQL

此时可再查询MySQL查询缓存是否开启

配置完毕之后，重启服务既可生效 ；

然后就可以在命令行执行SQL语句进行验证 ，执行一条比较耗时的SQL语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存。

我们可以进行测试，我们曾经建了一张表tb_item,里面有250万条数据。

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)

可以看到，第一次执行8s,第二次执行相同的SQL语句，只需要0s
这样我们就验证了查询缓存确实开启并且生效了。
我们可以看到缓存状态，命中数有了1次，添加到缓存中的次数为1次（因为相同的SQL语句只在第1次查询的时候添加）

5、查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项 ：
SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果 。
SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

注意：当 query_cache_type 系统变量的值为ON时，即使不加SQL_CACHE，也是会缓存的，而query_cache_type的变量的值为DEMAND，只有显示的指定了SQL_CACHE,才会做缓存。

tb_item表的前两行信息如下

mysql> select * from tb_item limit 2;+----+------------+----------+-------+------------+--------+------------+---------------------+---------------------+| id | title      | price    | num   | categoryid | status | sellerid   | createtime          | updatetime          |+----+------------+----------+-------+------------+--------+------------+---------------------+---------------------+|  1 | 货物1号    | 33494.85 |   365 |          0 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 ||  2 | 货物2号    |  5617.72 | 24060 |          0 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 |+----+------------+----------+-------+------------+--------+------------+---------------------+---------------------+2 rows in set (0.04 sec)

我们测试查询（注意，上一条语句已经被放到缓存中，相应的状态值会发生改变）

mysql> select title,sellerid from tb_item where id=1;+------------+------------+| title      | sellerid   |+------------+------------+| 货物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)

说明这一条语句也被加入到缓存中

当我不想要做缓存的时候，我们需要在select后面加上SELECT_NO_CACHE

mysql> select SQL_NO_CACHE title,sellerid from tb_item where id=2;+------------+------------+| title      | sellerid   |+------------+------------+| 货物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)

Qcache_inserts 的值仍然为3，说明没有缓存进去。

6、查询缓存失效的情况

我们前面已经提高过，当query_cache_type的值设置为1的时候，它会缓存符合条件的select语句的结果。原因是因为在某些情况下，查询缓存是会失效的。

1） SQL 语句不一致的情况， 要想命中查询缓存，查询的SQL语句必须完全一致。

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

仅大小写不同。

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） 当查询语句中有一些不确定的时，则不会缓存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。这些函数每次获取的结果都不同。

SQL1 : select * from tb_item where updatetime <p><strong>3） 不使用任何表查询语句。</strong><br> 比如select一个常量select ‘hello’;</p><pre class="brush:php;toolbar:false">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: 0

mysql> 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 (0.00 sec)

相关免费学习推荐：mysql数据库(视频)

The above is the detailed content of Advanced explanation of MySQL query cache optimization. For more information, please follow other related articles on the PHP Chinese website!