Mysql缓存调优的基本知识（附Demo）-EW帮帮网

前言

基本的知识推荐阅读：

java框架零基础从入门到精通的学习路线附开源项目面经等（超全）
Mysql优化高级篇（全）
Mysql底层原理详细剖析+常见面试题（全）

MySQL的缓存调优涉及多个方面，包括查询缓存、InnoDB缓冲池、表缓存等

查询缓存（Query Cache）
查询缓存是MySQL中缓存查询结果的机制，当相同的查询再次执行时，可以直接从缓存中获取结果，而无需重新解析和执行查询
InnoDB缓冲池（InnoDB Buffer Pool）
InnoDB缓冲池是InnoDB存储引擎中用来缓存数据和索引的主要内存区域
优化缓冲池可以显著提高InnoDB表的性能
表缓存（Table Cache）
表缓存用于缓存表的元数据和表文件的文件描述符，减少打开和关闭表的开销

1. 配置

一、查询缓存配置：

在my.cnf或my.ini配置文件中进行配置：

[mysqld]
query_cache_type = 1  # 0表示关闭，1表示开启，2表示按需开启
query_cache_size = 64M  # 缓存大小
query_cache_limit = 1M  # 单个查询缓存的最大大小

二、InnoDB缓冲池配置：

[mysqld]
innodb_buffer_pool_size = 1G  # 缓冲池大小，建议设置为物理内存的50%到75%
innodb_buffer_pool_instances = 8  # 缓冲池实例数，适用于多核CPU
innodb_log_buffer_size = 16M  # 日志缓冲区大小

三、表缓存配置：

[mysqld]
table_open_cache = 2000  # 打开表缓存大小

附出博主的配置：

port = 3306  # MySQL服务器的端口
socket = /tmp/mysql.sock  # MySQL服务器的Unix socket文件路径
innodb_force_recovery = 1  # 启用InnoDB恢复模式，数值范围为0-6，1表示最轻微的恢复

; Here follows entries for some specific programs
; 下面是一些特定程序的配置条目

; The MySQL server
[wampmysqld64]
;skip-grant-tables  # （已注释）跳过权限表的加载，通常用于忘记密码的恢复
port = 3306  # MySQL服务器的端口
socket = /tmp/mysql.sock  # MySQL服务器的Unix socket文件路径
key_buffer_size = 256M  # 用于MyISAM表索引的缓存大小
max_allowed_packet = 1M  # 单个查询允许的最大数据包大小

;Added to reduce memory used (minimum is 400)
table_definition_cache = 600  # 表定义缓存的数量，增大可以减少表打开的频率

sort_buffer_size = 2M  # 每个连接的排序缓存大小
net_buffer_length = 8K  # 网络缓存初始大小
read_buffer_size = 2M  # MyISAM表扫描的读取缓存大小
read_rnd_buffer_size = 2M  # MyISAM表随机读取的缓存大小
myisam_sort_buffer_size = 64M  # MyISAM表重建索引的缓存大小
basedir="c:/wamp64/bin/mysql/mysql5.7.21"  # MySQL安装的基础路径
log-error="c:/wamp64/logs/mysql.log"  # 错误日志文件路径
datadir="c:/wamp64/bin/mysql/mysql5.7.21/data"  # 数据文件路径

lc-messages-dir="c:/wamp64/bin/mysql/mysql5.7.21/share"  # 本地化消息文件目录
lc-messages=en_US  # 本地化消息语言

secure_file_priv="c:/wamp64/tmp"  # 限制LOAD DATA, SELECT ... INTO OUTFILE, and LOAD_FILE()的文件操作路径
skip-ssl  # 跳过SSL支持

explicit_defaults_for_timestamp=true  # 在没有提供默认值的情况下，明确的默认为TIMESTAMP类型字段赋值

; Set the SQL mode to strict
;sql-mode=""  # （已注释）默认SQL模式为空
sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"  # 设置SQL模式

skip-federated  # 跳过Federated存储引擎的支持

server-id = 1  # 服务器ID，用于复制

skip-slave-start  # 跳过从服务器启动时自动启动复制线程

early-plugin-load=""  # 提前加载的插件列表

