Loading...
墨滴

北在南方

2021/04/22  阅读:39  主题:默认主题

mysql 子查询

前言

前两天开发找DBA解决一个含有子查询的慢sql,我们通过将其修改为关联查询和添加索引解决。考虑到 大多数开发并没有准确的理解 MySQL 的子查询执行原理。本文介绍如何解决子查询慢查的思路。

原理

首先 知其然,知其所以然。大部分子查询为什么慢? 我们得了解 MySQL 关联查询和子查询的处理机制。

MySQL 在处理所有的查询的时候都强行转换为联接来执行,将每个查询包括多表中关联匹配,关联子查询,union,甚至单表的的查询都处理为联接,接着MySQL执行联接,把每个联接再处理为嵌套循环 (nest-loop);

很多使用子查询的人 想当然的认为 子查询会由内到外,先完成子查询的结果, 然后在用子查询来驱动外查询的表,完成查询。例如: select * from test where tid in (select aid from sub_test where gid=3) 通常我们会想到该sql的执行顺序为:

a. 先从 sub_test 表中获取 gid=3的记录(3,4,5)

b. 然后和外面的查询做匹配 tid in (3,4,5)。

但是,实际上对于子查询,外部查询的每条符合条件的记录,都会把子查询执行一次。如果遇到子查询查询量比较大或者索引不合理的情况,sql就变慢查。

当我们使用explian查看包含子查询的执行计划时,尤其要注意select_type 字段的内容,如果包含 SUBQUERY , DEPENDENT SUBQUERY 就需要提高警惕。

官方含义为:

SUBQUERY:子查询中的第一个SELECT;

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 ,注意如果外部查询的结果集数量比较大,比如几十万上百万,就会执行几十万上百万次子查询,必然造成慢查。

优化策略

MySQL子查询优化策略大致分为:

  1. 半连接(semi-join): 半连接优化本质上是把子查询上拉到父查询中,与父查询的表做join/semi-join的操作。关键词上拉

  2. 物化子查询(Materialization):子查询的结果通常缓存在内存或临时表中。

  3. EXISTS strategy:把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词下推

一图胜千言 。

https://mariadb.com/kb/en/subquery-optimizations-map/

需要对图中做解释的是:

  1. 白色区域是常见的 子查询类型, x IN (SELECT ...) ,x= any(select),exists (select )。

  2. 白色区域越大说明使用频率越多,比如最常见的子查询是 x IN (SELECT ...)

  3. 有颜色的区域表示优化方法和策略,不同颜色代表不同的mysql 分支。

强烈安利 Mariadb 的一系列博客,里面有n篇文章介绍subquery的优化。

https://mariadb.com/kb/en/subquery-optimizations/

书上来的终觉浅,绝知此事要躬行。

优化案例

业务的sql 如下,该sql 执行超过1200ms ,被sql-killer kill掉,影响业务使用。

select app_name,pkg_version,zone,created_at 
from  activity 
where id in (
    select MAX(idAS id 
    from  activity 
    where zone = 'qa' AND status = 2 AND zanpkg_version != ''
    AND namespace = 'qa' 
    group by app_name,zone) 
order by  id desc limit 500;

执行计划

第一步 MySQL 执行 select id, app_name,pkg_version,zone,created_at from activity order by id desc limit 500; 获取一个结果集

第二部 拿第一步中的结果500多行每一个记录去执行 子查询,每次遍历70w行左右。而且子查询里面没有合适的索引。

优化方法

1 where条件中zone=qa是固定值,group by zone 无意义,去掉group by zone。

2 针对 (zone, namespace, status) 加上组合索引。

3 改子查询为关联查询。

select  a.app_name, a.zanpkg_version, a.zone, a.created_at 
from activity a, ( select MAX(id) AS mid 
    from  activity 
    where zone = 'qa' AND status = 2 AND zanpkg_version != ''          
    AND namespace = 'qa' 
    group by app_name) b
where  a.id = b.mid limit 500;

修改之后的sql执行时间在 300-500ms 之间。

参考文章

https://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html https://blog.csdn.net/kk185800961/article/details/49340589

https://blog.csdn.net/fly2nn/article/details/61924636

https://blog.csdn.net/fly2nn/article/details/61924637

https://blog.csdn.net/fly2nn/article/details/61924640

北在南方

2021/04/22  阅读:39  主题:默认主题

作者介绍

北在南方