MySQL高级之查询截取分析

三、查询截取分析

0）查询优化之排序优化。
1）开启慢查询日志，设置其阈值，抓取慢SQL并记录。
2）explain+慢SQL
3）show profile，查询SQL在MySQL服务器里面的执行细节和生命周期情况。
4）DBA 对数据库服务器参数调优。

1、查询优化

1）小表驱动大表，小的数据集驱动大的数据集。
exist 与 in，

# in用小表B来驱动大表A
select * from A where id in (select id from B)
# exist 用大表驱动小表，要是A表比B表小，那么exists优于in
select * from A where exists (select 1 from B where A.id = B.id)

2）order by关键字优化

1. order by 子句，尽量利用index排序，从而避免using filesort

2. 尽可能在索引列上遵照左前缀完成排序。

3. 如果不在索引列上，filesort有两种排序算法，双路排序和单路排序。
   双路排序，扫描两次磁盘得到数据，读取行指针和orderby列，对其进行排序，然后扫描已经排好的列表，重新读取相应的数据输出。
   注：取一批数据，就要进行一次IO操作将数据读入内存，IO是很耗时的。
   单路排序，区别于双路排序，第一次就把一整行读入到buffer进行排序再输出。
   结论及引申出的问题，不出意外，单路排序优于双路。但是，如果单路排序不能一次获取所有数据，反而变得更加麻烦。单路排序读出来的所有字段信息可能超出了sort_buffer容量大小，再排的话就是多次IO。即本来想一次IO搞定，可是字段太多导致数据量太多，要把数据截成多段多次IO，而且还要产生临时表。

4. 优化策略
   进行SQL数据库服务器参数调优，比如把sort_buffer_size容量调大，把max_length_for_sort_data参数调大。除此之外，不要用select *，这样容易把buffer用满。

注：
1）当query的字段大小总和小于max_length_for_sort_data且排序字段不是TEXT|BOLB类型时，会用改进算法----单路算法。否则用老算法----双路算法。
2）两种算法都可能超过sort_buffer_size的大小，此时会建立临时文件并进行合并排序，导致多次IO。但是单路毕竟会比多路要加载的数据量大，所以需调高sort_buffer_size来尽可能执行单路算法。

5. 总结
   

3）group by关键字优化
类似于orderby，
6. group by实质是先排序，再分组，也应遵照左前缀来排序。
7. 当无法使用索引列字段时，增大sort_buffer_size和max_length_for_sort_data的大小。
8. where后条件优于having后的条件，所以能把限定条件写在where后就不写在having后。

2、慢查询日志

1）是什么？
A）是一种记录SQL查询的日志，用于记录执行SQL时间超过long_query_time的SQL语句。
B）long_query_time的默认值为10秒，我们可以手动设置更小的阈值。
2）怎么玩？
A）说明
默认没有开启慢查询，需手动设置该参数；如果不是调优需要，一般不建议启动该日志，开启后会写入慢SQL从而影响性能。
B）查看是否开启和如何开启

操作	命令
查看	show variables like ‘slow_query_log%’;
开启	set global slow_query_log = 1;

C）开启慢查询日志，什么样的SQL会被记录？

操作	命令
查看阈值	show variables like ‘long_query_time%’
设置阈值	set global long_query_time = 3;#表示大于3秒就记录
再次查看	要么重新建立session，要么show global variables like ‘long_query_time%’；
查询当前日志有多少慢SQL	show global status like ‘Slow_queries%’;

3）日志分析工具mysqldumpslow
A）查看mysqldumpslow的帮助信息
mysqldumpslow --help，参数如下，

参数	作用
-s	表示何种方式排序
-c	访问次数
-l	锁定时间
-r	返回记录
-t	查询时间
-al	平均锁定时间
-ar	平均返回记录数
-at	平常查询时间
-t	返回前面多少条数据
-g	后接正则表达式，忽略大小写。

B）工作常用参考

C）配置
my.ini、my.conf去配置。

3、批量数据脚本

1）建表
2）开启二进制日志

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;

3）创建函数
随机产生部门号和字符串，

Delimiter $$
CREATE FUNCTION rand_string ( n IN ) RETURNS VARCHAR ( 255 ) BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghigkrmnopqrstvuwxyz';
	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 ( ) RETURN INT ( 5 )
BEGIN
	DECLARE
	i INT DEFAULT 0;
	
	SET i = floor( 100+ rand( ) * 10 );
	RETURN i;
END $$

4）创建存储过程
emp，dept，

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 )
		VALUES
			( ( START + i ), rand_string ( 6 ), 'salesman', 0001, CURDATE( ) );
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;

END $$

5）调用存储过程

Delimiter;
CALL insert_dept ( 100, 10 );
CALL insert_emp ( 100001, 500000 );

4、Show Profiles

1）是什么？
用来分析当前session中SQL语句执行的资源消耗情况。所以其既可以测量SQL的优化度，又可以作为调优SQL的辅助信息。
2）官网
http://dev.mysql.com/doc/refman/5.5/en/show.profile.html
注：默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
3）分析步骤

理论	命令
1. 查看当前数据库版本是否支持	show variables like ‘profiling%’;
2. 默认是关闭，使用前需开启	set profiling = on;
3. 运行SQL样本	运行一些慢SQL样本语句。
4. 用show profiles来查看记录的结果	show profiles;

5. 诊断上面记录的结果
show profile cpu,block io for query Query_ID;

注：除了cpu,block io，还有如下参数，

type	explaining
all	显示所有的开销信息
block io	显示块io相关开销
context switches	上下文切换相关开销
cpu	显示cpu相关开销
ipc	显示发送和接收相关开销信息
memory	显示内存相关开销信息
page faults	显示页面错误相关开销信息
source	显示和source_function，source_file，source_line相关开销信息
swaps	显示交换次数相关开销的信息

6. 日常开发需注意的事项
   分析后，status中四个最重要的参数如下，

status	direction
converting HEAP to MyISAM	查询结果太大，内存装不下，往磁盘上装入
Creating tmp table	创建临时表
coping to tmp table on disk	把内存中临时表复制到磁盘，情况及其糟糕
locked	被锁住

注：creating tmp table 之后，拷贝数据到临时表，这个是最耗时的，最后再删除临时表，一共三步。

5、全局查询日志

1）配置启用
在Windows下的my.ini文件或是Linux下的my.cnf中，

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

2）编码启用

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

3）查看SQL

#general_log 会记录执行的SQL，
select * from mysql.general_log;

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