慢SQL优化前需要做什么

背景介绍

最近在做一些慢SQL（SELECT为主）优化的事情，遇到了记录一下~

MySQL的默认存储引擎是：Innodb

问题描述

第一次查询速度很慢（耗时: 60s左右），但是连续执行的话，查询速度都很快（耗时: 0.5s左右）

解决方案

遇到这个问题，第一时间就想到了应该是MySQL的缓存机制在“作乱”，于是乎开始以下尝试

一、禁用 query_cache 功能

• 查询相关配置：SHOW VARIABLES LIKE '%query_cache%';

方法一：修改配置临时关闭此功能：

• SET GLOBAL query_cache_size=0;
• SET GLOBAL query_cache_type=0;

方法二：在 SELECT 后面加 SQL_NO_CACHE

• e.g. SELECT SQL_NO_CACHE COUNT(1) FROM table1

但是上诉方法都**没有生效**？找了一些文章后发现：

其实MySQL在从MySQL 5.7.20开始，已经开始废弃query_cache，并在MySQL 8.0中删除，主要原因是不能与多核计算机上的高吞吐量工作负载进行扩展

二、设置 innodb_buffer_pool_size 属性值

• 查询相关配置：SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
  • Tips：修改该值之前先记录一下原值是多少，方便后续恢复使用

方法一：修改配置使缓存大小足够小

• SET GLOBAL innodb_buffer_pool_size = 1;

✅实测修改innodb_buffer_pool_size的值可以起到不走缓存的效果，赶紧展开优化工作吧

• 补充1：innodb_buffer_pool_size的值最小好像只能到：134217728 ==》128M
• 补充2：对于缓存值大于这个值的SQL可以做到每次都不走缓存，但是小一点的SQL就没办法了
  • 针对此问题在stackoverflow上看到这个答案，试用后有一定效果~（记得先备份，最后还原用）

    SET GLOBAL innodb_old_blocks_time=250; // This is 0.25 seconds
    SET GLOBAL innodb_old_blocks_pct=5;
    SET GLOBAL innodb_max_dirty_pages_pct=0;

参考文章：https://blog.csdn.net/u013257679/article/details/100548397