---------------------Database Index---------------------
1, the concept of the index:
Database index is a sort of data structure in the database management system, which helps to quickly query and update data in database tables. The implementation of an index typically uses a B-tree and its variants, plus trees.
In addition to data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.
Note: Setting an index for a table takes a price: one is to increase the storage space for the database, and the other is to spend more time inserting and modifying the data (because the index changes as well).
2, the advantages and disadvantages of the index:
1. Advantages:
1. By creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
2, can greatly speed up the retrieval speed of data, which is the most important reason to create indexes.
3, can speed up the connection between the table and the table, especially in the realization of the reference integrity of the data is particularly meaningful.
4. When using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
5, by using the index, you can use the optimization of the hidden device during the query, improve the performance of the system.
2. Disadvantages:
1. It takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.
2, 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 space will be larger.
3. When the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.
3. Applicable scenarios
Indexes are built on top of some columns in a database table. When you create an index, you should consider which columns you can create indexes on and which columns you cannot create indexes on. In general, indexes should be created on these columns: on columns that are frequently searched, you can speed up the search, enforce the uniqueness of the column on the column that is the primary key, and arrange the structure of the data in the organization table; These columns are often used on connected columns, which are mostly foreign keys, to speed up the connection Create an index on a column that often needs to be searched by scope, because the index is sorted, its specified range is contiguous, and the index is created on columns that are often ordered, because the index is sorted so that the query can take advantage of the sorting of the index to speed up the sort query time To speed up the judgment of a condition by creating an index on a column that is often used in the WHERE clause.
4. Scenario Not applicable
1. For those columns that are seldom used or referenced in queries, you should not create an index. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.
2. For columns that have very few data values, you should not increase the index. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy 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. Increasing the index does not significantly speed up the retrieval.
3. For columns that are 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 has very little value.
4. You should not create an index when modifying performance is far greater than retrieving performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.
3, the classification of the index:
1. Unique index
A unique index is one that does not allow any two rows to have the same index value.
When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.
2. Primary KEY index
Database tables often have one column or column combination whose values uniquely identify each row in the table. This column is called the primary key of the table.
Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to the data.
3. Clustered index
In a clustered index, the physical order of rows in a table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index.
If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.
Introduction to Database Indexing
