定期将数据加载到表中的最佳方法是什么

本文介绍了定期将数据加载到表中的最佳方法是什么的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

我可以考虑 3 种方法:

截断表

从文件加载数据

或

为每个表创建一个临时表

在那里加载数据

截断(或删除行?)原始表

从临时表中选择 * 插入原始表.

或

创建 table_new 并在那里加载数据

将原始表重命名为 table_old(或完全删除表)

将 table_new 重命名为原始表

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

解决方案

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

创建 table_new

删除 table_old 如果存在

将表重命名为 table_old

将 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:

Truncate table

Load data from files

Or

For each table create a temporary table

Load data there

Truncate (or delete rows?) original table

Insert into original table select * from temporary table.

Or

Create table_new and load data there

Rename original table to table_old (or drop table altogether)

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.

create table_new

drop table_old if exists

rename table to table_old

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.