106 lines
4.6 KiB
Markdown
106 lines
4.6 KiB
Markdown
---
|
||
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
|
||
|