1、MySQL SQL執(zhí)行計(jì)劃分析與優(yōu)化方案技術(shù)創(chuàng)新 變革未來(lái)讀懂最簡(jiǎn)單的SQL“聰明的” SQL優(yōu)化器Join 方式子查詢(xún)視圖有”缺陷的” MySQL優(yōu)化器如何糾正CONTENTS讀懂最簡(jiǎn)單的SQL深入學(xué)習(xí)的基礎(chǔ).一個(gè)最簡(jiǎn)單的SQL的執(zhí)行計(jì)劃但含有豐富的信息. 1.1 范圍查找range與等值查找ref5mysql explain select * fromscore where sid=0 andsid explain select * fromscore where sid=1 G* 1. row * id: 1select_type: SIMPLE table: scorepartitio

2、ns: NULL type: refpossible_keys: sidkey: sid key_len: 5ref: const rows: 13filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)對(duì)比觀察：Key相同Type不同6mysql explain select * fromscore where sid=2 andsid explainselect sid from scorewhere sid in (7,6) G* 1. row * id: 1select_type: SIMPLEtable: score

3、 partitions: NULLtype: rangepossible_keys: sidkey: sid key_len: 5ref: NULL rows: 26filtered: 100.00Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) 1.2 范圍查找range與全表掃描all為什么where 條件為sid in(7,6)是范圍查找？而sid=1的查詢(xún)?yōu)閞ef查找？7mysql explain select sid fromscoreG* 1. row * id: 1select_type: SIM

4、PLEtable: score partitions: NULLtype: indexpossible_keys: NULL key: sidkey_len: 10 ref: NULL rows: 91filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec) 1.3 全索引查找index“聰明的”SQL優(yōu)化器優(yōu)化器到底有多聰明？為什么可以這么聰明？ 2.1 為什么改變查找方式?9mysql explain select * fromscore where sid=0 andsid=1G* 1. row *id:

5、1 select_type: SIMPLEtable: score partitions: NULLtype: rangepossible_keys: sidkey: sidkey_len: 5 ref: NULL rows: 13filtered: 100.00Extra: Using index condition1 row in set, 1 warning (0.01 sec)sid explain select * fromscore where sid=2 andG* 1. row * id: 1select_type: SIMPLEtable: score partitions:

6、 NULLtype: ALL possible_keys: sidkey: NULLkey_len: NULL ref: NULL rows: 91filtered: 28.57Extra: Using where1 row in set, 1 warning (0.00 sec) 2.1.1 改變查找方式（執(zhí)行計(jì)劃）原因10因?yàn)閮?yōu)化器認(rèn)為走全表掃描比走索引查找的成本更低正確！但成本如何預(yù)算？執(zhí)行計(jì)劃會(huì)浮動(dòng)？同一個(gè)SQL，在不同環(huán)境或者同一環(huán)境卻發(fā)生變化？SQL優(yōu)化器有點(diǎn)”個(gè)性，隨意”。 但計(jì)算機(jī)里的邏輯都是客觀的。記下這個(gè)疑問(wèn)后面待續(xù) 2.2 未執(zhí)行就”知道”rows的值?11mysql e

7、xplainselect sid from scorewhere sid in (7,6) G* 1. row *id: 1 select_type: SIMPLEtable: score partitions: NULLtype: rangepossible_keys: sidkey: sidkey_len: 5 ref: NULL rows: 26filtered: 100.00Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)mysql explainselect sid from scorewhere si

8、d in (7,8) G* 1. row * id: 1select_type: SIMPLEtable: score partitions: NULLtype: range possible_keys: sidkey: sidkey_len: 5 ref: NULL rows: 14filtered: 100.00Extra: Using where; Using index 1 row in set, 1 warning (0.01 sec)rows的值如何計(jì)算的？準(zhǔn)嗎？ 2.3 未執(zhí)行就”知道”filtered的值?12mysql explain select * fromscore w

9、here sid=2 and score50 G* 1. row *id: 1 select_type: SIMPLEtable: score partitions: NULLtype: refpossible_keys: sid,idx_score key: sidkey_len: 5 ref: const rows: 13filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)mysql explain select * fromscore where sid=2 and score80G* 1. row *

