mysql系统数据库、服务器配置与变量、查询缓存、收缩数据库

mysql系统数据库

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

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

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

服务器配置与变量

mysqld选项，服务器系统变量和服务器状态变量
其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置

获取mysqld的可用选项列表：
mysqld --help --verbose
mysqld --print-defaults 获取默认设置
设置服务器选项方法：
在命令行中设置
shell> ./mysqld_safe --skip-name-resolve=1
在配置文件my.cnf中设置
skip_name_resolve=1

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

获取系统变量
SHOW GLOBAL VARIABLES;
SHOW [SESSION] VARIABLES;
SELECT @@VARIABLES;

修改服务器变量的值：
help SET

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

修改会话变量：
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

服务器状态变量：
分全局和会话两种
状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

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
将"||"视为连接操作符而非“或运算符”

查询缓存

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

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

哪些查询可能不会被缓存
查询语句中加了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，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足

query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE

query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报

query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；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语句才会缓存；其它均不予缓存

查询缓存相关的状态变量
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%

查询缓存内存使用率：(query_cache_size – qcache_free_memory) /
query_cache_size * 100%

收缩数据库

有时候删掉表里的很多数据，表的大小还是不变，这是是因为虽然数据没了，但是文件大小没有收缩回来，可以用命令来收缩

例如收缩test表：
optimize table test;