文章目录
- 一、MySQL安装
-
- 1.1 MySQL安装
- 1.2 MySQL修改密码
- 1.3 允许远程访问
- 二、MySQL架构与执行流程
-
- 2.1 MySQL架构组件
- 2.2 MySQL执行流程
-
- 2.2.1 解析器
- 2.2.2 优化器
- 2.2.3 执行计划
- 三、MySQL缓存
-
- 3.1 命中条件
- 3.2 工作流程
- 3.3.3 缓存说明
-
- 3.3.1 开启查询缓存
- 3.3.2 缓存参数
- 3.3.3 全局缓存状态
- 3.4 缓存测试
-
- 3.4.1 缓存命中测试
- 3.4.2 缓存清空测试
- 3.4.3 使用SQL Hint选择缓存
- 3.5 大数据量缓存性能测试
-
- 3.5.1 导入300W数据
- 3.5.2 什么SQL语句适合放入缓存
- 3.6 缓存的利用率
-
- 3.6.1 计算命中率
- 3.6.2 计算写入率
- 3.7 清空缓存
- 3.8 缓存失败
- 3.9 减少缓存碎片
- 3.10 缓存的弊端
一、MySQL安装
1.1 MySQL安装
1）下载安装wget命令
yum -y install wget
2）在线下载mysql安装包
mkdir mysql cd mysql wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar
3）解压mysql安装包：
tar -xvf mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar
安装包介绍：
| 安装包名称 | 简介 |
|---|---|
| mysql-community-client | MySQL客户端应用程序和工具 |
| mysql-community-common | 服务器和客户端库的通用文件 |
| mysql-community-devel | MySQL数据库客户端应用程序的开发头文件和库 |
| mysql-community-embedded-compat | MySQL服务器作为嵌入式库，与使用库版本18的应用程序兼容 |
| mysql-community-libs | MySQL数据库客户端应用程序的共享库 |
| mysql-community-libs-compat | 以前的MySQL安装的共享兼容性库 |
| mysql-community-server | 数据库服务器和相关工具 |
| mysql-community-server-debug | 调试服务器和插件二进制文件 |
| mysql-community-test | MySQL服务器的测试套件 |
| mysql-community | RPM的源代码看起来类似于mysql-community-8.0.24-1.el7.src.rpm，具体取决于所选的OS |
4）安装mysql服务：
rpm -ivh mysql-community-server-5.7.32-1.el7.x86_64.rpm
出现如下错误：
安装server被其他模块依赖；我们必须安装顺序来安装
5）安装如下顺序进行安装：
rpm -ivh mysql-community-common-5.7.32-1.el7.x86_64.rpm --nodeps --force rpm -ivh mysql-community-libs-5.7.32-1.el7.x86_64.rpm --nodeps --force rpm -ivh mysql-community-client-5.7.32-1.el7.x86_64.rpm --nodeps --force rpm -ivh mysql-community-server-5.7.32-1.el7.x86_64.rpm --nodeps --force
6）启动MySQL
systemctl start mysqld
1.2 MySQL修改密码
1）获取临时密码
grep 'temporary password' /var/log/mysqld.log
2）登录MySQL
mysql -uroot -p
然后输入刚刚复制的密码
3）将MySQL的密码校验强度改为低风险
set global validate_password_policy=LOW;
4）修改MySQL的密码长度
set global validate_password_length=5;
5）修改MySQL密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin';
1.3 允许远程访问
1）首先要关闭Cenots的防火墙
sudo systemctl disable firewalld
2）切换到mysql数据库
use mysql;
3）查看user表
select Host,User from user;
4）发现root用户只运行localhost访问，修改为允许任何地址访问
update user set Host='%' where User='root';
5）刷新权限
flush privileges;
6）使用Navicat连接工具测试
7）设置不区分大小写
Linux版本的MySQL默认是区分大小写了，如果想要使MySQL不区分大小写，可以在MySQL的配置文件（/etc/my.cnf）中设置：
lower_case_table-names=1
8）重启服务器
systemctl restart mysqld
- 创建测试表
CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `db01`; DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; insert into `class`(`id`,`name`) values (1,'Java'), (2,'UI'), (3,'产品'); DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student`(`id`,`name`,`class_id`) values (1,'张三',1), (2,'李四',1), (3,'王五',2), (4,'赵刘',1), (5,'钱七',3);
二、MySQL架构与执行流程
MySQL 数据库是单进程多线程的架构，和 SQL Server 类似，和 Oracle 不一样，Oracle 是多进程架构。
MySQL官网（5.7版本）：https://dev.mysql.com/doc/refman/5.7/en/
2.1 MySQL架构组件
MySQL主要有如下组件构成：
- Connectors：连接组件：
- 向外界的其他应用程序提供连接接口，实现外部程序访问MySQL，与MySQL进行交互
- Management Service&Utilities：服务管理与工具组件
- 提供MySQL系统管理、安全管理、权限校验、系统表管理、主从复制、集群等模块功能
- Connection Pool：连接池管理
- 负责外部连接MySQL校验、线程池的维护、线程重用、连接限制等，当一个外部连接成功连接MySQL之后，MySQL将会为其分配一个线程与固定的内存与该客户端进行交互，当此次会话结束后，线程将重回线程池；
- SQL Interface：MySQL对外提供的一系列功能性接口方法，例如我们调用存储过程、创建视图、触发器等都是调用对应的接口来实现对应的功能
- Parser：MySQL语法解析器，将对应的SQL进行解析、生成解析树，并对SQL语句进行语法校验；最后生成预处理报告，交由优化器进行优化
- Optimizer：优化器；我们实际发送的SQL语句并不是MySQL真正执行的SQL语句，优化器会有一系列成本计算方法来帮我们计算出一个最佳效率的SQL语句
- Cache & Buffer：查询缓存以及数据页缓冲模块
- Storage Engine：存储引擎；存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同，MySQL提供了插拔式存储引擎；
- File System：文件系统；主要是将数据存储在运行与裸设备的文件系统之上，并完成与存储引擎的交互。
2.2 MySQL执行流程
1）用户发送SQL语句来到MySQL服务端
2）首先查询缓存，如果缓存有数据直接响应，如果没有则进行下一步
3）解析器进行解析SQL语句、预处理、经过优化器优化SQL语句等一系列操作，调用存储引擎
4）存储引擎去磁盘上读取数据，最终将数据以MySQL的方式（即行和表的方式）响应给客户端，并且存入查询缓存一份。
2.2.1 解析器
一条SQL来到MySQL服务端如果没有命中缓存则接下来要被解析器（parser）进行语法的解析，首先将一段完整的SQL语句拆分成若干的词语（关键字、表名、列名、查询条件等），之后根据语法中的关键字进行关键字提取，生成一颗SQL解析树，之后进行SQL语句的预处理，例如检查列名是否存在，表是否存在，语法是否正确等；
2.2.2 优化器
SQL语句在经过解析器得到语法树之后，MySQL内部提供有优化器对SQL语句进行优化处理，优化器会根据一系列成本计算规则来最终得出一个最优方案的SQL语句，例如自动选择小表驱动大表、max()、min()、count()等函数的优化、索引列的选择、覆盖索引优化等优化；
在我们执行SQL语句时并不是我写什么SQL语句MySQL就真正执行什么SQL语句，因为MySQL内部有优化器对其进行优化，可能最终执行的不是我们原来的SQL语句，但不管怎么优化，SQL语句最终查询的结果肯定是一致的；
2.2.3 执行计划
优化器对SQL语句优化之后，会产生一个"优化清单"，这个"优化清单"就是执行计划，执行计划中可以反映SQL语句是如何进行优化的，如：表的执行顺序、表的查询情况（子查询还是关联查询还是union等等…）、命中表分区情况、是否命中索引、使用了什么索引、扫描的行数、过滤的比例、是否全表扫描、是否覆盖索引、是否索引下推等等。
有了这份"优化清单"（执行计划），我们就可以根据执行计划反馈的信息来针对性的优化SQL语句，执行计划是我们SQL调优的一个重要参考信息；
三、MySQL缓存
MySQL缓存机制就是缓存SQL文本及缓存结果，用KV形式保存在服务器内存中，如果运行相同的sql,服务器直接从缓存中去获取结果，不需要在再去解析、优化、执行sql。 如果这个表修改了（有修改的数据），那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。表中得任何改变是指表中任何数据或者是结构的改变，包括insert、update、delete、truncate、alter table、drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询，显然，对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能。
3.1 命中条件
- 缓存是有命中条件的，并不是所有的SQL语句都会进入缓存查找。
缓存存在一个hash表中，通过查询SQL，查询数据库，客户端协议等作为key，在判断命中前，mysql不会解析SQL，而是使用SQL去查询缓存，SQL上的任何字符的不同，如空格，注释，都会导致缓存不命中。如果查询有不确定的数据current_date()，那么查询完成后结果者不会被缓存，包含不确定的数的是不会放置到缓存中。
3.2 工作流程
1）服务器接收SQL，以SQL和一些其他条件为key查找缓存。
2）如果找到了缓存，则直接返回缓存。
3）如果没有找到缓存，则执行SQL查询，包括原来的SQL解析，优化等。
4）执行完SQL查询结果以后，将SQL查询结果放入查询缓存。
3.3.3 缓存说明
3.3.1 开启查询缓存
MySQL默认是将查询缓存关闭的，我们需要在配置文件中打开。
查询当前数据库缓存是否开启：
mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | OFF | +--------------------+ 1 row in set, 1 warning (0.00 sec)
修改配置文件：linux的是/etc/my.conf，Windows的是C:ProgramDataMySQLMySQL Server 5.7my.ini
在mysqld组下面增加：
query_cache_type=1
重启MySQL服务：
systemctl restart mysqld
再次查看：
mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | on | +--------------------+ 1 row in set, 1 warning (0.00 sec)
3.3.2 缓存参数
输入如下命令查询缓存相关参数
show variables like "%query_cache%";
- have_query_cache：当前数据库版本是否支持缓存 NO：不支持 YES：支持
- query_cache_limit：查询缓存能缓存单个SQL语句的最大容量，单位：B
- query_cache_size：缓存使用的总内存空间大小，单位是字节，这个值必须是1024的整数倍，否则MySQL实际分配可能跟这个数值不同
- query_cache_min_res_unit：分配内存块时的最小单位大小
- MySQL并不是一下子分配query_cache_size大小的内存作为缓存，而且将整个查询缓存的大小分成了若干个内存块，query_cache_min_res_unit正是决定这些块的大小，需要注意的是，即使缓存的数据没有达到一个缓存块大小也需要占用一个缓存块大小的空间。如果超出单个缓存块，那么需要申请多个缓存块，当查询完发现有缓存块内存多余，则会将多余的缓存块内存释放，造成缓存碎片。
- query_cache_type：是否打开缓存
- OFF(0)：关闭缓存（默认值）
- ON(1)：开启缓存（任意的select语句的结果集都会被缓存）
- DEMAND：只有明确写了SQL_CACHE的查询才会写入缓存
select SQL_CACHE * from t_goods; -- 将查询结果放入缓存（前提缓存是开启的） select SQL_NO_CACHE * from t_goods; -- 不缓存查询结果
- query_cache_wlock_invalidate：如果某个数据表被锁住，是否仍然从缓存中返回数据，默认是OFF，表示仍然可以返回。
3.3.3 全局缓存状态
输入如下命令查询全局缓存状态：
show global status like '%Qcache%';
- Qcache_free_blocks：缓存池中空闲块的个数
- Qcache_free_memory：缓存中空闲内存量
- Qcache_hits：缓存命中次数
- Qcache_inserts：缓存写入次数
- Qcache_lowmen_prunes：因内存不足删除缓存次数
- Qcache_not_cached：查询未被缓存次数，例如查询结果超出缓存块大小，查询中包含可变函数等，或者未查询到数据的行、或者SQL语句中使用了SQL_NO_CACHE等。
- Qcache_queries_in_cache：当前缓存中缓存的SQL数量
- Qcache_total_blocks：缓存总block数
3.4 缓存测试
3.4.1 缓存命中测试
- 创建数据库：
create database test; use test;
- 创建一张测试表：
CREATE TABLE `goods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `price` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
- 插入测试数据：
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (1, '华为4G全面屏游戏手机', '华为手机', 5299.00); INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (2, '神舟战神游戏本', '神舟笔记本', 4599.00); INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (3, '小米5G全面屏手机', '小米手机', 2899.00); INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (4, '小米4G游戏全面屏拍照手机', '小米手机', 1899.00);
- 查询当前缓存使用情况：
-- 首先执行一次查询语句 select * from goods; -- 查看是否有写入到缓存 show global status like '%Qcache%';
发现缓存写入次数+1
我们再次执行SQL语句，查看缓存使用情况：
select * from goods; show global status like '%Qcache%';
缓存命中次数+1
- 我们执行多次SQL语句，查看缓存插入和命中情况：
-- 写入缓存+1 select * from goods g; -- 注意仔细看SQL语句（取了个别名） -- 写入缓存+1 select * from goods where id=1; -- 写入缓存+1 select * from goods where id=2; -- 写入缓存+1 select * from goods g where id=1; -- 命中缓存+1 select * from goods g; -- 命中缓存+1 select * from goods where id=1; -- 命中缓存+1 select * from goods where id=2;
我们先计算一遍：
缓存写入数（Qcache_inserts）为1（原来的一次）+4=5
缓存命中数（Qcache_hits）为1（原来的一次）+3=4
- 查看缓存写入和命中情况：
show global status like '%Qcache%';
- 这里要注意一点，即使有的SQL查询不出来结果，也是会写入缓存的
-- 即使查询不到结果集,也会写入缓存 select * from goods where id=10; -- 即使查询不到结果集也会命中缓存 select * from goods where id=10; show global status like '%Qcache%';
发现即使有的SQL没有查询到结果集，也会写入缓存，并且再次查询也会命中缓存
3.4.2 缓存清空测试
我们上提到过，对表的任何DML操作都会导致缓存清空，包括insert,update,delete,truncate,alter table,drop table等。
为了方便测试，我重启MySQL服务器（缓存信息全部清空）：
systemctl restart mysqld
- 执行SQL语句观察缓存命中变化
mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from goods where id=1; # 写入缓存 +----+-------------------------------+--------------+---------+ | id | title | name | price | +----+-------------------------------+--------------+---------+ | 1 | 华为4G全面屏游戏手机 | 华为手机 | 5399.00 | +----+-------------------------------+--------------+---------+ 1 row in set (0.00 sec) mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1030296 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | # 当前缓存了一条SQL | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> select * from goods where id=1; # 命中缓存 +----+-------------------------------+--------------+---------+ | id | title | name | price | +----+-------------------------------+--------------+---------+ | 1 | 华为4G全面屏游戏手机 | 华为手机 | 5399.00 | +----+-------------------------------+--------------+---------+ 1 row in set (0.00 sec) mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1030296 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | # 当前缓存了一条SQL | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> update goods set price=8999 where id=1; # 清空缓存 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031832 | | Qcache_hits | 1 | | Qcache_inserts | 1 | # 注意:这里显示的是你当前写入了多少次缓存 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 0 | # 当前缓存了0条SQL | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.01 sec) mysql> select * from goods where id=1; # 写入缓存 +----+-------------------------------+--------------+---------+ | id | title | name | price | +----+-------------------------------+--------------+---------+ | 1 | 华为4G全面屏游戏手机 | 华为手机 | 8999.00 | +----+-------------------------------+--------------+---------+ 1 row in set (0.00 sec) mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1030296 | | Qcache_hits | 1 | | Qcache_inserts | 2 | # 发现缓存写入次数增加 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | # 当前缓存了一条SQL | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql>
通过观察Qcache_queries_in_cache参数可以发现，执行完update语句之后，有关于这张表的缓存全部清空，当再次执行SQL语句的时候，会重新写入缓存。
对MySQL表的任意DML操作都会导致有关于这张表的所有缓存全部清空。
3.4.3 使用SQL Hint选择缓存
我们知道MySQL的查询缓存一旦开启，会将本次SQL语句的结果集全部放入缓存中，这样其实是非常不友好的，因为我们知道，对于表的任何DML操作都会导致这张表的缓存全部清空。因此我们可以指定哪些SQL语句存入缓存，哪些不存。
- SQL_CACHE：将此次SQL语句的结果集存入缓存（前提是当前MySQL服务器时开启缓存的）
- SQL_NO_CACHE：此次SQL语句的结果集不存入缓存
执行如下SQL语句，分析缓存执行情况：
-- 缓存写入次数0 show global status like '%Qcache%'; -- 存入缓存 select * from goods; -- 缓存写入次数1 show global status like '%Qcache%'; -- 不存入缓存 select SQL_NO_CACHE * from goods g; -- 注意:取了个别名 -- 缓存写入次数还是1 show global status like '%Qcache%';
3.5 大数据量缓存性能测试
3.5.1 导入300W数据
我们已经明白缓存何时写入、何时清空，何时命中，接下来我们插入300W数据，来执行SQL语句，体验缓存给我们带来性能上的提升。
- 创建测试表：
CREATE TABLE `userinfo` ( `id` int(10) NOT NULL COMMENT '用户id', `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名', `age` int(3) NULL DEFAULT NULL COMMENT '年龄', `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号', `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男 ‘1’-女', `desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍', `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间', `login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间', `pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址', `look` int(10) NULL DEFAULT NULL COMMENT '查看数', PRIMARY KEY (`id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
- 编写存储过程
create procedure test_insert(count int) begin declare i int default 1; while i<=count do INSERT INTO userinfo values( i, -- id uuid(), -- username CEILING(RAND()*90+10), -- age FLOOR(RAND()*100000000000), -- phone round(FORMAT(rand(),1)), -- gender uuid(), -- desc now(), -- register_time now(), -- login_time uuid(), -- pic CEILING(RAND()*90+10) -- look ); set i=i+1; end while; end;
- 执行脚本，批量插入300W数据：
-- 关闭唯一性校验,提高批量插入速度 set unique_checks=0; -- 控制在一个事务,避免频繁开启/提交事务 start transaction; call test_insert(3000000); -- 模拟300W的数据 commit;
- 执行如下SQL测试缓存效率：
-- 第一次查询,将结果存入缓存(2.18) select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a'; -- 走缓存,效率快(0.00s) select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a'; -- 对表进行修改,关于这张表的缓存全部清空 update userinfo set username='1' where id=1; -- 再次查询,发现效率低,但又存入缓存了(2.51s) select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a'; -- 再次查询,走缓存,效率高(0.00s) select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';
再进行多次测试：
-- 写入缓存(2.45s) select * from userinfo where age=1; -- 命中缓存(0.00s) select * from userinfo where age=1; -- 写入缓存(2.25s) select * from userinfo where phone='1'; -- 命中缓存(0.00s) select * from userinfo where phone='1'; -- 命中缓存(0.00s) select * from userinfo where phone='1'; -- 写入缓存(1.99s) select * from userinfo where look=1; -- 命中缓存(0.00s) select * from userinfo where look=1; -- 命中缓存(0.00s) select * from userinfo where look=1;
发现命中缓存确实比没有命中缓存查询效率高多了。
3.5.2 什么SQL语句适合放入缓存
场景举例：
select * from xxx age > 18 and age <25; -- 在大量数据中，筛选出几条数据（这样的SQL语句最适合存入查询缓存） select * from xxx hobby in (1,2,3,4); select * from xxx limit 1000000,100; -- 在大量数据中，做分组操作（这样的SQL语句最适合存入查询缓存） select avg(age) from xxx group dept_id;
尽量缓存结果集不会太大的SQL语句（建立缓存时间短，就算缓存失效对我影响也不大），但是表中数据量大（表中数据量大，意味着查询速度慢），这样的SQL语句下次查询缓存速度上就能得到明显的提升。
什么情况下不适合使用查询缓存？
场景举例：
-- 整表缓存 select SQL_NO_CACHE * from xxx; -- 多表缓存 select SQL_NO_CACHE * from xxx x1 inner join xxx x2 on x1.x=x2.x; -- 大范围缓存 select SQL_NO_CACHE * from xxx where age>18;
3.6 缓存的利用率
3.6.1 计算命中率
缓存命中率 = Qcache_hits（缓存命中次数） / Com_select（查询次数，命中缓存时这个参数不会加）
3.6.2 计算写入率
缓存写入率 = Qcache_inserts（缓存写入次数） / Com_select（查询次数）
示例：
show status like 'com_select'; -- 显示当前会话的查询次数 show status like '%Qcache%'; -- 显示当前会话缓存使用情况
命中率：9 / 20 = 0.27 = 45%
写入率：13 / 20 = 0.18 = 65%
3.7 清空缓存
如果当前的MySQL缓存了过多的SQL语句没有及时清除，我们可以使用命令将缓存清空，来是否缓存：
reset query cache;
3.8 缓存失败
- 1、缓存碎片、内存不足、数据修改都会造缓存失败。如果配置了足够的缓存空间，而且query_cache_min_res_unit的大小也设置的合理。那么缓存失效应该主要是数据修改导致的。可以通过Qcache_not_cached参数来查看有多少次失效是由于内存不足导致的。
- 2、当某个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态（默认状态）
- 3、在Innodb中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。
3.9 减少缓存碎片
- 1）设置合适的query_cache_min_res_unit值
我们在分析缓存参的时候知道，MySQL并不是一下子分配query_cache_size内存作为缓存，而是将内存分为若干个query_cache_min_res_unit小内存，即使本次缓存没有达到一个缓存块大小也需要占用一个缓存块大小。但query_cache_min_res_unit也不能设置太小，设置太小会造成MySQL不断的分配很多个内存块来缓存本次SQL语句的结果集。
- 2）使用 FLUSH QUERY CACHE 命令可以有效的整理缓存碎片，来释放碎片占用的空间，但需要注意的是这个命令在整理缓存期间，会导致其他连接无法使用查询缓存。
3.10 缓存的弊端
- 1）查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。**因此读写非常频繁的业务场景，缓存开启还不如关闭效率高。**除非你的业务有一张静态表，很长时间更新一次，比如系统配置表，那么这张表的查询才适合做查询缓存。
- 2）缓存和数据耦合在了一起，我们在开发中，缓存和数据应该是独立分开的，这样的话有利于单独进行扩容；比如缓存的配置需要更好的，数据库服务器的配置一般就行了；
- 3）不支持持久化、不支持可视化等；
- 4）大多数情况下，我们都是采用市面上比较成熟的软件来进行缓存，如redis、memcache等，这些软件不管是性能、功能、操作上都比MySQL缓存要强大的多，而且利于我们进行性能扩容（如搭建缓存集群）。
MySQL官网上已经说明，在MySQL8.0及以上版本，MySQL的缓存功能已经被删除了