Database optimization measures and index optimization methods of Asp.Net website optimization series

In addition, if sql server has enough memory, the index will be put into memory, so it will be more efficient to search in memory. So we have to make good use of the index.
1) what columns are indexed
The database builds a clustered index on the primary key by default. What other columns should be indexed besides this index? This problem can only be analyzed on a case-by-case basis, depending on which column conditions the query is based on, depending on the sql statement that needs to be optimized (usually the statement with more queries and the corresponding higher query).
For example, in the database of the forum, there is a table which is the reply table of posts. In the application of the forum, the most frequently used one is to query the reply of a certain page of a specified post. It is inevitable to index the primary field.

2) 1 must the clustered index be built on the primary key
Normally sql server will automatically add clustered indexes to primary keys, but there are some exceptions where we need to build clustered indexes on other columns. For example, when we use table partitioning and the fields of the partition are not primary keys, we need to build clustered indexes on partitioned columns. Alternatively, if queries are less based on primary keys and more frequent based on other columns, you might consider building clustered indexes on non-primary keys. The only thing to note is that the columns of the clustered index must be unchangeable columns. If the clustered index is changed by 1, the records in the clustered index will be moved, resulting in the separation and fragmentation of page page. 2 causes every non-clustered index to be modified so that the key values of all relevant non-clustered index rows are corrected. This is a waste of time and space, and also leads to fragmentation, which adds unnecessary overhead (regrouping the aggregation key per column).

3) composite indexes (indexes with more than two columns) pay attention to the column order
Indexes are stored in the database as an B tree. The index of the A and B columns will first build the B tree according to the A column, and the leaf node of the A column will start to build the B tree according to the B column. So an index with two columns needs to determine the order of the two columns in the index based on the column of the query criteria.
You can do the experiment with sql:

 
USE [Test] 
GO 
/******  object : Table [dbo].[testIndexOrder]  Date of the script : 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]  Date of the script : 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) the number of indexes
Indexes improve query efficiency at the expense of slower updates, inserts, and deletes. Every time an index column changes, the index data needs to be adjusted accordingly. So you can't have too many indexes on one table unless you don't care about the efficiency of changing the data. In addition, sql server itself will have restrictions on the number of indexes and the data length of indexes, for details, please refer to
5) rebuild the index if necessary
After running Sql server for 1 period of time, some index fragments will be formed. At this point, it is necessary to rebuild the index. Sometimes rebuilding the index can have an unexpected effect.
View the index fragment, rebuild the index, can be rebuilt through sql server manager; You can also do this with the following sql statement:

 
-- According to the table testIndexOrder The index fragmentation situation  
DBCC SHOWCONTIG(testIndexOrder) 

-- Rebuild the index of the table  
-- The first 1 Can be either a table name or a table ID .  
-- The first 2 Two parameters, if yes '' Represents all indexes that affect the table.  
-- The first 3 The filling factor is the data filling degree of the index page. If it is, it means each 1 All index pages are filled select It's the most efficient, but when you insert the index later, you have to move all the subsequent pages, which is inefficient. If so, use the previous fill factor value.  
DBCC DBREINDEX(testIndexOrder . '' . ) 

Database optimization is a complex knowledge, need to continue to learn practice, accumulate experience.