前 言
?? 作者简介:,长跑型选手,立志坚持写10年博客,专注于java后端
?? 专栏简介:mysql进阶,主要讲解mysql数据库进阶知识,包括索引、数据库调优、分库分表等
?? 文章简介:本文将介绍数据库优化的步骤、思路、性能分析工具,比如慢查询、EXPLAIN
,SHOW PROFILING
等,并且对各个工具执行性能分析结果性能参数都有详细的介绍解释、建议收藏备用。
?? 相关推荐:
目录
- 1.数据库服务器的优化步骤
- 2.查看系统性能参数
- 3.统计SQL的查询成本:last_query_cost
- 4.定位执行慢的 SQL:慢查询日志
- 5.查看 SQL 执行成本:SHOW PROFILE
- 6.分析查询语句:EXPLAIN(重点)
- 7.EXPLAIN的进一步使用
- 8.分析优化器执行计划:trace
- 9.MySQL监控分析视图-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 variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-------------