The optimization of MySQL index and the implementation of LIKE fuzzy query function

When using MySQL for fuzzy queries, the performance of LIKE statements may be greatly affected, especially when the amount of data is large.

But essentially, using like to do fuzzy query, only the followingThree typesCondition:

Prefix matching: If the fuzzy query is a prefix match (such asLIKE '%abc'), MySQL can use indexes to speed up queries. Make sure that the index is created on the relevant columns
Suffix matching:For suffix matching (such as LIKE 'abc%'), MySQL cannot use normal B-tree indexes. Consider using Reverse Index or Full-Text Index
Intermediate Match:For intermediate matches (such as LIKE '%abc%'), MySQL also cannot use normal B-tree indexes. Full-text indexing or search engines such as Elasticsearcha may be a better choice.

1. Prefix matching optimization

Prefix matching (such as LIKE 'abc%') can use B-tree index, so it performs better. Make sure to create an index on the relevant columns

Example:

-- Create a table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL
);

-- Insert data
INSERT INTO users (username) VALUES ('john_doe'), ('jane_doe'), ('alice'), ('bob'), ('john_smith');

-- Create an index
CREATE INDEX idx_username ON users(username);

-- Prefix matching query
EXPLAIN SELECT * FROM users WHERE username LIKE 'john%';

Execution plan analysis
- If the index is used, the key column in the EXPLAIN result will display idx_username, indicating that the query uses the index
- Type will display range, indicating that index range scanning is used

2. Suffix matching optimization

Suffix matching (such as LIKE '%abc'), it is impossible to use B-tree index directly, and it can be optimized by inverting the string and creating an index.

Example:

-- Add inverted column
ALTER TABLE users ADD COLUMN reversed_username VARCHAR(255);

-- Update the inverted column data
UPDATE users SET reversed_username = REVERSE(username);
-- REVERSE('hello') The result is 'olleh'

-- Create an inverted column index
CREATE INDEX idx_reversed_username ON users(reversed_username);

-- Suffix matching query（Convert to prefix matching）
EXPLAIN SELECT * FROM users WHERE reversed_username LIKE REVERSE('doe') + '%';

Execution plan analysis
- When querying the reversed column, the key column in the EXPLAIN result will display idx_reversed_username, indicating that the index is used.
- The type column will display range, indicating that the index range scanning is used

3. Intermediate matching optimization

Intermediate matches (such as LIKE '%abc%') cannot use B-tree index. Consider using full-text indexes or external search engines

Example (using full text index)

-- Create a full text index
CREATE FULLTEXT INDEX idx_username_fulltext ON users(username);

-- Full-text index query
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

Implementation plan analysis:
- The key column in the EXPLAIN result will display idx_username_fulltext, indicating that the full text index is used.
- The type column will display fulltext, indicating that the full text index is used

4. Overlay index optimization

If the query only needs to return the index column, you can use Covering index to avoid table return operations

Example:

-- Create an overlay index
CREATE INDEX idx_username_covering ON users(username, id);

-- Overwrite index query
EXPLAIN SELECT username FROM users WHERE username LIKE 'john%';

5. Reduce the query scope

Reduce the query scope through other conditions and reduce the amount of data in fuzzy queries

Example:

-- Suppose there is a registration time column
ALTER TABLE users ADD COLUMN registered_at DATETIME;

-- Insert data
UPDATE users SET registered_at = NOW() - INTERVAL FLOOR(RAND() * 365) DAY;

-- Reduce the query scope
EXPLAIN SELECT * FROM users 
WHERE registered_at &gt; '2023-01-01' 
AND username LIKE 'john%';

Execution plan analysis
- The key column in the EXPLAIN result will display idx_username, indicating that the index is used
- The value of the rows column will decrease, indicating that the query range is reduced

6. Avoid the beginning of wildcard characters

Try to avoid using wildcards (such as %abc) in LIKE statements, because such queries cannot use indexes

Example:

-- Not recommended query
EXPLAIN SELECT * FROM users WHERE username LIKE '%doe';

-- Optimized query（Use full text indexing）
EXPLAIN SELECT * FROM users WHERE MATCH(username) AGAINST('doe');

Execution plan analysis：
- Not recommended inquiry,typeThe column will be displayedALL, indicates a full table scan.
- In the optimized query,typeThe column will be displayedfulltext, means that the full text index is used.

7. Use external search engines

For complex fuzzy query needs, especially for large data scenarios, external search engines (such as Elatsticsearch) can be used.

Example

Synchronize the data to Elasticsearch.
Use Elasticsearch for fuzzy queries.

8. Partition table optimization

If the data volume is very large, you can use partitioning tables to reduce the amount of data that needs to be scanned for each query

Example:

-- Create a partition table
CREATE TABLE users_partitioned (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    registered_at DATETIME
) PARTITION BY RANGE (YEAR(registered_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

-- Insert data
INSERT INTO users_partitioned (username, registered_at) 
SELECT username, registered_at FROM users;

-- Partition table query
EXPLAIN SELECT * FROM users_partitioned 
WHERE registered_at &gt; '2023-01-01' 
AND username LIKE 'john%';

Execution plan analysis：
- EXPLAINThe result ispartitionsThe column will display the partitions involved in the query, indicating that the query only scans some of the data.

9. Cache results

If the results of fuzzy queries do not change frequently, the query results can be cached to reduce the query pressure of the database

Example:

Use redis to cache query results
Set the expiration time of the cache to ensure the timeliness of the data

Summarize

Through the above methods, you can significantly optimize MySQLLIKEPerformance of fuzzy queries. Select the appropriate optimization strategy based on specific business needs and data characteristics:

Prefix matching: Use normal index.
Suffix matching: Use inverted index.
Intermediate Match: Use full-text index or external search engines.
Big data volume: Use partition tables or external search engines.
High frequency query: Use cache.

Note: Understand MySQL-MATCH... AGAINST Tool ReferenceMySQL-MATCH ... AGAINST Tool

This is the end of this article about MySQL--Export Optimization-LIKE Fuzzy Query. For more related contents of mysql like fuzzy query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!