;innodb_data_home_dir = C:/mysql/data/  # （已注释）InnoDB数据文件的主目录
innodb_data_file_path = ibdata1:12M:autoextend  # InnoDB数据文件路径及大小设置，自动扩展
;innodb_log_group_home_dir = C:/mysql/data/  # （已注释）InnoDB日志文件的主目录
;innodb_log_arch_dir = C:/mysql/data/  # （已注释）InnoDB日志归档目录
; You can set .._buffer_pool_size up to 50 - 80 %
; of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M  # InnoDB缓冲池大小，建议设置为物理内存的50%到80%
; Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 256M  # InnoDB日志文件大小，建议设置为缓冲池大小的25%
innodb_log_buffer_size = 8M  # InnoDB日志缓冲区大小
innodb_thread_concurrency = 16  # InnoDB线程并发数
innodb_flush_log_at_trx_commit = 2  # InnoDB日志刷新策略，2表示每秒刷新一次日志
innodb_lock_wait_timeout = 50  # InnoDB锁等待超时时间
innodb_flush_method=normal  # InnoDB刷盘方法
innodb_use_native_aio = true  # 启用本地异步I/O

[mysqldump]
quick  # 启用快速导出模式
max_allowed_packet = 16M  # mysqldump的最大数据包大小

[mysql]
no-auto-rehash  # 禁用自动补全功能
; Remove the next comment character if you are not familiar with SQL
;safe-updates  # （已注释）启用安全更新模式

[isamchk]
key_buffer_size = 20M  # 用于ISAM表检查和修复的键缓冲区大小
sort_buffer_size = 20M  # 用于ISAM表检查和修复的排序缓冲区大小
read_buffer_size = 2M  # 用于ISAM表检查和修复的读取缓冲区大小
write_buffer_size = 2M  # 用于ISAM表检查和修复的写入缓冲区大小

[myisamchk]
key_buffer_size = 20M  # 用于MyISAM表检查和修复的键缓冲区大小
sort_buffer_size_size = 20M  # （注意错字）用于MyISAM表检查和修复的排序缓冲区大小
read_buffer_size = 2M  # 用于MyISAM表检查和修复的读取缓冲区大小
write_buffer_size = 2M  # 用于MyISAM表检查和修复的写入缓冲区大小

[mysqlhotcopy]
interactive-timeout  # 启用交互超时

[mysqld]
port = 3306  # MySQL服务器的端口

配置文件包括了MySQL服务器的基本配置、内存调优参数、日志文件位置、InnoDB存储引擎设置以及其他特定工具（如mysqldump, mysql, isamchk, myisamchk, mysqlhotcopy）的配置

2. 缓存

查询缓存的基本命令如下：

通过正确配置和调优MySQL的缓存，可以显著提升数据库的性能和响应速度
定期监控和调整缓存配置也是确保数据库性能的关键

# 查询缓存相关命令
SHOW VARIABLES LIKE 'query_cache%';  # 查看查询缓存相关变量
SHOW STATUS LIKE 'Qcache%';  # 查看查询缓存状态

# InnoDB缓冲池相关命令
SHOW ENGINE INNODB STATUS;  # 查看InnoDB引擎状态，包括缓冲池信息
SHOW VARIABLES LIKE 'innodb_buffer_pool%';  # 查看InnoDB缓冲池相关变量
SHOW STATUS LIKE 'Innodb_buffer_pool%';  # 查看InnoDB缓冲池状态

# 表缓存相关命令
SHOW VARIABLES LIKE 'table_open_cache';  # 查看表缓存配置
SHOW STATUS LIKE 'Open_tables';  # 查看当前打开的表数
SHOW STATUS LIKE 'Opened_tables';  # 查看自服务器启动以来总共打开的表数

截图如下：

在这里插入图片描述

相应的清空命令如下：

清空查询缓存：RESET QUERY CACHE;
清空InnoDB缓冲池
InnoDB缓冲池通常不需要手动清空，重启MySQL服务会自动清空缓冲池
清空表缓存：FLUSH TABLES;

3. 策略

和上面的缓存有些关联，不过按照逻辑，还是照样扯一下

一、使用SQL_NO_CACHE关键字避免特定查询使用缓存

SELECT SQL_NO_CACHE * FROM my_table WHERE column = 'value';

二、使用SQL_CACHE关键字强制特定查询使用缓存

SELECT SQL_CACHE * FROM my_table WHERE column = 'value';

三、定期清理查询缓存以防止碎片化
定期清理查询缓存可以防止缓存碎片化，保持查询缓存的效率：RESET QUERY CACHE;

四、查询缓存状态和配置命令
使用以下命令查看查询缓存的状态和配置：

SHOW VARIABLES LIKE 'query_cache%';  # 查看查询缓存相关变量
SHOW STATUS LIKE 'Qcache%';  # 查看查询缓存状态

Mysql缓存调优的基本知识（附Demo）

目录

前言

1. 配置

2. 缓存

3. 策略

网站公告

今日签到

热门文章

最新发布