Database index advantages and disadvantages

Database index advantages and disadvantages

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 on columns that frequently need to be searched;
In a column that acts as a primary key, the uniqueness of the column and the data arrangement structure in the organization table are enforced;
These columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection;
Create an index on a column that often needs to be searched by range. The specified range is continuous because the index has been sorted;
Create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;
Create an index on the columns in the WHERE clause frequently to speed up condition judgment.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following features:

First, indexes should not be created for columns that are rarely used or referenced in queries. 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 for those columns with 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 much higher than the retrieval performance, you should not create a cable reference. 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 clause 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.