删除MYSQL数据库中的行后需要进行后期优化 [英] Post optimization needed after deleting rows in a MYSQL Database

问题描述

我有一个当前为10GB的日志表.在过去的两年中，它有很多数据，我真的感觉到现在我不需要那么多了.我是否以为在一个表中包含多年的数据是不好的(较小的表更好)?

I have a log table that is currently 10GB. It has a lot of data for the past 2 years, and I really feel at this point I don't need so much in there. Am I wrong to assume it is not good to have years of data in a table (a smaller table is better)?

我的桌子都具有MYISAM引擎.

My tables all have an engine of MYISAM.

我想删除2014年和2015年的所有数据，很快我将在2016年删除，但是我担心在运行DELETE语句后，究竟会发生什么.我了解是因为这是ISAM，在无法进行写操作的地方会发生锁定?我可能会按月删除数据，并在深夜进行，以将其最小化，因为它是生产数据库.

I would like to delete all data of 2014 and 2015, and soon i'll do 2016, but i'm concerned about after I run the DELETE statement, what exactly will happen. I understand because it's ISAM there is a lock that will occur where no writing can take place? I would probably delete data by the month, and do it late at night, to minimize this as it's a production DB.

我的主要兴趣是，具体是:删除后我应该采取某种措施吗?我是否需要手动告诉MYSQL对表做任何事情，还是MYSQL自己做所有的内部管理，回收所有内容，重新建立索引，并在要删除的40万条记录之后最终优化我的表.

My prime interest, specifically, is this: should I take some sort of action after this deletion? Do I need to manually tell MYSQL to do anything to my table, or is MYSQL going to do all the housekeeping itself, reclaiming everything, reindexing, and ultimately optimizing my table after the 400,000k records I'll be deleting.

谢谢大家！

推荐答案

方案A:使用表的时间序列PARTITIONing，以便将来由于DROP PARTITION而被即时"删除.更多讨论 此处 .仅当您要删除早于X的 all 行时，分区才有效.

Plan A: Use a time-series PARTITIONing of the table so that future deletions are 'instantaneous' because of DROP PARTITION. More discussion here . Partitioning only works if you will be deleting all rows older than X.

计划B:为避免冗长的锁定，请对删除进行分块.参见 此处 .可以选择在其后跟随OPTIMIZE TABLE来回收空间.

Plan B: To avoid lengthy locking, chunk the deletes. See here . This is optionally followed by an OPTIMIZE TABLE to reclaim space.

计划C:只需复制您想要保留的内容，然后放弃其余内容即可.如果只需要保留表的一小部分，则特别好.

Plan C: Simply copy over what you want to keep, then abandon the rest. This is especially good if you need to preserve only a small proportion of the table.

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE ... ;  -- just the newer rows;
RENAME TABLE real TO old, new TO real;   -- instantaneous and atomic
DROP TABLE old;    -- after verifying that all went well.

注意:.MYD文件包含数据；否则，不保存任何数据.它永远不会收缩.删除将在其中留下漏洞.进一步的插入物(和最佳选择)将优先使用孔而不是扩大桌子.计划A和C(但不是计划B)将避免这些漏洞，并真正释放空间.

Note: The .MYD file contains the data; it will never shrink. Deletes will leave holes in it. Further inserts (and opdates) will use the holes in preference to growing the table. Plans A and C (but not B) will avoid the holes, and truly free up space.

这篇关于删除MYSQL数据库中的行后需要进行后期优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！