The role and advantages of database indexing disadvantages __ Database

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.

One might ask: why is there so many advantages to adding an index, and why not to create an index for each column in the table? 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 often need to search for.
Enforces the uniqueness of the column and the arrangement of the data in the organization table on the column that is the primary key;
Often used on connected columns, 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 already sorted and its specified range is contiguous;
Create indexes on columns that often need to be sorted, because the indexes are sorted so that the query can use the sort of 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 create indexes 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.