mysql开启查询缓存优化查询速度

开启查询缓存

查询缓存是mysql服务器提供的用于缓存select语句结果的一种内部内存缓存系统。

如果开启了查询缓存，将所有的查询结果都缓存起来，使用同样的select语句再次查询时，直接返回缓存的结果

在配置文件my.cnf开启查询缓存，重启mysql

# 开启查询缓存
query_cache_type = 1
# 设置缓存大小 单位是 kb 相当于 128mb
query_cache_size = 134217728

查看缓存的开启情况

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.07 sec)

读取缓存

同一条sql语句执行两次，第二次直接获取缓存结果，耗时0秒

mysql> select count(*) from goods;
+----------+
| count(*) |
+----------+
|  2804500 |
+----------+
1 row in set (1.11 sec)

mysql> select count(*) from goods;
+----------+
| count(*) |
+----------+
|  2804500 |
+----------+
1 row in set (0.00 sec)

缓存失效

当数据改变时，所有缓存会失效，再次查询时重新生成缓存

mysql> delete from goods where id = 2860138;
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from goods;
+----------+
| count(*) |
+----------+
|  2804499 |
+----------+
1 row in set (0.34 sec)

mysql> select count(*) from goods;
+----------+
| count(*) |
+----------+
|  2804499 |
+----------+
1 row in set (0.00 sec)

不生成缓存

如果sql语句包含表达式，不会生成缓存

mysql> select count(*), now() from goods;
+----------+---------------------+
| count(*) | now()               |
+----------+---------------------+
|  2804499 | 2019-05-16 16:49:21 |
+----------+---------------------+
1 row in set (0.33 sec)

mysql> select count(*), now() from goods;
+----------+---------------------+
| count(*) | now()               |
+----------+---------------------+
|  2804499 | 2019-05-16 16:49:22 |
+----------+---------------------+
1 row in set (0.32 sec)

禁用缓存

使用sql_no_cache指定禁用缓存

mysql> select sql_no_cache count(*) from goods;
+----------+
| count(*) |
+----------+
|  2804499 |
+----------+
1 row in set, 1 warning (0.32 sec)

mysql> select sql_no_cache count(*) from goods;
+----------+
| count(*) |
+----------+
|  2804499 |
+----------+
1 row in set, 1 warning (0.33 sec)

缓存状况

查看缓存使用情况

mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 8         | # 处于空闲状态的Block数目
| Qcache_free_memory      | 132177640 | # 缓存中的空闲内存总量
| Qcache_hits             | 39010     | # 缓存命中次数
| Qcache_inserts          | 2405      | # 缓存失效次数
| Qcache_lowmem_prunes    | 0         | # 提示缓存内存不足的次数
| Qcache_not_cached       | 158       | # 不适合进行缓存的查询语句数量
| Qcache_queries_in_cache | 392       | # 当前缓存的数量
| Qcache_total_blocks     | 855       | # 缓存中块的数量
+-------------------------+-----------+
8 rows in set (0.03 sec)