mysql临时表怎么优化 mysql使用临时表提高查询效率

MySQL 5.7临时表空间怎么玩才能不掉坑里

MySQL 5.7临时表空间怎么玩才能不掉坑里

1、关掉mysql实例

2、cp big.ibd /new/big.ibd

3、rename big.ibd big.ibd.remove

4、ln -s big.ibd /new/big.ibd

5、chow -R mysql:mysql /new/big.ibd

6、启动数据库,检查是否异常

7、删掉 removemysql临时表怎么优化的文件.

mysql 参数调优（10）之 tmp_table_size 优化临时表

tmp_table_size默认16M。tmp_table_size如果过小，存不下了就会存到磁盘上。对于group by会有性能影响。

下面的sql EXPLAIN 如下，出现了Using temporary。表示查询会利用临时表。

在默认tmp_table_size大小16M下执行：

查看临时表统计信息，Created_tmp_disk_tables 为0，Created_tmp_tables 为1表示上诉sql执行后生产了一张内存里的临时表。

将tmp_table_size 调从16M调整为16K

再次执行,查询时间从4变成了18秒

重新统计

再次查看status，这次有在磁盘上创建1个临时表。

设置为32M

Percona Server中的临时表信息会记录到慢查询日志

由于MySQL慢查询日志里没有使用临时表的信息，这就给我们诊断性能问题带来了一些不便，第三方的版本如Percona Server，在慢查询里可以有更详细的信息，将会记录临时表使用的情况，从而有助于我们诊断和调优。

mysql8中对临时表有较大的优化

临时表引擎使用innodb（default 磁盘）和temptable(default 内存)

sql 如何用临时表优化性能

InnoDB 类型mysql临时表怎么优化的临时表存在mysql临时表怎么优化的潜在问题

尽管使用 InnoDB 是性能最佳的，但可能会出现新的潜在问题。在某些特定情况下，您可能会出现磁盘耗尽和服务器中断。

与数据库中的任何其他 InnoDB 表一样，临时表具有自己的表空间文件。新文件与通用表空间一起位于数据目录中，名称为 ibtmp1。它存储所有 tmp 表。不运行手动运行 OPTIMIZE TABLE，表空间文件就会不断增长。如果你不能使用 OPTIMIZE，那么唯一能将 ibtmp1 大小缩小为零的方法，就是重新启动服务器。幸运的是，即使文件无法减小，在执行查询后，临时表也会自动删除，表空间可回收使用。现在，mysql临时表怎么优化我们想一想以下情境：

存在未优化的查询，需要在磁盘上创建非常大的的临时表

存在优化的查询，但他们正在磁盘上创建非常大的临时表，因为你正在对此数据集进行计算（统计，分析）

高并发连接时，运行相同的查询，伴随临时表的创建

没有很多可用空间

在这些情况下，文件 ibtmp1 大大增加，很容易耗尽可用空间。这种情况每天发生几次，并且必须重启服务器才能完全缩小 ibtmp1 表空间。使用不可收缩的文件可以轻松耗尽磁盘空间mysql临时表怎么优化！

那么，如何避免磁盘耗尽和中断呢？

简单的解决方案：使用更大的磁盘

虽然可以暂时解决问题，但这不是最佳解决方案。实际上，您可以通过逐步增加磁盘大小，来猜测具体需要的空间。如果环境位于云中，或者在非常大的虚拟平台，这很容易实现。但是使用这种解决方案，您可能会面临不必要的开支。您还可以通过设置以下配置变量将 ibtmp1 文件移动到专用大型磁盘上：    [mysqld]    innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend

需要重启 MySQL 。注意，必须将路径指定为相对于数据目录。

设置 ibtmp1 大小的上限

例如：    [mysqld]    innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

在这种情况下，文件不能超过 10GB。可以降低宕机概率，但也是一个危险的解决方案。当数据文件达到最大值时，会查询失败并显示一个错误，提示表已满。

退回 MyISAM 将临时表存储在磁盘上

这个解决方案似乎违反直觉，但它可能是快速避免中断的最佳方法，并保证使用所有需要的临时表。

   internal_tmp_disk_storage_engine = MYISAM

由于变量是动态的，您也可以在运行时设置它：    SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

回到 MyISAM，您将大大降低写满磁盘空间的可能性。实际上，临时表将创建到不同的文件中，并在查询结束时立即删除。

虽然总是有可能看到相同的问题，以防你可以在同一时间运行查询或非常接近。在我的实际案例中，这是避免所有中断的解决方案。

优化你的查询

在将存储引擎退回到 MyISAM 以减轻中断发生后，必须花时间分析查询。目标是减小磁盘上临时表的大小。本文的目的不是解释如何调查查询，而是可以依赖慢速日志，像 pt-query-digest 和 EXPLAIN 这样的工具。一些技巧：

在表上创建缺少的索引

如果不需要，可以在查询中添加更多过滤条件以更少收集的数据

重写查询以优化执行计划

可以在应用程序中使用队列管理器来序列化它们的执行或减少并发性

但希望在所有优化之后，您可以返回将临时存储引擎设置为 InnoDB 以获得更好的性能。

结论

有时这些改进会产生意想不到的副作用。用于磁盘上临时表的 InnoDB 存储引擎是一个很好的改进，但在某些特定情况下，例如，如果您有未优化查询和很少的可用空间，则可能因“磁盘已满”错误而中断。将 tmp 存储引擎退回到 MyISAM 是避免中断的最快方法，但是为mysql临时表怎么优化了返回到 InnoDB，查询的优化是更重要的事情。更大或专用的磁盘也可能有所帮助。但这是一个微不足道的建议。

mysql临时表怎么优化的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql使用临时表提高查询效率、mysql临时表怎么优化的信息别忘了在本站进行查找喔。