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是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;