10、 id: 1select_type: SIMPLE table: scorepartitions: NULL type: refpossible_keys: sid,idx_scorekey: sid key_len: 5ref: const rows: 13filtered: 28.57Extra: Using where1 row in set, 1 warning (0.00 sec)Filtered值準(zhǔn)嗎？13mysql explain select * fromscore where sid=6 and score80 G* 1. row *id: 1 select_type: SI

11、MPLEtable: score partitions: NULLtype: refpossible_keys: sid,idx_score key: sidkey_len: 5 ref: const rows: 13filtered: 28.57Extra: Using where1 row in set, 1 warning (0.00 sec)mysql explain select * fromscore where sid=2 and score80G* 1. row * id: 1select_type: SIMPLE table: scorepartitions: NULL ty

12、pe: refPossible_keys: sid,idx_scorekey: sid key_len: 5ref: const rows: 13filtered: 28.57Extra: Using where1 row in set, 1 warning (0.00 sec)Filtered值為什么是28.57？返回結(jié)果行數(shù)=13*28.57% ? 2.4 如何計(jì)算filtered值14filtered (JSON name: filtered)The filtered column indicates an estimated percentage of table rows that

13、will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows filtered/ 100 shows the number of rows that will be joined with previous tables.官方手冊(cè)上僅介紹filtered值來(lái)自 估算。如何估算？calculate_condition_filter函數(shù)范圍優(yōu)化器是否已對(duì)執(zhí)行計(jì)劃所選訪問(wèn)方法未使 用的謂詞進(jìn)行行估計(jì)，如果是，則范圍優(yōu)化器的 估計(jì)值將用作這些字段的過(guò)

