全局程序集缓存gac中安装程序集_数据库缓存

最新推荐文章于 2020-12-23 18:00:54 发布

weixin_39796868

最新推荐文章于 2020-12-23 18:00:54 发布

阅读量241

点赞数

文章标签：全局程序集缓存gac中安装程序集

数据库缓存

作者

姓名：Neo Chen (陈景峯)
昵称：netkiller
呼号：BG7NYT
手机：+86 13113668890

多维度架构 - 知乎www.zhihu.com

什么是数据库缓存？
为什么缓存数据呢？
什么时候使用数据库缓存
涉及缓存的地方有哪些
谁来控制数据库缓存
怎么控制数据库缓存
SQL_CACHE 缓存
禁止缓存 SQL_NO_CACHE
关闭缓存 set session query_cache_type=off

什么是数据库缓存？

这里讲的缓存是数据库本身的缓存，并不是外部缓存例如Redis/Memcache等等。

数据库的数据分为冷数据和热数据库，通俗的讲冷数据是存储在磁盘上不经常查询的数据；而热数据是频繁查询的数据，这部分数据会被缓存到内存中。

为什么缓存数据呢？

因为频繁查询相同结果集的数据时，每次到磁盘上查找数据是非常耗时的，所以数据库将频繁查询且返回相同结果集的数据放到内存中，可以减少磁盘访问操作。

什么时候使用数据库缓存

频繁访问且返回相同结果集的情况下使用缓存。

偶尔查询一次且间隔时间较长的情况下不要使用缓存。

尺寸较大的结果集不建议使用缓存，因为数据太大太大，缓存不足以存储，会导致频繁载入与销毁，命中率低。

通常数据库默认情况是开启缓存的，也就是说正常的select查询，如果符合缓存规则就会经过缓存。

当一条SQL查询时如果结果集在内存中称作“命中”

涉及缓存的地方有哪些

数据库本身，查看数据库缓存状态

数据库应用程序接口（ODBC、JDBC......）

谁来控制数据库缓存

通常DBA只能控制数据库缓存是否开启，分配多少内存给缓存使用，过期销毁时间，以及策略等等.

上面我已经说过，通常数据库默认都开启缓存，所以更多的时候我们的操作是禁用缓存。这就需要开发人员来通过特定的SQL操作来控制数据库缓存。

怎么控制数据库缓存

以 MySQL 为例

mysql> show variables like '%query_cache%'; 
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.04 sec)

编辑 my.cnf 文件，加入配置项 query_cache_type=1 然后重启mysql服务

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

query_cache_type | ON 表示缓存已经开启。

SQL_CACHE 缓存

默认情况 select 查询操作只要符合数据库缓存规则那么结果集就会被缓存，如果你的数据库没有开启缓存，请参考下面

set session query_cache_type=on;

flush tables;
show status like 'qcache_q%';
select sql_cache * from member where id=1;
show status like 'qcache_q%';
select sql_cache * from member where id=1;
show status like 'qcache_q%';

例 13.1. 演示 SQL_CACHE

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select sql_cache * from member where id=1;
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
| id | age | ctime               | ip_address | mobile | mtime | name | picture | sex  | status | wechat |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
|  1 |   1 | 2017-08-24 17:05:43 | 1          | NULL   | NULL  | 1    | 1       | 1    | Enable | NULL   |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
1 row in set (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.01 sec)

mysql> select sql_cache * from member where id=1;
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
| id | age | ctime               | ip_address | mobile | mtime | name | picture | sex  | status | wechat |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
|  1 |   1 | 2017-08-24 17:05:43 | 1          | NULL   | NULL  | 1    | 1       | 1    | Enable | NULL   |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
1 row in set (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.01 sec)

我们可以看到 Qcache_queries_in_cache 值由0转为1表示缓存已经生效。

禁止缓存 SQL_NO_CACHE

这里我们主要讲怎样禁止缓存，使查询出的结果集不进入缓存。

SELECT SQL_NO_CACHE * FROM table where id=xxxx

下面的用法比较安全，切换到其他数据库也能正常工作

SELECT /*!40001 SQL_NO_CACHE */ * FROM table			
			
set session query_cache_type=on;

flush tables;
show status like 'qcache_q%';
select sql_no_cache * from member where id=1;
show status like 'qcache_q%';
select sql_no_cache * from member where id=1;
show status like 'qcache_q%';

例 13.2. 演示 SQL_NO_CACHE

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select sql_no_cache * from member where id=1;
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
| id | age | ctime               | ip_address | mobile | mtime | name | picture | sex  | status | wechat |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
|  1 |   1 | 2017-08-24 17:05:43 | 1          | NULL   | NULL  | 1    | 1       | 1    | Enable | NULL   |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
1 row in set (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select sql_no_cache * from member where id=1;
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
| id | age | ctime               | ip_address | mobile | mtime | name | picture | sex  | status | wechat |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
|  1 |   1 | 2017-08-24 17:05:43 | 1          | NULL   | NULL  | 1    | 1       | 1    | Enable | NULL   |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
1 row in set (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

使用 sql_no_cache 查询 Qcache_queries_in_cache 值始终是 0

关闭缓存 set session query_cache_type=off

我们使用 set session query_cache_type=off 可以关闭本次查询缓存。

set session query_cache_type=off;

flush tables;
show status like 'qcache_q%';
select sql_cache * from member where id=1;
show status like 'qcache_q%';
select sql_cache * from member where id=1;
show status like 'qcache_q%';

例 13.3. 演示 query_cache_type=off 关闭查询缓存

mysql> set session query_cache_type=off;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select sql_cache * from member where id=1;
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
| id | age | ctime               | ip_address | mobile | mtime | name | picture | sex  | status | wechat |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
|  1 |   1 | 2017-08-24 17:05:43 | 1          | NULL   | NULL  | 1    | 1       | 1    | Enable | NULL   |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
1 row in set (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> select sql_cache * from member where id=1;
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
| id | age | ctime               | ip_address | mobile | mtime | name | picture | sex  | status | wechat |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
|  1 |   1 | 2017-08-24 17:05:43 | 1          | NULL   | NULL  | 1    | 1       | 1    | Enable | NULL   |
+----+-----+---------------------+------------+--------+-------+------+---------+------+--------+--------+
1 row in set (0.00 sec)

mysql> show status like 'qcache_q%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

netkiller：数据库结构版本控制zhuanlan.zhihu.com

netkiller：数据库记录安全解决方案zhuanlan.zhihu.com

netkiller：数据库进程间通信解决方案（一）zhuanlan.zhihu.com

netkiller：数据库进程间通信解决方案（二）zhuanlan.zhihu.com

netkiller：数据库与图片完美解决方案zhuanlan.zhihu.com

netkiller：MySQL 大数据操作注意事项zhuanlan.zhihu.com

netkiller：MySQL 「锁」详解zhuanlan.zhihu.com

netkiller：MySQL 「事务」详解zhuanlan.zhihu.com

netkiller：数据库恢复方案zhuanlan.zhihu.com