The role and strengths of database indexes disadvantages

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 most basic reason to create indexes.
Thirdly, it is particularly meaningful to speed up the connection between tables and tables, especially when implementing 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.
By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

There may be people who ask: why does adding an index have so many advantages, 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 many advantages, it is unwise to add an index to each column in the table. This is because there is a lot of downside to adding indexes.

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 needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain physical space, if you want to establish a clustered index, then the need for more space.
Thirdly, when the data in the table is added, deleted and changed, 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 indexes on and which columns you cannot create indexes on. In general, you should create indexes on these columns, such as:

On the columns that often need to be searched, the speed of searching can be speeded up;
On the column that is the primary key, enforce the uniqueness of the column and the arrangement 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;
An index is created on a column that often needs to be searched by scope, because the index is sorted and its specified range is continuous;
Indexes are created on columns that are often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sorting query time;
Speed up the inference of a condition by creating an index on the columns that are often used in the WHERE clause.

, the index 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 very rarely used or included in queries. This is because, since these columns are very rarely used, they are indexed or non-indexed and do not improve query speed. Conversely, because of the addition of indexes, 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, such as 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 data rows in the table, that is, the data rows that need to be searched in the table are very large. 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 because the amount of data in these columns is either quite large or very small.
The index should not be created when the performance of the change is far greater than the retrieval performance. This is because changes in performance and retrieval performance are conflicting. When an index is added, retrieval performance is improved, but the performance of the change is reduced. When indexes are reduced, the performance of the changes is improved and the retrieval performance is reduced. Therefore, you should not create an index when the change performance is much larger 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 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. When you create an index in such a way, you can use many options, such as specifying the fill level of the data page, sorting, collating statistics, and so on, to optimize the index. Using this method, you can specify the type, uniqueness, and composition of the index, that is, the ability to create a clustered index, or to create a nonclustered index, to create an index on a single column, or to create an index on more than 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 creating a primary key constraint, the system proactively 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, there is no primary KEY constraint on the physical implementation, but only a unique 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, the index created by the PRIMARY KEY constraint or uniqueness key constraint overrides the standard index that was created if the table already has a standard index created using the CREATE INDEX statement. 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 include redundant data. Assuming there is already a primary KEY constraint or uniqueness key constraint in the table, SQL server itself creates a unique index itself when creating the table or altering the table. However, assuming uniqueness must be ensured, you should create a PRIMARY key constraint or a uniqueness key constraint instead of creating a unique index. 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 itself, assuming that the table contains data, and when the index is created, the SQL Server checks the table for redundancy of the data Whenever you insert data using an INSERT statement or change data using a change statement, SQL Server checks the redundancy of the data: assuming there are redundant values, SQL Server cancels 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 identified, and can only create unique indexes on columns that guarantee entity integrity, such as the inability to 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 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.