MySQL index length and degree of differentiation

First, the index length and the degree of differentiation are contradictory,

The index length is too short, then the sensitivity is very low, the index length is longer, the sensitivity is high, but the index is to occupy memory, so we need to find a balance point;

So how does this balance come about?

For example, the user table has a field username, to index him, the question is how much index length is appropriate?

In fact, we know that there is hundreds surname in surnames, but most people's surname is concentrated in the first 10; If I set the index length to 1, the dye-to-memory is small, but the sensitivity is low.

The lower the efficiency of the low-sensitivity index. It is too long to occupy memory;

The first thing you need to know is that MySQL's index is well-sequenced. If the higher the sorting degree, the lower the sensitivity, the slower the sorting;

For example: (Zhang, Zhang San, Zhang Sango), if the index length of 1, then the index of each row is the word Zhang, there is no degree of distinction, how do you let him sort? As a result, three rows are completely random, because the indexes are the same;

If the length is 2, then at least the first two are ranked at the same time, if take 3, the degree of distinction reached 100%, the order is completely correct;

Wait, do you think the longer the index, the better? The answer must be wrong, such as (Zhang, Li, Wang) and (Zhang San la la, Zhang San hehe, Zhang three-call); The former in-memory sorting takes up less space, the sorting is also fast, the latter is significantly slower and more memory, in big data applications this point is very scary;

So make a trade-off; this trade-off is not without a fixed amount; it needs to be judged by the data in your own database; the more general formula is:

Test is the field to be indexed, 5 is the index length,

Select Count (distinctleft (test,5)) / Count (*fromtable;   

Find a floating-point number, this floating point is gradually trending to 1, the online search for a picture to analyze;

This place observes that when the index length reaches 4, it tends to be 1, so the length is set to 4 is the best, in the large point of the index effect is already very small, this place is not said to be close to 1;

In fact, this value of 0.1 is acceptable; In short, find a balance point;

There are some special fields that are not very smooth, such as having a URL field and most of the URLs are http://www. Begins with

In this case, the index length taken to 11 is not valid, the need for a longer index, then there is no elegant way to solve it;

The first method: The data can be stored in reverse in the database;

The second method: The string is CRC32 hashed;

Both methods are good, of course, to cooperate with the client program to complete;

MySQL index length and degree of differentiation