mysql query plan_MySQL Execution Plan--IN查询计划(2)

最新推荐文章于 2024-04-02 23:58:41 发布
最新推荐文章于 2024-04-02 23:58:41 发布
阅读量292 收藏
点赞数
文章标签： mysql query plan
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29469195/article/details/113251849
版权

在MySQL中，IN查找经常出现性能问题，相同SQL在MySQL不同版本中表现不同。

准备测试数据：

## 创建表tb001CREATE TABLEtb001(

idINT unsigned NOT NULLAUTO_INCREMENT,

cidINT unsigned NOT NULL DEFAULT 0,

c1VARCHAR(50) NOT NULL DEFAULT '',

c2VARCHAR(50) NOT NULL DEFAULT '',

c3VARCHAR(50) NOT NULL DEFAULT '',

c4VARCHAR(50) NOT NULL DEFAULT '',

c5VARCHAR(50) NOT NULL DEFAULT '',

c6VARCHAR(50) NOT NULL DEFAULT '',PRIMARY KEY(id),INDEXidx_cid(cid)

);

## 第一次插入数据INSERT INTOtb001select NULL,FLOOR(RAND() * 1000000),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50)frominformation_schema.COLUMNS;

## 循环执行多次，使得tb001中包含百万数据INSERT INTOtb001select NULL,FLOOR(RAND() * 1000000),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50),

REPEAT('a', 50)FROMtb001;

## 创建表tb002CREATE TABLEtb002(

idint NOT NULL AUTO_INCREMENT primary key,

cidint)

## 向表中插入10条数据,cid值分散INSERT INTOtb002(cid)SELECT cid FROMtb001order by id descLIMIT10

表tb0001中包含上百万数据，表tb002中包含10条数据。

================================================================================

测试SQL 1：

SELECT *

FROMtb001WHERE cid IN(SELECT cid FROMtb002

);

MySQL 5.5.14版本执行计划为：

## MySQL 5.5.14版本执行计划+----+--------------------+-------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+-------+------+---------------+------+---------+------+---------+-------------+

| 1 | PRIMARY | tb001 | ALL | NULL | NULL | NULL | NULL | 4080170 | Using where |

| 2 | DEPENDENT SUBQUERY | tb002 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |

+----+--------------------+-------+------+---------------+------+---------+------+---------+-------------+

MySQL 5.7.24版本执行计划为：

## MySQL 5.7.24版本+----+--------------+-------------+------------+------+---------------+---------+---------+-----------------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------+-------------+------------+------+---------------+---------+---------+-----------------+------+----------+-----------------------+

| 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |

| 1 | SIMPLE | tb001 | NULL | ref | idx_cid | idx_cid | 4 | .cid | 5 | 100.00 | Using index condition |

| 2 | MATERIALIZED | tb002 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |

+----+--------------+-------------+------------+------+---------------+---------+---------+-----------------+------+----------+-----------------------+

在MySQL 5.7.24版本使用FORMAT=JOSN查看执行计划：

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "80.25"},"nested_loop": [

{"table": {"table_name": "","access_type": "ALL","attached_condition": "(``.`cid` is not null)","materialized_from_subquery": {"using_temporary_table": true,"query_block": {"table": {"table_name": "tb002","access_type": "ALL","rows_examined_per_scan": 10,"rows_produced_per_join": 10,"filtered": "100.00","cost_info": {"read_cost": "1.00","eval_cost": "2.00","prefix_cost": "3.00","data_read_per_join": "160"},"used_columns": ["cid"]

}

}

}

}

},

{"table": {"table_name": "tb001","access_type": "ref","possible_keys": ["idx_cid"],"key": "idx_cid","used_key_parts": ["cid"],"key_length": "4","ref": [".cid"],"rows_examined_per_scan": 5,"rows_produced_per_join": 5,"filtered": "100.00","index_condition": "(`demodb`.`tb001`.`cid` = ``.`cid`)","cost_info": {"read_cost": "59.37","eval_cost": "1.19","prefix_cost": "80.25","data_read_per_join": "5K"},"used_columns": ["id","cid","c1","c2","c3","c4","c5","c6"]

}

}

]

}

}

