-
Notifications
You must be signed in to change notification settings - Fork 683
/
Copy pathMySQL · 引擎特性 · 临时表那些事儿.md.html
966 lines (492 loc) · 44.7 KB
/
MySQL · 引擎特性 · 临时表那些事儿.md.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
<!DOCTYPE html>
<!-- saved from url=(0046)https://kaiiiz.github.io/hexo-theme-book-demo/ -->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1.0, user-scalable=no">
<link rel="icon" href="/static/favicon.png">
<title>MySQL · 引擎特性 · 临时表那些事儿.md.html</title>
<!-- Spectre.css framework -->
<link rel="stylesheet" href="/static/index.css">
<!-- theme css & js -->
<meta name="generator" content="Hexo 4.2.0">
</head>
<body>
<div class="book-container">
<div class="book-sidebar">
<div class="book-brand">
<a href="/">
<img src="/static/favicon.png">
<span>技术文章摘抄</span>
</a>
</div>
<div class="book-menu uncollapsible">
<ul class="uncollapsible">
<li><a href="/" class="current-tab">首页</a></li>
</ul>
<ul class="uncollapsible">
<li><a href="../">上一级</a></li>
</ul>
<ul class="uncollapsible">
<li>
<a href="/文章/AQS 万字图文全面解析.md.html">AQS 万字图文全面解析.md.html</a>
</li>
<li>
<a href="/文章/Docker 镜像构建原理及源码分析.md.html">Docker 镜像构建原理及源码分析.md.html</a>
</li>
<li>
<a href="/文章/ElasticSearch 小白从入门到精通.md.html">ElasticSearch 小白从入门到精通.md.html</a>
</li>
<li>
<a href="/文章/JVM CPU Profiler技术原理及源码深度解析.md.html">JVM CPU Profiler技术原理及源码深度解析.md.html</a>
</li>
<li>
<a href="/文章/JVM 垃圾收集器.md.html">JVM 垃圾收集器.md.html</a>
</li>
<li>
<a href="/文章/JVM 面试的 30 个知识点.md.html">JVM 面试的 30 个知识点.md.html</a>
</li>
<li>
<a href="/文章/Java IO 体系、线程模型大总结.md.html">Java IO 体系、线程模型大总结.md.html</a>
</li>
<li>
<a href="/文章/Java NIO浅析.md.html">Java NIO浅析.md.html</a>
</li>
<li>
<a href="/文章/Java 面试题集锦（网络篇）.md.html">Java 面试题集锦（网络篇）.md.html</a>
</li>
<li>
<a href="/文章/Java-直接内存 DirectMemory 详解.md.html">Java-直接内存 DirectMemory 详解.md.html</a>
</li>
<li>
<a href="/文章/Java中9种常见的CMS GC问题分析与解决（上）.md.html">Java中9种常见的CMS GC问题分析与解决（上）.md.html</a>
</li>
<li>
<a href="/文章/Java中9种常见的CMS GC问题分析与解决（下）.md.html">Java中9种常见的CMS GC问题分析与解决（下）.md.html</a>
</li>
<li>
<a href="/文章/Java中的SPI.md.html">Java中的SPI.md.html</a>
</li>
<li>
<a href="/文章/Java中的ThreadLocal.md.html">Java中的ThreadLocal.md.html</a>
</li>
<li>
<a href="/文章/Java线程池实现原理及其在美团业务中的实践.md.html">Java线程池实现原理及其在美团业务中的实践.md.html</a>
</li>
<li>
<a href="/文章/Java魔法类：Unsafe应用解析.md.html">Java魔法类：Unsafe应用解析.md.html</a>
</li>
<li>
<a href="/文章/Kafka 源码阅读笔记.md.html">Kafka 源码阅读笔记.md.html</a>
</li>
<li>
<a href="/文章/Kafka、ActiveMQ、RabbitMQ、RocketMQ 区别以及高可用原理.md.html">Kafka、ActiveMQ、RabbitMQ、RocketMQ 区别以及高可用原理.md.html</a>
</li>
<li>
<a href="/文章/MySQL · 引擎特性 · InnoDB Buffer Pool.md.html">MySQL · 引擎特性 · InnoDB Buffer Pool.md.html</a>
</li>
<li>
<a href="/文章/MySQL · 引擎特性 · InnoDB IO子系统.md.html">MySQL · 引擎特性 · InnoDB IO子系统.md.html</a>
</li>
<li>
<a href="/文章/MySQL · 引擎特性 · InnoDB 事务系统.md.html">MySQL · 引擎特性 · InnoDB 事务系统.md.html</a>
</li>
<li>
<a href="/文章/MySQL · 引擎特性 · InnoDB 同步机制.md.html">MySQL · 引擎特性 · InnoDB 同步机制.md.html</a>
</li>
<li>
<a href="/文章/MySQL · 引擎特性 · InnoDB 数据页解析.md.html">MySQL · 引擎特性 · InnoDB 数据页解析.md.html</a>
</li>
<li>
<a href="/文章/MySQL · 引擎特性 · InnoDB崩溃恢复.md.html">MySQL · 引擎特性 · InnoDB崩溃恢复.md.html</a>
</li>
<li>
<a class="current-tab" href="/文章/MySQL · 引擎特性 · 临时表那些事儿.md.html">MySQL · 引擎特性 · 临时表那些事儿.md.html</a>
</li>
<li>
<a href="/文章/MySQL 主从复制 半同步复制.md.html">MySQL 主从复制 半同步复制.md.html</a>
</li>
<li>
<a href="/文章/MySQL 主从复制 基于GTID复制.md.html">MySQL 主从复制 基于GTID复制.md.html</a>
</li>
<li>
<a href="/文章/MySQL 主从复制.md.html">MySQL 主从复制.md.html</a>
</li>
<li>
<a href="/文章/MySQL 事务日志(redo log和undo log).md.html">MySQL 事务日志(redo log和undo log).md.html</a>
</li>
<li>
<a href="/文章/MySQL 亿级别数据迁移实战代码分享.md.html">MySQL 亿级别数据迁移实战代码分享.md.html</a>
</li>
<li>
<a href="/文章/MySQL 从一条数据说起-InnoDB行存储数据结构.md.html">MySQL 从一条数据说起-InnoDB行存储数据结构.md.html</a>
</li>
<li>
<a href="/文章/MySQL 地基基础：事务和锁的面纱.md.html">MySQL 地基基础：事务和锁的面纱.md.html</a>
</li>
<li>
<a href="/文章/MySQL 地基基础：数据字典.md.html">MySQL 地基基础：数据字典.md.html</a>
</li>
<li>
<a href="/文章/MySQL 地基基础：数据库字符集.md.html">MySQL 地基基础：数据库字符集.md.html</a>
</li>
<li>
<a href="/文章/MySQL 性能优化：碎片整理.md.html">MySQL 性能优化：碎片整理.md.html</a>
</li>
<li>
<a href="/文章/MySQL 故障诊断：一个 ALTER TALBE 执行了很久，你慌不慌？.md.html">MySQL 故障诊断：一个 ALTER TALBE 执行了很久，你慌不慌？.md.html</a>
</li>
<li>
<a href="/文章/MySQL 故障诊断：如何在日志中轻松定位大事务.md.html">MySQL 故障诊断：如何在日志中轻松定位大事务.md.html</a>
</li>
<li>
<a href="/文章/MySQL 故障诊断：教你快速定位加锁的 SQL.md.html">MySQL 故障诊断：教你快速定位加锁的 SQL.md.html</a>
</li>
<li>
<a href="/文章/MySQL 日志详解.md.html">MySQL 日志详解.md.html</a>
</li>
<li>
<a href="/文章/MySQL 的半同步是什么？.md.html">MySQL 的半同步是什么？.md.html</a>
</li>
<li>
<a href="/文章/MySQL中的事务和MVCC.md.html">MySQL中的事务和MVCC.md.html</a>
</li>
<li>
<a href="/文章/MySQL事务_事务隔离级别详解.md.html">MySQL事务_事务隔离级别详解.md.html</a>
</li>
<li>
<a href="/文章/MySQL优化：优化 select count().md.html">MySQL优化：优化 select count().md.html</a>
</li>
<li>
<a href="/文章/MySQL共享锁、排他锁、悲观锁、乐观锁.md.html">MySQL共享锁、排他锁、悲观锁、乐观锁.md.html</a>
</li>
<li>
<a href="/文章/MySQL的MVCC（多版本并发控制）.md.html">MySQL的MVCC（多版本并发控制）.md.html</a>
</li>
<li>
<a href="/文章/QingStor 对象存储架构设计及最佳实践.md.html">QingStor 对象存储架构设计及最佳实践.md.html</a>
</li>
<li>
<a href="/文章/RocketMQ 面试题集锦.md.html">RocketMQ 面试题集锦.md.html</a>
</li>
<li>
<a href="/文章/SnowFlake 雪花算法生成分布式 ID.md.html">SnowFlake 雪花算法生成分布式 ID.md.html</a>
</li>
<li>
<a href="/文章/Spring Boot 2.x 结合 k8s 实现分布式微服务架构.md.html">Spring Boot 2.x 结合 k8s 实现分布式微服务架构.md.html</a>
</li>
<li>
<a href="/文章/Spring Boot 教程：如何开发一个 starter.md.html">Spring Boot 教程：如何开发一个 starter.md.html</a>
</li>
<li>
<a href="/文章/Spring MVC 原理.md.html">Spring MVC 原理.md.html</a>
</li>
<li>
<a href="/文章/Spring MyBatis和Spring整合的奥秘.md.html">Spring MyBatis和Spring整合的奥秘.md.html</a>
</li>
<li>
<a href="/文章/Spring 帮助你更好的理解Spring循环依赖.md.html">Spring 帮助你更好的理解Spring循环依赖.md.html</a>
</li>
<li>
<a href="/文章/Spring 循环依赖及解决方式.md.html">Spring 循环依赖及解决方式.md.html</a>
</li>
<li>
<a href="/文章/Spring中眼花缭乱的BeanDefinition.md.html">Spring中眼花缭乱的BeanDefinition.md.html</a>
</li>
<li>
<a href="/文章/Vert.x 基础入门.md.html">Vert.x 基础入门.md.html</a>
</li>
<li>
<a href="/文章/eBay 的 Elasticsearch 性能调优实践.md.html">eBay 的 Elasticsearch 性能调优实践.md.html</a>
</li>
<li>
<a href="/文章/不可不说的Java“锁”事.md.html">不可不说的Java“锁”事.md.html</a>
</li>
<li>
<a href="/文章/互联网并发限流实战.md.html">互联网并发限流实战.md.html</a>
</li>
<li>
<a href="/文章/从ReentrantLock的实现看AQS的原理及应用.md.html">从ReentrantLock的实现看AQS的原理及应用.md.html</a>
</li>
<li>
<a href="/文章/从SpringCloud开始，聊微服务架构.md.html">从SpringCloud开始，聊微服务架构.md.html</a>
</li>
<li>
<a href="/文章/全面了解 JDK 线程池实现原理.md.html">全面了解 JDK 线程池实现原理.md.html</a>
</li>
<li>
<a href="/文章/分布式一致性理论与算法.md.html">分布式一致性理论与算法.md.html</a>
</li>
<li>
<a href="/文章/分布式一致性算法 Raft.md.html">分布式一致性算法 Raft.md.html</a>
</li>
<li>
<a href="/文章/分布式唯一 ID 解析.md.html">分布式唯一 ID 解析.md.html</a>
</li>
<li>
<a href="/文章/分布式链路追踪：集群管理设计.md.html">分布式链路追踪：集群管理设计.md.html</a>
</li>
<li>
<a href="/文章/动态代理种类及原理，你知道多少？.md.html">动态代理种类及原理，你知道多少？.md.html</a>
</li>
<li>
<a href="/文章/响应式架构与 RxJava 在有赞零售的实践.md.html">响应式架构与 RxJava 在有赞零售的实践.md.html</a>
</li>
<li>
<a href="/文章/大数据算法——布隆过滤器.md.html">大数据算法——布隆过滤器.md.html</a>
</li>
<li>
<a href="/文章/如何优雅地记录操作日志？.md.html">如何优雅地记录操作日志？.md.html</a>
</li>
<li>
<a href="/文章/如何设计一个亿级消息量的 IM 系统.md.html">如何设计一个亿级消息量的 IM 系统.md.html</a>
</li>
<li>
<a href="/文章/异步网络模型.md.html">异步网络模型.md.html</a>
</li>
<li>
<a href="/文章/当我们在讨论CQRS时，我们在讨论些神马？.md.html">当我们在讨论CQRS时，我们在讨论些神马？.md.html</a>
</li>
<li>
<a href="/文章/彻底理解 MySQL 的索引机制.md.html">彻底理解 MySQL 的索引机制.md.html</a>
</li>
<li>
<a href="/文章/最全的 116 道 Redis 面试题解答.md.html">最全的 116 道 Redis 面试题解答.md.html</a>
</li>
<li>
<a href="/文章/有赞权限系统(SAM).md.html">有赞权限系统(SAM).md.html</a>
</li>
<li>
<a href="/文章/有赞零售中台建设方法的探索与实践.md.html">有赞零售中台建设方法的探索与实践.md.html</a>
</li>
<li>
<a href="/文章/服务注册与发现原理剖析（Eureka、Zookeeper、Nacos）.md.html">服务注册与发现原理剖析（Eureka、Zookeeper、Nacos）.md.html</a>
</li>
<li>
<a href="/文章/深入浅出Cache.md.html">深入浅出Cache.md.html</a>
</li>
<li>
<a href="/文章/深入理解 MySQL 底层实现.md.html">深入理解 MySQL 底层实现.md.html</a>
</li>
<li>
<a href="/文章/漫画讲解 git rebase VS git merge.md.html">漫画讲解 git rebase VS git merge.md.html</a>
</li>
<li>
<a href="/文章/生成浏览器唯一稳定 ID 的探索.md.html">生成浏览器唯一稳定 ID 的探索.md.html</a>
</li>
<li>
<a href="/文章/缓存 如何保证缓存与数据库的双写一致性？.md.html">缓存 如何保证缓存与数据库的双写一致性？.md.html</a>
</li>
<li>
<a href="/文章/网易严选怎么做全链路监控的？.md.html">网易严选怎么做全链路监控的？.md.html</a>
</li>
<li>
<a href="/文章/美团万亿级 KV 存储架构与实践.md.html">美团万亿级 KV 存储架构与实践.md.html</a>
</li>
<li>
<a href="/文章/美团点评Kubernetes集群管理实践.md.html">美团点评Kubernetes集群管理实践.md.html</a>
</li>
<li>
<a href="/文章/美团百亿规模API网关服务Shepherd的设计与实现.md.html">美团百亿规模API网关服务Shepherd的设计与实现.md.html</a>
</li>
<li>
<a href="/文章/解读《阿里巴巴 Java 开发手册》背后的思考.md.html">解读《阿里巴巴 Java 开发手册》背后的思考.md.html</a>
</li>
<li>
<a href="/文章/认识 MySQL 和 Redis 的数据一致性问题.md.html">认识 MySQL 和 Redis 的数据一致性问题.md.html</a>
</li>
<li>
<a href="/文章/进阶：Dockerfile 高阶使用指南及镜像优化.md.html">进阶：Dockerfile 高阶使用指南及镜像优化.md.html</a>
</li>
<li>
<a href="/文章/铁总在用的高性能分布式缓存计算框架 Geode.md.html">铁总在用的高性能分布式缓存计算框架 Geode.md.html</a>
</li>
<li>
<a href="/文章/阿里云PolarDB及其共享存储PolarFS技术实现分析（上）.md.html">阿里云PolarDB及其共享存储PolarFS技术实现分析（上）.md.html</a>
</li>
<li>
<a href="/文章/阿里云PolarDB及其共享存储PolarFS技术实现分析（下）.md.html">阿里云PolarDB及其共享存储PolarFS技术实现分析（下）.md.html</a>
</li>
<li>
<a href="/文章/面试最常被问的 Java 后端题.md.html">面试最常被问的 Java 后端题.md.html</a>
</li>
<li>
<a href="/文章/领域驱动设计在互联网业务开发中的实践.md.html">领域驱动设计在互联网业务开发中的实践.md.html</a>
</li>
<li>
<a href="/文章/领域驱动设计的菱形对称架构.md.html">领域驱动设计的菱形对称架构.md.html</a>
</li>
<li>
<a href="/文章/高效构建 Docker 镜像的最佳实践.md.html">高效构建 Docker 镜像的最佳实践.md.html</a>
</li>
</ul>
</div>
</div>
<div class="sidebar-toggle" onclick="sidebar_toggle()" onmouseover="add_inner()" onmouseleave="remove_inner()">
<div class="sidebar-toggle-inner"></div>
</div>
<script>
function add_inner() {
let inner = document.querySelector('.sidebar-toggle-inner')
inner.classList.add('show')
}
function remove_inner() {
let inner = document.querySelector('.sidebar-toggle-inner')
inner.classList.remove('show')
}
function sidebar_toggle() {
let sidebar_toggle = document.querySelector('.sidebar-toggle')
let sidebar = document.querySelector('.book-sidebar')
let content = document.querySelector('.off-canvas-content')
if (sidebar_toggle.classList.contains('extend')) { // show
sidebar_toggle.classList.remove('extend')
sidebar.classList.remove('hide')
content.classList.remove('extend')
} else { // hide
sidebar_toggle.classList.add('extend')
sidebar.classList.add('hide')
content.classList.add('extend')
}
}
function open_sidebar() {
let sidebar = document.querySelector('.book-sidebar')
let overlay = document.querySelector('.off-canvas-overlay')
sidebar.classList.add('show')
overlay.classList.add('show')
}
function hide_canvas() {
let sidebar = document.querySelector('.book-sidebar')
let overlay = document.querySelector('.off-canvas-overlay')
sidebar.classList.remove('show')
overlay.classList.remove('show')
}
</script>
<div class="off-canvas-content">
<div class="columns">
<div class="column col-12 col-lg-12">
<div class="book-navbar">
<!-- For Responsive Layout -->
<header class="navbar">
<section class="navbar-section">
<a onclick="open_sidebar()">
<i class="icon icon-menu"></i>
</a>
</section>
</header>
</div>
<div class="book-content" style="max-width: 960px; margin: 0 auto;
overflow-x: auto;
overflow-y: hidden;">
<div class="book-post">
<p id="tip" align="center"></p>
<div><h1>MySQL · 引擎特性 · 临时表那些事儿</h1>
<h2>前言</h2>
<p>相比于普通的用户数据表，MySQL/InnoDB中的临时表，大家应该会陌生很多。再加上不同的临时表创建的时机和创建的位置都不固定，这也进一步加大神秘感。最让人捉摸不透的是，临时表很多时候会先创建文件，然后什么都不做，就把文件删除，留一个句柄读写，给人的感觉是神龙见首不见尾。本文分析了详细MySQL各个版本临时表的处理方式，希望对大家有所帮助。</p>
<h2>综述</h2>
<p>准确的说，我们常说的临时表分为两种，一种真的是表，用来存储用户发送的数，读写走的是表读写接口，读写的时候表一定在文件系统上存在，另外一种，应该是一种临时文件，用来存储SQL计算中间过程的数据，读写走的是文件读写接口，读写的时候文件可能已经被删除了，留一个文件句柄进行操作。</p>
<h3>临时表</h3>
<p>临时表可以分为磁盘临时表和内存临时表，而临时文件，只会存在于磁盘上，不会存在于内存中。具体来说，临时表的内存形态有Memory引擎和Temptable引擎，主要区别是对字符类型(varchar, blob，text类型)的存储方式，前者不管实际字符多少，都是用定长的空间存储，后者会用变长的空间存储，这样提高了内存中的存储效率，有更多的数据可以放在内存中处理而不是转换成磁盘临时表。Memory引擎从早期的5.6就可以使用，Temptable是8.0引入的新的引擎。另外一方面，磁盘临时表也有三种形态，一种是MyISAM表，一种是InnoDB临时表，另外一种是Temptable的文件map表。其中最后一种方式，是8.0提供的。</p>
<p>在5.6以及以前的版本，磁盘临时表都是放在数据库配置的临时目录，磁盘临时表的undolog都是与普通表的undo放在一起(注意由于磁盘临时表在数据库重启后就被删除了，不需要redolog通过奔溃恢复来保证事务的完整性，所以不需要写redolog，但是undolog还是需要的，因为需要支持回滚)。</p>
<p>在MySQL 5.7后，磁盘临时表的数据和undo都被独立出来，放在一个单独的表空间ibtmp1里面。之所以把临时表独立出来，主要是为了减少创建删除表时维护元数据的开销。</p>
<p>在MySQL 8.0后，磁盘临时表的数据单独放在Session临时表空间池(#innodb_temp目录下的ibt文件)里面，临时表的undo放在global的表空间ibtmp1里面。另外一个大的改进是，8.0的磁盘临时表数据占用的空间在连接断开后，就能释放给操作系统，而5.7的版本中需要重启才能释放。</p>
<p>目前有以下两种情况会用到临时表：</p>
<h4>用户显式创建临时表</h4>
<p>这种是用户通过显式的执行命令<code>create temporary table</code>创建的表，引擎的类型要么显式指定，要么使用默认配置的值(default_tmp_storage_engine)。内存使用就遵循指定引擎的内存管理方式，比如InnoDB的表会先缓存在Buffer Pool中，然后通过刷脏线程写回磁盘文件。</p>
<p>在5.6中，磁盘临时表位于tmpdir下，文件名类似<code>#sql4d2b_8_0.ibd</code>，其中<code>#sql</code>是固定的前缀，<code>4d2b</code>是进程号的十六进制表示，<code>8</code>是MySQL线程号的十六进制表示(show processlist中的id)，<code>0</code>是每个连接从0开始的递增值，<code>ibd</code>是innodb的磁盘临时表(通过参数<code>default_tmp_storage_engine</code>控制)。在5.6中，磁盘临时表创建好后，对应的frm以及引擎文件就在tmpdir下创建完毕，可以通过文件系统ls命令查看到。在连接关闭后，相应文件自动删除。因此，我们如果在5.6的tmpdir里面看到很多类似格式文件名，可以通过文件名来判断是哪个进程，哪个连接使用的临时表，这个技巧在排查tmpdir目录占用过多空间的问题时，尤其适用。用户显式创建的这种临时表，在连接释放的时候，会自动释放并把空间释放回操作系统。临时表的undolog存在undo表空间中，与普通表的undo放在一起。有了undo回滚段，用户创建的这种临时表也能支持回滚了。</p>
<p>在5.7中，临时磁盘表位于ibtmp文件中，ibtmp文件位置及大小控制方式由参数<code>innodb_temp_data_file_path</code>控制。显式创建的表的数据和undo都在ibtmp里面。用户连接断开后，临时表会释放，但是仅仅是在ibtmp文件里面标记一下，空间是不会释放回操作系统的。如果要释放空间，需要重启数据库。另外，需要注意的一点是，5.6可以在tmpdir下直接看到创建的文件，但是5.7是创建在ibtmp这个表空间里面，因此是看不到具体的表文件的。如果需要查看，则需要查看<code>INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO</code>这个表，里面有一列name，这里可以看到表名。命名规格与5.6的类似，因此也可以快速找到占用空间大的连接。</p>
<p>在8.0中，临时表的数据和undo被进一步分开，数据是存放在ibt文件中(由参数<code>innodb_temp_tablespaces_dir</code>控制)，undo依然存放在ibtmp文件中(依然由参数<code>innodb_temp_data_file_path</code>控制)。存放ibt文件的叫做Session临时表空间，存放undo的ibtmp叫做Global临时表空间。这里介绍一下这个存放数据的Session临时表空间。Session临时表空间，在磁盘上的表现是一组以ibt文件组成的文件池。启动的时候，数据库会在配置的目录下重新创建，关闭数据库的时候删除。启动的时候，默认会创建10个ibt文件，每个连接最多使用两个，一个给用户创建的临时表用，另外一个给下文描述的优化器创建的隐式临时表使用。当然只有在需要临时表的时候，才会创建，如果不需要，则不会占用ibt文件。当10个ibt都被使用完后，数据库会继续创建，最多创建四十万个。当连接释放时候，会自动把这个连接使用的ibt文件给释放，同时回收空间。如果要回收Global临时表空间，依然需要重启。但是由于已经把存放数据的文件分离出来，且其支持动态回收(即连接断开即释放空间)，所以5.7上困扰大家多时的空间占用问题，已经得到了很好的缓解。当然，还是有优化空间的，例如，空间需要在连接断开后，才能释放，而理论上，很多空间在某些SQL(如用户drop了某个显式创建的临时表)执行后，即可以释放。另外，如果需要查看表名，依然查看<code>INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO</code>这个表。需要注意的是，8.0上，显式临时表不能是压缩表，而5.6和5.7可以。</p>
<h4>优化器隐式创建临时表</h4>
<p>这种临时表，是数据库为了辅助某些复杂SQL的执行而创建的辅助表，是否需要临时表，一般都是由优化器决定。与用户显式创建的临时表直接创建磁盘文件不同，如果需要优化器觉得SQL需要临时表辅助，会先使用内存临时表，如果超过配置的内存(min(tmp_table_size, max_heap_table_siz))，就会转化成磁盘临时表，这种磁盘临时表就类似用户显式创建的，引擎类型通过参数<code>internal_tmp_disk_storage_engine</code>控制。一般稍微复杂一点的查询，包括且不限于order by, group by, distinct等，都会用到这种隐式创建的临时表。用户可以通过explain命令，在Extra列中，看是否有Using temporary这样的字样，如果有，就肯定要用临时表。</p>
<p>在5.6中，隐式临时表依然在tmpdir下，在复杂SQL执行的过程中，就能看到这临时表，一旦执行结束，就被删除。值得注意的是，5.6中，这种隐式创建的临时表，只能用MyISAM引擎，即没有<code>internal_tmp_disk_storage_engine</code>这个参数可以控制。所以，当我们的系统中只有innodb表时，也会看到MyISAM的某些指标在变动，这种情况下，一般都是隐式临时表的原因。</p>
<p>在5.7中，隐式临时表是创建在ibtmp文件中的，SQL结束后，会标记删除，但是空间依然不会返还给操作系统，如果需要返还，则需要重启数据库。另外，5.7支持参数<code>internal_tmp_disk_storage_engine</code>，用户可以选择InnoDB或者MYISAM表作为磁盘临时表。</p>
<p>在8.0中，隐式临时表是创建在Session临时表空间中的，即与用户显式创建的临时表的数据放在一起。如果一个连接第一次需要隐式临时表，那么数据库会从ibt文件构成的池子中取出一个给这个连接使用，直到连接释放。上文中，我们也提到过，在8.0中，用户显式创建的临时表也会从池子中分配一个ibt来使用，每个连接最多使用两个ibt文件用来存储临时表。我们可以查询<code>INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES</code>来确定ibt文件的去向。这个表中，每个ibt文件是一行，当前系统中有几个ibt文件就有几行。有一列叫做ID，如果此列为0，表示此ibt没有被使用，如果非0，表示被此ID的连接在用，比如ID为8，则表示process_id为8的连接在用这个ibt文件。另外，还有一列purpose，值为INTRINSIC表示是隐式临时表在用这个ibt，USER则表示是显示临时表在用。此外，还有一列size，表示当前的大小。用户可以查询这个表来确定整个数据库临时表的使用情况，十分方便。</p>
<p>在5.6和5.7中，内存临时表只能使用Memory引擎，到了8.0，多了一种Temptable引擎的选择。Temptable在存储格式有采用了变长存储，可以节省存储空间，进一步提高内存使用率，减少转换成磁盘临时表的次数。如果设置的磁盘临时表是InnoDB或者MYISAM，则需要一个转换拷贝的消耗。为了尽可能减少消耗，Temptable提出了一种overflow机制，即如果内存临时表超过配置大小，则使用磁盘空间map的方式，即打开一个文件，然后删除，留一个句柄进行读写操作。读写文件格式和内存中格式一样，这样就略过了转换这一步，进一步提高性能。注意，这个功能是在还没发布的8.0.16版本中才有的，因为还看不到代码，只能通过文档猜测其实现。在8.0.16中，参数<code>internal_tmp_disk_storage_engine</code>已经被去掉，磁盘临时表只能使用InnoDB形式或者TempTable的这种overflow形式。从文档中，我们似乎看出官方比较推荐使用TempTable这个新的引擎。具体性能提升情况，还需要等代码发布后，测试过才能得出结论。</p>
<h3>临时文件</h3>
<p>相比临时表，临时文件对大家可能更加陌生，临时文件更多的被使用在缓存数据，排序数据的场景中。一般情况下，被缓存或者排序的数据，首先放在内存中，如果内存放不下，才会使用磁盘临时文件的方式。临时文件的使用方式与一般的表也不太一样，一般的表创建完后，就开始读写数据，使用完后，才把文件删除，但是临时文件的使用方式不一样，在创建完后(使用mkstemp系统函数)，马上调用unlink删除文件，但是不close文件，后续使用原来的句柄操作文件。这样的好处是，当进程异常crash，不会有临时文件因为没被删除而残留，但是坏处也是明显的，我们在文件系统上使用ls命令就看不到这个文件，需要使用lsof +L1来查看这种deleted属性的文件。</p>
<p>目前，我们主要在一下场景使用临时文件：</p>
<h4>DDL中的临时文件</h4>
<p>在做online DDL的过程中，很多操作需要对原表进行重建，对表重建前，需要对各种二级索引排序，而大量数据的排序，不太可能在内存中完成，需要依赖外部排序算法，MySQL使用了归并排序。这个过程中就需要创建临时文件。一般需要的空间大小与原表差不多。但是在使用完之后，会马上清理，所以在做DDL的时候，需要保留出足够的空间。用户可以通过指定innodb_tmpdir来指定这种排序文件的路径。这个参数可以动态修改，一般把他设置在有足够磁盘空间的路径上。临时文件的名字一般是类似<code>ibXXXXXX</code>，其中<code>ib</code>是固定前缀，<code>XXXXXX</code>是大小写字母以及数字的随机组合。</p>
<p>在做online DDL中，我们是允许用户对原表做DML操作的，即增删改查。我们不能直接插入原表中，因此需要一个地方记录对原表的修改操作，在DDL结束后，再应用在新表上。这个记录的地方就是online log，当然如果改动少的话，直接存在内存里(参数<code>innodb_sort_buffer_size</code>可控制，同时这个参数也控制online log每个读写块的大小)面即可。这个onlinelog也是用临时文件存，创建在innodb_tmpdir，最大大小为参数<code>innodb_online_alter_log_max_size</code>控制，如果超过这个大小了，DDL就会失败。临时文件的名字也类似上述的排序临时文件的名字。</p>
<p>在online DDL的最后阶段，需要把排序完的文件和中途产生的DML全都应用到一个中间文件上，中间文件文件名类似<code>#sql-ib53-522550444.ibd</code>，其中<code>#sql-ib</code>是固定的前缀，<code>53</code>是InnoDB层的table id，<code>522550444</code>是随机生成的数字。同时，在server层也会生成一个frm文件(8.0中没有)，文件名类似<code>#sql-4d2b_2a.frm</code>，其中<code>#sql</code>是固定前缀，<code>4d2b</code>是进程号的十六进制表示，<code>2a</code>是线程号的十六进制表示(show processlist中的id)。因此我们也可以通过这个命名规则来找到哪个线程在做DDL。这里需要注意一点，这里说的中间文件，其实算是一个临时表，并不是上文说中临时文件，这些中间文件可以通过ls来查看。当在DDL中的最后一步，会把这两个临时文件命名回原来的表名。正因为这个特性，所以当数据库中途crash的时候，可能会在磁盘上留下残余无用的文件。遇到这种情况，可以先把frm文件重命名成与ibd文件一样的名字，然后使用<code>DROP TABLE</code>#mysql50##sql-ib53-522550444`来清理残余的文件。注意，如果不用drop命令，直接删除ibd文件，可能会导致数据字典里面依然有残余的信息，做法不太优雅。当然，在8.0中，由于使用了原子的数据字典，就不会出现这种残余文件了。</p>
<h4>BinLog中的缓存操作</h4>
<p>BinLog只有在事务提交的时候才会写入到文件中，在没提交前，会先放在内存中(由参数<code>binlog_cache_size</code>控制)，如果内存放慢了，就会创建临时文件，使用方法也是先通过mkstemp创建，然后直接unlink，留一个句柄读写。临时文件名类似<code>MLXXXXXX</code>，其中<code>ML</code>是固定前缀，<code>XXXXXX</code>是大小写字母以及数字的随机组合。单个事务的BinLog太大，可能会导致整个BinLog的大小也过大，从而影响同步，因此我们需要尽可能控制事务大小。</p>
<h4>优化创建的临时文件</h4>
<p>有些操作，除了在引擎层需要依赖隐式临时表来辅助复杂SQL的计算，在Server层，也会创建临时文件来辅助，比如order by操作，会调用filesort函数。这个函数也会先使用内存(sort_buffer_size)排序，如果不够，就会创建一个临时文件，辅助排序。文件名类似<code>MYXXXXXX</code>，其中<code>MY</code>是固定前缀，<code>XXXXXX</code>是大小写字母以及数字的随机组合。</p>
<h4>Load data中用的临时文件</h4>
<p>在BinLog复制中，如果在主库上使用了Load Data命令，即从文件中导数据，数据库会把整个文件写入到RelayLog中，然后传到备库，备库解析RelayLog，从中抽取出对应的Load文件，然后在备库上应用。备库上这个文件存储的位置由参数<code>slave_load_tmpdir</code>控制。文档中建议这个目录不要配置在物理机的内存目录或者重启后会删除的目录。因为复制依赖这个文件，如果意外被删除，会导致复制中断。</p>
<h4>其他</h4>
<p>除了上文所述的几个地方外，还有其他几个地方也会用到临时文件：</p>
<ul>
<li>在InnoDB层，启动的时候会创建多个临时文件用来存储：最后一次外键或者唯一键错误; 最后一次死锁的信息; 最后的innodb状态信息。用临时文件而不用内存的原因猜测是，内存使用率不会因为写这些指标而波动。</li>
<li>在Server层，分区表使用show create table时，会用到临时文件。另外在MYISAM表内部排序的时候也会用到临时文件。</li>
</ul>
<h2>相关参数</h2>
<p>** tmpdir: ** 这个参数是临时目录的配置，在5.6以及之前的版本，临时表/文件默认都会放在这里。这个参数可以配置多个目录，这样就可以轮流在不同的目录上创建临时表/文件，如果不同的目录分别指向不同的磁盘，就可以达到分流的目的。
** innodb_tmpdir: ** 这个参数只要是被DDL中的排序临时文件使用的。其占用的空间会很大，建议单独配置。这个参数可以动态设置，也是一个Session变量。
** slave_load_tmpdir: ** 这个参数主要是给BinLog复制中Load Data时，配置备库存放临时文件位置时使用。因为数据库Crash后还需要依赖Load数据的文件，建议不要配置重启后会删除数据的目录。
** internal_tmp_disk_storage_engine: ** 当隐式临时表被转换成磁盘临时表时，使用哪种引擎，默认只有MyISAM和InnoDB。5.7及以后的版本才支持。8.0.16版本后取消的这个参数。
** internal_tmp_mem_storage_engine: ** 隐式临时表在内存时用的存储引擎，可以选择Memory或者Temptable引擎。建议选择新的Temptable引擎。
** default_tmp_storage_engine: ** 默认的显式临时表的引擎，即用户通过SQL语句创建的临时表的引擎。
** tmp_table_size: ** min(tmp_table_size,max_heap_table_size)是隐式临时表的内存大小，超过这个值会转换成磁盘临时表。
** max_heap_table_size: ** 用户创建的Memory内存表的内存限制大小。
** big_tables: ** 内存临时表转换成磁盘临时表需要有个转化操作，需要在不同引擎格式中转换，这个是需要消耗的。如果我们能提前知道执行某个SQL需要用到磁盘临时表，即内存肯定不够用，可以设置这个参数，这样优化器就跳过使用内存临时表，直接使用磁盘临时表，减少开销。
** temptable_max_ram: ** 这个参数是8.0后才有的，主要是给Temptable引擎指定内存大小，超过这个后，要么就转换成磁盘临时表，要么就使用自带的overflow机制。
** temptable_use_mmap: ** 是否使用Temptable的overflow机制。</p>
<h2>总结建议</h2>
<p>MySQL的临时表以及临时文件其实是一个比较复杂的话题，涉及的模块比较多，出现的时机比较难把握，导致排查问题相比普通表也难不少。建议读者结合代码细细研究，这样才能定位在线上可能出现的棘手问题。</p>
</div>
</div>
<div>
<div style="float: left">
<a href="/文章/MySQL · 引擎特性 · InnoDB崩溃恢复.md.html">上一页</a>
</div>
<div style="float: right">
<a href="/文章/MySQL 主从复制 半同步复制.md.html">下一页</a>
</div>
</div>
</div>
</div>
</div>
</div>
<a class="off-canvas-overlay" onclick="hide_canvas()"></a>
</div>
<script defer src="https://static.cloudflareinsights.com/beacon.min.js/v652eace1692a40cfa3763df669d7439c1639079717194" integrity="sha512-Gi7xpJR8tSkrpF7aordPZQlW2DLtzUlZcumS8dMQjwDHEnw9I7ZLyiOj/6tZStRBGtGgN6ceN6cMH8z7etPGlw==" data-cf-beacon='{"rayId":"70997fe02bdb8b66","version":"2021.12.0","r":1,"token":"1f5d475227ce4f0089a7cff1ab17c0f5","si":100}' crossorigin="anonymous"></script>
</body>
<!-- Global site tag (gtag.js) - Google Analytics -->
<script async src="https://www.googletagmanager.com/gtag/js?id=G-NPSEEVD756"></script>
<script>
window.dataLayer = window.dataLayer || [];
function gtag() {
dataLayer.push(arguments);
}
gtag('js', new Date());
gtag('config', 'G-NPSEEVD756');
var path = window.location.pathname
var cookie = getCookie("lastPath");
console.log(path)
if (path.replace("/", "") === "") {
if (cookie.replace("/", "") !== "") {
console.log(cookie)
document.getElementById("tip").innerHTML = "<a href='" + cookie + "'>跳转到上次进度</a>"
}
} else {
setCookie("lastPath", path)
}
function setCookie(cname, cvalue) {
var d = new Date();
d.setTime(d.getTime() + (180 * 24 * 60 * 60 * 1000));
var expires = "expires=" + d.toGMTString();
document.cookie = cname + "=" + cvalue + "; " + expires + ";path = /";
}
function getCookie(cname) {
var name = cname + "=";
var ca = document.cookie.split(';');
for (var i = 0; i < ca.length; i++) {
var c = ca[i].trim();
if (c.indexOf(name) === 0) return c.substring(name.length, c.length);
}
return "";
}
</script>
</html>