MySQL Execution Plan--IN查询计划(2)
在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) 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执行情况相近。