在MySQL 5.5.14版本中，循环遍历tb001表中每行记录去做IN条件判断，执行时间超过5分钟

在MySQL 5.7.24版本中，会将IN条件中数据固化(materialized_from_subquery)形成派生表subquery2，并且推断出cid is not null，再循环遍历subquery2中每条记录，去tb001中按照cid列上进行INDEX SEEK,查询执行低于10ms

================================================================================

测试SQL2:

将tb002中数据显示放入到IN列表中，最终SQL为：

SELECT *

FROMtb001WHERE cid IN(116672,660886,729254,328461,971017,508875,524453,704463,332621,986215)

MySQL 5.5.14版本执行计划为：

## MySQL 5.5.14版本+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | tb001 | range | idx_cid | idx_cid | 4 | NULL | 70 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

MySQL 5.7.24版本执行计划为：

## MySQL 5.7.24版本+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

| 1 | SIMPLE | tb001 | NULL | range | idx_cid | idx_cid | 4 | NULL | 67 | 100.00 | Using index condition |

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

排除MySQL 5.7版本中新增加的partitions和filtered两列，两个版本执行计划相同，执行时间类似，均低于10ms。

两个版本上使用PROFILING工具查看，执行消耗类似，主要消耗在Sending data部分。

+----------------------+----------+----------+------------+--------------+---------------+-------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |

+----------------------+----------+----------+------------+--------------+---------------+-------+

| starting | 0.000067 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| checking permissions | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| Opening tables | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| init | 0.000037 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| System lock | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| optimizing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| statistics | 0.000211 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| preparing | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| Sending data | 0.000863 | 0.000999 | 0.000000 | 0 | 0 | 0 |

| end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| query end | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| freeing items | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | 0 |

| cleaning up | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | 0 |

+----------------------+----------+----------+------------+--------------+---------------+-------+

================================================================================

测试SQL3: 将IN查询中的值数量升级到1000个

SELECT *

