The role and advantages of database indexing disadvantages

Why do you want to create an index? This is because creating an index can greatly improve the performance of the system.
First, by creating a unique index. To ensure that each row of unique data is in a database table.
Second, high-speed data retrieval can be greatly accelerated, which is the most basic reason to create an index.

Third. This is particularly relevant in terms of achieving the data's textual integrity.
The same can significantly reduce the time of grouping and sorting in queries when using grouping and sorting clauses for data retrieval.
Five, by using the index, can be in the process of querying. Improve the performance of your system with an optimized stealth device.

One might ask: there are so many advantages to adding indexes. Why not create an index for each column in the correct table? This kind of thought has its rationality, but also has its one-sidedness. Although the index has a lot of advantages, but. It is unwise to add an index to each column in the table. This is due to. Adding an index also has a number of disadvantages.

First, it takes time to create indexes and maintain indexes, and that time is added as the amount of data is added.
Second, the index takes up physical space, except that the data table occupies the data space. Each index also takes up a certain amount of physical space, assuming that the required space will be greater if a clustered index is to be established.
Thirdly, when the data in the table is added, deleted, and changed. Indexes also need to be maintained dynamically, thus reducing the speed of data maintenance.

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 which columns you cannot create indexes on. In general, you should create indexes on these columns. Like what:

You can speed up your search on columns that you often need to search for.
On the column that is the primary key. Enforces the uniqueness of the column and arranges the structure of the data in the organization table.
In often used on the connected columns, these columns are mainly foreign keys, 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, its specified range is continuous;
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 sorting of the indexes to speed up the sort query time.
Speed up the inference of a condition by creating an index on the columns that are often used in the WHERE clause.

Same. For some columns, you should not create an index.

Generally speaking. These columns that should not be indexed have the following characteristics:

First, you should not create an index for columns that are very rarely used or included in queries. This is because. Since these columns are very rarely used, they are indexed or not indexed. does not improve query speed. Instead. Because the index was added. It reduces the system maintenance speed and increases the space requirement.

Second. You should not add indexes to columns that have only very few data values.

This is because the values of these columns are very small. For example, the gender column of the personnel table. In the results of the query, the data rows of the result set occupy a very large proportion of the rows in the table, that is, the data rows that need to be searched in the table are of very large proportions. Adding an 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 due to. The amount of data in these columns is either quite large or has very little value.
Finally, when the change performance is far greater than the retrieval performance. You should not create an index. This is because changes in performance and retrieval performance are conflicting.

When an index is added, retrieval performance is improved. But it will reduce the performance of changes. When the index is reduced. Improves performance and reduces retrieval performance. therefore Indexes should not be created when the performance of the churn 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 an index. These methods include methods for creating indexes directly and indirectly creating indexes.

Create indexes directly, such as by using the CREATE INDEX statement or by creating an indexing wizard, such as when you define a PRIMARY key constraint or a uniqueness key constraint in a table, an index is also created at the same time. Although both methods are able to create indexes, the details of the indexes they create are different.
Using the CREATE INDEX statement or creating an index using the Make Indexing Wizard is the most important way to create an index, and the method is most flexible and can be customized to create an index that fits your needs. There are many options to use when creating an index in this way. such as specifying the full degree of data pages, sorting, sorting statistics, and so on. This optimizes the index. Use such a method. Ability to specify the type, uniqueness, and composition of an index, that is. The ability to create clustered indexes. You can also create nonclustered indexes. The ability to create indexes on a single column. It is also possible to create an index on two or more 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. It restricts records in the table to have the same primary key record.

When you create a PRIMARY key constraint. The system has voluntarily created a unique clustered index. Although. Logically. The PRIMARY KEY constraint is an important structure, but on 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. There is only a single clustered index. Similarly, when creating a Uniqueness key constraint, the index is also created at the same time, and such an index is a unique nonclustered index. As a result, when creating an index with constraints, the type and characteristics of the index are basically determined, and the user-defined room is smaller.

When a primary key or uniqueness key constraint is defined on a table, it is assumed that 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 standard index that was created.

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
There are two characteristics of the index. 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 include redundant data. Suppose there is already a primary KEY constraint or a Uniqueness key constraint in the table. When creating a table or altering a table, SQL Server itself creates a unique index on its own initiative. However, it is assumed that uniqueness must be guaranteed. Instead of creating a uniqueness index, you should create a PRIMARY key constraint or a uniqueness key constraint.

When creating 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 itself creates a unique index of its own, assuming that the table already contains data, when the index is created. SQL Server checks the redundancy of the data already in the table, and SQL Server checks the redundancy of the data whenever the INSERT statement is used to insert data or when the data is altered using a change statement: Assuming there are redundant values, SQL Server cancels the statement and returns an error message ; Ensure that each row of data in a table has a unique value. This ensures that each entity can be uniquely identified, 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 used as a key value. It is a good idea to create composite indexes on these columns. 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, which means that the composite column length cannot be too long. In the 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 important, so the order of the columns is carefully arranged, in principle. You should first define the most unique column, for example, the index on (col1,col2) is not the same as the index on (col2,col1). Because the order of the columns in the two indexes is different. The composite index to be used by the query optimizer. Query Language The WHERE clause must refer to the composite index of the first column, when there are multiple key columns in the table. Composite indexes are very useful. Using composite indexes can improve query performance and reduce the number of indexes in the table.

The role and advantages of database indexing disadvantages