慢查询日志
1.MySQL的慢查询日志是MySQL剃光的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
2.具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
3.由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
#查看慢日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /home/mysql_data/mysql/TestNode4-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.02 sec)
#开启慢日志
mysql> set global slow_query_log=1;
#另外开启一个连接，查看慢日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /home/mysql_data/mysql/TestNode4-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)
使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，
如果MySQL重启后则会失效。
如果要永久生效，就必须修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf（其它系统变量也是如此）
[mysqld]下增加或修改参数
slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。
如：
slow_query_log =1
slow_query_log_file=/home/mysql_data/mysql/TestNode4-slow.log
通过参数long_query_time控制时间阈值(默认情况下long_query_time的值为10秒)，运行时间大于该值的sql会被保存在慢日志中
#
查看long_query_time
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
#可以使用命令修改，也可以在配置文件中修改。
#使用命令
mysql> set global long_query_time=1;
#需要重新连接或新开一个会话才能看到修改值。
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
或者通过set session long_query_time=1来改变当前session变量;
mysql> set session long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
慢查询实验
#执行慢查询sql
mysql> select sleep(2);
#查看日志信息
$ cat /home/mysql_data/mysql/TestNode4-slow.log
/usr/sbin/mysqld, Version: 8.0.29 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 8.0.29 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2022-05-10T07:37:35.397145Z
# User@Host: root[root] @ localhost [] Id: 9
# Query_time: 2.000102 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1652168253;
select sleep(2);
#查看当前有几条慢查询记录
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
参数：
s: 是表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询行数
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据；
g:后边搭配一个正则匹配模式，大小写不敏感的；
常用参考
#得到返回记录集最多的10个SQL
$ mysqldumpslow -s r -t 10 /home/mysql_data/mysql/TestNode4-slow.log
#得到访问次数最多的10个SQL
$ mysqldumpslow -s c -t 10 /home/mysql_data/mysql/TestNode4-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
$ mysqldumpslow -s t -t 10 -g "left join" /home/mysql_data/mysql/TestNode4-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
$ mysqldumpslow -s r -t 10 /home/mysql_data/mysql/TestNode4-slow.log | more
批量数据脚本
往表里插入1000w数据
# 新建库
create database bigData;
use bigData;
#1 建表dept
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;
#2 建表emp
CREATE TABLE emp
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ;
记得关闭慢查询日志
mysql> set global slow_query_log=0;
#设置参数log_bin_trust_function_creators
#创建函数，假如报错：This function has none of DETERMINISTIC......
#由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.01 sec)
mysql> set global log_bin_trust_function_creators=1;
#这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
#在配置文件的[mysqld]加上log_bin_trust_function_creators=1
#添加函数
#随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN ##方法开始
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
##声明一个 字符窜长度为 100 的变量 chars_str ,默认值
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));
##concat 连接函数 ，substring(a,index,length) 从index处开始截取
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#假如要删除
#drop function rand_string;
#随机产生部门编号
#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
#假如要删除
#drop function rand_num;
#创建存储过程
#创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0 ；提高执行效率
SET autocommit = 0;
REPEAT ##重复
SET i = i + 1;
INSERT INTO emp(empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());
UNTIL i = max_num ##直到 上面也是一个循环
END REPEAT; ##满足条件后结束循环
COMMIT; ##执行完成后一起提交
END $$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
#创建往dept表中插入数据的存储过程
#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;
#调用存储过程
#dept表
DELIMITER ;
CALL insert_dept(100,10);
#执行存储过程，往emp表添加1万条数据
DELIMITER ;
CALL insert_emp(11,10000);
大量数据案例
#查询 部门编号为101的，且员工编号小于100100的用户，按用户名称排序
mysql> EXPLAIN SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 9847 | 3.33 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
#结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
#开始优化：
#思路： 尽量让where的过滤条件和排序使用上索引
#但是一共两个字段(deptno,empno)上有过滤条件，一个字段(ename)有索引
#1、我们建一个三个字段的组合索引可否？
mysql> create index idx_dno_eno_ena on emp(deptno,empno,ename);
#我们发现using filesort 依然存在，所以ename 并没有用到索引。
#所以删除索引
drop index idx_dno_eno_ena on emp;
#也就是说empno 和ename这个两个字段我只能二选其一。这样我们优化掉了 using filesort。
mysql> create index idx_dno_ena on emp(deptno,ename);
mysql> EXPLAIN SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ref | idx_dno_ena | idx_dno_ena | 3 | const | 1005 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#如果我们建立
mysql> create index idx_dno_eno on emp(deptno,empno);
mysql> EXPLAIN SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | emp | NULL | range | idx_dno_ena,idx_dno_eno | idx_dno_eno | 6 | NULL | 106 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
#出现了filesort,意味着排序用到索引
而执行sql后发现filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了近10倍。何故？
原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<100100 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
结论： 当范围条件和group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
Show Profile
show profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤：
#1.查看当前mysql是否支持(默认是关闭，使用前需要开启)
mysql> Show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
#2.开启功能，默认是关闭，使用前需要开启
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set (0.00 sec)
#3.运行sql
遇到问题：
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bigData.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#查看sql_mode
mysql> select @@global.sql_mode;
Connection id: 8
Current database: bigData
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
#删除ONLY_FULL_GROUP_BY（对已存在的数据库操作）
mysql> SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> select *,id%10 from emp group by id%10 limit 15000;
mysql> select * from emp group by id%20 order by 5;
#4.查看结果
mysql> show profiles;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------+
| 1 | 0.00452300 | show variables like 'profiling' |
| 2 | 0.00769575 | select *,id%10 from emp group by id%10 limit 15000 |
| 3 | 0.00722175 | select * from emp group by id%20 order by 5 |
+----------+------------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
#5.诊断SQL，show profile cpu,block io for query n (n为上一步前面的问题SQL数字号码);
type:
| ALL --显示所有的开销信息
| BLOCK IO --显示块IO相关开销
| CONTEXT SWITCHES --上下文切换相关开销
| CPU --显示CPU相关开销信息
| IPC --显示发送和接收相关开销信息
| MEMORY --显示内存相关开销信息
| PAGE FAULTS --显示页面错误相关开销信息
| SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS --显示交换次数相关开销的信息
mysql> show profile cpu,block io for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000052 | 0.000038 | 0.000014 | 0 | 0 |
| Executing hook on transaction | 0.000004 | 0.000002 | 0.000001 | 0 | 0 |
| starting | 0.000005 | 0.000004 | 0.000001 | 0 | 0 |
| checking permissions | 0.000004 | 0.000003 | 0.000001 | 0 | 0 |
| Opening tables | 0.000027 | 0.000020 | 0.000008 | 0 | 0 |
| init | 0.000004 | 0.000003 | 0.000001 | 0 | 0 |
| System lock | 0.000006 | 0.000004 | 0.000001 | 0 | 0 |
| optimizing | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |
| statistics | 0.000012 | 0.000009 | 0.000004 | 0 | 0 |
| preparing | 0.000008 | 0.000006 | 0.000002 | 0 | 0 |
| Creating tmp table | 0.000034 | 0.000024 | 0.000009 | 0 | 0 |
| executing | 0.007263 | 0.007193 | 0.000000 | 0 | 0 |
| end | 0.000009 | 0.000008 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000003 | 0.000000 | 0 | 0 |
| waiting for handler commit | 0.000012 | 0.000012 | 0.000000 | 0 | 0 |
| closing tables | 0.000007 | 0.000007 | 0.000000 | 0 | 0 |
| freeing items | 0.000234 | 0.000016 | 0.000000 | 0 | 0 |
| cleaning up | 0.000009 | 0.000008 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
18 rows in set, 1 warning (0.01 sec)
日常开发需要注意的绝伦
1.converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
2.Creating tmp table 创建临时表（拷贝数据到临时表；用完再删除）
3.Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
4.locked
全局查询日志
尽量不要在生产环境开启这个功能
在mysql的参数配置文件中，设置如下：
#开启
general_log=1
记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
#命令
mysql> set global general_log=1;
#全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中
set global log_output='TABLE';
#此后 ，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
mysql> select * from mysql.general_log;
1579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
