Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

Show all

Index Design

Updated on 2025-02-12 GMT+08:00
  • Use the same field type to prevent implicit conversion from causing invalid indexes.
  • Create unique indexes on all minimum sets of fields or combinations of fields with uniqueness.
    For example, there is a table containing the fields a, b, c, d, e, and f. If the combinations of fields ab and ef have uniqueness, you are advised to create unique indexes for ab and ef, respectively.
    NOTE:

    Even if complete verification control is implemented at the application layer, dirty data is generated as long as there is no unique index according to Murphy's Law.

    Before creating a unique index, consider whether it is helpful for queries. Useless indexes can be deleted.

    Evaluate the impact of extra indexes on INSERT operations. Determine whether to create unique indexes based on the requirements for the correctness and performance of data with uniqueness.

  • Create indexes on fixed-length fields (for example, INT). When creating an index on a VARCHAR field, the index length must be specified. It is not necessary to create an index on the whole field. The index length is determined according to the actual text distinction.
    NOTE:

    The index length and distinction are a pair of contradictions. Generally, for string type data, the distinction of an index with a length of 20 bytes will be higher than 90%. The distinction formula is COUNT(DISTINCT LEFT(Column_name, Index_length))/COUNT(*). Place the column names with a high distinction on the left.

  • If possible, do not use left fuzzy search (for example, SELECT * FROM users WHERE u_name LIKE ' %hk') or full fuzzy search on the page to avoid degradation from index scan to full table scan. Solve the problem at the application layer.
    NOTE:

    An index file has the leftmost prefix matching feature of B-tree. If the value on the left is not determined, the index cannot be used.

  • Use a covering index to query data and avoid returning to the table. However, do not add too many fields to the covering index, or the write performance will be compromised.
    NOTE:

    Types of indexes that can be created include primary key indexes, unique indexes, and normal indexes. A covering index indicates that if you execute EXPLAIN statements, "using index" will be displayed in the Extra column.

  • Optimize the SQL performance as follows: range (minimum requirement), ref (basic requirement), and consts (maximum requirement).
  • When creating a composite index, place the column with the highest distinction on the left.
  • Ensure that the number of indexes in a single table is at most 5, or does not exceed 20% of the number of table fields.
  • Avoid the following misunderstandings when creating indexes:
    • Indexes should be frequently used. An index needs to be created for a query.
    • Indexes should be as few as possible. Indexes consume space and slow down updates and insertions.
    • Unique indexes cannot be used. Unique features must be resolved at the application layer using the "query first and then insert" method.
  • Reduce the use of ORDER BY that cannot be used with indexes based on the actual service requirements. The statements such as ORDER BY, GROUP BY, and DISTINCT consume many CPU resources.
  • If a complex SQL statement is involved, use the existing index design and add EXPLAIN before the SQL statement. EXPLAIN can help you optimize the index by adding some query restrictions.
  • Execute new SELECT, UPDATE, or DELETE statements with EXPLAIN to check the index usage and ensure no Using filesort and Using temporary are displayed in the Extra column. If the number of scanned rows exceeds 1,000, exercise caution when executing these statements. Analyze slow query logs and delete unused slow query statements every day.
    NOTE:
    EXPLAIN:
    • type: ALL, index, range, ref, eq_ref, const, system, NULL (The performance is sorted from poor to good from left to right.)
    • possible_keys: indicates the indexes from which MySQL can choose to find rows in this table. If there is an index on a field, the index is listed but may not be used by the query.
    • key: indicates the key (index) that MySQL actually decided to use. If key is NULL, MySQL found no index to use for executing the query more efficiently. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.
    • ref: shows which columns or constants are compared to the index named in the key column to select rows from the table.
    • rows: indicates the estimated number of rows to be read for required records based on table statistics and index selection.
    • Extra:
      • Using temporary: To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
      • Using filesort: MySQL must do an extra pass to find out how to retrieve rows in sorted order.
      • Using index: The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. If Using where is displayed at the same time, it indicates that desired information needs to be obtained by using the index tree and reading rows of the table.
      • Using where: In WHERE clause, Using where is displayed when the desire data is obtained without reading all the data in the table or the desire data cannot be obtained by only using indexes. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
  • If a function is used on a WHERE statement, the index becomes invalid.

    For example, in WHERE left(name, 5) = 'zhang', the left function invalidates the index on name.

    You can modify the condition on the service side and delete the function. When the returned result set is small, the service side filters the rows that meet the condition.

  • For ultra-large tables, you also need to comply with the following rules when using indexes:
    • Create indexes for columns involved in the WHERE and ORDER BY statements. You can use EXPLAIN to check whether indexes or full table scans are used.
    • Fields with sparse value distribution, such as gender with only two or three values, cannot be indexed.
    • Do not use string fields as primary keys.
    • Do not use foreign keys. Programs can enforce the constraints.
    • When using multi-column indexes, arrange them in the same order as the query conditions and remove unnecessary single-column indexes (if any).
    • Before removing an index, conduct a thorough analysis and back up the data.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback