Some minor aspects that are easily overlooked in SQL Development (6)

Purpose: The Impact of indexes in the condition column on the delete operation of the database.

Cause: Today, I discussed the impact of indexes on Delete in the msngroup of the Beijing club in the blog Park. Afterwards, I felt very ashamed because of my mistakes. The general topic is like this, not the original saying:

[Discussion:]No index is created on the delete course where classid = 500001 classid. To improve the deletion efficiency, will creating a non-clustered index on the classid increase the deletion efficiency?

My opinion at that time: No.

My reason at that time: When deleting a database, if a non-clustered index is created on the classid, search for data based on the non-clustered index and find the index row, the process of finding and deleting a real physical data row based on the clustered index address next to the index row does not seem useful. You can only create clustered indexes to improve the deletion efficiency, because if classid is a clustered index, then the clustered index is deleted directly, which is the most efficient at this time.

After work, I thought about this topic again and felt that my views were self-contradictory. Since I knew that when I deleted the topic, I tried to apply an existing index to the condition column, why is it invalid to create a non-clustered index? If the data in the table is quite large and there is no index on the classid, a table scan is required to search for the data, and the table scan speed is quite slow. To prove this problem, I made a deliberate experiment.

Create two course and course2 tables. The statement is as follows. The only difference between them is the index,A non-clustered index is created on the classid of the Course table, but no index is created on course2..

Create Table [DBO]. [course] (
[ID] [ Int ] Identity ( 1 , 1 ) Not null,
[Scoursename] [nchar] ( 10 ) Collate chinese_prc_ci_as null,
[Classid] [ Int ] Null,
Constraint [pk_ckh] primary key clustered
(
[ID] ASC
) With (ignore_dup_key = Off) on [primary]
) On [primary]
-- Create an index
Create index ix_classid
On course (classid)

Create Table [DBO]. [course2] (
[ID] [Int] Identity (1,1) Not null,
[Scoursename] [nchar] (10) Collate chinese_prc_ci_as null,
[Classid] [Int] Null,
Constraint [pk_ckh2] primary key clustered
(
[ID] ASC
) With (ignore_dup_key=Off) on [primary]
) On [primary]

Experiment process:

Step 1: insert 1000 rows of equivalent data into the two tables respectively, and then delete 500th records.

Delete Course
Where Classid = 500
Delete course2
Where Classid = 500

The execution plan is shown in the figure below: the database is divided into three parts during deletion:

1: Find the data row to be deleted;

2: contains a top operation.

3: delete the clustered index.

Difference 1: because an index is created on the classid of the Course table, we use pk_classid to search for the index. Because the classid of the course2 table does not have any index, in order to find the data row to be deleted, you can only search by clustered index. This is actually a full table scan.

Difference 2: The system overhead is different. unexpectedly, the result shows that the daytime view is correct,The cost of a coure table with an index is larger than that of course2 without an index.

The reason for the difference is as follows:Let's take a look at the specific content of the clustered index deletion. The following figure shows the execution plan of the Course table, which created a non-clustered index on the condition column classid, after the index is deleted, it needs to maintain the index pk_classid, which occupies part of the system overhead. Course2, which does not create an index, is dominant because it does not have additional overhead for index maintenance.

 

Step 2: insert 10000 rows of equivalent data into the two tables, and then delete 5,000th records.

The difference is the same as the first step.Is my opinion true?

Step 3: insert 100000 rows of equivalent data into the two tables, and then delete 50,000th records. The execution plan is shown in the figure below:

Difference 1: The difference 1 with the previous two steps.

Difference 2:The system overhead is different. At this time, we will find that the course table with the index created accounts for 5% of the overhead, and the course2 table without the index created accounts for 95%. This is 10 times the difference.

Step 4: insert 1000000 rows of equivalent data into the two tables, and then delete 500,000th records.

The difference is the same as step 3.

Summary: when the condition column of the delete statement does not create an index, there are two situations:

First, the data volume is small. I tested the data below 10000. At this time, the difference between the two is not big, but the disk overhead will be caused by the index creation. The overhead is not large because the data volume is small, even if the full table scan speed is not slow, the index advantage is not obvious at this time.

Second, the data volume is large. I tested it at more than 100000, and the difference between the two is large. If an index is created for a condition column, the efficiency is high.

Third: in the final analysis, the main overhead of the system is still in the first step of deleting the data row. You can quickly find the most efficient solution to delete rows.