MySQL删除语句优化 [英] MySQL delete statement optimization

问题描述

我有一些删除查询要对一些非常大的表（~100 GB）运行，我想尽可能地优化它们：

i have some delete queries to run against some pretty huge table (~100 GB), and i want to optimize them as much as possible:

delete from table1 where column1 < date_sub(now(), interval 100 hour);

column1是 datetime 列，我假设为此列创建索引将加快删除速度。除此之外，我能在这做什么吗？将使用 date_sub（）函数减慢查询速度？我应该在运行查询之前计算该值吗？

column1 is a datetime column, i assume making an index for this column will speed up the deletions. besides that, anything i can do here? will using the date_sub() function slow down the query? should i calculate that value before running the query?

delete from table2 where column2 = x;

column2是table2的主键，因此它已经是根据mysql文档的索引。我的问题是：索引类型是 PRIMARY ，是否与 INDEX 相同？我必须制作另一种 INDEX 的索引来加速吗？

column2 is the primary key for table2, so it's already an index according to the mysql documentation. my question is: the index kind is PRIMARY, is that same as the INDEX? do i have to make another index of the kind INDEX for speeding up?

delete from table3 where column3 = y;

table3有一个复合主键，即column3和column4。所以我有一个主键索引，但由于删除查询不使用column4，我应该为column3创建一个单独的索引吗？或者组合的主键会这样做吗？

table3 has a compound primary key, which is column3 and column4. so i have an primary key index, but since the delete query doesn't use column4, should i make a separate index just for column3? or the combined primary key would do it?

我想这些都是非常基本的问题，但我找不到具体针对我的情况的明确答案，所以任何帮助都会不胜感激！

i guess these are pretty basic questions, but i couldn't find a definite answer specific to my situation, so any help would be appreciated!

推荐答案

如果您的 DELETE 旨在消除一个伟大的该表中的大多数行，人们经常做的一件事就是将要保留的行复制到重复的表中，然后使用 DROP TABLE 或 TRUNCATE 更快地消灭原始表格。

If your DELETE is intended to eliminate a great majority of the rows in that table, one thing that people often do is copy just the rows you want to keep to a duplicate table, and then use DROP TABLE or TRUNCATE to wipe out the original table much more quickly.

索引可能有助于找到你需要删除的行，但是删除需要更新索引。删除大量行后，索引可能不平衡，需要使用 OPTIMIZE TABLE 进行一些维护。

An index may help to find the rows you need to delete, but deleting requires updating the index. After deleting a lot of rows, the index may be imbalanced and requires some maintenance with OPTIMIZE TABLE.

DATE_SUB（） function是一个常量表达式（它不会逐行变化），因此查询优化器应该足够聪明，可以将其分解并执行一次计算。

The DATE_SUB() function is a constant expression (it does not vary row by row) so the query optimizer should be smart enough to factor it out and perform the calculation once.

您不需要为主键创建额外的索引。主键约束隐式地创建一个索引，该索引提供与非主键索引相同的好处。

You don't need to create an extra index for a primary key. The primary key constraint implicitly creates an index that gives the same benefit as a non-primary key index.

复合索引可能与单列索引一样有用，只要您的搜索条件引用索引的最左侧列。 可能的警告是由于各个索引节点较大，因此需要更多内存来缓存索引，但这是一个足够小的因素，我不会创建整个其他单列索引。

A compound index is probably just as useful as a single-column index, provided your search criteria reference the leftmost column(s) of the index. The "probably" caveat is due to the individual index nodes being larger and so it takes more memory to cache the index, but this is a small enough factor that I wouldn't create a whole other single-column index.

这篇关于MySQL删除语句优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！