51Testing软件测试网s
x ~k`!?|(O 通过对MySQL数据库源码的跟踪和调试,以及参考MySQL官方文档,对MySQL内部临时表使用策略进行整理,以便于更加深入的理解。51Testing软件测试网*H"C
DB9|/l7e
51Testing软件测试网*M}
E1U:p 使用内部临时表条件
K)l+Xo&drv6{04|ERL[v0 MySQL内部临时表的使用有一定的策略,从源码中关于SQL查询是否需要内部临时表。可以总结如下:51Testing软件测试网,D)xlJ%b|+[l
g
FLJ#f#q)X\0 1、DISTINCT查询,但是简单的DISTINCT查询,比如对primary key、unique key等DISTINCT查询时,查询优化器会将DISTINCT条件优化,去除DISTINCT条件,也不会创建临时表;51Testing软件测试网-~X-JJi0VMW@
M XX+di[]
E8RE0 2、不是第一个表的字段使用ORDER BY 或者GROUP BY;
L)C4U1tO4O-{051Testing软件测试网j
M.KoOt 3、ORDER BY和GROUP BY使用不同的顺序;51Testing软件测试网*?0|1[ V5F-C_
Nw7Ar:A-j3H0 4、用户需要缓存结果;
sC'h\[/C
x!vt.K051Testing软件测试网Jl&eJX$EM(_!` 5、ROLLUP查询。
3UpPuz&]l}051Testing软件测试网#Hp KN1\ 源码如下所示
+m&s8vzm0eK!Q2g4x!T+k4R4K0 代码地址:sql_select.cc:854,函数:JOIN::optimize(),位置:sql_select.cc:1399
:RO$_4l'no2`0/* $r8LRBK!h7Y0 Check if we need to create a temporary table. P-ry8u
I1c }0 This has to be done if all tables are not already read (const tables) 7n W3}w7Mz0 and one of the following conditions holds:51Testing软件测试网rY^x%^~ - We are using DISTINCT (simple distinct's are already optimized away) #BIXQRr$H-|0 - We are using an ORDER BY or GROUP BY on fields not in the first table 2?7W XT&q;}4I0 - We are using different ORDER BY and GROUP BY orders51Testing软件测试网7@\;Dp N]&Ve - The user wants us to buffer the result.51Testing软件测试网@&ag1h6YQ%TPJ When the WITH ROLLUP modifier is present, we cannot skip temporary table51Testing软件测试网C-x~/rekT creation for the DISTINCT clause just because there are only const tables.51Testing软件测试网WXOJv%y!z */51Testing软件测试网
J.Z
W N:D
kk z need_tmp= (( const_tables != tables &&51Testing软件测试网k6a;G`5]5P$G (( select_distinct || !simple_order || !simple_group) ||51Testing软件测试网kzX@9i8Q n rO ( group_list && order ) ||51Testing软件测试网y5i+H,H
z$x(B
u test(select_options & OPTION_BUFFER_RESULT))) ||51Testing软件测试网(A(P^)n0r1J[ S9@8? ( rollup.state != ROLLUP:: STATE_NONE && select_distinct )); |
51Testing软件测试网d]CEX,p5?x 内部临时表使用原则
?SQ/MY d1kF051Testing软件测试网(z:s~)C%__ Mt 但是使用了内部临时表,那么他是怎么存储的呢?原则是这样的:51Testing软件测试网,dc^vTR
3YGkWmq2^SG&n0 1、当查询结果较小的情况下,使用heap存储引擎进行存储。也就是说在内存中存储查询结果。51Testing软件测试网oP&V9u
w ?cu
-q+Ka(Gy0 2、当查询结果较大的情况下,使用myisam存储引擎进行存储。51Testing软件测试网
\%d"W;v+O
51Testing软件测试网)}2l H XTSe 3、当查询结果最初较小,但是不断增大的情况下,将会有从heap存储引擎转化为myisam存储引擎存储查询结果。
(K!L ED*i0W:v6M }.l3l8V3~0 什么情况算是查询结果较小呢?从源码中if的几个参数可以看出:
'_.K8e_h+N$rc0u.e"T~MM0 1、有blob字段的情况;
t$`7A(Y*sH08rR"I%I@
u[6apA/W0 2、使用唯一限制的情况;
pf+C3W;KcX0
~k0a"j5?0 3、当前表定义为大表的情况;51Testing软件测试网8D`(j_6Q*h#L"DG)X:pR*a
B uy{CW@,~5fB0 4、查询结果的选项为小结果集的情况;
/Q7k*J D
CS051Testing软件测试网,Tf5Dwn(z}%~D 5、查询结果的选项为强制使用myisam的情况。
,q-{ MpHhE051Testing软件测试网2Y$FlP,jf
6N"L;m
U$Tf'e-`j0 源码如下所示51Testing软件测试网,IA&X8D+k
P$F
51Testing软件测试网p){C-f
W q 代码地址:sql_select.cc:10229,函数:create_tmp_table(),位置:sql_select.cc:10557
4S
B3n`'R2f9pa3]051Testing软件测试网(b
j3N)f[)QHm9r?U5[H(p,@3rP`7b0/* If result table is small; use a heap */51Testing软件测试网p(r4Bev8Ex /* future: storage engine selection can be made dynamic? */ yr&i+X1cm0 if ( blob_count || using_unique_constraint51Testing软件测试网w6yc5Ec7M8Mu2x || ( thd->variables .big_tables && !( select_options & SELECT_SMALL_RESULT )) 2}YFl.Q)V0 || ( select_options & TMP_TABLE_FORCE_MYISAM ))51Testing软件测试网"]cJ:?:j2[9sg {51Testing软件测试网
Vzj?1a8?0gM$Y share->db_plugin = ha_lock_engine(0, myisam_hton);51Testing软件测试网%\Hd1g:S table->file = get_new_handler( share, &table ->mem_root,51Testing软件测试网2Hecm.~ share->db_type ());51Testing软件测试网$Yl5v AJ wLdK if (group &&
c/G;RJ OJ0 ( param->group_parts > table-> file->max_key_parts () ||51Testing软件测试网uDQGwK1@k param->group_length > table-> file->max_key_length ()))51Testing软件测试网^|![;m-P3} using_unique_constraint=1;51Testing软件测试网H;wIzA-Fu*mo } !WyKnoe4^0 else51Testing软件测试网g5KX
y:y:\2?3PwXp {51Testing软件测试网)F?)SAh/sL P share->db_plugin = ha_lock_engine(0, heap_hton); VX3|[0ye)JW0 table->file = get_new_handler( share, &table ->mem_root,51Testing软件测试网x+`3sE!us!\2V6[ share->db_type ()); n)Y.sV6h0 } |
Gj#j"q!_6B%M0 代码地址:sql_select.cc:11224,函数:create_myisam_from_heap(),位置:sql_select.cc:1128751Testing软件测试网M$X
e6|nq
51Testing软件测试网|Y/f pb51Testing软件测试网,DJ S
p#t2m\
/*51Testing软件测试网zY,P%o6Y copy all old rows from heap table to MyISAM table51Testing软件测试网+m ?"R"u&E This is the only code that uses record[1] to read/write but this51Testing软件测试网S}#T*d8Fc is safe as this is a temporary MyISAM table without timestamp/autoincrement51Testing软件测试网Owp-p9w w\&?P or partitioning. Xo4V d9N0 */ z3m~P:P'@&`0 while (! table->file ->rnd_next( new_table.record [1]))51Testing软件测试网K`8Q"V/D%KK%i {51Testing软件测试网*AT(r~+`w7r write_err= new_table .file-> ha_write_row(new_table .record[1]);51Testing软件测试网 gs:n@"XX0q4X8M'o DBUG_EXECUTE_IF("raise_error" , write_err= HA_ERR_FOUND_DUPP_KEY ;);51Testing软件测试网8G3z{8|/z0m2Rb&M if (write_err )51Testing软件测试网Gh/L7t)q2Fbb goto err ; E+udp;XTc0 } |
4S(e9h,D&n1N0 官方文档相关内容
xnA/H0p5A$z5o051Testing软件测试网z`f[K3O
k.k 以上内容只是源码表面的问题,通过查询MySQL的官方文档,得到了更为权威的官方信息。
2y]Dw1Xz0Ep&Ss/}
SRT.yj0 临时表创建的条件:
'c `lWI^0eYeI4P,h+~o(X0 1、如果order by条件和group by的条件不一样,或者order by或group by的不是join队列中的第一个表的字段。
WV
~IL-^`0_051Testing软件测试网7p!cr8~!kn k^ 2、DISTINCT联合order by条件的查询。51Testing软件测试网3ce/e"|d@?_#R
8[)q#g$I q^~g'o.F0 3、如果使用了SQL_SMALL_RESULT选项,MySQL使用memory临时表,否则,查询询结果需要存储到磁盘。
]7faU;Q*j%}m;q\5w6r08f3R$L^m5r8z:E-D0 临时表不使用内存表的原则:51Testing软件测试网0yYYL%B$y?r/Q
51Testing软件测试网^p1P7t)OSf 1、表中有BLOB或TEXT类型。
ew+c:h3L%N051Testing软件测试网Q3~k&J] P$n#b1Y*lg 2、group by或distinct条件中的字段大于512个字节。
'zN@|&h&f!j!z(nq051Testing软件测试网]/h
U:dCG 3、如果使用了UNION或UNION ALL,任何查询列表中的字段大于512个字节。51Testing软件测试网 AY-x%K1x"X4G$I
51Testing软件测试网ti(DHoE(RZ 此外,使用内存表最大为tmp_table_size和max_heap_table_size的最小值。如果超过该值,转化为myisam存储引擎存储到磁盘。51Testing软件测试网5pm7b8s9brGJ%P@