MySQL Execution Plan--IN查询计划(2)
在MySQL中，IN查找经常出现性能问题，相同SQL在MySQL不同版本中表现不同。
准备测试数据：
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。## 创建表tb001 CREATE TABLE tb001( id INT unsigned NOT NULL AUTO_INCREMENT, cid INT unsigned NOT NULL DEFAULT 0, c1 VARCHAR(50) NOT NULL DEFAULT '', c2 VARCHAR(50) NOT NULL DEFAULT '', c3 VARCHAR(50) NOT NULL DEFAULT '', c4 VARCHAR(50) NOT NULL DEFAULT '', c5 VARCHAR(50) NOT NULL DEFAULT '', c6 VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY(id), INDEX idx_cid(cid) ); ## 第一次插入数据 INSERT INTO tb001 select NULL, FLOOR(RAND() * 1000000), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50) from information_schema.COLUMNS; ## 循环执行多次，使得tb001中包含百万数据 INSERT INTO tb001 select NULL, FLOOR(RAND() * 1000000), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50), REPEAT('a', 50) FROM tb001; ## 创建表tb002 CREATE TABLE tb002( id int NOT NULL AUTO_INCREMENT primary key, cid int ) ## 向表中插入10条数据,cid值分散 INSERT INTO tb002(cid) SELECT cid FROM tb001 order by id desc LIMIT 10
表tb0001中包含上百万数据，表tb002中包含10条数据。
================================================================================
测试SQL 1：
SELECT * FROM tb001 WHERE cid IN( SELECT cid FROM tb002 );
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 | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where | | 1 | SIMPLE | tb001 | NULL | ref | idx_cid | idx_cid | 4 | <subquery2>.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": "<subquery2>", "access_type": "ALL", "attached_condition": "(`<subquery2>`.`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": [ "<subquery2>.cid" ], "rows_examined_per_scan": 5, "rows_produced_per_join": 5, "filtered": "100.00", "index_condition": "(`demodb`.`tb001`.`cid` = `<subquery2>`.`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 * FROM tb001 WHERE 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 * FROM tb001 WHERE 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执行情况相近。
更多精彩
