MySQL高级篇笔记（三）查询截取分析

最新推荐文章于 2024-02-05 17:38:44 发布

好运的云

最新推荐文章于 2024-02-05 17:38:44 发布

阅读量311

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/HeyYun/article/details/108294140

版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文深入探讨SQL查询优化技巧，包括小表驱动大表、orderby与groupby优化，以及慢查询日志分析。介绍了使用mysqldumpslow工具解析慢查询，批量数据脚本的创建，ShowProfile资源消耗分析，和全局查询日志的配置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

三、查询截取分析

1、查询优化

（1）小表驱动大表

即小的数据集驱动大的数据集

select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id=B.id

用in 还是 exists ？

当B的数据集必须小于A表的数据集时，用in优于exists

select * from A where exists (select 1 from B where B.id=A.id)
等价于：
for select id from B
for select * from A where A.id=B.id

EXISTS

SELECT … FROM table WHERE EXISTS (subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE OR FALSE）来决定主查询的数据结果是否得以保留

提示

EXSITS(subquery)只返回true或者false，因此子查询中的select *也可以是select 1或者select，官方说法是实际执行时会忽略select清单，因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们所理解的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题
EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

（2）order by关键字优化

1）ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

建立索引

create index idx_A_ageBirth on tblA(age,birth);

进行分析

order by birth，缺失age，索引失效，产生了filesort
同（1）
order by age，age排在了前面，没有违背最左前缀原则，所以没有filesort
一升一降导致之前建立索引时的排序无效，只能使用了mysql内排序了，所以产生了filesort

小总结

结论

以下两种情况会使用Index方式排序：

order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列

2）尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

3）如果不在索引列上，filesort有两种算法：双路排序和单路排序

双路排序：

MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据

读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段

问题是：取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。
单路排序：

从磁盘读取查询需要的所有列，按照orderby列在buffer区对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了
结论：
- 由于单路是后出来的，总体而言好过双路
- 但是用单路有问题：

（4）优化策略

增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

原因分析：

（3）group by关键字优化

1）group by实质是先排序后进行分组，遵循索引建的最佳左前缀

2）当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

3）where高于having，能写在where限定的条件就不要去having限定了

去重优化

尽量不要使用 distinct 关键字去重

t_mall_sku 表
  	id    shp_id      kcdz                
-------  ------- --------------------
     3       1    北京市昌平区  
     4       1    北京市昌平区  
     5       5    北京市昌平区  
     6       3       重庆              
     8       8     天津

例子：

select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 将产生重复数据，
select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 使用 distinct 关键字去重消耗性能
优化：

select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) group by kcdz 能够利用到索引

2、慢查询日志

（1）慢查询日志是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值是10，意思是运行10秒以上的语句。
由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的Explain进行分析。

（2）慢查询日志的用法

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要，一般不建议开启该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。

1）查看是否开启以及如何开启

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启

开启慢查询日志方法一

开启慢查询日志

使用SET GLOBAL slow_query_log=1;开启了慢查询日志只对当前数据库生效，如果重启MySQL后则会失效（或者使用

SET GLOBAL slow_query_log=0;关闭）

开启慢查询日志方法二

2）开启了慢查询日志后，怎样的SQL才会记录到慢查询日志里面呢

这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒

可以使用命令修改该参数，也可以在my.cnf里修改

加入时间正好等于long_query_time的情况，是不会被记录，也就是说在mysql源码里是判断大于long_query_time，而非大于等于

3）案例

①查看当前多少秒算慢

②设置慢的时间阈值

③为什么设置后看不出变化

需要重新连接或重开一个会话才能看到修改值

或者不用重开或连接，直接用命令：

SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

④记录慢SQL并后续分析

⑤查看当前系统有多少条慢查询记录

4）配置版

在【mysqld】下配置

slow_query_log=1;
slow_query_log_file=/....(地址)
long_query_time=3;
log_output=FILE

这样就会永久生效

3、日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

（1）查看mysqldumpslow的帮助信息

s：表示按照何种方式排序；

c：访问次数；

I：锁定时间

r：返回记录

t：查询时间

al：平均锁定时间

ar：平均返回记录数

at：平均查询时间

t：返回前面有多少条数据

g：后边搭配一个正则匹配模式，大小写不敏感

（2）工作常用参考

得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 /DESKTOP-O360IRK-slow.log

得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /DESKTOP-O360IRK-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：

mysqldumpslow -s t -t 10 -g "LEFT JOIN" /DESKTOP-O360IRK-slow.log

另外建议在使用这些属性的时候结合 | 和more使用，否则可能出现爆屏的情况：

mysqldumpslow -s r -t 10 /DESKTOP-O360IRK-slow.log | more

4、批量数据脚本

（1）创建表

# 新建库 
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 ;

设置参数log_bin_trust_function_creators

由于开启过慢查询日志，因为我们开启了 bin-log，我们就必须为我们的function指定一个参数，否则会报错：This function has none of DETERMINISTIC…