14、濾效果的計(jì)算。這樣做是因?yàn)榉秶鷥?yōu)化器比索引統(tǒng)計(jì)更準(zhǔn)確。const float selectivity=static_cast(table-quick_rowskeyno) / static_cast(tab-records();filter*= std:min(selectivity, 1.0f);Quick_rowkeyno的值來(lái)自于優(yōu)化器對(duì)where條件中列進(jìn)行范圍查找 時(shí)預(yù)計(jì)將輸出多少行。具體值來(lái)自函數(shù)check_quick_select是/*If the range optimizer has made rowestimates for predicates thatare not u

15、sedby the chosen access method, the estimate from the range optimizer is used as filtering effect for those fields. We do this because the range optimizer is more accurate thanindexstatistics.*/ 2.4.2 filtered值的計(jì)算邏輯/*Get filtering effect for predicates that are not already reflected in filter. The b

16、elow call gets this filtering effect based on index statistics and guesstimates.*/filter*=tab-join()-where_cond-get_filtering_effect(tab-table_ref-map(),used_tables, &table-tmp_set,static_cast(tab-records();因此,filtered值計(jì)算不一定來(lái)自索引統(tǒng)計(jì)信息，也可能來(lái) 自范圍優(yōu)化器對(duì)滿(mǎn)足該范圍的行數(shù)的預(yù)估。范圍優(yōu)化器比 索引統(tǒng)計(jì)更準(zhǔn)確。 2.4.3 filtered計(jì)算案例分析17mysql

17、 explain select * from score where sid=2 and score80 G* 1. row *id: 1select_type: SIMPLE table: scorepartitions: NULL type: refpossible_keys: sid,idx_score key: sidkey_len: 5ref: const rows: 13filtered: 28.57Extra: Using where1 row in set, 1 warning (7 min 15.94 sec)mysql select count(*) from score

18、where score80;+-+| count(*) |+-+|26 |+-+1 row in set (25.27 sec)mysql select count(*) from score ;+-+| count(*) |+-+|91 |+-+1 row in set (5.06 sec)26/91=0.2857142. 2.4.4 filtered計(jì)算調(diào)試18 2.4.4 filtered計(jì)算調(diào)試1920 2.5 為什么改變查找方式（回顧）mysql explain select * fromscore where sid=0 andsid=1G* 1. row *id: 1 selec

19、t_type: SIMPLEtable: score partitions: NULLtype: rangepossible_keys: sidkey: sidkey_len: 5 ref: NULL rows: 13filtered: 100.00Extra: Using index condition1 row in set, 1 warning (0.01 sec)sid explain select * fromscore where sid=2 andG* 1. row * id: 1select_type: SIMPLEtable: score partitions: NULLty

20、pe: ALL possible_keys: sidkey: NULLkey_len: NULL ref: NULL rows: 91filtered: 28.57Extra: Using where1 row in set, 1 warning (0.00 sec)21 2.5.1 執(zhí)行計(jì)劃計(jì)算成本/* purecov: inspected */if (!records) records+;double scan_time=cost_model-row_evaluate_cost(static_cast(records) + 1; Cost_estimate cost_est= head-f

21、ile-table_scan_cost(); cost_est.add_io(1.1);cost_est.add_cpu(scan_time);1. 計(jì)算全表掃描時(shí)的成本：(gdb) p cost_est$31 = io_cost = 2.1000000000000001, cpu_cost = 19.199999999999999, import_cost = 0, mem_cost = 0(gdb) p (0.20000000000000001*91)+1$12 = 19.199999999999999select count(*) from score結(jié)果為91(gdb) p *cost

22、$53 = io_cost = 27, cpu_cost = 5.21, import_cost = 0, mem_cost = 0 (gdb) p 26*0.20000000000000001+0.01$55 = 5.21*cost= read_cost(keyno, static_cast(n_ranges), static_cast(total_rows);cost-add_cpu(cost_model-row_evaluate_cost( static_cast(total_rows) + 0.01);*cost= read_cost(keyno, static_cast(n_rang

23、es), static_cast(total_rows);cost-add_cpu(cost_model-row_evaluate_cost( static_cast(total_rows) + 0.01);2. 計(jì)算索引查找時(shí)的成本：select count(*) fromscore where sid=2 andsid explain select * fromscore where sid=0 andsid=0 andsid explain select ,sc.cid,sc.score- from student s , score sc where sc.sid=s.id

24、 G* 1. row *id: 1 select_type: SIMPLEtable: s partitions: NULLtype: ALLpossible_keys: PRIMARY key: NULLkey_len: NULL ref: NULL rows: 21filtered: 100.00 Extra: NULL* 2. row * id: 1select_type: SIMPLEtable: sc partitions: NULLtype: ref possible_keys: sidkey: sidkey_len: 5ref: xcytest.s.idrows: 12filte

25、red: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)mysql alter table score drop key sid; mysql alter table student drop primary key ; mysql explain select ,sc.cid,sc.score- from student s , score sc where sc.sid=s.id G* 1. row *id: 1 select_type: SIMPLEtable: spartitions: NULL type: ALL

26、possible_keys: NULL key: NULLkey_len: NULLref: NULL rows: 21filtered: 100.00 Extra: NULL* 2. row * id: 1select_type: SIMPLEtable: sc partitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULL ref: NULL rows: 91filtered: 10.00Extra: Using where; Using join buffer (Block Nested Loop)2 rows in

27、set, 1 warning (0.01 sec)第一個(gè)表訪問(wèn)方式不變(dont care) ，第二個(gè)表的訪問(wèn)方式從ref變成了all,然 后join方式變成了block nested loop有沒(méi)有遇到過(guò)關(guān)聯(lián)中的內(nèi)表走索引 查找有時(shí)可能比全表掃描慢,而且可 能是慢非常多的情況？子查詢(xún)可能有曾聽(tīng)說(shuō)過(guò)在MySQL上不建議使用子查詢(xún)可曾試想過(guò)MySQL對(duì)子查詢(xún)是如何處理的，通過(guò)嵌套方式層層處理？但不是所有的子查詢(xún)是真正的子查詢(xún). 4子查詢(xún)優(yōu)化方式29The MySQL query optimizer has different strategies available to evaluate su

28、bqueries:For IN (or =ANY) subqueries, the optimizer has these choices: Semi-joinMaterialization EXISTS strategyFor NOT IN (or ALL) subqueries, the optimizer has these choices: MaterializationEXISTS strategyFor derived tables, the optimizer has these choices (which also apply to view references): Mer

29、ge the derived table into the outer query blockMaterialize the derived table to an internal temporary table內(nèi)容來(lái)自mysql 5.7refman30 4.1 子查詢(xún)變成內(nèi)連接mysql explainselect * from student whereidin ( select sid from score where cid=2) G* 1. row *id: 1 select_type: SIMPLEtable: scorepartitions: NULL type: indexp

30、ossible_keys: sidkey: sid key_len: 10ref: NULL rows: 91filtered: 10.00Extra: Using where; Using index* 2. row * id: 1select_type: SIMPLEtable: student partitions: NULLtype: eq_refpossible_keys: PRIMARY key: PRIMARYkey_len: 4ref: xcytest.score.sid rows: 1filtered: 100.00 Extra: NULL2 rows in set, 1 w

31、arning (0.03 sec)mysql show warnings;select xcytest.student.id AS id, AS namefrom xcytest.score join xcytest.studentwhere (xcytest.student.id = xcytest.score.sid) and (xcytest.score.cid = 2)為什么這個(gè)子查詢(xún)可以轉(zhuǎn)變成了內(nèi)連接？ 轉(zhuǎn)換成內(nèi)連接的好處是什么？31 4.1 子查詢(xún)變成內(nèi)連接mysql explainselect sid,cid,score fromscor

32、e sc wheresc.sid in ( selectid from student) and sc.score 60 G* 1. row * id: 1select_type: SIMPLEtable: student partitions: NULLtype: index possible_keys: PRIMARYkey: PRIMARYkey_len: 4 ref: NULLrows: 21filtered: 100.00Extra: Using index* 2. row *id: 1 select_type: SIMPLEtable: scpartitions: NULL typ

33、e: refpossible_keys: sid,idx_scorekey: sid key_len: 5ref: xcytest.student.id rows: 13filtered: 100.00Extra: Using wheremysql show warnings;select xcytest.sc.sid AS sid,xcytest.sc.cid AS cid,xcytest.sc.score AS score“from xcytest.student join xcytest.score sc“where (xcytest.sc.sid = xcytest.student.i

34、d)and (xcytest.sc.score 60)為什么這個(gè)子查詢(xún)也轉(zhuǎn)變成了內(nèi)連接？子查詢(xún)轉(zhuǎn)換成半連接條件321011DBUG_PRINT(info, (Checking if subq can be converted to semi-join);1012/*1013Check if were in subquery that is a candidate for flattening into a1014semi-join (which is done in flatten_subqueries(). The requirements are:10151. Subquery predi

35、cate is an IN/=ANY subquery predicate10162. Subquery is a single SELECT (not a UNION)10173. Subquery does not have GROUP BY10184. Subquery does not use aggregate functions or HAVING10195. Subquery predicate is (a) in an ON/WHERE clause, and (b) at1020the AND-top-level of that clause.10216. Parent qu

36、ery block accepts semijoins (i.e we are not in a subquery of1022a single table UPDATE/DELETE (TODO: We should handle this at some1023point by switching to multi-table UPDATE/DELETE)10247. Were not in a confluent table-less subquery, like SELECT 1.10258. No execution method was already chosen (by a p

37、repared statement)10269. Parent select is not a confluent table-less select102710. Neither parent nor child select have STRAIGHT_JOIN option.1028*/子查詢(xún)轉(zhuǎn)換成半連接條件代碼1029if (semijoin_enabled(thd) &1030in_predicate &/ 11031!is_part_of_union() &/ 21032!group_list.elements &/ 31033!m_having_cond & !with_sum_

38、func &/ 41034(outer-resolve_place = st_select_lex:RESOLVE_CONDITION |/ 5a1035outer-resolve_place = st_select_lex:RESOLVE_JOIN_NEST) &/ 5a1036!outer-semijoin_disallowed &/ 5b1037outer-sj_candidates &/ 61038leaf_table_count &/ 71039in_predicate-exec_method =1040Item_exists_subselect:EXEC_UNSPECIFIED &

39、/ 81041outer-leaf_table_count &/ 91042!(active_options() | outer-active_options() &104310441045SELECT_STRAIGHT_JOIN)/10DBUG_PRINT(info, (Subquery is semi-join conversion candidate);半連接進(jìn)一步轉(zhuǎn)換為內(nèi)連接/*Pull tables out of semi-join nests based on functional dependenciesparam join The join where to do the se

40、mi-join table pulloutreturn False if successful, true if error (Out of memory)detailsPull tables out of semi-join nests based on functional dependencies, ie. if a table is accessed via eq_ref(outer_tables).The function may be called several times, the caller is responsible for setting up proper key

41、information that this function acts upon.NOTETable pullout may make uncorrelated subquery correlated. Consider this example:. WHERE oe IN (SELECT it1.primary_key WHERE p(it1, it2) . )here table it1 can be pulled out (we have it1.primary_key=oe which gives us functional dependency).Making the subquer

42、y (i.e. its semi-join nest) correlated prevents us fromusing Materialization or LooseScan to execute it. */static bool pull_out_semijoin_tables(JOIN *join) 4.2 利用MATERIALIZED處理半連接35mysql explain score) G* 1. row * id: 1select_type: SIMPLE table: studentpartitions: NULL type: ALLPossible_keys: PRIMAR

43、Y key: NULLkey_len: NULL ref: NULL rows: 21filtered: 100.00 Extra: Using where* 2. row * id: 1select_type: SIMPLEtable: partitions: NULLtype: eq_ref possible_keys: key: key_len: 5ref: xcytest.student.id rows: 1filtered: 100.00 Extra: NULL* 3. row * id: 2select_type: MATERIALIZED table: scorepartitio

44、ns: NULLtype: index possible_keys: sidkey: sid key_len: 10ref: NULLselect* from student whereidin( select sid frommysql show warnings;select xcytest.student.id AS id, AS namefrom xcytest.student semi join (“xcytest.score)where (.sid =xcytest.student.id)通過(guò)MATERIALIZED的方式對(duì)子查詢(xún)生成臨時(shí)表

45、,然后跟外表進(jìn)行1vs1等 值連接eq_ref。 4.3 利用loosescan處理半連接36mysql explain select * from xcytestwherea in( selectbfrom xcytestb) ;+-+-+-+-+-+-+-+-+-+-+-+-+| id | select_type | table filtered | Extra| partitions | type| possible_keys | key| key_len | ref| rows |+-+-+-+-+-+-+-+-+-+-+-+-+|1 | SIMPLE| xcytestb | NULL

46、| Using index; LooseScan| index | b| b| 5| NULL |2 |100.00|1 | SIMPLE| xcytest| NULL| ALL| PRIMARY| NULL | NULL| NULL |2|50.00 | Using where; Using join buffer (Block Nested Loop) |+-+-+-+-+-+-+-+-+-+-+-+-+ 4.4 其他處理半連接策略37the firstmatch, loosescan, duplicateweedout, and materialization flags enable

47、finer control over the permitted semi-join strategies-請(qǐng)參考mysql 5.7 refman38 4.5 非常特殊的“子查詢(xún)”mysql explain select * from testa a where a.id in (selectb.id from testb b where b.id=100);+-+-+-+-+-+-+-+-+-+-+-+-+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | fi

48、ltered | Extra|+-+-+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE| NULL | NULL| NULL | NULL| NULL | NULL| NULL | NULL |NULL | nomatching row in const table |+-+-+-+-+-+-+-+-+-+-+-+-+1 row in set, 1 warning (0.00 sec)在分析階段就知道了這個(gè)查詢(xún)將為空？ 4.6 常量表轉(zhuǎn)換39(gdb) bt#0 ha_innobase:index_read (this=0 x7f067c058d00, buf=0 x7f06

49、7c059110 377, key_ptr=0 x7f067c06e930 004, key_len=4, find_flag=HA_READ_KEY_EXACT)#1 0 x0000000000f9fba4 in handler:index_read_map (this=0 x7f067c058d00, buf=0 x7f067c059110 377,key=0 x7f067c06e930 004, keypart_map=1, find_flag=HA_READ_KEY_EXACT)#4 0 x000000000154eb1b in read_const (table=0 x7f067c0

50、58350, ref=0 x7f067c073448)#5 0 x000000000154e5fe in join_read_const_table (tab=0 x7f067c0732e0, pos=0 x7f067c0735c0).#7 0 x0000000001579ad5 in JOIN:make_join_plan (this=0 x7f067c072d40)#8 0 x000000000156e576 in JOIN:optimize (this=0 x7f067c072d40)#9 0 x00000000015e5ee6 in st_select_lex:optimize (th

51、is=0 x7f067c053f70, thd=0 x7f067c01bf40) #100 x00000000015e4642 in handle_query (thd=0 x7f067c01bf40, lex=0 x7f067c01e238, result=0 x7f067c06dc38, added_options=0, removed_options=0) at /mysqldata/mysql- 5.7.18/sql/sql_select.cc:16440 4.7 必須手工優(yōu)化的子查詢(xún)select * from( select idfrom testaunionselect id fr

52、om testb)out_a_b where out_a_b.id=8;select * from( select idfrom testaunionall select id from testb)out_a_b where out_a_b.id=8;無(wú)法轉(zhuǎn)化成半連接的子查詢(xún)例如：。 4.7.1 手工優(yōu)化子查詢(xún)方法411.將子查詢(xún)上拉，將子查詢(xún)跟外層表平級(jí),將 嵌套查詢(xún)變成關(guān)聯(lián)查詢(xún)。 -消除子查詢(xún)2. 將查詢(xún)的條件下沉，使子查詢(xún)能夠快 速執(zhí)行且生成的結(jié)果集變小。-快速物 理化子查詢(xún)視圖/派生表采用跟外表合并的方式優(yōu)化謹(jǐn)慎使用包含union/union all的視圖/派生表 5.1 視圖/派生

53、表的優(yōu)化方式43For derived tables, the optimizer has these choices (which also apply to view references):Merge the derived table into the outer query block Materialize the derived table to an internal temporary table 5.2 視圖/派生表優(yōu)化函數(shù)442226/*2227Merge a derived table or view into a query block.2228 If some co

54、nstraint prevents the derived table from being merged then do 2229 nothing, which means the table will be prepared for materialization later. 22302231After this call, check is_merged() to see if the table was really merged. 22322233param thdThread handler2234param derived_table Derived table which i

55、s to be merged. 22352236return false if successful, true if error 2237*/22382239bool SELECT_LEX:merge_derived(THD *thd, TABLE_LIST *derived_table) 22402241DBUG_ENTER(SELECT_LEX:merge_derived);22422243if (!derived_table-is_view_or_derived() | derived_table-is_merged() 2244DBUG_RETURN(false);22452246S

56、ELECT_LEX_UNIT *const derived_unit= derived_table-derived_unit(); 22472248/ A derived table must be prepared before we can merge it 2249DBUG_ASSERT(derived_unit-is_prepared();22502251LEX *const lex= parent_lex; 22522253/ Check whether the outer query allows merged views2254if (master_unit() = lex-un

57、it & 。bool st_select_lex_unit:is_mergeable() constif (is_union()return false;SELECT_LEX *const select= first_select(); Item *item;List_iterator it(select-fields_list);while (item= it+)if (item-has_subquery() & item-used_tables() return false;return !select-is_grouped() &!select-having_cond() &!selec

58、t-is_distinct() &select-table_list.elements 0 &!select-has_limit() &thd-lex-set_var_list.elements = 0;5.3 派生表/視圖能合并的前提條件create view v_student_scoreas selectname,sid,cid,scorefrom score sc,student stwhere st.id=sc.sid;僅對(duì)視圖查詢(xún)：select * fromv_student_score where name=xu;視圖跟另外一個(gè)表joinselect ,vsc.s

59、id,, vsc.scorefromv_student_score vsc , coursec where =xuand vsc.cid=c.id ;5.4 視圖的good案例mysql explain select ,vsc.sid,, vsc.score- from v_student_score vsc , course c where =xuand vsc.cid=c.id ;+-+-+-+-+-+-+-+-+-+-+-+-+| id | select_type | table | partitions | typ

60、e | possible_keys | key | key_len | ref| rows | filtered | Extra|+-+-+-+-+-+-+-+-+-+-+-+-+| 1 | SIMPLE| 1 | SIMPLENested Loop) | 1 | SIMPLE| st| NULL| ALL | PRIMARY| NULL | NULL| NULL|21 |10.00 | Using where| c| NULL| ALL | PRIMARY| NULL | NULL| NULL|13 |100.00 | Using join buffer (Block| sc| NULL|