说说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子查询优化策略大致分为：
　　半连接（semi-join): 半连接优化本质上是把子查询上拉到父查询中，与父查询的表做join/semi-join的操作。关键词上拉。
　　物化子查询(Materialization)：子查询的结果通常缓存在内存或临时表中。
　　EXISTS strategy：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词下推。
　　一图胜千言 ，下图展示了 MySQL 针对子查询的优化策略
　　图片来自于 https://mariadb.com/kb/en/subquery-optimizations-map/
　　需要对图中做解释的是:
　　白色区域是常见的 子查询类型， x IN (SELECT ...) ，x= any(select)，exists (select )。
　　白色区域越大说明使用频率越多，比如最常见的子查询是 x IN (SELECT ...)
　　有颜色的区域表示优化方法和策略，不同颜色代表不同的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(id) AS id
　　from activity
　　where zone = ＂qa＂ AND status = 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
　　-The End-