Advantages and disadvantages of indexing
Why do you want to create an index? This is because creating an index can greatly improve the performance of the system. First, you can guarantee the uniqueness of each row of data in a database table by creating a unique index. Second, you can greatly speed up the retrieval of data, which is the main reason to create indexes. Third, you can speed up the connection between tables and tables, especially in terms of realizing the referential integrity of the data. Finally, when you use grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in a query. In the process of querying, the optimization of the hidden device can be used to improve the performance of the system by using the index.
Someone might ask: Why do you not create an index for each column in the table because there are so many advantages to adding an index? This kind of idea certainly has its rationality, but also has its one-sidedness. Although indexes have many advantages, it is unwise to add indexes to each column in the table. This is because there are a number of downside aspects to adding indexes. First, it takes time to create indexes and maintain indexes, which increase as the amount of data increases. Second, the index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain physical space, if you want to establish a clustered index, then need more space. Third, when the data in the table is added, deleted and modified, the index will be maintained dynamically, thus reducing the data maintenance speed.
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 indexes on, and on which columns you cannot create indexes. In general, you should create indexes on these columns, for example: You can speed up your search on columns that you frequently need to search; On columns that are the primary key, enforce the uniqueness of the column and the arrangement of the data in the organization table; These columns are mostly foreign keys that are used frequently on connected columns to speed up the connection. ; Create indexes on columns that often need to be searched by scope because the indexes are sorted, the ranges specified are contiguous, and indexes are created on columns that often need to be sorted, because the indexes are sorted so that the query can take advantage of the sort of the index to speed up the sorting query time ; Create indexes on columns that are frequently used in the WHERE clause to speed up the judgment of the condition.
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 indexes for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, they are indexed or indexed and do not increase the query speed. On the contrary, because of the addition of indexes, it reduces the maintenance speed of the system and increases the space requirement. Second, you should not add indexes to columns that have very few data values. This is because, because of the low values of these columns, such as the gender column of the personnel table, the result set's data rows account for 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. Adding indexes does not significantly speed up the retrieval. Third, columns that are defined as text, image, and bit data types should not be indexed. This is because these columns have either a large amount of data or a very small number of values. Four, you should not create an index when the modification performance is far greater than the retrieval performance. This is because the modification performance and retrieval performance are contradictory. When indexing is added, retrieval performance is improved, but modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, indexes should not be created when the modification performance is far greater than the retrieval performance.
Characteristics of methods and indexes for creating indexes
How to create an index
There are several ways to create an index, including methods for directly creating indexes and indirectly creating indexes. Create indexes directly, such as by using the CREATE INDEX statement, or indirectly by creating the Index wizard, such as defining a PRIMARY KEY constraint or a uniqueness key constraint in a table, and also creating an index. Although both methods can create indexes, the specifics of what they create are different.
Using the CREATE INDEX statement or creating an index using the Creation Index Wizard is the most basic way to create an index, and this is the most flexible way to customize the index that meets your needs. When you create an index in this way, you can optimize the index by using a number of options, such as specifying the full degree of the data page, sorting, collating statistics, and so on. With this method, 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 columns.
Indexes can also be created indirectly by defining primary key constraints or uniqueness key constraints. A PRIMARY KEY constraint is a logic that maintains data integrity, which restricts records in a table from having 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, in the physical structure, the structure corresponding to the primary KEY constraint is a unique clustered index. In other words, there is no primary KEY constraint on the physical implementation, and only a unique clustered index exists. Similarly, when you create a Uniqueness key constraint, the index is also created, which is a nonclustered index of uniqueness. Therefore, when you create an index using constraints, the type and characteristics of the index are basically determined, and the user-defined scope is relatively small.
When a primary key or a Uniqueness key constraint is defined on a table, the index created by a PRIMARY key constraint or a Uniqueness key constraint overrides a previously created standard index if the table already has a standard index created using the CREATE INDEX statement. That is, a primary key constraint or a Uniqueness key constraint creates an index that is higher than the index created by using the CREATE INDEX statement.
Characteristics of indexes
An index has two characteristics, namely, a uniqueness index and a composite index.
The uniqueness index guarantees that all data in the indexed column is unique and does not contain redundant data. If a primary key constraint or a Uniqueness key constraint is already in the table, SQL Server automatically creates a unique index when the table is created or 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 unique index, these rules should be carefully considered: SQL Server automatically creates a unique index when a primary KEY constraint or uniqueness key constraint is created in a table, and if the table already contains data, the redundancy of the data already in the SQL Server checklist when the index is created ; SQL Server checks the redundancy of data whenever a insert statement is used to insert data or modify the data using a modify statement: If there are redundant values, 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 validated and can only create a unique index on a column that can guarantee entity integrity, for example, you cannot create a unique index on a name column in the personnel table, because people can have the same name.
A composite index is an index that is created on two columns or multiple columns. When searching, it is a good idea to create a composite index on these columns when two or more columns are used as a key value. When you create a composite index, you should consider these rules: You can combine up to 16 columns into a single composite index, and the total length of the columns that make up the composite index cannot exceed 900 bytes, meaning that the length of the composite column cannot be too long; In a composite index, all columns must come from the same table, and you cannot build a composite column across tables ; In a composite index, the order of the columns is very important, so you should carefully arrange the order of the columns, in principle, you should first define the most unique columns, for example, the index on (col1,col2) is not the same as the index on (col2,col1), because the columns of the two indexes are in different order 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, and the use of composite indexes can improve query performance and reduce the number of indexes created in one table.
Types of indexes
Indexes can be divided into two types, depending on whether the order of the indexes is the same as the physical order of the datasheet. One is the clustered index in which the physical order of the data table is the same as the index order, and the other is the nonclustered index with a different physical order of the data table than the index order.
The architecture of clustered index
The structure of the index is similar to the tree structure, the top of the tree is called the leaf level, the other part of the tree is called the non-leaf level, and the root of the tree is in the non-leaf level. Similarly, in the clustered index, the leaf and non-leaf levels of the clustered index form a tree structure, and the lowest level of the index is the leaf level. In a clustered index, the data page of the table is a leaf level, and the index page above the leaf level is non-leaf-level, and the index page where the index data resides is non-leaf. In a clustered index, the order of data values is always sorted in ascending sequence.
You should create a clustered index on a column that is frequently searched in a table or on a column that is accessed sequentially. When you create a clustered index, you should consider these factors: 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 and the physical order of the rows in the index are the same, the clustered index is created before any nonclustered indexes are created, because the clustered index changes the physical order of the rows in the table , the data rows are arranged in a certain order, and the order is maintained automatically; The uniqueness of the key value is either explicitly maintained with the unique keyword or explicitly maintained by an internal unique identifier that 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 size of the actual clustered index often varies according to 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 a clustered index is created, Requires 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 to be retrieved. If an index exists and the index is meaningful, the system uses the index to access the records in the table. The system browses to data from the index, and index browsing begins 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 repeats until you encounter a key value that is larger than the search value, or the search value is greater than or equal to all the key values on the index page.
The architecture of non-clustered indexes
The structure of nonclustered indexes is also a tree-like structure, which is very similar to the structure of clustered index, but it also has obvious differences.
In nonclustered indexes, the leaf level contains only the key values, not the data rows. Non-clustered indexes represent the logical order of rows. Non-clustered indexes have two architectures: one architecture is to create nonclustered indexes on a table without a clustered index, and the other is to create nonclustered indexes on tables with clustered indexes.
If a data table does not have a clustered index, the data table is also called the data heap. When nonclustered indexes are created at the top of the data heap, the system uses the row identifiers in the index page to point to records 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 storage 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 through the data heap and find space to insert new record rows. 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 of the records in the IAM. When a nonclustered index is created on the data heap, the leaf level contains the row identifiers that point to the data page. The row identifier specifies the logical order of the record rows, consisting of a file ID, a page number, and a row ID. Identifiers for these rows remain unique. The order of the leaf pages of a nonclustered index is different from the physical order of the data in the table. These key values are maintained in ascending order in the leaf level.
When a nonclustered index is created on a table with a clustered index, 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 values that are mapped to the clustered key, rather than the row identifier that is mapped to the physical. When the system accesses data in a table with a nonclustered index, and the nonclustered index is created on a clustered index, it first finds a pointer to the clustered index from a nonclustered index, and then finds the data by using a 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 situations: By default, the indexes you create are nonclustered, and you can create no more than 249 non-clustered indexes on each table, with a clustered index of up to one.
Current 1/2 page
12 Next read the full text
