Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use strings to do indexing in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

In the era of PC Internet, many of our accounts need to be bound to e-mail, and we occasionally need to use e-mail, that is, strings, to query data. In order to ensure query efficiency, we usually have to index string fields.

As we all know, in InnoDB, the B + tree index is usually used. If the type of index is a string, then we may face such a problem: the length of the index will become very long, and the length of the index will not only take up more storage space, but also increase the maintenance cost of the index. Usually we use string indexing, only the first few characters will be used, if the beginning of the user's mailbox is 26 letters, and the user name is randomly and evenly distributed, then even if we use the first character as the index, we can also reduce the scan amount of 25max 26, if we use the first 2 characters, we can reduce 675max 676 scans. Therefore, even if we only use the first few characters, we can greatly reduce the scanning of the database and improve the query speed.

But in real life, user names are often not randomly distributed, and users who start with a tend to account for a large number. Some string fields are often similar at the beginning, such as the identity card number of residents, the first few digits are usually provinces and towns, and the student information of the Bureau of Education. Id is usually the time of enrollment plus month, the choice of the index is best to meet the uniqueness, we had better use more unique fields to build the index.

For example, with regard to the issue of ID cards, we know that the first six digits of ID cards are usually regional information, and the total number of provinces, cities and regions across the country is thousands, while the last six digits of ID cards are birthday day information and four random numbers. So, there's only an one in 300000 chance that the last six digits are the same. In other words, if the ID card number is stored in reverse order and the first 6 digits are taken as the index, the amount of data scanning can be reduced by nearly 100%, thus greatly improving the efficiency of scanning.

Another way is to build a hash index instead of a B+ tree index. The advantage of a hash index is that it almost always queries a row of data when querying a particular string. But the disadvantage of hash indexing is that it does not support range query and string matching. If the business does not have such a scenario, you can use hash index.

After reading the above, do you have any further understanding of how to use strings for indexing in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.