英文:
Is there any way to use MySQL Temp Tables in Go?
问题
我有一些创建临时表的存储过程。我想执行一个与这些临时表进行连接的查询。
问题是，使用Golang的database/sql设计，为了确保后续查询使用相同的连接，唯一的方法是创建一个事务。
如果我为了访问临时表而将大部分的SELECT语句包装在一个事务中，我是否会遇到麻烦？我知道这样做会损失一些性能/可扩展性，因为我将保持连接池中的连接，而不是在查询之间释放它们。但我想知道是否会出现锁定或其他严重问题。
我之所以需要这样做，是因为我的许多表的MySQL执行计划非常糟糕（我在大表之间进行了几次连接）。我希望执行一些中间查询，并将它们的结果存储在临时表中，以避免这个问题。
英文:
I have stored procedures that create temp tables. I would like to then execute a query that joins with these temp tables.
The problem is that with Golang's database/sql design, the only way to ensure you get the same connection for subsequent queries is to create a transaction.
Am I asking for trouble if I wrap the majority of my SELECTs in a transaction for the purpose of accessing a temp table? I understand that I will lose some performance/scalability because I'll be holding onto connections from the pool rather than allowing them to go back between queries. But I'm wondering if I'll start seeing locking or other serious issues with this strategy.
The reason I need to do this is because the MySQL execution plan for many of my tables is very poor (I'm doing several joins across large tables). I'd like to execute some intermediate queries and store their results in temp tables to avoid this issue.
答案1
得分: 7
您可以创建自己的伪临时表，可以被多个进程和连接访问。
思路是简单地创建内存表，运行操作，然后清理。
您可以使用以下SQL语句创建内存表：
CREATE TABLE mydb.temp_32rfd293 (
id int(11) auto_increment,
content varchar(50),
PRIMARY KEY (`id`)
) ENGINE=MEMORY;
执行一些有用的操作，然后使用以下语句删除它：
DROP TABLE temp_32rfd293;
定期事件以删除超过1天的mydb.temp_%表
您可能希望清理偶尔被遗弃的临时表，您可以在MySQL中创建一个定期事件来执行此操作。如果选择这样做，请考虑使用专用模式用于临时表，以防止意外删除。
注意：您需要在my.ini中设置event_scheduler=ON才能使其工作。
DELIMITER $$
CREATE
EVENT `cleanup_custom_temps`
ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 01:00:00'
DO BEGIN
---------------------------------------------------
-- Process to delete all tables with
-- prefix 'temp_', and older than 1 day
SET @tbls = (
SELECT GROUP_CONCAT(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME LIKE 'temp_%'
AND CREATE_TIME < NOW() - INTERVAL 1 DAY
);
SET @delStmt = CONCAT('DROP TABLE ', @tbls);
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
---------------------------------------------------
END */$$
DELIMITER ;
英文:
You can create your own pseudo temp tables that can be accessed by multiple processes, and connections.
The idea is to simply create memory tables, run your operations, and cleanup afterwards.
You can create a memory table with the following sql;
CREATE TABLE mydb.temp_32rfd293 (
id int(11) auto_increment,
content varchar(50),
PRIMARY KEY (`id`)
) ENGINE=MEMORY;
Do something useful, then drop it using;
DROP TABLE temp_32rfd293:
Scheduled event to remove mydb.temp_% tables older than 1 day
You'll want to clean up the occasional abandoned temp table, you can create a scheduled event in mysql to do this. If you choose to do this consider using a dedicated schema for temp tables to prevent accidental removals.
Note: You need event_scheduler=ON in your my.ini for this to work.
DELIMITER $$
CREATE
EVENT `cleanup_custom_temps`
ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 01:00:00'
DO BEGIN
---------------------------------------------------
-- Process to delete all tables with
-- prefix 'temp_', and older than 1 day
SET @tbls = (
SELECT GROUP_CONCAT(TABLE_NAME)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME LIKE 'temp_%'
AND CREATE_TIME < NOW() - INTERVAL 1 DAY
);
SET @delStmt = CONCAT('DROP TABLE ', @tbls);
PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
---------------------------------------------------
END */$$
DELIMITER ;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论