Asp. Net Website optimization database optimization measures INDEX OPTIMIZATION

The function of indexing is similar to the book directory. The book directory will be arranged in the order of chapters and will refer to a specific position. In this way, if we look for a chapter location in a book with hundreds of pages, we can scan only the book's directories, and the scanning range is reduced by n times, the query efficiency is naturally improved. In addition, if the SQL server Memory is sufficient, the index will be placed in the memory, and searching in the memory will naturally improve the efficiency. Therefore, we must make proper use of the index.

1) What columns are indexed?
By default, the database creates a clustered index for the primary key. In addition to this index, which columns need to be indexed? This problem can only be analyzed in specific situations. It depends on the SQL statement to be optimized (usually the statements with a large number of queries and the desired height), and the query is based on the conditions of the column.

For example, one table in the Forum database is the post reply table. The most commonly used table in the forum application is to query the reply on a page of the specified post, the condition for querying the reply table is the id of the primary sticker. At this time, it is inevitable to create an index on the primary sticker field.

2) Do I Have To create a clustered index on the primary key?
Generally, SQL server automatically adds a clustered index to the primary key, but with some exceptions, we need to create the clustered index on other columns. For example, we use table partitions, the partition field is not the primary key. In this case, the clustered index must be created on the partition column. In addition, if the number of queries based on the primary key is small, but the number of queries based on other columns is frequent, you can also consider setting the clustered index on a non-primary key. Note that clustering index columns must be difficult to change. If clustering index changes, the records in the clustered index will be relocated, resulting in page separation and fragmentation; second, the index of each non-clustered index will be modified, so that the index key value of all related non-clustered index rows can be corrected. This is a waste of time and space, as well as fragments that need to be organized, increasing unnecessary overhead (reorganizing clustering keys for each column ).

3) Compound indexes (with more than two indexes)
Indexes are stored in the database in the form of B trees. Indexes that contain columns A and B will first create a B tree based on column A, and the leaf node of column A will start to create a B tree based on column B. Therefore, the order of the two columns in the index must be determined based on the column where the query conditions are located.

You can use the following SQL to perform an experiment:

USE [Test]
GO
/***** Object: Table [dbo]. [testIndexOrder] script Date: 05/27/2010 09:11:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create table [dbo]. [testIndexOrder] (
[ID] [int] IDENTITY (1, 1) not null,
[FirstName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS not null,
[LastName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS not null,
[Desc] [nvarchar] (400) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_testIndexOrder] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/***** Object: Index [IX_testIndexOrder] script Date: 05/27/2010 09:11:51 ******/
Create nonclustered index [IX_testIndexOrder] ON [dbo]. [testIndexOrder]
(
[FirstName] ASC,
[LastName] ASC
) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
Declare @ I INT;
DECLARE @ random varchar (36 );
Set @ I = 0;
While @ I <100000
Begin
Set @ random = newid ();

Insert into [testIndexOrder]
(FirstName, LastName, [Desc])
VALUES (
Substring (@ random, 1, 8), substring (@ random, 12, 8), @ random
);
Set @ I = @ I + 1
End


Set statistics time on
Select * from [testIndexOrder] where lastname = '6f-4ECA -'
Select * from [testIndexOrder] where firstname = 'caabe009'
Set statistics time off

4) Number of Indexes
Improving query efficiency by indexing is at the cost of reducing the speed of updating, inserting, and deleting. You must adjust the index data whenever the index column changes. Therefore, you cannot create too many indexes on a table unless you do not care about the efficiency of data modification. In addition, SQL server limits the number of indexes and the length of indexes. For more information, see

5) re-indexing as necessary
After running SQL server for a period of time, some index fragments will be formed. At this time, indexes need to be re-built. Sometimes re-indexing can have unexpected results.

View the index fragmentation and re-create the index. You can use the SQL server Manager to re-create the index, or use the following SQL statement:

-- Displays the index fragmentation of the testIndexOrder table.
Dbcc showcontig (testIndexOrder)

-- Re-create the index of the table
-- The first parameter can be the table name or table ID.
-- The second parameter. If it is '', it indicates that all indexes of the table are affected.
-- The third parameter is the fill factor, that is, the data fill degree on the index page. If yes, it means that every index page is filled up. In this case, select is the most efficient, but when you want to insert an index in the future, you have to move all the subsequent pages, which is very inefficient. If yes, the previous fill factor value is used.
Dbcc dbreindex (testIndexOrder ,'',)

Database optimization is a complex learning that requires constant learning practices and accumulated experience.

Author: Yu Kai's technical blog