以下操作有可能在客户端（DataGrip、Navicat、SQLyog）执行无效，需要终端进入
一、优化步骤
1、优化的目标
利用宏观的
监控工具
和微观的
日志分析
帮我们快速找到调优的思路和方法
2、优化流程步骤
2.1、优化过程
我们可以通过使用分析工具来观察数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是 SQL 执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动
1、首先在
S1部分
，我们需要观察服务器的状态是否存在周期性的波动。如果
存在周期性波动
，有可能是周期性节点的原因，如：11、促销活动等。这样的话，我们可以通过
A1
这一步骤解决，也就是加缓存或者更改缓存失效策略
2、如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步
分析查询延时和卡顿的原因
。接下来进入
S2
这步，需要
开启慢查询
。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置
long_query_time
参数定义
慢的阈值
，如果SQL执行时间超过了
long_query_time
，则会认为是慢查询。当收集上来这些慢查询之后，就可以通过分析工具对慢查询日志进行分析
3、在
S3
这步中，就知道了执行慢的SQL，这样就可以针对性地用
EXPLAIN 或 DESCRIBE
查看对应 SQL 语句的执行计划或者利用
show_profile
查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长，还是等待时间长
4、如果是 SQL 等待时间长，进入
A2
步骤。在这一步骤中，可以
调优服务器的参数
，如适当增加数据库缓冲池
5、如果是 SQL 执行时间长，进入
A3
步骤，这一步中需要考虑
索引设计
的问题?
关联的数据表过多
？
数据表的字段设计
问题导致?
6、如果
A2
和
A3
都不能解决问题，需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤
7、如果
A2
和
A3
都不能解决问题，如果已经达到了
性能瓶颈
，进入
A4
阶段，需要考虑
增加服务器
，采用
读写分离
的架构，或者考虑对数据库进行
分库分表
，如
垂直分库、垂直分表、水平分表
3、优化效率比较
Innodb_rows_read：Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
Com_update：更新操作的次数。
Com_delete：删除操作的次数。
查询 MySQL 服务器的慢查询次数
慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对查询语句进行
表结构优化
或者
查询语句优化
SHOW STATUS LIKE 'Slow_queries';
三、统计 SQL 的查询成本：last_query_cost
一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL 会计算每个执行计划所需要的成本，从中选择 成本最小 的一个作为最终执行的执行计划。如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。它通常也是我们 评价一个查询的执行效率 的一个指标。这个查询成本对应的是 SQL 语句所需要读取的页的数量
1、实战1
查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找
SELECT student_id, class_id, name, create_time
FROM student_info
WHERE id = 900001;
SELECT student_id, class_id, name, create_time
FROM student_info
WHERE id BETWEEN 900001 AND 900100;
查询优化器的成本，这时我们大概需要进行 20 个页的查询
SHOW STATUS LIKE 'last_query_cost';
通过上面实例的对比发现实战2访问数据页是实战1的20倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量(last_query_cost)增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间
4、使用场景
last_query_cost：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候
SQL 查询是一个动态的过程，从 页 加载的角度来看，可以得到以下两点结论
1、位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多
2、批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取
3、所以说，遇到 I/O 并不用担心，方法找对了，效率也是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到 缓冲池 中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升
四、慢查询日志
MySQL的慢查询日志，用来记录在MySQL中 响应时间超过阈值 的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。 long_query_time 的默认值为 10 ，意思是运行 10 秒以上的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是帮助我们发现那些执行时间特别长的 SQL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。
默认情况下，MySQL数据库 没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响
1、开启慢查询日志
1.1、查询是否开启慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
1.3、修改long_query_time阈值
设置global的方式对当前session的 long_query_time失效。对新连接的客户端有效。所以需要重新开启一个连接，或者同时修改 GLOBAL，SESSION
查看 long_query_time 值
SHOW VARIABLES LIKE '%long_query_time%';
!!! 永久设置—修改 my.cnf 文件
在 [mysqld]下增加或修改参数 long_query_time、 slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器
[mysqld]
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/raven-mysql-0-slow.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的 SQL 即被记录到慢查询日志
log_output=FILE
4.2 查看慢查询数目
查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classid` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
步骤2：设置参数 log_bin_trust_function_creators
创建函数，假如报错：
This function has none of DETERMINISTIC......
命令开启：允许创建函数设置
# 不加global只是当前窗口有效。
SET GLOBAL log_bin_trust_function_creators = 1;
步骤3：创建函数
随机产生字符串
DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n
SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $ DELIMITER ;
DELIMITER $
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
RETURN i;
END $
DELIMITER ;
步骤4：创建存储过程
DELIMITER $
CREATE PROCEDURE insert_stu1(start INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0; SET autocommit = 0; #设置手动提交事务
REPEAT
SET i = i + 1; #赋值
INSERT INTO student (stuno, name, age, classid)
VALUES ((start + i), rand_string(6), rand_num(10, 100), rand_num(10, 1000));
UNTIL i = max_num END REPEAT;
COMMIT; #提交事务
END $
DELIMITER ;
步骤5：调用存储过程
CALL insert_stu1(100001,4000000);
4.4 测试及分析
SELECT * FROM student WHERE name = 'smgWpI';
除了上述变量，控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询 扫描过的最少记录树。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。
show variables like 'min_examined_row_limit%';
show variables like 'min%';
min_examined_row_limit默认是 0。与 long_query_time=10 合在一起，表示只要查询的执行时间超过 10 秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改 my.ini 文件，来修改查询时长，或者通过 SET 指令，用 SQL 语句修改 min_examined_row_limit的值
4.5 慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
1、查看mysqldumpslow的帮助信息
mysqldumpslow --help
2、mysqldumpslow 命令的具体参数如下
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据
-g: 后边搭配一个正则匹配模式，大小写不敏感的
3、mysqldumpslow 常用命令
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/raven-mysql-0-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/raven-mysql-0-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/raven-mysql-0-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/raven-mysql-0-slow.log
4.6 关闭慢查询日志
方式1：永久性方式
[mysqld]
slow_query_log=OFF
重启MySQL服务，执行如下语句查询慢日志功能
SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长
方式2：临时性方式-使用SET语句来设置
停止MySQL慢查询日志功能
SET GLOBAL slow_query_log=off;
重启MySQL服务，使用SHOW语句查询慢查询日志功能信息
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE '%long_query_time%';
4.7 删除慢查询日志
使用 SHOW 语句显示慢查询日志信息
SHOW VARIABLES LIKE 'slow_query_log%';
五、SHOW PROFILE（查看 SQL 执行成本）
SHOW PROFILE 是MySQL提供的可以用来分析当前会话中 SQL 都做了什么，执行的资源消耗情况的工具，可用于 SQL 调优的测量
5.1、查看 PROFILE 功能是否开启
show variables like 'profiling';
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息
5.3、需要注意的指标
① converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了
② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
③ Copying to tmp table on disk：把内存中临时表复制到磁盘上，警惕！！！
查询 MySQL 服务器的慢查询次数
慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对查询语句进行
表结构优化
或者
查询语句优化
SHOW STATUS LIKE 'Slow_queries';
三、统计 SQL 的查询成本：last_query_cost
一条 SQL 查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL 会计算每个执行计划所需要的成本，从中选择 成本最小 的一个作为最终执行的执行计划。如果我们想要查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。它通常也是我们 评价一个查询的执行效率 的一个指标。这个查询成本对应的是 SQL 语句所需要读取的页的数量
1、实战1
查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找
SELECT student_id, class_id, name, create_time
FROM student_info
WHERE id = 900001;
SELECT student_id, class_id, name, create_time
FROM student_info
WHERE id BETWEEN 900001 AND 900100;
查询优化器的成本，这时我们大概需要进行 20 个页的查询
SHOW STATUS LIKE 'last_query_cost';
通过上面实例的对比发现实战2访问数据页是实战1的20倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量(last_query_cost)增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间
4、使用场景
last_query_cost：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候
SQL 查询是一个动态的过程，从 页 加载的角度来看，可以得到以下两点结论
1、位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多
2、批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的（10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取
3、所以说，遇到 I/O 并不用担心，方法找对了，效率也是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到 缓冲池 中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升
四、慢查询日志
MySQL的慢查询日志，用来记录在MySQL中 响应时间超过阈值 的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。 long_query_time 的默认值为 10 ，意思是运行 10 秒以上的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是帮助我们发现那些执行时间特别长的 SQL 查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。
默认情况下，MySQL数据库 没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响
1、开启慢查询日志
1.1、查询是否开启慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
1.3、修改long_query_time阈值
设置global的方式对当前session的 long_query_time失效。对新连接的客户端有效。所以需要重新开启一个连接，或者同时修改 GLOBAL，SESSION
查看 long_query_time 值
SHOW VARIABLES LIKE '%long_query_time%';
!!! 永久设置—修改 my.cnf 文件
在 [mysqld]下增加或修改参数 long_query_time、 slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器
[mysqld]
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/raven-mysql-0-slow.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的 SQL 即被记录到慢查询日志
log_output=FILE
4.2 查看慢查询数目
查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classid` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
步骤2：设置参数 log_bin_trust_function_creators
创建函数，假如报错：
This function has none of DETERMINISTIC......
命令开启：允许创建函数设置
# 不加global只是当前窗口有效。
SET GLOBAL log_bin_trust_function_creators = 1;
步骤3：创建函数
随机产生字符串
DELIMITER $
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n
SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $ DELIMITER ;
DELIMITER $
CREATE FUNCTION rand_num(from_num INT, to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
RETURN i;
END $
DELIMITER ;
步骤4：创建存储过程
DELIMITER $
CREATE PROCEDURE insert_stu1(start INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0; SET autocommit = 0; #设置手动提交事务
REPEAT
SET i = i + 1; #赋值
INSERT INTO student (stuno, name, age, classid)
VALUES ((start + i), rand_string(6), rand_num(10, 100), rand_num(10, 1000));
UNTIL i = max_num END REPEAT;
COMMIT; #提交事务
END $
DELIMITER ;
步骤5：调用存储过程
CALL insert_stu1(100001,4000000);
4.4 测试及分析
SELECT * FROM student WHERE name = 'smgWpI';
除了上述变量，控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询 扫描过的最少记录树。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。
show variables like 'min_examined_row_limit%';
show variables like 'min%';
min_examined_row_limit默认是 0。与 long_query_time=10 合在一起，表示只要查询的执行时间超过 10 秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改 my.ini 文件，来修改查询时长，或者通过 SET 指令，用 SQL 语句修改 min_examined_row_limit的值
4.5 慢查询日志分析工具：mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
1、查看mysqldumpslow的帮助信息
mysqldumpslow --help
2、mysqldumpslow 命令的具体参数如下
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据
-g: 后边搭配一个正则匹配模式，大小写不敏感的
3、mysqldumpslow 常用命令
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/raven-mysql-0-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/raven-mysql-0-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/raven-mysql-0-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/raven-mysql-0-slow.log
4.6 关闭慢查询日志
方式1：永久性方式
[mysqld]
slow_query_log=OFF
重启MySQL服务，执行如下语句查询慢日志功能
SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长
方式2：临时性方式-使用SET语句来设置
停止MySQL慢查询日志功能
SET GLOBAL slow_query_log=off;
重启MySQL服务，使用SHOW语句查询慢查询日志功能信息
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE '%long_query_time%';
4.7 删除慢查询日志
使用 SHOW 语句显示慢查询日志信息
SHOW VARIABLES LIKE 'slow_query_log%';
五、SHOW PROFILE（查看 SQL 执行成本）
SHOW PROFILE 是MySQL提供的可以用来分析当前会话中 SQL 都做了什么，执行的资源消耗情况的工具，可用于 SQL 调优的测量
5.1、查看 PROFILE 功能是否开启
show variables like 'profiling';
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息
5.3、需要注意的指标
① converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了
② Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表
③ Copying to tmp table on disk：把内存中临时表复制到磁盘上，警惕！！！