MySQL 临时表的原理以及优化手段（mysql 临时表的原理以及优化手段是什么）

25-03-31 5

如果您想了解MySQL临时表的原理以及优化手段和mysql临时表的原理以及优化手段是什么的知识，那么本篇文章将是您的不二之选。我们将深入剖析MySQL临时表的原理以及优化手段的各个方面，并为您解答my

如果您想了解MySQL 临时表的原理以及优化手段和mysql 临时表的原理以及优化手段是什么的知识，那么本篇文章将是您的不二之选。我们将深入剖析MySQL 临时表的原理以及优化手段的各个方面，并为您解答mysql 临时表的原理以及优化手段是什么的疑在这篇文章中，我们将为您介绍MySQL 临时表的原理以及优化手段的相关知识，同时也会详细的解释mysql 临时表的原理以及优化手段是什么的运用方法，并给出实际的案例分析，希望能帮助到您！

本文目录一览：

MySQL 临时表的原理以及优化手段（mysql 临时表的原理以及优化手段是什么）
<转>MySQL临时表的简单用法
mysql explain 及常见优化手段
MySQL JOIN关联查询的原理及优化
MySQL 临时表与dstat mysql临时表监控插件开发

MySQL 临时表的原理以及优化手段（mysql 临时表的原理以及优化手段是什么）

1 临时表

sort buffer、内存临时表和join buffer，这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助sql语句的执行的。其中，在排序的时候用到了sort buffer，在使用join语句的时候用到了join buffer。

而使用临时表的时候，Explain的Extra字段中具有Using temporary标记。union、group by、distinct等等查询都有可能使用到临时表。

2 union临时表优化

使用union的时候，就需要用到内存临时表来进行去重。

union语句的执行流程为：

创建一个内存临时表。
执行第一个子查询，得到值，并存入临时表中。
执行第二个子查询：依次拿每一行数据和临时表中的每一行数据比较，如果重复则不会插入，这样就实现了去重的功能
从临时表中按行取出数据，返回结果，并删除临时表。在最后这一步还可以对临时表进行其他操作，比如limit、ORDER BY。

如果使用union all，则不需要去重，也就不需要临时表了。在执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此，除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MysqL会给临时表加上disTINCT选项，这会导致对整个临时我的数据做唯一性检查。这样做的代价非常高。如果不需要这些检查，那么甚至都不需要临时表。

另外，避免对于union之后的结果集进行操作，也能避免临时表的使用，通常需要手工地将MHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化，使得union的结果就是最终的结果（例如，直接将这些子句冗余地写一份到各个子查询)。

3 group by临时表优化

另外一个使用临时表的例子是group by，group by还具有隐藏的排序的语句，即在对某些字段进行分组之后，将数据再根据这些字段进行排序，最后返回排序后的结果。

如下sql：

  select id%10 as m, count(*) as c from t1 group by m;

这个语句的执行流程是这样的：

创建内存临时表，表里有两个字段m和c，主键是m；
扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；
1. 如果临时表中没有主键为x的行，就插入一个记录(x,1);
2. 如果表中有主键为x的行，就将x这一行的c值加1；
遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

此时，Explain的Extra字段中具有Using temporary; Using filesort标记。

如果并不需要对结果进行排序，那可以在sql语句末尾增加order by null，即：

  select id%10 as m, count(*) as c from t1 group by m order by null;

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。如果要处理的数据超过了最大大小，那么MysqL会把内存临时表转成磁盘临时表，而磁盘临时表默认使用的引擎是InnoDB，因此会按主键顺序存储数据，所以最终取出的结果还是默认有序的。

对于Group By的临时表的优化，同样是使用索引：因为如果进行Group By字段是有序的，那么在处理时（比如计算每组数量、个数等等），因为跟着的字段有索引，那么相同的值肯定是在一起的、连续的，所以直接顺序扫描输入的数据即可，不需要临时表，也不需要再额外排序。

总结：

如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；

如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

另外，对于distinct查询来说，如果无法使用索引，则也会使用到临时表，也会进行分组，它和group by的区别是不需要排序。想尝试的小伙伴可以在3A云服务器上部署一套环境，自己学习一下。

<转>MySQL临时表的简单用法

当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后在这些表运行查询。

创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字：

CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
)

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

DROP TABLE tmp_table

如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在，临时表将有必要屏蔽（隐藏）非临时表tmp_table。

如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它：

CREATE TEMPORARY TABLE tmp_table (   
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
) TYPE = HEAP

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。然而，HEAP表与一般的表有些不同，且有自身的限制。详见MySQL参考手册。

正如前面的建议，你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引，临时表可能一点不快。

1. 临时表再断开于mysql的连接后系统会自动删除临时表中的数据，但是这只限于用下面语句建立的表：

定义字段：

CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
)

2.直接将查询结果导入临时表

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

3. 另外mysql也允许你在内存中直接创建临时表，因为是在内存中所有速度会很快，语法如下：

CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
) TYPE = HEAP

4. 从上面的分析可以看出临时表的数据是会被清空的，你断开了连接就会被自动清空，但是你程序中不可能每发行一次sql就连接一次数据库吧(如果是这样的话，那就会出现你担心的问题，如果不是就没有问题)，因为只有断开数据库连接才会被清空数据，在一个数据库连接里面发行多次sql的话系统是不会自动清空临时表数据的。

mysql explain 及常见优化手段

在工作中如果遇到慢sql通常都可以用explain进行解析。

先列一下各个列名以及含义

列名	描述
id	在一个大的查询中每一个查询关键字都对应一个id
select type	select关键字对应的那个查询类型
table	表名
partitions（*）	分配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key len	实际用到的索引长度
ref	当索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
extra	一些额外的信息

id

一个查询语句就会有一个对应的id，如果其内部包含子查询且没有被查询优化器优化掉的情况下就会出现不同的id……

select type

primary 主查询（出现子查询的语句时会区分子和主查询）
subquery （非相关子查询）

非相关子查询得到的结果表会被物化，只需要执行一遍
dependent query（相关子查询）

相关子查询可能会被执行多次嗷
union 联查时
union result 临时表
simple 简单查询
derived派生表

出现在有子查询时，如果为该类型则代表该查询是以物化的方式执行的
materialized

当子查询物化后与外层查询进行连接时的查询类型。

type

system

innodb中不存在，MyISAM、Memory引擎中有，代表精确的查询
const

主键或者唯一二级索引时的常量查询

例如 where a=1,a为主键
eq_ref

代表连接查询时，被驱动表是通过逐渐或者唯一二级索引列等值匹配的方式进行访问的
ref

非主键或者唯一索引时使用索引的查询
ref or null

ref的情况+条件中出现null
index merge

索引合并查询，同时使用了多个索引的情况。
unique subquery

通常出现在相关子查询把in优化为exists而且子查询可以使用主键进行查找时
index subquery

与unique类似，但访问的是普通的索引
range

范围查询时出现
index

查询辅助索引字段时出现，遍历辅助索引值。
all

全表扫描

key lenth

当前使用索引字段的长度

如果索引值可以为空，key length会多存储一个字节

如果为变长字段（例如varchar），需要2个字节的存储空间存储长度。

ref

代表驱动查询的字段。

例如在相关子查询中，子查询的驱动字段应该为主查询中表的某个值。

filtered

通过该索引查询到的数据满足非索引条件的数据所占的百分比。

select * from table where index_case and non_index_case;

假设符合index_case 的值为100个（rows=100），但是符合non_index_case的值为20个，那么filtered就为20。

注：为估算值。

extra

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

no tables used

当查询语句的没有FROM子句时将会提示该额外信息。
impossible where

where子句永远为false
no matching min/max row

查询列表中有min或者max聚集函数，但是并没有where子句中的搜索条件记录时会提示该额外信息
using index

查询列表以及搜索条件中只包含属于某个索引的列，既索引覆盖
using index condition

搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件
using where

全表扫描并且where中有针对该表的搜索条件
using join buffer（Block Nested Loop）

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度时就分配一块join buffer内存块来加快查询速度。
using filesort

多数情况下排序操作无法用到索引，只能在内存中（记录较少时）或者磁盘中进行排序，这种在情况统称为文件排序。
using temporary

在诸多查询过程中，可能会借助临时表来完成一些查询功能，比如去重、排序之类的，比如我们在执行许多包含distinct、group by、union等子句的查询过程中，如果不能有效利用索引完成查询，mysql可能通过建立内部临时表来执行查询。
Start temporary, End temporary

子查询可以优化成半连接，但通过了临时表进行去重
firstmatch（table_name）

子查询时可以优化成半连接，但直接进行数据比较去重

index hint

use index

select * from table use index (index_name,index_name2) where case;

强制查询优化器在指定的索引中做选择。

force index

select * from table force index (index_name) where case;

强制查询优化器使用该索引

ignore index

select * from ignore index (index_name) where case;

强制忽略该索引。

小结

性能按照type排序

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range >index > ALL

性能按照extra排序

