--- title: "Mysql 缓存" date: 2019-10-30T11:00:49+08:00 lastmod: 2019-10-30T11:00:49+08:00 keywords: [] tags: ["cache", "mysql", "缓存"] categories: ["database"] --- ## MySQL 超时 - connect_timeout 与客户端连接建立超时,默认10秒 - interactive_timeout 交互终端超时断开,默认28800秒 - wait_timeout 非交互终端超时断开,默认28800秒 - net_read_timeout 从客户端读取数据超时,默认30秒 - net_write_timeout 向客户端写入数据超时,默认60秒 - innodb_lock_wait_timeout 锁等待超时,默认50秒 - innodb_rollback_on_timeout 超时后回滚整个事务操作,默认OFF - slave_net_timeout 从库读取binlog失败后,等待指定秒后重新连接主库,默认60秒 ## 查询缓存配置 ** MySQL 8.0 已关闭该功能 ** - have_query_cache 是否支持查询缓存,默认yes - query_cache_limit select结果集大于该值时不缓存,默认1048576 - qeury_cache_min_res_unit 查询缓存申请的内存单位(块)大小,默认4096,一般不改 - query_cache_size 查询缓存大小,默认1048576 - query_cache_type - 0(OFF) 关闭查询缓存,默认 - 1(ON) 如果select语句没有sql_no_cache选项,则使用查询缓存 - 2(DEMOND) 如果select语句没有sql_cache选项,则不使用查询缓存 - query_cache_wlock_invalidate - 0(OFF) 锁表后,该表的查询缓存依旧有效,默认 - 1(ON) 锁表后,该表的查询缓存失效 ## 查询缓存状态 ** MySQL 8.0 已关闭该功能 ** - Qcache_free_blocks 查询缓存中的内存碎片数量 - 碎片过多表明查询结果集较小,可适当减小query_cache_min_res_unit - 整理查询缓存中的碎片,获得一个较大的内存空闲空间 ```sql flush query cache ``` - 缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% - Qcache_free_memory 查询缓存中的可用内存 - Qcache_hits 使用查询缓存的次数 - Qcache_inserts 查询缓存中缓存过的select结果集数量(包括当前正在缓存的) - Qcache_lowmen_prunes 因查询缓存已满而删除的select结果集数量 - 该值较大表明查询缓存较小 - Qcache_not_cached 没有进入查询缓存的select语句个数 - Qcache_queries_in_cache 查询缓存中当前缓存着select结果集数量 - Qcache_total_blocks 查询缓存中的块总数 - Com_select 执行过的select语句数量 - 查询缓存的命中率 = Qcache_hits / Com_select * 100% ## 排序缓存配置 - sort_buffer_size 排序缓存大小,默认262144 - max_length_for_sort_data 排序sql中的全部字段长度总和小于该值时,使用优化排序方式,否则使用常规排序方式,默认1024 - [参考](https://www.cnblogs.com/moss_tan_jun/p/6021822.html) - max_sort_length 使用列的前(默认1024)个字节排序 - innodb_sort_buffer_size InnoDB用于创建/重建索引的排序缓存,默认1048576 ## 排序缓存状态 - Sort_merge_passes 使用临时文件完成排序操作的次数 - 增加sort_buffer_size可减少使用临时文件排序的次数 - Sort_range 使用范围排序的次数 - Sort_rows 已经排序的记录行数 - Sort_scan 通过全表扫描完成排序的次数 ## join 连接缓存 - join_buffer_size 两表join但不能使用索引时,为每张表分配的连接缓存,默认262144 - 尽量实现索引join ## 表/表结构 缓存配置 - table_open_cache 可以缓存表和视图的数量 - table_defination_cache 可以存储的表结构数量 - open_files_limit 可以打开的文件数量 ## InnoDB 重做日志缓存配置 - innodb_log_buffer_size 事务提交前,把重做日志写入缓存中 - innodb_flush_log_at_trx_commit - 0 每隔一秒,把日志缓存写到日志文件,并刷新到磁盘 - 1 每次提交时,把日志缓存写到日志文件,并刷新到磁盘 - 2 每次提交是,把日志缓存写到日志文件,每隔一秒刷新到磁盘 ## InnoDB 预读配置 - innodb_read_ahead_threshold 预读的前后数据库个数 ## FLUSH 语句 - HOSTS 清空主机表 - LOGS 关闭binlog文件,创建新编号binlog文件 - PRIVILEGES 从数据库授权表中重新装载权限到缓存中 - TABLES 关闭打开的表,清空查询缓存 - QUERY CACHE 整理碎片,不影响查询缓存中现有数据 - 清空查询缓存 ```sql RESET QUERY CACHE ``` - TABLES WITH READ LOCK 关闭打开的表并加一个只读锁 - 解锁 ```sql UNLOCK TABLES ``` - STATUS 重置大多数状态变量到0 - MASTER(RESET MASTER) 删除全部binlog文件,重建一个新的binlog文件 - SLAVE(RESET SLAVE) 忘记主库binlog文件位置,删除relay log