我们可以通过设置参数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

mysql> set global log_bin_trust_function_creators=1; 
Query OK, 0 rows affected

mysql> show variables like 'log_bin_trust_function_creators'; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set

# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法： 
windows下 修改my.ini，在[mysqld]中加上log_bin_trust_function_creators=1   
linux下 修改/etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

（2）创建函数

创建函数保证每条数据都不同（可用于压力测试）

随机产生字符串

// 这里的两个$$对应下面的end$$，可以是两个四个八个，也可以是其他的符号，表示函数的结束
// 因为mysql默认的结尾是；但现在需要把多行写在一块，所有我们得改一下结尾，不再用；结束
// 用两个$才表示这段程序结束
// 也就是每次执行sql语句都需要通过$$才会结束
DELIMITER $$ 
//减建立函数    使我们自定义的函数名字（rand_string）   RETURNS是返回值
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 ;

（3）创建存储过程

tip: 函数和存储过程的区别，函数有返回值，存储过程没有返回值

创建往emp表中插入数据的存储过程

DELIMITER $$ 

// 创建存储过程，第一个参数表示从多少id开始，第二个参数表示插入多少条数据
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))   
BEGIN   
DECLARE i INT DEFAULT 0;    
#set autocommit =0 把autocommit设置成0  ；提高执行效率 ，例如，执行五十次insert，提交一次
 SET autocommit = 0;     
 REPEAT  ##重复 ，简而言之就是循环 相当于while true
 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   ##直到  上面定义的max_num也是一个循环 
 END REPEAT;  ##满足条件后结束循环 
 COMMIT;   ##执行完成后一起提交 
 END $$ 
 
#删除 
# DELIMITER ; 
# drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据 

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;

（4）调用存储过程

对于dept表

# 首先将结尾符号修改回；
DELIMITER ; 

# 进行调用
CALL insert_dept(100,10);

对于emp表

#执行存储过程，往emp表添加50万条数据

DELIMITER ; #将结束标志换回;
CALL insert_emp(100001,500000);


# 演示
mysql> DELIMITER ;
mysql> CALL insert_emp(100001,500000);
Query OK, 0 rows affected

5、Show Profile

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量

官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

（1）分析步骤

1）查看当前的mysql版本是否支持Show Profile

show variables like 'profiling';

# 默认是关闭，使用前需要开启

2）开启

set profiling=1;
或者
set profiling=on;

3）运行SQL

select * from emp group by id%20 order by 5;
select * from emp group by id%10 limit 150000;

4）查看结果

show profiles;

（2）参数说明

Duratioin：持续的时间
Query：具体的操作

诊断SQL

show profile cpu,block io for query [n]

# [n]为Query_ID

左边的status是sql语句的完整生命：连接、初始化、打开表、查找、缓存等

（3）日常开发

如果status出现下面的就危险了

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table 创建临时表

如果你的SQL语句中有创建临时表的操作，数据量很大的话，创建临时表可能会使用较长的时间！

在这里插入图片描述

如上图，从创建临时表再到把临时表复制到磁盘中，占用了两秒多的时间

6、全局查询日志

（1）配置启用

在mysql的my.cnf中，设置如下：

#开启 
general_log=1    
# 记录日志文件的路径 
general_log_file=/path/logfile 
#输出格式 
log_output=FILE

（2）编码启用

命令

set global general_log=1;

全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中的命令如下：

set global log_output='TABLE';

此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;

（3）永远不要在生产环境开启此功能

p table 创建临时表

如果你的SQL语句中有创建临时表的操作，数据量很大的话，创建临时表可能会使用较长的时间！

[外链图片转存中…(img-9mXPVcgK-1598681794622)]

如上图，从创建临时表再到把临时表复制到磁盘中，占用了两秒多的时间

6、全局查询日志

（1）配置启用

在mysql的my.cnf中，设置如下：

#开启 
general_log=1    
# 记录日志文件的路径 
general_log_file=/path/logfile 
#输出格式 
log_output=FILE

（2）编码启用

命令

set global general_log=1;

全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中的命令如下：

set global log_output='TABLE';

此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;

MySQL高级篇笔记（三）查询截取分析

文章目录

三、查询截取分析

1、查询优化

（1）小表驱动大表

（2）order by关键字优化

（3）group by关键字优化

2、慢查询日志

（1）慢查询日志是什么？

（2）慢查询日志的用法

3、日志分析工具mysqldumpslow

（1）查看mysqldumpslow的帮助信息

（2）工作常用参考

4、批量数据脚本

（1）创建表

（2）创建函数

（3）创建存储过程

（4）调用存储过程

5、Show Profile

（1）分析步骤

（2）参数说明

（3）日常开发

6、全局查询日志

（1）配置启用

（2）编码启用

（3）永远不要在生产环境开启此功能

6、全局查询日志

（1）配置启用

（2）编码启用

（3）永远不要在生产环境开启此功能