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,
In the problem of target detection “ recall Recall”,“ Accuracy Precision”Linux Page replacement algorithm C Language implementation About wechat payment body( Product description ) Problems encountered stay Vue Use in Web Worker Calculate the working days of each month c++ Memory Pointer out of bounds detection mechanism _CrtMemBlockHeadeJavaScript Medium Call and Apply Remember once EventBus Project issues caused by memory leaks Hundreds of millions of locusts rarely collide Locusts want to be self driving Heroes Java Swing JList: List box components