SQL Server DELETE 使用索引速度较慢 [英] SQL Server DELETE is slower with indexes

问题描述

我有一个 SQL Server 2005 数据库，我尝试在适当的字段上放置索引，以加快从具有数百万行的表 (big_table) 中DELETEcode> 只有 3 列)，但现在 DELETE 的执行时间更长！(例如 1 小时对 13 分钟)

I have an SQL Server 2005 database, and I tried putting indexes on the appropriate fields in order to speed up the DELETE of records from a table with millions of rows (big_table has only 3 columns), but now the DELETE execution time is even longer! (1 hour versus 13 min for example)

我有一个表之间的关系，我过滤我的 DELETE 的列在另一个表中.例如

I have a relationship between to tables, and the column that I filter my DELETE by is in the other table. For example

DELETE FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)

顺便说一句，我也试过:

Btw, I've also tried:

DELETE FROM big_table
WHERE EXISTS
(SELECT 1 FROM small_table
WHERE small_table.id_product = big_table.id_product
AND small_table.id_category = 1)

虽然它的运行速度似乎比第一个略快，但使用索引仍然比不使用索引慢很多.

and while it seems to run slightly faster than the first, it's still a lot slower with the indexes than without.

我在这些字段上创建了索引:

I created indexes on these fields:

1. big_table.id_product
2. small_table.id_product
3. small_table.id_category

我的 .ldf 文件在 DELETE 期间增长了很多.

My .ldf file grows a lot during the DELETE.

为什么当我的表上有索引时，我的 DELETE 查询会变慢?我认为它们应该运行得更快.

Why are my DELETE queries slower when I have indexes on my tables? I thought they were supposed to run faster.

更新

好的，共识似乎是索引会减慢一个巨大的DELETE，因为索引必须更新.虽然，我仍然不明白为什么它不能一次 DELETE 所有行，而只在最后更新一次索引.

Okay, consensus seems to be indexes will slow down a huge DELETE becuase the index has to be updated. Although, I still don't understand why it can't DELETE all the rows all at once, and just update the index once at the end.

我从一些阅读中得出的印象是，通过更快地搜索 WHERE 子句中的字段，索引可以加快 DELETE 的速度.

I was under the impression from some of my reading that indexes would speed up DELETE by making searches for fields in the WHERE clause faster.

Odetocode.com 说:

索引在 DELETE 和 UPDATE 命令中搜索记录时与在 SELECT 语句中搜索记录时一样有效."

"Indexes work just as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements."

但是在文章的后面，它说太多的索引会影响性能.

But later in the article, it says that too many indexes can hurt performance.

bobs 问题的答案:

1. 表中有 5500 万行
2. 正在删除 4200 万行
3. 类似的 SELECT 语句不会运行(抛出System.OutOfMemoryException"类型的异常)

1. 55 million rows in table
2. 42 million rows being deleted
3. Similar SELECT statement would not run (Exception of type 'System.OutOfMemoryException' was thrown)

我尝试了以下 2 个查询:

I tried the following 2 queries:

SELECT * FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)

SELECT * FROM big_table
INNER JOIN small_table
ON small_table.id_product = big_table.id_product
WHERE small_table.id_category = 1

在运行 25 分钟后都失败，并显示来自 SQL Server 2005 的此错误消息:

Both failed after running for 25 min with this error message from SQL Server 2005:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

数据库服务器是一台较旧的双核 Xeon 机器，具有 7.5 GB 内存.这是我的玩具测试数据库 :) 所以它没有运行其他任何东西.

The database server is an older dual core Xeon machine with 7.5 GB ram. It's my toy test database :) so it's not running anything else.

在我CREATE索引之后，我是否需要对索引做一些特殊的处理以使其正常工作?

Do I need to do something special with my indexes after I CREATE them to make them work properly?

推荐答案

索引使查找速度更快 - 就像书后的索引一样.

Indexes make lookups faster - like the index at the back of a book.

更改数据的操作(如 DELETE)较慢，因为它们涉及操作索引.考虑一下书后的相同索引.如果您添加、删除或更改页面，您将有更多工作要做，因为您还必须更新索引.

Operations that change the data (like a DELETE) are slower, as they involve manipulating the indexes. Consider the same index at the back of the book. You have more work to do if you add, remove or change pages because you have to also update the index.

这篇关于SQL Server DELETE 使用索引速度较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！