An index is an optional structure that users can choose to create on specific fields based on their business needs, thereby accelerating queries on those fields. This topic describes the advantages and disadvantages of using indexes, as well as the availability and visibility of indexes.
Advantages and disadvantages of indexes
The advantages of indexes are as follows:
Users can accelerate queries without modifying SQL statements, by scanning only the necessary data.
Indexes typically store fewer columns, which can reduce query I/O.
The disadvantages of indexes are as follows:
Choosing the right fields to create indexes requires a deep understanding of the business and data model.
When business changes occur, it is necessary to reassess whether the existing indexes still meet the needs.
Maintaining index tables during data writes consumes a certain amount of performance.
Index tables occupy resources such as memory and disk space.
Availability and visibility of indexes
Index availability
When a partition is dropped, if the rebuild index option is not specified, the index is marked as UNUSABLE, indicating that it is unavailable. In this case, the optimizer does not use the index during DML operations, and the index does not need to be maintained.
Index visibility
Index visibility refers to whether the optimizer ignores the index. If an index is invisible, the optimizer ignores it, but the index still needs to be maintained during DML operations. Before deleting an index, it is common practice to first make the index invisible to observe its impact on the business. If no negative impact is observed, the index can then be safely deleted.
Relationship between indexes and keys
A key is a set of columns or expressions on which an index can be created. However, indexes and keys are different concepts. An index is a physical object stored in the database, while a key is a logical concept.
Types of indexes
Global and local indexes
Global index
A global index is created across the entire table and can contain data from all partitions of the table. Global indexes are suitable for queries that span multiple partitions.
Local index
A local index is created within each partition and contains only data from that partition. Local indexes are suitable for queries that occur within a single partition.
Unique and non-unique indexes
Unique index
A unique index requires that the values in the indexed columns are unique. Each index value corresponds to only one record. Unique indexes are used to ensure the uniqueness of a field in the table.
Non-unique index
A non-unique index allows for duplicate values in the indexed columns, meaning that multiple index values can correspond to one or more records. Non-unique indexes are used to accelerate queries on the indexed columns and do not guarantee the uniqueness of the indexed columns.
Spatial index
A spatial index is a special type of index designed to accelerate queries on spatial data. It is primarily used in geographic information systems (GIS) and spatial data processing. Spatial indexes support efficient queries and spatial relationship analysis for spatial objects such as points, lines, and areas.
Function-based index
A function-based index is an index created based on the results of calculations performed on one or more columns in the table. Function-based indexing is an optimization technique that allows for quick location of matching function values during queries, thereby avoiding redundant calculations and improving query efficiency.