Mysql进阶优化篇01——四万字详解数据库性能分析工具(深入、全面、详细，收藏备用)

前 言
?? 作者简介：，长跑型选手，立志坚持写10年博客，专注于java后端
?? 专栏简介：mysql进阶，主要讲解mysql数据库进阶知识，包括索引、数据库调优、分库分表等
?? 文章简介：本文将介绍数据库优化的步骤、思路、性能分析工具，比如慢查询、EXPLAIN,SHOW PROFILING等,并且对各个工具执行性能分析结果性能参数都有详细的介绍解释、建议收藏备用。
?? 相关推荐：

• MySql进阶索引篇01——深度讲解索引的数据结构：B+树
• Mysql进阶索引篇02——InnoDB存储引擎的数据存储结构
• Mysql进阶索引篇03——2个新特性，11+7条设计原则教你创建索引
• 大厂SQL面试真题大全

目录

• 1.数据库服务器的优化步骤
• 2.查看系统性能参数
• 3.统计SQL的查询成本：last_query_cost
• 4.定位执行慢的 SQL：慢查询日志
• • 4.1 开启慢查询日志
  • • 1??开启slow_query_log
    • 2??修改long_query_time阈值
  • 4.2 案例演示
  • • 1?? 建表
    • 2?? 设置参数 log_bin_trust_function_creators
    • 3??创建函数
    • 4??创建存储过程
  • 4.3 慢查询日志分析工具：Mysqldumpslow
  • 4.4 关闭慢查询日志
  • • 1??方式1：永久性方式
    • 2??方式2：临时性方式
  • 4.5 删除与恢复慢查询日志
• 5.查看 SQL 执行成本：SHOW PROFILE
• 6.分析查询语句：EXPLAIN(重点)
• • 6.1 EXPLAIN简介
  • • 1??作用
    • 2??官网介绍
    • 3??版本说明
  • 6.2 基本语法
  • 6.3 数据准备
  • • 1??.建表
    • 2??创建存储函数
    • 3??创建存储过程
    • 4??调用存储过程
  • 6.4 EXPLAIN各列作用
  • • 1?? table
    • 2??id
    • 3??select_type
    • 4??partitions (可略)
    • 5??type ☆
    • • (1)system
      • (2)Const
      • （4）eq_ref
      • （5）ref
      • （6）ref_or_null
      • （7）index_merge
      • (8)unique_subquery
      • (9)range
      • (10)index
      • (11)ALL
    • 6??possible_keys和key
    • 7??key_len ☆
    • 8??.ref
    • 9?? rows ☆
    • 1??0??filtered
    • 1??1?? Extra ☆
• 7.EXPLAIN的进一步使用
• • 7.1、EXPLAIN四种输出格式
  • • 1??传统格式
    • 2??JSON格式
    • 3??TREE格式
    • 4??可视化输出
  • 7.2 SHOW WARNINGS的使用
• 8.分析优化器执行计划：trace
• 9.MySQL监控分析视图-sys schema
• • 9.1 Sys schema视图摘要
  • 9.2 Sys schema视图使用场景

1.数据库服务器的优化步骤

数据库的优化整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。数据库的优化可以总结为下图。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

可以从图中看到，在整个流程中需要用到很多分析工具:比如慢查询，EXPLAIN,SHOW PROFILING等，这篇文章就会介绍这些数据库性能分析工具。
简单小结如下：

可以看到数据库调优的步骤中越往金字塔尖走，其成本越高，效果越差，因此我们在数据库调优的过程中，要重点把握金字塔底部的sql及索引调优，数据库表结构调优，系统配置参数调优等软件层面的调优。

2.查看系统性能参数

可以使用SHOW STATUS语句查询一些数据库服务器的性能参数和使用频率。
其语法如下：

SHOW [GLOBAL][SESSION] STATUES LIKE '参数';

一些常用的性能参数如下：

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Slow_queries：慢查询的次数。
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的上线时间

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 9     |
+---------------+-------+
1 row in set (0.01 sec)

看看存储引擎增删改查的行数。

mysql> show status like  'innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 8     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

3.统计SQL的查询成本：last_query_cost

先来造一下数据(友情提醒：上一篇文章已经造过，如果您是从上一篇文章跟着阅读过来的，不用重新造了哟。)

CREATE DATABASE atguigudb1;

USE atguigudb1;

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 DO
	   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	   SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //


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 //

# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	SET i = i + 1;  #赋值
	INSERT INTO course (course_id, course_name ) VALUES
	(rand_num(10000,10100),rand_string(6)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //
DELIMITER ;

# 存储过程2：创建插入学生表存储过程
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	SET i = i + 1;  #赋值
	INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //

# 插入课程数据
CALL insert_course(100);
#插入学生数据
CALL insert_stu(1000000);

执行查询操作并且查看sql执行成本，Value表示I/O加载的数据页的页数。

mysql> select * from student_info where id = 900001;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 |     128284 | jbCKPX |     10080 |    10001 | 2022-05-31 11:01:54 |
+--------+------------+--------+-----------+----------+---------------------+
1 row in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

再来个大的。

mysql>  select * from student_info where id between 900001 and 900100;
+--------+------------+--------+-----------+----------+---------------------+
| id     | student_id | name   | course_id | class_id | create_time         |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 |     128284 | jbCKPX |     10080 |    10001 | 2022-05-31 11:01:54 |
// ...
| 900099 |      45120 | MZOSay |     10081 |    10026 | 2022-05-31 11:01:54 |
| 900100 |      83397 | lQyTXg |     10034 |    10058 | 2022-05-31 11:01:54 |
+--------+------------+--------+-----------+----------+---------------------+
100 rows in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 41.136003 |
+-----------------+-----------+
1 row in set (0.00 sec)

不知道大家有没有发现，上面的查询页的数量是刚才的 41 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样,查询last_query_cost对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

?? SQL查询是一个动态的过程，从页加载的角度，我们可以得到以下两点结论：
1.位置决定效率:数据库缓冲池>内存>磁盘。
2.批量决定效率：顺序读取>大于随机读取，有时候批量顺序读取多个页甚至会比随机加载一个页更快。
在实际生产中，我们可以利用这个特点，把经常用于查询的数据尽量放在缓冲池中，其次我们可以充分利用磁盘的吞吐能力，批量读取数据。

4.定位执行慢的 SQL：慢查询日志

慢查询日志用来记录相应时间超过阈值的语句，它可以帮助我们发现那些执行时间特别长的sql语句，以期进行针对性优化。一般mysql的慢查询日志默认关闭，非调优情况不建议开启，避免影响数据库的性能。

4.1 开启慢查询日志

1开启slow_query_log

查看

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name       | Value                                                |
+---------------------+------------------------------------------------------+
| slow_query_log      | OFF                                                  |
| slow_query_log_file | D:mysql-5.7.26-winx64dataDESKTOP-1PB99O1-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

修改,注意这里要加global，因为它是全局系统变量，否则会报错哟。

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.02 sec)

再查看。

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name       | Value                                                |
+---------------------+------------------------------------------------------+
| slow_query_log      | ON                                                   |
| slow_query_log_file | D:mysql-5.7.26-winx64dataDESKTOP-1PB99O1-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

2修改long_query_time阈值

查看。

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.02 sec)

修改。

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

再查看。

mysql> show global vari