3.查询截取分析

基于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
• 永远不要在生产环境开启这个功能

---

附录