Index overview

Index is a kind of database structure that can speed up the retrieval , It contains keys generated from one or more columns of a table or view , The pointer and the location of the data that is mapped to . By creating well-designed indexes, you can significantly improve the performance of database queries and applications . In a way , Think of a database as a book , Think of an index as a table of contents for a book . Find information with a catalog , It's obviously more convenient and faster than a book without a catalog . In addition to improving the retrieval speed , Indexes can also force rows in a table to be unique , So as to ensure the integrity of the data .

Once the index is created , Will be DBMS Automatic management and maintenance . When inserting , When modifying or erasing records ,DBMS The indexes in the table are automatically updated . to write SQL Query statement , Tables with indexes and tables without indexes are used in the same way . Although the index has many advantages , But avoid creating a large number of indexes in one table , Otherwise, the insertion will be affected , delete , Update data performance , Increase the cost of index adjustment , Reduce the response speed of the system .


The type of index

Clustered index

In a clustered index , The physical storage order of rows in a table and the logic of index keys ( Indexes ) The order is the same . Because there is only one physical storage , therefore , A table can contain only one clustered index . Creating or modifying a clustered index can be time-consuming , Because the physical storage order needs to be readjusted according to the logical value of the index key .

In the following cases , Consider using clustered indexes .

(1) A column containing a finite number of unique values , If only 100 Columns of unique status codes ;
(2) use BETWeeN>>=,< and < Such an operator returns a query with a range of values ;
(3) Query of large result set .


Nonclustered index

Non set index and clustered index have similar index structure . The difference is that , Nonclustered indexes do not affect the physical storage order of data rows , Physical storage order of data row and logic of key ( Indexes ) The order is not consistent . Each table can have multiple set indexes , Unlike a clustered index, there is only one .

Similar to clustered index , Non aggregation can also improve the query speed of data , But it also reduces the speed of inserting people and updating data . When you change table data that contains a nonclustered index ,DBMS The index must be updated synchronously . If a table needs to update data frequently , It should not have too many nonclustered indexes . in addition , If hard disk and memory space is limited , You should also limit the number of nonclustered indexes .


unique index

A unique index guarantees that the index key does not contain duplicate values , This makes each row in the table unique in some way . It makes sense to specify a unique index only if uniqueness is a characteristic of the data itself . for example , If you want to make sure that the “ ID number ” The value of the column is unique , When the primary key is “ Student number ” Time , Can be used for “ ID number ” Column creates a
UNIQUE constraint . When you try to enter the same ID number for multiple students in this column , An error message is displayed , Do not enter duplicate values . Use multi column unique index , It can ensure that the combination of multiple columns in the index key value is unique . for example , If “ surname ” and “ name ” The combination of columns creates a unique index , Then any two rows of records in the table will not have exactly the same “ surname ” and “ name ” value .

Both clustered and nonclustered indexes can be unique , You can create a unique clustered index and multiple unique nonclustered indexes for the same table .

establish PRIMARY KEY or UNIQUE When constrained, a unique index is automatically created for the specified column . from
UNIQUE There is no essential difference between a unique index generated automatically by a constraint and a unique index created manually independent of the constraint , The two methods of data validation are the same , The query optimizer also does not distinguish whether a unique index is created automatically or manually by constraints . however , If the goal is to achieve data integrity , Should be created for the column
UNIQUE or PRIMARY KEY constraint , Only in this way can the target of the index be clear .


View index

Views are also known as virtual tables , The format of the result set returned by the view is the same as the base table , Both consist of rows and columns , stay SQL The view is used in the same way as the base table is used in the statement . The result set of the standard view is not permanently stored in the database . Every time a query references a standard view ,
SQL Server Internally, the definition of the view is replaced with the query , Until the modified query references only the base table .

For standard views , The result set generated by query is very expensive , In particular, views that involve complex processing of too many rows ( Such as aggregating large amounts of data or joining many rows ). If you use these views frequently in queries , You can improve performance by creating aggregate only indexes on views . Such indexes are called view indexes , The corresponding view is called an indexed view . Index view can effectively improve the query performance of standard view . After creating a unique clustered index on a view , The result set is stored directly in the database , It's like a base table with a clustered index .

If the underlying table data is rarely updated , The index view works best . If the basic table data is updated frequently , The overhead of maintaining indexed views may exceed the performance benefits of using indexed views . If the underlying table data is updated periodically in batch form , However, it is mainly treated as read-only data between two updates , Consider deleting all indexed views before updating , Regenerate after the update , This can improve the update performance of batch processing .


Full text index

Full text index is one of the key technologies of search engine . Imagine being in 1 Search for a word in a megabyte file , It could take a few seconds , stay 100 It can take tens of seconds in a megabyte file , Searching in larger files is more expensive . In order to speed up this kind of retrieval speed , Full text indexing technology has emerged , Also known as inverted document technology . The principle is to define a thesaurus first , Then find and store the frequency and location of each entry in the article , It is equivalent to the establishment of an index with thesaurus as the directory , In this way, when looking for a word, we can quickly locate the position of the word .


XML Indexes
Yes, yes xml Data type column creation XML Indexes .XML Index to column xml All tags for the instance , Values and paths are indexed , This improves query performance . In the following cases , Consider creating XML Indexes .

(1) Yes xml Column queries are common in work . But it should be noted that ,xml If the column is modified frequently , It may cause high index maintenance overhead .
(2)xml The value of the column is relatively large , The retrieval part is relatively small . Building indexes avoids analyzing all data at run time , It can realize efficient query processing .


Index design

Since indexes have so many advantages , Can you create an index for each column in the table ? This idea has its rationality , But it's also very unwise , Because the index also has many disadvantages :

(1) It takes time to create and maintain indexes , As the amount of data increases, so does the time .
(2) The index takes up physical storage space . If you build a clustered index , More storage is needed .
(3) When modifying data in a table , The index should be updated synchronously , Reduces the speed of data change .


therefore , Consider carefully which columns need to be indexed , Which columns cannot be indexed . Poor index design and lack of index are important bottlenecks that affect the performance of database and application . Designing efficient index is very important to get good database and application performance , The following index design criteria can be considered .

(1) If a table has too many indexes, it will affect INSERT, UPDATE and DELE The performance of Yuhe , To reduce or delete unnecessary indexes .
(2) Avoid creating too many indexes on frequently updated tables , And index as few columns as possible .
(3) Using multiple indexes can improve query performance with less update and large amount of data , Because the query optimizer has more indexes to choose from , Thus, the fastest access method can be determined .
(4) Indexing a table with a small amount of data may not have an optimization effect , It may take longer for the query optimizer to traverse the index data than to query the base table directly .
(5) Views contain aggregations , Table join , Or a combination of aggregation and table join , View indexing can significantly improve performance .
(6) Use database engine tuning advisor to analyze performance bottlenecks , And adjust the index accordingly .



reference material :[1] Chen Zhipo , Wang Chunling , Rochfort , Fan Chunmei . Database principle and Application Course ( The first 3 edition )[M]. Beijing : People's Posts and Telecommunications Press ,2014:91-94.


©2019-2020 Toolsou All rights reserved,
Send love - A little romance for programmers VHDL—— Design of frequency divider Python Implementation of Hanoi Tower code It's over , Starting salary 30khtml+css+js Make a simple website home page QQ Login interface implementation Hill sorting of sorting algorithm ——c++ realization 【 Wechat applet learning 】 Netease music cloud code page implementation details Resume the 13th session python Blue Bridge Cup 2022 Solution to the 13th Blue Bridge Cup ( whole )