文章目录
1. 数据库服务器的优化步骤
整个流程划分成了观察(Show status)
和行动(Action)
两个部分。字母 S 的部分代表观察(会使用相应的分析工具),字母 A 代表的部分是行动(对应分析可以采取的行动)。
2. 查看系统性能参数
在MySQL中,可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。
SHOW STATUS语句语法如下:
SHOW [GLOBAL|SESSION] STATUS 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服务器的慢查询次数,则可以执行如下语句:
SHOW STATUS LIKE 'Slow_queries';
3. 统计SQL的查询成本: last_query_cost
一条SQL查询语句在执行前需要查询执行计划,如果存在多种执行计划的话,MySQL会计算每个执行计划所需要的成本,从中选择成本最小的一个作为最终执行的执行计划。
如果我们想要查看某条SQL语句的查询成本,可以在执行完这条SQL语句之后,通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL 语句所需要读取的读页的数量。
SELECT student_id, class_id, NAME, create_time
FROM student_info WHERE id = 900001;
然后再看下查询优化器的成本,实际上我们只需要检索一个页即可:
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
SELECT student_id, class_id, NAME, create_time
FROM student_info WHERE id BETWEEN 900001 AND 900100;
然后再看下查询优化器的成本,这时我们大概需要进行 20 个页的查询:
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.120816 |
+-----------------+-----------+
1 row in set (0.00 sec)
你能看到页的数量是刚才的 20 倍,但是查询的效率并没有明显的变化,实际上这两个 SQL 查询的时间 基本上一样,就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中,然后再进行查找。虽然页数量(last_query_cost)增加了不少 ,但是通过缓冲池的机制,并没有增加多少查询时间 。
SQL查询时一个动态的过程,从页加载的角度来看,我们可以得到以下两点结论:
位置决定效率
。如果页就在数据库缓冲池
中,那么效率是最高的,否则还需要从 内存 或者 磁盘 中进行读取,当然针对单个页的读取来说,如果页存在于内存中,会比在磁盘中读取效率高很多。批量决定效率
。如果我们从磁盘中对单一页进行随机读,那么效率是很低的(差不多10ms),而采用顺序读取的方式,批量对页进行读取,平均一页的读取效率就会提升很多,甚至要快于单个页面在内存中的随机读取。
4. 定位执行慢的 SQL:慢查询日志
1. 开启慢查询日志参数
# OFF是关闭
mysql > show variables like '%slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.05 sec)
# slow_query_log_file是慢查询日志保存路径
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | D:\Mysql\mysql-8.0.23-winx64\Data\DESKTOP-O165SR5-slow.log |
+---------------------+------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
2. 修改long_query_time阈值
原理阈值为10,也就是查询时间为10s才算慢查询
mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
#测试发现:设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';
mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';
永久修改
修改
my.cnf 文件
或my.ini
,[mysqld] 下增加或修改参数 long_query_time、slow_query_log 和 slow_query_log_file 后,然后重启 MySQL 服务器。
[mysqld]
slow_query_log=ON # 开启慢查询日志开关
slow_query_log_file=D:\Mysql\mysql-8.0.23-winx64\Data\DESKTOP-O165SR5-slow.log # 慢查询日志的目录和文件名信息
long_query_time=3 # 设置慢查询的阈值为3秒,超出此设定值的SQL即被记录到慢查询日志
log_output=FILE
3. 查看慢查询数目
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
4. 关闭MySQL服务器停止慢查询日志功能有两种方法:
[mysqld]
slow_query_log=OFF
# 临时性
SET GLOBAL slow_query_log=off;
5. 删除慢查询日志
找到相关文件去删除
# 或者重置为null
mysqladmin -uroot -p flush-logs slow
5. 数据实验
准备数据
CREATE TABLE `student` (
`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`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 存储过程
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 ;
#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);
# 在数据量为4百万情况下,没索引查询时间都超过了慢查询设置阈值,所以下面sql是慢查询sql
SELECT * FROM student WHERE stuno = 3455655;
SELECT * FROM student WHERE name = 'oQmLUr';
慢查询日志分析工具:mysqldumpslow(定位哪些是慢sql)
分析工具是帮助定位慢查询sql的,查看mysqldumpslow的帮助信息:
mysqldumpslow --help
要先下载一个perl编译器
D:\Mysql\mysql-8.0.23-winx64\bin>perl mysqldumpslow.pl --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
mysqldumpslow 命令的具体参数如下:
- -a:不将数字抽象成N,字符串抽象成S
- -s:是表示按照何种方式排序
c:访问次数
l:锁定时间
r::返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 (默认方式)
ac:平均查询次数 - -t:即为返回前面多少条的数据
- -g:后边搭配一个正则匹配模式,大小写不敏感的
举例:我们想要按照查询时间排序,查看前五条 SQL 语句,这样写即可:
# windows下的 -s t表示按照查询时间排序,-t 5表示查看前5条sql语句
perl mysqldumpslow.pl -s t -t 5 D:\Mysql\mysql-8.0.23-winx64\Data\DESKTOP-O165SR5-slow.log
工作常用参考
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
6. 查看 慢执行SQL 执行成本:SHOW PROFILE(定位sql查询慢原因)
定位到相关慢查询sql后再进一步分析
# 查看是否开启
show variables like 'profiling';
# 然后执行相关的查询语句。接着看下当前会话都有哪些 profiles,使用下面这条命令:
show profiles;
# 查询id为61的sql执行成本
show profile cpu,block io for query 61
show profile的常用查询参数:
① ALL:显示所有的开销信息。
②BLOCK IO:显示块IO开销。
③CONTEXT SWITCHES:上下文切换开销。
④CPU:显示CPU开销信息。
⑤IPC:显示发送和接收开销信息。
⑥MEMORY:显示内存开销信 息。
⑦PAGE FAULTS:显示页面错误开销信息。
⑧SOURCE:显示和Source_function,Source_file, Source_line相关的开销信息。
⑨SWAPS:显示交换次数开销信息。
这里显示执行过程比较慢