MySQL删除语句优化

Question

我有一些需要针对非常庞大的表（约100 GB）运行的删除查询，并且我希望尽可能地优化它们：

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

column1 是一个 datetime 类型的列，我认为为此列创建索引将加快删除速度。除此之外，还有什么可以做的吗？使用 date_sub() 函数会减慢查询速度吗？在运行查询之前应该计算该值吗？

delete from table2 where column2 = x;

column2是table2的主键，因此根据MySQL文档，它已经是一个索引。我的问题是：索引类型是PRIMARY，这与INDEX相同吗？我是否需要创建另一种INDEX来加速？

delete from table3 where column3 = y;

table3有一个复合主键，即column3和column4。所以我有一个主键索引，但由于删除查询不使用column4，我是否应该为column3单独创建一个索引？还是使用组合主键就可以了？

我想这些都是非常基本的问题，但我找不到特定于我的情况的明确答案，所以任何帮助都将不胜感激！

- fei

---

你的第一步将是在那个删除语句上使用 EXPLAIN 并找出它在做什么。如果必要，可以将其粘贴进来。 - Schwern

EXPLAIN 仅适用于 SELECT 语句（目前为止）。 - Bill Karwin

我猜测column1是一个DATETIME而不是一个DATE？否则在它上面使用小时间隔会很愚蠢。 - Schwern

好的，那么对等的SELECT语句进行一个EXPLAIN。 EXPLAIN SELECT * FROM table1 where column1 < date_sub(now(), interval 100 hour); - Schwern

Answer 1

如果你的DELETE操作旨在消除该表中大部分行，人们通常会将只想保留的行复制到一个副本表中，然后使用DROP TABLE或TRUNCATE更快地清空原始表。

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

DATE_SUB()函数是一个常量表达式（它不会按行变化），因此查询优化器应该足够聪明以将其提取出来并执行一次计算。

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

复合索引可能与单列索引一样有用，前提是你的搜索条件引用了索引的左侧列。 "可能"这个限定语是由于各个索引节点较大，因此需要更多内存缓存索引，但这只是一个小因素，我不会创建一个完整的单列索引。

- Bill Karwin

Answer 2

我假设为这一列建立索引会加快删除操作。

不正确，因为同样需要更新该索引才能在将来的使用中发挥任何价值。

使用date_sub()函数会减慢查询吗？

不会，它很好，因为它不基于列值。对列值执行的函数确保如果存在于该列上的索引，则无法使用。

索引类型是“PRIMARY”，这与“INDEX”相同吗？

是的，主键部分确保该索引中的值也是唯一的。

我还需要创建另一种“INDEX”以加速吗？

不需要，MySQL还限制了可以在单个表上定义的索引的总大小，具体取决于类型。 InnoDB表的指定索引前缀的限制为767字节；MyISAM表为1,000字节。

测试这两种设置并决定。我个人认为不需要额外的索引。

- OMG Ponies