五、Mysql高级学习笔记之查询截取分析

查询截取分析

1、查询优化

1.1、永远小表驱动大表 类似嵌套循环Nested Loop

1.2、order by关键字优化

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

MySQL支持二种方式的排序，FileSort和Index,Index效率高。
它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：
1、ORDER BY语句使用索引最左前列
2、使用where子句与OrderBy子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

【双路排序】

1、MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
2、从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，
出现了第二张改进的算法，就是单路排序。

【取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。】

【单路排序】

从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，
因为它把每一行都保存在内存中了。

【结论及引申出的问题】

由于单路是后出来的，总体而言好过双路
但是用单路有问题

【优化策略】

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

【总结

1.3、GROUP BY关键字优化

【groupby实质是先排序后进行分组，遵照索引建的最佳左前缀】

【当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置】

【where高于having,能写在where限定的条件就不要去having限定了。】

2、慢查询日志

1、什么是慢查询日志

2、使用

说明：

查看是否开启及如何开启：
查看：SHOW VARIABLES LIKE ‘%slow_query_log%’
开启：set global slow_query_log = 1

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

查看当前多少秒算慢：
SHOW VARIABLES LIKE ‘long_query_time%’;
设置慢的阙值时间：
set global long_query_time=3;

配置版：

3、日志分析工具mysqldumpshow

在生产环境中，如果要手动分析日志，查找，分析sql，显然是个体力活，MYSQL提供了日志分析工具mysqldumpshow

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

常用：

3、批量数据脚本

往表里插入1000W数据

设置参数log_trust_function_createors

创建函数保证每条数据都不同

rand_string:随机产生字符串
rand_num:随机产生部门编号
创建存储过程：

DELIMITER $$
CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  ；提高执行效率
 SET autocommit = 0;    
 REPEAT  ##重复
 SET i = i + 1;  
 INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一个循环
 END REPEAT;  ##满足条件后结束循环
 COMMIT;   ##执行完成后一起提交
 END $$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

#执行存储过程，往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 ,rand_string(10),rand_string(8));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$ 
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

调用：
DELIMITER ;
CALL insert_dept(100,10);

DELIMITER ; #将 结束标志换回 ;
CALL insert_emp(100001,500000);
CALL insert_emp10000(100001,10000);