【尚硅谷mysql笔记】3.查询截取分析

标签: 数据库  mysql  数据库

基于MySQL5.5


SQL优化

SQL优化步骤
  • 上线后观察SQL,等待SQL跑一天,查看生产上慢SQL的执行情况
  • 开启慢查询日志,设置阈值,比如超过5秒的就是慢SQL,将其抓取出来
  • explain+慢SQL进行分析
  • show profile查询SQL在mysql服务器里面的执行细节和声明周期
  • 确定是否需要调整SQL数据库参数,找DBA讨论
优化
小表驱动大表:
  • 即小的数据集驱动大的数据集,关联查询时,数据量小的表先查询
    • 关键字in,先执行in中的子查询,所以当in中的数据量较小时效率较高
    • 关键字exists,先执行外面的SQL,所以当主表数据量较少时,使用exists更好
  • EXISTS:
    • SELECT … FROM table WHERE EXISTS(subquery),该语法可以理解为,将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否保留
    • EXISTS(subquery)只返回TRUE或FALSE,因此子查询中的SELECT*也可以是SELECT 1或SELECT ‘x’,实际执行时会忽略SELECT清单,因此没有区别
    • EXISTS子查询的实际执行过程可能经过了优化,而不是我们理解的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题
    • EXISTS子查询往往也可以用条件表达式,其他子查询或者JOIN来替代,何种最优需要具体问题具体分析
order by关键字优化
  • MySQL支持二种方式的排序,FileSort和Index相比,Index效率高,它指MySQL扫描索引本身完成排序,FileSort方式效率较低;尽可能在索引列上完成排序操作,遵照索引建的最佳做前列,否则mysql将使用filesort
  • Order By满足两种情况,会使用Index方式排序:
    • ORDER BY语句使用索引最左前列
    • 使用Where子句与Order By子句条件列组合满足最左前列
  • FileSort有两中算法:
    • 双路排序
      • MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中取值;
      • 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段
    • 单路排序
      • 从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据;并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。
      • 问题:在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp,多路合并),排完后再取sort_buffer容量大小,再排,从而多次IO;本来想着节省IO次数,结果导致了大量的IO操作,反而得不偿失。
  • 提高order by的速度,优化策略:
    • 增大sort_buffer_size参数的设置:不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进行的
    • 增大max_length_for_sort_data参数的设置:提高这个参数,会增加用改进算法的频率,但是如果设得太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率
    • Order by时select *是一个大忌,只Query需要的字段,这点非常重要
      • 当Query的字段大小总和小于max_length_for_sort_data而排序字段不是TEXT|BLOB类型时,会用改进后的算法-单路排序,否则用老算法-多路排序
      • 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size
group by关键字优化
  • group by实质是先排序后进行分组,遵照索引建立的最佳左前缀进行
  • 当无法使用索引列时,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
  • where高于having,能写在where限定的条件就不要去having限定了

慢查询日志

  • MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中
  • long_query_time的默认值为10,意思是运行10秒以上的语句
  • 由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的SQL,结合之前的explain进行全面的分析
说明
  • 默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数
  • 当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少的带来一定的性能影响,慢查询日志支持将日志记录写入文件
查看是否开启及如何开启
  • 默认:SHOW VARIABLES LIKE ‘%slow_query_log%’;
  • 开启:set global slow_query_log = 1;
  • 使用以上命令开启慢查询日志,只能对当前数据库生效,如果MySQL重启后则不失效
  • 如果要永久生效,需要修改my.cnf,[mysqld]下增加或修改参数, slow_query_log和slow_query_log_file后,然后重启MySQL服务器,也即将如下两行配置进my.cnf(windows为my.ini)文件,
slow_query_log=1
slow_query_log_file=/var/lib/mysql/CADE-slow.log
  • 关于慢查询的参数slow_query_log_file,它指定慢查询日志文件的存放路径,系统默认会给一个缺省的文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)
  • 这个是由参数long_query_time控制,默认情况下long_query_time的值为10秒,命令:SHOW VARIABLES LIKE ‘long_query_time%’;
  • 可以通过命令修改set global long_query_time=3,或者在my.cnf(my.ini)参数里进行修改
long_query_time=3
log_output=FILE
  • 假如运行时间正好等于long_query_time的情况,并不会被记录,也就是说,在mysql的源码里是判断大于long_query_time,而非大于等于
  • 查询当前系统中有多少条慢查询记录:SHOW GLOBAL STATUS LIKE ‘%SLOW_QUERIES%’;
查看mysqldumpslow的帮助信息
  • s:是表示按照何种方式排序
  • c:访问次数
  • l:锁定时间
  • r:返回记录
  • t:查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平局查询时间
  • t:即为返回前面多少条的数据
  • g:后边搭配一个正则匹配模式,大小写不敏感的
  • 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/CADE-slow.log
  • 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/CADE-slow.log
  • 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/CADE-slow.log
  • 另外建议在使用这些命令时结合|和more使用,否则有可能出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/CADE-slow.log | more

数据脚本

  • 创建函数,假如报错:This funtion has none of DETERMINISTIC… 由于开启了慢查询日志,因为我们开启了bin-log,我们就必须为我们的function指定一个参数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
  • 这样添加了以后,如果mysqld重启,上述参数又会消失,永久方法,my.cnf(my.ini)加上log_bin_trust_function_creators=1
随机产生字符串
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 $$
产生随机数
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RNAD()*10);
	RETURN i;
END $$
向某表里插入数据
DELIMITER $$
 CREATE PROCEDURE insert_student(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 student(id,name,age,school) VALUES ((START+i),rand_string(5),rand_string(3),rand_string(10));
		UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$
DELIMITER ;

SHOW PROFILE

  • 是什么:是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量
  • 官网:http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
  • 默认情况下,该参数处于关闭状态,并保存最近15次运行结果
SHOW VARIABLES LIKE 'profiling';
SET profilint=on;
show variables like '%profil%';
分析步骤
  • 是否支持:看看当前的mysql版本是否支持
  • 开启功能:默认是关闭的,使用前需要开启
  • 运行SQL
    • SELECT * FROM EMP group by id%10 limit 1500;
    • SELECT * FROM emp group by id%20 order by 5;
  • 诊断SQL:
    • show profiles;
    • SHOW PROFILE cpu,block io for query #id#;
  • 日常开发需要注意的结论
    • converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬了
    • Creating tmp table创建临时表,拷贝数据到临时表,用完再删除
    • Copying to tmp table on dist把内存中临时表复制到磁盘,危险!
  • type(status):
    • ALL:显示所有的开销信息
    • BLOCK IO:显示块IO相关开销
    • CONTEXT SWITCHES:上下文切换相关开销
    • CPU:显示CPU相关开销信息
    • IPC:显示发送和接收相关开销信息
    • MEMORY:显示内存相关开销信息
    • PAGE FAULTS:显示页面错误相关开销信息
    • SOURCE:显示和Source_function,Source_file,Source_line相关的开销信息
    • SWAPS:显示交换次数相关开销的信息

全局查询日志

  • 配置启用
    • 在my.cnf中设置如下:开启general_log=1
    • 记录日志文件的路径:general_log_file=/path/logfile
    • 输出格式:log_output=FILE
  • 编码启用
    • set global general_log=1;
    • set global log_output=‘TABLE’;
    • 此后,你所编写的所有SQL语句,都将会记录到mysql库里的general_log表中,可以使用命令查看:select * from mysql.general_log
  • 永远不要在生产环境开启这个功能

附录

在这里插入图片描述

版权声明:本文为Jahnsonxi原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Jahnsonxi/article/details/103910062