MySQL高级-5-查询截取分析

本文最后更新于:2020年8月12日 上午

mysql Ver 14.14 Distrib 5.5.62

5.1 查询优化

5.1.1 小表驱动大表

优化原则:小的数据集驱动大的数据集。

SELECT * FROM A
WHERE id IN (
	SELECT id from B
)

当B表的数据集必须小于A表的数据集时,用IN优于EXISTS。


SELECT * FROM A
WHERE EXISTS (
	SELECT 1 FROM B
    WHERE B.id = A.id
)

当A表的数据集小于B表的数据集时,用EXISTS优于IN。

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

注意:A表和B表的id字段应建立索引

提示1:EXISTS(subquery)只返回TRUE或FALSE,因此子查询中的SELECT*也可以是SELECT 1或其他,官方说法是实际执行时会忽略SELECT清单,因此没有区别。

提示2:EXISTS子查询的实际执行过程可能经过了优化,而不是我们理解上的逐条对比,如果担心效率问题,可进行实际校验一确定是否有效率问题。

提示3:EXISTS子查询往往也可以用条件表达式、其他子查询或JOIN代替,最优方案以实际情况为准。

5.1.2 ORDER BY关键字优化

5.1.2.1 介绍

  1. ORDER BY子句,尽量使用index方式排序,避免出现filesort方式排序。

    MySQL支持两种方式的排序,filesort和index,index效率更高。

    ORDER BY满足两种情况会使用index方式排序,ORDER BY语句使用索引最左前列;使用where子句与ORDER BY子句条件列组合满足索引最左前列。

  2. 尽可能在索引列上完成排序操作,遵循索引建的最佳左前缀。

  3. 如果不在索引列上,filesort有两种算法,双路排序和单路排序。

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

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

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

    在sort_buffer中,单路排序比双路排序要占用更多空间,因为单路排序是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能去sort_buffer容量大小的数据,然后进行排序(创建tmp文件,多路合并),排序完再取sort_buffer容量大小,再进行排序,从而多次I/O。

  4. 优化策略:

    增大sort_buffer_size参数的设置值。

    增大max_length_for_sort_data参数的设置值。

    提高ORDER BY速度的方法

    1、ORDER BY时最忌讳使用SELECT *,一定要只query需要的字段。主要的原因有两点,第一,当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用单路排序,否则用多路排序;第二,两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用算法的风险会更大一些,所以需要提高sort_buffer_size。

    2、尝试提高sort_buffer_size,不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力与提高,因为这个参数是针对每个进程的。

    3、尝试提高max_length_for_sort_data,提高这个参数会增加使用单路排序的概率,但是如果设置的值太高,数据总容量超过了sort_buffer_size的概率就会增大,直观体现在更高的磁盘I/O活动和更低的处理器利用率。

5.1.2.2 总结

MySQL两种排序方式:文件排序或扫描有序索引排序。

MySQL能为排序与查询使用相同的索引。

KEY a_b_c(a,b,c)

  1. order by能使用索引最左前缀。
    • ORDER BY a
    • ORDER BY a,b
    • ORDER BY a,b,c
    • ORDER BY a DESC,b DESC,c DESC
  2. 如果WHERE使用索引的最左前缀定义为常量,则order by能使用索引。
    • WHERE a=const ORDER BY b,c
    • WHERE a=const AND b=const ORDER BY c
    • WHERE a=const AND b>const ORDER BY b,c
  3. 不能使用索引进行排序。
    • ORDER BY a ASC,b DESC,c DESC
    • WHERE g=const ORDER BY b,c
    • WHERE a=const ORDER BY c
    • WHERE a=const ORDER BY a,d
    • WHERE a in(…) ORDER BY b,c

5.1.3 GROUP BY关键字优化

  1. GROUP BY实质是先排序后进行分组,遵照索引建的最佳左前缀。
  2. 当无法使用索引列时,增到max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置。
  3. where高于having,能写在where限定的条件就不要去having限定了。

5.2 慢查询日志

5.2.1 慢查询日志介绍

  1. MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
  2. 具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10秒。
  3. 默认情况下,MySQL数据库没有开启慢查询日志,需要手动设置。
  4. 如果不需要调优,一般不建议开启该参数,因为开启慢查询日志或多或少会带来一定程度上性能的影响。慢查询日志支持将日志记录写入文件。

5.2.2 查看是否开启

查看当前状态:

show variables like '%slow_query_log%';

开启:

set global slow_query_log=1;

如果要永久生效,必须修改配置文件。

配置文件内容为:

slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log
long_query_time=3
log_output=FILE

5.2.3 查看并设置慢查询日志记录的阈值

查看阈值:

show [global] variables like 'long_query_time%';

设置阈值:

#修改阈值为3s
set global long_query_time=3;

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

show global status like 'Slow_queries%';

5.2.5 日志分析工具mysqldumpslow

5.2.5.1 参数介绍

参数功能
-s按照指定方式排序
c访问次数
l锁定时间
r返回记录
t查询时间
al平均锁定时间
ar平均返回记录数
at平均查询时间
-t指定返回数据条目数
g后边搭配正则表达式,大小写不敏感。

5.2.5.2 常用指令

  1. 得到返回记录最多的10个SQL。

    mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
  2. 得到访问次数最多的10个SQL。

    mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
  3. 得到按照时间排序的前10条里面含有左连接的查询语句。

    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/mysql-slow.log
  4. 建议在使用这些命令的时候结合管道符|和more使用,否则可能会出现爆屏的情况。

    mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log | more

5.3 show profile

5.3.1 show profile介绍

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

默认情况下处于关闭状态,默认保存15次运行结果。

5.3.2 profile相关语法

  1. 查看当前MySQL版本是否支持show profile。

    show variables like 'have_profiling';
  2. 查看当前show profile状态。

    show variables like 'profiling';
  3. 开启show profile.

    set profiling=on;
  4. 查看结果。

    show profiles;

5.3.3 诊断SQL命令

show profile 参数1,参数2,参数3,... for query Query_ID;
参数功能
all显示所有信息
block io显示块I/O相关信息
context switches上下文切换相关信息
cpu此案是CPU相关信息
ipc显示发送和接收相关信息
memory显示内存相关信息
page faults显示页面错误相关信息
source显示和source_function,source_file,source_line先关的信息
swaps显示交换次数相关信息

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

  1. converting HEAP to MyISAM 查询结果太大,内存不够用,开始调用磁盘资源。
  2. creating tmp table 创建临时表,拷贝数据到临时表,使用完毕后再删除。
  3. copying to tmp table on disk 把内存中临时表复制到磁盘,该操作很危险。
  4. locked 出现锁

5.4 全局查询日志

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

5.4.1 配置启用全局查询日志

general_log=1
general_log_file=/path/logfile
log_output=FILE

5.4.2 编码启用全局查询日志

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

此后,所有执行的SQL语句将会记录到MySQL库里的general_log表中。

select * from mysql.general_log;

版权声明:自由转载-非商用-非衍生-保持署名 Creative Commons BY-NC-ND 3.0 协议,转载请注明出处!

 目录