定期将数据加载到表中的最佳方法是什么 [英] What is the best way to periodically load data into table

问题描述

我有一个包含静态表的数据库，需要每周从 CSV 更新.表是 Mysql MyISAM，静态我的意思是它们仅用于只读(显然，从 CVS 更新时除外).

每周有大约 50 个表和总共大约 200MB 的数据需要重新加载.

我可以考虑 3 种方法:

1. 截断表
2. 从文件加载数据

或

1. 为每个表创建一个临时表
2. 在那里加载数据
3. 截断(或删除行?)原始表
4. 从临时表中选择 * 插入原始表.

或

1. 创建 table_new 并在那里加载数据
2. 将原始表重命名为 table_old(或完全删除表)
3. 将 table_new 重命名为原始表

你认为最有效的方法是什么?

解决方案

我更喜欢第 3 种方法，也保留旧表.

<块引用>

1. 创建 table_new
2. 删除 table_old 如果存在
3. 将表重命名为 table_old
4. 将 table_new 重命名为 table

这种方法的优点是快速、安全，对读者的影响较小.新表的创建不会影响对现有表的读取.重命名操作更快(在 myisam 的情况下只是文件重命名)，因此停机时间并不多.所以客户不会受到太大的影响.您还必须保留旧数据，以防新数据出现问题.

由于您不打算在线更新它，我认为如果您使用 myisampack 会很好.

I have a database with static tables which require to be updated from CSV weekly. Tables are Mysql MyISAM and by static i mean they are used for read only (except when updated from CVS, obviously).

There're about 50 tables and in total about 200mb of data to be reloaded weekly.

I can think about 3 ways:

1. Truncate table
2. Load data from files

Or

1. For each table create a temporary table
2. Load data there
3. Truncate (or delete rows?) original table
4. Insert into original table select * from temporary table.

Or

1. Create table_new and load data there
2. Rename original table to table_old (or drop table altogether)
3. Rename table_new into original table

What do you reckon is the most efficient way?

解决方案

I would prefer the 3rd method and also keep the old table.

1. create table_new
2. drop table_old if exists
3. rename table to table_old
4. rename table_new to table

The advantage of this method is that it fast and safe with less effect on the readers. The creation of new table does not affect reads on existing table. The rename operation is faster (just a file rename in case of myisam) so the downtime is not that much. So the clients will not be affected by this that much. You also got to keep the old data in case something is wrong with the new data.

As you are not going to update it online I think it will be good if you do myisampack.

这篇关于定期将数据加载到表中的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！