mysql 内部 临时表_MySQL内部临时表策略 - Mr.南柯 - 51Testing软件测试网 51Testing软件测试网-软件测试人的精神家园...

1H#dGTog4r5J6H0通过对源码的跟踪和调试，以及参考MySQL官方文档，对MySQL内部临时表使用策略进行整理，以便于更加深入的理解。#D4s,PXr9k_2S051Testing软件测试网$S:S7K1v:nEc

k9f:B!@

使用内部临时表条件!~.m'ih~

j0

A])r{L

N0MySQL内部临时表的使用有一定的策略，从源码中关于查询是否需要内部临时表。可以总结如下：;I#P(qG}N0^0

4N{b3G,YS usC01、DISTINCT查询，但是简单的DISTINCT查询，比如对primary key、unique key等DISTINCT查询时，查询优化器会将DISTINCT条件优化，去除DISTINCT条件，也不会创建临时表；$~i4?%j Sq7c']6Q051Testing软件测试网?m~V0w

2、不是第一个表的字段使用ORDER BY 或者GROUP BY；Re?:M(K%u"R051Testing软件测试网K[1RB%N)E9{oB

3、ORDER BY和GROUP BY使用不同的顺序；TRD~^)Z!vb6h0

?B3]wgL,H7K04、用户需要缓存结果；51Testing软件测试网bs!PJH6o;VSW6?eR.s

N:A+P&E9|05、ROLLUP查询。-[Z8]iz1Q,HiT051Testing软件测试网2?"|&ti q,axx;G

源码如下所示8L^n,nf)b0

b[f.I~F-?g0代码地址：sql_select.cc:854，函数：JOIN::optimize()，位置：sql_select.cc:1399R5]^6GGuk%Pz0/*

&_'o9W5Q{:V3\v0    Check if we need to create a temporary table.

![&Y&M*p$tA0    This has to be done if all tables are not already read (const tables)51Testing软件测试网b&_kZ'V/k!h~N

and one of the following conditions holds:

fw T

EXW!q+C5`h;G0    - We are using DISTINCT (simple distinct's are already optimized away)

a9jR3sF7_LZ0G#?0    - We are using an ORDER BY or GROUP BY on fields not in the first table51Testing软件测试网ZF&Ip:Z+~

- We are using different ORDER BY and GROUP BY orders

;ui\2[S1}/Lrp0    - The user wants us to buffer the result.

9tNfs{0    When the WITH ROLLUP modifier is present, we cannot skip temporary table51Testing软件测试网fPF6R4e8V

creation for the DISTINCT clause just because there are only const tables.

s7F7]C2ucD~y$p0  */51Testing软件测试网y$[?

d5kz

need_tmp= (( const_tables != tables &&51Testing软件测试网@4[ar;]!hm7G

(( select_distinct || !simple_order || !simple_group) ||51Testing软件测试网6K3EZg1N:dAH

( group_list && order ) ||

&Ex]ET`R9`@0i"NI%jE0               (select_options & OPTION_BUFFER_RESULT))) ||

)fi?z@!w3@0             ( rollup.state != ROLLUP:: STATE_NONE && select_distinct ));51Testing软件测试网_$a fh$[a V*N$B;p

内部临时表使用原则51Testing软件测试网!j"bc;cHx [H51Testing软件测试网v\:J?D#@Q4l@,^

但是使用了内部临时表，那么他是怎么存储的呢？原则是这样的：8~.{1fWK1t8m tx0

(gXK;X)i/OK]01、当查询结果较小的情况下，使用heap存储引擎进行存储。也就是说在内存中存储查询结果。51Testing软件测试网j_ BH&m

6h3B+q `?02、当查询结果较大的情况下，使用myisam存储引擎进行存储。51Testing软件测试网"W`S[(s)UZ,H

~Cp0q:};Ae03、当查询结果最初较小，但是不断增大的情况下，将会有从heap存储引擎转化为myisam存储引擎存储查询结果。51Testing软件测试网I'HLV6q

p9T4LBU*w51Testing软件测试网g&f9m.S:C;W6}

什么情况算是查询结果较小呢？从源码中if的几个参数可以看出：51Testing软件测试网D1C)E$]l

1Fn~0o] r1`.K01、有blob字段的情况；A9^;MUeW*ifhzQS051Testing软件测试网7EO]G+[+Ho&l

2、使用唯一限制的情况；51Testing软件测试网(Q7FV7^ v3j51Testing软件测试网w)f\k4j5i;U

3、当前表定义为大表的情况；Z R,e1eo*K4nFB,~051Testing软件测试网a0d;D*v4jK.{'@g(e

4、查询结果的选项为小结果集的情况；5j/Zb\fFT{051Testing软件测试网,s4p n

N K*M+}'b

5、查询结果的选项为强制使用myisam的情况。51Testing软件测试网V%N"S?|9Je.h#R,p)`3tWd4NR+ebgV051Testing软件测试网1T1Vq/}-c!OW

