如何在 MySQL 中按天对 MyISAM 表进行分区 [英] How to partition a MyISAM table by day in MySQL

问题描述

我想将过去 45 天的日志数据保存在 MySQL 表中以用于统计报告目的.每天可能是 20-3000 万行.我计划创建一个平面文件并每天使用加载数据 infile 来获取数据.理想情况下，我希望每天都在自己的分区上，而不必每天编写脚本来创建分区.

I want to keep the last 45 days of log data in a MySQL table for statistical reporting purposes. Each day could be 20-30 million rows. I'm planning on creating a flat file and using load data infile to get the data in there each day. Ideally I'd like to have each day on it's own partition without having to write a script to create a partition every day.

在 MySQL 中有没有一种方法可以说每天自动获得自己的分区?

Is there a way in MySQL to just say each day gets it's own partition automatically?

谢谢

推荐答案

我在寻找其他东西时偶然发现了这个问题，想指出 MERGE 存储引擎 (http://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html).

I have stumbled on this question while looking for something else and wanted to point out the MERGE storage engine (http://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html).

MERGE 存储或多或少是一个指向多个表的简单指针，并​​且可以在几秒钟内重做.对于骑行日志，它可以非常强大！这是我要做的:

The MERGE storage is more or less a simple pointer to multiple tables, and can be redone in seconds. For cycling logs, it can be very powerfull! Here's what I'd do:

每天创建一张表，使用 LOAD DATA 作为 OP 提到的来填充它.完成后，删除 MERGE 表并重新创建它，包括该新表，同时忽略最旧的表.完成后，我可以删除/归档旧表.这将允许我快速查询特定的日期，或者全部，因为原始表和 MERGE 都是有效的.

Create one table per day, use LOAD DATA as OP mentionned to fill it up. Once it is done, drop the MERGE table and recreate it including that new table while ommiting the oldest one. Once done, I could delete/archive the old table. This would allow me to rapidly query a specific day, or all as both the orignal tables and the MERGE are valid.

CREATE TABLE logs_day_46 LIKE logs_day_45 ENGINE=MyISAM;
DROP TABLE IF EXISTS logs;
CREATE TABLE logs LIKE logs_day_46 ENGINE=MERGE UNION=(logs_day_2,[...],logs_day_46);
DROP TABLE logs_day_1;

请注意，MERGE 表与 PARTIONNED 表不同，它们具有一些优点和不便之处.但是请记住，如果您尝试从所有表中聚合，它会比所有数据都在一个表中时慢(分区也是如此，因为它们在后台基本上是不同的表).如果您主要在特定日期查询，则需要自己选择表，但如果分区是在日期值上完成的，MySQL 将自动抓取正确的表，这可能会更快、更容易编写.

Note that a MERGE table is not the same as a PARTIONNED one and offer some advantages and inconvenients. But do remember that if you are trying to aggregate from all tables it will be slower than if all data was in only one table (same is true for partitions, as they are basically different tables under the hood). If you are going to query mostly on specific days, you will need to choose the table yourself, but if partitions are done on the day values, MySQL will automatically grab the correct table(s) which might come out faster and easier to write.

这篇关于如何在 MySQL 中按天对 MyISAM 表进行分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！