How to add an efficient index to a string in MySQL

Requirements

In daily needs, it is a very common operation for users to log in to a system using their mobile phone number or email address. So how to establish a reasonable index on fields like mobile phone number or email address? Woolen cloth?

Prefix index

Prefix index uses a part of a field value as an index. If you do not specify the length of the index field when creating an index in MySQL, the entire string will be used to build the index.

语句1：
alter table test add index idx(email);
语句2：
alter table test add index idx(email(8));

For statement 1, the created index will contain the entire email string value in each record.

For statement 2, the index created stores the first 8 bytes of the email field in each record.

The advantage of using a prefix index is obvious, that is, the index will occupy less space, the entire index tree will be more compact, and the height of the tree will be relatively lower.

But correspondingly, the index discrimination will become lower, which may lead to an increase in the number of index scan rows.

When we create an index, the distinction of the index is a very important indicator. The higher the resolution, the fewer duplicate values ​​and the more efficient the scan.

Properly planning the length of the prefix index can not only save space, but also avoid the need to scan additional rows. The specific length to use is recommended to be judged and tested based on our actual business scenarios.

Reverse order Prefix index

Reverse order Prefix index has a classic usage scenario, which is to index ID numbers.

Suppose we now want to maintain the identity information of everyone in a city or county, and querying by ID number is a high-frequency scenario.

The ID number has 15 or 18 digits in total. Generally speaking, the first 6 digits of the ID number of people in the same city or county are generally the same. If the ID number is directly indexed in full, it will be compared. Wasted space, leading to performance degradation. If you use a direct prefix index, the first 6 bits have very little (or even no) discrimination, since most values ​​have the same first 6 bits.

At this time, the benefits of using the reverse prefix index are reflected.

We first store the ID card in reverse order, or make a redundant ID card number field in reverse order, and then take the first 6 digits as a prefix index.

The distinction between the last 6 digits of the ID card is basically enough for us. If you still feel that the query speed is not enough, then it is no problem to take the first 8 digits.

The above is the detailed content of How to add an efficient index to a string in MySQL. For more information, please follow other related articles on the PHP Chinese website!