1. Index Overview
indexing is an effective way to speed up queries, and users can depend on the needs of the application environment. Establish one or more indexes on the base table to provide multiple access paths to speed up the lookup.
In general, establishing and deleting an index is done by the DBA of the database administrator or the owner of the table, that is, the person who established the table, and when the system accesses the data, it chooses the appropriate index as the access path, and the user does not have to select the index.
2. Index strengths and weaknesses
First, it is clear why indexes are created, because creating indexes can greatly improve the performance of the system.
- By creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
- Can greatly speed up the retrieval of data, which is the most basic reason for creating indexes.
- The ability to accelerate the connection between tables and tables, especially when it is necessary to achieve the data's textual integrity.
- When you use grouping and sort clauses for data retrieval, the same can significantly reduce the time to group and sort in a query.
- By using the index. Can be in the process of querying. Improve the performance of your system with an optimized stealth device.
There are so many advantages to adding an index, why not create an index for each column in the correct table? The idea is reasonable, but it has its own one-sidedness, although the index has a lot of advantages, but. It is unwise to add indexes to each column in the table, because there is a lot of downside to adding indexes.
- It is time-consuming to create indexes and maintain indexes. This time is added as the amount of data is added.
- 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 you want to set up a clustered index, the space required will be larger.
- 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.
- An index is built on some columns in a database table. Therefore, when you create an index, you should carefully consider which columns are able to create indexes on which columns you cannot create indexes, and generally, you should create indexes on those columns:
1, on the columns that often need to search, can speed up the search speed2. On the column that is the primary key, enforce the uniqueness of the column and the arrangement of the data in the organization table.3, on the connected column is often used. These columns are primarily foreign keys that can speed up the connection.4. 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 continuous.
5. Create an index on a column that is often ordered, because the index is already sorted. This allows the query to use the sorting of the index to speed up the sort query time.
6. Speed up the inference of the condition by creating an index on the columns that are often used in the WHERE clause.The same is not an index that should be created for some columns. Generally speaking. These columns that should not be indexed have the following characteristics:1. For those columns that are very seldom used or included in the query, the index should not be created. Since these columns are very rarely used and therefore indexed or non-indexed, they do not improve the query speed, but instead, because of the addition of indexes, they reduce the maintenance speed of the system and add space requirements.2. You should not add indexes for columns that have only very few data values. This is due to. 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 proportions. The national index does not significantly speed up retrieval.
3. You should not add an index to columns that are defined as text, image, and bit data types. This is because the amount of data in these columns is either quite large or very small.4. You should not create an index when the change performance is far greater than the retrieval performance. This is due to. Changes in performance and retrieval performance are conflicting. When you add an index. Improves retrieval performance, but reduces churn performance. When the index is reduced. Improves performance and reduces retrieval performance, so you should not create an index when the performance of the churn is much larger than the retrieval performance.
3. Create an indexThere are several ways to create an index. These methods include directly creating an index method and indirectly creating an index, directly creating an index, using, for example, the following statement:CREATE [Unique][cluster][index < index name > on < table name > (< column name >[< order >][,< listing >[< Order;]).DescriptionThe < table name > is the name of the base table to create the index, and the index can also be built on one or more columns of the table, separated by commas, each < column name > The following can also be used in < order > Specify the sort order of index values, optionally ASC (ascending) or desc (descending), the default value is ASC.
A unique data record that indicates that each index value value for this index is corresponding.
Cluster indicates that the index to be established is a clustered index, which refers to the index organization in which the index entries are in the same order as the physical order of the records in the table.Another way to create an index is when you define a PRIMARY KEY constraint or a unique constraint in a table. Indexes are also created at the same time, although both methods are capable of creating indexes. However, the details of their index creation are different: when a primary key or UNIQUE constraint is defined on a table, the index created by the PRIMARY KEY constraint or uniqueness constraint overrides the standard index that was created if the table already has a standard index created using the CREATE INDEX statement. This means that the index created by the PRIMARY KEY constraint or UNIQUE constraint is higher than the index created with the CREATE INDEX statement.Eg: for students-Student,course in the curriculum database. SC 3 tables are indexed, in which the student table is indexed by number ascending, and the course table is uniquely indexed in ascending order by course number, and the SC table is indexed by number ascending and course number descending.
CREATE UNIQUE INDEX Stusno on Student (Sno);CREATE UNIQUE INDEX coucno on Course (Cno);CREATE UNIQUE INDEX scno on SC (Sno asc,cno DESC);
4. Delete IndexOnce the index is established, there is a system to use and maintain it, without the user's intervention, indexing is to reduce the time of the query operation, but assume that the data additions and deletions frequently, the system will spend a lot of time to maintain the index, thus reducing the query efficiency. You can delete some unnecessary indexes at this time.In SQL. Dropping an index uses the DROP INDEX statement, which is generally formatted as:DROP Index < index name >;Eg: delete the stusname index of the student tableDROP INDEX Stusname;When you delete an index, the system deletes the descriptive narrative that has the index in the data dictionary at the same time.
5. SummaryIndexes in RDBMS are generally implemented using B + trees and hash indexes. B + Tree Index has the advantage of dynamic balance, the hash index has the characteristics of fast searching, and the index is the internal implementation technology of relational database. belong to the scope of the internal model.When a user defines an index using the CREATE INDEX statement. Ability to define indexes as unique indexes, non-unique indexes, or meal-eating indexes. As to whether an index is a B + tree or a hash index, it is determined by a detailed RDBMS.
Introduction to Database Systems-[04] Index