FROMtb001WHERE cid IN(116672,660886,729254,328461,971017,508875,524453,704463,332621,986215,866151,114847,236027,355097,179820,848000,20061,750768,577927,46289,884506,125414,247522,370732,202046,546500,510151,334752,54537,979311,280673,141351,808694,634040,787169,767269,683058,549341,21216,852246,413339,738149,759136,352510,1139,217728,695834,753814,24412,122749,529778,175380,782375,912165,592817,350640,386794,861110,972919,1162,10964,17887,692823,815410,128075,274989,82291,378821,937272,102514,191765,952105,856253,109720,564116,236953,873550,710266,225927,139790,491008,105084,344804,872979,6697,720169,864716,201138,46991,677939,780901,742190,310016,269296,345489,152716,139365,181369,255827,365922,191261,196795,264238,374799,536585,769376,770099,360373,343922,453079,973825,472015,128750,57246,385826,623962,577995,755338,66232,858607,75601,506466,485577,973807,461152,443985,919591,51324,445677,59020,283141,452228,326000,392591,212319,556335,856452,575308,6696,486058,807865,517756,593638,150145,719029,148334,7547,296467,491596,387171,481420,6643,850599,415450,408884,876324,657578,470523,898345,340401,934554,444123,762021,788661,393761,60048,968827,928029,557106,952500,735131,907719,267822,810464,594832,721233,337111,988107,201142,339201,164954,828523,659221,489194,928916,51796,776687,943881,314239,875771,21539,410642,10557,880715,464339,146994,756877,879131,546259,617388,846894,872361,504970,550830,83060,954824,897020,510092,551147,415215,174976,486556,419268,536594,421235,856526,620466,415268,151958,114842,140750,862970,58033,142398,217986,315845,453746,317628,44799,302122,488604,380299,137631,769285,902931,581791,335341,364971,163172,483630,84993,980445,939078,238315,84166,60838,73342,101958,812149,319595,261943,996707,240530,401982,589793,515662,662839,599511,778239,430561,458189,953461,103039,520579,833285,881949,192153,359606,535292,438951,219822,886719,71259,156525,903788,787472,677858,533940,997280,484154,141164,407221,631648,250340,206684,594360,588372,115834,663600,67388,224447,18283,955656,253332,103668,421670,1561,528711,547332,735983,44194,161663,588041,926180,14287,381752,631491,591901,109338,651597,382883,754272,129493,297731,119721,433624,16134,549015,242980,888629,66722,814994,669416,844029,183576,691284,90948,254486,727160,793772,413900,91910,681883,105835,282431,435440,298257,685281,709383,953878,353336,270935,333779,465988,998720,509204,515767,689877,977873,66495,784868,952616,659404,448134,860423,617993,743182,365972,362226,815053,904346,902227,535596,258584,919108,778986,991941,393573,658641,633211,169139,404161,283889,671761,358306,706590,391548,83717,9012,320448,864410,667200,157197,372929,902483,46495,443331,56720,613423,3466,801302,1015,734591,223981,461771,28291,893228,812617,389953,379049,835098,265480,164101,851675,698091,212927,455515,688217,661856,207022,791084,916134,929861,244391,430698,636367,250379,871843,702945,880961,900620,207381,712094,784364,736575,966171,698093,508098,280800,965300,848567,92512,871331,88049,522971,175602,118346,472043,80623,310358,490389,47196,581863,620707,507224,971805,783933,315954,598613,225703,843301,330268,936013,116599,215661,987235,573506,960098,742328,583847,725637,14779,782753,632995,794074,112459,589407,944859,44963,759985,584987,388317,785534,846450,148906,299360,449298,315711,43085,299906,152148,153611,538636,179857,786883,584852,712460,992482,83318,808035,318953,595577,886311,278659,835662,966550,332363,231755,275471,48533,322399,187820,205567,613339,620376,496181,953509,90456,832923,691387,541337,671766,605831,119130,663274,732413,332930,310747,73601,352420,229499,265026,31876,964786,264017,451767,331606,735422,448501,169028,465546,52902,266321,759568,843839,486543,196292,831639,320635,257178,655086,339417,680667,354639,697510,44304,174670,276348,402357,245147,607792,597938,793966,123964,941952,528598,233680,546601,833108,872981,510598,560004,429807,196344,850638,352283,303592,55226,990846,633002,58054,885757,123577,244043,698879,750118,17238,112023,180960,597592,602334,138026,29265,669433,439301,842357,590828,370595,754049,799883,748456,647466,162958,55774,371971,934694,433834,411492,207875,542628,501755,655383,591720,767739,82326,547963,219164,515952,120199,695015,784959,743260,643780,491880,605207,844718,872795,173086,664212,382658,749693,455269,175551,807496,953976,987462,661112,106032,289684,780795,871431,192121,29724,318833,521481,429524,208714,871348,716009,902899,932829,587082,861,805741,342614,635777,278757,252670,661531,351662,365593,825435,920527,925733,569380,640389,544189,693518,974468,392940,996745,530261,884854,631337,757277,718349,278156,224144,947521,171155,636084,3089,793120,320837,270355,676980,704848,282421,235752,632113,308271,303081,69045,680715,778177,736723,635146,479670,254401,930779,799831,689459,215822,129883,144532,864259,725588,587894,737960,700636,255074,524911,456494,585854,157592,734035,170861,373424,898753,40114,100606,820973,1419,395375,991819,187906,649238,686094,260411,871987,699842,209075,781844,566715,17244,839836,393978,376685,829816,590799,708059,649832,847034,528447,265067,598963,576582,761316,743333,318450,616306,921026,996276,353451,590326,866060,246658,789077,899576,236268,760588,522224,950178,409555,365866,473258,142,842484,404093,725994,75292,550156,558496,414108,837348,257544,816791,942138,964633,293675,834582,959744,654713,771275,400553,77164,661756,77205,574757,932194,695404,514942,293102,316856,430394,278371,653820,663577,888021,925510,246510,102347,755075,224343,788880,906686,914850,555855,954808,739396,914057,943630,601364,687731,945763,680362,818682,471016,512133,29884,545762,566309,95261,969998,584910,984851,446449,415798,210847,212813,951073,180850,984393,247039,416934,612543,215261,487921,74781,295328,800620,569258,348556,724508,91329,910712,487410,842113,907719,565494,639918,211365,138969,161160,222277,316742,453811,317438,377793,190510,524918,478028,955074,765780,291276,563534,842131,215707,745468,170545,369085,891627,363017,641153,280917,587810,552712,391917,135624,762068,224905,120819,459731,725945,723331,296904,253730,323915,788616,873055,525553,251570,577433,211865,160630,863762,52728,799959,315689,592828,882818,273754,495317,734278,392024,408615,550350,114378,340514,529397,102158,327131,177832,520677,141850,729525,501926,260034,599416,579015,765219,415694,771084,146576,653932,707695,961514,366626,957205,139799,913931,476619,489721,990662,487864,816219,197744,724167,141031,993663,275761,795048,233159,790250,239364,135523,479417,753854,763285,687524,224778,145456,167500,564015,217525,841204,996165,379874,967736,819602,710091,243618,836000,312127,574879,520756,342967,398027,882389,671306,158047,372977,902279,712807,553919,18929,180242,960761,207854,26170,974332,281830,811606,592067,716243,663050,199669,111765,786386,606722,749075,379551,382893,844972,280656,186103,216620,298999,92791,721653,505158,934712,722802,893099,247564,781574,220102,106313,437154,359819)

