The MySQL indexing mechanism is the core of database optimization, and reasonable use can significantly improve performance. Common types include: 1. The primary key index is unique and non-empty, and it is recommended to use self-increment integer type; 2. The unique index ensures that the column value is unique, suitable for deduplication fields such as usernames; 3. Ordinary index is used to accelerate WHERE conditional query, suitable for fields with low repetition rate; 4. The joint index is based on multiple fields, follows the principle of leftmost matching, and the fields with high distinction degree are placed in front; 5. The full-text index is suitable for large text fuzzy searches, and Chinese word segmentation and delay issues need to be paid attention to.

MySQL's indexing mechanism is one of the core of database performance optimization. If you find that the query is slow and the data volume is stuttering, it is likely that it is because the index is not used well, or the inappropriate index is used.

MySQL supports multiple types of indexes, each of which is different in application scenarios. Understanding their differences and usage methods can help you better improve query efficiency. Let’s start with common index types, and then take a look at how to select and optimize them in actual applications.

Primary Key Index (PRIMARY KEY): Unique and non-empty

The primary key index is a field in a table that uniquely identifies each row record. It has two characteristics:

• Uniqueness: Cannot be repeated
• Non-emptiness: Cannot be NULL

Usually we will use the autoincrement ID as the primary key, such as id INT AUTO_INCREMENT PRIMARY KEY . This approach is simple and efficient, especially when doing association queries.

suggestion:

• Each table should have a primary key
• Try to use integer primary keys, because they are faster
• Do not modify the primary key value frequently, as it may easily cause chain update problems

Unique index (UNIQUE): Ensure that column values ??are unique

Unique indexes are similar to primary keys, and also require that column values ??are unique, but allow multiple NULL values ??(specific behavior depends on database implementation). Suitable for use in fields such as username, mobile phone number, etc. that need to be deduplicated.

For example:

 CREATE UNIQUE INDEX idx_username ON users(username);

Notes on use:

• Inserting the duplicate value will cause an error, and it needs to be judged or processed in advance.
• Query speed is slightly slower than normal indexes because you need to check uniqueness

Normal Index (INDEX): The most commonly used acceleration tool

There is no uniqueness limit for ordinary indexes, and they are mainly used to speed up query speed. Applicable to fields that often appear in WHERE conditions, such as order status, user gender, etc.

When to add?

• Fields are often used to filter data
• The data duplication rate is not high (too much repetition may not go through the index)
• When combining queries, you can consider establishing a joint index

For example:

 CREATE INDEX idx_status ON orders(status);

This will be faster when querying completed orders:

 SELECT * FROM orders WHERE status = 'completed';

Composite Index: Combination punch is more effective

Union indexes are indexes created based on multiple fields and are suitable for multi-conditional queries. For example, we often check orders based on user ID and time range:

 CREATE INDEX idx_user_time ON orders(user_id, created_at);

Key points to use:

• The principle of leftmost matching is very important: Only when the leading column is used will the following one take effect.
• The order is very particular: putting fields with high distinction in front of them is better
• Don't add too many fields at will, the index itself also has maintenance costs

For example, this query will hit the above joint index:

 SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

But if you only look for created_at, it will not hit.

Full-text index (FULLTEXT): Specialized text search

When you need to fuzzy match large text fields such as article content and product description, ordinary LIKE operations are very inefficient. At this time, you can consider the full text index.

Created as follows:

 CREATE FULLTEXT INDEX idx_content ON articles(content);

Then you can use the MATCH AGAINST syntax to query:

 SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

Notes:

• Limited support for Chinese, and it needs to be used with word segmentation plug-in
• There is a certain delay, not real-time update
• Not suitable for exact matching, more suitable for natural language search

Basically these commonly used index types. Although there are not many types, there are still many details to pay attention to when using them. Rational use of indexes can not only improve query speed, but also reduce server resource consumption.

The above is the detailed content of Deep Dive into MySQL Indexing Types and Usage. For more information, please follow other related articles on the PHP Chinese website!