查询缓存

查询缓存

MySQL中的系统数据库

• mysql数据库 是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

• performance\_schema数据库 MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE\_SCHEMA，用户不能创建存储引擎为PERFORMANCE\_SCHEMA的表

• information\_schema数据库 MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information\_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

服务器配置

• mysqld选项，服务器系统变量和服务器状态变量https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.htmlhttps://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

• 注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置（需要区别服务端选项、服务端变量、以及客户端命令）

• 获取mysqld的可用选项列表：

• 设置服务器选项方法：

• 在命令行中设置

• 在配置文件my.cnf中设置

服务器端设置

• 服务器系统变量：分全局和会话两种

• 获取系统变量

• 修改服务器变量的值：

• 修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

• 修改会话变量：

• 服务器状态变量： 分全局和会话两种

• 状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

服务器变量SQL\_MODE（特殊的服务端变量，默认是空值）

• SQL\_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/

• 常见MODE:
• NO\_AUTO\_CREATE\_USER 禁止GRANT创建密码为空的用户
• NO\_ZERO\_DATE 在严格模式，不允许使用‘0000-00-00’的时间
• ONLY\_FULL\_GROUP\_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
• NO\_BACKSLASH\_ESCAPES 反斜杠“\\”作为普通字符而非转义字符
• PIPES\_AS\_CONCAT 将”||”视为连接操作符而非“或运算符”

mysql架构

查询执行路径

查询缓存

• 查询缓存（ Query Cache ）原理 缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写

• 优缺点 不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

• 哪些查询可能不会被缓存
• 查询语句中加了SQL\_NO\_CACHE参数（select 中一个选项 sql\_no\_cache不用缓存，去执行查询）
• 查询语句中含有获得值的函数，包含自定义函数，如：NOW()CURDATE()、GET\_LOCK()、RAND()、CONVERT\_TZ()等
• 对系统数据库的查询：mysql、information\_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
• 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
• 对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
• 事务隔离级别为Serializable时，所有查询语句都不能缓存

查询缓存相关的服务器变量

• query\_cache\_min\_res\_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足（缓存最小分配空间 默认是4K 如果数据只有3K，注浪费了1K空间，多条缓存，没有用的空间，相当于碎片，浪费内存空间，若分配的较小，如两个2K，频繁的操作，也会影响性能）

• query\_cache\_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL\_NO\_CACHE（查询缓存的大小超过1M就不缓存）

• query\_cache\_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报(分配/设置缓存的大小)

• query\_cache\_wlock\_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许（默认是返回，ON表示不返回，表锁了也可以返回数据，如果事务没有结束，表上会加锁，此时事务是运行中，会有脏数据也会写入到缓存中，所以建议开启，避免脏/无效数据写入缓存中，除非有很特殊的需求）

• query\_cache\_type：是否开启缓存功能，取值为ON, OFF, DEMAND

• SELECT语句的缓存控制
• SQL\_CACHE：显式指定存储查询结果于缓存之中
• SQL\_NO\_CACHE：显式查询结果不予缓存

• query\_cache\_type参数变量
• query\_cache\_type的值为OFF或0时，查询缓存功能关闭
• query\_cache\_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL\_NO\_CACHE，不予缓存，此为默认值
• query\_cache\_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL\_CACHE的SELECT语句才会缓存；其它均不予缓存
• 参看：https://mariadb.com/kb/en/library/server-system-variables/#query\\_cache\\_typehttps://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

优化查询缓存

• 查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE ‘Qcache%’;

• Qcache\_free\_blocks：处于空闲状态 Query Cache中内存 Block 数

• Qcache\_total\_blocks：Query Cache 中总Block ，当Qcache\_free\_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片

• Qcache\_free\_memory：处于空闲状态的 Query Cache 内存总量

• Qcache\_hits：Query Cache 命中次数

• Qcache\_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数

• Qcache\_lowmem\_prunes：记录因为内存不足而被移除出查询缓存的查询数

• Qcache\_not\_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL以及由于 query\_cache\_type 设置的不会被 Cache 的 SQL语句

• Qcache\_queries\_in\_cache：在 Query Cache 中的 SQL 数量

命中率和内存使用率估算

• 查询缓存中内存块的最小分配单位query\_cache\_min\_res\_unit ：(query\_cache\_size - Qcache\_free\_memory) / Qcache\_queries\_in\_cache （缓存全部值的大小 - 空闲的内存总量 ）/ 查询被缓存的记录数 ==== 使用多少内存总量 / 查询被缓存的记录数缓存碎片多，可以减小分配的内存块空间

• 查询缓存命中率 ：Qcache\_hits / ( Qcache\_hits + Qcache\_inserts ) \* 100% 命中次数 / （命令次数 + 未命中次数 ）\* 100%

• 查询缓存内存使用率：(query\_cache\_size - qcache\_free\_memory) /query\_cache\_size \* 100% （ 查询缓存大小 - 空闲的内存问题） / 查询缓存大小 \* 100%

InnoDB存储引擎缓存

• InnoDB存储引擎的缓冲池: 通常InnoDB存储引擎缓冲池的命中不应该小于99%

• 查看相关状态变量：show global status like ‘innodb%read%’\\G
• Innodb\_buffer\_pool\_reads: 表示从物理磁盘读取页的次数
• Innodb\_buffer\_pool\_read\_ahead: 预读的次数
• Innodb\_buffer\_pool\_read\_ahead\_evicted: 预读页，但是没有读取就从缓冲池中被替换的页数量，一般用来判断预读的效率
• Innodb\_buffer\_pool\_read\_requests: 从缓冲池中读取页次数
• Innodb\_data\_read: 总共读入的字节数
• Innodb\_data\_reads: 发起读取请求的次数，每次读取可能需要读取多个页

• Innodb缓冲池命中率计算： Innodb\_buffer\_pool\_read\_requests/(Innodb\_buffer\_pool\_read\_requests+Innodb\_buffer\_pool\_read\_ahead+Innodb\_buffer\_pool\_reads)

• 平均每次读取的字节数： Innodb\_data\_read/Innodb\_data\_reads

小结