MySQL高级第三章——查询截取分析

时间:2023-09-18 13:39:43

一、查询分析

  1.永远小表驱动大表

    使用小的数据集驱动大的数据集。

    //复习 EXISTS 的知识:SELECT ... FROM tb WHERE EXISTS (subquery)  是因为前后数据集不一致时EXISTS比IN性能更高。

      (子查询可以改写IN的写法为SELECT 1 FROM tb WHERE ...等)

    对EXISTS的理解:

将主查询的数据,放到子查询中作条件验证,根据验证结果(TRUE | FALSE)来决定主查询的数据结果是否得以保留。

  2.ORDER BY排序优化

    现在我们关心的的不再是是WHERE后的(之前几节已经做过分析),我们关系的是ORDER BY后的,也就是是否产生了 filesort

    排序有两种情况:use index use filesort,由前面一章已经知道,最好不要有 filesort,Index的排序效率更高

    ORDER BY的排序依旧是使用索引的最左前列,也就是最左前缀原则依旧适用。并且,使用WHERE 和 ORDER BY来匹配最左前缀原则。

      所以说,应当尽可能的在索引列上完成排序操作,并遵循最左前缀原则。

      如果需求确实使得排序不在索引列上,Mysql就要启动filesort,其中存在两种算法:双路排序和单路排序

    关于排序算法,可以参见:http://www.cnblogs.com/zhoujinyi/p/5437289.html   (mysql4.1之前都是需要两次IO的双路排序)

    一些ORDER BY的排序优化实例,请参见:http://blog.csdn.net/z69183787/article/details/53389773

    关于如何进行ORDER BY的调优:

      增大sort_buffer_size参数的设置

      增大max_length_for_data参数的设置

    在排序时请尽量避免SELECT *,避免ASC DESC同时出现(都是ASC DESC都能用到索引,但不能同时存在升降序)

  3.GROUP BY的优化

    GROUP BY实质是先排序,后分组;并且遵循索引的最左前缀原则。

    参数的设置与ORDER BY的调优一致。

    能使用WHERE 限定就不要使用HAVING

二、慢查询日志

  1.是什么?

  慢查询日志是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件,通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的。
   通过使用--slow_query_log[={0|1}]选项来启用慢查询日志。所有执行时间超过long_query_time秒(可以看到,默认是10s,可自定义)的SQL语句都会被记录到慢查询日志。
   缺省情况下hostname-slow.log为慢查询日志文件安名,存放到数据目录,同时缺省情况下未开启慢查询日志。

   // 通过抓取慢的SQL,结合之前的起手式 EXPLAIN 就可以进行慢日志的分析

  2.怎么玩?

    查看是否开启:show variables like '%slow_query%'

    MySQL高级第三章——查询截取分析

    //这里显示了位置与开启状态。

      设置位置:set global slow_query_log_file="/var/lib/mysql/mysql_slow_query.log";

    如何开启:set global slow_query_log=1;

    MySQL高级第三章——查询截取分析

    //注意,这只是临时开启,并且仅仅针对本数据库有效,重启后便失效。(当然,慢日志应当是尽量避免开启,只在需要分析时开启)

    若要永久开启慢日志,需要修改配置文件,请参见:https://jingyan.baidu.com/article/0aa223755476db88cc0d6492.html

    查看慢查询时间:show variables like "long_query_time";

    MySQL高级第三章——查询截取分析

    //默认为10秒,并且,是 >而非 >=

    设置慢查询时间阈值:set global long_query_time=3;(设置阈值为3秒)

    MySQL高级第三章——查询截取分析

    //神奇的发现,还是10秒。需要重新建立一次连接才能看到!或者通过查看全局变量的形式查看:

    MySQL高级第三章——查询截取分析

    模拟慢查询的SQL:通过sleep(),类似线程

    MySQL高级第三章——查询截取分析

    找到慢日志:

    MySQL高级第三章——查询截取分析

    查看慢日志:

    MySQL高级第三章——查询截取分析

    查看出现慢查询日志的累计值:

    MySQL高级第三章——查询截取分析

  3.日志分析工具——mysqldumpslow

  手工查看日志显然不科学,mysql提供了日志分析工具:mysqldumpsolw

    查看帮助信息(之前命令复习):

    MySQL高级第三章——查询截取分析

    MySQL高级第三章——查询截取分析

    各参数简述:

    MySQL高级第三章——查询截取分析

    实例如下:(分析命令+日志位置)

    MySQL高级第三章——查询截取分析

    参数使用可以参见:http://www.cnblogs.com/cyt1153/p/6569124.html

 三、批量数据脚本

  大致步骤如下:

    (详细步骤可以参见:http://www.cnblogs.com/Onlywjy/archive/2017/08/13/7354865.html

    建表(员工部门表);

    为了防止插入大数据报错,开启参数:log_bin_trust_function_creators=1;

    创建产生随机字符串的函数(用于插入不重复的数据);

    同理,创建产生随机部门编号的函数;

    创建存储过程,调用函数插入记录(一次50W条,调用20次);

    调用存储过程,插入记录;

四、show profile  

  1.是什么

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

    与之前的功能一样,mysql为了提高自身性能,很多高级功能都是关闭的,需要手动开启。

  2.分析步骤:

    查看是否支持

    MySQL高级第三章——查询截取分析

    //可以看到,默认是关闭的。

    开启show profile——SET profiling=1;或 SET profiling=on;

    MySQL高级第三章——查询截取分析

    //默认保存15条

    运行SQL

      MySQL高级第三章——查询截取分析

      MySQL高级第三章——查询截取分析

      MySQL高级第三章——查询截取分析

      查看总体结果——show profiles;

      MySQL高级第三章——查询截取分析

      //显示了详细的查询ID,持续时间,查询语句

      查看详细结果——SHOW PROFILE FOR QUERY n | show profile cpu,block io fro query n;

              当然除了常用的cpu block io等信息,还可以选择其它信息

      MySQL高级第三章——查询截取分析

       //列出了完整的生命周期。(n代表query ID)

      //可以看出,发送数据比执行更长等信息

      以上繁杂的信息中,出现以下信息:危险!

      MySQL高级第三章——查询截取分析

      //出现了临时表等信息,慎重!

 五、全局查询日志  

    强烈建议:只能测试环境使用,慎用!生产环境严禁使用!

     开启:

        Set global general_log=1;

      Set global log_output=’TABLE’;

    

    此后,你所编写的sql语句 都会被记录到mysql 库里的general_log表,可以用以下命令查看:

    Select * from mysql.general_log;

    这样,就可以根据时间来定位收集发生问题的SQL