数据库与缓存

数据库与缓存

摘要: 这里讲的缓存是数据库本身的缓存,并不是外部缓存例如Redis/Memcache等等。 数据库的数据分为冷数据和热数据库,通俗的讲冷数据是存储在磁盘上不经常查询的数据;而热数据是频繁查询的数据,这部分数据会被缓存到内存中。

本文节选自《Netkiller Architect 手札》

第 10 章 数据库与缓存

目录

  • 10.1. 什么是数据库缓存?
  • 10.2. 为什么缓存数据呢?
  • 10.3. 什么时候使用数据库缓存
  • 10.4. 涉及缓存的地方有哪些
  • 10.5. 谁来控制数据库缓存
  • 10.6. 怎么控制数据库缓存
    • 10.6.1. SQL_CACHE 缓存
    • 10.6.2. 禁止缓存 SQL_NO_CACHE
    • 10.6.3. 关闭缓存 set session query_cache_type=off

10.1. 什么是数据库缓存?

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

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

10.2. 为什么缓存数据呢?

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

10.3. 什么时候使用数据库缓存

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

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

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

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

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

10.4. 涉及缓存的地方有哪些

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

数据库应用程序接口(ODBC、JDBC......)

10.5. 谁来控制数据库缓存

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

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

10.6. 怎么控制数据库缓存

以 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 表示缓存已经开启。

10.6.1. 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%';

例 10.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表示缓存已经生效。

10.6.2. 禁止缓存 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%';

例 10.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

10.6.3. 关闭缓存 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%';

例 10.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(netkiller-ebook)

原文发表时间:2017-09-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏xingoo, 一个梦想做发明家的程序员

Java程序员的日常 —— 《编程思想》包和访问权限

包的作用 在C++中有命名空间的概念,这是因为,不同的项目或者人写出来的代码,可能名称是一样的。比如,java.util中有List,java.awt中也有...

1718
来自专栏小尘哥的专栏

thymeleaf中使用layui

头部引入css,尾部引入js,定义两个模板(一个也可以,里面的碎片分开写,我喜欢写两个分别引入),注意th:fragment,fragment即碎片,可以在模板...

716
来自专栏北京马哥教育

Linux自动化运维工具之ansible(二)

糖豆贴心提醒,本文阅读时间8分钟 YAML简介 YAML是一个可读性高的用来表达资料序列的格式。 YAML参考了其他多种语言,包括:XML、C语言、Pytho...

2566
来自专栏张戈的专栏

常用MySQL语句搜集整理

折腾 WordPress 多少要接触到 MySQL 的一些操作,比如,玛思阁之前重装 postview 插件后发现所有的 view 数据都被清空了!只好到 ph...

3695
来自专栏张善友的专栏

Mongo Database 性能优化

SQL Server有工具进行数据库的优化,Mongo Database Profiler.不仅有,而且功能更强大。 MongoDB 自带 Profiler,可...

24310
来自专栏老马寒门IT

Node入门教程(6)第五章:node 模块化(上)模块化演进

node 模块化 JS 诞生的时候,仅仅是为了实现网页表单的本地校验和简单的 dom 操作处理。所以并没有模块化的规范设计。 项目小的时候,我们可以通过命名空间...

2584
来自专栏坚毅的PHP

my python FAQ

python编码规范 http://google-styleguide.googlecode.com/svn/trunk/pyguide.html 判断对象是否...

3217
来自专栏Hongten

python开发_tkinter_获取文本框内容_给文本框添加键盘输入事件

==========================================================

361
来自专栏python学习之旅

Python+Selenium笔记(十二):数据驱动测试

(一)   前言 通过使用数据驱动测试,实现对输入值和预期结果的参数化。(例如:输入数据和预期结果可以直接读取Excel文档的数据) (二)   ddt 使用d...

3797
来自专栏龙首琴剑庐

数据库连接池-tomcat-jdbc食用笔记

现在 主流的数据库连接池有:Proxool、C3P0、DBCP、tomcat-jdbc、Druid。其中tomcat-jdbc是tomcat服务器比较可靠的 数...

3689

扫描关注云+社区