查询截取分析

查询优化

1. 永远小表驱动大表

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

2.order by关键字优化

• ORDER BY 子句，尽量使用Index方式来排序，避免使用Filesort方式排序
• 尽可能在索引列上完成排序操作，遵守索引建的最佳左前缀
• 如果不在索引列上，filesort有两种算法：双路排序和单路排序

双路排序：MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和prderby列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新读取对应的数据输出
单路排序：从磁盘中读取查询需要的所有列，按照order by列对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但会使用更多的内存

• 优化策略

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

• 总结：

1.order by时select *是一个大忌只Query需要的字段，在这里的影响是：

• 1.1 当Queryd的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法----单路排序，否则用老算法—多路排序
  -1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些

2 尝试提高sort_buffer_size

• 不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高

3 尝试提高max_length_for_sort_data

• 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率
  

3.GROUP BY关键字优化

group by实质是先排序后进行分组，按照索引建的最佳左前追
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询记录中

查看是否开启及如何开启（默认关闭）

show VARIABLES like '%slow_query_log%';

开启（只对当前数据库生效）

set global slow_query_log=1;

查看参数long_query_time（规定超过时间）

show VARIABLES like '%long_query_time%';

设置参数long_query_time（需要重新连接或新开一个会话才能看到修改值）

set global long_query_time=5;

查询有几条慢查询语句

show global status like '%Slow_queries';

日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，MySQL提供了日志分析工具mysqldumpslow

mysqldumpslow --help

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

批量数据脚本
设置参数log_bin_trust_function_creators

set global log_bin_trust_function_creators=1；

创建函数随机产生字符串

delimiter $$
create function ran_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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+rand()*10);
return i;
end $$

创建存储过程

//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),ran_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

//dept表
delimiter $$ 
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),ran_string(10),ran_string(8));
until i=max_num
end repeat;
commit;
end $$

调用存储过程

//dept表
DELIMITER;
CALL insert_dept(100,10);

//emp表
DELIMITER;
CALL insert_emp(100001,500000);

Show Profile

Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量，默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

1. 当前mysql版本是否支持-------show VARIABLES like ‘profiling’;

2. 开启功能-------set profiling=on;

3. 运行SQL

4. 查看结果-------show profiles;

5. 诊断SQL—show profile cpu,block io for query Query_ID(语句id);
   

6. 日常开发需要注意的结论

converting HEAP to MyISAM----查询结果太大，内存不够用
Creating tmp table—创建了临时表
Copying to tmp table on disk—把内存中临时表复制到磁盘，危险！！！
locked

全局查询日志

set global general_log=1;
set global log_output='TABLE';
//查询sql
select * from mysql.general_log;

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