持久的临时表? [英] A persistent temporary table?

问题描述

现在，我在选择查询中使用临时表来加快执行速度.它们是在我每次执行查询时创建的.

Right now, I'm using temporary tables in my select queries to speed up the execution. They are created every time I execute the query.

在当前情况下，每天仅用一次新数据更新表，因此我在考虑创建一个持久性表，而不是使用MySQL的CREATE TEMPORARY TABLE语句，在某种意义上，该表将是 temporary ，因为它将在一天后被删除并重新创建.完成主表更新后，我就可以用 temporary 数据填充它.

In my current situation, the tables are updated with new data only once per day, so I was thinking that instead of using MySQL's CREATE TEMPORARY TABLE statement, I'll create a persistent table, which in a sense would be temporary since it'd be deleted and recreated after a day. And I could fill it up with the temporary data just after I've finished updating the main tables.

或者，InnoDB's data buffer是否足够聪明，可以为临时表本身缓存数据?

Or, will InnoDB's data buffer will be smart enough to cache the data for temporary tables itself?

还是还有另一种方式来缓存临时表?

Or is there another way for caching the temporary tables?

我还将使用AJAX加载的数据一起发送适当的缓存头，以减少服务器负载，而AJAX查询约占发送到mysql的读取请求的70％.

I'm also sending along appropriate cache headers with the data loaded using AJAX to reduce server load, and AJAX queries make up about 70% of the read requests sent to mysql.

我在想的只是磁盘空间的浪费，而表永远都不打算以这种方式使用，或者这对我的情况来说真的是个好主意吗?

Is what I'm thinking just a plain waste of disk space and tables are never meant to be used in this fashion, or is it a really bright idea for my situation?

推荐答案

我最近遇到了一个类似的问题，由于不断重复使用，CREATE TEMPORARY TABLE的成本很高.我还使用了Barranka描述的解决方案(创建一次并在完成时或在重用之前截断).

I came across a similar issue recently where the CREATE TEMPORARY TABLE came at a significant cost due to continual reuse. I also used the solution that Barranka describes (create once and truncate when finished or before reuse).

为了进一步提高性能，我使用了在RAM磁盘(ramfs)上创建的InnoDB表.这样就可以以很少的IO成本获得InnoDB存储引擎的所有优势.与使用MEMORY存储引擎相比，这是一个更好的解决方案，根据Oracle支持，MEMORY存储引擎仅可用于旧版应用程序，并且在一段时间内未得到改进或扩展.

To increase performance even more I used InnoDB tables that were created on a RAM disk (ramfs). This gives all the benefits of the InnoDB storage engine with very little IO cost. This is a better solution than using the MEMORY storage engine which, according to Oracle support, is only available for legacy applications and has not been improved or extended for some time.

这篇关于持久的临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！