Using index：用了覆盖索引
Using index condition：用了条件索引（索引下推）
Using where：从索引查出来数据后继续用where条件过滤
Using join buffer (Block Nested Loop)：join的时候利用了join buffer（优化策略：去除外连接、增大join buffer大小）
Using filesort：用了文件排序，排序的时候没有用到索引
Using temporary：用了临时表（优化策略：增加条件以减少结果集、增加索引，思路就是要么减少待排序的数量，要么提前排好序）
Start temporary, End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表来去重
FirstMatch(tbl_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重

常见优化手段

SQL语句中IN包含的值不应过多，不能超过200个，200个以内查询优化器计算成本时比较精准，超过200个是估算的成本，另外建议能用between就不要用in，这样就可以使用range索引了。
SELECT语句务必指明字段名称：SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。
当只需要一条数据的时候，使用limit 1
排序时注意是否能用到索引
使用or时如果没有用到索引，可以改为union all 或者union
如果in不能用到索引，可以改成exists看是否能用到索引
使用合理的分页方式以提高分页的效率
不建议使用%前缀模糊查询
避免在where子句中对字段进行表达式操作
避免隐式类型转换
对于联合索引来说，要遵守最左前缀法则
必要时可以使用force index来强制查询走某个索引
对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。
尽量使用inner join，避免left join，让查询优化器来自动选择小表作为驱动表
必要时刻可以使用straight_join来指定驱动表，前提条件是本身是inner join

MySQL JOIN关联查询的原理及优化

1 关联查询的执行

关联查询的执行过程是：先遍历关联表t1（驱动表，全表扫描），然后根据从表t1中取出的每行数据中的a值，去表t2（被关联表，被驱动表）中查找满足条件的记录，可以走t2的索引搜索。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ。在join语句的执行流程中，驱动表是走全表扫描，而被驱动表是走索引树搜索。

假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是 N + N2log2M。显然，N对扫描行数的影响更大，因此应该让小表来做驱动表：N扩大1000倍的话，扫描行数就会扩大1000倍；而M扩大1000倍，扫描行数扩大不到10倍。

结论：如果使用join语句的话，需要让小表做驱动表，并且被驱动表的关联字段应该建立索引。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引，即在被驱动的表的关联字段简历索引。

2 没有索引的算法

如果，被驱动表的关联字段没有使用索引，那么MySQL将使用另一种Block Nested-Loop Join算法。

把表t1的数据读入线程内存join_buffer中，这只会将查询需要返回的列放入，如果我们的语句中写的是select *，就会把整个表t1放入了内存；
扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

这个过程的流程图如下：

对应地，这条SQL语句的explain结果的Extra字段中将会展示：Block Nested Loop。在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是量表的数据总和M+N。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：M* N次。

假设小表的行数是N，大表的行数是M，那么在这个算法里：

两个表都做一次全表扫描，所以总的扫描行数是M+N；
内存中的判断次数是M*N，虽然不需要读盘，但是需要占用大量CPU进行计算。

可以看到，调换这两个算式中的M和N没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。

join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是将t1的数据分段放入、比较，假设表t1被分成了两次放入join_buffer中，那么会导致表t2会被扫描两次。虽然分成两次放入join_buffer，但是内存中判断等值条件的次数还是不变的，依然是M*N次。

假设，驱动表的数据行数是N，需要分K段才能完成算法流程，K大于等于1，被驱动表的数据行数是M。注意，这里的K不是常数，N越大K就会越大。

所以，在这个算法的执行过程中：

扫描行数是 N+K*M；
内存判断 N*M次。

可以看到，如果join_buffer_size没有足够大（这是常见的情况），那么N越小，这样K就更小，扫描的行数才会更少，因此仍然应该让小表当驱动表。而且K也是影响扫描行数的关键因素，这个值越小越好，如果N不变，那么影响K的就是join_buffer_size的大小。join_buffer_size越大，一次可以放入的行越多，分成的段数K也就越少，对被驱动表的全表扫描次数就越少。

因此，如果你的join语句很慢，除了让小表当驱动表，还有就把join_buffer_size改大。

如果确定“小表”呢？除了总行数之外，还应该是两个表按照各自的条件过滤，过滤完成之后，再计算参与join的各个字段的总数据量（因为还要放入内存中），数据量小的那个表，就是“小表”，应该作为驱动表。

实际在查询优化时，如果join不是使用的Index Nested-Loop Join算法，则应该尽量改为使用该算法。

到此这篇关于MySQL JOIN关联查询的原理及优化的文章就介绍到这了,更多相关MySQL JOIN关联查询内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

您可能感兴趣的文章:

SQL语句多表联查的实现方法示例
MySQL详细讲解多表关联查询
SQL- join多表关联问题

MySQL 临时表与dstat mysql临时表监控插件开发

MySQL在执行SQL查询时可能会用到临时表，一般而言，用到临时表就意味着性能较低。MySQL临时表可分：内存临时表和磁盘临时表，磁盘

临时表简介

MySQL在执行SQL查询时可能会用到临时表，一般而言，用到临时表就意味着性能较低。MySQL临时表可分：内存临时表和磁盘临时表，，磁盘临时表使用MyISAM存储，而内存临时表则使用Memory引擎。MySQL使用内存临时表来存放查询的中间结果集，如果中间结果集大于临时表的容量设定，又或者中间结果集含TEXT或BLOB列类型字段，则MySQL会把其转化为磁盘临时表。Linux平台缺省是/tmp,/tmp目录小的系统要注意啦。

临时表特性

下面列举几个内存临时表的特性

1.表结构(.frm)在磁盘，数据在内存

2.缺省使用哈希索引

3.定长存储(BTW：即使是varchar也是定长)

4.只支持表锁

5.不支持TEXT和BLOB列类型