MySQL--查询截取分析

1.查询优化

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

EXISTS

SELECT ... FROM table WHERE EXISTS(subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。

提示

1.EXISTS(subquery)只返回TRUE或FALSE，因此子查询中SELECT * 也可以是SELECT 1或SELECT 'X'，官方说法是实际执行时会忽略SELECT清单，因此没有区别

2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可以进行实际检验以确定是否有效率问题。

3.EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

1.2order by关键字优化

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

1.2.1.1建表SQL

1.2.1.2Case

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

1.2.1.3ORDER BY满足两情况，会使用Index方式排序：

1.ORDER BY语句使用索引最左前列

2.使用Where子句与Order BY子句条件列组合满足索引最左前列

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

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

1.2.2.1双路排序

1.MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

2.从磁盘取排列字段，在buffer进行排序，再从磁盘取其他字段

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

1.2.2.2单路排序

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

1.2.2.3结论及引伸出的问题

1.由于单路是后出的，总体而言好过双路

2.但是单路有问题

1.2.3优化策略

1.增大sort_buffer_size参数的设置

2.增大max_length_for_sort_data参数的设置

3.Why

1.2.4小总结

1.3GROUP BY关键字优化

1.group by实质是优先排序进行分组，遵照索引建的最佳左前缀

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

3.where高于having，能写在where限定的条件就不要去having限定了

2.慢查询日志

2.1是什么

2.2这么玩

2.2.1说明

2.2.2查看是否开启及如何开启

1.默认：SHOW VARIABLES LIKE '%slow_query_log%';

2.开启：set global slow_query_log=1;

2.2.3那么开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？

2.2.4Case

2.2.4.1查看当前多少秒算慢

SHOW VARIABLES LIKE 'long_query_time%';

2.2.4.2设置慢的阀值时间

set global long_query_time=3;

2.2.4.3为什么设置后看不出变化

1.需要重新连接或新开一个会话才能看到修改值。SHOW VARIABLES LIKE 'long_query_time%';

2.show global variables like 'long_query_time';

2.2.4.5记录慢SQL并后续分析

2.2.4.6查询当前系统中有多少条慢查询记录

2.2.5配置版

2.3日志分析工具MySQLdumpslow

2.3.1查看MySQLdumpslow的帮助信息

2.3.2工作常用参考

3.批量数据脚本

3.1建表

3.2设置参数log_bin_trust_function_creators

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

3.3.1随机产生字符串

3.3.2随机产生部门编号

3.4创建存储过程

3.4.1创建往emp表中插入数据的存储过程

3.4.2创建往dept表中插入数据的存储过程

4.5调用存储过程

4.5.1dept

4.5.2emp

4.Show Profile

4.1是什么

是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量

4.2官网

http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

4.3默认情况下，参数处于关闭状态，并保存最佳15次的运行结果

4.4分析步骤

4.4.1是否支持，看看当前的MySQL版本是否支持

4.4.2开启功能，默认是关闭，使用前需要开启

4.4.3运行SQL

1.SELECT * FROM emp GROUP BY id%10 limit 150000;

2.SELECT * FROM emp GROUP BY id%20 ORBER BY 5;

4.4.4查看结果，show profiles;

4.4.5诊断SQL，show profile cup，block io for query 上一步前面的问题SQL数字号码

参数备注

4.4.6日常开发需要注意的结论

4.4.6.1converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了。

4.4.6.2Creating tmp table创建临时表

1.拷贝数据到临时表

2.用完再删除

4.4.6.3Copying to tmp table on disk把内存中临时表复制到磁盘，危险

4.4.6.4locked

5.全局查询日志

5.1配置启用

5.2编码启用

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