源码如下所示51Testing软件测试网+` qv#K

}%mEkC51Testing软件测试网"r-bC0N h1b DU/g

代码地址：sql_select.cc:10229，函数：create_tmp_table()，位置：sql_select.cc:1055751Testing软件测试网VE#N/_5rW51Testing软件测试网3Hn!S[r!Slf

51Testing软件测试网2?qZB&_{e&Je,[`/* If result table is small; use a heap */51Testing软件测试网m,g S$Z-p3r

/* future: storage engine selection can be made dynamic? */

(z'bbW2t9J[0  if ( blob_count || using_unique_constraint

l)HN(c.b0      || ( thd->variables .big_tables && !( select_options & SELECT_SMALL_RESULT ))51Testing软件测试网M} uf!o6KM6i

|| ( select_options & TMP_TABLE_FORCE_MYISAM ))51Testing软件测试网9~hh)bz"A

{51Testing软件测试网.N,y-S9a0n0|o

share->db_plugin = ha_lock_engine(0, myisam_hton);51Testing软件测试网d2BWD8A,S

table->file = get_new_handler( share, &table ->mem_root,

VV,Clj0                                 share->db_type ());

fnAm$OL^v0    if (group &&51Testing软件测试网u3jy3V}\:}p;]e

( param->group_parts > table-> file->max_key_parts () ||

S0n]Shd0           param->group_length > table-> file->max_key_length ()))

B~nu&rb^1ROE ^0      using_unique_constraint=1;51Testing软件测试网}'aKuLq

}51Testing软件测试网#o%d0_;E|hDg$~

else

;Wb/Z%]2r5{0  {

H|6J-Fl3N*i0    share->db_plugin = ha_lock_engine(0, heap_hton);51Testing软件测试网Ffk"}!`0iI4r

table->file = get_new_handler( share, &table ->mem_root,51Testing软件测试网%q@#G*Z:S$R

share->db_type ());51Testing软件测试网0Bt5^|)S]sC

}

gFH(^0aZ~0代码地址：sql_select.cc:11224，函数：create_myisam_from_heap()，位置：sql_select.cc:1128751Testing软件测试网&`,\:t

S.~F.k)E

P5J%T&}-LOf0Cu_l.d4l0/*

n_bG(n*c0    copy all old rows from heap table to MyISAM table51Testing软件测试网"dR9e,].f:tb

q,R

This is the only code that uses record[1] to read/write but this51Testing软件测试网#P.s\]V*Nx)qx8_

is safe as this is a temporary MyISAM table without timestamp/autoincrement51Testing软件测试网+Xg-oR{ ]

or partitioning.

U#LoU+G!Mo0  */

7My+LbA_R9g0  while (! table->file ->rnd_next( new_table.record [1]))51Testing软件测试网IPfb2C?"]+u)V

{

1Y"G b)rB4i\0    write_err= new_table .file-> ha_write_row(new_table .record[1]);

8w8fS)~:Y7`[a0    DBUG_EXECUTE_IF("raise_error" , write_err= HA_ERR_FOUND_DUPP_KEY ;);

P9|!YzU.No`;N0    if (write_err )

/X-]Ryy#Yl;q%T0      goto err ;51Testing软件测试网{:Yo1Z WX

}51Testing软件测试网W rv&b#RH

官方文档相关内容51Testing软件测试网0~dK-EMB|I

Gk/Dk-R0以上内容只是源码表面的问题，通过查询MySQL的官方文档，得到了更为权威的官方信息。51Testing软件测试网Jf$g m8Ni

8[Xi;Z+hQ-\|0临时表创建的条件：Usi/J7`0

c7| TX}g01、如果order by条件和group by的条件不一样，或者order by或group by的不是join队列中的第一个表的字段。51Testing软件测试网l4|b8^^:Cm51Testing软件测试网S+n%Y*mX#K{8~

2、DISTINCT联合order by条件的查询。51Testing软件测试网pl6sRc!`#]vn}O51Testing软件测试网!vOtrc

3、如果使用了SQL_SMALL_RESULT选项，MySQL使用memory临时表，否则，查询询结果需要存储到磁盘。v-i

_u&vY7N:`0

+^$f

o8A7o7EhZ~6E0临时表不使用内存表的原则：51Testing软件测试网1o,hIXr

-N3\,mq;|2~9jD01、表中有BLOB或TEXT类型。51Testing软件测试网6J/\ d[C)X"`d

8Ec KoC/WA;wi02、group by或distinct条件中的字段大于512个字节。51Testing软件测试网W)}5@

K:IQ;CX51Testing软件测试网(gL}9P/F3AIb$aiN

3、如果使用了UNION或UNION ALL，任何查询列表中的字段大于512个字节。g-t2k)|P051Testing软件测试网,F^F$wc

此外，使用内存表最大为tmp_table_size和max_heap_table_size的最小值。如果超过该值，转化为myisam存储引擎存储到磁盘。X0j:I-x}0