在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執行情況相近。

總結

以上是生活随笔為你收集整理的mysql query plan_MySQL Execution Plan--IN查询计划(2)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。