EXPLAIN + SQL 语句 查看慢SQL；show profile进行进一步分析；最外层最好循环次数要少，否则会消耗很长时间。应当使用小的结果集驱动大的结果集。第一种情况
select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.idin 优于 exists第二种情况
select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.idexists 优于 in。exists讲解
exists 语法 select... from table where exists (subquery)
exists(subquery)只返回TRUE、FALSE，因此子查询中的条件字段可以是 SELECT 1 或 SELECT X，官方的解释是在实际执行时会忽略 SELECT清单，因此没有区别；exists子查询的实际执行过程可能经过了优化而不是逐条对比；exists子查询也可以使用条件表达式、其他子查询或者 JOIN来替代，需要根据具体问题具体分析；1. 怎样会产生Using FileSort
未按照前面的索引顺序进行排序或者把前面的几个索引不用上直接使用了后面的索引都会导致Using FileSort。2. ORDER BY简介
FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。避免使用FileSort方式排序。WHERE子句与ORDER BY子句条件列组合满足索引最左前列法则。3. Case
- 第一条
mysql> explain select * from tblA where age > 20 order by age;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tblA | NULL | index | idx_ageBirth | idx_ageBirth | 9 | NULL | 3 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
- 第二条
mysql> explain select * from tblA where birth > '2016-01-28 00:00:00' order by age;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tblA | NULL | index | NULL | idx_ageBirth | 9 | NULL | 3 | 33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
-第三条
mysql> explain select * from tblA where age > 20 order by age,birth;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tblA | NULL | index | idx_ageBirth | idx_ageBirth | 9 | NULL | 3 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)-第一条
mysql> explain select * from tblA where age > 20 order by birth;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | tblA | NULL | index | idx_ageBirth | idx_ageBirth | 9 | NULL | 3 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
-第二条
mysql> explain select * from tblA order by birth;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | tblA | NULL | index | NULL | idx_ageBirth | 9 | NULL | 3 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
-第三条
mysql> explain select * from tblA where birth > '2016-01-28 00:00:00' order by birth;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | tblA | NULL | index | NULL | idx_ageBirth | 9 | NULL | 3 | 33.33 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
-第四条
mysql> explain select * from tblA where age > 20 order by birth,age;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
| 1 | SIMPLE | tblA | NULL | index | idx_ageBirth | idx_ageBirth | 9 | NULL | 3 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from tblA order by age ASC, birth DESC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | tblA | NULL | index | NULL | idx_ageBirth | 9 | NULL | 3 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)4. 怎样避免出现Using FileSort
遵照索引建的最佳左前缀。5. FileSort的两种算法
两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。从磁盘读取查询所需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机I\O变成了顺序I\O，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
6. 单路排序出现的问题
在sort_buffer中，单路排序比双路排序要占用很多空间，因为单路排序是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取出sort_buffer容量大小，再排…从而多次I\O。本来想省一次I\O操作，反而导致了大量的I\O操作，反而得不偿失。
7.小结
举例说明
KEY a_b_c(a,b,c)
-- order by 能使用索引最左前缀
ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC
-- 如果where使用索引的最左前缀定义为常量，则order by 能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const order by b,c
where a = const and b > const order by b,c
-- 不能使用索引进行排序
order by a ASC,b DESC,c DESC -- 排序不一致
where g = const order by b,c -- 丢失a索引
where a = const order by c -- 丢失b索引
where a = const order by a,d -- d不是索引的一部分
where a in (...) order by b,c -- 对于排序来说，多个相等的条件也是范围查询。遵照索引建的最佳左前缀法则。增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。能写在where限定的条件就不要去having限定了。1.说明
2. 查看是否开启及如何开启
--- 查看
show variables like '%slow_query_log%';
-- 查看结果
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | D:\MySQL\mysql-5.7.19\data\LAPTOP-T4PHKJJQ-slow.log |
+---------------------+-----------------------------------------------------+
2 rows in set, 1 warning (0.00 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 | D:\MySQL\mysql-5.7.19\data\LAPTOP-T4PHKJJQ-slow.log |
+---------------------+-----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)my.cnf（其它系统变量也是如此）。host_name-slow.log（如果没有指定参数slow_query_log_file的话）。3. 开启了慢查询日志之后，什么样的SQL才会记录到慢查询日志里面?
-- 这个是由long_query_time控制，默认情况下是10秒
show variables like '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.00 sec)
-- 可以使用命令修改，也可以在my.cnf参数里面修改。4. 设置慢的阀值
-- 设置阀值为3
set global long_query_time = 3;5. 设置后看不出变化，应该怎么办？
-- 1. 需要重新连接或新开一个会话再使用相关命令才能看到修改值。
mysql> show variables like 'long_query_time%';
-- 2.
mysql> show global variables like 'long_query_time%';
-- 使用第二种方法的查询结果
mysql> show global variables like 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)6. 查询当前系统中有多少条慢查询记录
-- 查询命令
show global status like '%slow_queries%';
-- 查询结果
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)7. 通过配置文件实现永久修改
my.cnf 文件中找到下面的位置复制以下代码，根据自己的实际情况配置参数和日志输出位置。
[mysqld]
slow_query_log=1 -- 开启/关闭慢查询日志
slow_query_log_file=/var/lib/mysql/xxx-slow.log -- 慢查询日志文件输出位置和名称
long_query_time=3 -- 时间阀值
log_output=FILE -- 慢查询日志输出格式mysqldumpslow。1. 查看mysqldumpslow的帮助信息
| 参数 | 含义 |
|---|---|
| s | 表示按照何种方式进行排序 |
| c | 访问次数 |
| l | 锁定时间 |
| r | 返回记录 |
| t | 查询时间 |
| al | 平均锁定时间 |
| ar | 平均返回记录数 |
| at | 平均查询时间 |
| t | 返回前面多少条数据 |
| g | 后面搭配一个正则匹配模式，大小写不敏感 |
2. 工作常用参考
--获取返回记录集中最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/test-slow.log
-- 获取访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/test-slow.log
--获取按照时间排序的前 10 条包含左连接的查询语句
mysqldumpslow -s t 10 -g "left join" /var/lib/mysql/test-slow.log
-- 建议在执行以上命令时结合 | more 使用，防止打印的数据过长
mysqldumpslow -s r -t 10 /var/lib/test-slow.log | more创建dept和emp表
CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
ceo INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 1.1创建员工表
CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`empno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;-- 查看参数是否开启
show variables like 'log_bin_trust_function_creators';
-- 查看结果
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)
-- 打开参数
set global log_bin_trust_function_creators = 1
-- 打开后结果
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)log_bin_trust_function_creators的设置为 OFF，则在创建存储函数时会报错 ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)1. 创建随机产生字符串函数
DELIMITER $$ -- 修改结束字符，本来是;这样修改后结束字符就变为了$$
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 $$2. 创建随机产生编号函数
DELIMITER $$
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$$3. 删除函数
# 删除随机产生字符串的函数
drop function rand_string
# 删除随机产生编号的函数
drop function rand_num;1. 创建想emp表中插入数据的存储过程
DELIMITER $$
-- 存储过程没有返回值
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit 设置成0
-- 如果不设置为0，它每生成一条数据就会自动提交一次，所以不好，我们设置为统一提交
SET autocommit = 0;
REPEAT -- 循环
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) ,rand_num(30,50),rand_num(1,10000));
-- 上面的insert语句调用了字符串函数和随机编号产生函数
UNTIL i = max_num
END REPEAT; -- 循环结束
COMMIT; -- 统一提交
END$$2. 创建想dept表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$1. 调用存储过程向dept表中插入100条数据
DELIMITER ;
CALL insert_dept(100); -- 调用存储过程2. 调用存储过程向emp表中插入50w条数据
DELIMITER ; -- 重新声明结束符
CALL insert_emp(100000,500000);1. 是否支持，看看当前的mysql版本是否支持。
show variables like '%profiling%'
# 查看结果
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set, 1 warning (0.01 sec)2. 开启功能
set profiling = 1
#默认是关闭，使用前需要开启。
#开启后查看结果
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set, 1 warning (0.00 sec)3. 运行SQL
4. 查看结果
show profile；
mysql> show profiles;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------+
| 1 | 0.00040950 | show variables like '%profiling%' |
| 2 | 0.00006050 | show profies |
| 3 | 0.00008050 | select * from tbl_emp |
| 4 | 0.12290000 | select * from emp group by id limit 150000 |
| 5 | 1.03373300 | select * from emp left join dept on emp.deptid = dept.id |
+----------+------------+----------------------------------------------------------+
5 rows in set (0.00 sec)5. 诊断SQL
show profile cpu,block io for query 上一步前面的问题SQL数字号码;
# 显示相关SQL语句的具体信息
# 示例
mysql> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000068 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000003 | 0.000000 | 0.000000 | NULL | NULL |
| Opening tables | 0.001718 | 0.000000 | 0.000000 | NULL | NULL |
| init | 0.000023 | 0.000000 | 0.000000 | NULL | NULL |
| System lock | 0.000007 | 0.000000 | 0.000000 | NULL | NULL |
| optimizing | 0.000006 | 0.000000 | 0.000000 | NULL | NULL |
| statistics | 0.000019 | 0.000000 | 0.000000 | NULL | NULL |
| preparing | 0.036385 | 0.000000 | 0.015625 | NULL | NULL |
| executing | 0.000012 | 0.000000 | 0.000000 | NULL | NULL |
| Sending data | 0.000144 | 0.000000 | 0.000000 | NULL | NULL |
| end | 0.000005 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000016 | 0.000000 | 0.000000 | NULL | NULL |
| closing tables | 0.000011 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000090 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000017 | 0.000000 | 0.000000 | NULL | NULL |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set, 1 warning (0.00 sec)status参数信息
| 参数 | 含义 |
|---|---|
| ALL | 显示所有的开销信息 |
| BLOCK IO | 显示块 IO 相关开销信息 |
| CONTEXT SWITCHES | 显示上下文切换相关开销信息 |
| CPU | 显示 CPU 相关开销信息 |
| IPC | 显示发送和接收相关开销信息 |
| MEMORY | 显示内存相关开销信息 |
| PAGE FAULTS | 显示页面错误相关开销信息 |
| SOURCE | 显示和 Source_function，Source_file，Source_file 相关开销信息 |
| SWAPS | 显示交换次数相关开销信息 |
6. 日常开发需要注意的结论
在my.cnf文件中设置如下
[mysqld]
# 开启
general_log=1
# 记录日志文件路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE1. 开启全局慢查询
set global_log=1;
set global_log_output='table';2. 查看所有的查询记录
general_log 表select * from mysql.general_log查看不要在生产环境下开启全局查询日志。
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/weixin_56727438/article/details/121723763
内容来源于网络，如有侵权，请联系作者删除！