Advantages and disadvantages of indexes
Why do you create an index? This is because creating an index can greatly improve the performance of the system. First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table. Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes. Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data. Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries. By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.
Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Although this kind of thought has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is very unwise to add indexes to each column in a table. This is because there is a lot of downside to increasing the index. First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases. Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger. Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.
Indexes are built on top of some columns in a database table. Therefore, when you create an index, you should carefully consider which columns you can create an index on, and on which columns you cannot create an index. In general, you should create indexes on these columns, such as: on columns that are often searched, you can speed up the search, enforce the uniqueness of the column on the column that is the primary key, and arrange the structure of the data in the organization table; These columns are mostly foreign keys, which can speed up the connection. Create an index on a column that often needs to be searched by scope, because the index is sorted, its specified range is contiguous, and the index is created on columns that are often ordered, because the index is sorted so that the query can take advantage of the sorting of the index to speed up the sort query time To speed up the judgment of a condition by creating an index on a column that is often used in the WHERE clause.
Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics: first, you should not create an index for columns that are seldom used or referenced in a query. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement. Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval. Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has very little value. The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.
Methods for creating indexes and characteristics of indexes
How to create an index
There are several ways to create indexes, including methods for creating indexes directly and indirectly creating indexes. Create indexes directly, such as by using the CREATE INDEX statement or by creating the Index wizard indirectly, such as when you define a PRIMARY key constraint or a uniqueness key constraint in a table, and you also create an index. Although both of these methods can create indexes, there are differences in the specifics of how they create indexes.
Using the CREATE INDEX statement or creating an index using the Make Indexing Wizard is the most basic way to create an index, and this method is most flexible and can be customized to create an index that fits your needs. When you create an index this way, you can use a number of options, such as specifying the fill level of the data page, sorting, collating statistics, and so on, which optimizes the index. Using this approach, you can specify the type, uniqueness, and composition of the index, that is, you can create either a clustered index or a nonclustered index, either by creating an index on one column or by creating an index on two or more than two columns.
You can also create an index indirectly by defining a primary KEY constraint or a uniqueness key constraint. A PRIMARY KEY constraint is a logic that preserves data integrity, which restricts records in the table to have the same primary key record. When you create a PRIMARY key constraint, the system automatically creates a unique clustered index. Although, logically, the primary KEY constraint is an important structure, on the physical structure, the structure corresponding to the primary KEY constraint is a unique clustered index. In other words, on a physical implementation, there is no primary KEY constraint, and only a unique clustered index exists. Similarly, when creating a Uniqueness key constraint, an index is created at the same time, and the index is a unique, non-clustered index. As a result, when creating an index with constraints, the type and characteristics of the index are basically determined, and the user-defined scope is relatively small.
When a primary key or uniqueness key constraint is defined on a table, if a standard index created using the CREATE INDEX statement is already in the table, the index created by the PRIMARY KEY constraint or uniqueness key constraint overrides the previously created standard index. That is, the index created by the PRIMARY KEY constraint or uniqueness key constraint is higher than the index created with the CREATE INDEX statement.
Characteristics of the Index
The index has two characteristics, that is, the uniqueness index and the composite index.
A uniqueness index guarantees that all data in the indexed column is unique and does not contain redundant data. If there is already a primary KEY constraint or uniqueness key constraint in the table, SQL server automatically creates a unique index when the table is created or when the table is modified. However, if uniqueness must be guaranteed, you should create a PRIMARY key constraint or a uniqueness key constraint instead of creating a unique index. When you create a uniqueness index, you should carefully consider these rules: when you create a PRIMARY KEY constraint or a uniqueness key constraint in a table, SQL Server automatically creates a unique index, and if the table already contains data, the SQL Server checks the data redundancy in the table when you create the index Whenever you insert data using an INSERT statement or modify data using a modification statement, SQL Server checks the redundancy of the data: if there is a redundant value, SQL Server cancels the execution of the statement and returns an error message, ensuring that each row of data in the table has a unique value. This ensures that each entity can be uniquely acknowledged, and only a unique index can be created on columns that guarantee entity integrity, for example, you cannot create a unique index on a name column in a personnel table because people can have the same name.
A composite index is an index that is created on two or more columns. When searching, when two or more columns are a key value, it is best to create composite indexes on those columns. When you create a composite index, you should consider these rules: You can combine up to 16 columns into a single composite index, the total length of the columns that make up the composite index cannot exceed 900 bytes, which means that the composite column length cannot be too long; In a composite index, all columns must be from the same table, and composite columns cannot be created across tables In a composite index, the order of the columns is very important, so the order of the columns is carefully arranged, in principle, the most unique column should be defined first, for example, the index on (col1,col2) is not the same as the index on (col2,col1), because the order of the two-indexed columns is different ; for the query optimizer to use a composite index, the WHERE clause in the query statement must refer to the first column in the composite index, which is useful when there are multiple key columns in the table; Using composite indexes can improve query performance and reduce the number of indexes created in a table.
Type of index
The index can be divided into two types, depending on whether the order of the indexes is the same as the physical order of the data table. One is a clustered index in which the physical order of the data table is the same as the index order, and the other is a non-clustered index with a different physical order of the data table than the index order.
Architecture of Clustered Indexes
The structure of the index is similar to the tree structure, the top of the tree is called the leaf level, the other parts of the tree are called non-leaf levels, and the roots of the tree are in the non-leaf level. Similarly, in a clustered index, the leaf and non-leaf levels of the clustered index constitute a tree structure with the lowest level of the index being the leaf level. In a clustered index, the data page in the table is the leaf level, and the index page above the leaf level is non-leaf, and the index page where the index data resides is non-leaf. In a clustered index, the order of the data values is always sorted in ascending sequence.
Clustered indexes should be created on columns that are frequently searched in the table or on columns that are accessed sequentially. These factors should be considered when creating a clustered index: Each table can have only one clustered index, because the physical order of the data in the table can only be one; the physical order of the rows in the table is the same as the physical order of the rows in the index, and the clustered index is created before any nonclustered indexes are created because the clustered index alters the physical order , the data rows are arranged in a certain order, and the order is automatically maintained; The uniqueness of the key values is either explicitly maintained with the unique keyword or explicitly maintained by an internal unique identifier, which is used by the system itself and cannot be accessed by the user The average size of the clustered index is approximately 5% of the data table, but the actual size of the clustered index often varies depending on the size of the indexed column; During the creation of the index, SQL Server temporarily uses the disk space of the current database, and when the clustered index is created, It takes 1.2 times times the size of the table space, so be sure to have enough space to create the clustered index.
When the system accesses data in a table, it first determines whether there is an index on the corresponding column and whether the index is meaningful for the data being retrieved. If the index exists and the index is very meaningful, the system uses that index to access the records in the table. The system browses to the data from the index, and the index browse starts at the root of the tree index. Starting at the root, the search value is compared to each key value to determine whether the search value is greater than or equal to the key value. This step is repeated until a key value larger than the search value is encountered, or the search value is greater than or equal to all the key values on the index page.
Architecture of non-clustered indexes
The structure of non-clustered indexes is also a tree-like structure, which is very similar to the structure of clustered indexes, but it also has a distinct difference.
In a nonclustered index, the leaf level contains only the key values, not the data rows. Non-clustered indexes represent the logical order of rows. Nonclustered indexes have two architectures: one architecture is to create a nonclustered index on a table that does not have a clustered index, and the other is to create a nonclustered index on a clustered index table.
If a data table does not have a clustered index, the data table is also called the data heap. When a nonclustered index is created at the top of the data heap, the system uses the row identifier in the index page to point to the record in the data page. The row identifier stores information about where the data resides. The data heap is maintained by using an index allocation diagram (IAM) page. The IAM page contains the stored information for the cluster where the data heap resides. In system table sysindexes, there is a pointer to the first IAM page associated with the data heap. The system uses IAM pages to browse and find space in the data heap where new record rows can be inserted. These data pages and the records in these data pages are not in any order and are not linked together. The only connection between these data pages is the order in which they are recorded in IAM. When a nonclustered index is created on the data heap, the leaf level contains the row identifier that points to the data page. The row identifier specifies the logical order of the record rows, consisting of the file ID, page number, and row ID. The identifiers of these rows remain unique. The order of the leaf pages of a nonclustered index differs from the physical order of the data in the table. These key values are maintained in ascending order at the leaf level.
When a nonclustered index is created on a clustered index table, the system uses the clustered key that points to the clustered index in the index page. The cluster key stores the location information of the data. If a table has a clustered index, the leaf level of the nonclustered index contains the clustered key value mapped to the clustered key, rather than the row identifier mapped to the physical. When the system accesses data in a table with non-clustered indexes, and the nonclustered index is created on the clustered index, it first finds a pointer to the clustered index from the nonclustered index, and then finds the data by using the clustered index.
Nonclustered indexes are useful when you need to retrieve data in a variety of ways. When you create a nonclustered index, consider these cases: By default, the indexes you create are nonclustered indexes, and on top of each table, you can create no more than 249 nonclustered indexes, and a clustered index may have at most one.
http://www.bkjia.com/PHPjc/317091.html www.bkjia.com true http://www.bkjia.com/PHPjc/317091.html techarticle advantages and disadvantages of indexes Why do you create an index? This is because creating an index can greatly improve the performance of the system. First, by creating a unique index, you can guarantee that the database table ...
