Index Management

Indexes are a key technology for enhancing database query performance. By creating indexes for frequently queried fields, query speed and user experience can be significantly improved.

Management Portal

1. Access the console: Go to the Tencent Cloud Development Platform
2. Select Database: Switch to Tencent Cloud Development Platform/Database/Document-based
3. Go to Index Management: Select the target collection and click the 'Index Management' tab.
4. Manage Indexes: Create, delete, or modify index configurations.

Detailed Explanation of Index Types

Single-Field Index

Applicable Scenarios: Query and sort operations targeting a single field

Feature Description:

• Supports nested field indexes, accessing them using "dot notation".
• Ascending or descending order can be specified.
• Suitable for simple single-condition queries

Example Configuration:

// Raw data structure
{
  "_id": "product_001",
  "name": "iPhone 15",
  "price": 5999,
  "style": {
    "color": "Deep Space Black",
    "storage": "128GB"
  }
}

Index Configuration Example:

Field Path	Index Type	Sort Order	Applicable Queries
name	Single Field	Ascending	Query by product name
price	Single Field	Descending	Sort by price
style.color	Single Field	Ascending	Filter by color

💡 Note: Nested fields use dot notation, such as style.color to access the color field within the style object.

Composite Index

Applicable Scenarios: Multi-field joint queries and complex sorting operations

Core Concept: An index contains multiple fields and supports prefix matching queries.

Sample Data:

{
  "_id": "student_001",
  "name": "Zhang San",
  "age": 20,
  "score": 85,
  "class": "Computer Science"
}

Index Prefix Rule:

Suppose we create a composite index: name + age + score

Index Prefix	Supported Query Combinations	Query Example
name	Query name alone	db.collection('students').where({name: 'Zhang San'})
name, age	Query name + age	db.collection('students').where({name: 'Zhang San', age: 20})
name, age, score	Query all fields	db.collection('students').where({name: 'Zhang San', age: 20, score: 85})

💡 Prefix Matching Principle

The composite index (name, age, score) includes the following prefixes:

• ✅ name - can leverage the index
• ✅ name, age - can leverage the index
• ✅ name, age, score - can leverage the index
• ❌ age - cannot leverage the index (not a prefix)
• ❌ score - cannot leverage the index (not a prefix)
• ❌ age, score - cannot leverage the index (not a prefix)

Key Features of Composite Indexes:

🔄 1. Importance of Field Order

The order of index fields directly affects query performance:

Index Definition	Supported Queries	Unsupported Queries
(name, age)	✅ name query ✅ name + age query	❌ age query alone ❌ age + score query
(age, name)	✅ age query ✅ age + name query	❌ name query alone ❌ name + score query

📊 2. Impact of Sort Order

The sort order of indexes affects whether they can be leveraged in sort queries:

Index Configuration: age: ascending, score: descending

Query Sort Order	Index Usage	Notes
(name, age)	✅ name query ✅ name + age query	❌ age query alone ❌ age + score query
(age, name)	✅ age query ✅ age + name query	❌ name query alone ❌ name + score query
age: descending, score: ascending	✅ Yes	Index supports reverse scan
age: ascending, score: ascending	❌ No	Sort order inconsistent
age: descending, score: descending	❌ No	Sort order inconsistent
score: any, age: any	❌ No	Prefix rule not satisfied

Index Configuration: age: ascending, score: ascending

Query Sort Order	Index Usage	Notes
age: ascending, score: ascending	✅ Yes	Exactly matches the index order
age: descending, score: descending	✅ Yes	Index supports reverse scan
age: ascending, score: descending	❌ No	Sort order inconsistent
age: descending, score: ascending	❌ No	Sort order inconsistent

Geospatial Index

Applicable Scenarios: Geolocation queries, proximity search, regional filtering

Features:

• Supports geospatial indexing for planar geometry
• Must create a dedicated geospatial index for geospatial fields
• Supports geometric shapes such as points, lines, and polygons

Data Structure Example:

{
  "_id": "store_001",
  "name": "Starbucks Coffee Shop",
  "location": {
    "type": "Point",
    "coordinates": [116.4074, 39.9042]  // [longitude, latitude]
  },
  "address": "Chaoyang District, Beijing"
}

Index Creation:

1. Select the collection in the console
2. go to the Index Management page
3. Select the geolocation field (e.g., location)
4. Set the index type to "geospatial index"

Query Example:

// query nearby stores within 1000 meters
db.collection('stores').where({
    location: db.command.geoNear({
        geometry: db.Geo.Point(116.4074, 39.9042),
        maxDistance: 1000
    })
}).get()

Considerations for Index Usage

Uniqueness Constraint

When creating an index, select Unique for the index property to add a uniqueness constraint. This constraint requires that values corresponding to the indexed field must be unique within the collection.

For example, if a collection has an index field foo created with the "unique" attribute, then no documents with identical foo field values can exist within that collection.

Tip

It is particularly important to note that if a field is absent from a record, its value defaults to null for the indexed field. If the index has a uniqueness constraint, no two or more records are allowed to have a null value for this field / lack this field.

Size Limits

• The size limit of an indexed field cannot exceed 1024 bytes.
• When adding an index, if any document in the collection has an indexed field exceeding 1024 bytes, an error will be reported during index creation.
• For an indexed field, inserting a document where this field exceeds 1024 bytes will result in an error.

Tip

Each English letter (regardless of case) occupies 1 byte of space, and each Chinese character occupies 2 bytes of space.

Regular Expression

Regular expression queries cannot utilize indexes to enhance performance.