What is an index?
An index is a data structure on a disk that is based on a table or view. Using an index can make data acquisition faster and higher in universities, and also affect some other performance, such as inserting or updating.
There are two main types of indexes: clustered and nonclustered indexes.
Dictionary directory is an index, according to pinyin query the desired word is a clustered index (physical continuous, page number and table one by one corresponds), the radicals are a nonclustered index (logical continuous, page number and directory discontinuity).
Clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous.
A clustered index can have only one table, and a non-clustered index may have more than one table.
The pros and cons of indexes
The index is used to avoid full table scans because full table scans read every data page of a table from disk, and if an index points to a data value, only a few disks are required to read.
Indexed tables take up more space in the database, and it takes longer to add, delete, and change data commands.
Storage mechanism of indexes
The catalog in the book is a word and a list of page numbers in which the index in the database is the value in the table and the list where the values are stored.
A clustered index is a new physical space in the database that holds the values he arranges, and when new data is inserted, he rearranges the entire physical storage space.
A nonclustered index contains only the columns of the nonclustered indexes in the original table and a pointer to the actual physical table.
Basic structure of the data table
When a new data table is created, the system allocates a contiguous space of 8k in the disk. When a 8k run out of time, the database pointer will automatically allocate a 8k of space, each 8k of space is called a data page, and assigned from 0-7 page number, each file's No. 0 page records the boot information called the page header, every 8 data pages by 64k form the expansion area. The combination of all data pages forms a heap.
SQL Server specifies that rows cannot span data pages, so the maximum number of records per row can only be 8k, which is why char and varchar are limited to 8k in size, and storing more than 8k of data should use the text type. In fact, the text type of the field value can not be directly entered and saved, it is to store a pointer to a number of 8k data page composed of the expansion area, the real data is actually placed in these data pages.
When to set the index
1, using SSMS to create a clustered index
Expand the table to create the index, right-click Index, select New index, clustered index, new indexing point, select Columns, select column, and choose Ascending or Descending, enter name, OK.
By default, a clustered index is automatically created at the same time that the primary key is generated.
2. Create a clustered index using T-SQL
Create clustered index index_name /*clustered index name*/ On table_name ( Id desc ) With(drop_existing=on); /* delete */ if it exists
Each table or view can contain only one clustered index, because the clustered index changes the way data is stored and arranged. Whether clustered or nonclustered, the information is stored in a balance tree or B-tree, and B-trees identify and group similar data, precisely because the retrieval in the B-tree is based on key values, so the index can increase the speed of data access. The B-Tree will have a combination of similar keys, so the database engine can find the target record by searching only a few pages.
You can have more than one nonclustered index on each table, you can create an index on a column, or you can create an index on multiple columns that are already part of an existing index.
The SSMs creation method is the same as the T-SQL creation method:
Create nonclustered index fei /* clustered index name */ On defualt ( Hits desc )
FILLFACTOR: Used when creating an index, the data for each index page is a percentage of the index size, default 100. When the table needs to be modified frequently, it is recommended to set to 70-80, which is recommended when updating infrequently. 90.
Pad_index: Used to keep open space on each page in the Index intermediate level. The value cannot be set, and his value inherits from FillFactor.
Create table ceshi -- create a new table ( Id int identity(1,1) primary key, Name varchar(20), Code varchar(20), [date] datetime ) -- Insert 10w test data Declare @n int Set @n = 1 While @n <100000 Begin Insert into ceshi (name,code,[date]) Values (‘name‘+cast(@n as varchar(20)), ‘code’+cast(@n as varchar(20)), getutcdate()) Set @n=@n+1 End --View index status Set statistics io on -- view disk io Set statistics time on -- view sql statement analysis compile and execute time Select * from ceshi --View index status Exec sp_helpindex ceshi Select * from ceshi where name = ‘name1’ --ctrl+l View execution plan Clustered index scan overhead 100%, consider optimization for index lookup, build non-clustered index on name --Create a non-clustered index Create index name_index on ceshi ( Name ) --View the index again. Newly created non-clustered index Exec sp_helpindex ceshi -- Run the above statement Select * from ceshi where name = ‘name1’ - Obviously found that the speed is getting faster, ctrl+l found that the clustered index and the non-clustered index each accounted for 50%
Exec sp_helpindex ceshi -- view the index in the table Exec sp_rename ‘ceshi.name_index‘, ‘new_name’ -- renamed Drop index ceshi.new_name -- delete index Dbcc showcontig(ceshi,new_name) -- check for fragmentation Dbcc indexdefrag(webDB,ceshi,new_name) -- defragmentation Update statistics ceshi -- update statistics for all indexes in the table
SQL Server Index