测试SQL3执行情况与测试SQL2执行情况相近。

罗明灯他妈
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql-5.7.24-linux-glibc2.12-x86_64.tar.7z
04-15
在查询性能方面，MySQL 5.7.24引入了Query Execution Plan (QEP)的可视化功能，允许管理员和开发者通过EXPLAIN语句更直观地理解查询执行过程，从而优化查询效率。此外，查询缓存也得到了增强，可以更好地处理动态...
mysql.rar
06-03
5. **查询优化器改进**：MySQL 5.7的查询优化器得到了显著提升，例如，改进了 Cost-Based Optimizer (CBO) 和 Query Execution Plan (QEP)，能够更准确地估计查询成本，选择最优执行路径。 6. **安全增强**：MySQL ...
mysql query plan_MySQL Execution Plan--使用Query Rewrite
weixin_39996478的博客
01-19 113
/*Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.This program is free software; you can redistribute it and/or modifyit under the terms of the GNU General Public License as publ...
SQL Server Query Plan
USTBHacker的专栏
07-08 1054
SQL Server engine high-level architecture<br /><br />Query processing<br />
SQL查询计划（Query Plan）处理模型（processing model）
qq_41775852的博客
03-23 1399
https://www.iteblog.com/archives/9806.html
explain plan mysql_查看执行计划方法总结之一:explain plan命令
weixin_34404808的博客
02-23 395
查看执行计划的方法有很多种，笔者今后将在博客里一一为大家介绍。本文介绍的是第一种方法，利用explain plan命令来查看执行计划查看执行计划的方法有很多种，笔者今后将在博客里一一为大家介绍。本文介绍的是第一种方法，利用explain plan命令来查看执行计划。1.用hr用户登录数据库[Oracle@ENMOEDU ~]$ sqlplus hr/oracle@ENMOEDUSQL*Plus: ...
MySQL慢查询Explain Plan分析
乐杨俊浅谈LAMP
10-18 1147
MySQL慢查询Explain Plan分析
简述Mysql Explain 命令
09-09
它提供了一个查询执行计划（Query Execution Plan，QEP），展示了MySQL如何解析、优化并执行SQL语句，尤其是SELECT、DELETE、INSERT、REPLACE和UPDATE这些操作。通过理解EXPLAIN的输出，我们可以深入洞察查询性能...
mysql5.6.15win32 版本
02-10
- **Execution Plan**：提供了更详细的执行计划信息，帮助开发者优化查询语句。 4. **复制功能**： - **半同步复制**：增强了复制的可靠性，确保至少有一个从服务器接收到主服务器的更新后，主服务器才确认事务...
MySQL性能优化概述
01-13
#### Query Execution Plan (EXPLAIN) `EXPLAIN` 是一个非常有用的命令，它可以模拟MySQL查询优化器的工作过程，并返回详细的查询执行计划。通过`EXPLAIN`我们可以了解到以下信息： - 表的读取顺序。 - 执行的读...
MySQL执行计划
互联网知识分享
07-21 886
MySQL执行计划是数据库查询优化过程中的一部分，它是一个计划（plan）的集合，通过这个计划，MySQL可以选择最佳的执行方式来执行给定的查询语句。执行计划包含了查询的各个步骤、执行顺序、访问方式和查询所需的资源等信息。MySQL的执行计划由查询优化器生成，它会根据查询语句、表结构、索引等相关信息，尝试找到最佳的执行计划。优化器会考虑多种执行方式，并选择成本最低的执行计划作为最终的执行方式。通过执行计划，我们可以了解到查询的每个步骤和对应的成本，从而帮助我们优化查询的性能。
Mysql——》执行计划
小仙~
04-17 328
版权声明：本文为博主原创文章，无需授权即可转载，甚至无需保留以上版权声明，转载时请务必注明作者。 https://blog.csdn.net/weixin_43453386/article/details/89326727 Mysql——》执行计划一、执行计划的作用二、执行计划的语法1、生成执行计划——普通表——以表格格式显示输出2、生成执行计划——分区表——以表格格式显示输出3、生成执行计划——...
【MySQL系列】- MySQL执行计划
做一个有思想的技术人
09-26 3100
MySQL执行计划在面试中会经常问到，尤其是type字段会经常被问到。同时，了解执行计划在工作中也能更好的对SQL查询进行优化，所以执行计划是mysql学习过程中的一个必备技能，来充实一下自己的技能包。
mysql explain plan_MySQL Explain详解(转)
weixin_34619335的博客
02-07 654
explain SELECT a.* FROM test a,(select id from test where level_id <=4 order by aa_id limit 243000, 100) b where a.id=b.id ;因为延迟关联通过覆盖索引返回所需数据行的主键，再根据主键关联原表获得需要的数据，所以速度比之前快上不少。覆盖索引(只访问索引的查询，即查询只需要...
MySQL-执行计划、explain关键字查询执行计划以及explain案例分析
最新发布
小孔靠得住的博客
04-02 2062
MySQL 的执行计划（Execution Plan）是数据库查询优化器生成的一种指导性信息，它描述了 MySQL 执行查询时所采用的具体执行策略。执行计划通常由 MySQL 的查询优化器根据查询语句、表结构、索引等信息进行生成，并且用于指导 MySQL 数据库引擎执行查询操作。
MySQL Execution Plan--IN查询计划(2)
weixin_30325071的博客
04-16 98
在MySQL中，IN查找经常出现性能问题，相同SQL在MySQL不同版本中表现不同。 准备测试数据： ## 创建表tb001 CREATE TABLE tb001( id INT unsigned NOT NULL AUTO_INCREMENT, cid INT unsigned NOT NULL DEFAULT 0, c1 VARCHAR(50)...
MySQL Execution Plan--EXPLAIN用法
weixin_38169359的博客
04-17 549
MySQL Explain新用法： --使用EXPLAIN来查看语句的最终执行计划 语法：EXPLAIN [EXTENDED] SELECT select_options --在MYSQL 5.7版本后，可以查看正在执行的语句的执行计划 EXPLAIN FOR CONNECTION connection_id; --在MYSQL 5.7版本后，可以指定生成执...
Mysql执行计划-看这一篇就够了
GM_115的博客
03-10 8836
Mysql执行计划
关于 MYSQL EXPLAIN 执行计划详解
N-SOKACH的博客
01-08 542
explain的作用： explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 EXPLAIN SELECT ( SELECT c. NAME FROM fx_channel c WHERE c.
MySQL Execution Plan--IN子查询对UPDATE语句影响
05-20
在MySQL中，IN子查询可以用于UPDATE语句的WHERE子句中，以过滤要更新的数据行。这样做可能会对UPDATE语句的性能产生一定影响。 当UPDATE语句包含IN子查询时，MySQL会执行以下步骤： 1. 执行子查询，将结果保存在一...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值