第３章，查询截取分析

最新推荐文章于 2024-03-16 22:16:30 发布

asertgh123

最新推荐文章于 2024-03-16 22:16:30 发布

阅读量213

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/asertgh123/article/details/110790539

版权

第３章，查询截取分析

3.1, 查询优化

3.1.1,MySQL 优化原则

mysql 的调优大纲

观察，至少跑一天，看看生产的慢SQL情况
慢查询的开启并捕获，设置阈值，比如超过５秒钟的就是慢SQL，并将它抓取出来。
explain+慢SQL分析
show　profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
运维经理或DBA,进行SQL数据库服务器的参数调优

3.1.2 永远小表驱动大表，类似嵌套循环 Nested Loop

1, EXISTS 语法：

SELECT … FROM table WHERE EXISTS(subquery)
该语法可以理解为：将查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
EXISTS(subquery) 只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

结论：

永远记住小表驱动大表
当 B 表数据集小于 A 表数据集时，使用 in
当 A 表数据集小于 B 表数据集时，使用 exist

3.1.3 ORDER BY 优化

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

结论：

MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低。
ORDER BY满足两情况（最佳左前缀原则），会使用Index方式排序，ORDER BY语句使用索引最左前列，使用where子句与OrderBy子句条件列组合满足索引最左前列
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果未在索引列上完成排序，mysql 会启动 filesort 的两种算法：双路排序和单路排序
１，双路排序
　　MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和将要进行order　by操作的列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
　　从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
２，单路排序
　　取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了改进的算法，就是单路排序。
　　从磁盘读取查询需要的所有列，按照将要进行orderby的列，在sort buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
　　在这里插入图片描述

３，结论及引申出的问题：

由于单路是改进的算法，总体而言好过双路
在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而会导致多次I/O。
结论：本来想省一次I/O操作，反而导致了大量的/O操作，反而得不偿失。
更深层次的优化策略：
　　增大sort_buffer_size参数的设置
　　增大max_length_for_sort_data参数的设置

４，遵循如下规则，可提高Order By的速度

Order by时select *是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
　　当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
　　两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的.
尝试提高max_length_for_sort_data提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

在这里插入图片描述

3.1.4 GROUP BY 优化

ONLY_FULL_GROUP_BY SQL模式
 关于group by 报错的原因
group by关键字优化

group by实质是先排序后进行分组，遵照索引的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了其余的规则均和 order by 一致

3.2 慢查询日志

3.2.1 慢查询日志介绍

慢查询日志是什么？

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

3.2.2 慢查询日志开启

说明：

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

查看是否开启及如何开启
1,查看慢查询日志是否开启：

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
可以通过设置slow_query_log的值来开启
通过SHOW VARIABLES LIKE ‘%slow_query_log%’;查看 mysql 的慢查询日志是否开启

如何开启开启慢查询日志：

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

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数：slow_query_log和slow_query_log_file后，然后重启MySQL服务器。
也即将如下两行配置进my.cnf文件

[mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log

那么开启慢查询日志后，什么样的SQL参会记录到慢查询里面？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE ‘long_query_time%’;查看慢 SQL 的阈值。
可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

3.2.3 慢查询日志示例

set global long_query_time=3;
为什么设置后阈值时间没变？
　　需要重新连接或者新开一个回话才能看到修改值。
　　查看全局的 long_query_time 值：show global variables like ‘long_query_time’;发现已经生效
记录慢 SQL 以供后续分析
　　怼个 select sleep(4); 超过 3s ，肯定会被记录到日志中
查询当前系统中有多少条慢查询记录：show global status like ‘%Slow_queries%’;
配置版的慢查询日志

在 /etc/my.cnf 文件的 [mysqld] 节点下配置

slow_query_log=1；
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
long_query_time=3；
log_output=FILE

3.2.4 日志分析命令 mysqldumpslow

mysqldumpslow是什么？

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看 mysqldumpslow的帮助信息: mysqldumpslow --help
mysqldumpshow 参数解释

		 s：是表示按何种方式排序
		 c：访问次数
		 l：锁定时间
		r：返回记录
		t：查询时间
		al：平均锁定时间
		ar：平均返回记录数
		at：平均查询时间
		t：即为返回前面多少条的数据
		g：后边搭配一个正则匹配模式，大小写不敏感的

常用参数手册

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

mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log1

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

mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log1

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

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/Heygo-slow.log1

另外建议在使用这些命令时结合 | 和more使用，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more

3.3 批量数据脚本

3.3.1 设置参数

创建函数，假如报错：This function has none of DETERMINISTIC………
由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。
log_bin_trust_function_creators = OFF ，默认必须为 function 传递一个参数
通过 set global log_bin_trust_function_creators=1;我们可以不用为 function 传参
这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法在配置文件中修改‘

	windows下：my.ini --> [mysqld] 节点下加上 log_bin_trust_function_creators=1
	linux下：/etc/my.cnf --> [mysqld] 节点下加上 log_bin_trust_function_creators=1

3.3.2 创建函数

随机产生字符串的函数

# 两个 $$ 表示结束,这里是设置$$为结束标志，不再是；
delimiter $$　                                          
　create function rand_string(n int) returns varchar(255)
　begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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 $$

3.3.3 创建存储过程

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

delimiter $$
create procedure insert_emp(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 emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
        until i=max_num
        end repeat;
    commit;
end $$

3.3.4 调用存储过程

向 dept 表中插入 10 条记录

DELIMITER ;
CALL insert_dept(100, 10);

3.4 Show Profile

3.4.1 是什么

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果

3.4.2 分析步骤

查看是当前的SQL版本是否支持Show Profile
show variables like ‘profiling’; 查看 Show Profile 是否开启
开启功能 Show Profile ，默认是关闭，使用前需要开启 set profiling=on; 开启 Show Profile
运行SQL,运行正常SQL和慢SQL
查看结果，通过 show profiles; 指令查看结果
诊断SQL　　show profile cpu, block io for query SQL编号; 查看 SQL 语句执行的具体流程以及每个步骤花费的时间

参数备注：
show profile cpu, block io for query SQL编号;，这里还可以查询别的，如下

ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销
CONTEXT SWITCHES：上下文切换相关开销
CPU：显示CPU相关开销信息
IPC：显示发送和接收相关开销信息
MEMORY：显示内存相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS：显示交换次数相关开销的信息

日常开发需要注意的结论，出现这些意味着有问题

converting HEAP to MyISAM：查询结果太大，内存都不够用了，开始往磁盘上搬了。
Creating tmp table：创建临时表，mysql 先将拷贝数据到临时表，然后用完再将临时表删除
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
locked：锁表

3.5 全局查询日志

永远不要在生产环境开启这个功能。

3.5.1 配置启用全局查询日志

在mysql的my.cnf中，设置如下：
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

3.5.2 编码启用全局查询日志

执行如下指令开启全局查询日志

  set global general_log=1;
  set global log_output='TABLE';

此后，你所执行的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

asertgh123

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