An index is an optional structure that users can create on some fields based on their business needs. Its purpose is to accelerate the query speed on those fields. This topic mainly introduces the advantages and disadvantages of using indexes, as well as their availability and visibility.
Advantages and disadvantages
Indexes have the following advantages:
Indexes enable faster SQL queries without the need to modify SQL statements, as only the necessary data is scanned.
Indexes typically store a minimal number of columns, reducing query I/O overhead.
Indexes have the following disadvantages:
Creating indexes requires a deep understanding of the business and its data model to choose the appropriate columns.
If any changes occur in the business, a reassessment of existing indexes is necessary to ensure they still meet the requirements.
Maintaining index table data during incremental data writes can impact database performance.
Index tables consume resources, including memory and disk resources.
Usability and visibility
Usability of indexes
In a DROP PARTITION operation, if the rebuild index field is not specified, the index is marked as UNUSABLE, which indicates that the index is unavailable. In this case, the index does not need to be maintained in Data Manipulation Language (DML) operations, and is ignored by the optimizer.
Visibility of indexes
The visibility of an index determines whether the index is ignored by the optimizer. If an index is invisible, it is ignored by the optimizer but needs to be maintained in DML operations. Before you drop an index, you can make it invisible to test the effect on your business. If your business is not affected, you can drop the index.
Relationships between indexes and keys
A key is one or more columns or expressions on which you can create indexes. A key is a logical concept, whereas an index is an object stored in a database.
Index types
Local and global indexes
Global indexes
A global index is an index that is created on the entire table and can contain data from all partitions of the table. Global indexes are suitable for cross-partition queries.
Local indexes
A local index is an index that is created in each partition and contains only data of that partition. Local indexes are suitable for scenarios where queries are performed within a specific partition.
Unique and non-unique indexes
Unique indexes
A unique index ensures the uniqueness of values of an indexed column in the index. Each index value can correspond to only one record. Unique indexes ensure that no two rows of a table have duplicate values in an indexed column.
Non-unique indexes
Non-unique indexes allow duplicate values in an indexed column. That is, multiple index values can correspond to one or more records. Non-unique indexes are used to accelerate queries on indexed columns. However, non-unique indexes cannot guarantee the uniqueness of indexed columns.
Spatial indexes
A spatial index accelerates the query of spatial data, which is mainly used to process geographic information system (GIS) and spatial data. Spatial indexes can support efficient query and spatial relationship analysis of spatial objects, such as points, lines, and planes.