MySQL establishes efficient index example analysis

This article uses examples to describe how to create efficient indexes in MySQL. Share with you for your reference, the details are as follows:

How to create an ideal index?

• Query frequency
• Discrimination
• Index length
• Overwrite Fields

Discrimination

Assuming there are 1 million users, the genders are basically 500,000 males and 500,000 females, so the differentiation is low.

Short length

The index length directly affects the size of the index file, the speed of addition, deletion and modification, and indirectly affects the query speed (occupies more memory).

High discrimination, short length

Question: What if we make the discrimination high and the length small?

Answer: You can create an index by cutting off the values ​​in the column from left to right.

(1) The shorter the cut, the higher the repetition, the lower the differentiation, and the worse the indexing effect. (2) The longer the cut, the lower the repetition, the higher the differentiation, and the better the indexing effect, but the greater the impact - the addition and deletion are slow, and the query speed is affected.

Therefore, we need to strike a balance between discrimination and length. Conventional method: Cut different lengths and test their discrimination.

Suppose we have a table: a vocabulary table for English Level 4, which contains 13,324 records. How do we add an index to the name field?

How to calculate discrimination?

Extract the first non-repeating number of a word:

select count(distinct left(name,1)) from dict

Total quantity:

select count(*) from dict

Distinction: unique number/total number, the SQL statement is as follows:

select (select count(distinct left(name,1)) from dict) / (select count(*) from dict) as rate;

Then follow these steps to find out the discrimination corresponding to other lengths. Looking at this chart, we can see that when the length is 11, the repetition rate is only 1%. We can consider creating an 11-bit index.

alter table dict add index name name(11);

The left prefix is ​​difficult to distinguish

Tips for creating indexes for columns whose left prefix is ​​not easily distinguished

Such as url column

http://www.baidu.com
http://www.web-bc.cn

The first 11 characters of the column are the same and difficult to distinguish. You can use the following two methods to solve this problem.

(1) Store the column contents in reverse order and create an index

moc.udiab.www//:ptth
nc.cb-bew.www//://ptth

This way the left prefix has great discrimination

(2) Pseudo-hash index effect

Store both url and url_hash columns

#Create table create table t10 (
id int primary key,
url char(60) not null default ''
);
#Insert into t10 values
(1,'http://www.baidu.com'),
(2,'http://www.sina.com'),
(3,'http://www.sohu.com.cn'),
(4,'http://www.onlinedown.net'),
(5,'http://www.gov.cn');
#Modify the table structure and add the urlcrc column alter table t10 add urlcrc int unsigned not null;

When storing, insert the crc32 code corresponding to the url into the database, and then create an index according to the urlcrc field. When searching, we convert the corresponding url into crc32 in the business layer for search, and then we can use the index.

Because the result of crc is a 32-bit int unsigned number, there will be duplication when the data exceeds 4 billion, but it is worth it. (The index length is int4 bytes)

Multi-column indexes

Considerations for multi-column indexes - column query frequency, column differentiation, and be sure to combine them with actual business scenarios

Taking ecshop as an example, cat_id and brand_id in the goods table are multi-column indexes. From the perspective of differentiation, brand_id has higher differentiation. However, from the actual business of the mall, customers generally choose large categories -> small categories -> brands first, and finally choose to create two indexes:

(1) index(cat_id,brand_id)
(2) index(cat_id,shop_price)

You can even add (3) index (cat_id, brand_id, shop_price), 3 redundant indexes

But the first two columns in (3) are the same as the first two columns in (1), so we can remove (1) and create two indexes.

index(cat_id,price) and index(cat_id,brand_id,shop_price);

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.