Index advantages and disadvantages: 12th pages. Why should I create an index? This is because creating an index can greatly improve the system performance. First, you can create a unique index to ensure the advantages and disadvantages of indexes in database tables.
Why create an index? This is because creating an index can greatly improve the system performance. First, you can create a unique index to ensure the uniqueness of each row of data in the database table. Second, it can greatly speed up data retrieval, which is also the main reason for creating an index. Third, it can accelerate the connection between tables, especially in achieving data reference integrity. Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries. Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.
Some may ask: why not create an index for each column in the table because increasing indexes has so many advantages? Although such an idea has its own rationality, it also has its own one-sidedness. Although indexes have many advantages, it is unwise to add indexes to every column in the table. This is because adding indexes also has many disadvantages. First, it takes time to create and maintain indexes. this time increases with the increase of data volume. Second, indexes occupy physical space. in addition to data tables, each index occupies a certain amount of physical space. to create a clustered index, the required space is larger. Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the data maintenance speed.
Indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which columns cannot create an index. In general, you should create an index on these columns. for example, you can speed up the search for columns that frequently need to be searched, force the uniqueness of the column and the data arrangement structure in the organization table. These columns are usually used in connected columns and are mainly foreign keys, which can speed up the connection; create an index on a column that often needs to be searched by range. because the index has been sorted, the specified range is continuous. create an index on a column that frequently needs to be sorted because the index has been sorted, in this way, the sorting of indexes can be used to speed up the sorting query time. indexes are often created on the columns in the WHERE clause to accelerate the condition judgment speed.
Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics: first, indexes should not be created for those columns that are rarely used in queries or referenced. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements. Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. in the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed. Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small. Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.
Index creation methods and features
How to create an index
There are multiple ways to create an index. These methods include directly creating an index and indirectly creating an index. Directly CREATE an INDEX. for example, you can use the create index statement or the create index wizard to indirectly CREATE an INDEX. for example, you can also CREATE an INDEX when defining a primary key constraint or a unique key constraint in a table. Although both methods can create indexes, the specific content of the indexes they create is different.
Using the create index statement or using the INDEX creation wizard to CREATE an INDEX is the most basic method for creating an INDEX. this method is the most flexible and can be customized to CREATE an INDEX that meets your needs. When using this method to create an index, you can use many options, such as specifying the page fullness, sorting, and sorting statistics, to optimize the index. Using this method, you can specify the index type, uniqueness, and composite. that is to say, you can create a clustered index or a non-clustered index. you can create an index on a column, you can also create an index on two or more columns.
You can also create indexes indirectly by defining primary key constraints or uniqueness key constraints. A primary key constraint is a logic that maintains data integrity. it limits that records in a table 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, in terms of physical structure, the structure corresponding to the primary key constraint is a unique clustered index. In other words, in physical implementation, there is no primary key constraint, but only a unique clustered index. Similarly, an index is also created when a unique key constraint is created. this index is a unique non-clustered index. Therefore, when using constraints to create an index, the index type and features are basically determined, and there is little room for customization.
When you define a primary key or unique key constraint on a table, if the table already has a standard INDEX created using the create index statement, then, the index created by the primary key constraint or the unique key constraint overwrites the previously created standard index. That is to say, the primary key constraint or the unique key constraint takes precedence over the INDEX created using the create index statement.
Index features
An index has two features: a unique index and a composite index.
The unique index ensures that all data in the index column is unique and does not contain redundant data. If a table already has a primary key constraint or a unique key constraint, SQL Server automatically creates a unique index when creating or modifying a table. However, if uniqueness must be ensured, a primary key constraint or a unique key constraint should be created instead of a unique index. When creating a unique index, you should carefully consider these rules: When creating a primary key constraint or a unique key constraint in a table, SQL Server automatically creates a unique index. if the table already contains data, when an index is created, SQL Server checks the redundancy of existing data in the SQL Server checklist. whenever you use an insert statement to insert data or use a modify statement to modify data, SQL Server checks data redundancy: if there is a redundant value, SQL Server cancels the execution of the statement and returns an error message. make sure that each row of data in the table has a unique value, this ensures that each object can be uniquely identified. you can only create a unique index on a column that guarantees the integrity of the object. for example, you cannot create a unique index on the name column in the personnel table, because people can have the same name.
A composite index is an index created in two or more columns. When you search for two or more columns as a key value, it is best to create a composite index on these columns. When creating a composite index, consider these rules: you can combine up to 16 columns into a separate Composite Index. The total length of a composite index column cannot exceed 900 bytes, that is to say, the composite column length cannot be too long. in composite indexes, all columns must come from the same table and cannot create composite columns across tables. in composite indexes, the order of columns is very important. Therefore, we must carefully sort the order of columns. in principle, we should first define the most unique column, for example, in (COL1, COL2) the index on is different from the index on (COL2, COL1) because the order of the two index columns is different. to enable 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. when multiple key columns exist in the table, the composite index is very useful. The Composite index can improve the query performance, reduce the number of indexes created in a table.
Index type
You can divide an index into two types based on the order of the index and the physical order of the data table. One is the clustered index with the same physical order and index order as the data table, and the other is the non-clustered index with different physical order and Index Order of the data table.
Architecture of clustered index
The index structure is similar to the tree structure. the top of the tree is called the Leaf level. the rest of the tree is called the non-leaf level, and the root of the tree is in the non-leaf level. Similarly, in clustering indexes, the leaf-level and non-leaf-level of clustering indexes constitute a tree structure, and the lowest level of the index is the leaf-level. In a clustered index, the data page of the table data is at the leaf level, the index page on the leaf level is at the non-leaf level, and the index page on the index data is at the non-leaf level. In clustering indexes, data values are always sorted in ascending order.
Create a clustered index for frequently searched columns in the table or columns accessed in sequence. When creating a clustered index, 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 row in the table is the same as that of the row in the index. you can create a clustered index before creating any non-clustered index, this is because the clustering index changes the physical order of the rows in the table. The data rows are arranged in a certain order and the order is automatically maintained. the uniqueness of key values is either explicitly maintained using the UNIQUE keyword, either it is explicitly maintained by an internal unique identifier. these unique identifiers are used by the system and cannot be accessed by users. The average size of the clustered index is about 5% of the data table size. However, the actual size of the clustered index often varies according to the size of the index column. during index creation, SQL Server temporarily uses the disk space of the current database, when creating a clustered index, it requires 1.2 times the size of the tablespace. therefore, make sure you have enough space to create a clustered index.
When the system accesses the data in the table, first determine whether there is an index in the corresponding column and whether the index is meaningful to the data to be retrieved. If the index exists and makes sense, the system uses the index to access records in the table. The system browses data from the index, and the index browses starts from the root of the tree index. From the root, compare the search value with each key value to determine whether the search value is greater than or equal to the key value. This step repeats until a key value greater than the search value is met, 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 the non-clustered index is also a tree structure, which is similar to that of the clustered index, but it is also significantly different.
In non-clustered indexes, leaf-level indexes only contain key values, but not data rows. Non-clustered indexes indicate the logical order of rows. Non-clustered indexes have two architectures: one is to create non-clustered indexes on tables without clustered indexes, another architecture is to create non-clustered indexes on tables with clustered indexes.
If a data table does not have a clustered index, this data table is also called a data heap. When a non-clustered index is created at the top of the data heap, the system uses the row identifier on the index page to point to the record on the data page. The row identifier stores the information of the data location. The data heap is maintained by using the index distribution graph (IAM) page. The IAM page contains the storage information of the cluster where the data heap is located. In the system table sysindexes, a pointer points to the first IAM page related to the data heap. The system uses the IAM page to browse and find the space where new record rows can be inserted in the data heap. These data pages and records on these data pages are not in any order and are not linked together. The only connection between these data pages is the order recorded in IAM. When a non-clustered index is created on the data stack, the leaf level contains the row identifier pointing to the data page. The row Identifier specifies the logical sequence of the record rows. it consists of the file ID, page number, and row ID. The identifiers of these rows must be unique. The order of leaf-level pages of non-clustered indexes is different from the physical order of table data. These key values are maintained in ascending order at the leaf level.
When a non-clustered index is created on a table with a clustered index, the system uses the clustering key pointing to the clustered index on the index page. The cluster key stores the data location information. If a table has a clustering index, the leaf level of the non-clustering index contains the clustering key value mapped to the clustering key, rather than the physical row identifier. When the system accesses data in a table with a non-clustered index, and the non-clustered index is created on the clustered index, then it first finds the pointer to the clustered index from the non-clustered index, and then finds the data by using the clustered index.
When you need to retrieve data in multiple ways, non-clustered indexes are very useful. When creating a non-clustered index, consider these situations: by default, the created index is not a clustered index; on each table, you can create no more than 249 non-clustered indexes, but a maximum of one clustered index can be created.
Why should I create an index? This is because creating an index can greatly improve the system performance. First, you can create a unique index to ensure that the database table is...
