Index problem
I. Overview
You can quickly access specific information in the database table using the index. The index is a structure that sorts the values of one or more columns in the database table. The index provides a pointer to point to the data value stored in the table, and then arrange these pointers according to the specified sort order. The database used indexes is very similar to the directory of the book: found a specific value by searching the index, then follow the pointer to the line containing the value.
Two types of two indexes:
Gathering index = cluster set index
The aggregated index is based on the key value of the data line to sort and store these data lines. Since the data line is stored in the sorting order based on the aggregation index key, the aggregation index is very effective. Each table can only have a aggregation index because the data line itself can only be stored in one order. The data line itself constitutes the lowest level of aggregation index.
Only when the table contains the aggregation index, the data line in the table is stored according to the sequence order. If the table has no aggregation index, its data line is stored by a stack.
The aggregation index is particularly effective for those columns that are often searching range values. After using the aggregation index to find the row containing the first value, you can ensure that rows containing subsequent index values are physically adjacent. For example, if a query executed by the application often retrieves records within a certain date, use the aggregation index can quickly find rows containing the start date, and then retrieve all adjacent rows in the table until the end date is reached. This helps to improve the performance of such queries. Similarly, if a column is often used when sorting the data retrieved from the table, the table can be aggregated on the column (physical sort), avoiding the ordering of each query, saving costs
Non-aggregated index
The non-aggregated index has a structure that is completely independent of the data line. The minimum row of non-aggregated indexes contains a key value of the non-aggregated index, and each key value item has a pointer pointing to the data line containing the key value. The data line is not stored in the order based on non-aggregated keys.
In the non-aggregated index, pointers from the index line points to the data line are referred to as a row locator. The structure of the line locator depends on the storage method of the data page is a stack or aggregation. For a stack, the line locator is a pointer to the row. For tables with aggregation indexes, the line locator is a gathering index key. The row in the table is stored in a specific order when the aggregation index is created on the table. These rows are stored in order based on the aggregation index key. If a table only has a non-aggregated index, its data line will store non-aggregated indexes in disorderly stacking, and both can improve query performance.
The non-aggregated index is as a B tree structure as the aggregation index, but there are two major differences: the data line is not sorted and stored in the order of the non-aggregated index key. The leaf layer of the non-aggregated index does not contain data pages. Instead, the leaf node contains the index line. Each index line contains a non-aggregated key value and one or more row locator, which points to the data line with the key value (if the index is not unique, may be multi-line). Non-aggregated indexes can be defined on tables, stacks or index views with aggregation indexes
Another unique index
The unique index ensures that the index column does not contain a duplicate value. In the case of multi-column unique index, the index ensures that each value combination in the index column is unique. The only index is both an index and constraints.
The complex index tie is a plurality of call combination indexes, also called a composite index. Pay attention to the order of the index when composite index is used.
Creation of two indexes
There are two ways to define indexes within SQL Server: CREATE INDEX statement and CREATE TABLE statement
CREATE TABLE Support uses the following constraints when creating an index:
Primary Key Create a unique index to enforce primary key Unique Creating a Unique Index Clustered Creating a Gathering Index Nonclustered Creating a non-aggregation index
Note: 1 When defining an index, you can specify that the data of each column is stored in ascending order or descending. If not specified, the default is that ascending 2 supports creating indexes 3 to specify the filling factor to specify the extent of each index page to specify the filling factor on the calculation column. The amount of empty space on the index page is very important, because when the index page is filled, the system must take the time to split it for the new line.
Three index maintenance statement
DBCC DBREINDEX Rebuilds One or more index DBCC indexfrags in the specified database to organize the specified table or the aggregation index and auxiliary index
Speed compatibility log affects data access affects additional disk space DBCC is the fastest and best, but by bringing the data from data without large DBREINDEX, it can be accessed, which affects all the simplicity of the simple reduction log.
DBCC is slow, but you must divide a small data is not locked. You need a small indexdefrag. Don't specify the execution at any time.
DROP INDEX must be large, but can be made to make a simple reduction of logging data TEMPDB by specifying a fault reduction model in CREATE INDEX, specifying a fault reduction model in Create INDEX.
Four ways to check the index
Sp_indexes Returns Index Information of Specifies Remote Table IndexKey_Property Returns the information about the indexes system table database to each index and table in the table, which is stored in each database.
5 Can be established on an index by performing a plan to view the SQL statement
For example, Create Table Test (Field_1 Int Not Null, Field_2 Int Constraint PK_TEST Primary Key Clustered (Field_1))
Create Index IX_TEST ON TEST (Field_2)
1 Select * from test where field_2 = 408 Execution Plan can be seen using IX_TEST index 2 Select * from test where field_1 = 1 Execution plan can be seen using pk_test3 but if it is SELECT * from test with (IX_TEST) Where Field_1 = 1 specifies the index
Six index specific use (reposted)
1) Design of index A: Try to avoid table scan checking the WHERE clause of your query statement because it is an important place to focus on optimizer. Each column contained in WHERE is a possible candidate index, in order to achieve the optimal performance, considering the examples given below: This column is given in the WHERE clause of the COLMN1. The following two conditions can improve the optimized query performance of the index! First: There is a single index on the column1 column in the table, there is a multi-index in the table, but the column1 is the first index column to avoid defining a multi-index and column1 is the second or back index, such Indexing cannot optimize server performance, for example: The following example uses a PUBS database. SELECT AU_ID, AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_LNAME = 'White' The index created on the following columns will be useful to the optimizer? AU_LNAME? AU_LNAME, AU_FNAME and the index established on the following columns will not Will play a good role in the optimizer? AU_ADDRESS? AU_FNAME, AU_LNAME Consider using a narrow index on one or two columns, narrow index is more effective than multi-indexes and composite indexes. With a narrow index, there will be more rows and fewer index levels on each page (relatively similar to multi-indexes and composite indexes), which will advance system performance. For multi-column index, SQL Server maintains a density statistics on all columns (for union) and Histogram (histogram) statistics on the first index. According to statistics, if the first index on the composite index is rarely selected, the optimizer will not use the index for many query requests. Useful indexes increase the performance of the SELECT statement, including INSERT, UODATE, DELETE. However, since a table is changed, the index will be affected. Every INSERT, UPDATE, the DELETE statement will reduce performance. Experiments show that do not use a large number of indexes on a single table, do not use overlapping indexes on a shared column (refer to the reference constraint in the multi-table). Check the number of unique data on a list, compare it to a comparison with the number of rows in the table. This is the selectivity of the data, which compares the results will help you decide whether to use a column as the index of the Hou Selection, if needed, to build which index. You can return the number of different values of a column with the following query statement. Select Count (DistINCT Cloumn_Name) from table_name assumes that column_name is a 10,000-row table, then look at the column_name return value to determine if it should be used, and what index should be used. UNIQUE VALUES INDEX5000 Nonclustered Index 20 Clustered Index 3 NO Index
2) 镞 Edge and non-index selection
<1:> 镞 Index is consistent with the order of the physical order and index. The actual data pages are included on each level of the page, the low-level index. A table can only have a 镞 index. Since Update, the Delete statement requires relatively more read operations, so the index is often able to accelerate such an operation. In the table at least one index, you should have a 镞 index. In several cases, you can consider using the index: for example: the number of different values including a column is limited (but not very small) the state name of the customer table has 50 or so of different state names. The abbreviation value can be used. For example, a column that returns a certain range value can be used, such as columns to operate on columns with BetWeen,>,> =, <, <=, etc.. Select * from sales where order_date betWeen '5/1/93' and '6/1/93', for example, columns that return a large number of results when the query can use the 镞 index. Select * from phonebook where last_name = 'smith' When there is a large number of rows being inserted into the table, avoid establishing an index on the columns of this table (for example, the Identity column). If you have established an index, the performance of INSERT will be greatly reduced. Because each inserted row must go to the final data page of the table. When a data is being inserted (this data page is locked), all other inserts must wait until the current insert is over. A indexed leaf-level page includes the actual data page, and the order of the data pages on the hard disk is the same as the logic order of the index.
<2:> A non-embarrassing index is that the order of physical order and the order of the index are different. A non-indexed leaf-level contains a pointer to the row data page. There are multiple non-indexes in a table, you can consider using non-indexes in the following cases. On the columns with many different values, you can consider using non-rumored indexes, for example: a part_id column Select * from Employee WHERE EMP_ID = 'PCM9809F' in a part table, can consider using the 镞 index on the column of the Order By clause
3) If a list is set to the primary key, it automatically generates a clustered index that cannot be used directly using the Drop index table1.tableIndex1 statement must delete the primary key constraint, with the statement: ALTER TABLE TABLE1 DROP CONSTRAINT constraint name (eg PK_XXX)
Finally supplement
Full-text index, please refer to http://expert.9cbs.net/expert/topic/1594/1594455.xml?temp=.1432154
