MySQL子查询的优化
2017-08-30 16:47 abce 阅读(1055) 评论(2) 编辑 收藏 举报本文基于MySQL5.7.19测试
创建四张表，pt1、pt2表加上主键
1 2 3 4 | mysql> create table t1 (a1 int, b1 int);mysql> create table t2 (a2 int, b2 int); mysql> create table pt1 (a1 int, b1 int, primary key (a1));mysql> create table pt2 (a2 int, b2 int, primary key (a2)); |
向表中分别插入10000条数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> delimiter //mysql> create procedure prod_dt() -> begin -> declare i int; -> set i=0; -> while i<10000 do -> insert into t1(a1,b1) values(i,i+1); -> insert into t2(a2,b2) values(i+1,i+2); -> insert into pt1(a1,b1) values(i,i+1); -> insert into pt2(a2,b2) values(i+1,i+2); -> set i=i+1; -> end while; -> end; -> //Query OK, 0 rows affected (0.05 sec)mysql> delimiter ;mysql> call prod_dt() ; |
MySQL支持对简单SELECT查询中的子查询优化，包括：
1 简单SELECT查询中的子查询。
2 带有DISTINCT、ORDERBY、LIMIT操作的简单SELECT查询中的子查询。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # 没有主键，优化器进行了优化，子查询物化后和表t1进行连接。执行计划中没有子查询mysql> explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2 >10);+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where || 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 3.33 | Using where; Using join buffer (Block Nested Loop) || 2 | MATERIALIZED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 33.33 | Using where |+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+3 rows in set, 2 warnings (0.00 sec)#有主键，优化器进行了优化。执行计划中没有子查询mysql> explain extended select * from pt1 where pt1.a1<100 and a1 in (select a2 from pt2 where pt2.a2 >10);+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 89 | 100.00 | Using where || 1 | SIMPLE | pt2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | abce.pt1.a1 | 1 | 100.00 | Using index |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)mysql> |
MySQL不支持对如下情况的子查询进行优化：
-带有UNION操作。
-带有GROUPBY、HAVING、聚集函数。
-使用ORDERBY中带有LIMIT。
-内表、外表的个数超过MySQL支持的最大表的连接数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #有聚合函数，没有进行子查询优化mysql> explain extended select * from t1 where t1.a1>(select min(t2.a2) from t2);+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 33.33 | Using where || 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+2 rows in set, 2 warnings (0.01 sec)mysql> explain extended select * from pt1 where pt1.a1>(select min(pt2.a2) from pt2);+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+| 1 | PRIMARY | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5000 | 100.00 | Using where || 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+2 rows in set, 2 warnings (0.00 sec)mysql> |
MySQL支持哪些子查询的优化技术？
1 子查询合并技术 --> 不支持
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | #t2表上执行了2次子查询。如果支持子查询合并技术,则t2表上只执行一次子查询mysql> explain extended select * from t1 where a1<4 and (exists (select a2 from t2 where t2.a2<5 and t2.b2=1) or exists (select a2 from t2 where t2.a2<5 and t2.b2=2) );+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 33.33 | Using where || 3 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 3.33 | Using where || 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 3.33 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+3 rows in set, 2 warnings (0.00 sec)mysql> #pt2表上执行了2次子查询。如果支持子查询合并技术,则pt2表上只执行一次子查询mysql> explain extended select * from pt1 where a1<4 and (exists (select a2 from pt2 where pt2.a2<5 and pt2.b2=1) or exists (select a2 from pt2 where pt2.a2<5 and pt2.b2=2) );+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | PRIMARY | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using where || 3 | SUBQUERY | pt2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 10.00 | Using where || 2 | SUBQUERY | pt2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 10.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+3 rows in set, 2 warnings (0.00 sec)mysql> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #人为的合并查询条件为“(t2.b2=1 OR t2.b2=2)”t2表上的子查询，只执行一次mysql> explain extended select * from t1 where a1<10 and exists (select a2 from t2 where t2.a2<5 and (t2.b2=1 or t2.b2=2));+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 33.33 | Using where || 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 6.33 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+2 rows in set, 2 warnings (0.01 sec)mysql> explain extended select * from pt1 where a1<10 and exists (select a2 from pt2 where pt2.a2<5 and (pt2.b2=1 or pt2.b2=2));+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | PRIMARY | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where || 2 | SUBQUERY | pt2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 19.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)mysql> |
2 子查询展开(子查询反嵌套)技术 --> 支持得不够好
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | mysql> explain extended select * from t1, (select * from t2 where t2.a2 >10) v_t2 where t1.a1<10 and v_t2.a2<20;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 11.11 | Using where || 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 33.33 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+2 rows in set, 2 warnings (0.00 sec)mysql> explain extended select * from pt1, (select * from pt2 where pt2.a2 >10) v_t2 where pt1.a1<10 and v_t2.a2<20;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+| 1 | SIMPLE | pt2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 100.00 | Using where || 1 | SIMPLE | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+2 rows in set, 2 warnings (0.00 sec)mysql> #IN子查询的例子,可以看出子查询被物化mysql> explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2 >10);+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where || 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 3.33 | Using where; Using join buffer (Block Nested Loop) || 2 | MATERIALIZED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 33.33 | Using where |+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+3 rows in set, 2 warnings (0.00 sec)mysql>#从查询执行计划看，子查询不存在，SQL语句被转换为内连接操作，这表明MySQL只有在针对主键列进行类似的子查询时，才把子查询上拉为内连接。所以，MySQL还是支持子查询展开技术的。mysql> explain extended select * from pt1 where pt1.a1<100 and a1 in (select a2 from pt2 where pt2.a2 >10);+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 89 | 100.00 | Using where || 1 | SIMPLE | pt2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | abce.pt1.a1 | 1 | 100.00 | Using index |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)mysql> |
3 聚集子查询消除技术 --> 不支持
#MySQL认为，聚集子查询，只需要执行一次，得到结果后，即可把结果缓冲到内存中供后续连接或过滤等操作使用，没有必要消除掉子查询。
#另外，如果聚集子查询在索引列上执行，则会更快得到查询结果，更加能加速查询速度。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> explain extended select * from t1 where t1.a1>(select min(t2.a2) from t2);+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10362 | 33.33 | Using where || 2 | SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10157 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)mysql> explain extended select * from pt1 where pt1.a1>(select min(pt2.a2) from pt2);+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+| 1 | PRIMARY | pt1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5000 | 100.00 | Using where || 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+2 rows in set, 2 warnings (0.00 sec)mysql> |
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）